SQL query to find all parent records where child records are a specific set An Item has many ItemDetails. An ItemDetail has fields of "type", "value" and "item_id".

I need to find all Items if and only if item has exact ItemDetails which are restricted by some changeable conditions.
For example I need to find all Items with ItemDetails of (type=10, value=1000) and (type=20 and value=2000)

My first solution was like this:

select p.*
from item p
where not exists
    (
      select c.id from item_detail c
      where c.item_id=p.id
      and (c.type<>10 or c.value<>1000)
      and (c.type<>20 or c.value<>2000)
    );
-- Execution Time: 17.819 ms

But I realised that it was fetching items with only one ItemDetail(type=10, value=1000).
Then I found this question and changed query like below.

select p.*
from item p
where not exists
    (
      select c.id from item_detail c
      where c.item_id=p.id
      and (c.type<>10 or c.value<>1000)
      and (c.type<>20 or c.value<>2000)
    )
and 2 = (
  select count(c.item_id) from item_detail c
  where c.item_id=p.id);
-- Execution Time: 2426.596 ms

But second subquery is causing performance problem. Execution time for first query is 5 ms, but for second it is 800 ms.
Is there a better way of doing this?

I am using PostgreSQL 9.5.

Here is the fiddle for it.

Edit-1: Here is fiddle example for those who cannot reach it:

CREATE TABLE public.item
(
  id integer NOT NULL,
  name  character varying(10) NOT NULL,
  CONSTRAINT item_pkey PRIMARY KEY (id)
);

CREATE TABLE public.item_detail
(
  id bigint NOT NULL,
  item_id integer NOT NULL,
  type  integer NOT NULL,
  value integer NOT NULL,
  CONSTRAINT item_detail_pkey PRIMARY KEY (id),
  CONSTRAINT fk_item_id FOREIGN KEY (item_id)
      REFERENCES item (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uq_item_type_value UNIQUE (item_id, type, value)
);

INSERT INTO public.item VALUES (1, 'Item1'),(2, 'Item2'),(3, 'Item3'),(4, 'Item4'),(5, 'Item5');

INSERT INTO public.item_detail
VALUES
  (1,1,10,1000),
  (2,1,20,2000),
  (3,2,10,1000),
  (4,3,10,1000),
  (5,3,20,2000),
  (6,3,30,3000),
  (7,4,10,1000),
  (8,4,10,1500);

Edit-2: I came up with a solution like this by choosing the best option for me from this source given by ypercubeᵀᴹ.

SELECT p.* FROM item p
WHERE EXISTS (SELECT item_id FROM item_detail
                WHERE item_id = p.id AND (type, value) = (10, 1000))
AND   EXISTS (SELECT item_id FROM item_detail
                WHERE item_id = p.id AND (type, value) = (20, 2000))
AND NOT EXISTS (SELECT item_id FROM item_detail
                WHERE item_id = p.id AND (type, value) NOT IN ((10, 1000), (20,2000)));
-- Execution Time: 0.984 ms

The problem we are trying to solve is called exact relational division. It requires an extra condition from the relational division and there are still a lot (too many actually) ways to solve this kind of problem.

Check this related SO question where you will find more than 10 different ways to do this in Postgres and performance tests. The problem discussed there is (simple, not exact) relational division so you'll have to adjust the answers:

How to filter SQL results in a has-many-through relation

Here is another one:

select p.*
from item p
  join
    ( select item_id from item_detail
      where (type, value) = (10, 1000)
      intersect
      select item_id from item_detail
      where (type, value) = (20, 2000)
      except
      select item_id from item_detail
      where (type, value) not in ((10, 1000), (20, 2000))
    ) as c
  on c.item_id = p.id ;

I suggest an index on (type, value, item_id).


how to get list of months between two dates in db2 I have two dates start and end date i need find list of months between two dates.for example, 01-12-2015 as start date and 01-02-2015 as end date i need result to be as , January. I am using IBM db2. another thing i need to find these using select query i have no access to create function or procedures.

Your date format is ambiguous but given your expected answer is January, I'll assume your start date is 2015-01-02 and end date is 2015-01-12. The following recursive query should do what you need:

WITH months(i, d) AS (
    VALUES (1, DATE('2015-01-02'))

    UNION ALL

    SELECT i + 1, d + 1 MONTH
      FROM months
     WHERE i < 10000
       AND d + 1 MONTH <= DATE('2015-01-12')
)
SELECT monthname(d)
  FROM months

The "i" term in the recursive element is simply there to suppress the "possibly infinite" warning that occurs otherwise. If you're not bothered by the warning, you could just remove it.

This will work on DB2 for LUW and I think it'll work on DB2 for z/OS but that'll depend on whether they've finally added support for VALUES or not; if they haven't just use SELECT foo FROM SYSIBM.SYSDUMMY1 in the base case instead).


Creating pluggable database using dbca I have created a PDB using Database Configuration Assistant.Is there a way I can look at where the data files got created? I did not get the option in DBCA to specify my data file.

Thanks in advance

Here is one of the options:

Connect to the CDB and query cdb_data_files and cdb_pdbs data dictionary views

select file_name
  from cdb_data_files cdf
       join cdb_pdbs p
          on (cdf.con_id = p.con_id)
where p.pdb_name = '<container_name>'

Note: cdb_pdbs data dictionary view doesn't show root container - only pdbs associated with the current cdb. If there is a need to see all pdbs including cdb query v$containersview.


postgres pg_upgrade 9.2 to 9.5 problem I'm trying to upgrade from 9.2 to 9.5 using the pg_upgrade tool.

I'm using CentOS 7.0

current postgres bin folder: /opt/rh/postgresql92/root/usr/bin
new postgres bin folder: /opt/rh/rh-postgresql95/root/usr/bin
current data folder: /mnt/postgres/data
new data folder: /mnt/postgres/data95

I've set the LD_LIBRARY_PATH=/opt/rh/rh-postgresql95/root/usr/lib64

When I run

/opt/rh/rh-postgresql95/root/usr/bin/pg_upgrade --link --check --old-bindir=/opt/rh/postgresql92/root/usr/bin --new-bindir=/opt/rh/rh-postgresql95/root/usr/bin --old-datadir=/mnt/postgres/data --new-datadir=/mnt/postgres/data95

I get this message :

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   /opt/rh/postgresql92/root/usr/bin/pg_ctl: error while loading shared libraries: libpq.so.postgresql92-5: cannot open shared object file: No such file or directory

Could not get pg_ctl version data using "/opt/rh/postgresql92/root/usr/bin/pg_ctl" --version: No such file or directory
Failure, exiting

I'm not sure why it is trying to use the 9.2 version of pg_ctl

which pg_ctl

returns

/opt/rh/rh-postgresql95/root/usr/bin/pg_ctl

What have I done wrong?

The issue repeated for me on a legacy PostgreSQL 9.2 to 11 upgrade. The pg_upgrade would fail with: FATAL:  unrecognized configuration parameter "unix_socket_directory"

Blog entry at https://altinukshini.wordpress.com/2018/05/03/upgrade-postgres-9-2-to-9-6-in-centos-7-bold-lines/ gives the solution, ditto:

mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >>
   /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

Run now the pg_upgrade (as postgres user), the unix_socket_directories error should be gone.
Lastly, (as root) revert:
mv -f /usr/bin/pg_ctl{-orig,}


MongoDB stale replication, odd timestamp after data flush Right before my secondary goes stale, it first has this written in the log:

2017-04-25T06:48:02.991+0000 I STORAGE  [DataFileSync] flushing mmaps took 13530ms  for 3150 files

Then it gives me a odd stale message, note the "b" in the oldest available.

2017-04-25T06:50:03.815+0000 I REPL     [ReplicationExecutor] could not find member to sync from
2017-04-25T06:50:03.815+0000 E REPL     [rsBackgroundSync] too stale to catch up -- entering maintenance mode
2017-04-25T06:50:03.815+0000 I REPL     [rsBackgroundSync] our last optime : (term: 6, timestamp: Apr 25 06:32:08:333)
2017-04-25T06:50:03.815+0000 I REPL     [rsBackgroundSync] oldest available is (term: 6, timestamp: Apr 25 06:32:08:3b1)
2017-04-25T06:50:03.815+0000 I REPL     [rsBackgroundSync] See http://dochub.mongodb.org/core/resyncingaverystalereplicasetmember

I am running mongodb 3.2.8. Is that b expected to be in there? Is this expected?

No, it's not expected be there. Timestamp values should be decimal, not hexadecimal and msec value 3b1 is bigger than 333.

You could "move" primary status from current node to another node and try to resync this stale node again, this time it would read that other node and hopefully it's opLog is in good condition.


Compress backup file using T-SQL Is it possible to compress a backup file to .zip (or another) in a stored procedure invoked from a maintenance plan?

My maintenance plan contains these steps:

check DB (consistency, re_index, maybe not important)
full backup
to check backup file by using restore_verifyonly
shrink database

After restore_verifyonly/shrink database I want to compress this file.

Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.

When creating a backup, you can specify the WITH COMPRESSION keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.

For SQL2005 or older, the best way really (other than using a specific tool like RedGate) is to ensure that xp_cmdshell is enabled on the instances, and then use a command line to compact using for e.g. WinRar.

I use a cmd file that looks something like this:

@echo off
Set "winrarPath=C:\Program Files\WinRAR"
"%winrarPath%\winrar.exe" a -r "ZippedBackup.zip" "BackupFile.BAK"

You can then execute this cmd file from your 2000 / 2005 instances.  You can also play around with passing %1-type variables to the cmd file, if your filenames are not generic.


How do I deal with query getting slower when a select parameter is not specified inline? I faced this problem twice with SQL Azure and two different queries so far.

Something like this:

select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here'

runs super fast but equivalent code (generated from Linq-to-SQL)

declare @accountId uniqueidentifier;
set @accountId = 'actual-guid-typed-in-here'
select top(100) * from SomeView WHERE AccountId=@accountId

runs super slow and upon careful analysis I see that the engine uses the wrong index in the latter case. Note that the latter case would also arise if I moved my code into a stored procedure.

The two queries are identical and so I expect them to be executed identically but the database engine thinks otherwise.

What's the typical solution from this case?

If you look at the two execution plans you will see that the estimated cardinality is different in each case.  When the actual value is in the SELECT statement the optimiser can use the cardinality of that one, actual value to choose an access path.  When the SELECT has a variable the optimiser cannot know whether you have provided a very popular or very unpopular accountId so produces a 'best guess' plan.  Unfortunately for your situation this is awful.

Stored procedures (SPs) would be good.  Then you can put all the query hints you want in there.  Also use WITH RECOMPILE to get around parameter sniffing issues.  If you have good inexes already, and you don't have access to the source SQL or can't use SPs, Plan Guides may be a good option.  If you end up using them be sure to schedule a review every so often to ensure the chosen plan is still optimal.


Reorder data in one column based on data in another column I have the following table structure:

create table abc ( Name number , No number )

The data is like this:

Name | No
-----+-----
 100 |  11
 101 |  20
 102 |  12
 103 |  16
 104 |  13
 105 |  14
 106 |  15

I need to retain the exact values that are in the No column; I just need to modify the records the current values are associated with, so that the seq values are in the same order as the Name values.

Name | No
-----+-----
 100 |  11
 101 |  12
 102 |  13
 103 |  14
 104 |  15
 105 |  16
 106 |  20

I'm no pro on Oracle, but yo need something like this wrapped in an UPDATE statement,

SELECT *
FROM ( SELECT id, row_number() OVER (ORDER BY id) FROM dummy )
  AS t1(id, rn)
JOIN ( SELECT seq, row_number() OVER (ORDER BY seq) FROM dummy )
  AS t2(seq, rn)
USING (rn);

We look at the two columns id and seq

For id, we sort by id, and return the row number() and id
For seq, we sort by seq, and return the row_number() and seq

We have the same amount of rows for both, so we'll have the same amount of row_number(). Now we just join on row_number(). Tie-handling is undefined here.

You should probably rename seq to priority.

A priority can be static and global depending on the context. The value can matter, as in a priority queue or job scheduler where some priorities hold significance.
A seq by convention usually has gaps so the values don't matter, just the comparison (sometimes the uniqueness).

Here is one attempt at making this approach work in Oracle (dbfiddle):

UPDATE
  test tgt
SET
  seq =
  (
    SELECT sortedBySeq.seq
    FROM (SELECT test.*, ROW_NUMBER() OVER (ORDER BY Id  ASC) AS rn FROM test) sortedById
    JOIN (SELECT test.*, ROW_NUMBER() OVER (ORDER BY seq ASC) AS rn FROM test) sortedByseq
      ON sortedById.rn = sortedBySeq.rn
    WHERE sortedById.Id = tgt.Id
  )
;

Referencing the same table three times probably won't lend itself well to efficiency, but this is something to start with at least.


What strategy does MySQL optimizer use when choosing between two indexes? In question:

Optimize a query with two range conditions

the OP wanted to know if there were a way to optimize his query. In an answer given the query were rewritten as a LEFT JOIN, and that was the same thought that I had as well. However, that did not help, the execution plan remained the same.

table   type    possible_keys                 key
book    ref     author_id,publish_date,i0,i1  author_id

So I removed the index author_id and created a new index like:

  KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)

I was a bit surprised to see the new plan:

table   type    possible_keys                 key
book    ref     publish_date,i0,i1,i2         i0

why did it not choose i2 over i0 when it did choose author_id before? I got even more puzzled when I switched places in the table for i0 and i2:

PRIMARY KEY (`id`),
KEY `publish_date` (`publish_date`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`),
KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`)

Now all the sudden index i2 were chosen:

table   type    possible_keys                 key
book    ref     publish_date,i2,i0,i1         i2

Do I miss something or does the optimizer pick the first declared index that it benefits from even though there are better indexes declared later on?

With the following indexes and a query where predicates involve author_id, country, org, publish_date, price:

KEY `author_id` (`author_id`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)

I would expect the optimizer to choose i2 over author_id, but if I get it right it depends on the order they are defined.

Are these observations true in general or does it only happen for small tables?

Index selection is based on the estimated number of rows that will be accessed when using the different indexes.  The order the indexes is declared in should only make a difference if the estimated cost is the same for both indexes.  In your case, the three columns org, country, and author_id can be used for index lookup. Any index that start with these three columns may be used and will have equal cost.

The most likely reason for your observations is that the InnoDB index statistics have not been updated after the bulk insert.  If I do ANALYZE TABLE on the two tables, the optimizer selects i0 index instead of author_id.

If you want to understand more about why a particular query plan is chosen, I recommend to look at the optimizer trace for the query.


Oracle average active sessions high threshold I am trying make sense of the DB load from the AWR report for a period handed over to me by DBA. Researching a bit, I see the "Average active sessions" is a metric widely used to measure DB load. It is calculated using (Total DB time/Total wall clock elapsed time). From the AWR, in my case it came a little over 7. Does this number indicate high DB activity? Or do I need to take into account anything else?

To know whether 7 is high or low, you'd need to know something about the database server.

If, for example, you have a single CPU with 4 cores, 7 sessions would mean that, at any given time, 3 of your 7 threads were probably waiting to get scheduled for the CPU since your machine is only capable of running 4 sessions at any instant in time.  That would imply that your server was under a pretty decent load.  If, on the other hand, you have 4 CPUs each with 8 cores, then only 7 of your 32 cores were, on average, busy at any instant in time leaving 25 of your 32 cores free.  That would imply that your server itself wasn't particularly heavily taxed.

You'd also want to know a bit about the application.  A 15 minute spike where the average number of active sessions went up to 7 that was preceded and followed by a lower number would generally be less concerning than a report of a 24 hour period where the average number of active sessions was 7 because it's likely that there is a lot more variation over the 24 hour period (i.e. sometimes the number of active sessions was much higher than 7).  Application history also comes in to play.  If the application has always seen the average number of active sessions top out at 3 or 4 under peak load and it suddenly starts spiking to 7, that's more of a concern than if the application has always had those performance characteristics.


Maximum value from multiple tables Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this solution:

select id
from T1
where price in(
   select max(price)
   from(
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)

union

select id
from T2
where price in(
   select max(price)
   from(
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)

union

....    same for T3 table.

Is it possiple to optimize this query?

I'm assuming that you mean you want the id of the item which is the most expensive based on the data from all three tables.  For each table, you need the id and the price of the item(s) with the maximum price in that table.  For one table, that is:

SELECT id, price FROM Tn WHERE price = (SELECT MAX(price) FROM Tn)

So, there's a sub-query:

SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
UNION
SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
UNION
SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)

And, you need to find one of the id values with the maximum price from that sub-query:

SELECT id
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 ORDER BY price DESC
 LIMIT 1

Note that if there are two equally expensive items listed in any one of the tables, the UNION query will return more than 3 rows.  If there are two or more equally expensive items, the query with the LIMIT chooses one, and you can't predict which.  If you need to see all the id values of equally expensive items that cost the most, then you need to play a similar game using nested queries.  This blows my mind, but should work:

SELECT id, price
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 WHERE price =
       (SELECT MAX(Price)
          FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
                UNION
                SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
                UNION
                SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
               ) AS M2
       );

This would be easier using a WITH clause:

WITH MaxPrices AS
     (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
      UNION
      SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
      UNION
      SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
     )
SELECT id, price
  FROM M
 WHERE price = (SELECT MAX(Price) FROM M);

Not every DBMS supports the WITH clause like this, and I believe MySQL is one of the DBMS in the category that does not include such support.


Student and Course Relationship - Grade and Attendance I was reading on this DBA page, about Student and Course relationship, and the use of a junction table. Suppose I wanted to do two things:

Include the student grade for each course
Allow the professor to generate an attendance sheet of students

I have a Student table like this:

Student (id, first name, last name)

Student_Course (student_id, course_id)

Subject (id, name, professorID)

Can I place the grade in the Student_Course table? What table can I use to generate an attendance sheet with StudentID, and First/Last name? I'm guessing attendance would be a query.

As the student's grade is logically tied to both the student and the course, Student_Course is the best place to put it.

For attendance, you would indeed use a query. You can't generate the attendance sheet from one table. You're starting from a professor and/or course name, and need to get data out of the Student table. I suppose you'd need to have some way to connect the professor to the students. In the query, you'd need to find a way to tie each of the tables together. I'd check the documentation for your database, and see if it mentions any way to join tables together in a query.


global foreign_key_checks = 0 on RBR slave Example schema:

CREATE SCHEMA account;
CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `address` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE SCHEMA data;
CREATE TABLE `project` (
    `id` INT(11) NOT NULL,
    `account_user_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_user` (`user_id`),
    CONSTRAINT `fk_user` FOREIGN KEY (`account_user_id`) REFERENCES
`accounts`.`user` (`id`)
) ENGINE=InnoDB;

Problem Background

I have row based replication (RBR) slaves, which should not contain the 'accounts' schema. I use replicate_wild_ignore_table 'accounts'.'%' so that only the 'data' schema exists.

The only problem is, replication breaks when we insert to data.project with: "Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW;" since the accounts.user.id doesn't exist on the slave.

Question:

Setting "set global set foreign_key_checks = 0" permanently on the slaves seems like a good fix in this case. My thinking is:

The key constraints are enforced on the master, where are writes/updates occur; rejected statements do not appear in the binlog / do not replicate to the slave.
Values for each record inserted/updated are explicitly set by the row-based replication. If a FK causes an update, it will be captured in another binlog statement, which will replicate to the slaves.
Therefore, I don't need to worry about having the slave check FK constraints.

Is there a flaw in my thinking? Has anyone tried this? I would appreciate any insights.

Other options I've considered:

set slave-skip-errors = 1452

inserts to data.project won't occur (bad), since the statement that generates the error gets skipped (per MySQL: What happens to the row when skip-errors=1062 is set).

Master & slave - remove foreign key constraint

Not ideal; we want to ensure data integrity on the master.

Slave - remove foreign key constraint

Also not ideal; There are multiple slaves to maintain, and more may be required. There are multiple cross-schema FKs, and likely to add more in the future. I fear it will become unwieldy to manage.

To answer my own question, I tried 'set global foreign_key_checks=0' on the slave. This didn't work, as the statements generated for my RBR binlog explicitly set foreign_key_checks=1.

So, I may try option 3.


Shell script for partial table backup I have multiple database in my MYSQL server and i want to take some particular tables backup from each database.
    can anyone help me in writting a shell script for this scenario.

write table names in a file and use following command to take backups

  for i in `cat table_name_file`
  do
  mysqldump -uroot -p -T $i
  done

Hope it helps


Partially-Unique Check Constraints I have an old Access table which is being migrated to SQL Server, and in it there's a field called "LinkedID". This field was mostly unused until ~5 years ago, so for many historical records it is either null or defaulted to "00000000". However, going forward, we're wanting it to be set up that no duplicate values are allowed (lets say 1,000 records are null, 500 are "00000000", and ~10,000 are actual unique values we want).

Looking into it, since I have multiple records which are set to "00000000" and multiple which are null, I understand that I can't use the Unique constraint. Is there a way that I can create a Check constraint that checks if the value being inserted/updated is unique if not all 0's or null? Or if it's not workable via a check constraint, my other thought was maybe use an insert/update trigger to somehow validate the data?

You can use a filtered unique index for this:

CREATE UNIQUE INDEX LinkedID_Unique_except_Zeroes_or_Null
    ON dbo.oldAccessTable (LinkedID)
    WHERE LinkedID <> '00000000' ;

The condition (LinkedID <> '00000000') takes care of both requirements, i.e. rows with '00000000' or NULL are ignored and not stored in the index, so they are not checked for uniqueness.


Splitting a large SQL Server MDF file I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 GB file (which is practically empty).

Now, the tricky bit: the server to which I'm migrating only has 3 700 GB volumes, meaning I need to somehow dissect this goliath into three equal chunks. Most advice I've found involves creating 3 target files and running DBCC SHRINKFILE EMPTYFILE on my main file to empty it into the targets, but that'd take ages with a database this large.

Is there a recommended method for splitting a database this large? I'm considering using the Sql Server Integration Services Data Export feature to dump the data into a clone database with the proper file structure, but I'm curious as to whether there's a better way.

DBCC SHRINKFILE -- will be single-threaded – which will contribute to the long run-time.

Also, Sql Server Integration Services Data Export will be slower due to massive database size (1 TB) !

Instead, you should look for BCP OUT (in binary format) and BULK INSERT in the database.

BCP can read the SQL Server native format from text files. This is a very fast option that requires minimal parsing of the text file input.
The BULK INSERT command is the in-process method for bringing data from a text file into SQL Server. Because it runs in process with Sqlservr.exe, it is a very fast way to load data files into SQL Server.
Additionally, enable Trace Flag 610 to minimally log inserts into Indexed tables along with BATCHSIZE and ROWS_PER_BATCH parameters.
You can even disable Lock Escalation which on sql server 2005 generally occurs at 5000 locks allocated. This can be disabled on sql server 2005 by setting a batchsize to a number lower than this or enableing Trace flag 1211 (to completely disable lock esclation). In sql server 2008, you can do it using alter table  <table_name> (LOCK_ESCLATION = DISABLE)
BCP has a -a switch to allow to specify packet size or if using SSIS it can be done in properties of the connection manager --> PacketSize = 32767
To avoid  PFS contention, consider using -E startup parameter.

Also at hardware level, you can look into

Use fast NIC and switches.
Have the latest certified drivers for your NIC.
Enable full duplex.
Enable support for Jumbo frames.
Use TCP Chimney Offload.
Use Receive Side Scaling (RSS).

Check the BCP & BULK INSERT script that I provided at here

Note: Your hardware including disk partitioning, layout, number of CPU's, NUMA configuration, etc will also play a big role in performance when you load your data.

Excellent reading:

Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour
The Data Loading Performance Guide


Snapshot Replication Retention I've got Snapshot replication set up on my SQL Server 2008 production server, and I just noticed that there are snapshots ranging back to a year ago in the snapshot folder.  How can I change the retention for these snapshots?  Specifically I'd like it to retain snapshots for 5 days.

Here's a screenshot of the folder I'm looking at:

This is usually an issue where the permissions on xp_cmdshell have been modified for security reasons. You can also have this issue if the SQL Agent account does not have the required network permissions to delete data from your replication folder.

Verify xp_cmdshell is enabled and that the account executing it has proper permissions.

USE master
GO
EXEC sp_configure 'show advanced option', '1';
reconfigure;
GO
exec sp_configure 'xp_cmdshell';

Output should be:

name         minimum    maximum config_value run_value
xp_cmdshell  0          1       1            1


replicate-ignore-table setup and usage for excluding table from binlog backup I need to make an everyday backup of a mysql database that is very big on a remote server and I only want to get the difference since the last backup. I also need to exclude some tables from being backed up.
I've setup the mysql server as a master, here is the important part of my.cnf i've changed :

log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = my_database

Then on the backup server i've setup mysql my.cnf like this :

replicate-ignore-table  = my_db_name.my_table_name

To test that everything is ok, I do the following:

On Master:

$ mysql -u root -p
MariaDB [my_database]> insert into my_table (param1, param2, ...) values(1, 1, ...)
MariaDB [my_database]> exit
$ scp /var/log/mysql/mysql-bin.00000X vagrant@backupserver:/tmp/

On Slave:

$ mysqlbinlog mysql-bin.00000X | mysql -u root -p

When I check the slave database I always see the new entry in the table, but that's not the expected result....

I must miss something, any idea ?

The replicate-ignore-table filter applies only the streaming over replication protocol (specifically in this case the SQL thread Slave), it doesn't affect if you apply log changes manually as you are doing. The detailed information about replication filtering can be complemented with the replication rule application if you want to understand fully the mechanisms for replication filtering.

A very easy step-by-step tutorial will allow you to setup a standard replica using no other tools than the ones included in the official distribution.

Although you don't want to replicate the table, I recommend you to create the table structure but using BLACKHOLE engine for avoiding some scenarios issues.


Why a query is not covered when using a range operator on documents? I have created the following 1000 documents:

for(i=0;i<1000;i++){db.doc.insert({"doc":{"k1":"v_"+i,"k2":i},"scal":i})}

So a document looks like this:

var d1 = db.doc.findOne()
{
    "_id" : ObjectId("5ce25f8920b0be2428648e38"),
    "doc" : {
        "k1" : "v_0",
        "k2" : 0
    },
    "scal" : 0
}

I've created the following two indexes:

db.doc.createIndex({"doc":1})
db.doc.createIndex({"scal":1})

I wonder why the following query is not covered:

db.doc.explain(true).find({doc:{$gt:d1.doc}},{_id:0,doc:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 999,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 999,
        "totalDocsExamined" : 999,
        ...
}

However, the same query using a scalar instead of a document is covered:

db.doc.explain(true).find({scal:{$gt:d1.scal}},{_id:0,scal:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 999,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 999,
        "totalDocsExamined" : 0,
        ...
}

I only get the query covered on documents, when I'm not using a range operator:

db.doc.explain(true).find({doc:d1.doc},{_id:0,doc:1})
{
    ...
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 0,
        ...
}

Even $in would result in a covered query:

var dis = db.doc.distinct("doc")
db.doc.explain(true).find({doc:{$in:dis}},{_id:0,doc:1})
{
...
"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1000,
        "executionTimeMillis" : 8,
        "totalKeysExamined" : 1000,
        "totalDocsExamined" : 0,
        ...
}

However range operators such as $gt seem not to be able to result in a covered query when applied to documents. Is this an expected behaviour which is documented anywhere or is it a bug?

I'm using a replSet running with mongodb v3.6 Linux 64 Bit.

db.doc.explain(true).find({doc:{$gt:d1.doc}},{_id:0,doc:1})

The above query doesn't use db.doc.createIndex({"doc":1}) index, because the index is created at embedded document level. To use this index, you should specify all fields in the document. For example

db.doc.explain(true).find({"doc" : { "k1" : "v_0", "k2" : 0 }},{_id:0,doc:1})

or else you need to create an index at field level for embedded documents as shown below

db.doc.createIndex({"doc.k1":1});
db.doc.createIndex({"doc.k2":1});

For more details refer

Create an Index on an Embedded Field

Create an Index on Embedded Document


Cross tab query with concat field I want to create a cross tab query for my table. When I use CONCAT I am unable to get output successfully. Can anyone help me with my query?

    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('CASE WHEN TX_STOP_NAME=''', TX_STOP_NAME, '''  THEN
    concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT
    else null  END AS `', concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT , '`'))
    INTO @sql
    FROM cmn_trip_statistics;
    SET @sql = CONCAT(' select *from(SELECT s.FKYS_TRIP_ID,s.FKYS_REPORT_TYPE,sum(s.NO_HALT_DUR) as totalhalt, ', @sql, '
    FROM cmn_trip_statistics s
    group by s.FKYS_TRIP_ID,S.TX_STOP_NAME)T where FKYS_REPORT_TYPE=''HALT''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

DEALLOCATE PREPARE stmt;

The default length of a GROUP_CONCAT is 1024.

Please run this

mysql> SET group_concat_max_len = 1048576;

before the query. This will allow for a 1M string as the maximum GROUP_CONCAT.

If you want every session going forwards to have this, add this to my.cnf

[mysqld]
group_concat_max_len = 1M

and run this

mysql> SET GLOBAL group_concat_max_len = 1048576;

MySQL Restart not required

As for the query itself, avoid using newline characters. Change to this:

    SET @sql = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT('CASE WHEN TX_STOP_NAME=''', TX_STOP_NAME, '''  THEN
    concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT
    else null  END AS `', concat('TIME:-',s.TS_ACTUAL_ARIVAL,' HALT FOR:- ',s.NO_HALT_DUR,' STOP NAME:-',s.TX_STOP_NAME) HALT , '`'))
    INTO @sql
    FROM cmn_trip_statistics;
    SET @sql = CONCAT('select * from (SELECT s.FKYS_TRIP_ID,s.FKYS_REPORT_TYPE,sum(s.NO_HALT_DUR) as totalhalt, ', @sql);
    SET @sql = CONCAT(@sql,' FROM cmn_trip_statistics s group by');
    SET @sql = CONCAT(@sql,' s.FKYS_TRIP_ID,S.TX_STOP_NAME) T');
    SET @sql = CONCAT(@sql,' where FKYS_REPORT_TYPE=''HALT''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


Find continuous records with equal values in each group I have a table named "MachineHealth". Here is the sample data:

Date
MachineId
IsBad

2020-01-05
1
1

2020-01-04
1
1

2020-01-03
1
1

2020-01-02
1
0

2020-01-01
1
1

2020-01-05
2
1

2020-01-04
2
1

2020-01-03
2
0

2020-01-02
2
0

2020-01-01
2
1

I want to find continuous 1 count in field IsBad group by MachineId, that means the number of days that a machine is continuously bad for the most recent time. For example, the query on above data should return:

MachineId
ContinuousBadDays

1
3

2
2

How to write this sql?

Assuming your duplicate Dates for MachineId = 1 was an accident (as Scott pointed out) something like this should be what you're after:
WITH CTE_GoodDates_Sorted AS
(
    SELECT MachineId, [Date], ROW_NUMBER() OVER (PARTITION BY MachineId ORDER BY [Date] DESC) AS SortId -- Generates a unique ID for each row within each MachineId, then resets on the next MachineId
    FROM MachineHealth
    WHERE IsBad = 0 -- Only want good Dates so we can get the last good Date per MachineId later
)

SELECT MH.MachineId, SUM(MH.IsBad) AS ContinuousBadDays
FROM MachineHealth AS MH
INNER JOIN CTE_GoodDates_Sorted AS GD
    ON MH.MachineId = GD.MachineId
    AND MH.[Date] > GD.[Date] -- Ensures we only get rows that came after the good Date
WHERE GD.SortId = 1 -- Using the auto-generate unique ID (within each MachineId), ensures we only have the latest good Date for each MachineId to compare to in the previous INNER JOIN
GROUP BY MH.MachineId

This uses the ROW_NUMBER() window function to get the latest Date no accidents occurred on per each MachineId in a CTE then uses that CTE to join to filter down the results to only the most recent continuous bad Dates. The final query then does a GROUP BY on MachineId and sums the IsBad field to get the total days (though you probably can even substitute that for a COUNT(1) instead if your IsBad field at most has a value of 1).


ORA-01658 on index tablespace when trying to insert into empty table of new database We are getting ORA-01658 (Error :ORA-01658: unable to create INITIAL extent for segment in tablespace MYDATA_LDATA_INDEX) in our application logs. This happens when application is trying to insert data for the first time into tables that are empty. It is only happening to this particular index. The tablespace free space decreases by multiple of 50M.

The parameters used to create the index tablespace:

CREATE TABLESPACE MYDATA_LDATA_INDEX DATAFILE
  '/index/oradata/mydb/mydata_ldata_index_01.dbf'
SIZE 1300M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

We are sure application is not trying to insert massive amount of data into the tables, an operation that only insert one row also causes this.

After increasing tablespace for the index manually, regular operation can be continued and tablespace usage for the index is not increasing dramatically.

We are using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production on Solaris 11.1.

How do we pin point source of the problem? Is there an issue with the parameter of the index tablespace?

Oracle docs note this about ORA-01658:

  ORA-01658: unable to create INITIAL extent for segment in tablespace string

  Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

  Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL.

The obvious cure for the ORA-01657 error is to:

Make the INITIAL extent size smaller
Empty the recycle bin
Add a datafile the tablespace to provide a larger contiguous chunk size.
Reorganize the tables  in the tablespace

Try this query for your tablespace maximum chunk size tsfree.sql.

This case of ORA-01658 concerns needing to make the tablespace bigger, which can be done by either extending your file, or adding another one.  This is a good solution for ORA-01658 because repository tables need to be meticulously created keeping the extents of the table in mind.

This information was taken from ORA-01658: unable to create INITIAL extent tips from the dba-oracle.com website.

Disclaimer:
I am in no way affiliated with the company.


Can I use impdp FULL=Y for an Oracle database charset conversion? I need create a copy of an Oracle 12.2 instance (Oracle Restart) running on an Linux VM. The new instance needs a charset conversion from WE8ISO8859P1 to UTF8

I started by creating a clone of the VM that is now up and running.

I was thinking about following these steps:

expdp FULL=YES from the cloned machine to a safe storage location
destroy the database from the clone using DBCA
create a new database on the clone using DBCA setting UTF-8 as charset
impdp FULL=YES from the previous exported datapump

I have some doubts about the impdp FULL=YES... will this work or will it overwite some configuration on SYS or other Oracle maintained schemas with some values that still refers to WE8ISO8859P1 charset?

Will this work or should I exclude all the Oracle maintained schemas from the impdp?

On export the documentation states:

A full export does not, by default, export system schemas that contain Oracle-managed data
and metadata. Examples of system schemas that are not exported by default include
SYS, ORDSYS, and MDSYS.

If you want to be absolutely sure I would switch to a schema-mode import instead. You can still use the full export. That way you specify what schemas you want to import.

Regards,
Daniel


How to sum of column by depending other column in my sql? how to calculate / SUM of column by the latest data of updateid column ?

I try this :

SELECT * FROM yourTable;

SELECT
ANY_VALUE(code) AS code,
COUNT(*) AS total_rows,
SUM(data->'$.chart."21"') AS total_a
FROM yourTable
GROUP BY code
ORDER BY code;

From above query it sums all rows, it should be sum the latest update id

anyone can solve this problem ?

here is the fiddle : https://www.db-fiddle.com/f/qHnaHNQtEhDasLiSB4Jhv5/0

SELECT
t1.code,
COUNT(*) AS total_rows,
SUM(t1.data->'$.chart."21"') AS total_a
FROM yourTable AS t1
JOIN (SELECT code, MAX(updateid) AS updateid
      FROM yourTable
      GROUP BY code) AS t2 ON t1.code = t2.code
                          AND t1.updateid = t2.updateid
GROUP BY t1.code
ORDER BY t1.code;


Performance tuning across two tables using indexes? Let say I have two tables and a SQL query like this

SELECT table_a.*, table_b.*
FROM table_a, table_b
WHERE table_a.id = ?
AND table_b.col_x = table_a.col_x
AND table_b.id = table_a.id;

Would there be any performance improvement by rewriting the SQL query to JOIN

SELECT table_a.*, table_b.*
FROM table_a
JOIN table_b on (table_b.id = table_a.id AND table_b.col_x = table_a.col_x)
WHERE table_a.id = ?;

Assuming that the tables have these current INDEXES

--table_a
create index table_a_index_z_id on table_a (col_z, id);

--table_a
create index table_a_index_x on table_a (col_x);

--table_b
create index table_b_index_id on table_b (id);

What extra indexes if any would improve the performance of the queries above?

EDIT

The primary keys for the tables are

alter table table_a add (constraint table_a_pk primary key (id, col_b, col_x))
// there is no PK on table_b

Your first query is also a JOIN, only using the 'old' ANSI-89 syntax, instead of the explicit JOIN clause which was introduced in ANSI-92.

Most DBMS should treat both queries as equivalent, but you should check with your specific engine by executing both and examining the execution plan.

With regard to indexing, I would recommend that you try a composite index on (id, col_x) for both tables.
I would also try and add a predicate to the WHERE clause:

WHERE table_a.id = ? AND table_b.id = ?

Even though this is logically redundant, and will not change the output as the join already filters out rows where the id is not the same, some query optimizers tend to better use indexes when the predicate is explicit and not implied, as they lack the logic to infer that. Check the execution plan, and you will know.

Happy holidays!


How to get text for default language I have so tables:

and so data at Language table:

and so data at Text table:

I have to return text for requested language if it exists and text for default language if it does not exist. Is it possible to do that in one query (no while, please)?

Code:

DECLARE @CommentId  bigint = 1
--DECLARE @LanguageCode  nvarchar(2) = 'en' -- "english text" returns
DECLARE @LanguageCode  nvarchar(2) = 'ua'   -- nothing at this moment

SELECT
     t.CommentId
    ,t.TextId
    ,t.[Text]
    ,t.LanguageId
    ,RequestedLanguageId = @LanguageCode
FROM dbo.common_Text t
    INNER JOIN dbo.common_LanguageType l
        ON t.LanguageId = l.LanguageId
WHERE l.Code = @LanguageCode
    AND t.CommentId = @CommentId

Thank you.

ADDED:

If code requests a text in 'ua' (ukrainian) and this is no any text for this language, so it is going to search text for russian. if found - ok, if don't it will look for a text for english. List of languages can vary.

Using a recursive CTE, this query builds a table of all languages that point somewhere in their default chain to a language that has the required comment.  It shows each language next to its first default that has the required comment.  It then filters this table to give you the first available text for the language you selected.

DECLARE @CommentId BIGINT = 1;
DECLARE @LanguageCode NVARCHAR(2) = 'en';

WITH languages AS (
    -- base case: language has required comment
    SELECT
          lt.LanguageId AS RootLanguageId
        , lt.LanguageId
        , lt.Code
        , lt.DefaultId
        , 0 AS Level
    FROM
        dbo.common_LanguageType lt
    --WHERE
    --  lt.LanguageId = lt.DefaultId
    WHERE
        EXISTS (
            SELECT *
            FROM dbo.common_Text    t
            WHERE
                    t.CommentId = @CommentId
                AND t.LanguageId = lt.LanguageId
        )

    UNION ALL

    -- recursive case: language is not its own default and
    --                 does not have the required comment
    SELECT
          l_default.RootLanguageId
        , l.LanguageId
        , l.Code
        , l.DefaultId
        , l_default.Level + 1 AS Level
    FROM
                    dbo.common_LanguageType l
        INNER JOIN  languages               l_default
            ON l.DefaultId = l_default.LanguageId
    WHERE
            l.LanguageId <> l.DefaultId
        AND NOT EXISTS (
            SELECT *
            FROM dbo.common_Text    t
            WHERE
                    t.CommentId = @CommentId
                AND t.LanguageId = l.LanguageId
        )
)
SELECT t.Text
FROM
                languages       l
    INNER JOIN  dbo.common_Text t
        ON  l.RootLanguageId = t.LanguageId
WHERE
    l.Code = @LanguageCode
;

Try just running

SELECT *
FROM languages;

if you want to get a feel for what the recursive CTE is doing.  I've uploaded a script to create the tables, insert some sample data, and run the above code to gist.

Pretty sexy, no?


general design pattern for smushing rows into one column I have a table of people :
id, person_name
I have a table of tags:
id, tag_name
there is a junction table for the tags that connects people and tags:
id, person_id, tag_id
I have a query to get a group of people based on different search criteria so it's being built up dynamically and what I would like to do is within that query - for each person - I'd like to be able to grab all of the tag names associated w/ that person. I'd like to grab all the associated tags and stick them into one column for each person row (preferably as an array or object). What is the general pattern to be able to return a group of stuff as one column when cycling through rows like this.  I'm having trouble figuring out what to search for to find a generalized answer online.  I'm using postgres.

As others have mentioned, your goal is to denormalize the data and is not a usual use case for a relational database system, but it is certainly achievable. So if you really want to accomplish that, what you'd want to Google is something along the lines of "PostgreSQL concatenate multiple rows". The goal usually is to create a comma separated values list (CSV or something of a similar structure).
Doing so actually led me to this StackOverflow answer on using the string_agg() function in PostgreSQL.
For historical reference, here is Erwin's example from the aforementioned StackOverflow answer, leveraging string_agg() to smush multiple Actor values into a single field per Movie row in a table of Movies:
SELECT movie, string_agg(actor, ', ') AS actor_list
FROM tbl
GROUP BY 1;

In your case, you'd want to JOIN your people to your tags table (via your junction table). From the above example movie would be equivalent to your person_name field, and actor would be equivalent to your tag_name field. A query likely similar to this is what you're looking for:
SELECT P.person_name, string_agg(T.tag_name, ', ') AS tag_list
FROM people P
LEFT JOIN peopleTagJunctionTable JT
    ON P.id = JT.person_id
LEFT JOIN tags T
    ON JT.tag_id = T.id
GROUP BY P.person_name;


Will index on joined table be used here? select * from employee, department where employee.id = department.id

department.id is primary key on department

My question is will index created on  department.id(internally created index on primary key by oracle) be used while employee.id = department.id i.e. while finding corresponding row_id in department table

HASH JOIN - no

NESTED LOOPS JOIN - yes, depends on statistics

Oracle can not use index range scan for join predicates when performing a hash join. But it can use indexes for other predicates for the probe table ("second" table), so hash join does not automatically result in a full table scan of the probe table.


How is parallelism threshold (seconds) used if query CPU cost is actually unitless How does the optimizer decide whether or not to use parallelism when the parallelism threshold is specified in seconds (on specific hardware, see below) but the estimated CPU costs are supposedly unit-less?  Is the estimate treated as if it were seconds or is there a conversion happening internally based on some hardware metrics?

From MS:
cost threshold for parallelism Option

  The cost refers to an estimated elapsed time in seconds required to
  run the serial plan on a specific hardware configuration

how-do-i-read-query-cost-and-is-it-always-a-percentage

sql-server-execution-plan-estimated-io-cost-estimated-cpu-cost-no-unit

The parallelism threshold is based on the estimated plan cost. If the estimated plan cost goes past the parallelism threshold, the optimizer considers parallel plan options too. There is no other measurement involved.

In the beginnings of the optimizer the plan cost was derived based on actual measurements on a specific computer. Since then throughout many versions a lot has changed with the optimizer, so attaching any time unit to the current "estimated cost" is not appropriate anymore. For example, not only estimated execution time influences that estimated cost in newer versions of SQL Server. The only thing it is telling you is that the optimizer assumes that a query plan with a higher estimated cost for the same query executed under identical circumstances will require more resources.

You cannot even compare the estimated cost of two different queries. It is fairly easy to come up with two queries where the estimated cost of the one is ten times higher, but the execution time of the other is actually significantly higher. So, you should never use the estimated cost of a query plan for performance tuning.

The quote in your question is a left over from those early days when it still made sense. It was at that time also mentioned in the section explaining the estimated query cost.


Incorrect datetime value I am trying to fill my table with data but I am having trouble with the datetimes (Incorrect datetime value error). My data is in this format 2017-08-30 10:00:00-07:00.

What kind of transformation is needed so that I can insert the right time in my database?

Here's an example which is not working. I am using MySQL 5.6.10

INSERT INTO table (id, created_at)
VALUES (89208460, "2017-08-30 10:00:00-07:00");

Running SELECT NOW() gives me 2017-09-27 11:47:38, it's a DATETIME(19) field.

Community wiki answer:

  Running SELECT NOW() gives me 2017-09-27 11:47:38, it's a DATETIME(19) field.

Not (19), the number is the number of decimal places in the seconds.

  What kind of transformation is needed so that I can insert the right time in my database?

MySQL has no concept of time zone representation.

That is there is no way to use (read or write) -07:00.


MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can't Bring It Online I can take databases offline (via GUI) but can't bring them back online. The server details are as follow:

RDBMS: MS SQL 2016 Express
Host: Amazon AWS/RDS Free Tier

Details/History of the Problem
A few months ago, I created a db instance on Amazon AWS and at the time of creation, the 'master/admin' account was setup via the AWS/RDS web page. With this 'admin' account, I have created several databases on that instance without any problems.

Over the past few months,  I have used this 'admin' account to change several databases to contained databases. I do this so that I can setup contained users. I have also  done this several times on this server instance with the same admin account with no problems.

Last night, I had just created a new database via this admin account. I then tried to set this new database as a contained database and the process failed. The dialog box error message stated among other things "please try again later".

After the 3rd failed attempt, I decided to take the database offline (via the GUI in SSMS). I did this in a bid to force close any possible open processes or connections that might be on this new database. That worked. However, I have not been able to bring it back online. I have tried via the GUI and also via a query and it keeps failing.

I have then checked the server roles assigned to this 'admin' account. It is not part of sysadmin role. As I understand, the 'sysadmin' role can do absolutely anything on the db instance. I reckon my admin account is not of this sysadmin role because it is meant for the in-house DBAs at Amazon AWS. I have tried to add it as sysadmin but it fails.

To ensure that my 'admin' account is the problem, I have taken another database offline (it's empty). It went offline but it is also failing to come back online.

What could be the problem? Please help. Note that my skill level is very very low and I'm learning as I go along.

The server logs don't show anything useful. I have attached screenshots.

Error bringing database online in SSMS

Error looking at SQL Server error log from SSMS

Permissions for login

Error adding login to sysadmin role

1:

You are correct: your admin account is not sysadmin, but a less powerful account. This is necessary to ensure that AWS is able to limit you from doing EVERYTHING to this instance, and escaping security boundaries.

Setting a database online

As part of your admin account, you have access to set databases offline, but bringing a database back online requires sysadmin (which you don't have). To work around this permission restriction, AWS provides a stored procedure to bring databases online. You'll need to use the rds_set_database_online procedure in the rdsadmin database

EXEC rdsadmin.dbo.rds_set_database_online 'MyOfflineDatabase';

This procedure is fully documented by AWS here.

Reading the error log

Similarly, you  cannot use xp_readerrorlog on RDS SQL Server because it requires sysadmin. Instead, AWS provides THE stored procedure rds_read_error_log

EXEC rdsadmin.dbo.rds_read_error_log;

This procedure is fully documented by AWS here.


How to create a TSVECTOR column in a materialized view? I am trying to implement full text search in a materialized view (MV for brevity), and I read that it's not possible to alter the MV itself, which rules out these solutions:

Adding a new column with a specific data type after the MV has been created.
Creating the MV with an empty column and change the column data type.

I tried both and well yes, as expected it doesn't work.

So my idea was to add a cast on the empty column when creating the MV:

CREATE MATERIALIZED VIEW mv AS
  SELECT id, '' AS textsearchable_index_col::tsvector
  FROM tbl
  ...
WITH NO DATA

But it returns the following error:

  ERROR:  syntax error at or near "::"

How can I achieve this?

ps: I am willing to have a column instead of an index because I'd like to use weighing.

Additional information

I am able to have the column created like so:

 CREATE MATERIALIZED VIEW mv AS
   SELECT id, CAST ('' AS tsvector) AS textsearchable_index_col
   FROM tbl
   ...
 WITH NO DATA

However I can't update the column using:

UPDATE mv SET textsearchable_index_col = to_tsvector('english', coalesce(title,''))

I get  an error:

  ERROR:  cannot change materialized view "mv"

Why don't you drop the materialized view ,and recreate it in a transaction? You can't update a materialized view. They're a view that's been written to disk.

Instead of

CREATE MATERIALIZED VIEW mv AS
   SELECT id, CAST ('' AS tsvector) AS textsearchable_index_col
   FROM tbl
[...]
UPDATE mv SET textsearchable_index_col = to_tsvector('english', coalesce(title,''))

Just do

BEGIN;
  DROP MATERIALIZED VIEW IF EXISTS mv;
  CREATE MATERIALIZED VIEW mv AS
     SELECT
       id,
       textsearchable_index_col = to_tsvector('english', coalesce(title,''))
     FROM tbl
COMMIT;

You don't have to have a materialized view for this though, yo can create a functional index which will work on the table directly

CREATE INDEX ON tbl USING GIN (to_tsvector('english', title));

Or, just use the default cast..

CREATE INDEX ON tbl USING GIN ((title::tsvector));

And then depending, on your index condition,

SELECT * FROM tbl
WHERE 'foo & bar' @@ to_tsvector('english', title);

SELECT * FROM tbl
WHERE 'foo & bar' @@ title::tsvector;


Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber' Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly.

But today, when I executed the same script, it threw a wierd exception in SQL Server:

Altering dbo.YourTableHere...

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 367
The data definition language (DDL) command cannot be executed at the Subscriber. DDL commands can only be executed at the Publisher. In a republishing hierarchy, DDL commands can only be executed at the root Publisher, not at any of the republishing Subscribers.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 191
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

The statement which threw the problem is:

PRINT N'Altering dbo.YourTableHere...';
GO
ALTER TABLE [dbo].[YourTableHere]
         ADD [a_new_timestamp] TIMESTAMP NOT NULL;

Is there anything I can configure to make my script run correctly without these warnings?

The computer I am running this script is acting as subscriber and it has a Replication that should be deleted?

We found traces of a replication that had been activated in the past in a specific table.

Replication was not fully removed from this table.

We also notice that the script works in different database name (a database with the same structure).

Here is the history:

We restored database from DATABASE_A (with traces of replacation) to DATABASE_B
We run the stript on DATABASE_B and it works
We run the script on DATABASE_A and it fails

Our DBA found traces of replication in DATABASE_A and in order to clean up the replication traces, he had run:

sp_removedbreplication 'DATABASE_A'

I am not sure if there is something configured on system tables in SQL Server that are related to the name DATABASE_A. We are going to investigate this issue deeper to find dependencies or what is causing the error.


How do I resolve "ORA-23293: Cannot rename a column which is part of a join index?" Disclaimer: I am a developer, not a DBA, but this question seems more appropriate for DBAs than for StackOverflow. Now to my question:

We have a table in an Oracle Exadata schema that uses a numeric, sequence-generated PK simply called ID. We would like to rename this column to [TABLENAME]_ID so that its name is consistent with its table name (and therefore consistent with other naming conventions used at the company).

When I attempt to rename this column with ALTER TABLE TABLENAME RENAME COLUMN ID TO TABLENAME_ID;, I'm slapped with the message SQL Error: ORA-23293: Cannot rename a column which is part of a join index. The obvious suggestion from Oracle is to drop the join index so that I can rename the column, but that's the fun part: I (nor any of my coworkers) have ever explicitly created such an index, nor are aware of any that exist.

At the moment, my only thought is that the index in question was auto-generated by the creation of some other object or by some unique configuration of this table. I've tried searching in user_indexes and user_objects for signs of the join index without any luck; are there other system tables I should consult to hunt this index down?

FWIW, the table has a few things about it that make it unique vs other tables where we have been able to renamed the ID column:

This table has a CLOB column in it.
This table is partitioned based on a date column. Partitions are based on the year.
Other tables in the schema are partitioned based on their foreign key references to this table.

Can anyone clue me in on whether there's some "shadow" join index created by one of the above features, and/or the correct way to find this phantom join index? Thanks!

There is no such index/object type as JOIN INDEX in the USER_INDEXES/USER_OBJECTS views. A join index is a BITMAP index, search for that. Also you can query what columns are indexed from USER_IND_COLUMNS view. Thinking backwards, you can filter to one specific column and check whether it is indexed or not (this will also reveal the index name):

select * from user_ind_columns where column_name = 'ID' and table_name = 'TABLENAME';

When an index name starts as SYS_.... and has a seemingly random identifier on the end, then that index was generated automatically, which is quite unlikely for a bitmap join index :)

You can confirm the index being a bitmap join index e.g with the use of dbms_metadata.get_ddl, which will show you the actual join condition used:

select dbms_metadata.get_ddl('INDEX', 'INDEX_NAME', 'INDEX_OWNER') from dual;


Linking between SQL Server and Excel I want to dump Excel data in SQL Server database. What is the best/easiest way to do this?

I have SQL Server 2012 Management Studio. I have tried many techniques but failed miserably, there is no option to connect a link between Excel and SQL Server.

I would be interested to hear what you have tried and what issues you have faced.

You say you have SSMS 2012 installed, but it's not clear if you have a database engine in which to host a database and associated tables?

If you do not have a database engine installed, you can install the SQL Express engine locally. Download it here.

You are going to need to import the data from Excel into a table within an existing SQL Server database.

To do this, you can use SSIS, if you have access to it, and create a simple ETL package involving a Source (Excel) and OLEDB Destination (SQL Server). You will need to create an existing table in your database, with appropriate fields and data-types, in which to 'dump' the data.

Or,

You can import the data through Management Studio directly, but you will need an existing database. Right click the database, click Tasks --> Import Data, and then follow the steps, selecting Excel Source and SQL Destination.


How Can I Measure How long a query takes on Azure SQL Data Warehouse In traditional SQL Server I can get CPU and Elapsed Time by setting the following

set statistics time on

When I try that on Azure SQL Data Warehouse I get the following error

Msg 103010, Level 16, State 1, Line 19
Parse error at line: 1, column: 5: Incorrect syntax near 'statistics'.

What options do I have for getting similar diagnostic information from SQL Data Warehouse

Add a label to your query using the OPTION ( LABEL ... ) syntax which is supported in Azure SQL Data Warehouse.  You can then monitor it either via the portal or using the DMV sys.dm_pdw_exec_requests, for example

SELECT *
FROM dbo.yourBillionRowTable
OPTION ( LABEL = 'Your Unique Query Label 042' )

-- In a separate window...
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [label] =  'Your Unique Query Label 042'

Results:

Or monitor it in the portal:


What's the difference between ALL_TABLES and TABLES? The docs are unenlightening:

ALL_TABLES:

Provides summary information about tables in a Vertica database.

TABLES:

Provides information about all tables in the database.

The reason I'm asking is that users are confused about why they can see some tables in one but not the other. It's related to permissions, but it's not clear exactly how.
For example, a user is telling me they have access to query a table. However, that table doesn't show up in TABLES and its columns don't show up in COLUMNS, but the table does show up in ALL_TABLES.
So, this leads to the following questions:

What's the difference between ALL_TABLES and TABLES?
How can users see a list of all the tables (and columns!) they have access to query?

ALL_TABLES shows tables and views.
TABLES shows only tables:

dbadmin=> create or replace view test as select now() ;
CREATE VIEW
dbadmin=>
dbadmin=> select * from test;
              now
-------------------------------
 2018-08-13 13:04:24.500297-04
(1 row)

dbadmin=>
dbadmin=> select * from all_tables where table_type = 'VIEW';
 schema_name |     table_id      | table_name | table_type | remarks
-------------+-------------------+------------+------------+---------
 public      | 45035996273742356 | test       | VIEW       |
(1 row)

dbadmin=>
dbadmin=>
dbadmin=>
dbadmin=> select table_name, owner_name from tables where table_name = 'test' ;
 table_name | owner_name
------------+------------
(0 rows)

dbadmin=>
dbadmin=>

There is also a system table called views:

dbadmin=>
dbadmin=>
dbadmin=>
dbadmin=> select table_name, owner_name from views ;
 table_name | owner_name
------------+------------
 test       | dbadmin
(1 row)

A user can select from tables, all_tables and views to see which tables and vies he has permissions for.
I believe I got this from
http://www.vertica-forums.com/viewtopic.php?t=726

Here is a tool I use as DBADMIN to summarize permissions:

 SELECT X.object_type,
        X.object_schema,
        X.object_name,
        X.permissions,
        X.grantor,
        X.owner,
        X."grant_sql (for permissions that have '*')"
 FROM ( SELECT 1 AS ordinal,
        g.object_schema,
        g.object_name,
        g.object_type,
        CASE WHEN (g.privileges_description = 'USAGE'::varchar(5)) THEN (g.privileges_description || ' (READONLY access) '::varchar(19)) WHEN (g.privileges_description ~~ '%CREATE%'::varchar(8)) THEN (g.privileges_description || ' (can read, create & drop tables) '::varchar(34)) ELSE NULL END AS permissions,
        g.grantor,
        s.schema_owner AS owner,
        CASE WHEN (g.privileges_description ~~ '%*%'::varchar(3)) THEN (('grant <INSERT, SELECT, UPDATE, DELETE, REFERENCES> on '::varchar(54) || g.object_name) || ' to <user_name> ; '::varchar(18)) ELSE NULL END AS "grant_sql (for permissions that have '*')"
 FROM (v_catalog.grants g JOIN v_catalog.schemata s ON ((g.object_name = s.schema_name)))
 WHERE (g.object_type = 'SCHEMA'::varchar(6)) UNION  SELECT 2 AS ordinal,
        g.object_schema,
        ((t.table_schema || '.'::varchar(1)) || t.table_name) AS object_name,
        g.object_type,
        g.privileges_description AS permissions,
        g.grantor,
        t.owner_name AS owner,
        CASE WHEN (g.privileges_description ~~ '%*%'::varchar(3)) THEN (('grant <usage, create> on '::varchar(25) || g.object_name) || ' to <user_name> ; '::varchar(18)) ELSE NULL END AS "grant_sql (for permissions that have '*')"
 FROM (v_catalog.grants g JOIN v_catalog.tables t ON (((g.object_schema = t.table_schema) AND (g.object_name = t.table_name))))
 WHERE (g.object_type = 'TABLE'::varchar(5))) X
 ORDER BY X.ordinal,
          X.object_name;


Impact of OS timezone change on Postgres Database I want change OS time zone to UTC. This will shift back time by 2h. Can this possibly damage data in any way (for example overwrite data)? What should I do to safely change the OS time zone?

If you have set a time zone for your Postgres DB cluster in postgresql.conf, changing the time zone of the server OS will have no bearing on your databases at all - as long as you don't actually change the time itself, not just the time zone.

You need to make sure that none of your client programs feed local time to the database, which would actually change with the time zone setting of the server.

Even the time zone of the Postgres server has no bearing on data while operating with the data type timestamptz (timestamp with time zone). Only the display is adjusted to the timezone setting of each session - which defaults to the setting of the DB cluster unless specified otherwise. But the value is always the same.

However, it matters for timestamp (timestamp without time zone). And it also matters whenever a timestamptz is cast to timestamp or date (and the time zone is not provided explicitly with the AT TIME ZONE construct). This may even be hidden in expressions transforming or formatting temporal data. All of those will show an actual time-shift of two hours when the timezone setting of the session changes.

Related:

PostgreSQL timezone does not match system timezone
Ignoring timezones altogether in Rails and PostgreSQL


When interrupting a query on a Master, does it replicate to the Slave? In MySQL, when does the master send a query to the slaves?

Here's a specific scenario that I'm trying to understand.

Admin entered an update on master that took a long time to execute.
Admin hits Ctrl-C to stop the query.

I see four scenarios that may have happened:

Nothing happened on the slave, because master waits until the query is finished executing before it sends the query to the slave.
A huge update happened on the slave, because the master passed the query along immediately, and didn't pass the ^C along to the slave.
The same update happened on the slave as the master, because the master passed the query along immediately, and also passed the ^C along to the slave.
The same updates happened on the slave as the master, because the master passed each individual update along to the slave.

Which one of these actually happened?

This is a local phenomenon within the MySQL Replication Architecture. Here is a sample message that can sometimes appear when you interrupt a query on a Master

  Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: (INSERT vhc (v_id, vc_id) SELECT 7421594, vc_id FROM vc WHERE label='st' AND value='re')

Error 1053 is a Server Shutdown. Obviously, the intent was not to shutdown mysql and not tell connected Slaves. Hitting Ctrl-C on a Master would kill the DB Connection. However, such disconnects are simply interpreted as a shutdown. In my old post Does MySQL support replicating all databases?, I described how MySQL replicates from Master to Slave:

Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject 'USE dbname;' among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Looking back at the error message I described, the Slave detected that a binlog event in its relay logs that came from the Master. It was anticipating another event with the actual SQL saying everything went well on the Master. Error 1053 (the phantom shutdown caused by Ctrl-C) came up instead. This will give you the chance to say OH MASTER HAD THAT QUERY INTERRUPTED. I"LL GO LOOK AT THE DATA TO SEE IF THEY MATCH.

When it comes to your actual question, I would go with #1

  Nothing happened on the slave, because master waits until the query is finished executing before it sends the query to the slave.

but with a caveat

  Since Ctrl-C was done on the Master, the Master recorded the SQL in the binlog along with error 1053. The Slave intercepted this scenario.


Why does SQL*PLUS need a slash after CREATE TYPE? I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error.

Example:

CREATE OR REPLACE TYPE MyType AS OBJECT (
    Item1 NUMBER,
    Item2 NUMBER
);

For some reason I have to add a slash here

CREATE OR REPLACE TYPE MyType AS OBJECT (
    Item1 NUMBER,
    Item2 NUMBER
);
/

To me it looks similar to a Create Table statement, which doesn't require a slash.
I find it rather confusing. I know how it works, but can anyone explain why this design decision was made?

you need a / after a PL/SQL block in SQL*Plus:

SQL> begin
  2     null;
  3  end;
  4  -- here you need a /
  5  /

PL/SQL procedure successfully completed

This is so SQL*Plus knows you are done with your statement (which could include intermediate, non-terminating ;).

SQL types may include PL/SQL code, therefore the SQL*Plus devs decided that you need a / in all cases after a CREATE TYPE:

SQL> CREATE OR REPLACE TYPE t AS OBJECT (
  2     x NUMBER,
  3     MEMBER PROCEDURE setx(p_x NUMBER)
  4  );
  5  /

Type created

SQL> CREATE OR REPLACE TYPE BODY t AS
  2     MEMBER PROCEDURE setx (p_x NUMBER) IS
  3     BEGIN
  4        x := p_x;
  5     END;
  6  END;
  7  /

Type body created

Note: you also need a / after you define a procedure, a package or a package body (for the same reason).


Select rows starting from specified value until another specified value I have a table containing several columns and want to check for a column value, select the rows after that column value until a separate column value shows up. I have tried using BETWEEN, but if the column values are integers it only looks for numbers between the integers.

For example, if I have a table like this:

id  time      value
t1  12:00PM   15
t1  12:02PM   4
t1  12:03PM   7
t1  12:05PM   16
t5  12:10PM   250
t5  12:15PM   15
t8  11:00AM   15
t8  3:00PM    2
t8  3:05PM    100
t2  7:00PM    15
t2  7:01PM    16
t15 5:00AM    35

I would want to get the rows between values of 15 and 16. Basically, if I could sort by id, then time, and gather the rows after 15 appears until there is a value of 16 within that same id. If there is no value 16, I would want the next 100 rows for example and then search for the next value of 15.

I would like the query to return this:

id  time      value
t1  12:00PM   15
t1  12:02PM   4
t1  12:03PM   7
t1  12:05PM   16
t2  7:00PM    15
t2  7:01PM    16
t5  12:15PM   15
t8  11:00AM   15
t8  3:00PM    2
t8  3:05PM    100

That may be confusing. I've tried:

SELECT * FROM table WHERE value BETWEEN '15' AND '16' ORDER BY id, time

as a starting point, but that only returns rows with values of 15 or 16 because they are integers.

I want to sort the table by id then time. These entries are added automatically via another system so I'm trying to query the table for specific value ranges.

Any ideas?

Clarification:

If I had rows with 15, 1, 16, 7, 15, 2, 16 for the same id, I would want both "islands": 15, 1, 16, 15, 2, 16.

A suggestion that should work in 2008 version.

Tested at rextester.com:

with
  end_points as                       -- find start and end points
    ( select id, time, value
      from table_x
      where value in (15, 16)
    ),
  start_points as                     -- only the start points
    ( select id, time, value
      from end_points
      where value = 15
    )
select
    t.id, t.time, t.value
from
    start_points as s
  outer apply                         -- find where each island ends
    ( select top (1) ep.*
      from end_points as ep
      where s.id   = ep.id
        and s.time < ep.time
      order by ep.time
    ) as e
  cross apply                         -- and run through each island
    ( select p.id, p.time, p.value,
             rn = row_number() over (order by p.time)
      from table_x as p
      where s.id   = p.id
        and s.time <= p.time
        and         ( p.time < e.time
                   or p.time = e.time and e.value = 16
                   or          e.time is null)
    ) as t
where
    t.rn <= 100
order by
    t.id, t.time  ;

More info:

Using Common Table Expressions
Using APPLY
ROW_NUMBER (Transact-SQL)


How to get the ID of Query I have this table

CREATE TABLE `LastPosition` (
`Id` CHAR(36) COLLATE utf8_bin NOT NULL DEFAULT '',
`Serial` BIGINT(20) NOT NULL,
`DateGps` DATETIME NOT NULL,
PRIMARY KEY USING BTREE (`Id`, `DateGps`),
KEY `serial_idx` USING BTREE (`Serial`),
KEY `dategps_idx` USING BTREE (`DateGps`),
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

And this query

SELECT
    Serial,
    MAX(DateGps) AS maxi
FROM
    LastPosition
GROUP BY
    Serial
ORDER BY
    DateGps

How can I get the id of the record with the max of DateGPS?

Keep in mind that you could get more than 1 row just in case there are two or more records with the same DateGps.

SELECT lp.Id
FROM   LastPosition lp
JOIN   (SELECT  Serial,
                MAX(DateGps) AS maxi
        FROM    LastPosition
        GROUP BY Serial) t1
ON      lp.Serial = t1.Serial
AND     lp.DateGps = t1.maxi;


Mysql master-master permission replication issue I came to know it's safe to replicate mysql database to have the users and permissions replicated, from a post in ServerFault is-it-ok-to-replicate-mysql-db. I have setup master-master replication with a slave for each master(totally 4 nodes). Everything is working, databases, tables are synced but NOT permissions, permissions applied to a db is not replicated from or to any server.
I dumped mysql(mysqldump -uroot -pmypass mysql > /tmp/mysql.sql) db on original server and restored it to all the servers and also given super privileges to the repli user as mentioned in the above link. But permission are not synced from or to any server. I issued GRANT ALL PRIVILEGES... on a test db and checked with SHOW GRANTS for.. but privileges are not showing on remote servers(did flush privileges on where I granted and on all).
Posting configuration may not be useful but still here it's:
Master1(192.168.1.5)
server-id = 10
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

master-host = 192.168.1.10
master-user = repli
master-password = secret
master-connect-retry = 60

log-slave-updates
replicate-ignore-db=information_schema
report-host=192.168.1.5

binlog-ignore-db=information_schema
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

Master2(192.168.1.10)
server-id = 20
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2

master-host = 192.168.1.5
master-user = repli
master-password = secret
master-connect-retry = 60

log-slave-updates
replicate-ignore-db=information_schema
report-host=192.168.1.10

binlog-ignore-db=information_schema
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index

Isn't there any way to check if permission are replicated? with show slave status, db replication is fine and the status looks similar to below on the masters:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 4045
               Relay_Log_File: relay.000008
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: information_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4045
              Relay_Log_Space: 541
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.01 sec)

I have tried this setup twice(once at office and once at home), but the result is same.
Am I doing anything wrong? What's the proper way to replicate along with users and their privileges? Anybody know please?
Thanks!

Your grants are not replicating because you have an (unnecessary) replication filter on INFORMATION_SCHEMA.  There is no reason to exclude this, as it is not replicated anyhow.  When replication filters are in place, you have to USE a database or your statements are not replicated.

For example...not replicated

Grant all privileges on *.* to foo@localhost identified by 'bar';

This is replicated...

USE anydb
Grant all privileges on *.* to foo@localhost identified by 'bar';

....but instead, just remove that replication filter.


Create database before restore multiple files I've several database, most of them have multiple files (both data and logs) and multiple diff and trn backups. I've already a restore script (scheduled and working on other server) that takes all the needed files and restores them through UNC path. There is a check at the beginning that verifies that the db exists, if not, throws an error. But I want instead to create the database and then restore it. I don't want to use the MOVE option on the RESTORE statement. I've made some searches and what I've found doesn't cover my needs.

So, how can I create a CREATE statement to match what I will restore on the next step? Maybe a silly question, but can't find how to do it.

Edit

From Aaron comment I get now why I can't prevent using MOVE. But then I should create the database with all the requiered filegroups, correct? Something like this:

CREATE DATABASE [Testdb] ON  PRIMARY
    ( NAME = N'Testdb', FILENAME = N'F:\SQLDBs\Testdb.mdf' , SIZE = 102400KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA]
    ( NAME = N'Test_Data', FILENAME = N'F:\SQLDBs\Test_Data.ndf' , SIZE = 5242880KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_DATA2]
    ( NAME = N'Test_Data2', FILENAME = N'F:\SQLDBs\Test_Data2.ndf' , SIZE = 5242880KB ,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
 FILEGROUP [TEST_INDEX]
    ( NAME = N'Test_Index', FILENAME = N'F:\SQLDBs\Test_Index.ndf' , SIZE = 5242880KB,
      MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
    ( NAME = N'Testdb_log', FILENAME = N'G:\SQLLogs\Testdb_log.ldf' , SIZE = 1048576KB,
      MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [Testdb] SET COMPATIBILITY_LEVEL = 100
GO

I think the main problem is that you think that creating the database first will allow you to bypass WITH MOVE. This is not the case.

If the destination file paths don't match those contained in the backup file, the presence of a database (and its file locations) aren't going to make a difference. RESTORE is going to look at the file locations specified in the backup file, completely ignoring the existing database. And this can only be overridden by using WITH MOVE.


Server and Client type Subscriptions What is Server and Client type subscriptions in Merge Replication? How to select one of these during merge replication setup?

Server subscriptions are for:

Republishing data
Serve as alternate synchronization partners (This has been deprecated)
Resolving conflicts according to a priority

If your subscription(s) do not require this behavior then a client subscription will work just fine.  This is covered in Subscription Type.

The Merge subscription type can be specified on the Subscription Type page of the New Subscription Wizard or by specifying a value of local (client) or global (server) for the @subscriber_type parameter in sp_addmergesubscription and sp_addmergepullsubscription.  This is covered in Specify a Merge Subscription Type and Conflict Resolution Priority.

I hope this helps.


SQL Server: how to monitor activity in several databases I have several dbs in production and I'd like to have a high level overview of the activity.

Ideally I'd like to know:

number of users accessing the db per day
number of inserts per table per day
number of updates per table per day

I thought about a little script that would run a query to get the rowcount of each table, and then save it to another db. I could execute it at night.

That would only give the number of inserts, but at least would be a first step.

Is there some tool, logging device, system table, or something that would let me get this kind of info?

I have SQL Server from 2008 onward.

To track the inserts and updates, you can use the below query which will collect the SUM of inserts, updates and deletes against the HEAP or CLUSTERED INDEX for each user table in the database.

Log this to a table and then query the deltas over time to see the inserts\updates\deletes per day. NOTE: This would have to be run against each database you wish to monitor as these DMVs are database-scoped.

USE [Database]
GO
SELECT
    DB_NAME() AS [DATABASE NAME],
    SUM(A.LEAF_INSERT_COUNT) AS INSERTS,
    SUM(A.LEAF_UPDATE_COUNT) AS UPDATES,
    SUM(A.LEAF_DELETE_COUNT) AS DELETES
FROM
    SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A
    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
    AND I.INDEX_ID IN (0,1)

The user access is a little harder. If your users access the databases through an application, and that application uses a single login for SQL Server access, then you can't just track logins to determine user access. You would need to track this via your application.

If you're using individual logins, then you could use a login trigger to add a record to a tracking table to record the user, login time and initial database. See the documentation for more info. A good example can be found here. This is actually for logging sysadmin logins, but could easily be modified for your purpose.


Verify failed SQL Server connections to instances on a given server and email them I am trying to monitor the SQL Server instances on standalone server / cluster server. I would like to setup something from commandline to check for connectivity to each SQL Server instance on the server. Wherever it fails to connect to an instance I would like to have an email (we have smtp server setup) sent to our DBA team.

Each server is having more than 5 instances and we are unable to track them without customer complaining about connectivity, hence trying to be proactive.

Can we try something like osql or sqlcmd?

You can use server audit specification to track failed logins.
use ssis and a script task to read audit file and email it if needed.


Is it better to use AUTOCOMMIT = 0 Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like

INSERT INTO SomeTable (column1, column2) VALUES (val1,val2),(val3,val4),... ?

According to the documentation, AUTOCOMMIT should be turned off in InnoDB.

When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert.
When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 instead of the default value 1


Query to Secondary on Sharded Cluster How to specify query from mongos shell which is inteded to query data from secondary?

The goal is not to bother primary load on write and use data in secondary shards instead.

You have two options:

1) The safest is to use on your driver the "secondary" option as described on the below article:

http://docs.mongodb.org/manual/reference/read-preference/

2) Another option is to tag your secondaries as described below and target your queries against the tag, most drivers offer that option.

Disadvantage is that you have to ensure that these nodes can't become primaries else you will target primaries. Advantage is that you can target certain operations to certain nodes.

http://docs.mongodb.org/manual/tutorial/configure-replica-set-tag-sets/#add-tag-sets-to-a-replica-set

I would say to stick with first option if you just starting using mongo.


Running reports across multiple MySQL databases with the same schema Every client of our online services has its own MySQL database. All databases have exactly same schema, however, a number of databases is constantly changing as we archive db for inactive client's as well as new db gets created after every signup.

Is there a way to run analytics in this kind of environment? There seems to be no way to run query across multiple databases. The same applies to ETL solutions that we have investigated.

Merging databases and updating app code seems like a major effort. Create merged db for reporting purposes using db triggers might be the way to go.

Currently, if we need to run some reports across db, we use PHP script to execute query on every single client db which is very inefficient.

Any recommendations?

Not everything can be done in standard commands, or in complicated combinations of SQL.

I would write in my application coding language (PHP, Java, ...) SQL to fetch the list of databases from information_schema, then iterate through that list, doing the analytics.


Postgres odd behavior with indices I've got a datavalue table with ~200M rows or so, with indices on both site_id and paramter_id. I need to execute queries like "return all sites with data" and "return all parameters with data". The site table has only 200 rows or so, and the parameter table has only 100 or so rows.

The site query is fast and uses the index:

EXPLAIN ANALYZE
select *
from site
where exists (
      select 1 from datavalue
      where datavalue.site_id = site.id limit 1
);

Seq Scan on site  (cost=0.00..64.47 rows=64 width=113) (actual time=0.046..1.106 rows=89 loops=1)
  Filter: (SubPlan 1)
  Rows Removed by Filter: 39
  SubPlan 1
    ->  Limit  (cost=0.44..0.47 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=128)
          ->  Index Only Scan using ix_datavalue_site_id on datavalue  (cost=0.44..8142.71 rows=248930 width=0) (actual time=0.008..0.008 rows=1 loops=128)
                Index Cond: (site_id = site.id)
                Heap Fetches: 0
Planning time: 0.361 ms
Execution time: 1.149 ms

The same query for parameters is rather slow and does NOT use the index:

EXPLAIN ANALYZE
select *
from parameter
where exists (
      select 1 from datavalue
      where datavalue.parameter_id = parameter.id limit 1
);

Seq Scan on parameter  (cost=0.00..20.50 rows=15 width=2648) (actual time=2895.972..21331.701 rows=15 loops=1)
  Filter: (SubPlan 1)
  Rows Removed by Filter: 6
  SubPlan 1
    ->  Limit  (cost=0.00..0.34 rows=1 width=0) (actual time=1015.790..1015.790 rows=1 loops=21)
          ->  Seq Scan on datavalue  (cost=0.00..502127.10 rows=1476987 width=0) (actual time=1015.786..1015.786 rows=1 loops=21)
                Filter: (parameter_id = parameter.id)
                Rows Removed by Filter: 7739355
Planning time: 0.123 ms
Execution time: 21331.736 ms

What the deuce is going on here? Alternatively, whats a good way to do this?

Some of the table description:

id BIGINT DEFAULT nextval('datavalue_id_seq'::regclass) NOT NULL,
value DOUBLE PRECISION NOT NULL,
site_id INTEGER NOT NULL,
parameter_id INTEGER NOT NULL,
deployment_id INTEGER,
instrument_id INTEGER,
invalid BOOLEAN,
Indexes:
    "datavalue_pkey" PRIMARY KEY, btree (id)
    "datavalue_datetime_utc_site_id_parameter_id_instrument_id_key" UNIQUE CONSTRAINT, btree (datetime_utc, site_id, parameter_id, instrument_id)
    "ix_datavalue_instrument_id" btree (instrument_id)
    "ix_datavalue_parameter_id" btree (parameter_id)
    "ix_datavalue_site_id" btree (site_id)
    "tmp_idx" btree (site_id, datetime_utc)
Foreign-key constraints:
    "datavalue_instrument_id_fkey" FOREIGN KEY (instrument_id) REFERENCES instrument(id) ON UPDATE CASCADE ON DELETE CASCADE
    "datavalue_parameter_id_fkey" FOREIGN KEY (parameter_id) REFERENCES parameter(id) ON UPDATE CASCADE ON DELETE CASCADE
    "datavalue_site_id_fkey" FOREIGN KEY (site_id) REFERENCES coastal.site(id) ON UPDATE CASCADE ON DELETE CASCADE
    "datavalue_statistic_type_id_fkey"

Edit: Here's the count distribution

select count(parameter_id), parameter_id from datavalue group by parameter_id

88169   14
2889171 8
15805   17
8570    12
4257262 21
3947049 15
1225902 2
4091090 3
103877  10
633764  11
994442  18
49232   20
14935   4
563638  13
2955919 7

Removing the limit 1 in  the sub query fixed the problem. More discussion on S.O.


MySQL: Data pagination according data on another table I have a database with the following structure

Items table: itemID, itemName
Ads table: AdsId, itemID

I would like to make pagination on items table, 10 items in each page, but first I must retrieve items whose IDs are in the Ads table, and then retrieve other items.
I know I must use limit such as:
SELECT * FROM Items LIMIT $offset, $no_of_records_per_page

And may I joined it with adsTable, such as:
SELECT * FROM Items inner join Ads on Ads.itemID = Items.itemID
LIMIT $offset, $no_of_records_per_page

but how can I achieve what I described?
Thanks in advance.

METHOD #1
You could create a temp table called itemID_paginator and paginate this
CREATE TABLE items_paginator AS SELECT itemID from Items WHERE 1=2;
ALTER TABLE items_paginator ADD PRIMARY KEY (itemID);
INSERT INTO items_paginator SELECT itemID from Items;

You then paginate against itemID_paginator and join it to Ads
SELECT A.*,B.* FROM
(
    SELECT itemID FROM itemID_paginator ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) PG
INNER JOIN Items A ON PG.ItemID = A.ItemID
INNER JOIN Ads   B ON A.ItemID  = B.ItemID;

METHOD #2
If you prefer not to use a temp table, then perform LIMIT directly to the Items table
SELECT A.*,B.* FROM
(
    SELECT * FROM Items ORDER BY ItemID
    LIMIT $offset, $no_of_records_per_page
) A INNER JOIN Ads B ON A.ItemID  = B.ItemID;

I suggested these methods before in the following posts:

Jan 31, 2017 : Slow query while selecting a higher offset from millions of rows
Dec 21, 2013 : Using OFFSET in a large table by a simple sub-query
Feb 21, 2013 : Does MYSQL join before LIMIT?
May 16, 2011 : Fetching a Single Row from Join Table

GIVE IT A TRY !!!