texts: Is there a way to reduce the amount of time spent on a point in time recovery?

texts:
I'm pretty sure I know the answer to this, but the limitations of a point in time recovery blows my mind.

Say I have a 2TB database set to FULL recovery mode. I diligently take weekly full backups, daily diff backups, and log backups every 30 minutes.

Now, I want to roll the database back to 1 hour ago. Do I really have to restore the entire full backup, diffs, and logs up to one hour ago? Am I correct that this will take a very long time?

Is there no way to ROLLBACK the changes using the transaction log files so that this type of recovery takes a few minutes?


texts: MySQL 5.7 - Disable use of SSL connection

texts:
For some reason when setting up a server with MySQL 5.7, it was created to use SSL connections:

have_ssl=YES,
have_openssl =YES

There is no SSL configured in my.cnf.

How can I disable this option? It's for a development machine and we don't need this on it. We get an SSL authentication error, and for some reason this feature is enabled on the server while it was not configured and there is no SSL reference in the my.cnf.

I was using 5.7 and could not find a way to disable the SSL. I needed to downgrade to 5.5 (due to timestamp rounding bug on 5.6/7) and on 5.5 SSL is disabled. I just want to know if there is an option/command to change this option to disabled. In 5.5 its disabled by default.


texts:
I'm wondering if anyone knows if there is a way to return the name that was used to connect to the SQL server rather than the actual server's name. For instance I typically connect to the server via a DNS CName. But if I run this ..
select @@SERVERNAME

... I get the actual server name. I'm looking to find a way to get the CName with which I connected if it is even possible. Thoughts?


texts: What is effective way to delete duplicate records based on multiple columns in MySQL?

texts:
I have a table named users having approx 70 Million records, out of which I guess 10 Million is duplicate. How to remove those 10 Million records keeping one copy of that.  Two records are said duplicate if both have same email, alt_email, mobile and alt_mobile.I searched on stackexchange, but couldn't found any solution effective with such a huge data. Any help would be appreciated. My table is in MySQL 5.5.45 with InnoDB engine.

This is the users table. Apart from mentioned columns, I have few more columns but the mentioned ones are relevant to the question.

CREATE TABLE users
(
  id int(11) NOT NULL AUTO_INCREMENT,
  email varchar(200) DEFAULT NULL,
  alt_email varchar(200) DEFAULT NULL,
  mobile varchar(100) DEFAULT NULL,
  alt_mobile varchar(100) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY mobile (mobile),
  KEY email (email)
) ENGINE=InnoDB;


texts: Hexadeicimal data and corresponding MySQL field type

texts:
I used some Node.js code to stream a file image before inserting it to the database. I have a problem inserting the data into the related MySQL table field. So I run console.log(streamedFileContent) on the server to see what is this data about and I got this:

<Buffer 89 50 4e 47 0d 0a 1a 0a 00 00 00 0d 49 48 44 52 00 00 03 9e 00 00 03 52 08 06 00 00 00 0d a5 11 28 00 00 00 04 73 42 49 54 08 08 08 08 7\
c 08 64 88 00 ... 150808 more bytes>

What should be the appropriate MySQL field type for this data?


texts:
What does the TRUNCATE do differently to drop?

I believe it deletes all the data in the table but keeps the table name in the database, where as DROP deletes all data and the table. Is this correct ?


texts:
I migrated my eCommerce website database (Magento) from MySQL to MariaDB (10.0.25-MariaDB) and my site isn't performing as well as it did with MySQL.

I've gone through my.cnf several times but to be honest I'm not sure about some of the settings but suspect there is one or more that are hurting performance since I had taken taken them over from my old my.cnf.

The server is a bit older with 2 x Xeon E5620 (16 cores total hyper-threaded) and 118GB RAM.

my.cnf

[mysqld]
local-infile = 1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

CACHES AND LIMITS

back_log = 200
interactive_timeout = 7200
wait_timeout = 7200
net_read_timeout = 120
net_write_timeout = 300
sort_buffer_size = 2M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
join_buffer_size = 8M
tmp_table_size = 128M
max_heap_table_size = 128M
query_cache_type = 1
query_cache_size = 96M
query_cache_limit = 2M
max_connections = 70
thread_cache_size= 32
open_files_limit = 65535
table_definition_cache = 4000
table_open_cache = 15000
max_allowed_packet = 32M
key_buffer_size = 512M # up from 256#

INNODB_

innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 7200
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_buffer_pool_size = 26G

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
!includedir /etc/my.cnf.d

MYSQL TUNER REPORT

Storage Engine Statistics
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 96K (Tables: 163)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in InnoDB tables: 7M (Tables: 954)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 2K (Tables: 33)
[!!] Total fragmented tables: 913

Performance Metrics
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 26.7G global + 8.3M per thread (70 max threads)
[OK] Maximum possible memory usage: 27.3G (25% of installed RAM)
[OK] Slow queries: 0% (14K/1B)
[OK] Highest usage of available connections: 47% (33/70)
[OK] Key buffer size / total MyISAM indexes: 512.0M/862.4M
[OK] Key buffer hit rate: 100.0% (17B cached / 7M reads)
[OK] Sorts requiring temporary tables: 0% (42K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 2417569
[OK] Temporary tables created on disk: 24% (3M on disk / 15M total)
[OK] Thread cache hit rate: 99% (33 created / 1M connections)
[!!] Table cache hit rate: 0% (1K open / 225K opened)
Use of uninitialized value $myvar{"table_cache"} in concatenation (.) or string
    at mysqltuner.pl line 868, <> line 2 (#1)
[OK] Open file limit used: 0% (69/65K)
[OK] Table locks acquired immediately: 99% (653M immediate / 653M locks)
[OK] InnoDB data size / buffer pool: 7.4M/26.0G

Recommendations
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Upgrade MySQL to version 4+ to utilize query caching
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Upgrade to MySQL 4.1+ to use concurrent MyISAM inserts
Variables to adjust:
join_buffer_size (> 8.0M, or always use indexes with joins)
table_cache (> )

Are there any settings that are incorrect, missing or that should be changed?


texts:
I have a table with 3 columns: key, status and rank.
status contains numbers and rank contains t or f.
key is auto numbered and irrelevant here.

my goal is to count how many in each status are t and how many false
for example:

if my data is (key, status, rank)

1 10 f
2 10 t
3 10 t
4 11 t

then result will be : (status, rank, count value)

10 f 1
10 t 2
11 t 1

how do i do that?


texts:
What would be the best index(es) to add to the tables in order to make this query as fast as possible. The sd.incidents tables is about 2.5M records, problems about 8K, and changes about 120K.

select
  --bunch of fields
from
    sd.incidents a
    left join sd.problems b on a.pr_number = b.pr_number
    left join sd.problems c on b.pr_number = c.parent_pr_number and c.is_root_cause = 1
    left join sd.changes d on b.caused_by_change_number_clean = d.change_number
where
    a.severity in (1,2,3) and
    (a.pr_number is not null or a.parent_number is null)


texts: Auto update stats turned off for a user database on SQL Server

texts:
While trying to improve the performance of few queries, we found via execution plan that there were lot of index / clustered index seeks.

Therefore:

First thing we did, was to check our Re-indexing and update stats job which runs weekly for this user DB ( Around 400 GB in size and is used 24*5). The job was running fine.

Later when we ran SP_Blitz, we came to know that auto-update-stats is disabled for this user DB. We expected this to be a possible cause and change it from false to TRUE (Auto update stats)

Also, per SP_blitz there are user-created statistics for this DB. When ran the query to check how many, we saw around 7K user stats out there.

So my questions would be

setting the Auto update stats to TRUE would require a reboot or once changed I need to track the performance
Should we consider dropping those user created stats or manually look into them one by one?

How should we proceed on this, please suggest, thanks!


texts: MySQL: Updating a column referenced by two foreign keys in the same table leads to foreign key constraint failure

texts:
MySQL 5.5

I have a simple InnoDB table:

CREATE TABLE `T` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `deleted` bigint(20) unsigned NOT NULL DEFAULT '0',
  `other` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`,`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

And another table that encodes relationships among entries from the first table:

CREATE TABLE `T_T` (
  `id1` bigint(20) unsigned NOT NULL,
  `deleted1` bigint(20) unsigned NOT NULL,
  `id2` bigint(20) unsigned NOT NULL,
  `deleted2` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id1`,`deleted1`,`id2`,`deleted2`),
  KEY `fk_T_T_2_idx` (`id2`,`deleted2`),
  CONSTRAINT `fk_T_T_2` FOREIGN KEY (`id2`, `deleted2`) REFERENCES `T` (`id`, `deleted`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_T_T_1` FOREIGN KEY (`id1`, `deleted1`) REFERENCES `T` (`id`, `deleted`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Creation of tables and population works perfectly fine.

INSERT INTO T (id, deleted, other) VALUES (1, 0, 'whatever');
INSERT INTO T_T (id1, deleted1, id2, deleted2) VALUES (1, 0, 1, 0);

Updating an entry in T, however,  with something like this:

UPDATE T SET deleted = 1 WHERE id = 1;

leads to the following error:

ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails    (`test`.`T_T`, CONSTRAINT `fk_T_T_1` FOREIGN KEY (`id1`, `deleted1`) REFERENCES `T` (`id`, `deleted`) ON DELETE CASCADE ON UPDATE CASCADE)

As noted by others, this works if the entries in T_T don't refer to self (id1 != id2).  But an UPDATE will fail when a row in T_T refers with both ids to the same row in T.

Is there a problem in MySQL with foreign keys that refer to another table's column(s) twice?
Is there a work around for something like this?


texts: SQL Server - Get a new columns with name of the columns have the values X base on rows

texts:
I want to get a new columns on table and this new columns need to contain another columns name separate by - example: (field_1-field_2) where the values = X

I can not hard code the column names because each time these column name are different depending on the projects.

|  id  |  field_1 | field_2  | field_3 |  field_4 |  field_5  | new_column              |
|  1   |     X    |  NULL    |      X  |  NULL    |     X     | field_1-field_3-field_5 |
|  2   |  NULL    |     X    |      X  |  NULL    |  NULL     | field_2-field_3         |
|  3   |  NULL    |  NULL    |   NULL  |     X    |  NULL     | field_4                 |


texts: Recover Oracle database with damaged system datafile

texts:
I'm working with Oracle version 11. My system01.dbf is corrupted and there is no backup.
The database was in archive log mode and logs exists.

Is there any way to recover data and import to a new database?


texts: Error 7373 connecting to SSAS 2008 only from one machine

texts:
Within the last week, I've gotten the following error when trying to use a linked server in SSMS (tried 2008 and 2012) to connect to a SSAS 2008 R2 instance on our cluster:

  Cannot set initialization properties for OLE DB provider 'MSOLAP' for
  linked server ....  returned message "The following system error
  occurred: ." (Microsoft SQL Server, Error: 7373)

It's not related to my login, because I can use it fine from other servers as well as SQL agent jobs.  Found some googling suggesting a DNS issue, but using linked servers with the fully-qualified name as well as IP address doesn't make a difference.  Ports are open and listening on the server, and "Allow Inprocess" is enabled for MSOLAP on both SSMS 2008 & 2012.

My machine is Win7. I've been testing from Win2008 servers; other machines with Win7 do not have this issue.

I see a few hits on the web about this issue but ZERO about resolutions. Anyone have any ideas what else I can try?


texts: Preferred design to avoid circular/multiple update paths

texts:
Consider the following design:
CREATE TABLE dbo.Farmers
(
    FarmerName varchar(10) NOT NULL
        PRIMARY KEY
);

CREATE TABLE dbo.FarmEquipment
(
    FarmEquipmentName varchar(10) NOT NULL
        PRIMARY KEY
    , CreatorFarmer varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE dbo.Fields
(
    FieldName varchar(10) NOT NULL
    , OwnerFarmer varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
    , FarmEquipment varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.FarmEquipment(FarmEquipmentName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Use case:

Farmer Joe owns land.
Farmer Ted owns land.
Farmer Joe builds a combine-harvester, and loans it to Farmer Ted for use on one of Farmer Ted's fields.
If Farmer Joe changes his name to Farmer Joseph, I want that to reflect in the Fields table, so Farmer Ted knows who owns that combine harvester he's been using.

I realize this is the exact use-case for surrogate keys, but I'm trying to determine how this would work if you're using only natural keys.
In SQL Server, you cannot actually create this structure, since the ON UPDATE CASCADE and ON UPDATE DELETE clauses in dbo.Fields produce the following error message:

Msg 1785, Level 16, State 0, Line 21
Introducing FOREIGN KEY constraint 'FK__Fields__FarmEqui__3AD6B8E2' on table 'Fields' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 21
Could not create constraint. See previous errors.

How should this model be expressed in SQL Server?
I realize I can just remove the ON UPDATE clauses, but that's not really the point. I want updates and deletes to cascade.


texts: where are the infos from 'SHOW CREATE TABLE' coming from? It's not INFORMATION_SCHEMA

texts:
Quote from MariaDB 10.2 CHECK and DEFAULT clauses:

  Another limitation is that CHECK metadata are not accessible via the
  INFORMATION_SCHEMA. The only way to find out if a table has CHECK
  clauses is parsing the output of SHOW CREATE TABLE.

I also found in MariaDB KB

  Note that SHOW CREATE TABLE is not meant to provide metadata about a
  table. It provides information about how the table was declared, but
  the real table structure could differ a bit. For example, if an index
  has been declared as HASH, the CREATE TABLE statement returned by SHOW
  CREATE TABLE will declare that index as HASH; however, it is possible
  that the index is in fact a BTREE, because the storage engine does not
  support HASH.

So from where does SHOW CREATE TABLE collect its output?


texts: Using AUTO_UPDATE_STATISTICS_ASYNC on an ASP.NET Session database?

texts:
I don't see a lot of bulk operations within plan cache, mostly individual insert/update/delete type commands executed from sprocs, so on the surface it seems like a good candidate for async stats to possibly reduce stat refresh.  I see a good bit of performance bottlenecks related to operations against our production session database during times of high use so I've already addressed things like writelog wait types via delayed durability forced, but am always looking to get a little better IO.  Does anyone have experience changing a ASP.NET Session database to use this?  Comparatively I see this setting gets enabled by default from sql projects for Identity server by default which tend to operate in a similar manner (related to creating/destroying data frequently) except revolving tokens.  I don't have a current good means to generate loads of session workloads locally so I would prefer a response from someone who may have given this a swing.
Thanks in advance


texts:
I was applying an index to a database with archived data. I knew this would take quite a while. It was at 14 hours running when I left it last night. I came back to it this morning and enterprise manager told me it had lost connection.

On checking activity monitor on the server, I can see the task. Its state is SUSPENDED with a wait type of IO_COMPLETION.

Is there a way to resume this or kill it off and start again?

I ran sp_who2 but it's not being blocked by any other activity and has a status of ROLLBACK. If I then run kill 61 with statusonly, SQL tells me that;

  Status report cannot be obtained. Rollback operation for Process ID 61
  is not in progress.

Just looking for options on what I can do/try next. Thanks.


texts: Alter Table for Multiple Databases (same table name)

texts:
Is there a good way to Alter a table (add a column) for multiple databases (think like 50 or so). Or is it pretty much manual for all of them?

the Tables are all the same and the column added will be the same as well.

Thanks!


texts:
I'm running a Postgres 9.1 database on a Debian Squeeze server. It's a paravirtualized XEN guest system with 4G RAM and 1 CPU core, based on a i7-2600 host system with Linux software-RAID1.

I'm trying to get a feel for how fast/slow my setup is, and am running pgbench. This is the command I've been running to initialize pgbench:

pgbench -i -s 100 -U jboss -p 5432 -h 127.0.0.1 project2_core

The first 5000000 tuples run by very quickly, but then it gets slower and slower. Now, it displayed

10000000 tuples done.

for a very long time (10 minutes or so) with no CPU load, and also RAM is not an issue. Only then, it continued with

set primary key...

and some NOTICEs about primary keys.

My question: is this runtime to be expected, or must I suspect there's something wrong with my system?


texts: How do I configure MySQL to store specific entire InnoDB tables on a different drive in Windows ( Server )?

texts: After skimming the MySQL ( 5.5 ) docs on Partitioning, that seems to deal with breaking parts of the same table off onto other drives. I'm struggling to find information on how I pinpoint an incessantly used table in an application and throw it onto its own drive.


texts: Is there a difference between text_pattern_ops and COLLATE "C"?

texts:
If I have a text column name with a collation, for example tr-TR, and I run a query
SELECT * FROM t WHERE name LIKE 'a%'

then this will do a seq scan.
If I now create an index
CREATE INDEX ON t(name text_pattern_ops)

the above query will become a bitmap scan. But there is another technique that will achieve the same result:
CREATE INDEX ON t(name COLLATE "C")

Are those approaches completely equivalent or are there differences?


texts:
I'm not very experienced with SQL Server, so maybe I'm missing something

My situation is as follows:

Session 1 runs a CREATE TABLE (or other CREATE statements) with autocommit off and the CREATE is not being comitted.
Session 2 runs a sp_table statement but hangs as long as session 1 is not committing the DDL

The scenario where this happens is developers working on the database. Some of them browsing the tables, some of them doing DDL. If one user forgets to commit the DDL all other sessions that want to list the tables are blocked. Note that the sp_tables is issued e.g. by the SQL client (through the JDBC driver API), so it's not something that can be changed.

The database I'm working with has snapshot_isolation enabled and the isolation level is set to read committed (SET ALLOW_SNAPSHOT_ISOLATION ON and SET READ_COMMITTED_SNAPSHOT ON)

My assumption was that these settings should make SQL Server behave better with regards to locking in concurrent sessions (e.g. like PostgreSQL and Oracle where SELECTs are never blocked by any writer) - but apparently this is not the case.

So, is there any way to make SQL Server more friendly against concurrent read/write situations with regards to DDL? (apart from submitting DDL only in auto-commit mode).


texts: Are resource_semaphore_query_compile waits causing 100% CPU on server?

texts:
Of late, the cpu usage on the db server is consistently at 100%. Even at 2 in the night when there is almost no traffic on the website and the load is negligible.

While trying to find the root cause, I see a lot of waiting tasks with wait type resource_semaphore_query_compile? All of these have started appearing lately in sys.dm_os_waiting_tasks

0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E9E28  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E8F28  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E94C8  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x008DC988  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x008DD798  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E9C48  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x594364D8  NULL    NULL    resourceWait
0x006E9978  97  0   0   RESOURCE_SEMAPHORE_QUERY_COMPILE    0x027AAAC0  0x006E8C58  NULL    NULL    resourceWait

Why is this happening all of a sudden when nothing has changed? Also, the server 'self healed' itself yesterday, but the problem is back today? How to debug and fix this


texts: mysql -> show changes in fields between the entries

texts:
I have a table that keeps records of account settings. When someone (administrator) makes a change to an account, a new entry gets recorded for that particular account at that time (updatetime). The changes in the record set are updatetime, operator and any settings that were changed (setting1, 2, 3 or any combination of them).

CREATE TABLE settingslog(
    updatetime DATETIME,
    account VARCHAR(20),
    administrator VARCHAR(20),
    setting1 VARCHAR(20),
    setting2 VARCHAR(50),
    setting3 VARCHAR(20)
);

INSERT INTO settingslog (updatetime, account, administrator, setting1, setting2, setting3)
    VALUES
('2017-05-02 15:00:25', 'account1','admin2','enabled','enabled','enabled'),
('2017-05-02 14:00:13', 'account1','admin1','enabled','disabled','enabled'),
('2017-05-02 13:10:11', 'account1','admin1','enabled','enabled','enabled');

The desired output would be:

'2017-05-02 14:00:13', 'account1', 'admin1', 'setting2' changed from 'enabled' to 'disabled';
'2017-05-02 15:00:25', 'account1', 'admin2', 'setting2' changed from 'disabled' to 'enabled';

Thank you all in advance for your help! It is much appreciated.


texts: Howto create useful dacpac versioning along with SSDT deployment?

texts:
It took me almost one day to go through lots of articles and blogs to realize check-in driven continuous integration with SQL Server Database Projects (SSDT) using TFS and msbuild. Now once this is working properly, I would like to introduce versioning.

The database project's dacpac properties allow us to fill in a version in the format "x.x.x.x" that is getting published with the dacpac to Sql Server and can be queried there using:

select * from msdb.dbo.sysdac_instances_internal

This is nice but I wonder how to create something more useful and practical than a manually editable text for the version number? Of course the version number will never change as long it's up to developers to adjust it in the database project's properties...

Remembering how assembly version for example in c# project works, there is some magic increment possible by defining something like

  "1.2.."

including the build number. How can this be done?


texts: Upgrading SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3

texts:
We need to upgrade our production SQL Server 2012 Enterprise SP1 (11.0.3000.0) to SP2 or SP3. So which one would be more stable out of those 2 service packs and also how could this be achieved with a detailed step if possible.

Before upgrading the Production Server directly, we plan to upgrade our test servers which have SQL Server 2012 Enterprise 11.0.2100.60 version on it.


texts: what is the right way to restart a mariadb galera cluster?

texts:
I am trying to restart cluster. I have two problems in stopping cluster and starting cluster

In stopping cluster, I follow the instruction https://software.mirantis.com/reference-documentation-on-fuel-folsom/galera/

For safe, I stop replication on node by set global wsrep_on='OFF'
and then, I cannot stop it gracefully. I must kill it. It is not clean.

In starting cluster, I start the first node by service mysql start --wsrep-cluster-address=gcomm:// I am not sure it is the right way because it create new cluster.

The instruction is in https://mariadb.com/kb/en/mariadb/documentation/replication-cluster-multi-master/galera/getting-started-with-mariadb-galera-cluster/#state-transfer-failure not available to me.

What is the right way to me ?


texts: Subquerying results in no row returned although records exist

texts:
I have two tables, namely Employees and Payments. Below is the contents of the two:

The Employees table

 EmpID |EmpName
 ------ --------
   113    Harry

The Payments table

EmpID | Amount | Period | PaymentType | St
------------------------------------------
------------------------------------------
113 |  100   | 201207 |    1        |  1
------------------------------------------
113 |  70    | 201206 |    1        |  0
------------------------------------------
113 |  120   | 201207 |    1        |  0
------------------------------------------
113 |  200   | 201207 |    1        |  1
------------------------------------------
113 |  90    | 201207 |    2        |  1
------------------------------------------

Here's my query:

    SELECT EMP.EMPID ID, PAID_TOTAL,PAID_CURRENT,PAID_PAST,PAID_OT
  FROM EMPLOYEES EMP
  , (
      SELECT EMPID,SUM(AMOUNT)PAID_TOTAL
      FROM PAYMENTS
      WHERE PERIOD<='201207' AND ST=1
      GROUP BY EMPID
    )
    PY_TOTAL, (
      SELECT EMPID,SUM(AMOUNT)PAID_CURRENT
      FROM PAYMENTS
      WHERE PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1
      GROUP BY EMPID
    )
    PY_CURRENT, (
      SELECT EMPID,SUM(AMOUNT)PAID_PAST
      FROM PAYMENTS
      WHERE PERIOD<'201207' AND PAYMENTTYPE=1 AND ST=1
      GROUP BY EMPID
    )
    PY_PAST, (
      SELECT EMPID,SUM(AMOUNT)PAID_OT
      FROM PAYMENTS
      WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
      GROUP BY EMPID
    )
    PY_OT
  WHERE
    (
      EMP.EMPID=PY_TOTAL.EMPID OR EMP.EMPID=PY_CURRENT.EMPID OR
      EMP.EMPID=PY_PAST.EMPID OR EMP.EMPID=PY_OT.EMPID
    );

What I would expect to get is:

  ID | PAID_TOTAL | PAID_CURRENT | PAID_PAST | PAID_OT
  113     460          300             70         90

But no row is returned from the above query.


texts: How do you ensure accuracy of your query

texts:
I am have a table with about 100,000 rows.

Lets suppose the primary key is some sequence of numbers like a barcode for example.

I am doing checks like

SELECT count(*) FROM TABLE WHERE LEFT(Barcode, 1)  is 0

I have about a 100 more rules to implement. They should ALL return 0 rows. But how do I ensure that they are returning 0 rows because the data is in fact correct OR that it is returning 0 rows because of a poorly written query.

I know that all my queries should return 0 since there are constraints that prevent bad data from getting in the first place but this is still needed by the company.


texts: View additional relevant keywords used for query expansion match queries [MySQL]

texts:
Using mysql's match against a fulltext index with query expansion, we can do blind query expansion with
WHERE MATCH (matchField) AGAINST ('keyword_to_expand_on' WITH QUERY EXPANSION);
And this returns a set of results for the expanded query. I can't work out if there's a way to see what keywords were actually used were. I'm keen to look at what the most commonly used words in the resultset for the query are.
Is there a way to see what the additional relevant keywords mysql decides are useful for the 'expanded' query are? If not, does anyone know a way you can query to get the most commonly indexed words (ordered by occurences) in a resultset - maybe querying the fulltext index directly?


texts: How to select specific value from a table that has multiple values for a record in primary key table in PostgreSQL?

texts:
I have three tables as follows (with sample data):

CREATE TABLE users (
  id int PRIMARY KEY,
  login varchar(20),
  organization varchar(20)
);
CREATE TABLE rights (
  id int PRIMARY KEY,
  name varchar(20)
);
CREATE TABLE user_rights_rel (
  uid int REFERENCES users,
  rid int REFERENCES rights,
  PRIMARY KEY (uid, rid)
);

-- 1-5  'A'
-- 6-10 'B'
INSERT INTO users VALUES
  (1,  'Smith',   'A'),
  (2,  'Jane',    'A'),
  (3,  'Katrin',  'A'),
  (4,  'Todd',    'A'),
  (5,  'Ervine',  'A'),
  (6,  'David',   'B'),
  (7,  'White',   'B'),
  (8,  'Anderson','B'),
  (9,  'Randi',   'B'),
  (10, 'Polak',   'B');

INSERT INTO rights values
  (9,'Contact'),
  (62,'Download'),
  (59,'Reviewer'),
  (32,'High Reviewer'),
  (64,'Read Only'),
  (66,'Data Entry');

INSERT INTO user_rights_rel VALUES
  (1,9),(1,64),(1,59),(1,62),
  (2,64),(2,59),(2,62),(2,66),
  (3,66),(3,62),(3,9),
  (4,66),(4,64),(4,9),
  (5,62),(5,64),(5,9),
  (6,9),(6,64),(6,62),
  (7,64),(7,62),
  (8,66),(8,9),
  (9,66),(9,64),(9,9),(9,32),
  (10,62),(10,64),(10,9);

Now I want to write a query based on the below criteria:

If a user has Reviewer or High Reviewer right along with other rights, it should be counted as Reviewer.
If a user has Data Entry right along with the other rights(except Reviewer and High Reviewer), it should be counted as Data Entry User.
If a user has Read Only right along with the other rights(except Reviewer, High Reviewer and Data Entry), it should be counted as Read Only User.

As per the above criteria, my desired output is:

Organization        Reviewer     Data Entry        Read Only
   A                    2            2                 1
   B                    1            1                 3

Organization A has 2 Reviewers (Users 1 and 2). If a user has Reviewer right then we count it as Reviewer user and we ignore other rights.

Organization A has 2 Data Entry users (Users 3 and 4). If a user does not have Reviewer and High Reviewer rights then we look for Data Entry right, if it has Data Entry right then we count it as Data Entry user and ignore other rights.

Organization A has 1 Read Only user (User 5). If a user does not have Reviewer,High Reviewer and Data Entry rights, then we look for Read Only right, if it has Read Only right then we count it as Read Only user and ignore other rights.

The priority should be as follows:
1. Reviewer (if a user has it, count him/her as Reviewer and ignore other rights).
2. Data Entry (if a user has it, count him/her as Data Entry and ignore other rights).
3. Read Only (If a user has it, count him/her as Read Only and ignore other rights.)

Any help is appreciated in advance.


texts: OFFSET X ROWS FETCH NEXT 50 ROWS ONLY not using indexes

texts:
I have a query that is using OFFSET X ROWS FETCH NEXT 50 ROWS ONLY. If X is 160745 it uses the index, if X is 160746 it does not. There are more than 400000 records. With the index, it takes about 2 seconds to run, without the index it takes ~30 seconds.

We are using Azure S0

I know I can use query hints, however is there another option? Is there something in Azure that I can set to improve this? Any ideas would be appreciated.

**Note: assume all columns are no bigger than nvarchar(255) and only 6 columns.

Below is the query:

SELECT *
FROM [TableName]
ORDER BY [ColumnName] ASC
OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY


texts: High Availability for MariaDB and Postgres : replication or active-passive (SAN)

texts:
During the last weeks I did a lot of research on High Availability for Postgres and MariaDB. Both by my own research and also recommended by vendor's, replications seems to have more advantages.

Some benefits as using a standby node for backup; scaling; the fact it is simpler (no need for storage replication) and the flexibility...

However; our own DBA's don't like replication. Our Oracle DBA mentions replication is only useful in addition to clustering.

Is replication a valid (or even better) alternative for active-passive clustering? What do most new HA implementations use?

-- a Linux architect


texts: Update does not working while select returns right results

texts:
I am trying to execute the query below:

UPDATE(
  SELECT Ord.Completed
  FROM Product P
  INNER JOIN Order1 Ord
  ON Ord.OrderID = P.OrderID
  WHERE EXISTS(
          SELECT *
          FROM Delivery D
          WHERE Delivery.Delivered = 0 AND P.ProductID = Delivery.DeliveryID
        )
)
SET Completed= 'NO';

If I run the SELECT part only, without UPDATE it returns the right results. When I try to execute the UPDATE query I get

"ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:
*Action:
Error at Line: 2 Column: 250"

Any ideas?


texts: SQL Server 2008 posibilities of having views from one database in another database?

texts:
I have a vendor database from I want to extract and transform certain information. As I dont want to change the structure of the original database due to licensing I therefor wondering if I can put together views from the vendor database but storing those in a second sql server 2008 database together with some other replicated and transformed data. The second database is supposed to live beside the first one on the same server.

Is it possible to have views like this and if so, do I loose a lot of performance when querying the views?


texts: How can I backup/dump MYSQL database with foreign key constraints?

texts:
I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship.

I am taking the dump with following command:

  mysqldump -u root -p --no-data --compact db_doctor > db_doctor_no_data.sql

What am I missing?


texts: How to set mariadb read_write for some specific tables while starting as read_only=1

texts:
I am trying to set a mariadb slave which is exact replica of master db. As per the suggestion I start my slave with command --relay-log=mysql-relay-bin" "--log-slave-updates=1" "--read-only=1" . But as I use my slave db as a stage server of my website, I need some tables like cache_* writable to make my stage server work perfectly.

Can you please help me if it is possible or not ? If not then is there any workaround for it ?


texts: High amount of Read Misses and Pages To Be Flushed

texts:
I am running a mysql database backend for a Moodle installation, and after a few months performance really starts to suffer (Up to 30 seconds for some pages to load). Under investigation in the InnoDB buffer pool, I found that the buffer pool size seemed to be correct (innodb_buffer_pool_wait_free = 0). However, I also found that I have an exceedingly high percentage of Read Misses (52%) and what seems like a rather large amount of Pages To Be flushed (31 million). I'm currently running the slow query log, but the lag on a page loading seems like too much to be from simply an unoptimized query.

I haven't been able to find any explanation of why those could both be so high. Does anybody have an explanation for why Read Misses and Pages To Be Flushed would have those results?

Update:
I am restarting the servers on a weekly basis during a scheduled down-period. I still cannot imagine why this is getting so large. Is there no auto-flush mechanism built-in?


texts:
Delete Duplicates Leaving One of Two Identical Records

One of my MySQL database tables contains four BLObs in each row. When it was first set up the table had no keys. However when I now try to add a Primary Key (PK) I find I cannot do so as one of my people ran the same add twice. As a result I now have nearly a thousand duplicate records in over 5000 total records. Having Googled and searched here for a bit there seems more than one way of removing duplicates but which is the best one to use here?

Thank you...


texts: How do I get access logs from SQL Server database

texts:
I am in the process of carrying out system reviews for some systems powered by SQL Server. IS there a way of getting the access logs to these systems databases spanning a period of 6 months starting from January 2020 to June 2020?
Is there a specific query that I can run to extract for me access logs on a given system FROM a given date TO a certain date?


texts: Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?


texts: ODBC from Win10 32bit client only connects using IP address, but not using hostname

texts:
I'm experiencing a pretty strange problem.

We're using a MS SQL based quality assurance software. The database is hosted on a MS SQL 2016 Std. server running on a Windows Server 2012 R2 VM.

Our clients are a mix of mostly 64bit Windows 7 and Windows 10 clients.

In order to connect a client to the SQL server we have to create an ODBC connection using the odbcad32.exe situated at c:\windows\syswow64\ on 64bit clients or under c:\windows\system32 on 32bit machines.

Now, one of those Windows 10 32bit clients straight out refuses to connect to the SQL server using the hostname.
When adding the connection, I'm using the "SQL Server" ODBC driver version 10.00.17763.01, the same driver which works without any problems on 64bit Windows 10 clients.

I can select the SQL server instance and enter the appropriate SQL user name and passwords.
The connection however times out with the following, unfortunately German error message:

Manually entering the ip address of the SQL server allows me to connect to the SQL server instance without any problems.
Since the license of the software is linked to the hostname of the SQL server which hosts the database, I can't login afterwards...I need to connect to the database using the hostname and not the ip address.

Unfortunately, this is the only 32bit Windows 10 client in our environment, so I can't say if there is something wrong with this particular machine.

Anybody ever stumbled across a similar problem? Is there perhaps a dedicated ODBC SQL server driver for 32bit Windows 10?

Thanks in advance,

Dominik


texts: I would like to get a unique based on email address (MySQL)

texts:
I can work on basic queries and have been having a bit of trouble the way Wordpress stores their database information. The below query I have been using to download all form submissions for contests that we run. I usually download the results and then sort what I need to using excel or google sheets. Our last bunch of contests have had a lot of submissions, and I would like to take the following query and take it one step further :

SELECT b.id, b.date_created, a.lead_id, a.form_id,
   max(case when a.field_number = 13 then value end) as FirstName,
   max(case when a.field_number = 14 then value end) as LastName,
   max(case when a.field_number = 15 then value end) as Address,
   max(case when a.field_number = 16 then value end) as Address2 ,
   max(case when a.field_number = 17 then value end) as City,
   max(case when a.field_number = 18 then value end) as Province,
   max(case when a.field_number = 19 then value end) as PostalCode,
   max(case when a.field_number = 20 then value end) as Country,
   max(case when a.field_number = 4 then value end) as PhoneNumber,
   max(case when a.field_number = 3 then value end) as EmailAddress,
   max(case when a.field_number = 44 then value end) as Question,
   max(case when a.field_number LIKE 42.1 then value end) as ClientNewsletter,
   max(case when a.field_number LIKE 27.1 then value end) as LocalNewsletter
FROM wp_rg_lead_detail a
LEFT JOIN wp_rg_lead b
ON a.lead_id = b.id
WHERE a.form_id = 45
GROUP BY a.lead_id;

This gets me my full list of entries. I would like to be able to generate three more results.

This current result I would like to get unique entries by EmailAddress

The two new lists I would like are based on whether ClientNewsletter is selected, as well as LocalNewsletter.

Any help is appreciated.


texts:
MySQL CONVERT_TZ returns null, if timezone value is in plus.

example:

select CONVERT_TZ(now(),SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5),'+00:00');

it returns perfect time 2015-12-16 10:32:19 when the time zone is in minus value. The same is not working when the time zone value is in plus. For an example @@session.time_zone returns 05:30. If I concat plus manually, it is working. Please let me know, if any alternative solutions are available.

Manually concatenated code:

select CONVERT_TZ(now(),concat("+",SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5)),'+00:00');

Thanks in advance.


texts: Efficient Database schema for multi-lingual content?

texts:
Assume I have a table for blog posts, posts, and schema as

id
author
title
content

and each post can belonged to some categories

id
name
descrtiption

So a blog post can belong to multiple categories.

Obviously title and content in posts, and name and description in categories can be translated to different languages.

So, given the existing db schema design, what would be the most efficient and reasonable way to extend the support for a multi-lingual application?


texts: How to automatically fix Orphaned Users with PowerShell and SQLCMD

texts:
I'm trying to properly capture output from SQLCMD in SQL Server 2012.

Here is the PS script:

$dbname = "MyDB"
$InstanceTarget = "localhost\SQLEXPRESS"

$FixOrphansSQL=@"
WITH login_CTE (name, type, sid)
As
(
Select name, type, sid from sys.database_principals where type = 'S'
)
select 'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from sys.server_principals a JOIN login_CTE b ON a.sid <> b.sid and a.name = b.name COLLATE Latin1_General_CI_AS
GO
"@
$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname
sqlcmd -S $InstanceTarget -Q $RepairOrphansSQL -d $dbname

If I run this

sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname

it works and the result is the script commands (on separate lines) to fix the orphaned users – but with some extra characters:

(lots of dashes -----------------)
ALTER USER InsightETLUser WITH LOGIN =InsightETLUser
ALTER USER APSDT_CDLinkedServerUser WITH LOGIN =APSDT_CDLinkedServerUser

(2 rows affected)

When I run

$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname

nothing gets captured into $RepairOrphansSQL.


texts:
I am trying to create an application for monitoring MySQL,in my application there's a part of  monitor DISK STORAGE, so after more research I have not found how to calculate :

used and free size of tablespace,
used and free of data files,
used and free of log files

Anyone know how to calculate their sizes or someone can give me articles or books can help me.
And Thanks.


texts: How can you close or flush a binlog file on a remote server

texts:
I am replicating my binlog files to a secondary server using the mysqlbinlog --stop-never --read-from-remote-server flags.  If the primary database server is compromised I want to replay the binlogs on the remote server, but I'm having trouble with the "current" binlog.

When using mysqlbinlog to convert it to SQL I always get an error like:

ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 2682, event
_type: 2
ERROR: Could not read entry at offset 4426095: Error in log format or read error

I'm assuming that this issue is related to the fact that this binlog file hasn't been closed or flushed on the main database server.  I do not have this problem on the file after running flush logs on the main database server.

So, my question is this:  If the main database server is compromised and I'm unable to run flush logs, only having access to the binlog files that were replicated to a remote server.  How can I clean up,  close or flush that binlog that was open at the time of a DB outage event?


texts:
I have a 2 docker setup - one container with a MysqlDB on it and another with a web server. Both are on the same machine.

The db container has it 3306 port exposed though the hosts 3306 port (argument -p 3306:3306). This container is reachable (I can log into the mysql database) both through the host machine and outside, but for some reason, the web server container fails to connect to it.

I have ssh on the container with the web server. When I run:

nmap -p 3306 host.machine.ip.address

on it, I get:

PORT     STATE    SERVICE
3306/tcp filtered mysql

and when I actually try to log in I get:

mysql -h host.machine.ip.address -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'host.machine.ip.address' (113)

Can somebody point me to where the issue might be?


texts: Revoke delete from table for database owner

texts:
I am trying to revoke the DELETE privilege on a table for a database owner.

I execute the command:

REVOKE DELETE on my_table FROM database_owner;

But when querying this via

SELECT has_table_privilege('users','delete');

I get true returned and am able to continue deletion.

How can I revoke deletion from this table?

Thanks.


texts: Predicate pushdown on view to optimize query?

texts:
I have a view that references a fact table Cost with 300M records. That view also has a couple joins. The fact table has a clustered index on Date and the view definition is something like this:

select *
from FactCost f
INNER JOIN SomeOtherTable b on a.id = f.id
where f.Date < getdate()-7

If I query the view like below, the optimizer first processes the predicate within the view and then the date in my query. So it actually reads 300M records and returns only 1 million. I can't figure out what kind of workaround I can try to optimize this.

SELECT
              *
    FROM [dbo].vwFactCost
    WHERE Date >= '2011-07-01'
        AND Date <= '2011-07-31'

see below

EDIT

:

I encountered this other scenario where the solution was unintuitive.

table: FactRegistration with 300m records, 30 columns,  RegDate is date column

below is view definition:

SELECT <columns>
FROM FactRegistration fr
WHERE FR.RegDate < CAST(DATEADD(DAY,-7,GETDATE()) AS DATE)

query:

SELECT <columns>
INTO #tmp
FROM edw.dbo.vwDemo_slow fr
WHERE fr.RegDate >= CAST('20140501' AS DATE)
    AND fr.RegDate <= CAST('20140531' AS DATE)

Results in this plan (https://gist.github.com/gflores1023/f0f0089315841d21ab072837cf12145d):

If I change the view definition to use this WHERE clause instead:

WHERE FR.RegDate < CAST(CAST(DATEADD(DAY,-7,GETDATE()) AS DATE) AS DATETIME)

I get a much better plan (https://gist.github.com/gflores1023/e3904609c98babbbbc646eaec76ebba4):

Running SQL Server 2016 SP1


texts:
I'm developing ETL from a file where dates are expressed in the format "DDMMYYYY", example "31012016".

I'd like to grab this string and pass it directly to the SQL text where data will be inserted. I don't want to parse it into C# DateTime to then format it to "YYYY-MM-DD" so that SQL Server parses it once more.

As I do currently, the date string is parsed once by .Net, then formated by .Net and printed in the SQL code, then parsed again by SQL Server. I want to grab the string from the flat file and place it directly into SQL code without any conversion.

SQL Server recognizes "YYYYMMDD", but it fails for "DDMMYYYY" with message:

  Conversion failed when converting date and/or time from character string.

Example:

DECLARE @datevar date = PARSE('31012016' as date using 'pt-BR');
SELECT @datevar;

Is there any way to make it work?

I tried PARSE('31012016' as date using 'pt-BR') but raised exception. TRY_PARSE returns null. Also tried CAST and CONVERT (Transact-SQL) but there doesn't seem to exist this style.


texts: Need to take backup from other server on network using AutoMySQLBackup

texts:
We have two servers namely serverA and serverB. I need to take mysql database backup of serverA staying at serverB. The tool I need to use is "AutoMySQLBackup".

I got the link for downloading it:

https://sourceforge.net/projects/automysqlbackup/

Extracted the tar and changed the config file in /etc/automysqlbackup/automysqlbackup.conf as per my need.

As I run automysqlbackup, I get the backups on daily basis though there are warnings:

###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES
mysqldump: [Warning] Using a password on the command line interface can be insecure.

But, I need to take the backup of remote server and don't find the way to do that using this tool. I tried using :

ssh serverA@ip-address | automysqlbackup

but nothing is happening.

Note: I can't reply to answers so i am mentioning it here. I need to get the backup directly from serverA to serverB with the help of automysqlbackup like the way mysqldump or someother tool does.

mysqldump --host 192.168.1.15 -P 3306 -u dev -pmjQ9Y mydb > mydb.sql

Is there way to pipe automysqldump with ssh?
No scp,rsync.
I have access to certain database in serverA while accessing from serverB.

If anyone of you have used this tool, then please help me.


texts: fighting filter (nested loops) execution plan from Oracle

texts:
My query is:

SELECT category, count(*)
FROM large_data_table t
WHERE
(t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 1))
OR
(t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 2))
GROUP BY GROUPING SETS ((category), ());

The resulting execution plan is like this:

SELECT STATEMENT
  SORT (GROUP BY ROLLUP)
    FILTER
      large_data_table  TABLE ACCESS (FULL)
      SMALL_TABLE  TABLE ACCESS (BY INDEX ROWID)
      SMALL_TABLE  INDEX (RANGE SCAN)
      SMALL_TABLE  TABLE ACCESS (BY INDEX ROWID)
      SMALL_TABLE  INDEX (RANGE SCAN)

Where FILTER is, essentially, NESTED LOOP JOIN which never finishes as NESTED LOOPS often do on large tables.  (the two subqueries on the small table return, say, 100 rows each).
I am looking for a way to tell Oracle to NOT use the stupid FILTER here.  I've tried sticking USE_HASH hint everywhere including some places I'd rather not talk about now....
GROUPING SETS seem to play a role in this, but getting rid of them is very difficult here.  The one reasonable thing that works is to replace OR with a UNION, but it doesn't cover all cases either.


texts: Why should I install both 32 and 64 bits Oracle provider for the same machine?

texts:
I needed to read an Oracle DB from SQL Server, so I had to install the Oracle provider (ORAOLEDB.Oracle).

My machine:

System: Windows Server R2 2008 Enterprise, 64 bits (SP1).

RDMS: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

However, at the beginning I installed just the 64 bits ODAC version for Oracle provider but it did not work. Finally I got it done by installing the 32 bits version, just how is said here.

Then I need to replicate the installation in production, I don't want to install both if is not required, so, I want to know why I should use both.


texts: Mysql logs select but not update statements

texts:
I'm trying to find out what update query a 3rd party web app is running on a mysql v5.6 database. I thought I would be able to see this in the general.log file, but nothing useful is being printed.

With logging turned on, this is all I see when I update something using the web interface.

171120 16:17:59     2 Query     BEGIN
                    2 Query     COMMIT /* implicit, from Xid_log_event */
1020172 Query   /* PID: 113227; URI: /ajax/customer/change_status.json.is_blocked.1.is_exported.0.prov_term_date..perm_term_date..restore_date..lift_suspension_date..tz_name.Europe.2FPrague.i_customer.256.generate_invoice.0.bill_status.O.csrf_token.<token>; User: <myusername>; IP: <myIP>; Realm: admin; Script: /usr/sbin/httpd; Statement: GetACLPermissionsBulk; */

SELECT p.i_acl as i_acl,
       p.access_type as access_type, p.mode as mode,
       p.object as object, p.attribute as attribute
FROM Access_Levels acl, Access_Levels_Permissions p
WHERE acl.i_acl = p.i_acl
ORDER BY acl.i_acl

1020172 Query   /* PID: 113227; URI: /ajax/customer/change_status.json.is_blocked.1.is_exported.0.prov_term_date..perm_term_date..restore_date..lift_suspension_date..tz_name.Europe.2FPrague.i_customer.256.generate_invoice.0.bill_status.O.csrf_token.<token>; User: <my username>; IP: <my IP>; Realm: admin; Script: /usr/sbin/httpd; Statement: GetAllAccessLevelsBulk; */

The field I updated is "blocked", so the commend on the 1020172 query is relevant, but it still doesn't help me.

I must be doing something wrong. How can I make sure the full UPDATE query gets logged?


texts: Unable to deploy single SSIS package into SSISDB when referencing parameters

texts:
I'm attempting to deploy a single package into an Integration Services Catalog using the project deployment model in SQL 2016.

This however is giving me some grief, as I've configured environmental variables, and am referencing these in project variables. It seems the package requires the project to be able to validate its' variables.

Any thoughts on if this is possible? It is looking tougher to control promotions to Production when we have to deploy every package on every occasion.


texts: Restore mysql database with different name

texts:
How can we restore mysql database with different name from mysqldump file.
 I dont't want to open dump file and edit it. Any other better methods?


texts: In InnoDB, how does fuzzy checkpointing's recovery consistency work?

texts:
I've looked the documentation quite thoroughly but still cannot figure out so asking this question.

In InnoDB, I understand that any updates against buffer pool are tracked by redo log, which gets persisted on the disk, and the redo logs are used for recovery in the case of crash.

Occasionally, the MySQL server also flushes the dirty pages of the Buffer Pool.  Under its normal operation, MySQL server only flushed some part of dirty pages, and they call it "fuzzy checkpointing".  Under this procedure, the current Buffer Pool is claimed to be recoverable by reading the content of pages on disk and then applying all the redo logs whose LSN is greater than the last checkpoint.

My question is, how does the MySQL server chooses which dirty pages to flush, and also supports the crash-recoverability?

From some googling, I understood that by utilizing the dirty page's first modification LNS number, one can know which pages should be flushed so that   the checkpoint LNS can be incremented.

But the dirty page with earliest uncheckpointed redo log can also have been modified by the latest transaction and thus have future content compared to the earliest uncheckpointed redo log.  I assume it is very difficult (if possible) to redo from the disk persisted buffer pool pages if those pages includes such future contents.

So question:

How does the MySQL server chooses which pages to flush, and also support crash recovery?


texts: Repair table field: Import/append records from specific backup field if they are not null

texts:
One of my 3rd party applications that writes to my production MySQL database mydb has nulled SOME of the data in one of my fields myfield in table mytable.

I'm trying to import only the NON null records for this field from my backup mydump.sql. The ID field can be used as a unique identifier to make the comparison.

Do I have to import the backup in it's entirety to a temp location, and then build a query/command? Or can I write a query that will also look into an external file? Either way I think the query is a bit above my skill level, so any help will be appreciated.

Ideally, if myfield data in my prod table exists (i.e. not null) then we'll leave that alone, as I'm only looking to import/append myfield data if it's in the backup and not in the prod field.


texts: SSIS package succesfully completes but does not import 100% of records from flat file into table

texts:
I have a fixed width text file. I have created an SSIS package that imports the file into a table in a SQL 2014 server.

When I open the file (with notepad++) i see that it contains 259,735 records.

When I run the SSIS package it only imports 216,610 records. The package finishes and gives a Success output message but I am wondering why it is not importing all the records.

The package was previously working and it would import the 259,735 records but I made a change to the file layout and underlying tables and now I only get the 216,610 loaded.

I am not sure what is the best way to trouble shoot this and can't think of anything that would cause this issue other then maybe a line not being terminated correctly but then I would have probably got a truncation error if that were the case.

my row delimiter is CRLF and it appears in the same position (400) on every line of the file and as I mentioned yesterday I was able to import all 259k records with this same package so I am pretty sure that the file is properly formatted and is not the issue.

Does anyone have any ideas on what can be causing this issue or how I should go about troubleshooting further?


texts: How to find value for specific dates for a process stored in table

texts:
I have a table called as tab where we store all the process running on machine in below format

proc_name     start_date     Duration
proc1         24-Jul-18      0:0
proc2         24-Jul-18      0:0
proc3         24-Jul-18      0:0
proc4         24-Jul-18      0:0
proc1         25-Jul-18      0:01
proc2         25-Jul-18      0:02
proc3         25-Jul-18      0:04
proc4         25-Jul-18      0:03
proc1         26-Jul-18      01:0
proc2         26-Jul-18      02:0
proc3         26-Jul-18      02:0
proc4         26-Jul-18      04:0

I want to see output as per below format:

Proc_name   Date1  Duration(of date1) Date2 Duration(of date2) Date3 Duration(of date3)
proc1       24_jul-18  0:0            25-Jul-18 0:01           26-Jul-18 01:0
proc2       24_jul-18  0:0            25-Jul-18 0:02           26-Jul-18 02:0
proc3       24_jul-18  0:0            25-Jul-18 0:04           26-Jul-18 02:0
proc4       24_jul-18  0:0            25-Jul-18 0:03           26-Jul-18 04:0


texts:
Having difficulty cloning a live DB.  In the table structure i have a number of tables that extend are linked from one another, they are quite big and the names means the tables aren't very close in the listing.

An example of what happens when I mysqldump is the last record in table A has ID 123123 then by the time the dump gets to table Z, the ID's in the live system have jumped to 123234.  The knock on effect is that when I try to use my newly cloned DB in my development environment I get a lot of duplicate ID errors.

A few questions, is there a way to lock groups of tables for a dump (don't want to lock the whole db as its ~5GB and its too long to be offine) Or is there a nice way to clean up orphaned IDs that get created this way.


texts:
I have this table with data but we need to get all the (unique) rdx and rdy values:

CREATE TABLE public.positions
(
  datetime timestamp without time zone NOT NULL,
  unitid bigint NOT NULL,
  rdx double precision NOT NULL,
  rdy double precision NOT NULL,
  speed integer NOT NULL,
  course integer NOT NULL,
  numsatallites integer NOT NULL,
  hdop integer NOT NULL,
  quality character varying(255) NOT NULL,
  CONSTRAINT positions_pkey PRIMARY KEY (datetime, unitid),
  CONSTRAINT car_fkey_positions FOREIGN KEY (unitid)
      REFERENCES public.car (unitid) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
);

Create index rdx_index on Positions (rdx,rdy);

We tried this query:

SELECT rdx,rdy
FROM Positions
GROUP BY rdx,rdy

But the Query is taking to long (30seconds+).

I have tried to use explain but it doesn't use an index.

How can we speed this query up?

Or is there no possible way because we need to retrieve nearly all the data from the table.

PostgreSQL version: 9.5

Output of explain (analyze, buffers):

Group  (cost=0.43..106482.35 rows=134251 width=16) (actual time=0.044..819.760 rows=995461 loops=1)"
 Group Key: rdx, rdy"
 Buffers: shared hit=971840 read=5464"
   ->  Index Only Scan using rdx_index on positions  (cost=0.43..99769.81 rows=1342509 width=16) (actual time=0.039..597.593 rows=1342509 loops=1)"
        Heap Fetches: 1342509"
       Buffers: shared hit=971840 read=5464"
Planning time: 0.183 ms"
Execution time: 840.152 ms"


texts: Create table with constraint based on another column

texts:
Say a database has the following tables:

movie(id, title, rent_period)
customer(id, name)

I would like to create the following table:

CREATE TABLE rental (
   movie_id int,
   customer_id int,
   rent_date date,
   return_date date,
   FOREIGN KEY (movie_id) REFERENCES movie(id),
   FOREIGN KEY (customer_id) REFERENCES customer(id)
   PRIMARY KEY rental_pk (movie_id, customer_id)
)

Can I make make the return_date field be populated automatically as rental(rent_date) + movie(rent_period)?


texts: SQL Arrays are returned as strings in PHP

texts:
I have a column with type Array, I need to get all the elements in the array, I tried:

SELECT managers_ids::INTEGER[] FROM teams WHERE id = 14

the result is  '{8,9,10}'. How can I iterate on the results when it's returned as a string?

Any ideas?


texts: What does Threads_connected=-1 signify in MySQL?

texts:
I was running the following: mysqladmin -u root -p ext -ri 10 | grep Threads_connected and was kind of surprised to find that Threads_connected could be -1:

| Threads_connected                 | 1            |
| Threads_connected                 | -1           |
| Threads_connected                 | 1            |
| Threads_connected                 | -1           |

What does the -1 signify?

This is on MySQL 5.0.77 on x86_64.

Thanks!


texts: I will be performing a postgres upgrade from 11.5 to 12.5 that will affect production environment. The first step assumes, that the most recent backup of database was taken, before upgrading - we would like to reduce time of this stage, due to performing upgrade in night hours. After unsuccessful research I would like to as if taking a backup made few hours before exact upgrade backup will reduce the time of next backup procedure?


texts: Mysql: Order by boolean value mixed into the results

texts:
Imagine there is a job portal where people can post job offers

Some job offers are sponsored or premium offers that people pay for. These premium offers I want to display randomly in between search results.

Lets say a user searches for
programmer

so I do

select *
from jobs
where description like '%programmer%'
order by created_at

is there a way in mysql to add in random job records that have
is_premium = 1?

lets say, each 3 results, I want to ignore the search term, and add a random record with is_premium


texts: Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

texts:
We are using Oracle 11g, 11.2.0.3.

We know a record exists in a table but a select is not returning it for some odd reason.

The execution plans the general query, not forcing any index, shows that an Index oneIndex is used. No record is returned.
Forcing another index, using the same query returns the missing record.
Setting oneIndex to invisible and back to visible...
Rexecuting the initial query (not forcing any index) does return the record (but somehow, the execution plan is not using oneIndex anymore). Maybe it woke up and found that stats were old ???
Taking the first query again and forcing the use of the faulty index (oneIndex) works fine now.

Facts: Statistics were quite old.

Session Parameter for ORACLE SQL Developer:

ALTER SESSION SET
   OPTIMIZER_FEATURES_ENABLE = '11.2.0.1'
   OPTIMIZER_MODE = FIRST_ROWS_1
   "_HASH_JOIN_ENABLED" = FALSE
   "_PUSH_JOIN_PREDICATE" = TRUE
   "_PUSH_JOIN_UNION_VIEW" = TRUE
   "_COMPLEX_VIEW_MERGING" = TRUE
   "_TABLE_SCAN_COST_PLUS_ONE" = TRUE
   "_ORDERED_NESTED_LOOP" = TRUE
   "_NEW_INITIAL_JOIN_ORDERS" = TRUE
   "_UNNEST_SUBQUERY" = TRUE
   "_INDEX_JOIN_ENABLED" = TRUE
   "_LIKE_WITH_BIND_AS_EQUALITY" = TRUE
   NLS_SORT = BINARY
   NLS_COMP = BINARY
   NLS_NUMERIC_CHARACTERS = '.,'
   QUERY_REWRITE_ENABLED = FORCE
   CURSOR_SHARING = EXACT
   DB_FILE_MULTIBLOCK_READ_COUNT = 0
   OPTIMIZER_INDEX_COST_ADJ = 5
   OPTIMIZER_INDEX_CACHING = 95

The SQL is a bit had to follow since it is produced by a Content Management application. I will not provide it for now.

Q1: Why is an index no longer chosen by the execution plan after
switching to invisible and then back to visible?
Q2: How can Oracle momentarily not see a record when searched from an
index instead of another index?


texts: Database Transfer from PostgreSQL to MySQL

texts: Is there any method to tranfer the data and schema from a postgresql database to mysql?


texts: Capturing Column Names in Dynamic SQL Statement

texts:
I'm in the process of updating some procs made for SQL 2008R2 to 2016 that are using a dynamic result set.

Assuming there is a procedure that always has exactly one result set, but the result set has a dynamic column return each time, is there a way to capture the column names at runtime in tsql without doing something janky?
I'm not worried about data types, just column names.

Thanks!


texts: Is it worth to separate columns into multiple tables for one-to-one relational table

texts:
I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table.

The number of columns would be around 45 and I need to sort data on different columns on different query (one sort per query).

I will be using MyISAM storage engine.

Furthermore, there will be millions of data in the table(s).


texts: Oracle SYS Auditing with Audit Trail to DB

texts:
I have an Oracle Database 11g R2 on Oracle Enterprise Linux. I have enabled SYS auditing as:

SQL> ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile;

SQL> ALTER SYSTEM SET audit_trail=OS SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

The audit information is storing in adump folder. But I want to set audit trail to Database(i-e: in SYS.AUD$). I have tried this:

SQL> ALTER SYSTEM SET audit_trail=DB SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

but after this setting, audit records are not going to SYS.AUD$ but instead these are going to operating system adump folder. So how can I set audit trail to database?


texts: SQL Server : Job not reconnecting to restarted Linked Server

texts:
The problem
To keep two tables across two servers in sync, we have a trigger that adds a change to a MergeTable. Every so often, a merging job runs on SQL Server to push the changes to the Progress linked server.
I was testing connectivity issues when I ran into this problem:

shut down Progress DB
job fails as expected
start up Progress DB
job continues to not connect to Progress DB until manually disabled and re-enabled

I get many multiples of these three messages:

OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server crash likely.". [SQLSTATE 01000]
OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Socket closed.". [SQLSTATE 01000]
OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Unexpected Network Error.  ErrNum = 10054". [SQLSTATE 01000]

What I've tried
I can execute sp_testlinkedserver and get an error, so I can detect WHEN it happens:

Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST".

I attempted to call sp_dropserver, sp_addlinkedserver, and sp_testlinkedserver in quick succession, which does not throw an error, but the job thinks it successfully completes when it didn't.
I'm quite new to SQL/T-SQL. There's no sp_reconnectlinkedserver, and I can't replicate this issue outside the job; running the individual procedures works when the database gets back up. I'm thinking this is a session issue, but I wouldn't know where to begin fixing it.


texts: How to prevent 'table is mark as and last (automatic) repair failed'?

texts:
This is the second time within the last 4 days that one of the tables in my database got corrupted. The is the error that I have seen in my apache log:

  PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 144 Table [TABLE_NAME] is marked as crashed and last (automatic?) repair failed'

I managed to repair it manually using:

myisamchk -r -f $TABLE_NAME

but of course this is not a long term solution.

I need to understand why is this happening, so I can prevent it from happening again in the future.

Total database size is 2Gb, 100 tables, 7.5 million rows.
The table where it happened is the largest in the database: 1.2 million rows, 650Mb.
Server version: 5.5.50
ubuntu 14.04.1
PHP 5.5.9
I checked and there was enough free disk space. About ram, it was not an issue so far.
The host where the db reside is c3.xlarge (8Gb ram)

The database sits in the server harddisk. I am thinking about moving it to RDS, I wonder if that would help.

What do you suggest I should do? Any way to analyze mysql and understand where is the problem?

UPDATE

/var/log/mysql/error.log has this in it:

200213 14:57:21 [Warning] /usr/sbin/mysqld: Forcing close of thread 200315105  user: '[user1]'
200213 14:57:47 [Warning] Warning: Optimize table got errno 28 on [TABLENAME], retrying
200213 14:57:47  InnoDB: Starting shutdown...
200213 14:57:49  InnoDB: Shutdown completed; log sequence number 1983956605556

UPDATE - It is out of memory

found this in my syslog

Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.270767] Out of memory: Kill process 14232 (mysqld) score 32 or sacrifice child
Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.285148] Killed process 14232 (mysqld) total-vm:2447868kB, anon-rss:241432kB, file-rss:0kB
Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744020.934420] init: mysql main process (14232) killed by KILL signal

Like I said before, my server is c3.xlarge with 8Gb RAM. Normal utilization is pretty low. I think there was a backup process that was running, maybe it could have caused higher resource utilization.

Any suggestions how to check further?


texts: What C# type is the correct representation for DB2 UDB boolean?

texts:
The project that I work on uses IBM.Data.DB2 nuget package for its data layer.
I need to call into utl_mail.send_attach_varchar2 stored procedure on the DB2 side. It has a boolean parameter att_inline for which I initially tried to supply DB2Type.Boolean data type. This does not work due to this error when adding it:
{"Specified argument was out of the range of valid values.\r\nParameter name: Invalid DB2 Type enumeration value=1015."}

The value 1015 corresponds to DB2Type.Boolean. I also tried DB2Type.Integer and supplied 0 for the value, or DB2Type.Char and supplied '0', but there is this error that I get in both cases when executing the command:
{"ERROR [22018] [IBM][DB2/NT64] SQL0420N  Invalid character found in a character string argument of the function \"DECIMAL\"."}

Now, I am thinking that I have covered the parameters that are common with SEND procedure because I used it from the same code and it worked. The difference is in the attachment handling, so I am assuming that it is the att_inline parameter that is causing it.
There does not seem to be anything DECIMAL in the command that I supply to DB2Command.Text
"call utl_mail.send_attach_varchar2 (@sender, @recipients, @cc, @bcc, @subject, @message, @mime_type, @attachment, @att_inline, @att_mime_type, @att_filename)"

I tested with the SEND procedure by omitting @attachment and all parameters that follow, and it worked fine, so the command seems Okay to me, unless I am missing something.


texts: Count up number of rows matching criteria in same table

texts:
I'm trying to extract a list of results, with the greatet number of rows found. Basically, we have a table of values, linked with the key link_id_fk. What I want to basically do is look at each link_id_fk value, and then count the number of rows in the table for that record. So if I were to do it manually for each one, it would look like:

SELECT COUNT(*) FROM lsql_Flex_Values WHERE flex_type = "address" AND link_id_fk = 12345

I'm trying this query, but it always returns no results:

SELECT link_id_fk,
    (SELECT COUNT(*) FROM lsql_Flex_Values WHERE flex_type = "address" AND link_id_fk = flex.link_id_fk) AS total
    FROM lsql_Flex_Values flex WHERE flex_type = "address"

What am I doing wrong? I'm sure the veterans will spot it a mile off - but I'm just not seeing it :/


texts: How to create a check between two attributes of two different tables

texts:
I have to create a check to know if the date in Livraison (Delivery in French) is >= Date in Commande (Order in French)

I Hhad some issues on the SQL :

We did it in BonDeLivraison (DeliveryTicket in French):

CREATE TABLE BonDeLivraison (
NumeroBon INT NOT NULL  ,
IDClient INT NOT NULL ,
IDNumeroCde INT NOT NULL ,
AdresseLiv VARCHAR(255) NOT NULL ,
CONSTRAINT un_bdl UNIQUE(NumeroBon),
CONSTRAINT cbdl PRIMARY KEY (NumeroBon) ,
CONSTRAINT FK_KEY_IDClient_BDL
    FOREIGN KEY(IDClient)
        REFERENCES Client (IDClient )
            ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT FK_KEY_IDNumeroCde_BDL
    FOREIGN KEY(IDNumeroCde)
        REFERENCES Commande (IDNumeroCde )
            ON UPDATE RESTRICT ON DELETE RESTRICT,
CHECK (
    (SELECT Date FROM Commande c WHERE IDNumeroCde = c.IDNumeroCde
        AND IDClient = c.IDClient)
        > (SELECT Date FROM Livraison l WHERE
            (SELECT * FROM ContenuBL b WHERE l.IDProduit = b.IDProduit
                AND l.NumeroBon = b.NumeroBon
                    AND b.NumeroBon = NumeroBon
            )
           )
    )
);


texts: moving binary database folder is causing issues with innodb tables

texts:
so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables are opening The innodb tables are listed but when I click on any of them on the left tab (phpmyadmin) I get this error: #1146 - Table 'databasename.users' doesn't exist this would be for the users table. Other innodb tables are having the same issue. I would like to know how to remedy this issue. I logged in to mysql directly through ssh as root, when I do : SHOW TABLES; the table users and the other innodb tables are all listed. When I do SHOW COLUMNS FROM users; I get :

mysql> show columns from article;
ERROR 1146 (42S02): Table 'sitecontent.article' doesn't exist

any ideas please ? (issue resides in innodb tables only!)


texts:
I am developing a webapp for an institute and I designed these tables:

Are these tables normalized?


texts: What is the cost to create Materialized View log

texts:
Recently I ran DBMS_ADVISOR.TUNE_MVIEW and it gives me several lines to

CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."COMPANY" WITH ROWID
CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."BOARD" WITH ROWID
....
CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."CONTROL" WITH ROWID

and the script to create materialized view is pretty simple with multiple joins and conditions:

  CREATE MATERIALIZED VIEW V_SUMBAL_AMT
  BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+1*60/(60*60*24)) WITH rowid AS
  SELECT sb.id as id,
  SUM(c.OPEN_DAY) as open_day, sum(c.OPEN_NIGHT) as open_night,
  SUM(c.CLOS_DAY) as clos_day, sum(c.CLOS_UNV) as clos_night
  FROM company c
  join board b on c.company_id = b.id
  join summaryBoard sb on sb.id = b.summary_id
  join control cl on cl.id = c.control_id
  where cl.ignored <> 1 and c.control_id IN (SELECT cm.control_id from control_manager cm where cm.manager_id = b.manager_id)
  GROUP BY sb.id

I know it looks a little messy, just give an idea about how the view looks like. My question is shall I follow the instruction to create view log? And what is the cost if the dba will never manager the view log so what is the cost and impact to the database?


texts: Document database structure for auditors

texts:
I am DBA for a Microsoft SQL Server 2008r2.
Due to an audition, I need to provide data structure of my database, to people without an explicit IT background.
I guess they would like the traditional format, with the tables as rectangles, with the fields listed inside, and the arrows between them to represent foreign keys.
Is this something that is provided out-of-the-box by Microsoft?


texts: Find the neighbouring polygons of a given polygon in a MySQL spatial table in ver < 5.6.1

texts:
Finding neighbouring shapes in MySQL spatial table made up of ID and 2D geometry (polygon) fields.

I have a table with an ID column and a geometry column (holding a polygon) representing various areas on a map. Given the ID of a particular row, say ID 4, I would like to find every polygon in the table that meets/touches the polygon held at ID 4.

I am aware MySQL < 5.6.1 only uses Minimum bounding rectangle rather than a proper function for this so could accept any MBR of another geometry column that intersects it for example.

I am having a little difficulty starting to write such a query. It would go something along the lines of:

check all rows for MBR_Intersects(some geometry column, geometry column for ID=4)

My SQL is like so:

SELECT * FROM
(SELECT `geometry` as gp FROM geometrytable WHERE `id` = 4) AS TT
WHERE MBRTouches(TT.gp, geometrytable.`geometry`) = 1;

I appreciate I am horribly off the mark in both the above but would really appreciate some guidance on how to tackle this.


texts: Postgres read from master after slave failure

texts:
I'm new to postgres and have setup a master:slave database. Everywhere I look I see slave promoting to master failover, but what I can't seem to find anywhere is what happens if the slave fails.

How do I make the queries select the right master/slave db incase the slave has gone down? I'm using pgbouncer for my connection manager for each instance. Should I be using something different?


texts: MySQL get a lock to update certain rows and prevent other sessions to read that row

texts:
my question is:
How can I get a certain row to update and on the other side, block that row to be selected by other sessions. I don't want a simple SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE. I want the second session not to be able to select the row at all. Maybe some isolation level, I tried them all but nothing.
Thanks


texts:
(MySQL Version 5.5.58)

I have two tables articles and comments. articles has 1K rows, while comments has 100K rows:

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` text COLLATE utf8_unicode_ci,
  `type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` mediumtext COLLATE utf8_unicode_ci,
  `result` varchar(255) COLLATE utf8_unicode_ci,
  `article_id` int(11) DEFAULT '0',
  `user_id` int(11) DEFAULT '0',
  `spec_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_comments_on_result` (`result`),
  KEY `index_comments_on_user_id` (`user_id`),
  KEY `index_comments_on_article_id` (`article_id`),
  KEY `index_comments_on_spec_id` (`spec_id`),
)

I have a "select" query from those tables:

SELECT articles.*,
       count(DISTINCT CASE
                          WHEN s.result = 'OK' THEN s.user_id
                      END) user_ok,
       count(DISTINCT s.user_id) user_cnt,
       count(CASE
                 WHEN s.result = 'OK' THEN 1
             END) com_ok,
       count(s.id) com_cnt,
       bit_or(s.result = 'OK'
              AND s.user_id = 1) cur_user_ok,
       bit_or(s.user_id = 1) cur_user_all
FROM articles
LEFT JOIN comments s ON s.article_id = articles.id AND s.spec_id IS NULL
GROUP BY articles.id
ORDER BY articles.id ASC

This query will finish in ~100ms normally.

However, after 2~3 days of uptime, the query time will suddenly increase to about 200 seconds. This will usually last 4~6 hours before it gets back to its normal state (~100ms). If I restart MySQL in the period, there is a chance (about 50%) to solve the slowdown (temporary).

I have a backup routine run 3 times a day, which mysqldump all databases. I noticed that the moment the query time increases is almost always after the backup routine finished. But if I disable the backup routine, it still occurs.

The memory usage (RSS reported by ps) always stayed at about 500MB, and didn't seems to increase over time or differ in the slowdown period. Yet Handler_read_next increases a lot in the slowdown period: normally it is about 80K, but during the period it is 100M. SHOW PROFILES indicates that most of the time are spent in "Copying to tmp table".

Here's my my.cnf settings:

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
key_buffer_size = 256M
table_open_cache = 2000
sort_buffer_size = 4M
read_buffer_size = 1M
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
tmp_table_size = 128M
max_heap_table_size = 512M
query_cache_size = 0
query_cache_type = 0

What are the possible reasons?


texts: MariaDB or MySQL @@SESSION.tx_read_only always returns 0

texts:
I'm trying to figure out possible "read only" transaction setup leak between pooled connections (on the same physical connection) in Java application which lead me to a question "How can I find out what the current transaction read-only-ness really is?"
I'm using versions 10.0.28-MariaDB-wsrep and 5.7.31-0ubuntu0.18.04.1 and whatever I do, when I issue the statement:
SELECT @@GLOBAL.tx_read_only, @@SESSION.tx_read_only;

It always returns 0 for both values. (I've tried also the non-deprecated variable on MySql, the same result.)
I've read how to start transaction for both databases and even when I get the error Cannot execute statement in a READ ONLY transaction. which is haunting me in the application, the tx_read_only is still 0.
I tried the following in IDEA SQL console:

turn of auto-commit
set TRANSACTION READ ONLY;
START TRANSACTION;
INSERT ... - Originally: no problem on MySQL, failed on MariaDB, I understand the reason, it is documented. EDIT: I tried this fiddle and this one and both fail on the first INSERT. My previous experience might have been caused by unclear session.
start TRANSACTION READ ONLY; -- EDIT: probably not relevant now.
INSERT ... - ended with Cannot execute statement in a READ ONLY transaction. on both DBs.

Any step of the way, tx_read_only was always 0. What may be the problem? What is the right way to ask the DB whether INSERT is allowed before actually trying to do it?


texts:
I have designed a database design for a simple User Management System, this system consist of the following things,

User - A user may be a Vendor / Employee / Buyer.
User - A user may have more than one Address - /delivery/ Billing / Profile / Company Address.
User - A user can have more than one role- Sales Executive/Manager/CEO.
User - A user can access all his roles / needs by using a single login credential (Username / Email address).

keeping in this, I have designed this system. This is my first application, I need to know whether the designed application will meet all requirements and is this good to go in this design?

Any suggestions and ideas regarding this and some tips for best practise. Thanks in advance.


texts: Passing current server name from SQL Server Agent job to PowerShell

texts:
I have a SQL Server Agent job that calls a PowerShell script in one step. And I want to pass the SQL server name and the current job-id to my script.

With the job-id I have no problems. But with the server name...

My PowerShell call looks like this:

.\StopServices.ps1 -ServerName $(ESCAPE_DQUOTE(A-SVR)) -JobId $(ESCAPE_DQUOTE(JOBID))

The job-id works perfect, but the server name is empty.

How can I pass the server name to my PowerShell script?


texts: export data to xml by the use of query to xml then importing it using xpath

texts:
i have use QUERY_TO_XML function in order to export data to xml , after that i want to export this data. this data might contain reserved chars in the xml tags like "' & ..."
ex  electrical ' engineer.
i am using xpath to extract the data into temp table then inserting this data to the receiving table by the use of the temp table.
what to do in order to escape this charcater while using xpath.


texts: Is a relational database with a dynamic number of tables a good design?

texts:
I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records.
This table has a fixed structure, so if there are 5 such entities in my system, there will be 5 different tables with the same schema.
The schema is generic with one of the columns in the schema as JSON for flexibility. I do not expect queries based on the fields in the JSON. I expect the following queries on each entity:

On the auto-increment id primary key column with LIMIT and OFFSET where I need to read X rows from the record with id Y.
On the creation date column with LIMIT X.

I expect thousands of such entities to be created on the fly so in turn there will be thousands of tables in the database.
In future when one of these entities have fulfilled their purpose, the table would be simply deleted.
I expect most of these tables to have not more than 100 rows while there will be a few with at least 1M rows as time goes by.
This design makes data easy to query as my application can determine the table name from the entity name.
Is this a bad design?
Is there a limit to the number of tables in a database in RDBMS (the above design is with Postgresql 11 in mind) keeping performance in mind?
Should I use any different datastore to achieve this other than RDBMS? Any suggestions?


texts:
This is a fairly noob question. I work with an old-school PowerBuilder software (PowerCampus) that has some strange design decisions from the 80s and 90s.

What's perhaps the most important table in the system, dbo.ACADEMIC, has only a clustered primary key made up of multiple columns. I need to be able to SELECT and UPDATE rows on dbo.ACADEMIC via an immutable key such as a GUID, but I also can't alter the table.

There are many reasons I can't modify the structure of this table, but chief among them is that one school tried it and broke the desktop client software.

Here's a simplified sample of ACADEMIC's primary key:

PEOPLE_CODE_ID ACADEMIC_YEAR ACADEMIC_TERM CURRICULUM
-------------- ------------- ------------- ----------
P000011111     1996          SPRING        URBAN
P000022222     2012          SPRING        HCSMGT
P000033333     2002          SUMMER        CHEMIS
P000044444     2015          FALL          SEDUCA
P000055555     1983          SUMMER        POLSCI

Some columns that are part of the key change a lot -- when a student changes curriculum, for example. Integrating with other systems is very hard because of this lack of an immutable id.

Can this be solved by creating another table with a unique primary key plus the identifying columns from ACADEMIC? Then a view could be constructed that would show all the data from ACADEMIC plus the id column; integration projects could use this view.

Proposed custom.AcademicKey table:

id                                   PEOPLE_CODE_ID ACADEMIC_YEAR ACADEMIC_TERM CURRICULUM
------------------------------------ -------------- ------------- ------------- ----------
956EB195-C7C3-4E47-9A1B-0000191B95A2 P000011111     1996          SPRING        URBAN
FE00D2F4-5F5E-45D2-AB63-00002CC92F40 P000022222     2012          SPRING        HCSMGT
C1AF44CD-DDD1-4D46-A19E-0000A841B70F P000033333     2002          SUMMER        CHEMIS
D4E8FC3F-CECC-4195-9924-0000B9BC33A7 P000044444     2015          FALL          SEDUCA
456B16ED-C504-456B-A2E5-00019F244A95 P000055555     1983          SUMMER        POLSCI

Would a foreign key tied to the clustered key on ACADEMIC accomplish this?

Would triggers have to be added to ACADEMIC in order to populate new rows into AcademicKey?


texts:
I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current location. I want to know how long time a vehicle halts in certain location.

Will explain by below table, it is the latest data

id gadget_id  location  submitted_date
   ---------  --------  ----------
1  1          calicut   2012-07-15
2  1          calicut   2012-07-14
3  1          calicut   2012-07-13
4  2          thrissur  2012-07-12
5  1          calicut   2012-07-11
6  1          kannur    2012-07-10
7  2          thrissur  2012-07-09
8  1          calicut   2012-07-08
…          …         …

*I want to know how long time gadget_id 1 halts in position calicut.*

Here we can show that the gadget_id = 1 and position=calicut last 4 data's comes from same position calicut. The next data of the gadget_id=1 is from kannur, so we avoid the data from this id. How to get the count 4 when we give the input gadget_id = 1 and position=calicut

Anybody give the suitable query, expect a single query without joins.


texts: Linked server from MSSQL to Oracle Issue

texts:
Last week we began having an issue with a Linked Server to an Oracle database on one of our MSSQL Servers. If I use the linked server on the MSSQL server it works fine. However, if i try to use it remotely through SSMS it throws the below error. But, if i log in to the SQL Server remotely with SSMS and an SQL sysadm account it does work. Server is on mixed mode and normally we connect using window credentials.

OLE DB provider "OraOLEDB.Oracle" for linked server "*****" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "*******".

This worked fine up until last week when it just seemed to stop working.

The same server on our DR site with the same linked server works fine.

We have checked the provider settings against a working Linked Server and they are fine. We have rebooted the Server. Checked TNSNAMES is matching and correct. I am now completely out of ideas of what to try next and wondered if anybody out there has had a similar problem and has been able to resolve the issue. I could try to reinstall the Oracle ODAC but trying to avoid this.

Thanks for any help or advice you can give.

Richard


texts: Using pgpool and PostgreSQL in differents hosts.

texts: I want to use pgpool but I did some research and I have a doubt, if neccesary install PostgreSQL and pgpool in the same machine? When I did my first configuration and installation in a same host. But this gave me some problems, some time Postgresql services can't start and I want to separate them? It is possible?


texts: How many nodes can you install SSRS on when using basic availability groups?

texts:
I have migrated a SQL Enterprise standalone SSRS installation to a new SQL Standard 2 node Basic AG recently.  I got the expected "scale-out-deployment is not supported error", and I resolved that by removing the old server's information from the keys table in the reportserver DB. I now have just 2 lines in that table, a NULL machine name and Node1.  the report server works perfectly fine on Node1 right now, but when i fail over to node2 I get the "scale out feature is not supported" error when I go to the web service URL link(http://Node2:80/Reportserver), but NOT when i go to the web Portal URL link (http://node2:80/Reports).  That gives me access to the reports fodlers, but when I try to launch a report I get the Scale out error.

If I restore the encryption key, it works fine, despite having already restored the encryption key previously.

Can I not have SSRS installed on both nodes of a basic availability group?  Should it be installed on Node1 only?  If so, will I still be able to access the shared reportserverDB?

This is my first attempt with basic availability groups, I didn't run into this issue at all with my last enterprise install since I was using scale-out deployment.

Just had another thought...  the source DB that the reportserver is pulling data from is in a different AG since basic AG's can only have 1 DB in them.  if that DB is on a different node then what reportserver and reportservertempDB are running on, could that be a problem?  would the source DB need to be failed over as well?


texts: Oracle forms and report 6i compatibility issue

texts:
Please advise,is there any compatibility issues with windows server 2008 64 bit and oracle forms and reports 6i?

I am being asked to buy a new server with 32bit processors by my support provider. Are they saying the truth?


texts: 12.0 Access Database Engine OLD DB Provider Not showing up in SQLEXPRESS 2008

texts:
I am trying to create a linked server to an 32 bit 2010 access database inside of SQLEXPRESS 2008 R2 64bit. Everything I've read has pointed me at the download : Access Database Engine

I've downloaded the 32 bit version (64 bit tells me my office is 32 bit) and installed it; however, the drop down in SQLEXPRESS > Create New Linked Server does not come back with an option for Microsoft Office 12.0 Access Database Engine OLD DB Provider.

I tried createing the linked server via sp_createlinkedserver but when I run a test connection on that server I get the error

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

Does this mean that it found the driver I needed? If so how do I get it to run with the linked server?


texts:
What is the correct way to cleanup WAL postgres? I have a database with more than 100 GB and it haves around 600 GB in pg_wal. Also I have 2 logical replications set up.

Master and Slaves - Postgres 10.

Master and Slaves have commented wal_keep_segments and max_wal_size.

pg_archivecleanup did not work with %r option, then I got the archive name by searching in pg_controldata latest checkpoint's REDO WAL file and deleted the logs, but then one replica stopped with the error

  could not receive data from WAL stream: FATAL: requested WAL segment xxx has already been removed

To solve this I deleted and recreated the replica.


texts:
The project I am currently working request me to name the tables to something like: df_cmn_aum_xx_typ_cur instead of something like courses.

Are there some known standards or best practices to recommend my bosses to abandon this convention?


texts: Can't overwrite an existing Azure SQL Managed Instance database during restore

texts:
We're working on migrating some SQL Server VMs to Azure SQL Managed Instances.  We did an initial backup/restore without a problem:

RESTORE DATABASE [xxx]
    FROM URL = 'https://<account-name>.blob.core.windows.net/<container-name>/<filename>.bak'

After doing testing, we now need to do a fresh restore from our SQL VMs.  We started doing the same process we initially did but when restoring, we got an error:

Msg 1801, Level 16, State 1, Line 1
Database 'xxx' already exists. Choose a different database name.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

So I tried the WITH REPLACE option.  Unfortunately:

Msg 41901, Level 16, State 2, Line 1
One or more of the options (replace) are not supported for this statement in SQL Database Managed Instance. Review the documentation for supported options.

Reviewing the documentation just tells me that WITH REPLACE isn't available on a Managed Instance.  I can't find any documentation on doing an in-place restore/overwrite of an existing managed instance database.

Am I forced to drop and re-create the database?


texts: checksum(newid()) as primary key - what happens if a collision occurs?

texts:
Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). This makes me feel uneasy; the newids are guaranteed to be unique, but by applying the checksum (to a 32-bit integer, presumably for performance reasons?), you have some chance that you get a collision (with 1M rows in the table, I'd put that chance at around 1:4000, too much for my liking).

So:

Am I missing some crucial bit of information that says the above is actually okay?
If not - what happens if the application tries to insert a new row into the table and the checksum(newid()) gives a primary key that already exists? Will my table blow up? Will the insert fail and leave it up to the application what to do with it?


texts: check format of every value of column in sql server

texts:
I have a table_1 that has column 'dob' which has dates in nvarchar(max). Now I want to check every date whether it is in datetime format or not. The dates which are in datetime format should be copied to table_2 that has column which has dates in datetime datatype format.

Any help how can I do this?


texts:
Is there a way to make automated  backups of a DB without having the I/O frozen ?

I am using an Amazon EC2 instance .

I/O is frozen on database distribution. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.


texts: Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?


texts: Uniqueness-like constraint but with limit greater than 1