Order by column with specific adjacent value Apologies for the vague title. I wasn't really sure how to describe this.
I have the following tables:
items:
id | name
---|----------
1  | first-item
2  | second-item
3  | third-item

fields:
id | name
---|------------
1  | title
2  | description
3  | available

values:
id | item_id | field_id | value (json)
---|---------|----------|-------------------------
1  | 1       | 1        | "First Item"
2  | 1       | 2        | "This is the first item"
3  | 1       | 3        | 1
4  | 2       | 1        | "Second Item"
5  | 2       | 2        | "This is the second item"
6  | 2       | 3        | 1
7  | 3       | 1        | "Third Item"
8  | 3       | 2        | "This is the third item"
9  | 3       | 3        | 0

Here's an example query with the order by represented as a select to try to illustrate the desired logic :
select `items`.*
from `items`
         left join `values` on `items`.`id` = `values`.`item_id`
         left join `fields` on `values`.`field_id` = `fields`.`id`
where (`fields`.`name` = 'available' and `values`.`value` = 1)
group by `items`.`id`
order by (select `values`.`value` from 'values' where `fields`.`name` = 'title') desc

What I'm trying to do it order the items by values.value for one or more specific fields. Hopefully that makes sense?
The end result should look something like this:
id | name
---|----------
2  | second-item
1  | first-item

I'm really not sure where to start with this and searching has yet to yield any leads. Any help would be greatly appreciated.

Basically Json makes the things, a bit more complicated, but not that much, but as you noticed, i don't think that it is necessary.
My approach as you need the data, is to use a pivot table.
This is actually flexible as it uses all, independent which are needed.
At the end is the query without the flexibility of automation. But then you have manually have to select the item you want.
Another approach is to transfor the values data into a json, bu then you need to extract all vital parts again. It wouldn't look that much prettier

CREATE TABLE items (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO items
  (`id`, `name`)
VALUES
  ('1', 'first-item'),
  ('2', 'second-item'),
  ('3', 'third-item');

CREATE TABLE fields (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO fields
  (`id`, `name`)
VALUES
  ('1', 'title'),
  ('2', 'description'),
  ('3', 'available');

CREATE TABLE values1 (
  `id` INTEGER,
  `item_id` INTEGER,
  `field_id` INTEGER,
  `value` JSON
);

INSERT INTO values1
  (`id`, `item_id`, `field_id`, `value`)
VALUES
  ('1', '1', '1', '"First Item"')
  ,
  ('2', '1', '2', '"This is the first item"'),
  ('3', '1', '3', '"1"'),
  ('4', '2', '1', '"Second Item"'),
  ('5', '2', '2', '"This is the second item"'),
  ('6', '2', '3', '"1"'),
  ('7', '3', '1', '"Third Item"'),
  ('8', '3', '2', '"This is the third item"'),
  ('9', '3', '3', '"0"');

    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(CASE v.`field_id` WHEN "',
         f.`id`,
          '" THEN v.`value` ELSE NULL END) AS `',
          f.`name`), '`'

        ORDER BY f.`id`
      )
      INTO @sql
    FROM `items` i
         left join
         `values1` v
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`;

SET @sql = CONCAT('select MAX(i.`name`), ',@sql,' from `items` i
         left join
         `values1` v
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY title DESC');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | "Second Item" | "This is the second item" | "1"
first-item    | "First Item"  | "This is the first item"  | "1"

select
MAX(i.`name`)
, MAX(CASE v.`field_id` WHEN "1" THEN v.`value` ELSE NULL END) AS `title`
,MAX(CASE v.`field_id` WHEN "2" THEN v.`value` ELSE NULL END) AS `description`
,MAX(CASE v.`field_id` WHEN "3" THEN v.`value` ELSE NULL END) AS `available`
from `items` i
         LEFT  join
         `values1` v
         on i.`id` = v.`item_id`
         LEFT join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY `title` DESC

MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | "Second Item" | "This is the second item" | "1"
first-item    | "First Item"  | "This is the first item"  | "1"

db<>fiddle here


Filling in Dates - Postgres/Redshift SQL I have a dates table with a "calendar_date" field and I have been trying to utilize it for when I need to fill in missing dates when an id does not have a record for a particular day AND have the daily count = 0. I cannot seem to find the proper solution for it.
Result example:

        SELECT
        id,
        date_trunc('day',created_at) as activity_date,
        COALESCE(COUNT(DISTINCT user_id),0) AS daily_count
        FROM events e
        WHERE date(date_trunc('day',created_at)) <= CURRENT_DATE - INTERVAL '1 day' AND date(date_trunc('day',created_at)) >= CURRENT_DATE - INTERVAL '90 day'
        AND name = 'visited site'
        AND user_id != 0
        GROUP BY date_trunc('day',created_at), id

Any ideas/help would be appreciated!

In Postgres (which you explicitly tagged) you can do it like this. But I suspect you don't really use Postgres and the following is not supported by Redshift.
select d.id,
       d.activity_date,
       count(distinct e.user_id) as daily_count
from (
  select distinct e1.id, g.dt::date as activity_date
  from events e1
    cross join generate_series(current_date - 90, current_date - 1, interval '1 day') as g(dt)
  where e1.activity_date >= current_date - 90
    and e1.activity_date <= current_date - 1
) d
  left join events e
    on e.activity_date = d.activity_date
   and e.id = d.id
   and e.user_id <> 0
   and e.name = 'visited site'
group by d.id, d.activity_date

The query first creates a list of all IDs and dates in the desired interval. This is necessary because you also want to group by the id column. Then a left join is used to bring in the events and do the aggregation.
The WHERE clause in the derived table (inner query) means that only IDs are used, that have at least one event in the desired range. If you need all IDs regardless if there was an event or not in that time frame, you either need to pull them from a different table, or remove the WHERE clause of the inner query.


How To Detect locking due to multi row updates Hi we are thinking of moving our updating records operations over from c# to sql, I am afraid of locking problems, how can I detect this problem if it occurs, and if it causes locking, is there any solution other then making small batches

we are thinking of moving our updating records operations over from c# to sql

Typically the performance benefits of multi-row DML outweigh any increased blocking.  Plus you should be using transactions when performing a multi-row update from C#, in which case you'll have similar locking footprint to a server-side multi-row update:  Every row updated will be locked until the transaction commits.
You don't want to monitor locking, you want to monitor blocking.  And it's tracked in the system wait stats, session wait stats, and in query store wait stats, and with the good old Blocked Process Report.
See generally from the docs:
Transaction Locking and Row Versioning Guide
And from Brent Ozar:
Locking and Blocking in SQL Server
and
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


How to create SQL Stored Procedure with parameters of type List I need to create a Stored Procedure that will take parameters of type List. For example :

Select *
 From Table
Where State   in @StateList
   or Lob     in @LobList
   or Company in @CompanyList

The parameters @StateList, @LobList and @CompanyList will contain string data like :

StateList:[ "GA", "AL", "TN" ]
LobList:[ "D20", "PXL", "APM" ]
CompanyList:[ "QQ", "WW", "RE" ]

Does SQL allow this ?

There is no type you can use with SQL Server that would let you pass in and use a list as you've suggested.

You have two options:

Dynamic SQL

Pass the list in as a string value ('(12, 34, 13, 29)'). Build a nvarchar string with your SQL statement, with that value concatenated in:

SET @stmt = N'Select *
  From Table
 Where State   in ' + @StateList + N'
    or Lob     in ' + @LobList + N'
    or Company in ' + @CompanyList

Then, EXECUTE sp_executesql @stmt to run the dynamic SQL.

Use table-valued parameters

SQL Server does allow table variables to be passed, as table-valued parameters. You can pass an 'IEnumerable' as the value in your C# code (see the docs at the link for more data). Obviously, you'd need to re-write your query:

SELECT *
  FROM Table tbl
 WHERE EXISTS (SELECT 1 FROM @StateTbl where State = tbl.State)
    OR EXISTS (SELECT 1 FROM @LobTbl where Lob = tbl.Lob)
    OR EXISTS (SELECT 1 FROM @CompanyTbl where Company = tbl.Company)
;

or

SELECT tbl.*
  FROM Table tbl
         LEFT JOIN @StateTbl st ON (tbl.State = st.State)
         LEFT JOIN @LobTbl lob ON (tbl.Lob = lob.Lob)
         LEFT JOIN @CompanyTbl co ON (tbl.Company = co.Company)
 WHERE COALESCE(st.State, lob.Lob, co.Company) IS NOT NULL
;


Unusual deadlock occuring I have spent a few days trying to debug a deadlock that occurs in production. This is one I didn't understand however.
Below is pseudo code for the stored procedure. The stored procedure generates a deadlock with another instance of itself.

I know that the code is not brilliant, this old code on the system. But I want to understand why this deadlocks. The graph always shows the second selects deadlocking.

The first select performs a table scan, so I believe that places click on all rows in table.

I'd have thought this would queue one stored procedure behind the other. I cannot recreate this after days if trying.

Begin tran

Begin tran

Set isolation level repeatable read

//This select does a table scan, so I believe does X lock on all rows

Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

Delete from queuetable
Where a = X

Commit tran

Begin tran

//Get oldest row - this is the deadlocking query

Select top 1 a,b,c
From queuetable
Order by b

//Mark row to be processed
Update queuetable
Set c=processing
Where a = y

Commit tran

Commit tran

Try without the first BEGIN TRAN and last COMMIT TRAN.

If you are using a stored procedure, the procedure itself already creates a transaction.

Your transaction locks because of the following script:

//This select does a table scan, so I believe does X lock on all rows
Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

From what I see in the script, the with(xlock, rowlock) is not needed.

Try with the following script:

SET XACT_ABORT ON
SET ISOLATION LEVEL REPEATABLE READ

-- This select does a table scan, so I believe does X lock on all rows
SELECT a, b, c
  FROM queuetable
 WHERE a = x

DELETE queuetable
 WHERE a = X

-- Get oldest row - this is the deallocking query
SELECT TOP 1 a, b, c
  FROM queuetable
 ORDER BY b

-- Mark row to be processed
UPDATE queuetable
   SET c = processing
 WHERE a = y


How to use imdpd to import data file into different schema including index from different tablespace as well The original data pump file is generated by the schema which living in two tablespaces(OLD_SCHEMA_DAT and OLD_SCHEMA_IDX), if import back to the original tablespace, everything is fine, but when I use following command:

impdp OLD_USER/OLD_PASS dumpfile=whole.package.dmp directory=DATA_PUMP_DIR
REMAP_SCHEMA=ORIGINAL_SCHEMA:NEW_SCHEMA

It works but with 200 errors stating that

ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00959: tablespace 'OLD_SCHEMA_IDX' does not exist

it looks for the original index tablespace instead of the new one, and even I tried:

impdp OLD_USER/OLD_PASS dumpfile=whole.package.dmp directory=DATA_PUMP_DIR
REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT
REMAP_TABLESPACE=OLD_SCHEMA_IDX:NEW_SCHEMA_IDX

Still did not resolve the problem, then what to do? I am on 11g.

There is only one REMAP_TABLESPACE parameter in de command.
It would be seperated with ",".
REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT,OLD_SCHEMA_IDX:NEW_SCHEMA_IDX

Oracle doc impdp 11.2
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL929


PostgreSQL - How to list all foreign key link to Primary key of a table I have a table res_users with primary key id

I would like to list all the foreign key link to id from all the tables in my database.

You can query the catalog tables:

SELECT la.attrelid::regclass AS referencing_table,
       la.attname AS referencing_column
FROM pg_constraint AS c
   JOIN pg_index AS i
      ON i.indexrelid = c.conindid
   JOIN pg_attribute AS la
      ON la.attrelid = c.conrelid
         AND la.attnum = c.conkey[1]
   JOIN pg_attribute AS ra
      ON ra.attrelid = c.confrelid
         AND ra.attnum = c.confkey[1]
WHERE c.confrelid = 'res_users'::regclass
  AND c.contype = 'f'
  AND ra.attname = 'id'
  AND cardinality(c.confkey) = 1;


Facing Error 'Missing expression' while trying to create a After insert trigger in PL/SQL Hi everyone hope you're doing well.
AS you might know by now I'm new to PL/SQL and I'm trying to write a trigger
as you can see below :

CREATE OR REPLACE TRIGGER T_Insert
  AFTER INSERT ON tag

    for each row
      BEGIN

       Select case
          WHEN EXISTS (Select tag , brand_id
                       from my_tags
                       where tag      = :new.tag AND
                       brand_id = :new.brand_id) THEN

                      UPDATE my_tags set count := count +1
         ELSE
            insert into my_tags (sr_no,tag,count,brand_id)
            valuse (:new.sr_no , :new.tag ,1 , :new.brand_id)
            end case AS SS
     from dual;

 END;

When I want to run the code and create the trigger I receive these errors:

ORA_00936 : missing expression. (Line 15) update statement
SQL statement ignored

am I using the Select Case and When Exists The wrong way?

try this one

CREATE OR REPLACE TRIGGER T_Insert
AFTER INSERT ON tag

for each row
declare
  v_cnt number := 0;
BEGIN
  -- selection number of rows
  -- where tag = :new.tag and AND brand_id = :new.brand_id
  -- the result will be stored in a variable v_cnt
  -- if there no rows matched the Count(1) will return 0
  Select count(1)
    into v_cnt
    from my_tags
   where tag = :new.tag
     AND brand_id = :new.brand_id;

  -- check if some rows Exits
  -- than do an update
  -- else do an insert
  if v_cnt > 0
  then
    UPDATE my_tags set count = count + 1
    -- if you want to update only one row
    WHERE tag=:new.tag AND brand_id=:new.brand_id
    ;

  ELSE
    insert into my_tags
      (sr_no, tag, count, brand_id) values
      (:new.sr_no, :new.tag, 1, :new.brand_id);
  end if;

END;


No results from traceid from sys.fn_trace_getinfo(0); SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#);

The above command returns no results.  There should be a bunch of trace event id being traced.

It looks like you have no traces running on your server.

The function mentioned in your post title is:

SELECT * FROM sys.fn_trace_getinfo(0)

Using either a 0 or a NULL as the parameter will produce a listing of all running traces.  That list could look like this:

traceid property    value
1   1   2
1   2   D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_6215.trc
1   3   20
1   4   NULL
1   5   1
2   1   1
2   2   NULL
2   3   NULL
2   4   NULL
2   5   1

However, if you use another traceid it will only show the description rows for that trace. (Or no values if the traceid is invalid.)

The other function that you show was:

SELECT DISTINCT(eventid) FROM sys.fn_trace_geteventinfo(#);

Using a traceid, this will produce a list of event ids for the traceid.  An invalid traceid will return no rows.

Since you are not getting any return on your queries, then there should be no traces running.


How come an INNER JOIN returns zero rows when I use coalesce? I have the following query:

SELECT CPC.importe
FROM fac_factura FAC
INNER JOIN fac_concepto_periodo_control CPC ON CPC.id_factura = FAC.id
INNER JOIN fac_factura_deuda_pendiente FDP ON FDP.id_factura = FAC.id
INNER JOIN fac_item_facturacion FIF ON CPC.id_item_facturacion = FIF.id
WHERE FIF.codigo = 427
AND FDP.estado ILIKE 'CANCELADA'
AND CPC.estado ILIKE 'FACTURADO'

The column named importe is numeric but the JOIN conditions are not being fulfilled so it returns a blank value instead of a NULL mark.

How can I set this value to 0 instead of a blank value, I tried with COALESCE but it's not working.

The column named importe is numeric but the JOIN conditions are not being fulfilled so it returns a blank value instead of a NULL mark.
How can I set this value to 0 instead of a blank value, I tried with COALESCE but it's not working.

If the join conditions are not being fulfilled you have 0-rows. An INNER JOIN on false returns 0-rows: not one row with a null value, not one row with a zero.
SELECT x,y
FROM ( VALUES (1) ) AS t(x)
JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y
---+---
(0 rows)

Returns nothing. What you want is an outer join.
SELECT x,y
FROM ( VALUES (1) ) AS t(x)
LEFT OUTER JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y
---+---
 1 |
(1 row)

Now that you have a row, you can use COALESCE
SELECT x, coalesce(y,0) AS y
FROM ( VALUES (1) ) AS t(x)
LEFT OUTER JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y
---+---
 1 | 0
(1 row)


Named Parameters in SQuirreL SQL? Is it possible to set named-parameters in queries SQuirreL SQL?

Figured this out, it's easy, you just add :paramName in your query and it automatically asks your for the parameters when you run the query.


Stored procedure query plan different in Always on server Stored procedure run 10 sec in primary server but keeps running(not executed) in secondary server in always on configured servers. I've verified all configurations, both are identical but execution plans are different. How to make both plans are identical in always on sql server 2012 version.

They are not identical:
on primary you have probably different cached data.

A longer time can be consequence of more time spent on disk.
Also statistics are different: you have in both database the statistics created on primary. But on secondary you also have statistics stored in tempdb.

You can go deeper comparing the execution plans.


Circular replication with servers in different locations My service runs on single machine, and database (mysql 5.1.66) is replicated to 2 other identical slaves (with lesser system resources, mysql 5.1.66) via chained replication. Machines are located in different isps. There will always be a single entry point to service, and if master goes down, i'll switch to the next slave and continue.

simply the scenario is as master -> slave1 -> slave2.

But i don't want to deal with resyncing the master, when it comes up. I'm thinking of switching to circular replication model. which is:

master -> slave1 -> slave2 -> master ...

I've never used circular model, and the latency between master and slaves do not bother much in current implementation. But with circular model, i guess master will also be waiting for a reply from slave2, even though no nodes other than the master will be writing the database. Is this latency -if exists- a show stopper? Is it sane to implement circular replication between different locations?

CIRCULAR REPLICATION
You should upgrade to MySQL 5.5 because it features Semisynchronous Replication
There are settings that can let you dictate how much delay a Master can tolerate before switching back to asynchronous replication until latency dies down. I have written posts on Semisynchronous Replication before:

Aug 05, 2011 : Is MySQL Replication Affected by a High-Latency Interconnect?
Jul 25, 2012 : Mysql database replication on different vlan/subnet/another site
Mar 12, 2013 : Breaking Semisynchronous Replication in MySQL 5.5

Some have argued that it is not good over a geographic distance. This is debatable depending on several factors

Using Statement-Based or Row-Based Replication
Actual Distance Between Data Centers
Doing DML statements on all MySQL Masters
If you are plain old content living with latency

DATA RESILIENCY
If you have Multiple Masters in Different Data Centers for the purpose of Disaster Recovery (or as the politically correct would say, "Business Continuity"), you should try to make the Data Center intrinsically resilient for the sake of MySQL. How would you do that?
Setup DRBD in each Data Center. Then, setup Circular Replication among the DRBD Primaries.
Here are my posts on setting this up

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Dec 19, 2011 : Best way to setup master to multi master replication
Dec 31, 2012 : Automatic MySQL Replication Fail-over Strategy


SQL Server : not allocating all memory that it has on disposal Yesterday, we moved from dedicated server to amazon AWS. And since then we see strange thing. Our SQL Server is not using more than 2.8GB of RAM, which is strange because on old server it always load everything to RAM. Our database is 289GB large, we have so many indexes etc. We are running on Microsoft SQL Server Standard edition, which should give us access to 128GB RAM for database usage.
What could it be the reason? Anyone experienced similar issue? Beside this, database seems to be working fine at the moment, although it had issue 1 time during 24 hours where it became completely unresponsive, so we restarted the SQL Server at that time. But something is strange, something is not right, because that ram should be filled to maximum as it always does.

Check the following counters:
select object_name,counter_name,instance_name,cntr_value/1024/1024 [GB]
from sys.dm_os_performance_counters
where counter_name like 'T%Server%'

Total Server memory - current consumption
Target Server memory - intention to consume next


Reducing DB size while restoring I have DB backup with size approx. 150 Mb.

I am trying to restore it and getting an error:
"System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 67771760640 additional free bytes, while only 45085224960 bytes are available. (Microsoft.SqlServer.Smo)"

Restore is about to create DB with much bigger size than backup has. Is there a possibility to reduce size of DB being restored?

A restore always restores the files to the same sizes they were when they were backed up.

There is currently no way to "restore with shrink."

You'll need to restore the files to a drive with sufficient free space, and perhaps consider shrinking the files at that point, then use your preferred technique to move the files to the spot you want.


MySQL: split value in column to get multiple rows I have some data in a table like so:

product_id      | categories
----------------+-------------
10              | 9,12
11              | 8
12              | 11,18,5

I want a select statement that would produce this output:

product_id      | category_id
----------------+-------------
10              | 9
10              | 12
11              | 8
12              | 11
12              | 18
12              | 5

I don't know how to phrase this scenario to be able to google it.

What you are looking for is the inverse of a GROUP BY aggregate query using the GROUP_CONCAT. If you are willing to store the results in a temp table, I got just the thing.

First, here is the code to use you sample data in a table called prod and a temp table called prodcat to hold the results you are looking for.

use test
drop table if exists prod;
drop table if exists prodcat;
create table prod
(
  product_id int not null,
  categories varchar(255)
) engine=MyISAM;
create table prodcat
(
  product_id int not null,
  cat  int not null
) engine=MyISAM;
insert into prod values
(10,'9,12'),(11,'8'),(12,'11,18,5');
select * from prod;

Here it is loaded

mysql> use test
Database changed
mysql> drop table if exists prod;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists prodcat;
Query OK, 0 rows affected (0.00 sec)

mysql> create table prod
    -> (
    ->   product_id int not null,
    ->   categories varchar(255)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> create table prodcat
    -> (
    ->   product_id int not null,
    ->   cat  int not null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into prod values
    -> (10,'9,12'),(11,'8'),(12,'11,18,5');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from prod;
+------------+------------+
| product_id | categories |
+------------+------------+
|         10 | 9,12       |
|         11 | 8          |
|         12 | 11,18,5    |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

OK, you need query to put together each product_id with each category. Here it is:

select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;

Here it is executed

mysql> select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
    -> replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;
+----------------------------------------------------------------------------------------------------------------------------------+
| ProdCatQueries                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------+
| insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;                 |
| insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;                                 |
| insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL; |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Let me run each line by hand

mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

OK, good. The queries work. Did the prodcat table populate properly?

mysql> select * from prodcat;
+------------+-----+
| product_id | cat |
+------------+-----+
|         10 |   9 |
|         10 |  12 |
|         11 |   8 |
|         12 |  11 |
|         12 |  18 |
|         12 |   5 |
+------------+-----+
6 rows in set (0.00 sec)

mysql>

OK Great. It has the data.

To be honest, I think SQL Server can perform all of this in a single pivot query without a handmade temp table.

I could have taken it to another level and concatenated all the queries into a single query, but the SQL would have been insanely long. If your actual query had 1000s of rows, a single MySQL would not have been practical.

Instead of running the 3 INSERT queries by hand, you could echo the 3 INSERT queries to a text file and execute it as a script. Then, you have a table with the products and categories combinations individually written.


T-Log Backup in AOAG Can I take a log backup manually in Primary replica when all my databases are synchronized with secondary replicas in AOAG?

Will it break my Log sequence for Synchronization?

In AlwaysON AGs do we take T-Log backups for Synchronization manually or do they happen over the wire (End points 5022)? Can some one please simplify this process for my understanding?

I would like to understand will it affect my AOAG with LSN Mismatch?

You can manually take transaction log backup on any replicas of AG. For primary and secondary replica you can take normal T-log backup, only copy_only full backups are possible on secondary replica. I would suggest to take T-log backup from secondary replica this will put less load on primary replica and would also not affect peak time I/O.

In any case log backup chain would NOT be disturbed just make sure that when you take T-Log backup you do not delete it but store it like you do for your normal backups so that you can use it in case of disaster.

  In AlwaysON AGs do we take T-Log backups for Synchronization manually or do they happen over the wire (End points 5022 ) . Can some one please simplify this process for my understanding ?

T-Log backup has no relation with synchronization, yes communication happens via endpoints and commit handshake happens via endpoints. For more details read Always On Series


concurrent insertion in mutually exclusive tables in oracle There are three tables:

create table C(id_row integer primary key)

create TABLE A(col_a1,col_a2,col_a3,..., id_row integer primary key, constraint fk_a_c foreign key (id_row)  references C(id_row))

create TABLE B(col_b1,col_b2,col_b3,..., id_row integer primary key, constraint fk_b_c foreign key (id_row)  references C(id_row))

Table C is populated with a number of values.
I have to write an application that will allow its users to insert in table A or B an id_row already present in table C, constrained by the fact that a single id_row can be present at most in only one of A and B.

The application I have to write will establish a connection to the database for each user and start a new transaction for every id_row to be inserted.  The isolation level must be READ COMMITTED.

The procedure I would write to implement mutually exclusive inserts would be this:

 select * from table c where id_row =in_id_row for update; --lock the row in the parent table
 check id_row is not already in a or b, if it is raise exception;
 proceed to insert the row in a or b.

Does anybody know  other approaches to achieve this behavior without using   'select for update' or 'lock table'?

One way is to add discriminator column, something like

create table c (id_c int not null, val char(1))
create index idx_c1 on c(id_c,val);
alter table c add constraint chk_val check(val in ('a','b'));

alter table c add constraint c_pk primary key (id_c) using index idx_c1;
alter table c add constraint c_uq unique (id_c,val) using index idx_c1;

create table a(id_a int not null primary key,
val char(1) default 'a' not null ,
constraint fk_t foreign key(id_a,val)  references c (id_c,val));

alter table a add constraint chk_val_a check (val ='a');

Similar for table (but check constraint should b check(val='b').

Then detail record can be in table a or b, but not in both. Overhead is small thanks to the fact Oracle allows multiple constraint supported by the same index.


How to compare write speed before and after adding index? I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on a development server. I just want to know the methodology of testing the writing speed.

I want to know the methodology of testing the writing speed.

I am assuming you want to know if queries are running faster. It can be read, write, or a combination of both.  There are many approaches you can take. It depends on the usage pattern of the table. I am sure I won't be able to list everything, but the following points will give you enough tools to measure the success of your newly created non-clustered index. Despite the method you choose, always be consistent with your test. Test with the same workload and on the same hardware.

You want to confirm if the new index is being used? How can you tell if an index is, being used? by Paul Randal explain how to do that.
If you measure the effectiveness of your non clustered index by using one query (or one store procedure call), you can use some of the methods listed below.

i) Comparing CPU, time, read, write. Measuring SQL Query Performance by Matthew McGiffen explains how to do that.

ii) Adam Machanic originally wrote SQLQueryStress, which is a fantastic tool to test the performance of a single query. How do I best measure the query performance? has more details on that plus talking about statistics parser.

iii) Compare Showplan in SSMS can give some good insight into the pre and post-
performance. Compare execution plans documentation by Microsoft explains how to use it.

If you need to measure a workload (a combination of multiple queries or stored procedures), you will need to do more work.

i)As explained in the comment section by @David Browne, you can use the Experiment Assistant.

ii) You can use ostress tool before and after the change. SQL Server stress testing using OStress by Nikhilesh Patel explains how to use the tool


Hints gc_grace and expirations Have 3 node cluster. RF=3. Table with gc_grace_seconds = 120 secs. max_hints duration as default at 3 hours.

inserted a row

nodetool flush on all the 3 nodes

bring down node 3

delete the inserted row

nodetool flush on node 1 and node 2.

hints directory on node 1 and node 2 show the hints files.

Now I bring up the node 3. the hints are sent to the node 3 and deleted entry is made on the node 3.

Why is this behaviour? Is'nt the hint supposed to be expired with the gc_grace_seconds over? hints should not be sent to node 3. Am I missing something here?

help appreciated

Hint and gcgrace(you can set in table level) are two different parameters in Cassandra. irrespective of gcgrace seconds a hint will be stored in system.hints table for the >max_hint_window_in_ms< period mentioned in cassandra.yaml and will be removed after that. max_hint_window_in_ms is your SLA to bring the down node back online and run repair or the deleted data will comeback as a zombie.


How to add 1 milliseconds to a datetime string? Based on a select, I can return x rows like this:

1   2019-07-23 10:14:04.000
1   2019-07-23 10:14:11.000
2   2019-07-23 10:45:32.000
1   2019-07-23 10:45:33.000

We have all milliseconds with 0.

Is there a way to add 1 by 1 milliseconds, so the select would look like this:

1   2019-07-23 10:14:04.001
1   2019-07-23 10:14:11.002
2   2019-07-23 10:45:32.003
1   2019-07-23 10:45:33.004

I'm trying to create a cursor or even a update with no success.

This is the query to get the results I want:

  select top 10 ModifiedOn
    from [SCHEMA].[dbo].[TABLE]
  where FIELD between '2019-07-23 00:00' and '2019-07-23 23:59'

There are 81k values. The field is DATETIME.

Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2).

Documentation

Important quote:

  Accuracy  Rounded to increments of .000, .003, or .007 seconds


PostgreSQL exit status when running a file When running PostgreSQL with a single SQL command error codes are returned as expected:

% psql -c "SELECT * FROM AWDASDASDASDAS" my_db
ERROR:  relation "awdasdasdasdas" does not exist
LINE 1: SELECT * FROM AWDASDASDASDAS
% echo $?
1

But when running a file, the error is suppressed:

% psql -f test.sql my_db
psql:test.sql:1: ERROR:  relation "awdasdasdasdas" does not exist
LINE 1: SELECT * FROM AWDASDASDASDAS
% echo $?
0

Any idea how to get these errors back?

Adding this to the first line of the SQL file does the trick:

\set ON_ERROR_STOP 1

New comments (Thanks!) suggest the following solutions:

-v ON_ERROR_STOP=1
-v ON_ERROR_STOP=on


Inserting fetched records into new table I'm fetching some records with below query:

;with tb1 as (select * from tb where condition), select ... from tb1 join tb2 condition

I want to insert the records fetched with the above query to a new table. How can I do that?

I tried this:

select * into NewTable from (followed by above query)

and this:

;with Tb1 as (select * from tb where condition), select * into NewTable from (select ... from Tb1 join tb2 condition)

But it doesn't work. Any tips? Thanks.

EDIT:

Acutal QUERY which I need to insert into New Table:

; with dub as
(select docnumber, sum(amount) summedamount from stg_invoice_cust_vw where year(crmcreateddatetime)=2017
group by docnumber
having count(docnumber)>1)
select dub.docnumber, dub.summedamount, sup.slamount SLAmount, (dub.summedamount - sup.slamount) diff from STG_INVOICE_SUP_VW sup inner join dub on dub.docnumber = sup.docnumber
where (dub.summedamount - sup.slamount) <> 0

Instead of a SELECT you just follow the CTE with a SELECT INTO:

;WITH cte AS
(
   SELECT x = CONVERT(int, 1)
)
SELECT x INTO dbo.TrashMe FROM cte;

With your new query:

; with dub as
(
  select docnumber, sum(amount) summedamount
  from stg_invoice_cust_vw where year(crmcreateddatetime)=2017
  group by docnumber
  having count(docnumber)>1
)
select
  dub.docnumber,
  dub.summedamount,
  sup.slamount SLAmount,
  (dub.summedamount - sup.slamount) diff
-----------
INTO dbo.NEW_TABLE_NAME --<--------------------------- *** HERE ***
-----------
from STG_INVOICE_SUP_VW sup
inner join dub on dub.docnumber = sup.docnumber
where (dub.summedamount - sup.slamount) <> 0

Note you can only do this once... the next time you run this code, you will get an error that NEW_TABLE_NAME already exists.


MySQL `innodb_log_file_size`: how big is too big? In many answers, discussions, and comments, the prevailing wisdom seems to be to "set innodb_log_file_size to be 25% of the inndb_buffer_pool_size.

What about fairly large buffer pools? For example, I have a server with 64GiB of memory and my buffer pool is currently 38GiB. 25% of that is 9.5GiB. What are the risks of using a "large" innodb_log_file_size versus leaving it "too small"?

With 64GiB available, I'm likely to increase my innodb_buffer_pool_size to something greater than 40GiB, possibly higher. Something makes me nervous about a 10GiB+ log file size. Am I needlessly worrying?

You have to find the right tradeoff between recovery time in the event of a crash and how much write throughput your database will be expected to support.

Percona has a great post discussing a similar scenario to yours, with a 64GB buffer pool, and about 2/5ths of the way into the article you can see a nice comparison he does between a 2GB and 15GB logfile.

15GB allows innodb enough log space to 'smoothly' flush pages out of the log, whereas with 2GB there is more pressure to keep the log from filling up, which ultimately hurts performance as pages have to be evicted. In the benchmarks they cite, performance is 20% lower as a result.

TL;DR you can't really go wrong with a larger log file, esp. if you have a slave to failover to in the event of a crash, but you can certainly hurt performance with one that is too small.


SQL Check Constraint that requires a not null value in a column based on the value of another column I have a table with the following two columns

OnSite BIT NOT NULL
ClientId INT NULL

I want to add a constraint that requires ClientId to be NOT NULL when OnSite is true(1).

I could not find anything on the site.

Thanks

You want to enforce the implication:

(OnSite=true) => (ClientId is not null)

This can be rewritten as:

(OnSite=false) or (ClientId is not null)

Your constraint therefore becomes:

CHECK ( OnSite=0 or ClientId is not null)


Log shipping Primary database log backup job failing/stuck We have set up LS to do cutover from standalone SQL server 2012 to clustered 2012 servers.

The primary database where we have set up LS is also involved in database mirroring and is about 1.8 TB in size. This is PERF env to test the actual cutover and transactions are not frequent on database where LS is set up.

Issue: I see that LS backup job on primary runs longer than scheduled [every 15 mins] or fails with below error:

  Unhandled Exception: System.IO.FileLoadException: The process cannot
  access the file because it is being used by another process.
  (Exception from HRESULT: 0x80070020)    at

There is no fixed pattern when its failing. generally it completes in 18-20secs but on some runs it fails when running over 50 mins or so.

How can i troubleshoot this further?

Edit: @ Log backups are from 100-200 MB generally every 15 mins

How can i troubleshoot this further?

Since you can't make it happen every time a backup occurs, I'd run procmon and filter against the folder you're sending the log backups. When it occurs, stop the procmon and investigate what applications are taking locks on the file. My guess, though, is going to be antivirus or some other filter driver filesystem watcher. The fact it happens more frequently on larger files is a giveaway.

If you know it's going to fail, or is currently having issues you could use process explorer and search for the file in the handles and see what processes are touching it.

This is happening at the windows/driver level, not the SQL Server application level.


COPY query with variable path I am trying to write a stored procedure that imports a file but what I've noticed is that COPY can be followed by string literal and no variable?

I am trying to achieve something like this

FUNCTION insert_file (IN filepath VARCHAR(255)) ...
   COPY filepath ...

is it even possible or I need to do some magic?

Yes, for example:

FUNCTION insert_file (IN filepath VARCHAR(255)) ...
sqltxt:='COPY table_name from '''||filepath||''' with (FORMAT csv)';
execute sqltxt;

'''||variable||''' will produce 'value' with quotation marks.

'||variable||' will produce the value without quotation marks. usefully for table names and integers for example.

You need to declare sqltxt as text.


Merge 2 mongodb databases Our team have hard time. Situation:

API went down, all infrastructure (API + MongoDB) terminated.
We restore API and mongodb (data restored from backup).
After a while (let's say 1 day) we discovered we used not latest backup, but backup from month ago. We cannot just shutdown API and restore from latest backup, because users already created data for 1 day.

State: on our prod DB 1 month of production data has been lost. But in the same time we have this gap inside backup.

Question: what is best solution to merge missing data to existing prod? Better with no downtime.

P.S. Because if this we had to create some data manually, for example, recreate some users. What gonna happen when we merge these 2 DBs?

Bring second instance up, restore data to that instance. Then mongoexport data to json files what you can mongoimport to current mongodb with "--mode upsert --upsertFields '_id'" (using _id as a key)


Do I always need boilerplate code to prevent nested transactions? Because I never know if my procedure might be called from other procedures that have an opened transaction, should I always check for existing transactions before opening a new one?

It seems like a bit of boilerplate code that I need to repeat everywhere and it will make for worse readability.

I'm trying to future proof against this error:

  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Example of boilerplate code Exception handling and nested transactions

Unless you like typing it all out by hand, yes, you should use boiler plate code for transactions in procedures, if your need explicit transactions.  The pattern I use to to wrap all my non-boiler plate code within the transaction in a BEGIN/END block, with a comment on the same line after the begin.
I also have a block before the transaction code for all setup/work that can be done outside a transaction.  For example, suppose I need to create a transaction for all accounts in a given state.  I may identify all accounts in a certain state and load them into a temp table.  Then I build out the transaction, line items, etc., in to temp tables doing all the calculations, etc. outside the main transaction.  Note: I do not care if someone if price changes during this process because the business would consider it valid as long as everyone in the list got the same pricing (either just before) or just after, the change.
When I go to insert the transactions and line items, I do all of that in the boiler plate to ensure that it is impossible to have a transaction without the corresponding line items.  Also, I try to make more of my processes "trial-able"... you can try it in production, output what the results would look like, then rollback.
Keep in mind single statements have an implicit transaction, so no need to boiler plate if you are really just running one query.  Or, if you are running multiple queries and a change between is acceptable for your particular case.
Lastly, for some cases, I also add internal testing as well, ensuring the state of things before I start, while in process, and a check at the end that we haven't arrived at an invalid state.  A lot of this has to do with users using a flexible system, then doing something that breaks assumptions.  These are generally high importance/infrequently run scripts (e.g. Billing).  I also tend to run in trial mode just before production mode, to catch any issues first.
Because of that, my boiler plate also includes the code to deal with testing and for the trialing of results.  I just delete it when I don't need it.


Log file won't shrink - suggestions? I have a 225GB DB with a 390GB log file, which has only recently exploded in size. I am trying to shrink it - per these instructions: https://www.brentozar.com/blitz/transaction-log-larger-than-data-file/

I am not able to Shrink it on SSMS or by the DBCC SHRINKFILE command. I tried waiting until it was not in use, and also tried a Log backup then running the DBCC SHRINKFILE again, but to no avail. Any ideas on what I can do?

I saw in this article (https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/) that it says I could try flipping the recovery model over to Simple, run the shrink, then flip it back to Full, but that sounds risky to me so I am cautious to try that.

Also worth noting is that my database is Mirroring and currently stuck in a Suspended state.

Wondering if you guys could lend any advice. We are your typical DBA-less shop, so I appreciate any novice-conscience pointers or suggestions! Thanks!

Your log_reuse_wait_desc is database_mirroring and you mentioned it currently stuck in a Suspended state

select name, log_reuse_wait_desc from sys.databases

You should find a cause your mirroring was suspended. Try to search messages that include Database mirroring has been suspended in ERRORLOG file on the principal. It could be like one below:

  2019-08-13 11:40:48.98 spid64s     Error: 1453, Severity: 16, State:
  1.

  2019-08-13 11:40:48.98 spid64s     'TCP://xxx.com:5022', the remote mirroring partner for database 'DB', encountered error 5149, status 3,
  severity 25. Database mirroring has been suspended.  Resolve the error
  on the remote server and resume mirroring, or remove mirroring and
  re-establish the mirror server instance.

Using error number you are able to get its description from sys.messages:

select * from sys.messages where message_id = 5149

This case:

  MODIFY FILE encountered operating system error %ls while attempting to
  expand the physical file '%ls'.

That means there is not enough physical space to expand a file on the mirror.

After solving any found errors you have to turn on your mirroring. Run on the principal:

ALTER DATABASE YourDBName SET PARTNER RESUME;

In case you won't be able to find any related errors in ERRORLOG, just try to resume your mirroring using a command above and then look after the database and check your ERRORLOG for errors once the mirrored database goes to the suspended state (if it does).


In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns? In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row).
How can I alter this table so that it has a composite key which enforces that no duplicate rows exist? Also, the table below does not have a primary key. How can I add a primary key to it?

Possible solution: add into the table structure one more column which is generated and concatenates the whole row, then define this column unique.
Of course if you'll realize this directly then this index will be larger than the table body itself, and it will decrease processing speed dramatically. To avoid this select some hash function, which provides the collision probability small enough, and apply it to concatenated row content. For NULLable columns add a byte which depends of the column value is null, and replace the column's NULL with some constant value.
For example, for the table
CREATE TABLE test ( i_column INT,
                    t_column DATETIME,
                    s_column TEXT );

this may be
ALTER TABLE test
ADD COLUMN check_uniqueness BINARY(128)
           GENERATED ALWAYS AS (SHA2(
                                     CONCAT(
                                            i_column IS NULL,
                                            COALESCE(i_column, 0),
                                            t_column IS NULL,
                                            COALESCE(t_column, '2000-01-01'),
                                            s_column IS NULL,
                                            COALESCE(s_column, '')
                                           ),
                                     512
                                    )
                               ) VIRTUAL,
ADD UNIQUE INDEX checking_uniqueness (check_uniqueness);

DEMO fiddle


Grant rights to create a table with an identity column on another schema I have a user in charge of deploying DDL on other schemas than its own.
This user can create a table on those schemas, it can also create sequences on those schemas.

But when I try to create a table with a GENERATED IDENTITY column on those schemas,
the user encounters an ORA-01031: privileges insuffisants

When this user try the same create table on its own schema he can do it properly.

This user was given the following rights :

GRANT CREATE ANY SEQUENCE TO ddl_role;
GRANT CREATE ANY TABLE TO ddl_role;

Here is an example of the query executed :

CREATE TABLE "sch1"."tab1"
   (        "col1" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE)
TABLESPACE "sch1_data";

The following queries work :

CREATE TABLE "sch1"."tab1"
    (        "col1" NUMBER )
TABLESPACE "sch1_data";

CREATE SEQUENCE "sch1"."seq1";

References :
https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1

In order to create a table using the GENERATED BY... mechanism you need also the SELECT ANY SEQUENCE privilege, in addition to CREATE ANY SEQUENCE.

I don't know why that is the case, since you are able to create a standalone sequence. I suspect it has to do with setting up the link between the table and the sequence.

Here is an example:

SQL> connect pg_test/pg_test@graphdb
Connected.

SQL> select privilege from user_sys_privs;

PRIVILEGE
-------------------------------------------------------------------------------
CREATE ANY TABLE
CREATE SESSION
CREATE ANY SEQUENCE
SELECT ANY SEQUENCE
ALTER SESSION

5 rows selected.

SQL> create table scott.t1 (i number GENERATED BY DEFAULT ON NULL AS IDENTITY);
Table created.

And in the other schema:

SQL> connect scott/tiger@graphdb
Connected.

SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ ---------------------------------
...
T1                             TABLE
ISEQ$$_81682                   SEQUENCE

50 rows selected.

If I revoke the SELECT ANY SEQUENCE privilege from user pg_test then the table creation will fail.


Combining a tree structured Group model with its HABTM User model in one VIEW I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a Has-and-Belongs-to-Many (HABTM) relationship using the join-table groups_users

I want to create a view that creates a new tree which includes all the groups and all the users as children of the groups they belong to. The view should have the columns id, parent_id, model, foreign_key, lft and rght.

The id column should just be the Nth row and parent_id, lft and rght can't be just copied from the Group model, they will have to calculated for the tree of the view.

An example of what the view could look like:

| id | parent_id | model | foreign_key | lft | rght
----------------------------------------------------
|  1 |    NULL   | Group |      1      |  1  |  14
|  2 |     1     | User  |      1      |  2  |   3
|  3 |     2     | Group |      2      |  4  |   7
|  4 |     3     | User  |      3      |  5  |   6
|  5 |     4     | Group |      3      |  8  |  13
|  6 |     5     | User  |      2      |  9  |  10
|  7 |     5     | User  |      4      | 11  |  12
|  8 |    NULL   | Group |      4      | 15  |  20
|  9 |     8     | User  |      4      | 16  |  17
| 10 |     8     | User  |      5      | 18  |  19

How do I do this?

Can it be done with some super complex SELECT statement or do I need to use a procedure?

I'm not super skilled at MySQL; I can do simple SELECTs and JOINs, but this is way beyond my skill level.

This can't be done with a view.

Views in MySQL cannot reference variables.  Try defining even a simple view that references a session variable, and there's no joy.  Take a working query that references a variable and try creating a view using that query:

ERROR 1351 (HY000): View's SELECT contains a variable or parameter

Granted, there's a hack-around for this: you can call a stored function that you wrote, which returns the value of the variable you want to be accessible to the view.  But, oops, we're already out of the scope of "view".

Additionally, a view, like a query, has no ability to iterate through rows and do something in "this" row because of what happened in "that" row.

Granted, again, there are hacks with variables in queries that can emulate some of that behavior but on the best days they can only look backwards and not forwards, and they don't always "see" the rows in the same sequence that the rows are returned.

I see three possible approaches to this, if SQL is where you really want to do it:

Option 1 involves a stored procedure and a temporary table.  A stored procedure can iterate through rows with a cursor, and you'd need a temporary table since stored procedures don't have arrays or hashes.  Iterate through your source data, either with a cursor or in a loop that starts by finding the top parent node and then each subsequent select based on what you've found so far... populating the temporary table as you go, taking precautions to avoid infinite loops caused by circular references in parent_id values (gotcha!), and then SELECT from your temporary table within the proc to return its contents as a result set to the client.

You might even end up with a second stored procedure that you call from the first one, which then recursively calls itself, to traverse your tree and build your nested set.

Option 2 involves insert, update, and delete triggers on the group and user tables, which would rebuild your MPTT structures every time any modification is done to group and user.

This would, in a sense, be the "most correct" way to do it, since your left and right ids would never be inconsistent with the underlying data... but as @dogmatic69 has pointed out, it's a very expensive operation... and not without its own snags, since you're limited to what you can to to table_x while you're inside a trigger on table_x.

On the other hand, with this option, when you needed to see the tree, the work to build it has already been done... so, much faster on SELECT.

Option 3 is to calculate the tree values with something other than SQL... so, really, I lied earlier, and there were only two SQL options I've come up with.  Calculate it all in PHP, Perl, etc., and then populate the database with the values you calculated, which is how I generally do it. :)  But I get away with it, because the parent/child relationships in my databases are not updated by any other process.  If they were, I'd be staring down the barrel of Option 2.


How should I use indexes with MySQL? Can someone give me a brief intro to indexes? My questions:

Why would I want to use indexes?
How I should use them? It looks like I can create multiple indexes on a single table - what is that for?
How can I find out if I need to use them?
Are indexes created automatically for me with InnoDB?

You don't, initially you want to create your table relationships and infrastructure without the indexes.

This is because until you know what how often you query a table, and the various queries, how would you define the BEST index? Indexes only speed up queries that intend to retreive data, more specifically for dealing with large volumes of data.

I would revise them, only when things begin to "slow down".

Primary Keys - define your Primary unique index for a table. Auto Increment fields are generally your primary keys, sometimes you may have a joiner table with a Many to Many relationship, where you don't want duplicate records so you'd make ALL fields the primary key.

For Example: parent_id is 1 and child_id is 1, but you don't want more than 1 record in the joiner table with 1 relating to 1.

Foreign Key indexes (keys that relate to another table), which are for integrity. I would recommend using these when creating your tables. Cascade updating and deleting is used to ensure when you delete a parent record, the children records associated are automatically deleted.

Although you assign your foreign key constraints to a "child" table referencing it's parent, when executed MySQL recognises "When item X is deleted from Parent_Table, we shall delete ALL records from Child_Table with a parent_id of X" - I hope this makes sense.

Index keys - are used to speed up queries which collect records, or even joining records from related tables, and sub-queries (Although I discourage the use of these).

FYI - InnoDB is a transactional database, thus unlike MyISAM, will ONLY lock an individual ROW when modifying a table (inserting, deleting, updating records). MyISAM locks the entire table when modifying records within it (hense why data importing large volumes of data to a MyISAM is discouraged).

IMO - You should use foreign key constraints, in INNODB tables. Ensure your tables have primary keys where necessary at most. Then when you're done, look at your data and keep a constant eye on how slow the queries become, then consider the best index forward from there.


Can I rename the View Report button in SSRS I was wondering if it is possible to rename the button View Report in SSRS or even add some command buttons with it.

Nope. That button can't be edited.

Up until SSRS 2016, the amount of customization possible in SSRS outside of the report canvas was close to zero. With 2016, there have been some improvements, but I believe that button is still not customizable.


Exclude Generated Objects - SQL Developer I am a software developer that supports and develops an application that connects to both Oracle databases and SQL Server databases.

I have created a connection to a database (orcl) using the SYS account.  There are hundreds of system generated tables e.g. access$, aclmv$ etc.  How do I hide all these tables so that I can see the tables I have created.  I have tried applying a filter to the tables (Exclude Generated Objects), but this has not worked.

There is no such thing. Unfortunately you can not create fully customized filters, you can use only the predefined ones. Even if you could, there is no reliable way of filtering built-in objects. Starting with 12c, there is a new column in the USER|ALL|DBA_OBJECTS views, called ORACLE_MAINTAINED, but we can not rely on that, because its is not accurate (for example, AWR objects or some built-in directories and queues are not accounted as maintained by Oracle).

As it was already mentioned, do not use SYS, that is for administrative tasks. Create your own user, and log in as that user.


Suspended query causing high CPU usage Environment detail:

  SQL Server 2008 R2.

I have applications running on JBoss that access the DB using JDBC driver.

Issue: My online DB server was experiencing high CPU usage.

Using the following query I was able to determine there are 2 queries (Select command) in suspended status (wait type - CXPACKET) for nearly 10 minutes and going on. I believe this is the cause of high CPU usage.

USE MASTER GO

select scheduler_id, runnable_tasks_count, pending_disk_io_count from
sys.dm_os_schedulers where scheduler_id < 255

select qs.percent_complete,qs.session_id, scheduler_id, blocking_session_id,
qs.status, command, wait_time, wait_type, last_wait_type, wait_resource, st.text,
host_name, program_name /* ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE
QS.statement_end_offset END - QS.statement_start_offset )/2 ) + 1) AS statement_text
--, qp. from sys.dm_exec_requests qs left join sys.dm_exec_sessions es on
(qs.session_id = es.session_id) CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
-- CROSS APPLY sys.dm_exec_query_plan(QS.sql_handle) as qp where qs.session_id >= 50
and qs.session_id != @@SPID

SELECT CASE WHEN Requests.sql_handle IS NULL THEN ' ' ELSE SubString (
Statements.text, (Requests.Statement_Start_Offset+2)/2, ( CASE WHEN
Requests.Statement_End_Offset = -1 THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2
ELSE Requests.Statement_End_Offset END - Requests.Statement_Start_Offset ) / 2 ) END
AS StatementText, QueryPlans.query_plan AS QueryPlan, Statements.Text AS Batch_Text,
Sessions.Session_ID, Sessions.Login_Name, Sessions.Host_Name, Sessions.Program_Name,
Sessions.Client_Interface_Name, Requests.Wait_Time, Requests.Cpu_Time,
Requests.Total_Elapsed_Time, Requests.Reads, Requests.Writes, Requests.Logical_Reads,
Requests.Row_Count, Requests.Granted_Query_Memory*8/1024 AS Granted_Query_Memory_MB,
LEN(Statements.text) AS Batch_Text_Length, Requests.Statement_Start_Offset/2 AS
Statement_Start_Offset, CASE WHEN Requests.Statement_End_Offset = -1 THEN
LEN(CONVERT(nvarchar(MAX),Statements.Text))*2 ELSE Requests.Statement_End_Offset END /
2 AS Statement_End_Position, ( CASE WHEN Requests.Statement_End_Offset = -1 THEN
LEN(CONVERT(nvarchar(MAX),Statements.text))*2 ELSE Requests.Statement_End_Offset END -
Requests.Statement_Start_Offset ) / 2 AS Statement_Text_Length FROM
sys.dm_exec_sessions Sessions INNER JOIN sys.dm_exec_requests Requests ON
Sessions.session_id
= Requests.Session_ID CROSS APPLY sys.dm_exec_sql_text(sql_handle) Statements CROSS
APPLY sys.dm_exec_query_plan(plan_handle) QueryPlans WHERE Sessions.Session_ID !=
@@SPID

select * from sys.dm_exec_requests where blocking_session_id != 0

Any suggestions on how I can kill these queries in suspended state?

Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL  :-)


Is MongoDB a good fit to track a stock portfolio? Is mongoDB or any document database in general a good fit to track a stock portfolio?

I have identified 5 main data domains:

Portfolios
Transactions
Holdings
Stocks information
Stock Prices

The holdings are just the combined view of all the transactions and are therefore derivable from the transaction history. Every time a transaction is updated, created, deleted, the holdings change.

It seems to be the most challenging part.

In a RDBMS, we could use triggers or views to work around this but I am not sure what we could do on MongoDB and if there is a good solution?

The "Stocks information" domain potentially fits well with a document database, such as MongoDB. The information available for different financial instruments might vary substantially, which means that a loose schema and the ability to store in-depth information in loosely defined sub-document structures could be useful. Also, you may want to extend the information scope gradually over time, in directions that may be hard to anticipate, which could be another argument in favor of a loose schema for the storage.

However, the other four data domains are typical examples of what a relational database is optimized for. While there may perhaps exist other, less widely used document databases that provide sufficient integrity constraint and join functionality to work well in this scenario, MongoDB certainly does not.

If the data really must be stored in MongoDB, the best would probably be to put all portfolio, transaction and holding information of each owner, along with cached price data for relevant instruments, in a single document. The application would then handle all necessary calculations when a stock owner makes a transaction, which would already be more complicated than achieving the same thing using built-in functionality for triggers, constraints and views in relational databases, but could, with some effort, be made to work reliably and with reasonable performance.

Once data for several owners must be aggregated, or when already loaded price information has been determined to contain errors, requiring all ownership documents to be recalculated, you will, however, have to spend even more time doing things that would be trivial with a relational database solution. Performance is also likely to be worse.

If there are strong other reasons to use a document database, and a solution with two concurrent database types is not acceptable, it might be worth the trouble to use MongoDB, but otherwise, a relational database will be the easiest and most robust solution.

Also, there are now relational databases with reasonably good document storage functionality. Microsoft SQL Server, for example, has JSON column support since version 2016, with query support for document content that could be sufficient for fairly complex document-based financial instrument information models.


Interpolate dates along a line I have a road_condition table in Oracle 12c:

create table road_condition (
    cond_id number(5,0),
    road_id number(5,0),
    cond_date date,
    condition number(5,0)
);

insert into road_condition (cond_id,road_id,cond_date,condition)
values (1,100,to_date('01-NOV-84','DD-MON-RR'),18);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (2,100,to_date('01-JAN-09','DD-MON-RR'),6);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (3,100,to_date('19-JUN-12','DD-MON-RR'),4);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (4,100,to_date('29-APR-15','DD-MON-RR'),4);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (5,200,to_date('29-APR-92','DD-MON-RR'),20);
insert into road_condition (cond_id,road_id,cond_date,condition)
values (6,200,to_date('05-APR-17','DD-MON-RR'),3);
commit;

Resulting table data:

COND_ID ROAD_ID   COND_DAT CONDITION
------- -------   -------- ---------
      1     100   84-11-01        18
      2     100   09-01-01         6
      3     100   12-06-19         4
      4     100   15-04-29         4
      5     200   92-04-29        20
      6     200   17-04-05         3

Here is a graph of road #100:

Condition in the table is categorized like this:

20 to 15 is in Good condition and does not require any treatment
14 to 11 requires an Overlay treatment
10 to 8 requires a Full Depth Removal (FDR) treatment
7 to 0 requires Reconstruction treatment

Condition is from 0 to 20, 20 being a perfect road.

When roads are inspected, the condition is stored as a whole number(integer), never as a decimal.

Question:

Rather than show the line in the graph as a single colour (blue), I would like to stripe the line as coloured categories.

I've mocked up road #100 in Excel:

Idea:

To stripe/categorize the data as described, I think I need to create dummy points (rows) at the beginning and end of each range along the line.

Perhaps the simplest way to do this would be to create points (rows) at all intervals of condition, instead of just at the beginning and end of each range.

The graphing software that I'm using creates a new line in the graph for each Y-column/series (similar to the way Excel does).

Therefore, I believe I need each category to be isolated in a separate column, so it can be drawn as a separate line (with a separate colour).

Additionally, there needs to be overlap between each category, so that the graphing software does not display gaps between lines.

How can I create the dummy rows (interpolate the dates)?

Update:

I have a related question here: Date as x-axis: How to multiply and divide dates?

Have fun.

with
  num_years as (select extract(year from max(cond_date)) - extract(year from min(cond_date)) +1 as years from road_condition where road_id = 100),
  generated_dates as (select add_months(trunc((select min(cond_date) from road_condition where road_id = 100), 'YYYY'), (rownum - 1) * 12) as cond_date from dual connect by level <= (select years from num_years)),
  generated_data as (select rc.cond_id, rc.road_id,nvl(rc.cond_date, gd.cond_date) as cond_date, rc.condition
    from generated_dates gd left join (select * from road_condition where road_id = 100) rc on (gd.cond_date = trunc(rc.cond_date, 'YYYY'))
  ),
  data1 as (select
      last_value(cond_id ignore nulls) over (order by cond_date) as cond_id,
      cond_id as original_cond_id,
      road_id, cond_date, condition,
      last_value(condition ignore nulls) over (order by cond_date) as s1,
      lead(condition ignore nulls) over (order by cond_date) as s2
    from generated_data
  ),
  data2 as (
    select
      cond_id, road_id, original_cond_id, cond_date, condition, s1, s2,
      count(*) over (partition by cond_id) + 1 as s,
      count(*) over (partition by cond_id order by cond_date rows between unbounded preceding and current row) as s0
   from data1
  ),
  data3 as (
    select original_cond_id as cond_id, road_id, cond_date,
     round(nvl(condition, (1-s0/s)*(s1-s2)+s2)) as condition
    from data2
  ),
  final_data as (
    select cond_id, road_id, cond_date,
      case when condition between 15 and 20 then condition end as cond_good,
      case when condition between 11 and 14 then condition end as cond_overlay,
      case when condition between 8 and 10 then condition end as cond_fdr,
     case when condition between 0 and 7 then condition end as cond_recon
    from data3
  ),
  data_for_graph as
  (
    select
      cond_id, road_id, cond_date, cond_good,
      case when cond_good is not null and lead(cond_overlay) over (order by cond_date) is not null then cond_good else cond_overlay end as cond_overlay,
      case when cond_overlay is not null and lead(cond_fdr) over (order by cond_date) is not null then cond_overlay else cond_fdr end as cond_fdr,
      case when cond_fdr is not null and lead(cond_recon) over (order by cond_date) is not null then cond_fdr else cond_recon end as cond_recon
    from final_data
  )
select * from data_for_graph order by cond_date;

Output:

   COND_ID    ROAD_ID COND_DATE   COND_GOOD COND_OVERLAY   COND_FDR COND_RECON
---------- ---------- ---------- ---------- ------------ ---------- ----------
         1        100 1984-11-01         18
                      1985-01-01         17
                      1986-01-01         17
                      1987-01-01         16
                      1988-01-01         16
                      1989-01-01         15
                      1990-01-01         15           15
                      1991-01-01                      14
                      1992-01-01                      14
                      1993-01-01                      13
                      1994-01-01                      13
                      1995-01-01                      12
                      1996-01-01                      12
                      1997-01-01                      12
                      1998-01-01                      11
                      1999-01-01                      11         11
                      2000-01-01                                 10
                      2001-01-01                                 10
                      2002-01-01                                  9
                      2003-01-01                                  9
                      2004-01-01                                  8
                      2005-01-01                                  8          8
                      2006-01-01                                             7
                      2007-01-01                                             7
                      2008-01-01                                             6
         2        100 2009-01-01                                             6
                      2010-01-01                                             5
                      2011-01-01                                             5
         3        100 2012-06-19                                             4
                      2013-01-01                                             4
                      2014-01-01                                             4
         4        100 2015-04-29                                             4

You can follow the steps by selecting from the CTEs in the last line in the order I listed them (so select * from num_years, then select * from generated_dates, then select * from generated_data, and so on).


Teradata 15 : Failure 5619 Role nesting exceeded single-level I am using Teradata 15.  In Active Directory, We have a policy to assign a universal group, which has people, to a local group, where permissions are assigned. So I create two Teradata roles to mimic this structure.

I granted one role to another role:

grant role_1 to role_2;

but I got the following error.

*** Failure 5619 Role nesting exceeded single-level.

I am wondering if I need to create special type of roles to identify a parent and a child relationship first?

Your help is greatly appreciated.

Teradata only allows one level of role nesting, there's no workaround.

Either role_2 or is already granted to another role or role_1 has been granted another role.

role_a -- top level
   |
role_b -- GRANT role_b TO role_a; this is ok, 1st nested level
   |
role_c -- GRANT role_c T1O role_b; this will fail, 2nd nested level


Monitoring blocking queries in SQL Server 2012 have to monitor blocking queries in SQL Server 2012. How do we figure out which queries are the locked ones? Do I use the Activity Monitor? I want actual SQL statements.

The Microsoft Tiger Team has posted a set of scripts that handle this for you. Check this link out

https://github.com/Microsoft/tigertoolbox/blob/master/Running-Blocked-Processes/view_Running_Blocked_processes.sql

This will give you a lot of information about the processes running on your system. You can modify this to send the data to a table if that helps you track issues and do some trending.

Check out the rest of the scripts in the toolbox they have published.

https://github.com/Microsoft/tigertoolbox


Estimated number of rows is inaccurate I have a table with a field that has a very large variance on a particular field that is often searched. The number of records associated with any value in the field highly differs.

My estimated number of rows are always off, before refreshing statistics it's highly overestimated after refreshing it's highly underestimated. What can I do to improve it?

I update the statistics with full scan. The code is usually called from within a procedure but now I tested it outside.

You could create additional statistics for the data. Include a WHERE clause so each new statistics object references only a subset of the table. By bucketing the rows appropriately (big, medium, small?) the filtered statistics will be less bad than the whole-table ones, leading to better plans.

The predicate must match that used in queries. Depending on your settings there may be more system activity for stats maintenance. Of course this assumes the table contains (or could change to contain) some combination of columns on which rows can be meaningfully aggregated.


Why does LIKE not work with CONCAT but works with STRING value Please look on the following scenario;

my_db=# select version();
                                                              version
------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Debian 9.6.9-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

CREATE TABLE binary_data ("data" BYTEA NOT NULL);
CREATE OR REPLACE FUNCTION random_string(lengh integer)
RETURNS varchar AS $$
  SELECT array_to_string(ARRAY(
          SELECT substr('abcdefghijklmnopqrstuv',trunc(random()*21+1)::int,1)
             FROM generate_series(1,$1)),'')
$$ LANGUAGE sql VOLATILE;
insert into binary_data ("data") select CONVERT_TO(random_string(64), 'utf8') FROM generate_series(1,1000000);

Now if we run following query it works really well;

select CONVERT_FROM(data, 'utf8') from binary_data where "data" like '%me%' limit 10;

Bu if we run following query;

select CONVERT_FROM(data, 'utf8') from binary_data where "data" like CONCAT('%', 'me', '%') limit 10;

It failed with following error

Kernel error: ERROR:  operator does not exist: bytea ~~ text

Can't understand what's the difference between '%me%' and CONCAT('%', 'me', '%').

First guess was the type must be different to check it I run:

SELECT pg_typeof('%me'), pg_typeof(CONCAT('%', 'me', '%'));

And yes my guess was right;

unknown text

So '%me%' is treated as unknown and CONCAT('%', 'me', '%') is treated as text. To use the same query with CONCAT('%', 'me', '%')::UNKNOWN result to another error.

Kernel error: ERROR:  failed to find conversion function from unknown to bytea

Any one have any idea what's going wrong with above queries?

Can't understand what's the difference between '%me%' and CONCAT('%', 'me', '%').

The first is an untyped string literal. The second is type text since the function concat() returns text. Not exactly the same thing for Operator Type Resolution! Quoting the manual there:

  Look for the best match.

  a. Discard candidate operators for which the input types do not match
  and cannot be converted (using an implicit conversion) to match.
  unknown literals are assumed to be convertible to anything for this
  purpose. If only one candidate remains, use it; else continue to the
  next step.

And there is no implicit conversion for text -> bytea in standard Postgres.
And there is no variant of LIKE for bytea / text, only for:

bytea / bytea
name / text
text / text

db<>fiddle here (testing in Postgres 11)

To solve, add an explicit cast to bytea (text can be cast to any type):

SELECT CONVERT_FROM(data, 'utf8')
FROM   binary_data
WHERE  "data" LIKE ('%' || 'me' || '%')::bytea  --!
LIMIT  10;


Transferring grants from one mysql DB to another I would like to migrate a mysql DB from one server (Server A) to another (Server B). I took a Mysqldump and imported into the B.

Now, I would also like to export all the grants from A to B, to do so I did the following:

Ran the following on server A and got all the grants:

mysql -u root -B -N -p -e "SELECT user, host FROM user" mysql

I specifically wanted the grants for user galaxy, readgalaxy and writegalaxy so I ran the following command on server A to get them:

mysql -u root -p -B -N -e"SHOW GRANTS FOR 'galaxy'@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'readgalaxy '@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'writegalaxy '@localhost";
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'writegalaxy '@localhost";

I got the grants and then ran those grants against the server B like this:

GRANT USAGE ON *.* TO 'readgalaxy'@'localhost' IDENTIFIED BY
  PASSWORD '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

I get the following output:

Query OK, 0 rows affected (0.00 sec)

Ran the following on server B:

mysql> SELECT user,host,password FROM mysql.user;

But now when I try to see the grant that I allocated to user readgalaxy, I get the following error:

ERROR 1141 (42000): There is no such grant defined for user 'readgalaxy' on host 'localhost'

What's happening here?

An easier way is to use pt-show-grants.

On source server:

$ pt-show-grants -uroot -p > grants.sql

Destination server:

$ mysql -uroot -p < grants.sql
$ mysql -uroot -p -e "FLUSH PRIVILEGES;"


How to see what is cached in memory in SQL server 2008? Is there a way how to find out what is cached in SQL Server 2008 R2? I have found the following nice article: http://blog.sqlauthority.com/2010/06/17/sql-server-data-pages-in-buffer-pool-data-stored-in-memory-cache . However, I would like to know how much data (e.g. in percentage and KB) are stored of each table and index. Is there some simple way how to obtain such data?

You can find whats stored in the buffer pool (data cache) using below query :

From here :

select
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Excellent reference : Inside the Storage Engine: What’s in the buffer pool? by Paul Randal.


Idempotent PostgreSQL schema dump that can be applied to an existing DB I'm looking for a way to replicate a PostgreSQL schema to a database which may already have some of it defined. I wonder if there's a simple way to produce DDL that is idempotent (i.e. that can be applied any number of times) from PostgreSQL.
Currently I obtain DDL from my source DB using pgdump -s, and manipulate it using the ad-hoc Python code below. I wonder if there's a cleaner solution.

import re

def MakeDdlIdempotent(sql):
    "Make PGSQL DDL idempotent by changing various 'create' statements to idempotent versions"
    ddlReplacements = {
        "CREATE SCHEMA":   "CREATE SCHEMA IF NOT EXISTS",
        "CREATE TABLE":    "CREATE TABLE IF NOT EXISTS",
        "CREATE INDEX":    "CREATE INDEX IF NOT EXISTS",
        "CREATE VIEW":     "CREATE OR REPLACE VIEW",
        "CREATE FUNCTION": "CREATE OR REPLACE FUNCTION"
        }

    for orig, replacement in ddlReplacements.items():
        sql = sql.replace(orig, replacement)

    addConstraintRe = re.compile(r"ALTER TABLE [^;]+ ADD CONSTRAINT [^;]+;", flags = re.DOTALL | re.MULTILINE)
    sql = addConstraintRe.sub(
        r"""DO $$
BEGIN
  BEGIN
    \g<0>
    EXCEPTION
        WHEN invalid_table_definition OR duplicate_object OR duplicate_table THEN
    END;
END $$;""",
    sql)

    return sql

I wonder if there's a simple way to produce DDL that is idempotent

There is not, and it doesn't make much sense for a schema with data in it. If one column goes away and another appears, how should such a tool know whether it's an ALTERed column that's had its type changed and been renamed, or a DROP + a ADD? It could be either. The tool would have no way to deduce what the expression used to move the data over, if any, was either.

What you should generally do is use a schema management tool that can create versioned "migration" files for you, like liquibase.


Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638 Any suggestions on how to deal with this error:

TITLE: Microsoft SQL Server 2017 Setup
------------------------------

The following error has occurred:

VS Shell installation has failed with exit code 1638.

For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=0x5B39C8B9%25401434%25403

------------------------------
BUTTONS:

OK
------------------------------

This is a new laptop running

SQL Server 2016 express
Visual Studio 2017
SSMS 2017

I tried uninstalling anything related to SQL Server or Visual Studio.

Log: [3500:3970][2017-11-03T16:25:20]e000: Error 0x80070666: Cannot install a product when a newer version is installed.

Detailed results:
  Feature:                       Full-Text and Semantic Extractions for Search
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, and then try the setup process again.
  Component name:                Microsoft Visual C++ 2015 Redistributable
  Component error code:          1638
  Component log file:            C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20171103_162358\VCRuntime140_x64_Cpu64_1.log
  Error description:             VS Shell installation has failed with exit code 1638.
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400x1638

  Feature:                       Database Engine Services
  Status:                        Failed
  Reason for failure:            An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step:                     Use the following information to resolve the error, and then try the setup process again.
  Component name:                Microsoft Visual C++ 2015 Redistributable
  Component error code:          1638
  Component log file:            C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20171103_162358\VCRuntime140_x64_Cpu64_1.log
  Error description:             VS Shell installation has failed with exit code 1638.
  Error help link:               https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=VCRuntime140_x64%40Install%400x1638

I experienced this problem as well with SQL Server 2017 Developer and it appears to be just bad planning on the part of the SQL Server installation package people.  The problem is that Visual Studio 2017 installs the Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and the SQL Server installation tries to install the Microsoft Visual C++ 2015 Redistributables, which it can't because the SQL Server installation doesn't allow you to install an older version with the newer version installed.

This Microsoft Support article presents their explanation of the problem, and their recommended workarounds.

Solution 1: Install SQL Server first before installing Visual Studio 2017.

Solution 2 (what I did):

Uninstall the Microsoft Visual C++ 2017 Redistributable (x86) and (x64),
install SQL Server,
then reinstall the Microsoft Visual C++ 2017 Redistributable (x86) and (x64).

They are a little hard to find, so here are the direct links:

X64: https://go.microsoft.com/fwlink/?LinkId=746572

x86: https://go.microsoft.com/fwlink/?LinkId=746571

Note: If you don't have Microsoft Visual C++ 2017 Redistributable installed at all, just install it from one of the above links, and retry installing SQL Server. It should work now.

You may have to repeat these steps when installed additional named instances of SQL Server 2017.


SQL Financial Year Query I'd like to be able to have this query running daily selecting only where central_enquiry.enquiry_time is in the current financial year. The financial year runs from 01/04/YYYY to 31/03/YYYY. Ideally this would be dynamic so that I wouldn't have to manually change the dates next April

SELECT
  central_enquiry.enquiry_number,
  central_enquiry.enquiry_name,
  central_enquiry.enquiry_time
FROM
  central_enquiry
WHERE
  central_enquiry.enquiry_time

with start_date (d) as ( select
  case when current_timestamp < CONVERT(datetime,
          cast(datepart(yyyy, current_timestamp) as varchar) +
          '-04-01T00:00:00.000', 126)
    then convert(datetime,
           cast(datepart(yyyy, current_timestamp) - 1 as varchar)
           + '-04-01T00:00:00.000', 126)
    else convert(datetime,
           cast(datepart(yyyy, current_timestamp) as varchar) +
          '-04-01T00:00:00.000', 126)
  end )
SELECT central_enquiry.enquiry_number,
       central_enquiry.enquiry_name,
       central_enquiry.enquiry_time
    FROM central_enquiry, start_date sd
    WHERE central_enquiry.enquiry_time >= sd.d
      AND central_enquiry.enquiry_time <  dateadd(yyyy,1,sd.d)

This should always select data for the current book year.


How to deal with very large transaction logs in SQL Server Here is my situation: I have being asked to deal with a SQL Server 2008 R2 database that has a transaction log that is several times bigger than the database itself. The database is in full recovery mode and the transaction log has never been backed up.

I intend to run the transaction log backup every 15 minutes but I am afraid that the first backup may take a very very long time (hundreds of GBs of data).

What is the right course of action here?

I'm a developer by training and not a DBA so I would appreciate a more detailed answer. Thanks in advance.

I would take the first transaction log backup by hand (not as a scheduled task) and during hours when a performance hit will be acceptable to your users.

Then take a second immediately so that the working area of the log file moves back to the start of the file.

Then shrink the log file.

Then start your scheduled transaction log backups.


Check current memory_limit of Replication Server version 15.7.1 Does anyone knows how to check the currently memory_limit of replication server? I need to increase the memory_limit of my replication server, but I couldn't check the current memory limit. Can anyone help?

  W. 2018/03/22 15:58:08. WARNING #7038 SQM(199:0 pds.pdb1) -
  de/generic/mem/mem.c(2763)
          WARNING: Memory usage is above 80 percent. Increase 'memory_limit' or reduce cache sizes to avoid repserver threads from
  sleeping due to lack of memory.

  W. 2018/03/22 15:58:53. WARNING #7039 DSI EXEC(199(1) pds.pdb1) -
  de/generic/mem/mem.c(2763)
          WARNING CANCEL: Memory usage is below 80 percent.

Have you tried using the 'admin config' command to display current configuration settings?

admin config, memory_limit


Date format on the output of cmdshell dir C:\ /TC I would like to ask you guys if anyone of you had this trouble before.

In SQL I read the create time of the file like

exec master.dbo.xp_cmdshell 'dir C:\ /TC'

which gives me;

07/14/2009  04:20    <DIR>          Windows

As you noticed the date format of this is mm/dd/yyyy although it's set to dd/mm/yyyy format in region settings.

Same script on different PC returns

14/07/2009  04:20    <DIR>          Windows

Is there any setting for this you guys aware ?
And is there any method to change the format of create time of the
command ?

You can get the date format of dd/mm/yy by using wmic command. This command allows you to get current date without getting affected by regional settings.

**@echo off

    SETLOCAL EnableDelayedExpansion

    for /f "skip=1 tokens=1-6 delims= " %%a in ('wmic path Win32_LocalTime Get Day^,Hour^,Minute^,Month^,Second^,Year /Format:table') do (
        IF NOT "%%~f"=="" (
            set /a FormattedDate=10000 * %%f + 100 * %%d + %%a
            set /a FormattedTime=%%b * 10000 + %%c * 100 + %%e
        )
    )

    for /f %%f in ("D:\Test*.zip") DO set filename=%%~nf
move "D:\Test*.zip" "D:\%FormattedDate%%FormattedTime%-%filename%.zip"**

You can save it as date.bat and run this batch file by executing the following in command prompt:

C:>date.bat


Query MySQL Database On-The-Go I apologize if this question is elementary; I am new to Database administration.

I have a database on my home computer (iMac) that I would like to be able to access from anywhere (on any OS).  Specifically, I need to be able to query my data from any other computer.

How can I do this?

There are plenty of web based admin tools - PHPMyAdmin for MySQL (installed it once - seems OK and is widely used).

The MySQL Workbench tool is top-notch for administering MySQL - if you can telnet/ssh into your box, you can use it - I would recommend this approach as your starting point.

As a general tool, I also recommend SQuirreL SQL - a JDBC based tool - some shops can have strange requirements re. 3rd party tools and the like - worth looking at also!


MongoDB Write Concern when Journaling is off I'm running mongo 3.4.7 (WiredTiger) replica set with two secondaries.

According to the docs, when journaling is enabled and write concern is {w: "majority", j: 0}, the ack is sent after the data is written to "In memory".

However the docs don't specify what happens when journaling is off on the server (--nojournal). What happens in that case? What is the write concern waiting for?

The answer to your question: Writing data change to disk.

When journaling is on, the first change is written to the journal, then it is written to data blocks.


startup mount issues with Oracle I am trying to simply shutdown and restart an Oracle db instance on a Windows Server 2008 machine (Oracle 11.2.0).  Logged in via sqlplus, I issue the command shutdown immediate;.  I get the

Database closed.
Database dismounted.
ORACLE instance shut down

reponses.  I exit out of sqlplus, and try to log back in (ORACLE_SID is set correctly) so I can issue the startup mount; command, but I get the

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

error.  All of the Windows services involving my database dantest3 are started.  Doing research, I found the oradim command, but while that starts my database and allowing me to login, that is the full startup, and I want to only be in "mount" mode.

I am logging in with the following command -

sqlplus sys/[passwordforacct]@dantest3 as sysdba

If I try the simple sqlplus / as sydba, I get a

ORA-12560: TNS:protocol adapter error

oradim command -

oradim -start -sid dantest3

Requested variables -

TNS_ADMIN = C:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
ORACLE_SID = dantest3
ORACLE_HOME_11gR2 = C:\Oracle\product\11.2.0\dbhome_1

Is the environment variable LOCAL set? this will cause sqlplus to connect to a TNS alias instead of directly to the local database instance.

Your other problem with starting it into mount, you are missing a parameter to oradim -STARTTYPE srvc.  All of the options can be seen by typing oradim /?

to start just the service so that you can manually start to mount use

oradim -start -sid dantest3 -starttype srvc

At this point the database will not be registered with the listener so you will still need to connect without using the listener.


Which command Deletes and Frees Space I'm reading through the 98-364 exam book and there's a question I can't quite answer:

  "What command would you use to remove all records from the Contact
  table that do not have a country of USA and at the same time free the
  space used by those records?"

If "that do not have a country of USA" wasn't in there, it would simply be "The Truncate Command" as it would wipe out all data in that table, and free the space.
To my knowledge the delete command doesn't free the space, and I can't find a command that both deletes and frees the space while target specific records.

Does the book have a typo here? Or is there a command anyone knows of?

Note: It's "What command...", not commands.

Edit: (Stuart) There's been a lot of useful answers here, and it seems to me that the book is somewhat ambiguous. Outside of the 'Which Command would you use", and in the event this comes up on the exam, what would the "expected" answer be?
I would assume they are looking for "The delete command with where clause". Simply because they only suggest three methods in the book up to this point (Delete/Truncate/Drop).

Microsoft exams (and preparation material) tends to be stupid in some cases. It works best with excluding answers. You have to keep records? So truncate is not an option for you (and creating a new table is typically not the right way to solve it).

Delete does free up the space. Of course not the harddisk space but (again typically) the space inside the clustered index. The background that the ghost cleanup process has to really tidy up is too complex for 98-364. This exam is about database fundamentals.