DBCC CheckDB : Operating System Error 27(The drive cannot find the sector requested) So my regular DBCC check had this lovely message waiting for me in the error logs this morning. I've been trying to find out more about this issue and what can be done about it. Manually running DBCC CHECKDB will result in DBCC aborting with Error 6 (connection closed by remote host) and get me this error in the SQL log file. Running DBCC CHECKDB(dbname) WITH PHYSICAL ONLY or DBCC CHECKDB(dbname, NOINDEX) will complete successfully and report no errors. Chkdsk reports no errors on the drive.
Both the drive and the database have ample free space. The disk is on a SAN (rather than local).
How can I determine if this a corruption error in the database file or an actual "on the disk" bad sector? Can this be fixed in place or do I need a new disk and to restore from backups? While I have good backups, if I restore a recent backup, will that also "restore" the missing data/sector?
FWIW the DB is 2.2 TB so I'm rather reluctant to jump into a fix until I really know what's going on.
EDIT: This morning, things went even further sideways. We had a log full error, but the log could not be extended or backed up due to the same disk error (sector cannot be found). This was preventing any actions in the database or it's related application. Had to back up the log to another drive to free up log space and allow for normal operations to continue.

Turns out the problem had to do with the SAN returning invalid sectors during disk IO events. What had happened was that the disk had had 400Gb of unusable space removed, and something went sideways in the disk definition in the SAN. The solution was to shrink the disk by 10Gb then grow back that 10Gb to reestablish the disk definition in the SAN. Once that was complete, suddenly everything was fine. Not at all what I expected but I wanted to show what the solution was, because I think it's important that "rock solid, never has an error, why are you even looking at that" systems need to be included in a full error analysis.


One to many join, Sum from both table I have two tables: orders and ordersdetail...(partial table)

orders- id(primary key), amount, date( index)

orderdeails- id(primary key), skunitid, quantity, order_id

Each order has multiple order details.

Query:

SELECT Sum(orders.amount), SUM(ordersdetails.quantity)
FROM orders joins orderdetails on ordersdetail.order_id = orders.id
WHERE {{conditions on date column of order table}}

I know its the wrong query, but I want data from both tables in single query (single iteration). When I use a join, the order table's amount comes so many times. How I can get the sum of amount and sum of quantity in single query (I have an index on date column in the order table which has million rows).

I need an efficient way..

Suppose your date range is 2014-12-08 to 2014-12-11

This would be the Query you need

SELECT B.*,A.quantity
FROM
(
    SELECT
        AA.id,SUM(BB.quantity) quantity
    FROM
    (
        SELECT id FROM orders
        WHERE date >= '2014-12-08' AND date <= '2014-12-11'
    ) AA INNER JOIN orderdetails BB ON AA.id = BB.order_id
    GROUP BY AA.id
) A INNER JOIN orders B USING (id);


In mongodb cluster, Can I shut down one config server and one data server in the shard at the same time I have one mongodb cluster, including 3 config servers, and one shard (3 data servers in this shard). One data server and one config server are on the same machine. Can I shut both of them down to upgrade the cluster without stop the service?

The normal rules of replica set elections apply.

If your shard is backed by a 3-node replica set, then you can have any single node down while still being able to maintain a primary. A strict majority of nodes must be available, so 2/3 in the 3-node example.

If the data-bearing server you are planning to restart happens to be a replica set primary, I would shut it down gracefully with db.shutdownServer() to minimise down time (and make sure there is an eligible secondary whose oplog data is within 10 seconds of the primary). db.shutdownServer() runs the shutdown command against the admin database.


MySQL InnoDB Deadlock For 2 simple insert queries I have a deadlock for this two insert queries:

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)

Here is the InnoDB Status:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-12-23 15:47:11 1f4c
*** (1) TRANSACTION:
TRANSACTION 19896526, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17988, OS thread handle 0x17bc, query id 5701353 localhost 127.0.0.1 root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,  nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896526 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 19896542, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 17979, OS thread handle 0x1f4c, query id 5701360 localhost 127.0.0.1    root update
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition,   nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896542 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of    table `db`.`playerclub` trx id 19896542 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

The only foriegn key on this table is the "account_id".

Any ideas?

EDIT:
Here is my PlayerClub info:

CREATE TABLE `PlayerClub` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `modifiedBy` bigint(20) DEFAULT NULL,
  `timeCreated` datetime NOT NULL,
  `account_id` bigint(20) DEFAULT NULL,
  `currentClubId` bigint(20) DEFAULT NULL,
  `endingLevelPosition` int(11) NOT NULL,
  `nextClubId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  KEY `FK_cagoa3q409gsukj51ltiokjoh` (`account_id`),
  CONSTRAINT `FK_cagoa3q409gsukj51ltiokjoh` FOREIGN KEY (`account_id`) REFERENCES   `PlayerAccount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

HERE ARE THE FACTS

Here are the two INSERTs

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)

Here are the two lines from your SHOW ENGINE INNODB STATUS\G

RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896526 lock_mode X insert intention waiting
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896542 lock_mode X

OBSERVATIONS

You doing an INSERT with two different account_ids: 561 and 563.

They are unique and should not have issues, right ? WRONG !!!

Due to InnoDB's Clustered Index, there can still be a deadlock. Why ?

Look back at your two INSERTs. The PRIMARY KEY on id in not specified. It must be auto generated. Any key other than the PRIMARY KEY (unique or non-unique) will have the PRIMARY KEY attached.

Please note the MySQL Documentation on how a Secondary Index and a a Primary Key are intertwined:

  All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

  If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

Although you are inserting account_id 561 and 563, under the hood you are insert 561-(id) and 563-(id) into the UK_cagoa3q409gsukj51ltiokjoh index. The PRIMARY KEY becomes the bottleneck because the Secondary Index has to wait until the id column is auto_generated.

RECOMMENDATION

You have a table with two candidate keys

PRIMARY KEY on id
UNIQUE KEY on UK_cagoa3q409gsukj51ltiokjoh

Since both are BIGINT, you could increase performance and have a smaller PlayerClub table by getting rid of id and still maintain uniqueness because of UK_cagoa3q409gsukj51ltiokjoh as well as avoiding this deadlock situation.


You need to unnest the array then extract the keys as columns from the resulting JSON values:

select td.id, td.name, tg.tag_data ->> 'name' as tag_name, tg.tag_data ->> 'scope' as scope
from todos td
  cross join lateral jsonb_array_elements(td.tags) as tg(tag_data)
order by td.id;

Online example: http://rextester.com/DWYIJ81620


One-directional database replication without delete on target database? I have two databases: A small one where data is permanently added and a big one where the data is copied from the small one and is supposed to be stored long term. The small database deletes old data if the database is growing to big. But the big database is supposed to keep this data (that's its purpose).
Is there a way to achieve this by transactional replication?
(I'm not interested in a SQL-Statement solution and regularly calling a job. I know how to do that.)
I've read some stuff about one-directional replication, snapshot replication, transactional replication, and merge replication but I can't figure out if one of those can do what I need. The scenario is not that exotic, isn't it?
I just need someone to point me the right direction.
Any help would be appreciated.

Yes you can choose to not replicate delete statement.
Try this:


SQL Server spatial geometry properties on index Is there a way to add geometry.STSrid(object property) to a spatial index or is it already added when I create a spatial index in SQL Server 2012.

I have mixed SRID table and I was wondering if it could help?

We collect spatial data from several source to one database structure. I don't have a simple solution to transform spatial data on our stack (SQL Server) and I need to keep original data. One solution that I have considered is to keep original geometry and add another transformed geometry on side.

The SRID does not appear to be an integral part of the spatial index nor can it be added.

It is integral to the geometry even though there are no real tools for manipulating geometries based on the SRIDs in SQL Server. It does have a few rules about SRIDs.

Geographies must have a SRID from a list of specific SRIDs.
When comparing, manipulating geometries and geographies they must have the same SRID.

For example using the following set up.

CREATE TABLE TEST  (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    ,GEOM GEOMETRY
    )

INSERT INTO TEST (GEOM) VALUES
(Geometry::Point(0,1,0)),
(Geometry::Point(3,2,1)),
(Geometry::Point(2,3,2)),
(Geometry::Point(1,0,2))

CREATE SPATIAL INDEX TEST_SDX ON TEST(GEOM) WITH (BOUNDING_BOX=(0,0,5,5))

The first query determines distances between points.  It will only return a distance between the 3rd and 4th point as they are the only ones with the same SRID.  The other distances will be NULL.
The second query is for selecting geometries inside a buffer.  Only a single row is returned since the query buffer was created with SRID 2.

SELECT a.ID, b.ID. a.GEOM.STDistance(b.GEOM)
FROM TEST a
    INNER JOIN TEST b ON a.ID < b.ID

ID          ID
----------- ----------- ----------------------
1           2           NULL
1           3           NULL
2           3           NULL
1           4           NULL
2           4           NULL
3           4           3

SELECT *
FROM TEST a
WHERE Geometry::Point(1,1,1).STBuffer(3).STContains(a.GEOM) = 1

ID          GEOM
----------- --------------------------------------------------
2           0x01000000010C00000000000000000000000000000040

Also note SQL Server let me build a spatial index over the geometry column that contains mixed SRIDs, but it is of limited use.  Even if there was a lot more data, the index would be of limited use.

I would suggest to make life easier that you pick a single projection to use across all you data and transform geometries to that.  This can be done alongside the current geometries or replace them.  Also if you choose a Lat/Lon projection, use the Geography data type.

There is an open source project, SQL Server Spatial Tools, that has some tools that will allow you to do projections.  You will however have to know the parameters for the projections you are using as it does not appear to have a list of projections to work with.  I can't advise how good these are, as I haven't used them.


Insert data from 1 column to many columns in another table (using 1 line) in sql I have a table (table1) in which IDs of transactions, transaction state (there are 10 total states) and their time of entering that state are stored.

I want to feed this data into another table which would have 11 columns: 1 for the Transaction ID and the other 10 for the time that they enter each state.

I want to write this as an SQL query. I looked for it and found

insert into table2(ID, s1, s2, s3, s4 ... )
select ID, time from table1
where [];

but I don't have 10 different columns in table1 to copy from as there are 10 different rows corresponding to each ID and each state.

Please help me out in doing this is 1 query.

I am new to SQL. Thanks in advance.


Show rows with non-unique value in one field I'm familiar with how to aggregate rows as shown in this answer:

Show only duplicate values

I'm also familiar with how to filter the aggregated results using a HAVING clause.

What I can't seem to grasp (so that it sticks) is how to filter rows based on values or comparisons other rows, without aggregating them.

I know the answer involves something about window functions or window clauses, and in fact I've done it successfully before.  But it just doesn't seem to stick in my mind how it works; I feel I'm missing something fundamental.

To give an example, with the info:

 fruit_name | some_field
------------+------------
 apple      |       3.25
 apple      |        6.8
 apple      |        0.7
 orange     |        2.6
 banana     |        3.5
 banana     |       2.49
 cherry     |          1
 grapefruit |        2.6
 grapefruit |        2.7

I want to get all rows with a row count (by fruit name) greater than 1, so it should look like:

 fruit_name | some_field
------------+------------
 apple      |       3.25
 apple      |        6.8
 apple      |        0.7
 banana     |        3.5
 banana     |       2.49
 grapefruit |        2.6
 grapefruit |        2.7

What is the correct idiomatic way to do this?

(I'd like an SQL standard answer if possible, and if there's a simpler Postgres-specific way to do it I'd like to know that also.)

Select fruit_name, some_value
  From tooty_fruity
  where fruit_name in (
    Select fruit_name from tooty_fruity
      Group by fruit_name
      having count(*) > 1)


retrieve data based on a condition I have 2 tables, TA and TB:

TA
--------
id  name
1   a
2   b
3   c
4   d
5   e
6   f

TB
--------
id  p_id  name
1   1     a
2   1     b
3   1     c
4   2     a
5   2     b
6   2     d

I need : if p_id = 1 then :

id  name match
1   a    1
2   b    1
3   c    1
4   d    0
5   e    0
6   f    0

If p_id = 2 then :

id  name match
1   a    1
2   b    1
3   c    0
4   d    1
5   e    0
6   f    0

You need to use a LEFT OUTER JOIN to achieve this:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE ta
( id INT NOT NULL,
  name VARCHAR(20) NOT NULL
) ;

INSERT INTO ta
  (id, name)
VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd'),
  (5, 'e'),
  (6, 'f') ;

CREATE TABLE TB
    (`id` int, `p_id` int, `name` varchar(20))
;

INSERT INTO TB
    (`id`, `p_id`, `name`)
VALUES
    (1, 1, 'a'),
    (2, 1, 'b'),
    (3, 1, 'c'),
    (4, 2, 'a'),
    (5, 2, 'b'),
    (6, 2, 'd')
;

GO

CREATE PROCEDURE test
(my_p_id INT)
BEGIN
  SELECT TA.id, TA.name,
         (IF(TB.name IS NOT NULL, 1, 0)) AS `match`
    FROM TA
    LEFT JOIN TB
      ON TA.name = TB.name
     AND TB.p_id = my_p_id;

END;

Query 1:

CALL test(1);

Results:

| ID | NAME | MATCH |
|----|------|-------|
|  1 |    a |     1 |
|  2 |    b |     1 |
|  3 |    c |     1 |
|  4 |    d |     0 |
|  5 |    e |     0 |
|  6 |    f |     0 |

Query 2:

CALL test(2);

Results:

| ID | NAME | MATCH |
|----|------|-------|
|  1 |    a |     1 |
|  2 |    b |     1 |
|  3 |    c |     0 |
|  4 |    d |     1 |
|  5 |    e |     0 |
|  6 |    f |     0 |

The query of interest is wraped in the procedure test. That makes working with SQLFiddle a little easier. But you certainly can use the query outside of a procedure. However, pay attention to the fact that the comparison to the parameter value is part of the LEFT JOIN evaluation.

For more informations on JOINs check out my JOIN series here: http://sqlity.net/en/1146/a-join-a-day-introduction/

That series is written about SQL Server. But most concepts and most of the syntax apply to MySQL as well.


Is nvarchar 0-indexed? Is the max length configuration for nvarchar 0-indexed, e.g. If I declare column x as type nvarchar and set the max length to (2): (X nvarchar(2)) does that mean that the column takes 3 nvarchars? or only 2?

This is quite clearly explained in the documentation. The value in parentheses indicates the string length. So nvarchar(2) has a string length of 2.

https://msdn.microsoft.com/en-gb/library/ms186939.aspx

  nvarchar [ ( n | max ) ]  Variable-length Unicode string data. n
  defines the string length and can be a value from 1 through 4,000.


Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn't end in that character I need to get all rows which end in a specific character, P for example, but in which a similar key sans the P exists. I have no idea how to approach this one in MySQL.

This is very small example, my actual data is huge with other columns also.

+------------+
|    key     |
+------------+
| value_100  |
| value_100P |
| value_101  |
| value_101  |
| value_102  |
| value_102P |
| value_102P |
| value_102P |
| value_103P |
| value_103P |
| value_104P |
+------------+

The query would output,

+------------+
|    key     |
+------------+
| value_100P |
| value_102P |
+------------+

How about this :

select t1.key
from table1 t1
inner join table1 t2
      on   t1.key = t2.key || 'P'
where t1.key like '%P'
order by t1.key ;

Querying t1 gives you a list of all of the rows with keys that end in 'P'.

The join to t2 is based on rows where t2's key, plus a trailing 'P', matches the key in t1  (or, alternatively, the original key without the trailing 'P').


postgresql where are temp files stored Once the work_mem , temp_buffer or maintenence_work_mem is full where does the PostgreSQL writes to the disk, what is the location of temporary files by default

Quote from the manual

Temporary files (for operations such as sorting more data than can fit in memory) are created within PGDATA/base/pgsql_tmp, or within a pgsql_tmp subdirectory of a tablespace directory if a tablespace other than pg_default is specified for them. The name of a temporary file has the form pgsql_tmpPPP.NNN, where PPP is the PID of the owning backend and NNN distinguishes different temporary files of that backend


How to preserve ID generated from a PRIMARY KEY when moving data into a new table? I have a large table (~4 million rows, ~100 fields) which I need to split into a few smaller tables by type so it doesn't become unmanageable as it grows.

Each entry has an ID which is an automatically generated primary key. These keys need to be preserved when the data is moved to new tables. Additionally, new unique keys will need to be generated; and these will need to be unique across all of the smaller tables, rather than simply within each table itself.

I was thinking that a way to do this would be to transfer the ID into a new field (or a non-automatically generated primary key field?) when transferring the data into the new tables. Once the data is transferred, I could strip down the original table so it is just a primary key so it can continue automatically generating keys for any additional data - i.e. before inserting data into one of the new tables, get a new primary key from the original table.

However, that seems rather clumsy!

Also it leaves the question, if a user is referring to an ID without knowing what type the data is (i.e. what table it will be found in), how could they be 'directed' to the correct table?

I'm sure there must be a better way to do this?

When you have a Primary Key with an auto_increment it will generate a new ID only if you insert a NULL value. If you set ID=4 in your INSERT, the ID will be 4 so you'll not loose your ID during your "move" operation.

We don't have the "SEQUENCE" notion like in Oracle database so your "global ID" problem it's not so easy to do.

Maybe you can try something like this (but it'll add complications for just a 4 millions rows table)

Create a table used for generates your "Global ID", with one int filed auto_incremented:

CREATE TABLE test.sequence_table (next_id int primary key auto_increment);

When you want insert a new row in your child table:

Solution 1: With SELECT in information_schema

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=(auto_increment - 1) FROM information_schema.tables WHERE table_schema="test" AND table_name="sequence_table"; -- Here I use a MySQL Variable but you can store it in PHP or whatever

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)

Edit after ypercube comment:

Solution 2: With LAST_INSERT_ID()

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=LAST_INSERT_ID(); -- Use of the MySQL function LAST_INSERT_ID()

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)


Subquery in where clause forces seq scan instead of using trigram index I have a query that is supposed to use a bunch of trigram indexes to speed up ILIKE clauses. This works as long as there isn't a subquery in one of the where terms.

I've simplified my query and can reproduce the issue with a trivial subquery and a simple ILIKE comparison. The actual query has several more ILIKE comparisons and some subqueries that actually do something useful. I'm using Postgres 9.6.

The index is defined like this:

CREATE INDEX datasets_notes_trgm_idx ON datasets USING GIN(notes gin_trgm_ops);

The following query is slow and doesn't use any of my indexes:

SELECT * FROM datasets
WHERE
    datasets.notes ILIKE '%test%' OR
    datasets_id IN (SELECT 1)

Explain output for this query:

"Seq Scan on datasets  (cost=0.01..21156.99 rows=53076 width=1428)
(actual time=0.042..155.697 rows=1098 loops=1)"
"  Filter: ((notes ~~* '%test%'::text) OR (hashed SubPlan 1))"
"  Rows Removed by Filter: 104967"
"  Buffers: shared hit=19634"
"  SubPlan 1"
"    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=1)"
"Planning time: 0.980 ms"
"Execution time: 158.579 ms"

The follow query is fast and uses the index:

SELECT * FROM datasets
WHERE
    datasets.notes ILIKE '%test%'

Explain output for this query:

"Bitmap Heap Scan on datasets  (cost=28.68..360.36 rows=87 width=1428)
(actual time=1.056..11.763 rows=1097 loops=1)"
"  Recheck Cond: (notes ~~* '%test%'::text)"
"  Rows Removed by Index Recheck: 16"
"  Heap Blocks: exact=1053"
"  Buffers: shared hit=1097"
"  ->  Bitmap Index Scan on datasets_notes_trgm_idx  (cost=0.00..28.66 rows=87 width=0) (actual time=0.732..0.732 rows=1113 loops=1)"
"        Index Cond: (notes ~~* '%test%'::text)"
"        Buffers: shared hit=12"
"Planning time: 0.869 ms"
"Execution time: 14.563 ms"

Replacing the subquery with a static list of ids also works and the trigram index is used.

SELECT * FROM datasets
WHERE
    datasets.notes ILIKE '%test%' OR
    datasets_id IN (1,2,3)

Explain output for this query:

"Bitmap Heap Scan on datasets  (cost=41.98..385.13 rows=90 width=1428) (actual time=0.593..7.194 rows=1100 loops=1)"
"  Recheck Cond: ((notes ~~* '%test%'::text) OR (datasets_id = ANY ('{1,2,3}'::integer[])))"
"  Rows Removed by Index Recheck: 16"
"  Heap Blocks: exact=1054"
"  Buffers: shared hit=1107"
"  ->  BitmapOr  (cost=41.98..41.98 rows=90 width=0) (actual time=0.428..0.428 rows=0 loops=1)"
"        Buffers: shared hit=21"
"        ->  Bitmap Index Scan on datasets_notes_trgm_idx  (cost=0.00..28.66 rows=87 width=0) (actual time=0.401..0.401 rows=1113 loops=1)"
"              Index Cond: (notes ~~* '%test%'::text)"
"              Buffers: shared hit=12"
"        ->  Bitmap Index Scan on datasets_pkey  (cost=0.00..13.28 rows=3 width=0) (actual time=0.019..0.019 rows=3 loops=1)"
"              Index Cond: (datasets_id = ANY ('{1,2,3}'::integer[]))"
"              Buffers: shared hit=9"
"Planning time: 0.543 ms"
"Execution time: 10.034 ms"

The actual performance difference is much larger in the full query that uses several more ILIKE comparisons.

Why does the subquery force a sequential scan on my query? And how can I avoid this and add subqueries while still using my trigram indexes?

You get that problem because of the OR, which very often cripples query performance, and because the optimizer cannot "optimize away" that OR condition.

If you cannot simplify the query to get rid of the OR, you can try to create an index on datasets_id in the hope to get a "bitmap or" that combines the two bitmap index scans.

A general problem with subqueries is that PostgreSQL can estimate how many rows a subquery will return, but it does not know whether the result values will be "most common values" or not, so it may choose a less than optimal execution plan.

Sometimes execution can be improved by splitting one query into two queries so that the results of one query become constants in the other, and the optimizer can choose a better plan.


Error Querying MySQL server linked to SQL Server 2008 I've created a linked server in MS SQL Server 2008 to a remote MySQL server. When I try to query any tables, I get an error:

.tablename. contains no columns that can be selected or the current user does not have     permissions on that object.

Does anyone know why this happens?

UPDATE: Looks like this is a known issue with MSSQL SERVER 2008

http://support.microsoft.com/kb/971261

it looks like a permission issue with the authentication mode you're using against your mysql server. You can test the linked servers using the stored procedure "sp_testlinkedserver" - details here. See if you can take data from your linked server using simple select statement: "select top 10 * from server.database.schema.table".

The MS error is for the action "Script as" - when you want to script the schema of an object, not for simply selecting data.


Hoursworked/day computation via Bio Logs This is my checkinout table:

USERID  CHECKTIME            CHECKTYPE
------  -------------------  ---------
404     2016-02-12 07:24:03  I
404     2016-02-12 17:01:34  O
404     2016-02-15 07:24:21  I
404     2016-02-15 17:00:36  I
404     2016-02-16 07:09:01  I
404     2016-02-16 17:01:33  O

I used the script below to compute hoursworked/day, but the output gives my column Hoursworked huge value:

SELECT userid,Name
    DATE(CHECKTIME) AS date,
    SUM(UNIX_TIMESTAMP(CHECKTIME)*(1-2*checktype))/3600 AS hours_worked
    FROM CHECKINOUT
    GROUP BY date(CHECKTIME), userid;

Output:

Hoursworked  Userid  Date(Checktime)
-----------  ------  ---------------
NULL         404     2016-02-12
NULL         404     2016-02-12
NULL         404     2016-02-15
NULL         404     2016-02-15
NULL         404     2016-02-16
NULL         404     2016-02-16

How to resolve this issue?

My data source is inconsistent: our employee has the habit of multiple login\logout. Can you help where to eliminate the employee with incomplete I\O and for those having multiple login\out to get the first and last login\out logs.

The idea I wrote in the comment http://sqlfiddle.com/#!9/785bc/5 :

select
    a.userid,
    date(a.CHECKTIME),
    timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as diff
from CHECKINOUT a
join CHECKINOUT b
    on date(a.CHECKTIME) = date(b.CHECKTIME) and a.userid = b.userid
where
    a.CHECKTYPE = 'I'
    AND b.CHECKTYPE = 'O'
group by date(a.CHECKTIME), userid;

But as I wrote, there is a problem with the days where one of the I/O rows is missing and you need to either fix your data or specify what to do in such case.


MySQL binlog missing queries In direct reference to the accepted answer in this question

  MySQL doesn't log to the binary log file when you use direct database.table syntax in your update queries (eg. UPDATE Foo.Bar SET ...).

Is the above true? It doesn't say this explicitly in the link the answerer provides. And is the only way to mitigate this is to ensure that your application doesn't prefix the schema name to the table refs in queries?

The reason I am asking is because we recently restored from a recent backup after an accidental drop database. I was also hoping to complete the data by restoring that day's activity from the binlogs. When I extracted the queries from the binlogs I couldn't find a single query before drop database appeared in the log. - I am also certain that I extracted the correct timestamps.

That is true in the context of binlog-do-db (and equivalent) using STATEMENT based replication. You should never use binlog-do-db unless you know what you are doing (or you will lose data because of replication filters). If you want to do replication filtering, you should use replicate-wild-do-table (and other wild options) on the replica. It is easy to filter out events, but impossible to put them back if they were never logged. ROW sometimes can also help saner filtering behaviour.

If you are not using replication filtering, all write commands that are not server specific are sent to the master's binlog, if enabled correctly and not truncated/expired.

You need to provide more information to understand the issue you suffered (mysql version, topology used and options).


How to collapse contiguous single day start/end date ranges in MySQL (edited - I oversimplified the original problem)

I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges.  Here is my sample input data:

INSERT INTO my_table(tag, start_date, end_date) VALUES ('AA', '2020-07-01', '2020-07-02');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('XB', '2020-07-02', '2020-07-03');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('YC', '2020-07-03', '2020-07-04');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('ZD', '2020-07-04', '2020-07-05');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('0E', '2020-07-05', '2020-07-06');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-06', '2020-07-15');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-20', '2020-07-21');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-21', '2020-07-22');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-22', '2020-07-23');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('EA', '2020-07-23', '2020-07-31');

Here is the output I want:

AA,2020-07-01,2020-07-06
CA,2020-07-06,2020-07-15
CA,2020-07-20,2020-07-23
EA,2020-07-23,2020-07-31

It seems like it should be simple, but I can't figure out how to do it in pure MySQL.  I've tried joining the table to itself and using various combinations of MIN() and MAX(), but the solution eludes me.  I can do it in PHP code, but would prefer to let MySQL do the work.

I can't rely on the tag field for grouping (it can be anything). The date collapse should be done only on the date ranges that are 1-day long and contiguous.  Date ranges longer than one day should not be collapsed. The selected tag should be the value associated with the first date in the range

After a little more research, I figured out the solution to this "gaps and islands" problem:

SELECT t.tag, begin AS start_date, MAX(end) AS end_date
FROM (
    SELECT
    a.*
    CASE WHEN (DATEDIFF(start_date, @prev_date) = 1 AND DATEDIFF(end_date, start_date) = 1)
        THEN @begin := @begin
        ELSE @begin := start_date
    END AS begin,
    end_date AS end,
    @prev_date := start_date
    FROM (SELECT @prev_date := NULL, @begin := NULL) AS vars
    JOIN my_table a
    ORDER BY start_date
) AS t
GROUP BY begin;


Patroni with etcd starting up I have an existing PostgreSQL cluster v11.5 with streaming replication.
I want to set up high availability managed by Patroni.
I installed Patroni on both machines, I set up an etcd cluster on three others Virtuals Machines ['http://10.A.A.191:2379', 'http://10.B.B.99:2379', 'http://10.C.C.170:2379'] but Patroni starting failed with systemctl.
When I start Patroni with patroni -c /etc/patroni.yml command, I have the errors below:

2019-12-26 16:51:38,207 DEBUG: Selection next machine in cache. Available machines: ['http://10.A.A.191:2379', 'http://10.B.B.99:2379', 'http://10.C.C.170:2379']
2019-12-26 16:51:38,207 INFO: Selected new etcd server http://10.C.C.170:2379
2019-12-26 16:51:38,208 DEBUG: Converted retries value: 0 -> Retry(total=0, connect=None, read=None, redirect=0, status=None)
2019-12-26 16:51:38,208 DEBUG: Starting new HTTP connection (1): 10.C.C.170:2379
2019-12-26 16:51:38,210 DEBUG: http://10.C.C.170:2379 "GET /v2/machines HTTP/1.1" 404 19
2019-12-26 16:51:38,211 ERROR: Failed to get list of machines from http://10.C.C.170:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2019-12-26 16:51:38,211 INFO: Retrying on http://10.A.A.191:2379
2019-12-26 16:51:38,211 DEBUG: Converted retries value: 0 -> Retry(total=0, connect=None, read=None, redirect=0, status=None)
2019-12-26 16:51:38,211 DEBUG: Starting new HTTP connection (1): 10.237.132.191:2379
2019-12-26 16:51:38,214 DEBUG: http://10.A.A.191:2379 "GET /v2/machines HTTP/1.1" 404 19
2019-12-26 16:51:38,214 ERROR: Failed to get list of machines from http://10.A.A.191:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2019-12-26 16:51:38,214 INFO: Retrying on http://10.B.B.99:2379
2019-12-26 16:51:38,215 DEBUG: Converted retries value: 0 -> Retry(total=0, connect=None, read=None, redirect=0, status=None)
2019-12-26 16:51:38,215 DEBUG: Starting new HTTP connection (1): 10.B.B.99:2379
2019-12-26 16:51:38,217 DEBUG: http://10.B.B.99:2379 "GET /v2/machines HTTP/1.1" 404 19
2019-12-26 16:51:38,217 ERROR: Failed to get list of machines from http://10.B.B.99:2379/v2: EtcdException('Bad response : 404 page not found\n',)
2019-12-26 16:51:38,217 INFO: waiting on etcd

The problem seems to be at API level DEBUG: http://10.B.B.99:2379 "GET /v2/machines HTTP/1.1" 404 19
etcd is reacheable from PostgreSQL host:
curl -s http://10.B.B.99:2380/members |jq -r returns

[
  {
    "id": 730XXXXXXXXXXX000,
    "peerURLs": [
      "http://10.B.B.99:2380"
    ],
    "name": "etcd1",
    "clientURLs": [
      "http://0.0.0.0:2379"
    ]
  },
  {
    "id": 956XXXXXXXXXXX000,
    "peerURLs": [
      "http://10.C.C.170:2380"
    ],
    "name": "etcd3",
    "clientURLs": [
      "http://0.0.0.0:2379"
    ]
  },
  {
    "id": 1551XXXXXXXXXXX000,
    "peerURLs": [
      "http://10.A.A.191:2380"
    ],
    "name": "etcd2",
    "clientURLs": [
      "http://0.0.0.0:2379"
    ]
  }
]

etcd1 start command:

ExecStart=/usr/local/bin/etcd \
--name etcd1 \
--advertise-client-urls http://0.0.0.0:2379 \
--listen-client-urls http://0.0.0.0:2379 \
--initial-advertise-peer-urls http://10.B.B.99:2380 \
--listen-peer-urls http://0.0.0.0:2380 \
--initial-cluster etcd1=http://10.B.B.99:2380,etcd2=http://10.A.A.191:2380,etcd3=http://10.C.C.170:2380 \
--initial-cluster-token etcd-cluster-atom \
--initial-cluster-state new \
--logger zap \
--log-outputs stdout \
--log-level debug

Someone could help me please?
What is wrong?
Many thanks

Versions:
etcd Version: 3.4.0
etcdctl version: 3.4.0
API version: 3.4
patroni 1.6.3
python-etcd==0.4.5

Thanks to CyberDem0n.

Two issues in etcd1 start command:

API VERSION
Etcd v3.4 default API version is v3.
Patroni doesn't currently support the v3 API.
Etcd 3.4 turned API v2 off by default.

I added --enable-v2=true flag on the three etcd.service and this error was fixed.

advertise-client-urls FLAG

And for each etcd server I replaced 0.0.0.0 by the real etcd server ip on the --advertise-client-urls flag (like --advertise-client-urls http://10.B.B.99:2379 in the example).


Is it better to use FREETEXT or CONTAINS Full Text Searches when searching by single characters I am trying to come up with the most efficient way to search for records in a collection of about 130,000 total records.  The long, short of it is we have about 130,000 Active Directory records.  I am building a site that looks up users in AD.  I would like to implement TypeAhead functionality on a Search box.  I could query AD directly in code, but am thinking about dumping the data into SQL Server and querying that instead.  TypeAhead does a search as you type characters in the textbox.  Is it efficient to search a table of 130,000 records by single characters?  I am trying to determine if the searching should be done by the SQL Server engine or on the site?  I am thinking SQL Server.  If true, is it better to use FREETEXT or CONTAINS full text searches?

Curious what the community thinks about this?  Or am I way off?

I'm not sure I'm understanding your question.  FREETEXT evaluates not only on the string, but also the meaning.  Whereas CONTAINS is used for precise or fuzzy searching on a string.


How to replace (SIMILAR TO + regular expression) with LIKE or ~ in PostgreSQL? I have the following PostgreSQL function which checks if p_text1 contains the word/phrase p_text2 within it:

CREATE OR REPLACE FUNCTION public."Contains"(
p_text1 character varying,
p_text2 character varying)
RETURNS boolean
LANGUAGE 'plpgsql'

COST 100
IMMUTABLE
AS $BODY$

BEGIN

 perform 1
 where   ( p_text1 similar to '((% )|(%-)|(%\())?'||p_text2||'(( %)|(-%)|(\)%))?' ) or
    ( replace(p_text1,'-',' ') similar to '((% )|(%\())?'||replace(p_text2,'-',' ')||'(( %)|(\)%))?' ) or
    ( replace(p_text1,'-','') similar to '((% )|(%\())?'||replace(p_text2,'-','')||'(( %)|(\)%))?' );

 return found;

END;
$BODY$;

p_text2 is considered a word/phrase if it is preceded/followed by a dash, space, parenthesis or nothing.

Examples:

select public."Contains"('data mining' , 'mining') --> true

select public."Contains"('information retrieval (ir) system' , 'ir') --> true

select public."Contains"('semantic (information retrieval)' , 'semantic information') --> false

select public."Contains"('ontology-based queries' , 'ontology') --> true

select public."Contains"('ontology-based queries' , 'ontology based') --> true

The function will be called like this:

select * from my_table
where public."Contains"( text_column , some_text_variable) = true;

Given that my_table contains about 15,000 rows.

I read many advises to avoid using SIMILAR TO and to replace it with simple LIKE for performance issues... I just don't know how to rewrite such a query with LIKE, and I don't know if that would yield better performance.
Any help is appreciated. Thanks in advance

LIKE is much less flexible than SIMILAR TO.  In general there is going to be no formulaic way to downgrade from SIMILAR TO to LIKE.  Based on your example, it might take like 34 (4*4 + 3*3 + 3*3) different ORed together LIKE to accomplish this.  For example, your first expression in the OR branch gets to pair 3 alternatives plus the empty possibility (so 4 total) at the beginning with another 4 at the end, so that would expand to 16.

  I read many advises to avoid using SIMILAR TO and to replace it with simple LIKE for performance issues

This advice would only apply if there is a simple like which does the same job.  But in general, tuning should be based on evidence, not on rumors.  So to start with, do you actually have a performance problem?


What's faster than Mirroring tables, compare or write? A co-worker is trying to sync  table B with the contents of table A, both tables have the same structure, we wrote 3 statements, one for update, one for delete and one for inserts.

I have a question when it comes to updating rows:

tables structure:

        [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [value1] [int] NULL,
        [value2] [int] NULL,

We could have this:

update tb_B
set tb_B.value1 = tb_A.value1,tb_B.value2 = tb_A.value2
from tb_B inner join  b_A on tb_B.id = tb_A.id

but each time we would be writing all the values again and again regardless if the values changed, so I thought about something like this:

update tb_B
set
    tb_B.value1 = tb_A.value1,
    tb_B.value2 = tb_A.value2
from tb_B
inner join tb_A on tb_B.id = tb_A.id
where
    tb_B.value1 <> tb_A.value1 or
    tb_B.value2 <> tb_A.value2

The last update checks if the value of any column has changed, if true, then it proceeds to update all of the values for that row.

Which do you think it's faster ? writing all the values again and again VS comparing the values and writing ONLY if at least one value is different ?

I think that the farther away the updated column is, the more SQL will take to compare because of the way the OR operator works, this is how I think it works:

think of the numbers as columns:
if ( 1 <> 1 OR 2 <> 2 OR 3 <> 3 OR 4 <> 5)
    then UPDATE

the program will make 4 comparisons in order to realize the row needs to be updated, whereas in this case:

if ( 1 <> 2 OR 2 <> 2 OR 3 <> 3 OR 4 <> 5)
    then UPDATE

the program will make 1 comparison and proceed with the update. The comparison in my opinion will vary tremendously because the number of comparisons vary for each row !

I hope I am on the right track here,

I appreciate your comments,
thanks

For a question like this, you really need to time the queries to check performance.

My guess is that the logging overhead on the update is going to be more expensive than any reasonable list of "or" statements.  If you are going to use the comparisons, though, you should have a correct statement that takes NULLs into account:

where (tb_B.value1 <> tb_A.value1) or
      (tb_b.value1 is null and tb_a.value1 is not null) or (tb_b.value1 is not null and tb_a.value1 is null) or
      tb_B.value2 <> tb_A.value2 or
      (tb_b.value2 is null and tb_a.value2 is not null) or (tb_b.value2 is not null and tb_a.value2 is null)

You can make the query more efficient by defining an index on each table with (id, value1, value2).  This will allow all the logic to use the index.  A page would only be necessary in the page cache when a mismatch is found.


Merging adjacent date intervals into a single row Here is the scenario:
If the employee transaction is continuous display as-is. if the employee got any gap display as a new row.

Below is the sample table

Emid    Fromdate    Todate
-----   --------    ------
A   1/1/2012    12/31/2012
A   12/31/2012  12/31/2013
A   12/31/2013  12/31/2014
A   2/2/2015    12/31/2099

Expected output:

emid    Fromdate    Todate
======  ========    ========
A   1/1/2012    12/31/2014
A   2/2/2015    12/31/2099

You could try this:

[edit] fixed a bug .. comment on line fixed [/edit]

with w_data as (
        select 'A' emid, to_date('01/01/2012','mm/dd/yyyy') fromdate, to_date('12/31/2012','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2012','mm/dd/yyyy') fromdate, to_date('12/31/2013','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('12/31/2013','mm/dd/yyyy') fromdate, to_date('12/31/2014','mm/dd/yyyy') todate from dual union all
        select 'A' emid, to_date('02/02/2015','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('12/30/2013','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('03/22/2014','mm/dd/yyyy') todate from dual union all
        select 'B' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('05/12/2013','mm/dd/yyyy') fromdate, to_date('11/23/2013','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('11/23/2013','mm/dd/yyyy') fromdate, to_date('01/10/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('01/10/2014','mm/dd/yyyy') fromdate, to_date('04/03/2014','mm/dd/yyyy') todate from dual union all
        select 'C' emid, to_date('04/03/2014','mm/dd/yyyy') fromdate, to_date('12/31/2099','mm/dd/yyyy') todate from dual
        ),
  w_sub as (
     select emid, fromdate, todate,
              sum(grp) over (partition by emid order by fromdate) grp_id  -- added partition by .. forgot to group it by employee
       from (
              select emid, fromdate, todate,
                     case when fromdate = lag(todate) over (partition by emid order by fromdate)
                       then 0 else 1 end  grp
                from w_data
             )
        )
select emid, min(fromdate) fromdate, max(todate) todate
  from w_sub
 group by emid, grp_id
 order by emid, grp_id, fromdate
/

E FROMDATE    TODATE
- ----------- -----------
A 01-jan-2012 31-dec-2014
A 02-feb-2015 31-dec-2099
B 12-may-2013 30-dec-2013
B 10-jan-2014 22-mar-2014
B 03-apr-2014 31-dec-2099
C 12-may-2013 31-dec-2099

6 rows selected.


Confused by GRANT ALL PRIVILEGES ON *.* (...) WITH GRANT OPTION Normally I manage users on my databases via phpMyAdmin, but since I do everything else in standalone programs such as SQLyog or HeidiSQL, I wanted the ability to manage users remotely from these programs.

The remote user already had SUPER because it is needed when copying views from a local database to a remote database.
The remote user did not have the required database-level rights on mysql and the target databases as SUPER does not give that.

I tried this (MySQL version 5.6.23):

GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION

Strangely, the *.* did not seem to work. For instance:

SELECT USER, HOST, db, select_priv, insert_priv, grant_priv FROM mysql.db
WHERE db="somedatabase"

returned an empty set. As a result, at that stage my remote user was not able to grant privileges to users (error 1442).

And yet,

SHOW GRANTS FOR 'UserName'@'myIP'

did show

  GRANT ALL PRIVILEGES ON *.* TO 'UserName'@'myIP' IDENTIFIED BY
  PASSWORD 'someHash' WITH GRANT OPTION

To solve it, I had to GRANT ALL specifically on mysql and on somedatabase:

GRANT ALL PRIVILEGES ON mysql.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON somedatabase.* TO 'UserName'@'myIP' IDENTIFIED BY 'password'
WITH GRANT OPTION;

Would someone have insights as to why *.* was not enough?

I really had to think about this one. Error 1442 affecting GRANT commands ?

Note the MySQL Documentation

  Error: 1442 SQLSTATE: HY000 (ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG)

  Message: Can't update table '%s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

If you are using managed hosting, some are known to use triggers and stored procedures to deliberately prevent running GRANT commands.

EXAMPLE : Amazon RDS allows the following:

  Q: What privileges are granted to the master user for my DB Instance?

  For MySQL, the default privileges for the master user include: create, drop, references, event, alter, delete, index, insert, select, update, create temporary tables, lock tables, trigger, create view, show view, alter routine, create routine, execute, trigger, create user, process, show databases, grant option.

What grants are disallowed ?

SUPER
FILE
SHUTDOWN (See my post what could cause a mysql process to close (clean))
REPLICATION SLAVE
REPLICATION CLIENT
See MySQL Documentation on all the GRANTS

If you are seeing Error 1442, then your hosting company must be using some Proxy code written as a Stored Procedure or has created triggers against the mysql schema to throw regular GRANT command under the bus. From a business point-of-view, they are right in doing this.

If Amazon RDS allowed all grants, can you imagine the havoc being wreaked ?

If you had SHUTDOWN, you could shutdown MySQL RDS instance without Amazon's API
If you had REPLICATION SLAVE and REPLICATION CLIENT, you could setup Slaves outside of Amazon (with the right security group defined).
If had SUPER, you can kill connections, hose binary logs, increase connection limits, etc.
If you had FILE, you could create files with SELECT ... INTO OUTFILE right inside the MySQL RDS Instance and LOAD DATA ... INFILE from external sources (again, with the right security group defined).

Therefore, you need to read the hosting company's documentation on the limits they place on MySQL grants. If Amazon does this with RDS, I am sure other companies would have the same protocols in place. The way around this for an Amazon RDS user would be to ditch Amazon RDS and switch to Amazon EC2.

Contact your hosting company and see if this is indeed the case.


SQL Server Replication - Publication and Subscriber schema differ, need to sync I have a SQL Server 2005 database where the publication and subscriber schema are different. This was intentionally done by turning the "Replicate Schema" option to False.

However I now need to propagate the publication schema to the subscriber.

What is the best way of achieving this?

I would use Visual Studio's Schema Comparison tool, or some third party equivalent; it will visually show you what's different and optionally create a script to sync them. Just beware that depending on the change, it may try to copy the table to a work table and then drop the source and rename the new table, which will fail if the table is replicated.


SSRS Blocking Processes We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes:

[dbo].[CheckSessionLock]
[dbo].[WriteLockSession]

We are aware that some reports are running unacceptably slow. What I need to know is whether the blocking in the ReportServer database is symptomatic of an overloaded server or whether the blocking is causing the reports to run slow.

I am inclined to believe it is the first case but haven't been able to gather evidence prove it definitively as such.

I have read the thread at https://connect.microsoft.com/SQLServer/feedback/details/698388/blocking-in-ssrs-reportserver-database in which the issue is loosely acknowledged but I cant get to the link cited for addressing this issue.

Has some out there got to the bottom of same problem with blocking in the ReportServer database?

Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report

See this link for an explanation about the why and some ways to work around it.

You can increase the session timeout as some sort of workaround, the script is documented in the article.

From the article:

  However, there is one scenario where the ping doesn't work, and that is during the inital report execution.  The problem is that while the report is being executed, the user's session is locked (as we are populating the user's temporary snapshot) and the keepalive from the viewer control will be blocked.  Normally, this is not a problem because report executions aren't supposed to take a long time and quite often they finish before the session timeout hits.  Unfortunately, there are some cases where reports (for whatever reason) take an incredibly long time to execute.  What happens in this case is that the user's session is aged out while the report is being executed, resulting in all sorts of strange behavior.


Check status of SQL restore with NORECOVERY before RECOVERY We are going to move some 100+ databases and some of them are 1 TB each. To save time on Friday, we are going to perform restore from sunday's Full  backups using with norecovery. And we do this during the week. And Diff restore with recovery on Friday to catch up.

Now I noticed during the week that if a full restore with norecovery fails, it still says "Restoring" ??. One would expect that the database would disappear.

So the question is. How can I double check on thursday let's say, that all databases that are restored from full backups with norecovery, actually are successfully restored so far, when both failed (but initiated successfully) and non failed restores, seems to both show "restoring" state?

Regards

The easiest would be simply to capture and review the output of the restore scripts, that provide the full detail of each restore, including any that encountered problems. How exactly to capture this will depend on where you are performing the restore - you can send the output from a SQL agent job step to a text file, for example.

If you haven't done that, I believe the SQL errorlog will capture the status of each restore as they finish.


Need postgres statement that returns non unique records even if I pass same ids in array For Example, the following query

SELECT id, name, price
from tennis_ticket
where id=ANY('{3,3}')

returns:

 id |     name     | price
----+--------------+-------
  3 |       Eighty |    80
(1 row)

What I want is:

 id |     name     | price
----+--------------+-------
  3 |       Eighty |    80
  3 |       Eighty |    80
(2 rows)

Join against the array:

select tt.*
from tennis_ticket tt
  join unnest(array[3,3]) x(id) on x.id = tt.id;

or use a values clause

select tt.*
from tennis_ticket tt
  join (values (3), (3)) as x(id) on x.id = tt.id;


Upgrade to SQL Server 2008 R2 Standard with Mirroring (1 Thread Limit) I am upgrading a mirroring pair of SQL2005 Enterprise servers to SQL2008R2 Standard which has 1 thread for mirroring limitation.

As far as I understand this limitation applies to redo queue: http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/a5b58253-cf4a-4dd6-9472-c484347d39ab/ but I don't fully understand what this means.

My question is: how do I measure the current database system to make sure I won't run into problems with this upgrade (with this 1 thread limitation)?

Under normal operations, the redo queue is close to zero. What you can do is: track your current redo counters in perfmon (SQLServer:Database Mirroring> Redo Bytes/sec,Redo Queue). With this value you will know how much of your redo log performance will be affected when switching between the SQL editions.

Note that is SQL2008 R2 EE, if you have fewer that five CPUS, SQL will still use only one thread for the redo process. So if you have four or lesser CPUs, SQL will still use a single threaded redo operation.


Remove all records with duplicates in db2. (Not just the duplicate records) How can I remove all the records with duplicates in db2. I have looked at various answers but they only remove the duplicates leaving one record from that set in the table. This is what I found already.

DELETE FROM
    (SELECT ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN
     FROM SESSION.TEST) AS A
WHERE RN > 1;

But, I need a query that will remove all the records that contain duplicates not leaving behind one of them in the table.

A      A       1  <-- delete this
 A      A       2  <-- delete this too
 B      B       3
 C      C       4

P.S: Using RN >= 1 does not work as it will make the table empty by deleting all records.

Assuming your desired uniqueness constraint is (one,two), here is one way to do it:

DELETE FROM session.test WHERE (one, two) IN
(
   SELECT one, two FROM session.test
   GROUP BY one, two
   HAVING COUNT(*) > 1
)


Backing up my SQL Server System Databases I'm trying to create a recurring scheduled job to fully backup the master, msdb and model databases, I'm using the Maintenance Plans for SQL Server 2008 R2, in the "Specific Databases to choose from the Backup DB Task Window" I wasn't sure if I should choose [System Databases] or [These databases: master, model, msdb]? Aren't they the same if I specifically check mark the specific databases or just choose the radio button for all system databases?
If I choose to create them under the SQL Agent, would that be the same results? as I just need to create a daily or weekly backups for them.

If you just want to back up the system databases, you should use the "System Databases" group in the drop down menu as opposed to individually selecting the system databases.

If you have not looked at the Ola Hallengren SQL Maintenance solution, I would recommend taking a peek as opposed to using the SQL Server maintenance plans.

More info about maintenance plans: TN Maintenance plans


PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure Could I create a VIEW from the JSON data type but have it as a relational structure?

Example:

SELECT *
FROM json_test;

Results

[
    {
        "name": "Roy",
        "Country": "USA",
        "hobby": "Swim",
        "address": "Church Street",
        "sex": "M"
    },
    {
        "name": "Roy",
        "Country": "USA",
        "hobby": "Cricket",
        "address": "Amsterdam",
        "sex": "F"
    },
    {
        "name": "Anam",
        "country": "Greece",
        "hobby": "Polo",
        "address": "MG Road",
        "sex": "M"
    }
]

Then to create the VIEW would be something like (not sure if/how to do this)

CREATE VIEW normalized AS
SELECT name, country, hobby, address, sex
FROM JSON data

Then I could query the view with something like this

SELECT *
FROM normalized

Result set

name  | country | hobby   | address       | sex
------+---------+---------+---------------+----
 Roy  | USA     | Swim    | Church Street | M
 Roy  | USA     | Cricket | Amsterdam     | F
 Anam | Greece  | Polo    | MG Road       | M
(3 rows)

As you have multiple rows inside the JSON column, you need a function that returns a set. This can be done using the json_to_recordset() function:

select j.*
from json_test,
     json_to_recordset(json_data) as j(name text, country text, hobby text, address text, sex text);

Because this is an anonymous record type, you must explicitly define each column. Probably not a big thing if you want to put that into a view. Note that your sample data would not return a value for the Country keys in your JSON data, only for the one named country because the matching between column names and JSON keys is case-sensitive.

If you don't want to create a view, you can shorten the above using a custom type:

create type person_t as (name text, country text, hobby text, address text, sex text);

select j.*
from json_test,
     json_populate_recordset(null::person_t, json_data) as j;

SQLFiddle: http://sqlfiddle.com/#!15/91b85/1


Why does my INSERT fail on SELECT permissions when I have change tracking enabled? I have a user in a SQL Server 2014 database that only has permissions to insert into a table (no select permissions). When change tracking is enabled on the table, the inserts will fail with The SELECT permission was denied on the object ...

Why is this the case?

Steps to reproduce:

USE [master];
CREATE DATABASE insert_test;
GO
USE insert_test;
GO
CREATE TABLE dbo.test(id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, value NVARCHAR(50));
ALTER DATABASE insert_test SET CHANGE_TRACKING=ON;
ALTER TABLE dbo.test ENABLE CHANGE_TRACKING;
GO
CREATE LOGIN test_user WITH PASSWORD='FD3nIk1p(4$LKH!eSY';
CREATE USER test_user FOR LOGIN test_user;
GRANT INSERT ON dbo.test TO test_user;
GO
EXECUTE AS USER='test_user';
INSERT INTO dbo.test(value) VALUES(N'hello');  -- Fails with: The SELECT permission was denied on the object 'test', database 'insert_test', schema 'dbo'

/*
-- Cleanup
USE [master];
GO
DROP DATABASE insert_test;
GO
DROP LOGIN test_user;
GO
*/

Change tracking operations are running within the user's context, and in order to track what is being changed it has to read the row using a SELECT before it is changed.


PostgreSQL : Is this because of locks(row level)? I have a table with approximately 2.1 million tuples. Which has latitude and longitude columns. I am trying to convert this into a geographic type (point with SRID).

The function(procedure) I have written, works fine when I limit the entries (say : SELECT id,longitude,latitude FROM list_of_location limit 50).

CREATE OR REPLACE FUNCTION convertlatlon() RETURNS VOID AS $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id,longitude,latitude FROM list_of_location
    LOOP
    UPDATE list_of_location SET location= concat('SRID=4326;POINT(',rec.longitude,' ',rec.latitude,')') WHERE id=rec.id;
    END LOOP;
END;
$$ LANGUAGE 'plpgsql' ;

When I try to run it on the entire table, PostgreSQL seems to do nothing. Have waited for an hour and a half.
Consumes 99% of CPU on the core it is running.
Does not spring any other instance of PostgreSQL to utilize other cores(since the request is from a single user?).
Is this because of locks(row level)?
How to circumvent this?

Running a DML statement inside a loop is never a good idea. You are multiplying the amount of work to be done. Relational databases are best when operating on sets, when you do a loop you are operating on a single row at a time.

You can achieve the same by doing the update in a single statement:

UPDATE list_of_location
   SET location = ST_MakePoint(longitude,latitude)::geography;

I'm not 100% about the syntax for calculating the actual point, as I don't use geometric stuff, but I guess you'll get the idea.


Consistency errors in database table after upgrade to SQL Server 2012 I keep getting a weird problem with some databases after upgrade to SQL Server 2012 (either from SQL Server 2005 or from SQL Server 2008 R2).

The backup (.bak) is fine on older version. I do DBCC CHECKDB on SQL Server 2008 R2 and everything is fine. I create a .bak file, restored it on SQL Server 2012 and run DBCC CHECKDB -> 2 consistency errors in table.

Using suggestions from the article "Upgrading To SQL 2012: Ten Things You Don’t Want To Miss" I tried DBCC CHECKDB WITH DATA_PURITY; before and after upgrade.

Is this a know/common issue?

The error details are:

  Msg 8970, Level 16, State 1, Server NUX2012\S11, Line 1
  Row error: Object ID 277576027, index ID 2, partition ID 581141175926784, alloc unit ID 581141175926784 (type In-row data), page ID (1:258), row ID 1. Column 'ID' was created NOT NULL, but is NULL in the row.

  Msg 8970, Level 16, State 1, Server NUX2012\S11, Line 1
  Row error: Object ID 277576027, index ID 2, partition ID 581141175926784, alloc unit ID 581141175926784 (type In-row data), page ID (1:258), row ID 2. Column 'ID' was created NOT NULL, but is NULL in the row.

Obviously I checked the table in question and the ID column doesn't have any rows with NULL values.

Hm... That's interesting...

So the index is somehow corrupted before the upgrade and when you see an error as above you can both fix this before and after upgrading, but only after upgrading DBCC will detect the problem.

Steps:

Figure out which index is failing

Run DBCC CHECKDB to get a column name.
Find index that spans over this column (for me it was a primary key).

Disable index: ALTER INDEX [my index name] ON [my table name] DISABLE; (some foreign keys might be disable along with it, don't worry about that).
Rebuild/re-enable index: ALTER INDEX [my index name] ON [my table name] REBUILD;.
Run DBCC CHECKDB; to make sure everything is fine.

That worked for me but YMMV. Again, you can do above in MS SQL 2008 R2 and then restore fresh backup on MS SQL 2012 or do above when already on MS SQL 2012.


Space Consumption for SQL Index Rebuild I am attempting to rebuild the indexes for a single table on a SQL Server 2005 reporting database.  The indexes are heavily fragmented as this has not been done for a long time.  I am running the following query...

ALTER INDEX ALL ON Table_A REBUILD

Before building the index, the data is consuming 32 GB and the indexes 98 GB.  I am unable to finish the rebuild as I am running out of space once the rebuild consumes an additional 80 GB.

Is there any way to determine how much space I will need to rebuild the indexes on this table (and eventually all tables within my db)?

Typical wisdom is 1.5x index size, plus a little fudge depending on fill factor and how full your current index pages are (in most cases I would expect it to be slightly less than 1.5x). But I would definitely not cut it close. If you don't have 150GB free on your drive, I would find a way to do it elsewhere. I don't know if WITH DROP_EXISTING will help here, somehow I don't think so.


Should the value of a select max be indexed when the where clause is already indexed I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company (every company has their own invoice counting).

When I create a new customer_order, I have this query to get the largest invoice number for that company and increment it.

select max(invoice) from customer_orders where customer_id = :customer_id

because customer_id already has an index is there any point in adding an index to invoice?

this database is on MySQL in production and sqlite3 for testing & development (ruby on rails).

An index on (invoice) is going to typically be fairly meaningless in your case since there are many invoices with the same numbers and an invoice number by itself is almost completely meaningless in your design without knowing which customer it belongs to ... so you should have a UNIQUE index on (customer_id,invoice).

ALTER TABLE customer_orders ADD UNIQUE KEY (customer_id,invoice); -- MySQL syntax

This declaration creates both an index and a constraint -- it allows the server to quickly go to the appropriate customer_id and then quickly go to the largest value associated with that customer ID, all using the new index... and it also constrains you from inadvertently duplicating an invoice number for any reason -- the database will refuse to allow a given customer_id to have two rows with the same value for 'invoice' but will still allow the 'invoice' value to be reused on different customers, which sounds like what you need.

<digression>
If production is on MySQL, I would recommend development and testing on MySQL as well.  Supporting more DBMSs than you need to is likely to be more trouble than it's worth and you could encounter surprises in production if there are cases where SQLite and MySQL behave differently.  They are certainly unlikely to optimize queries uniformly, and if you haven't tested with the same underlying database as is used in production, then I would suggest that you really haven't "tested."
</digression>


Multi query with group by , join them in different columns One table has different columns for each column I want to do a group by function. I get unique values for each column in the table.

Origin Table data:

+----+--------------+--------------+
| id | customername | amazonnumber |
+----+--------------+--------------+
|  1 | Mark         |         1122 |
|  2 | Pieter       |         1122 |
|  3 | Jean         |         1122 |
|  4 | Jean         |         1122 |
|  5 | Janis        |         1123 |
|  6 | Janis        |         1123 |
+----+--------------+--------------+

At this moment i do this:

SELECT customername FROM tablegroup  group by customername;
SELECT amazonnumber FROM tablegroup  group by amazonnumber;

This is great i get for each column the unique value.

I want to combine them so i get all the data vertical in columns.
I have tried this but, this give not the right results:

SELECT * FROM
(SELECT customername FROM tablegroup  group by customername) b
 CROSS JOIN
(SELECT amazonnumber FROM tablegroup  group by amazonnumber) c;

This is the wrong result:

+---------------+--------------+
| customername  | amazonnumber |
+---------------+--------------+
| Mark          |        11222 |
| Pieter        |        11222 |
| Jean          |        11222 |
| Janis         |        11222 |
+---------------+--------------+

End Result:

+---------------+--------------+
| customername  | amazonnumber |
+---------------+--------------+
| Mark          |        11222 |
| Pieter        |        11223 |
| Jean          |              |
| Janis         |              |
+---------------+--------------+

For MySQL 8+:

WITH RECURSIVE
cte1 AS ( SELECT customername, ROW_NUMBER() OVER () rn
          FROM tablegroup
          GROUP BY customername
        ),
cte2 AS ( SELECT amazonnumber, ROW_NUMBER() OVER () rn
          FROM tablegroup
          GROUP BY amazonnumber
        ),
cte3 AS ( SELECT 1 num
          UNION ALL
          SELECT num+1
          FROM cte3
          WHERE num < ( SELECT GREATEST(cnt1, cnt2)
                        FROM ( SELECT COUNT(*) cnt1
                               FROM cte1
                             ),
                             ( SELECT COUNT(*) cnt2
                               FROM cte2
                             )
                      )
        )
SELECT cte1.customername, cte2.amazonnumber
FROM cte3
LEFT JOIN cte1 ON cte3.num = cte1.rn
LEFT JOIN cte2 ON cte3.num = cte2.rn


Can MySQL 5.1 read a MySQL 5.5 MyISAM file? We're in the middle of an upgrade from mysql 5.1 to mysql 5.5. Our only remaining holdout is the master, at 5.1, while all the slaves are at 5.5.

However, we have a stats process that runs on an admin slave, which rsyncs the myisam files to the master, who mount it in a stats DB, then imports those stats into the main production db.

All the tables use the default row_format for myisam, which is static. We're only using simple column types: int, varchar, enum, datetime.

We've already run this process, and it didn't fall down, which is encouraging. But I'm concerned with errors that didn't make it into the log.

So the question is: what are the complications of this process? The MyISAM file is created by a 5.5 instance, then moved to a 5.1 instance.

What's going to go wrong?

(This is a duplicate post to https://stackoverflow.com/questions/9415715/can-mysql-5-1-read-a-mysql-5-5-myisam-file, where there was no detailed answer available.)

What could go wrong? If the way MyISAM changed the format of how something is stored in their files, it will break on 5.1. This will render your stats database on the master corrupt and unusable.

Even if the initial load went fine, it doesn't mean something won't come along later with the result listed above...a corrupt database. It's a risk, and if it can be avoided, I personally would do so.

And in your case, it can be avoided by dumping the data using mysqldump quickly and then load into the 5.1 instance.


starting pgbouncer on startup I installed pgbouncer and configured it and it starts only via using this command

pgbouncer /etc/pgbouncer/pgbouncer.ini

however using service pgbouncer start it dose not start. I then set up supervior with this settings to mannage it which works fine.

[program:pgbouncer]
command=pgbouncer /etc/pgbouncer/pgbouncer.ini
stdout_logfile=/var/log/supervisor/pg_bouncer.log
stderr_logfile=/var/log/supervisor/pg_bouncer_error.log
autostart=true
stopsignal=QUIT
redirect_stderr=true
directory=/etc/pgbouncer/
user=postgres
group=postgres
preload_app = True

when i reboot the system pgbouncer dose not work,looking into the pgbouncer log here is what the error says

WARNING Cannot listen on ::/6432: bind(): Address already in use
WARNING Cannot listen on unix:/var/run/postgresql/.s.PGSQL.6432: bind(): No such file or directory
FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file

and looking into supervisor log this is what it says

LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 130
LOG listening on 0.0.0.0:6432
WARNING Cannot listen on ::/6432: bind(): Address already in use
WARNING Cannot listen on unix:/var/run/postgresql/.s.PGSQL.6432: bind(): No such file or directory
FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file or directory

can any one help out with this please

It turns out that I was putting my pidfile in /var/run which is fine for running it but when server is rebooted the file is deleted and pgbouncer can't find the file and it gives the this error

FATAL @src/main.c:553 in function write_pidfile(): /var/run/pgbouncer/pgbouncer.pid: No such file or directory

So simply moving the file to another location and chown the directory was all that was needed

$ sudo mkdir ~/pgbouncer
$ sudo chown -R postgres:postgres ~/pgbouncer


MySQL vs. MariaDB Binary Logging Difference I've been doing some testing with both MySQL and MariaDB and I've noticed a big difference with regards to the growth of binary logs - I can't find out much about this online.

I have a test database, and I have written a script which simulates transactional activity (selects, updates, inserts, deletes etc) with the aim of doing some simple load testing and later, for testing point in time recovery procedures.

What I've noticed is that on MySQL running my automated script for half an hour causes about 400MB of binary logs.

When I do the same thing on MariaDB, I only get around 2MB of binary logs.

All settings are the same, ie.

Max Binlog Size: 100MB
Buffer Pool: 2GB max

MySQL is running v 5.7 and Mariadb is running 10.3 - both are running in Docker on my local laptop.

I've also noticed that MariaDB is a little bit slicker performance wise, I get more query throughput in half an hour than I do with MySQL.

Obviously the big difference here is with binary log size. The smaller size of MariaDB means I can roll forward on the binary log much quicker for point in time recovery than I can on MySQL.

Is there any documented evidence out there to support this behaviour? Is MariaDB more efficient than MySQL when looking at binary logs and recovery?

Are there some hidden/non-obvious variables I should be checking that may be having a big impact on this testing?

Any help understanding this would be great.

There are many differences in default values for system variables between MariaDB 10.3 and MySQL 5.7, so that can explain both some of the performance and binlog size differences. Additionally, MariaDB 10.3 is of course newer code, being the most recent major MariaDB release at the moment, whereas MySQL 5.7 is not the most recent MySQL major version since the release of 8.0.

For example, the default value for binlog_format is different between MySQL 5.7: ROW and MariaDB 10.3: MIXED. MIXED means statement-based in most cases, and statement-based binlog format has the advantage of less data written to log files:

  When updates or deletes affect many
  rows, this results in much less storage space required for log files.
  This also means that taking and restoring from backups can be
  accomplished more quickly.

MariaDB also has log_bin_compress, see Compressing Events to Reduce Size of the Binary Log, though this is disabled by default.


Migrate 7TB of Database for Colo Migration We are going to migrate our data center from one state to another in next 3 months. I wanted to know, what is the best possible way to migrate one of our biggest database (i.e. ~7TB in size) as quickly as possible. Here is the information on the server and database:

Existing Data-Center: Georgia
New Data-Center: New Jersey
Server OS: Win 2008 R2 Datacenter
SQL Server: 2008 SP1 Enterprise Edition
Data Storage: XIO System (Which we are not going to get it at new data-center)
Database Size: ~7TB
How data gets into this database: via Transactional Replication from other 8 cluster servers.
How 8 cluster is getting data: via Merge Replication from all our 2000 store servers every 30mins.

We will have a network pipe between our existing and new data-center, but it is not going to be that big. It is also going to get consumed by all the VM's our infrastructure team is going to transfer on daily basis.

We ran a Full Backup (i.e. Ideara SQLSafe with iSpeed settings) on this ~7TB of database, it took ~9 hours and size was ~630 GB. We checked with the vendor who is managing our colo migration and network pipe between two data-center and they said, it won't be possible to Copy over 640 GB file faster to new data-center and we would have to do Full + Differential + TLOG to get up to date.

Our issue is, data we are getting from all our store systems every 30 mins (via Merge Replication to Home Office), it generates lot of data change. If we try to do Full + Differential + TLog, we will be having same sizing issue and lot of downtime to copy over Differential and also we would have to deal with the Log file getting full, which we have limited space on our existing database server (as we are not getting XIO Storage anymore).
We were thinking about SAN Replication, but, as our SAN is going to be different at the new data-center, that won't work for us.

What approach you guys think we should take to migrate this ~7 TB of database from our existing data-center to new data-center with minimum downtime?

**Note:Please let me know if I miss anything from the information side.

Thank you,

HP

One could write a book on this topic, but one option would be an out-of-band initial replication of your database to the new site by flying a copy of the server hard drive(s) to New Jersey, followed by log shipping until you catch up, followed by setting up your "transnational" replication to target both old and new sites until you're ready to switch.


Does SQL Server Support Parallel Bulk Inserts From Multiple Connections? Does SQL Server support parallel bulk inserts into the same database table?
Some clarifications for my particular use case:

The table has a clustered index composed of two distinct index keys, and two non-clustered indexes, each one consisting of a single index key.
Parallel here means from multiple, distinct connections.  Each connection is a unique sqlalchemy.orm.session object, and each Session is inserting 20k records of data at the same time using the Session.bulk_insert_mappings() method.

The specific questions I have:

I am trying to understand what is happening under the hood on the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the inserts performed at the same time, in parallel?
We are also assuming that no two distinct connections will be attempting to insert the same records (based on Primary Key), BUT what if two of the parallel connections WERE trying to insert records that would cause a PK violation?
Are the answers to the questions above different if we are asking for SQL Server 2008 vs 2017?
Does Microsoft have any sort of documentation on this?

Does Microsoft have any sort of documentation on this?

Yes, lots. One particularly good reference is the Data Loading Performance Guide.

I am trying to understand what is happening under the hood on the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the inserts performed at the same time, in parallel?

There's nothing particularly special about bulk inserts. There isn't a special queue, or anything like that. Each is processed as a normal command, which will either proceed or be blocked based on concurrent locking activity. To be clear: yes concurrent bulk imports to the same clustered table from different server connections are possible.

We are also assuming that no two distinct connections will be attempting to insert the same records (based on Primary Key), BUT what if two of the parallel connections WERE trying to insert records that would cause a PK violation?

If two connections attempt to insert the same records into a unique index, one will be blocked behind the other. When the first one releases its exclusive lock, the second will throw a uniqueness violation error (unless esoteric options like IGNORE_DUP_KEY are present on the target index).

Are the answers to the questions above different if we are asking for SQL Server 2008 vs 2017?

Not materially different. SQL Server 2016 and later can achieve minimally-logged bulk inserts to an index without trace flag 610.

It can be tricky to achieve truly concurrent bulk inserts to a table with one (or more) b-tree indexes in practice. You may need to disable lock escalation for the target and/or take other actions. It is a more complex undertaking when several indexes are present. See the Data Loading Performance Guide for more details.


Non-Admin access to SQL Server Configuration Manager Is there a way that access can be granted for a non-admin user for SQL Server Configuration Manager?

There is but it would be a bit painful to implement and might be granting more access than you think.

It will depend on what level of access you want (read only, or ability to manage services). This is mostly a guess for SSCM but in Window Server 2003 you used to use "SUBINACL" command line to grant access. I believe this is still availble in higher OS but you would need to check.

Outside of that just specific to SSCM, this uses WMI calls in the background so at most you are going to need permissions to execute WMI calls. Understand that this is not just specific to SSCM so granting access to this may give more permissions that is needed.

I am also aware that the old "sc" command was used to grant permissions to services so that might also be used as well. This was required when WMI calls did not work properly and services did not show up in SSCM like they should.


How do you combine the input of three parameters into one? I'm working on a query that will run via C# to SQL server. I'm trying to make the query flexible and provide parameters for all the inputs.

Here is the query:

SELECT
  Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project,
  Test_Matrix.tblTestMatrix.Request,
  Test_Matrix.tblTestMatrix.Release,
  Test_Matrix.tblTestMatrix.TestName,
  Test_Matrix.tblTestMatrix.Formulation,
  Test_Matrix.tblTestMatrix.Container,
  Test_Matrix.tblTestMatrix.Closure,
  Test_Matrix.tblTestMatrix.Shipper,
  Test_Matrix.tblTestMatrix.Label_Front,
  Test_Matrix.tblTestMatrix.Label_Back,
  Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other,Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM
Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix
  ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber
  AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request
  AND Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE  (Test_Matrix.tblTestMatrix.Formulation = 'N5200-500') OR
       (Test_Matrix.tblTestMatrix.Container = '') OR
       (Test_Matrix.tblTestMatrix.Closure = '') OR
       (Test_Matrix.tblTestMatrix.Shipper = '') OR
       (Test_Matrix.tblTestMatrix.Label_Front = '') OR
       (Test_Matrix.tblTestMatrix.Label_Back = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release

When I run this in SSMS it returns data but when looking at it in the query designer I see that the three "other" components that I'm trying to combine into one field name "Other" does not appear to be correct.

I would appreciate some comments and suggestions on what I can do to combine these three input parameters into one output field.

I want this query, when the or case is selected and just one formulation N'9870-532' is selected to return only that formulation and of course the other components relating to that formulation.

Query:

SELECT
Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release, Test_Matrix.tblTestMatrix.TestName, Test_Matrix.tblTestMatrix.Formulation,
Test_Matrix.tblTestMatrix.Container, Test_Matrix.tblTestMatrix.Closure, Test_Matrix.tblTestMatrix.Shipper, Test_Matrix.tblTestMatrix.Label_Front, Test_Matrix.tblTestMatrix.Label_Back,
Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other, Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request AND
Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE (Test_Matrix.tblTestMatrix.Formulation = N'9870-532') OR
      (Test_Matrix.tblTestMatrix.Container = '') OR
      (Test_Matrix.tblTestMatrix.Closure = '') OR
      (Test_Matrix.tblTestMatrix.Shipper = '') OR
      (Test_Matrix.tblTestMatrix.Label_Front = '') OR
      (Test_Matrix.tblTestMatrix.Label_Back = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release

Results:

The return is not containing only Formulation 9870-532 and other associated items, seen above you have 9670-532 as a formulation item.

Note: I answered instead of comment because a comment was too long.


Half of the partitions are empty on a KEY partition We are trying to move data from one table that currently has over 1B rows to a partitioned table.
But as we started to do so we notice that half of the partitions are empty, why is that?
Table is partitioned by the column that has UUIDv4 values.
Our table definition:
CREATE TABLE table1 (
  `col1` varchar(36) NOT NULL DEFAULT '',
  `col2` varchar(256) NOT NULL DEFAULT '',
  `col3` datetime NOT NULL DEFAULT NOW(),
  UNIQUE KEY `unq_col1_col2 (`col1`,`col2`),
  KEY `idx_col3` (`col3`)
)
ENGINE=InnoDB
PARTITION BY KEY(col1)
PARTITIONS 100;

INFORMATION_SCHEMA.PARTITIONS output:
PARTITION_NAME  TABLE_ROWS
p0  136532
p1  0
p2  339523
p3  0
p4  156955
p5  0
p6  305700
p7  0
p8  209548
p9  0
p10 329291
p11 0
p12 152995
p13 0
p14 388600
p15 0
p16 251903
p17 0
p18 364532
p19 0
p20 200799
p21 0
......
p89 0
p90 303628
p91 0
p92 215546
p93 0
p94 399165
p95 0
p96 210364
p97 0
p98 318675
p99 0

We are using Google Cloud SQL (MySQL) which is currently on version 5.7.32-google-log.

You have 100 partitions. So it's likely that your hash values modulus 100 are all even numbers.
This is a general risk of hashing. If the hashing function produces only values with a pattern like this, you can get imbalanced partitions.
One way to fix this is to use a prime number for the number of partitions. For example 101 is a prime number close to your desired number of partitions.
This helps because if the hash values modulus 100 produced a pattern, that pattern will be offset by 1 per window of 100 values. Thus it will be spread more evenly over the partitions.


How does MySQL Workbench decide what number to put in parentheses after a datatype? I create columns in MySQL Workbench and it often puts different numbers in parentheses after datatype, e.g. I input this:

Column Name          Datatype:
id                   INT
account_parameters   INT
accounts             TINYINT
account_properties   TINYINT

and after executing the query I get

Column Name          Datatype:
id                   INT(10)
account_parameters   INT(11)
accounts             TINYINT(1)
account_properties   TINYINT(2)

Why does it do it? Is there a reason why one should be INT(11), while others are INT(10)?

In this case, the difference is likely due to the int and tinyint being signed (default) or unsigned in their definitions. Note that the numbers in the parentheses are largely irrelevant for most applications (they define a display width).


Columnstore Index maintenance and finding fragmentation We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a rebuild reduces the table size to a third and improves 10% performance.

Besides checking sys.column_store_row_groups for deleted_rows, is there any other indicator for columnstore indexes fragmentation we can use to identify which partitions/tables should be rebuilt?

My production experience with columnstore indexes is on SQL Server 2016 and later versions but I believe everything in this answer also applies to SQL Server 2014. The simplest answer is that you can look at the ratio of row count and space used by each partition if you prefer to not use the sys.column_store_row_groups dmv. The more complicated answer is that there are many different types of possible fragmentation for a columnstore index. The most important ones to you depend on your data and workload.

Compressed deleted rows - this is the one that you mentioned in your question. The deleted rows take up space and serve no useful purpose.

Rows in delta rowgroups - these are rows that haven't been compressed (yet) - if you end up with too many you can see an impact to query performance.

Compressed rowgroups not of the maximum size (1048576 rows) - for a variety of reasons you can end up with compressed rowgroups with a row count less than 1048576. Microsoft claims that tables typically get the best compression if the rowgroup row count is maximized. In my experience this varies depending on the data. I see no measurable difference with our data model.

Misaligned segments - you may be loading your data in an order to get highly selective rowgroup elimination on key columns. If data ends up out of the preferred order, such as after a maintenance operation, then it can be considered to be fragmented.


How to solve "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" error? I am trying to create connection with the database for debugging ASP.Net website. Database is 11g Oracle DB... There is no problem when I connect using SQL Developer, however using Visual Studio I get the error of ORA-12514 (TNS:listener does not currently know of service requested in connect descriptor). 11g ODAC for Visual Studio 2010 has been configured as well.  There are similar questions about this error, I could not find solution for my problem.

Thanks in advance for the help!!!

I do not have any clear explanation for this problem solution. However I used SID instead of service name in connection string and it worked.

Edit: A service name is more flexible than a SID would be. A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the same service name.A database on the other hand has a single SID. And a single SID goes to a single database. So I would say it is safe to use SID rather than service name.


Database Design Dilema I am building a fleet unit management software which handles data from GPS devices mounted on the clients' vehicles.

Now i have to implement the database design. I have two main tables.

table 1: Fleet_Unit(unit_id, col_2, col_3, col_4), unit_id is PK

table 2: Gps_Device(device_id, col_2, col_3, col_4), device_id is PK

In most scenarious only one gps device is mounted on one vehicle but there are cases when two or even more gps devices may be mounted on the same vehicle for various reasons. So i have to decide if i should have an extra table to map the vehicles to the gps devices or put an extra column inside the Gps_Device table where the ID of the Fleet_Unit table will be stored.

I decided to use the first approach so i made one more table:

Fleet_unit_gps_device_mapping(fleet_unit_id, gps_device_id).

So before i ask you for opinion let me introduce you few more tables that are used to describe the fleet_unit object.

Each gps device has some analog and digital I/O. There may be one or more fuel meters connected to the gps device and vehicle's engine, temp sensors, card readers and so on.

So here is ONE of the extra tables (the others are similar):

fleet_unit_digital_sensors_mapping(fleet_unit_id, gps_device_id, sensor_col_1, sensor_col_2)

As i mentioned there may be more than one gps device mounted on the same vehicle so digital-sensor-1 which is read from first gps device is different from digital-sensor-1 which is read from the second gps device.

Now i'd like to ask you how to implement the database.

Should i use fleet_unit_id and gps_device_id from table fleet_unit_digital_sensors_mapping as foreign keys for tables Fleet_Unit and Gps_Device

or

create a new column master_id which is "combination" of fleet_unit_id and gps_device_id and connect it to the mapping table - Fleet_unit_gps_device_mapping where i need to introduce another column used for the map.

The reason I'm asking is because in first scenario if i have 10+ tables that describe the vehicle i have to connect each one with the fleet_unit and gps_device table. In second case i simply connect all 10+ tables with the table that maps the vehicle and the gps devices.

Thanks in advance.

I would add Fleet_Unit.unit_id to the GPS_Device table as a FK. I would use a surrogate key as the PK, and put a unique constraint on device_ID.


T-SQL script to check if index rebuild operations are online are off I wanted to see if it was possible to determine whether my indexes are rebuilt online or offline with some T-SQL. I'm currently trying to troubleshoot extra disk space used during a rebuild procedure and I thought this may me a good place to start.

Referencing the information in Capture Index Maintenance Operations, you might be able to use Extended Events to capture online index operations.

From that post:

Create the Extended Event

Note that this example is using the Ring Buffer, so you may want to pick a different target.  The example is also looking for database_id = 5, so adjust accordingly

-- Create the Event Session
IF EXISTS (
        SELECT *
        FROM sys.server_event_sessions
        WHERE NAME = 'OnlineIXOps'
        )
    DROP EVENT SESSION OnlineIXOps ON SERVER;
GO

CREATE EVENT SESSION OnlineIXOps ON SERVER
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    /* The online index operations */
    --ADD EVENT sqlserver.progress_report_online_index_operation
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    ADD EVENT sqlserver.progress_report_online_index_operation (
    ACTION(sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text, sqlserver.session_id)
    --Change this to match the database in question,
    WHERE sqlserver.database_id = 5
    ) ADD TARGET package0.ring_buffer
    WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

-- Start the Event Session
ALTER EVENT SESSION OnlineIXOps ON SERVER STATE = START;
GO

Now, run some kind of online index operation.  From the post:

ALTER INDEX IX_PolicyUserRole ON PolicyUserRole REBUILD PARTITION = ALL WITH (online = ON)

Having rebuilt that index online, you can then query the Event session with tsql using a query similar to the following:

SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
    ,event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS TIMESTAMP
    ,event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(max)') AS DBName
    ,event_data.value('(event/data[@name="object_name"]/value)[1]', 'varchar(max)') AS ObjName
    ,event_data.value('(event/data[@name="index_name"]/value)[1]', 'varchar(max)') AS index_name
    ,event_data.value('(event/data[@name="partition_number"]/value)[1]', 'varchar(max)') AS PartitionNumber
    ,event_data.value('(event/action[@name="session_id"]/value)[1]', 'varchar(max)') AS SessionID
    ,event_data.value('(event/data[@name="build_stage"]/value)[1]', 'varchar(max)') AS Build_Stage
    ,event_data.value('(event/data[@name="build_stage"]/text)[1]', 'varchar(max)') AS BuildStage_Description
    ,event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(max)') AS Client_hostName
    ,event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(max)') AS Client_AppName
    ,event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
    ,event_data.value('(event/data[@name="duration"]/value)[1]', 'Decimal(18,2)') / 1000 AS Duration_ms
    ,event_data.value('(event/data[@name="rows_inserted"]/value)[1]', 'varchar(max)') AS rows_inserted
FROM (
    SELECT evnt.query('.') AS event_data
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_sessions AS s
        INNER JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address
        WHERE s.NAME = 'OnlineIXOps'
            AND t.target_name = 'ring_buffer'
        ) AS tab
    CROSS APPLY TargetData.nodes('RingBufferTarget/event') AS split(evnt)
    ) AS evts(event_data)
ORDER BY TIMESTAMP
    ,Build_Stage

When  I tested a non-online rebuild, there we no Extended Events captured.