Consistency in non-persistent computed columns within transactions Is there a way to prevent a non-persistent computed column from updating within a transaction?

I have thrown together an example which shows that these columns can update when using non-deterministic functions (in this instance getutcdate()). Does this not break the consistency requirements of a transaction? I have tried using transaction levels to prevent this (serializable and snapshot) which has not helped.

Query:

set nocount on;

create table dbo.TransactionTest (
    [Timestamp] datetime,
    IsUpdated as case when datediff(second, [Timestamp], getutcdate()) >= 1 then 1 else 0 end
);

insert into dbo.TransactionTest ([Timestamp]) values (getutcdate());

begin tran

select  *
from    dbo.TransactionTest;

waitfor delay '00:00:01';

select  *
from    dbo.TransactionTest;

rollback

drop table dbo.TransactionTest;

Results:

Timestamp               IsUpdated
----------------------- -----------
2017-03-19 00:44:28.130 0

Timestamp               IsUpdated
----------------------- -----------
2017-03-19 00:44:28.130 1

There's a few different questions here.

Q: Can I force GETUTCDATE() to stay the same during a transaction?

No. If you have a long-running transaction, the time is going to change while you work. This has nothing to do with your isolation level, and more to do with the fact that life doesn't have a Pause button. (Yet. I've tried pointing Dr. Horrible's Freeze Ray at my computer, but that just made my solid state drives become even more solid.)

Q: Can I force non-deterministic functions to return the same result every time?

By definition, no. If you could, they would be deterministic.

Q: If I need dates/times to stay static during a transaction, what do I do?

If you only need a single value, create a variable, and set its value at the beginning of your transaction. In sp_BlitzFirst, for example, I set a @StartSampleTime variable, and then keep using that throughout the code.

If you need multiple rows, select the data into a permanent object like user table or a temp table, and repeatedly refer to them during the life of the transaction.


Cube Processing vs Database Processing We have a SSAS database (SQL Server 2012 Enterprise) with 4 cubes in it. 1 of the cubes is significantly bigger than the other 3. We want to process the 3 smaller cubes at regular intervals during the day and update the large cube every night.

A really basic question, if I just process a selected cube, does that still process all changes in measure groups and dimensions that are included in that cube?

To put it another way, will the data in the cube that I process be exactly the same if I process just 1 cube or if I process the whole database?

There is a huge difference.

If you process a cube then it processes the measure groups and partitions inside it.

If you process a database then it also processes the dimensions


Change Name of Availability Group Replica The name we want it to be is actively being used by a production reporting server, we are building out this VM ahead of time that will replace it and will eventually swap the names.
Was going to go ahead and get the data syncing to avoid down time and then swap the names this weekend. Hoping I don't have to swap the names, add the new VM to the AG and then start syncing

Not seeing a lot of documentation on this.

I want to change the name of an Availability Group Replica because [paraphrased] the name we want it to be is actively being used by a production reporting server [sic]

Personally the name of the nodes shouldn't really matter. Yes, I get naming conventions and whatnot, but at some point (just like which node in a cluster owns a specific role) there is diminishing returns. This is one of those times where you're going to put way more effort into changing it than I personally believe you'll get out of it.

  Not seeing a lot of documentation on this.

I'll add it to my documentation list, and because of this I'll go ahead and answer it here, hoping others may find it.

The Overall Logical Steps

Please note that this does not cover if the node is acting as a forwarder in a distributed availability group.

Remove the node from the AG if it's part of it.
Remove the node from any Read Only Routing (ROR) configurations.
Disable Always On Availability Groups from the SQL Server Configuration Manager/Powershell if it's enabled.
Remove any clustered applications from the Windows Server Failover Cluster Node (WSFC). This includes FCI's acting as part of an AG. You don't have to remove local installs of SQL Server, but you will have to follow the renaming items by dropping and adding the server name.
Evict the node from the WSFC.
Rename the computer and double check the AD computer object is successfully renamed and replicated to other DCs before continuing.
Join the node to the WSFC under the new name.
Install any clustered services or applications removed in step 4. Also enable Availability Groups from the SQL Server Configuration Manager/Powershell.
Add the node to the AG.
Update any ROR configurations.


What does "Drop Connections" and "Update Statistics" in the "Detach Database" dialog do? As a testing phase I have installed the evaluation version of SQL Server 2014 Enterprise Edition.

When I detach a database through SQL Server Management Studio, I see two options:

Drop Connections
Update Statistics

Both options will detach the database from the SQL Server Instance.  What is the difference between the two options?

Drop Connections will force the database into Single User mode before detaching the database, thereby forcing all users to disconnect.  If you don't check-mark that option, and users are connected to the database, the database will not be detached.

Update Statistics will refresh any out of date statistics before detaching the database.  If you leave this option un-checked, statistics objects will remain as they were at the time you detached the database.

Microsoft has an excellent article with more details on MSDN.


How to use SSIS , server side, without SSDT? Is it required to use SSDT or Visual Studio to create packages for server side SSIS?

I thought there was another way, but the docs I find keep referring to the above tools.

(When I use the SQL Server "Import & Export Data" tool, it offers me the option to save the profile to a server, but I don't know where it goes?)

Could you be thinking of BIML (Business Intelligence Markup Language)? This can be used to generate SSIS packages.

Apparently there's a commercial version, and an open-source version. The open source version appears to integrate into Visual Studio (per this article at MSSQL Tips from 2013), so I can't say whether this is really a solution that uses other tools.

Note, of course, that an SSIS package is stored as an XML file with the extension .dtsx, so (in theory, at least) it might be possible to create one with a text editor. However, having dug through these to some extent, there's a lot of IDs and interlinkages amongst things, and it's not an experiment I personally would want to try.

The packages created by the import/export tool should be able to be saved either as a file, or to a server. Depending on the configuration of the server, and the options selected, they may wind up either stored in that server's msdb databases, or in a particular folder on the server (usually under the SQL root directory for the instance, in the subfolder \DTS\Packages\).


How to store multiple values in a column in database? I'm trying to make three tables in phpMyAdmin. The main columns here are named Campaign, Program and Asset. Under Campaign, there are different kinds of Programs. And under each Program, there are Assets with corresponding columns. Here is the rough sketch of what I'm trying to build right now.

Campaign 1

Campaign Description
Program 1

Program Description
Asset 1

Asset Type
Asset Description
Region
Supporting Asset
Status
Release Date
External Link
Source File Link
Contact Person
Target Audience
Color

Asset 2

Asset Type
Asset Description
Region
Supporting Asset
Status
Release Date
External Link
Source File Link
Contact Person
Target Audience
Color

How can i construct my tables like these interconnecting with each other? How do i do querying? Thanks in advance! :)

While there are different approaches to manage your problem, the simplest way to do it is following the most conventional relational and normalised way, which is separating Campaigns, Programs and Assets as different tables. You can specify relationships between tables like "has" or "belongs to" with foreign keys that references other table rows.

You can read an introduction about the relational model here: http://en.wikipedia.org/wiki/Relational_model

When querying complete information, you have to use the JOIN clause ( http://en.wikipedia.org/wiki/Join_(SQL) ) to merge results from different tables, for example:

SELECT * FROM campaign
JOIN program ON campaign.id = program.campaign_id
JOIN asset ON program.id = asset.program_id


Optimizing a stored procedure I have done some optimizations on the below mysql stored procedure, however it still runs pretty slow:

CREATE DEFINER=`xxx`@`%` PROCEDURE `testing`(IN _MONTH INT, IN _YEAR INT, IN _PRODUCTS TEXT)
BEGIN

set @_START = date(_YEAR * 10000 + _MONTH * 100 + 1);
set @_END = date_add(date(_YEAR * 10000 + _MONTH * 100 + 1), interval 1 month);

DROP TABLE IF EXISTS ReportThreeTotal;
DROP TABLE IF EXISTS ReportThreeAbandondCalls;
DROP TABLE IF EXISTS ReportThreeSpillGain;
DROP TABLE IF EXISTS ReportThreeStamp;
DROP TABLE IF EXISTS ReportThreeResults;

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeTotal AS (
    SELECT count(*) as 'count', DAYOFWEEK(`end`) as 'DOW'
    FROM callstats.cdrdata_archive
    where end >= @_START and end < @_END
    AND calling IN (
        SELECT Number FROM callstats.products WHERE products.id IN (_PRODUCTS) )
    GROUP BY DAYOFWEEK(`end`)
);

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeAbandondCalls AS (
    SELECT count(*) as 'count', DAYOFWEEK(`end`) as 'DOW'
    FROM callstats.cdrdata_archive
    WHERE calling IN (
        SELECT Number
        FROM callstats.products
        WHERE products.id IN (_PRODUCTS) )
    AND end >= @_START and end < @_END
    AND CAST(`duration` AS UNSIGNED) < 30
    GROUP BY DAYOFWEEK(`end`)
);

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeSpillGain AS (
SELECT (case when (SUM(count) is null) then 0 else SUM(count) end) as 'SPILLGAIN', DAYOFWEEK(`end`) as 'DOW'
FROM (SELECT count(*) as 'count', called, end
    FROM (
        SELECT called, calling, count(*) as 'count', end
        FROM cdrdata_archive
        JOIN products ON Number = calling
        WHERE end >= @_START AND end < @_END
        AND products.id IN (_PRODUCTS)
        AND called != 'Anonymous'
        GROUP BY called, calling, DAYOFWEEK(`end`)
        ORDER BY `end` ASC )
    AS tbl1
     GROUP BY called having count(*) > 1)
 AS tbl2
 GROUP BY DAYOFWEEK(`end`));

CREATE TEMPORARY TABLE ReportThreeStamp(stamp text, DOW INT);
    SET @Counter = 1;
    while @Counter <= 7 do
        INSERT INTO ReportThreeStamp VALUES(TIMESTAMP_FROM_DATE(_YEAR * 10000 + _MONTH * 100 + @Counter), @Counter);
        SET @Counter=@Counter+1;
    end while;

CREATE TEMPORARY TABLE IF NOT EXISTS ReportThreeResults AS (
SELECT CONCAT('[',S.`stamp`,',', (
    case when (A.`count` = 0)
         then 0
         else (A.`count` / T.`count`) * 100 end),']') AS 'JSON_PERCENT',
    CONCAT('[',S.`stamp`,',',T.`count`,']') AS 'JSON_TOTAL',
    A.`DOW` as 'DOTW',
 A.`COUNT` as 'ATOTAL', T.`COUNT` as 'TOTAL',
SG.SPILLGAIN as 'GAINPER', SG.SPILLGAIN as 'SPILLPER'
FROM ReportThreeAbandondCalls A LEFT JOIN ReportThreeTotal T ON A.`DOW` = T.`DOW` LEFT JOIN ReportThreeSpillGain SG ON SG.`DOW` = A.`DOW`
LEFT JOIN ReportThreeStamp S ON A.`DOW` = S.`DOW`);

SELECT * FROM ReportThreeResults;

DROP TABLE IF EXISTS ReportThreeTotal;
DROP TABLE IF EXISTS ReportThreeAbandondCalls;
DROP TABLE IF EXISTS ReportThreeSpillGain;
DROP TABLE IF EXISTS ReportThreeStamp;
DROP TABLE IF EXISTS ReportThreeResults;

END

There is an index set for the end field which should increase the performance.
Any ideas how to optimize this stored procedure?

"IN ( SELECT ... )" optimizes very poorly; switch to a JOIN.
CREATE/DROP TABLE is slow, especially on Windows.
ReportThreeStamp seems static; why not build it as a non-TEMPORARY TABLE, and do so only once.
(case when (SUM(count) is null) then 0 else SUM(count) end) --> IFNULL(SUM(count), 0))
Your temp tables seem to have no indexes; this leads to slow table scans.  Suggest this syntax to create an index while creating and populating:

CREATE TABLE x ( PRIMARY KEY (y) ) SELECT ...
How many rows in the tables?  If millions, we should talk about "summary tables" and how they could probably speed up the processing 10-fold.


ORA-01031: insufficient privileges while connecting with sys as sysdba I installed Oracle database 10g as an administrator on a windows 10 machine, it worked fine, but when i try to connect sys as sysdba it gives me the error:

ORA-01031: insufficient privileges

I checked sqlnet.ora file and if SQLNET.AUTHENTICATION_SERVICES is NTS and it is:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

I added the user to the ORA_DBA group and i checked with:

c:\Oracle>echo %username%

and

c:\Oracle>NET LOCALGROUP ORA_DBA

and the user is in the group.

I don't know what else i should do, anything will be helpful.

Are you connecting as

 sys as sysdba

or

 / as sysdba

?

If you are using the first option, is it prompting you for a password, and are you entering the password for the user?  Did you create a local Windows user account as the user, not the user id used to install the software?  Oracle recommends a user with administrators group install the software, and a local Windows user be defined for the sys and system accounts.


How do I correctly group output from multiple tables? I have more than 2 tables where entries are associated with dates.  Each table can have multiple entries per date.  I want to produce output that lists a date, then lists the entries of each table for that date.  e.g.

date      |   tbl1_col
1/1/2015  |     dat1_1
1/2/2015  |     dat1_2
1/2/2015  |     dat1_3
1/3/2015  |     dat1_4

date      |   tbl2_col
1/1/2015  |     dat2_1
1/2/2015  |     dat2_2
1/2/2015  |     dat2_3
1/3/2015  |     dat2_4

date      |   tbl3_col
1/1/2015  |     dat3_1
1/2/2015  |     dat3_2
1/2/2015  |     dat3_3
1/3/2015  |     dat3_4
1/3/2015  |     dat3_5

I want output like this:

date      |  tbl1_col  |  tbl2_col  | tbl3_col |
1/1/2015  |  dat1_1    |  dat2_1    | dat3_1   |
1/2/2015  |  dat1_2    |  dat2_2    | dat3_2   |
          |  dat1_3    |  dat2_3    | dat3_3   |
1/3/2015  |  dat1_4    |  dat2_4    | dat3_4   |
          |            |            | dat3_5   |

The problem is I'm missing the association between individual table records (I don't know if dat1_1 really corresponds to dat2_1.  So the only useful way for me to look it is by date.  If I use a join I get output that creates every possibility:

1/2/2015  |  dat1_2    |  dat2_2    | dat3_2   |
1/2/2015  |  dat1_2    |  dat2_2    | dat3_3   |
1/2/2015  |  dat1_2    |  dat2_3    | dat3_2   |
1/2/2015  |  dat1_3    |  dat2_3    | dat3_3   |
1/2/2015  |  dat1_3    |  dat2_3    | dat3_2   |
1/2/2015  |  dat1_3    |  dat2_2    | dat3_3   |

So from what I can infer, your desired output is a column of all the dates from all 3 tables, with the 3 tables joined onto it.

So something like

Select dates.date, tbl1_col, tbl2.col, tbl3.col from
    (select distinct Date from table1
    union
    select distinct date from table2
    union
    select distinct date from table3
    )dates --Now we have a list of all the dates from all the tables.
left join table1 on dates.date=table1.date
left join table2 on dates.date=table2.date
left join table3 on dates.date=table3.date
--we add the tables back on, keyed against the dates.

could be what you are looking for. Just an idea, reading into how you have phrased it.


Disabling Checking of Schema On Function/Stored Procedure Creation I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database.  The process I put in place drops and recreates my stored procedures and  functions, as well as run scripts to change the tables/columns/data.  Unfortunately, one of the scripts requires one of the functions to be put in place first.  But I can't run all stored proc/function changes first because it relies on columns being added from the tables/columns/data change scripts first.

I was wondering if it was possible to run stored procedures and functions without SQL Server validating the columns used in the definition of the function/SP?  I tried looking but couldn't find a condition or command to enable this.

You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). You cannot create stored procedures that reference columns that don't exist yet in objects that do already exist. This is the double-edged sword of deferred name resolution - SQL Server gives you the benefit of the doubt in some cases, but not all. See Erland's ideas for SET STRICT_CHECKS ON; to get some ideas of the places this works and the places it breaks:

http://www.sommarskog.se/strict_checks.html

(And how he'd like the polar opposite of what you're after - you want to allow anything to compile regardless of existence, and he wants every single column or table to be checked.)

There is no setting like SET DEFERRED_NAME_RESOLUTION OFF; though it has been asked for:

http://connect.microsoft.com/sql/127152

And there is no setting like IGNORE ALL_RESOLUTION;.

You could get around this in a few ways, including:

(a) use dynamic SQL in the affected stored procedure(s).

(b) build a stub for CREATE PROCEDURE with nothing in it, then run the rest of your script, then run an ALTER PROCEDURE which has the real body (in essence, deploy the procedure in two phases).

(c) make your deployment tool smarter about the order of operations. If table changes require the presence of a function, script those changes last. Schema comparison tools like RedGate's SQL Compare are pretty good about generating scripts for you in the proper dependency order. You don't mention what tool you're using, but if it's not doing this...

(d) Martin Smith has an interesting workaround here, but I haven't played with it.


Output of UNION query UNION
Suppose 2 SELECT queries are joined by UNION. Can I get output if the 2nd SELECT query has 0 rows and 1st SELECT query returned some rows?

Yes, you will have output even if only 1 of the SELECT query has results.

Though by default, you cannot determine if the result was from the 1st or 2nd SELECT query, you can include an additional column in each query to determine where in those SELECT query the result came from. See this example


Does this error message indicate a SQL injection vulnerability? I just took over management for a webservice and today I got a user report about this error. Now I'm no sql injection expert but does this error make this attack possible?

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.22-community-nt] You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the right
 syntax to use near '') ORDER BY TCOL_date desc' at line 1

I'm asking because for some reason I'm not given access to the code and the previous guy insists that the site is safe so I need to come with some proof or facts that it's actually vulnerable.

It may, yes, though it is difficult to tell without knowing what the code is doing around that point. Anywhere where you take user input and place it into ad-doc SQL an easy injection route is presented to a malicious user simply by adding ' characters into the relevant input.

For instance if your code does something like:

results = dbObject.RunSQL("SELECT * FROM some_object WHERE person_name='" & request("name") & "'")

a malicious user can make a malformed HTTP request with the name value containing '; DELETE * FROM users_table; SELECT ' the code you present to the database becomes:

SELECT * FROM some_object WHERE person_name=''; DELETE * FROM users_table; SELECT ''

Perfectly we formed SQL so it will run, and if you have a table called users_table you have just lost its contents. There are much more clever attacks than this simple one possible by the same route. This isn't limited to SELECT statements - any ad-hoc SQL is potentially vulnerable to this.

Without seeing some of the relevant code, what seems to be happening is some ad-hoc SQL is being put together with a string that contains apostrophe characters resulting in incorrect syntax. So you likely have a bug that creates an injection attack route, though in this instance the bug is simply causing an error doe to some specific input value. Find it and fix it while the force is with you! Instigate a full code review too, there may be many more examples of the problem.

To fix this sort of error you need to look at two things:

Proper input validation. Refuse any request where a value seems to be in the wrong format. Expecting a UUID in a given variable? Then error and do nothing if you see anything that isn't a valid representation of that. Expecting a positive integer? Make sure the value contains nothing but numeric characters and throw out the request if not. Do this as early as possible, definitely before trying to touch your database with anything using values received from the client (in fact you should validate data from the database too before passing it back - bad data may have got into the database earlier either maliciously or due to a bug).
Use parametrised queries where ever possible - avoid ad-hoc SQL except where absolutely necessary. Your data layer (ADO, OLEDB, ...) should handle this cleanly for you meaning you do not need to worry about it misinterpreting characters that have special meaning, for instance apostrophes sometimes found in names like O'hare. For bulk inserts/updates where single procedure calls per row are too inefficient consider using an updatable recordset rather than ad-hoc INSERTstatements if such a concept is supported.
If you absolutely must put together ad-hoc SQL strings, be very very careful to escape special characters.

One thing I would recommend, though it can be costly, is having an instance of your application (not one containing real client data of course) properly penetration tested by a professional pen-testing company. The cost and hassle of this is nothing compared to losing all your data and/or customers if a hole they could have found is instead found and abused by a black-hat.


Implementation of DENSE_RANK I am trying to implement the DENSE_RANK function of SQL Server using only basic SQL functions. I managed to implement the RANK function but I don't know how extend it to DENSE_RANK. More specifically, I don't know how to arbitrarily distinsguish between rows of the same rank.

Here is my implementation of RANK.

SELECT A.partition_id,
    A.order_column_id,
    COUNT(B.order_column_id) AS rank
FROM MyTable AS A
INNER JOIN MyTable AS B
    ON A.partition_id = B.partition_b AND A.order_column_id <= B.order_column_id
GROUP BY A.partition_id, A.order_column_id

How can I write an implementation of DENSE_RANK in a similar way?

Just use COUNT(DISTINCT instead of COUNT( - because that’s essentially the difference between DENSE_RANK and RANK.


Delete rule on view does not execute every statement When I have more than one statements in a rule on a view, the second one is not executed in case the first one deletes the original entry. Even when the second statement does not have anything to do with the original entry at all.

Here is a simple example. Tables are reach and rp (reachpoint) and in reach we have an id that can be referenced to rp.

CREATE TABLE test_delete_rule.reach
(
  id integer NOT NULL,
  rp integer
);

CREATE TABLE test_delete_rule.rp
(
  id integer NOT NULL
);

INSERT INTO test_delete_rule.reach (id, rp) VALUES (1,10);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (2,20);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (3,30);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (4,40);
INSERT INTO test_delete_rule.rp (id) VALUES (10);
INSERT INTO test_delete_rule.rp (id) VALUES (20);
INSERT INTO test_delete_rule.rp (id) VALUES (30);
INSERT INTO test_delete_rule.rp (id) VALUES (40);

And there is a view on reach called vw_reach:

CREATE VIEW test_delete_rule.vw_reach AS
    select * from test_delete_rule.reach

And on this view is a rule, deleting the reach and the referenced rp:

CREATE OR REPLACE RULE reach_on_delete AS
  ON DELETE TO test_delete_rule.vw_reach
  DO INSTEAD
  (
    DELETE FROM test_delete_rule.reach
    WHERE reach.id = old.id;

    DELETE FROM test_delete_rule.rp
    WHERE rp.id = old.rp;
  );

When we delete now an entry from the vw_reach

delete from test_delete_rule.vw_reach where id = 2

The entry with id 2 from reach is deleted, but not the entry with 20 from rp.

When I have the same rule on the table with DO ALSO ir works:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.reach
    DO ALSO
    (
       DELETE FROM test_delete_rule.rp
       WHERE rp.id = old.rp;
    );

So according the response here https://stackoverflow.com/questions/5534927/how-to-write-a-delete-rule-on-a-view the second statement is not executed because old.rp does not exist anymore. This makes sense but I cannot confirm.
Because it cannot be because of the old, because it does not execute the second statement neither if it has nothing to do with the original entry.

E.g.:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.vw_reach
    DO INSTEAD
    (
       DELETE FROM test_delete_rule.reach
       WHERE reach.id = old.id;

       DELETE FROM test_delete_rule.rp
       WHERE rp.id = 20;
    );

Although the the second command has nothing to do with the values of the original entry.

But what in fact it works, when the order changes and the statement concerning the original is at the end. The following works:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.vw_reach
    DO INSTEAD
    (
        DELETE FROM test_delete_rule.rp
        WHERE rp.id = old.rp;

        DELETE FROM test_delete_rule.reach
        WHERE reach.id = old.id;
    );

So the conclusion is, that the rule is not continued after the original entry is deleted. It kinds of escapes. It does not do that on the table with DO ALSO.

Does anyone have a explanation for that behavior? It's quite confusing.

Quote from the manual

  Caution

  In many cases, tasks that could be performed by rules on INSERT/UPDATE/DELETE are better done with triggers. Triggers are notationally a bit more complicated, but their semantics are much simpler to understand.

(Emphasis mine)

I think this is one of the many cases where this is better done with a trigger:

create or replace function delete_reach()
  returns trigger
as
$$
begin
  DELETE FROM reach
  WHERE reach.id = old.id;

  DELETE FROM rp
  WHERE rp.id = old.rp;
  return old;
end;
$$
language plpgsql;

create trigger delete_reach_trigger
   instead of delete
   on vw_reach
   for each row
   execute procedure delete_reach();

Online example: https://rextester.com/KMEDTV61929


Oracle TKPROF Insert data doesn't match text results I'm trying to make sense of the output from a SQL Trace from an Oracle 12.2 database on Windows. I've used TKPROF to interpret the the resulting trace files. This utility generates a text file output and a SQL file output. But the data don't match up between the two files.

The data was generated using syntax like tkprof Trace.trc trace.trc.txt insert=trace.trc.sql

For a specific query, the TKPROF output via text file is:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     13      0.04       0.03          0          0          1           0
Fetch       13      0.00       0.07          6         26          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       27      0.04       0.11          6         26          1           0

But TKPROF's SQL output generates data for that query that doesn't have the same numbers for the Elapsed column.

Here's the Insert statement:

INSERT INTO tkprof_table (
    date_of_insert, cursor_num, depth, user_id,
    parse_cnt,   parse_cpu, parse_elap, parse_disk, parse_query, parse_current, parse_miss,
    exe_count,   exe_cpu,   exe_elap,   exe_disk,   exe_query,   exe_current,   exe_miss,  exe_rows,
    fetch_count, fetch_cpu, fetch_elap, fetch_disk, fetch_query, fetch_current, fetch_rows,
    ticks, sql_statement
    )
VALUES
(
  SYSDATE, 4, 0, 9,
  1, 0, 86, 0, 0, 0, 0,
  13,46875, 31424, 0, 0, 1, 2, 0,
  13, 0, 79075, 6, 26, 0, 0, 152918
, 'SELECT [...]')

And then converting those SQL columns to match the text file's table layout that becomes:

call     count       cpu    elapsed    disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0       86          0          0          0           0
Execute     13      46875   31424       0          0          1           0
Fetch       13      0       79075       6         26          0           0

Oracle's documentation says the TKPROF Elapsed values are

  "total elapsed time in seconds for all parse, execute, or fetch calls
  for the statement."

And I believe that's accurate for the text file output. But the SQL data must be some other measure. I can't find any documentation that defines a unit of measure for the mismatched data.

There's a "ticks" column, that appears to correspond to CPU ticks. But I don't know if that's what the mismatched values are too. If so, how do I convert that data to seconds?

Or it looks like these values may be microseconds. Is that correct? Is there anywhere that definitively defines that unit of measure?

Those values are just fine. They are just truncated in the tkprof output and displayed as seconds instead of microseconds.

Time for SQL executions is typically measured in microseconds.

See V$SQL for example:

  ELAPSED_TIME

  NUMBER

  Elapsed time (in microseconds) used by this cursor for parsing,
  executing, and fetching. If the cursor uses parallel execution, then
  ELAPSED_TIME is the cumulative time for the query coordinator, plus
  all parallel query slave processes.

Tkprof also displays time in microseconds at the row source operations section (and displays the unit as us).

I did not find any explicit reference for describing all the columns of TKPROF_TABLE, stating the time unit to be microsecond, but its quite logical.


Can I show a relation - "Either in this table or in that " in ER diagram? I am making a database for handling leave applications in an academic institute. Given that there is hierarchy existent in such institutes, and I need to keep record of the people that were at these posts, say 'Dean', I have implemented a table 'archived_cross_cutting_faculty' in addition to 'current_cross_cutting_faculty', where cross_cutting_faculty means the guy is dean or something else in addition to being a faculty.
To have a record of retired faculty, i have table retired_faculty in addition to faculty table.

Now, the faculty listed in archived_cross_cutting maybe a retired faculty, hence he maybe in the retired_faculty table. Or he maybe present in the faculty table, i.e. he is currently serving as a faculty but say, he is not a dean now.

Can this relation be shown in ER Diagram? That a person in archived_cross_cutting_faculty is either in the faculty table or in the retired_faculty table? How?

In the ER model,  this concept is known as Generalization/Specialization.  A diagram illustrating this is shown in the artcle linked to.

https://www.geeksforgeeks.org/generalization-specialization-and-aggregation-in-er-model/

A word of caution is in order, however.  Gen/spec is applied to Entities, not tables. The difference between entities and tables is in the level of abstraction.  If you want to think clearly about the subject matter,  and not just the database, it behooves you to keep the levels distinct in your mind.
An entity is an object in the subject matter whose descriptive data may be stored in a table.


Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where? I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common expressions up out of the CTE, etc. It's often claimed to be a behaviour required by the SQL standards.

CTEs are definitely an optimisation fence in PostgreSQL ... but is this required by the standard, or actually just implementation detail?

For example, these mailing list posts claim or suggest that it's standard:

http://www.digipedia.pl/usenet/thread/11566/101385/

After mentioning it in a comment I was asked where it's specified - and after a look at the only draft of SQL:2008 I have access to I'm not having much luck finding it.

I haven't yet intensively studied the standard, so I'm hoping for a suggestion from somebody who has: Is the optimisation fencing of CTEs in PostgreSQL actually required by the standard? And if so, where's it specified? Or are the statements on the Pg mailing list in error?

See also the thread CTE optimization fence on the todo list?.

I think it's an implementation detail.

  A conforming implementation is not required to perform the exact
  sequence of actions defined in the General Rules, provided its effect
  on SQL-data and schemas, on host parameters and host variable, and on
  SQL parameters and SQL variables is identical to the effect of that
  sequence. The term effectively is used to emphasize actions whose
  effect might be achieved in other ways by an implementation.1

I think an implementer could evaluate a common table expression 20 times, even in 20 different ways, and still have a conforming implementation. The only relevant issue is whether "its effect . . . is identical to the effect" of the sequence of actions defined in the General rules.

[1]. Section 6.3.3.3, "Rule evaluation order", in a draft of the SQL 2008 standard, having the local filename 5CD2-01-Framework-2006-01.pdf, p. 41 I have no idea where I got it.  Google might know.


Is a clustered index on column A the same as creating a table ordered by A? Reading the official PostgreSQL documentation for version 9.0 I read an interesting escamotage that performs better than CLUSTER for big tables:

  The CLUSTER command reorders the original table by scanning it using the index you specify. This can be slow on large tables because the rows are fetched from the table in index order, and if the table is disordered, the entries are on random pages, so there is one disk page retrieved for every row moved. (PostgreSQL has a cache, but the majority of a big table will not fit in the cache.) The other way to cluster a table is to use:

  CREATE TABLE newtable AS
    SELECT * FROM table ORDER BY columnlist;

  which uses the PostgreSQL sorting code to produce the desired order; this is usually much faster than an index scan for disordered data. Then you drop the old table, use ALTER TABLE ... RENAME to rename newtable to the old name, and recreate the table's indexes. The big disadvantage of this approach is that it does not preserve OIDs, constraints, foreign key relationships, granted privileges, and other ancillary properties of the table — all such items must be manually recreated. Another disadvantage is that this way requires a sort temporary file about the same size as the table itself, so peak disk usage is about three times the table size instead of twice the table size.

The problem is that this suggestion doesn't appear in > 9.0 versions of the official documentation.

My question is if this escamotage is still valid for 9.1, 9.2, 9.3 and 9.4 because I'm stuck with a CLUSTER operation over two big tables (one has ~750M rows and the other one has ~1650M rows) and average disk write/read speed is 3MB/s due to the CLUSTER algorithm explained in the official doc. It's a slow process over big tables, so I'd like to avoid it doing the "create ordered table over index-associated-column" trick. This will save me days of DB processing.

Like @dezso commented, creating a new table and dropping the old used to be faster in old versions, but not any more with the new implementation in pg 9.1.

The most common problem with CLUSTER is that it requires an exclusive lock on the table, which does not go well with concurrent access to it.

The solution to this problem is pg_repack, which does not lock the table exclusively.

Generally, make sure that your server configuration is fit for the task. High settings (a lot of RAM) for maintenance_work_mem would help both CLUSTER and CREATE INDEX on big tables. Standard setting is way too small for you. Follow the links for details.

You might set it very high temporarily for a transaction with SET LOCAL and leave it at a reasonable setting otherwise:

BEGIN;
SET LOCAL maintenance_work_mem = ????MB; -- find the sweet spot
CLUSTER tbl;
COMMIT;

If possible, set it high enough to fit the whole operation in RAM.

More:

PostgreSQL 9.0 how to reclaim disk space after delete without rebuilding table?
Configuring PostgreSQL for read performance


SQL to Find Partial Duplicates of a Field I have a table with a field for a member number. The table can contain records with duplicate values for this field and does.

I have a SQL query that will obtain any duplicate values for this field, however, I would now like to find records where there is another record with the same member number but only the first 8 digits out of the 9 that there are.

For example, I would like to return the below when the query is run;

MEMBNO
123456789
123456782

At the moment, my query only returns;

MEMBNO
123456789

where there are two or more records with this exact number.

Current query is;

SELECT
  basic.MEMBNO
FROM
  basic
GROUP BY
  basic.MEMBNO
HAVING
  COUNT(basic.MEMBNO) >1
ORDER BY
  basic.MEMBNO

A query I thought might work was;

SELECT
  basic.MEMBNO
FROM
  basic
GROUP BY
  basic.MEMBNO
HAVING
  COUNT(LEFT(basic.MEMBNO,8)) >1
ORDER BY
  basic.MEMBNO

I'm faily new to SQL and databases in general, so apologies if I haven't explained myself that well. Does it require the LIKE operator somewhere?

I'm using Microsoft SQL Server Report Builder 3.0.

You can identify the repeated 1->8 substrings using this:

SELECT LEFT(basic.MEMBNO,8)
FROM dbo.basic
GROUP BY LEFT(basic.MEMBNO,8)
HAVING COUNT(*) > 1;

So you can join with that to get the individual values:

;WITH x(m) AS
(
  SELECT LEFT(basic.MEMBNO,8)
  FROM dbo.basic
  GROUP BY LEFT(basic.MEMBNO,8)
  HAVING COUNT(*) > 1
)
SELECT b.MEMBNO
  FROM dbo.basic AS b
  INNER JOIN x
  ON x.m = LEFT(b.MEMBNO, 8)
  ORDER BY b.MEMBNO;

You can also do it this way (I just wasn't sure when windowed COUNT was introduced):

;WITH x AS
(
  SELECT MEMBNO, c = COUNT(*) OVER (PARTITION BY LEFT(MEMBNO, 8))
  FROM dbo.basic
)
SELECT MEMBNO FROM x WHERE c > 1
ORDER BY MEMBNO;


Good learning source for learning normalization Can anyone recommend a good learning source to learn about database normalization from scratch please?

I referred to many youtube videos but they weren't fit to me.

I know it's essentially a comic, but I keep it on my shelf for reference and to start conversations.  If you want something to talk about database normalization from scratch with good examples, I recommend "The Manga Guide to Databases by Mana Takahashi".  http://amzn.com/1593271905


Was using `set datcollate='C'` a mistake? In a pre-production environment running django 1.6 on PostgreSQL 9.1 we had a problem with database records arriving in a different order from what the application was expecting. So we ran this command as the postgres user:

update pg_database set datcollate='C' where datname='appname';

About a week later it was suddenly impossible to log into the application as a user which did exist in the database and had been unchanged for years. Attempts at looking up the user by username fails but looking up the same user by primary key worked, and the returned username was in fact correct.

That problem was fixed by reindexing the database like this:

REINDEX DATABASE appname;

Is it possible that the update command has broken the indexes? If that update command is indeed unsafe, then what is the safe way to change collation on a PostgreSQL database?

In case it is relevant, these are the queries that django generates for looking up the user by username or primary key:

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."username" = username
SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 1

The datcollate column of pg_database stores

  LC_COLLATE for this database

An other page of the documentation about collations says:

  The collation feature allows specifying the sort order and character
  classification behavior of data per-column, or even per-operation.
  This alleviates the restriction that the LC_COLLATE and LC_CTYPE
  settings of a database cannot be changed after its creation.

That means that by updating that column you managed to cross some river that should not be crossed under any circumstances.

On the other hand, as collations affect the ordering of text, varchar and varchar typed columns (and possibly some others, check the documentation page for details).  This also means that the existing indexes on such columns will be ordered differently from what the LC_COLLATE dictates.  This will result of some weird behaviour, and as you've seen, at least a REINDEX will be needed.  I would expect that doing a

SET enable_indexscan TO off;

would help you finding the user by username, because it would do a sequential scan instead of an index scan, so not trying to use the unusable index.

I cannot tell if this is enough to fix your DB, but I would do the following instead:

set up a new cluster with the desired collation
take a dump from your DB
restore it on the new cluster, watching for errors
test it thoroughly and then start using it instead of the old one

For the future, you cannot change the DB level collation, but you can define collation on the column level.

One can even change the collation on a column like

ALTER TABLE t ALTER COLUMN col1 TYPE text COLLATE "en_US";

where you keep the type as it is, just set a different collation.

Just a final notice: changing the system catalogs directly should always be a last resort.  Before doing that (probably except adding new enum values in pre-9.1 versions), one should carefully examine the possible outcomes from the documentation, blog or mailing list posts and testing.


Should I increase max_connections in AWS RDS t1-micro for MySQL? I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max value by creating a DB parameter group for this Micro instance.

My confusion is

Should I increase the max connection value for micro in DB parameter group? or Should i consider upgrading to next RDS level which provides more maximum connections (125)?
Should I increase max_connections on micro RDS to 125 vs upgrading to RDS small instance?
Why and What factors should I make the decision on?

Thanks

Each connection carries the load of per-connection buffers as set by these parameters

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd buffer_size

Changing the number of connections increases the amount of memory each connection can demand to this : (join_buffer_size+sort_buffer_size+read_buffer_size
+read_rnd buffer_size) X max_connections
I have written about these before

Jan 03, 2012 : Understanding mysqltuner Recomendations w/ Unused Catalogs
Apr 24, 2012 : How costly is opening and closing of a DB connection?
Feb 01, 2013 : Show processlist / Max concurrent connections seem to max out at 131

ANALYSIS
Amazon has to set the number of connections based on each model's right to demand a certain amount of memory and connections
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I wrote about this as well : When should I think about upgrading our RDS MySQL instance based on memory usage?
This allows Amazon to do the following:

Charge you for each memory model based on seamless MySQL usage
Fairly Apportion Resources for MySQL RDS per region
Shoot yourself in the foot for tampering with per-connection settings

RECOMMENDATION
Perhaps you should try useing Amazon EC2 where you have no restrictions on access to my.cnf


Find numbers not used in a column How can I find in a column filled with numbers, the ones that are not used.

Table_A:

'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
'client_code' int(4) unsigned NOT NULL,
'client_name' varchar(50) NOT NULL,
PRIMARY KEY ('id')

INSERT INTO Table_A ('client_code','client_name') VALUES (1,'Bob');
INSERT INTO Table_A ('client_code','client_name') VALUES (2,'Anna');
INSERT INTO Table_A ('client_code','client_name') VALUES (5,'Jim');
INSERT INTO Table_A ('client_code','client_name') VALUES (6,'Tom');
INSERT INTO Table_A ('client_code','client_name') VALUES (10,'Mattew');

If I SELECT 'client_code' FROM Table_A the output will be 1,2,5,6,10. How can I have the numbers that are not used?

(3,4,7,8,9,11,12...)

To see what's missing, you can compare to a complete list.
There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums.

Helper table to provide numbers from 0-9:

CREATE TABLE int10 (i INT);
INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Building on that, generate a complete range of numbers:

SELECT i1.i * 10 + i0.i AS client_code
FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a) -- limit to actual max

Now you can generate the list of missing numbers, exactly as requested:

SELECT GROUP_CONCAT(n.client_code ORDER BY n.client_code) AS missing_codes
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
WHERE  a.client_code IS NULL;

Key elements are the LEFT JOIN and the aggregate function GROUP_CONCAT().
To get full list of codes / names including missing codes, as may be your ultimate goal:

SELECT n.client_code, a.client_name
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
ORDER  BY n.client_code;

-> SQLfiddle demo.


oracle - hashing method, excluding DBMS_CRYPTO BACKGROUND:
DBAs have hidden DBMS_CRYPTO package to any role, grantee, and I wonder why. I have to use wrapper instead :(

Is there any way to get the same hash for the text field with the same text? All below methods give different results for the same text as they seem depending on rowid, or something similar:
SELECT
 , utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg))                                                  AS "RAW"
 , rawtohex(utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))                                        AS raw_hex
 , rawtohex(standard_hash(sys_op_map_nonnull(log_msg), 'MD5'))                                       AS md5_hex
 , ora_hash(log_msg)
 , dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))           AS md5
 , rawtohex(dbms_obfuscation_toolkit.md5(input => utl_raw.cast_to_raw(sys_op_map_nonnull(log_msg)))) AS hex

log_msg is CLOB

DBMS_OBFUSCATION_TOOLKIT was deprecated and replaced by DBMS_CRYPTO.
ORA_HASH does not produce a unique checksum, per se, and does not support LOB types.
STANDARD_HASH uses DBMS_CRYPTO under the hood, but also does not support LOB types.

Having recently faced a similar problem myself, I was able to get execute privileges on DBMS_CRYPTO for the application schema by demonstrating the need. The DBAs didn't have any problem granting the access as long is it was required and documented. It's the only real option for what you're trying to do. My function to produce a SHA256 hash wound up looking like this:
function get_sha256sum (p_clob in clob) return raw
is
begin
    return dbms_crypto.hash (src => utl_raw.cast_to_raw(p_clob), typ => 4);
end get_sha256sum;

Note: don't try to cast the raw checksum to anything else - it's only accurate as a checksum in the original format.


What is the best solution to prevent picking the same value (by multiple requests)? Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick up next tour with highest priority for the given color.

This is easy, but the challenge is to completely prevent picking up the same tour multiple times, which can surely happen in live system.

Current approach is:

SELECT tour_id based on (color, priority, pickedUp = false)
UPDATE tour set PickedUp=true
Return tour_id to the calling application

Obviously, since we have SELECT and UPDATE as separate requests, another request can come at this time and pick up the same tour.

I know it's possible to have a single UPDATE + SELECT query, but: I still need to return the picked up tour_id to the caller, how would I do this? Is it possible with SQL to execute SELECT after UPDATE + SELECT in one query?

There are several approaches, but a simple one in pseudo code is:

Retry:
    SELECT tour_id based on (color, priority, pickedUp = false)
    if none selected, then no vacancy error
    UPDATE tour set PickedUp=true where tour_id=:tour_id and PickedUp=false
    If none updated, then retry
Do something with :tour_id

But pickedUp should probably be more than a boolean, recording who picked up the tour.  Otherwise if the "Do something" fails then the tour will appear booked in the database, but no one will know why.


Summarizing over Outrigger Dimensions Directly (e.g. Total Customers by Demographic by Signup Date) According to Kimball:
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/

A dimension can contain a reference to another dimension table. For instance, a bank account dimension can reference a separate dimension representing the date the account was opened.

This suggests to me you'd be able to just query the Account dimension directly if you wanted to track number of accounts opened over time, for example. Is this common?
In my case, I'm modeling Customer as a dimension, which also references Date (for Signed Up Date) and Demographic dimensions.
A common kind of query the business ask is how many signups we've had over time by demographic, for example. This doesn't involve a fact table at all. All this can be worked out by summarizing the dimension itself. Does this indicate some kind of a smell in the schema design, or is this just a trait of certain kinds of dimensions where the dimension itself has some significance to the business?

Conceptually, CustomerSignup is a fact.  But if every Customer has exactly one Signup, you only care about the date, and there no measures associated with it, it might as well just be stored on the Customer dimension.


Login permission on Availability group database We have an availability group database. Just wanted to check on the below statement  whether it needs to be run on all replica (By failing over and making it primary)or it can be run on the primary and the permission will be replicated to the replicas.

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON [DB_Lock] TO sqlogin

SQLOGIN is a sql login and has been mapped to the  availability group database user.

DB_Lock is a table on the Availability group database. Thanks for the help!

If the database is in high availability group, then No, you do not need to failover, once you do it on the primary replica, it will be picked up and replicated to to other secondary replicas.
Of course, we assume the sql login exists already on all replicas.


Deleting would-be orphans when creating FK in Postgres I am working on a script that replicates schema and data. I first create all the structure except for foreign keys after reading it from the source's system tables, then copy the data, then create the FKs so that if a would-be orphan gets inserted in a child table after I copied the parent (while I am running the script), I still get the basic schema and the data, even though the FK creation fails due to the orphan data.

My next step is to do something like wrap a try/catch around the create FK statement and, if it fails, delete the orphan data that caused it. Doing this will be somewhat complicated because I would have to extract all the reference keys and assemble my own delete statement (I currently don't do that because PG system tables provide a ready REFERENCES statement which I can concatenate).

But I am wondering if there is a way to do a ALTER TABLE x ADD CONSTRAINT fk_y FOREIGN KEY ... DELETE ANY WOULD-BE ORPHANS which would save me that step.

So if my parent table is:

 parent_id     | parent_name
---------------------------------
 1             | Joe
 2             | Mary

and the child is:

child_id      | child_name     | parent_parent_id
--------------------------------------------------
 11           | Jimmy          | 1
 21           | Joey           | 2
 31           | Jeff           | 3

'Jeff' would be deleted before enforcing referential integrity because his parent and he were introduced in the source after the source parent was copied to the target. Then the FK can be created against consistent data.

Clarifications (from comments / chat discussion):

  Do you read the data in a single transaction or not?

I don't use a single transaction as it would be too huge. I break the retrieved data by table and for some large tables by table segment (batch of 5000 or 10000 rows).
This is essentially an ETL that makes an "evil twin" out of a DB. hope that makes sense.
On average a DB copy in my environment takes between 20 min (for smalled DBs) to 1.5-2 hrs (for larger DBs) some tables have millions of rows. I load chunk by chunk in the memory of my Python script

  What about pg_dump or pg_basebackup?

My app does some other things that pg_dump doesn't do, such as better logging. Also, pg_dump expects the same users on both the source and target and a host of other issues. I tried it, didn't like it. I like my tool much better, I want to  work through this minor issue that is left and happens only sporadically.

As has been suggested in comments, the standard tool pg_dump might serve you better. It takes a consistent snapshot and preserves referential integrity during backup automatically - given the source DB is consistent (FK constraints in place). Use it if you can!

The related tools pg_dumpall and pg_basebackup have also been mentioned, but those work on whole DB clusters while you obviously only want to copy (parts of) a single database.

However, there may still be valid reasons for the question. It seems unfair to turn it down on speculation. The question itself is interesting enough:

How to import inconsistent data into a FK relationship and fix it?

NOT VALID option

There is no DELETE ANY WOULD-BE ORPHANS option in the creation of FK constraints (like you'd wish), but the related NOT VALID option for FOREIGN KEY constraints in Postgres 9.1 or later should be instrumental. The manual on ALTER TABLE:

ADD table_constraint [ NOT VALID ]

  This form adds a new constraint to a table using the same syntax as
  CREATE TABLE, plus the option NOT VALID, which is currently only
  allowed for foreign key and CHECK constraints. If the constraint is
  marked NOT VALID, the potentially-lengthy initial check to verify that
  all rows in the table satisfy the constraint is skipped. The
  constraint will still be enforced against subsequent inserts or
  updates (that is, they'll fail unless there is a matching row in the
  referenced table, in the case of foreign keys; and they'll fail unless
  the new row matches the specified check constraints). But the database
  will not assume that the constraint holds for all rows in the table,
  until it is validated by using the VALIDATE CONSTRAINT option.

So you can create involved tables and import data and create PK constraints, and FK constraints with NOT VALID.

ALTER TABLE child ADD CONSTRAINT child_parent_id_fkey
FOREIGN KEY (parent_id) REFERENCES parent(parent_id) NOT VALID;

That prevents any new writes from violating referential integrity, while tolerating existing violations. (And the system is aware of it.)

Delete orphans later, at the earliest opportunity:

DELETE FROM child c
WHERE  NOT EXISTS (
   SELECT 1 FROM parent p
   WHERE  p.parent_id = c.parent_id
   );

This does nothing if the data is consistent, and the work needs to be done either way to verify integrity. So it won't get any more efficient for unreliable data that needs verification.

Then validate the constraint:

ALTER TABLE child VALIDATE CONSTRAINT child_parent_id_fkey;

Doesn't have to be in the same transaction. Read the manual for details.

Related:

Partial db dump/restore
Disable all constraints and table checks while restoring a dump


simple mysql query design help Simple query design help. Below is my query:

SELECT tkt.ticket_id, tkt.`number`, val.field_id, FF.name,    val.value
FROM os_TICKET tkt, os_form_entry_values val, os_form_field FF
where tkt.ticket_id = val.entry_id
and val.field_id = FF.id

OUTPUT

ticket_id   number  field_id    name        value
5       592593         4        SSN         11111111111
5       592593         87       title       Mr
5       592593         117      dob         05/01/2011
5       592593         3        mobile      0091981123232

I want to have the output like this:

ticket_id  Number    SSN          Title   dob            mobile
5          592593    1111111111   Mr.    05/012011  009198182838485

the ssn, title, dob and mobile are in another table (form_entry_value) and is linked with the ticket_id

Server version: 5.6.17 MySQL Community Server (GPL)

Update: I have change a bit the stored procedure to cope with null values.

Update: I have added fiddle

I have deduced your schema. I think that you schema is generated by:

create table  os_TICKET (
  ticket_id int not null primary key,
  number int not null
);

insert into os_TICKET values (5,592593);

create table os_form_entry_values (
  entry_id int not null,
  field_id int not null,
  value varchar(100)
);

insert into os_form_entry_values values
(5, 4, "111111"),
(5, 87, "Mr"),
(5, 117, "05/01/2011"),
(5, 3, "0091981123232");

create table os_form_field(
  id int not null primary key,
  name varchar(16)
);

insert into os_form_field values
(4, "SSN"),
(87, "title"),
(117, "dob"),
(3, "mobile");

To pivot the SSN field you can use this query:

select tkt.ticket_id,  tkt.number, SSN
from os_TICKET tkt
inner join (select val.entry_id, val.value as SSN
            from os_form_entry_values val
            where val.field_id in (4))  as SSN
   on tkt.ticket_id = SSN.entry_id
where tkt.ticket_id =5;

  +-----------+--------+--------+
  | ticket_id | number | SSN    |
  +-----------+--------+--------+
  |         5 | 592593 | 111111 |
  +-----------+--------+--------+

To put attribute value as column, we must join table. We can extends the previous query to include the other attributes:

select tkt.ticket_id,  tkt.number, SSN, title, dob, mobile
from os_TICKET tkt
inner join (select val.entry_id, val.value as SSN
            from os_form_entry_values val
            where val.field_id in (4))  as SSN
  on tkt.ticket_id = SSN.entry_id
inner join (select val.entry_id, val.value as title
            from os_form_entry_values val
            where val.field_id in (87))  as title
  on tkt.ticket_id = title.entry_id
inner join (select val.entry_id, val.value as dob
            from os_form_entry_values val
            where val.field_id in (117))  as dob
  on tkt.ticket_id = title.entry_id
inner join (select val.entry_id, val.value as mobile
            from os_form_entry_values val
            where val.field_id in (3))  as mobile
  on tkt.ticket_id = title.entry_id
where ticket_id = 5;

  +-----------+--------+--------+-------+------------+---------------+
  | ticket_id | number | SSN    | title | dob        | mobile        |
  +-----------+--------+--------+-------+------------+---------------+
  |         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 |
  +-----------+--------+--------+-------+------------+---------------+
  1 row in set (0.00 sec)

The above query is using a static attributes (SSN, title, doc, mobile).
If you have a static form that are never changing, the query can be working.
But if your form fields are changing, it is possible to
generate a dynamic sql using a stored procedure.

delimiter $$
CREATE PROCEDURE pivot(in tid int)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE field_name varchar(16);
  DECLARE field_id int;

  DECLARE names CURSOR FOR
    select  FF.id, FF.name
    from os_TICKET tkt
     inner join os_form_entry_values val on tkt.ticket_id = val.entry_id
     inner join os_form_field FF on val.field_id = FF.id
     where tkt.ticket_id = tid;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  set @sql_selector := 'select tkt.ticket_id, tkt.number ';
  set @sql_joins    := ' from os_TICKET tkt ';
  set @sql_where    := concat(' where ticket_id = ', tid);

  OPEN names;
  read_loop: LOOP
    FETCH names INTO field_id, field_name;
    select field_id, field_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    if field_id is not null and field_name is not null then
      set @sql_selector := concat(@sql_selector, ',', field_name);
      SET @sql_joins    := concat(@sql_joins,
       ' left join (select val.entry_id, val.value as ',field_name,
                 ' from os_form_entry_values val
                  where val.field_id in (', field_id,'))  as ', field_name,
                 ' on tkt.ticket_id = ', field_name ,'.entry_id');
    end if;
  END LOOP;
  close names;

  set @sql := concat(@sql_selector, @sql_joins, @sql_where);
  PREPARE dynamic_statement FROM @sql;
  EXECUTE dynamic_statement;
  DEALLOCATE PREPARE dynamic_statement;
END$$
delimiter ;

So if we call pivot the result must be the same of the previous query.

mysql> call pivot(5);
+-----------+--------+--------+-------+------------+---------------+
| ticket_id | number | SSN    | title | dob        | mobile        |
+-----------+--------+--------+-------+------------+---------------+
|         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 |
+-----------+--------+--------+-------+------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

We can insert a new field:

insert into os_form_field values(200, 'new_field');
insert into os_form_entry_values values(5, 200, 'new_value');

mysql> call pivot(5);
+-----------+--------+--------+-------+------------+---------------+-----------+
| ticket_id | number | SSN    | title | dob        | mobile        | new_field |
+-----------+--------+--------+-------+------------+---------------+-----------+
|         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 | new_value |
+-----------+--------+--------+-------+------------+---------------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I advise you to refactor you database, or to do pivoting in your application.
The previous queries and stored procedure could be very very inefficient.

Hope it help.


Mysql Slow when join query running increaed Our Mysql dedicated server box always have around 40 - 80 active user connection , so the query speed very good and Cpu usage low . (around 4 - 8 core from 24 core)
but sometimes when some wordpress "Join" query running , server speed slow and other query go in queue so server running query increased over 600 -1000 /s.

this is following query that I think cause issue :

SELECT SQL_CALC_FOUND_ROWS  WP_POSTS.ID FROM WP_POSTS  INNER JOIN
WP_TERM_RELATIONSHIPS ON (WP_POSTS.ID = W
P_TERM_RELATIONSHIPS.OBJECT_ID) WHERE 1=1  AND (
WP_TERM_RELATIONSHIPS.TERM_TAXONOMY_ID IN ()

My question ;

why Join query affected our mysql server performance ?
is it config related problem ? or we need more hardware ? at this time our server 24 core + 64 GIG RAM + SAS 15K RAID 10

Update 1 :

Simple Processlist related one of user :

| 1871506 | xhamster_gdgdgd  | server6:48608 | xhamster_hehehe           | Query   | 456  | Sending data                   | SELECT SQL_CALC_FOUND_RO
WS  wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2467509 | kordgsmc_gsm     | server6:37144 | kordgsmc_vb             | Query   | 22   | Waiting for table level lock | SELECT userid, username
                                FROM user
                               sWHERE userid IN (6295)                                   |
| 2468708 | samseria_wp      | server6:38899 | samseria_wp             | Query   | 23   | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2469440 | samseria_wp      | server6:39953 | samseria_wp             | Query   | 6    | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |
| 2469825 | samseria_wp      | server6:40542 | samseria_wp             | Query   | 23   | Sending data                 | SELECT SQL_CALC_FOUND_ROWS
wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts |

With MyISAM, an INSERT/UPDATE/DELETE will request an exclusive lock, and have to wait for any SELECTs to finish. If another SELECT comes in after the write, then it will be blocked waiting for the write to get its lock and finish its action.

That is, one simple write can snowball into the mess you are seeing.

Changing to InnoDB is likely to avoid the problem, since locks are taken at the row level, not the table level.

Let's see the EXPLAIN for your SELECT (and provide one that does not have syntax errors).  Plus tell us how big the table is.  And SHOW CREATE TABLE for each relevant table.

Speeding up the query will help avoid the locks.


Insert into partitioned tables with select, handle conflicts I need to insert into partitioned tables using a select, how do I handle conflicts? Postgres complains this:
[0A000] ERROR: ON CONFLICT clause is not supported with partitioned tables

My query looks like this:

INSERT INTO table_partition SELECT * FROM old_table WHERE start_time >= '2014-12-01 00:00:00' AND start_time < '2015-01-01 00:00:00' ON CONFLICT ON CONSTRAINT table_partition_201412_pkey DO NOTHING ;

Currently, I have created the partition tables with the old table having triggers to copy insert/update/delete onto the partition tables. I've to backfill the older data onto partitions, once done, I can drop the older tables and start using the partitioned tables, but there's a chance some of the data with few date ranges might overlap. It's a problem for me. Could you please help?

Do the same "by hands" like

WITH cte AS ( INSERT INTO dest
              SELECT FROM src
              WHERE (non-matching condition)
            )
UPDATE dest
SET dst.fieldset = src.fieldset
FROM src
WHERE (matching condition)

Dependent by the data and matched/non-matched relation you may swap inserting and updating parts or return processed records identifiers from CTE and use it in main query.


SQL show only the oldest date with same number I'm writing some code but there are some errors. If the numbers are the same, to show only one record of the oldest date. Thanks for help.

The errors: SQL command not properly ended and not a single-group group function

My code in the following

    select
       DIVISION,
       DEPT_CODE,
       NO,
       min(START_DATE),
    row_number() over (order by
       DIVISION,
       DEPT_CODE
    ) as "NEW_NO"
    FROM INFO
    Group by NO
    where (TITLE IS NULL
      OR (upper(TITLE) NOT LIKE '%RESEARCH%'
      AND upper(TITLE) NOT LIKE '%POSTDOCTORAL FELLOW%'
      AND upper(TITLE) NOT LIKE '%PROJECT%'
      )
    )

To sum up, there are three major issues in your query:

As noted by ypercubeᵀᴹ, the order of the clauses is wrong in one place: the WHERE clause should go after the FROM clause and before the GROUP BY clause:

SELECT
  ...
FROM
  ...
WHERE
  ...
GROUP BY
  ...

As has already been addressed by Rajesh Ranjan, the GROUP BY clause must include all non-aggregated columns of your SELECT list, so it should look like this:

GROUP BY
  DIVISION,
  DEPT_CODE,
  NO

Finally, if your statement happens to be part of a PL/SQL block, you must end it with a semicolon:

SELECT
  DIVISION,
  DEPT_CODE,
  NO,
  min(START_DATE),
  row_number() over (order by
    DIVISION,
    DEPT_CODE
  ) as "NEW_NO"
FROM INFO
WHERE (TITLE IS NULL
  OR (upper(TITLE) NOT LIKE '%RESEARCH%'
  AND upper(TITLE) NOT LIKE '%POSTDOCTORAL FELLOW%'
  AND upper(TITLE) NOT LIKE '%PROJECT%'
  )
)
GROUP BY
  DIVISION,
  DEPT_CODE,
  NO;

Note that resolving all these issues does not guarantee you will get the expected result. Addressing the above points should merely let you run your query without errors. If it shows incorrect results, that will be a separate issue requiring a separate question.


postgres composite index design In my rails application using the Postgres database, I have a table called tw_schedules which belongs to a scenarios table (one scenario to many tw_schedules). In addition to the scenario_id foreign key (primary key "id" column from scenarios table), it contains two more foreign keys: type_well_id (primary key "id" column from type_wells table) and tw_import_id (primary key "id" column from tw_imports table).

Each tw_schedules belonging to a particular scenario can either have a type_well_id or tw_import_id, but not both.  This is an application enforced constraint with the result that type_well_id or tw_import_id can have a null value.

My question is: in order to optimize the query retrieval time, should I create two compound indexes on:

scenario_id + type_well_id

and

scenario_id + tw_import_id

or three individual indexes on columns:

scenario_id
type_well_id
tw_import_id

The reason I ask is that both type_well_id and tw_import_id columns can contain null values.

I would try making two composite partial indexes like

CREATE INDEX idx1 ON tw_schedules (scenario_id, type_well_id) WHERE type_well_id IS NOT NULL;

CREATE INDEX idx2 ON tw_schedules (scenario_id, tw_import_id) WHERE tw_import_id IS NOT NULL;

But, of course, everything depends on the selectivity of these indexes.


Problem with EXEC MyLinkedServer...sp_executesql within transaction I rather clueless about this so any help is appreciated.

We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET).

This stored procedure has got the follwing (here simplified) code in it:

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'DELETE FROM ... WHERE ...'
--And now the important part:
EXEC MyLinkedServer...sp_executesql @sql

So it calls sp_executesql on a linked server. Most of the time this query runs fine, however sometimes the EXEC-line fails. The error message is at the end of this post.

I am not quite sure what is happening here. I am not sure if it's EXEC not working within distributed transactions or if maybe EXEC is not running within the "normal" process (whatever that is, just guessing from the error message...).

The motivation behing the EXEC-construct in this case is that we need to delete some entries from a very big table on the linked server and this way it is much faster than just writing:

DELETE FROM MyLinkedServer.DBName.TableName WHERE ...

The error message:

Original: Die Verwendung des OLE DB-Anbieters "SQLNCLI10" mit SQL Server außerhalb des Prozesses with nicht unterstützt.

Google-Translation: The use of OLE DB provider "SQLNCLI10" with SQL Server out of process ist not supported.

The error message refers to the OLE DB provider defined for the linked server. Hence the SQL code has no error.

The problem is the linked server is not properly defined. You should test the connection with the linked server and you will see the full error.

To fix it: recreate the Linked server setting an OLE DB provider supported in your Sql Server version. For example: 'SQLNCLI' or 'SQLNCLI11'.

To recreate a linked server:

Right click -> Script Linked Server as -> Create To -> New query editor window.
Change the OLE DB provider and the correct password (it will be scripted as @rmtpassword='########').
Test the connection with the linked server.
Try to execute again:

EXEC MyLinkedServer...sp_executesql @sql


SELECT users.id,users.group_id,Approved_Courses.module
      ,Approved_Courses.order_number , Enrollments.id
FROM
Users full outer join
approved_courses
  on users.group_id=approved_courses.group_id
left outer  join Classes
  on Classes.id=Approved_Courses.group_id and Approved_Courses.module=Classes.module
left outer join Enrollments
  on Classes.id=Enrollments.class_id
 and Users.id=Enrollments.staff_id

order by users.id,users.group_id
        ,Approved_Courses.module ,Approved_Courses.order_number


Transactional Replication Concerns We are debating an architecture that employs sql server 2012 and transaction replication. Idea is to offload reporting activity to a secondary server and have the ability to include/exclude what is replicated by keeping historic data on the reporting server.

Couple of questions:

Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.
If the subscriber server goes down, will the primary still function

Thanks!

1.Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.

There will be an impact on the publisher, but depending on how you set up your transactional replication schema that can be very small.  One big problem that can become an issue is if you have a local distributor, especially if you are dealing with a large amount of publications being replicated to multiple subscribers in a push configuration.  If that is the case, one way to alleviate the server impact is to have a remote distributor.

Also, by design with transactional replication the Log Reader Agent is doing just that:  reading the transaction log of the publisher.  There are many strategies to reduce this impact, such as replicating stored procedure executions instead of the resulting data.

  2.If the subscriber server goes down, will the primary still function

Yes, the publisher will still function.  Commands to get replicated will be queuing up, and depending on how long your retention is setup for and how long the subscriber is disconnected you may need to reinitialize the subscription.

I recommend this TechNet reference: Enhance Transaction Replication Performance.  There are a handful of great strategies in that document that should be considered.


UPDATE table element using data and id from another table I need to update an element the row for id=N in table A with derived data from Table B.

Table A has uid as its primary key, and contains DATE element foo.

Table B has a_uid as an element, for each of which there are multiple rows for DATE element b_foo.

Conceptually, what I want to do is

UPDATE A SET foo=MAX(b_foo) WHERE uid = a_uid

Table A:

uid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
foo DATE

Table B:

b_uid INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
a_uid INT(10) UNSIGNED,
b_foo DATE

I feel like this should be more obvious than I'm finding it to be; maybe it's because it's a COVID Monday? Regardless, many thanks for help!

What you want is called an UPDATE JOIN

First you must create a query that has the MAX(b_foo) for every a_uid:

SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid;

Making this a subquery, you can perform the UPDATE JOIN as follows:

UPDATE A INNER JOIN
(SELECT a_uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
ON A.id = C.a_uid SET A.foo = C.max_b_foo;

or you can get fancy and do

UPDATE A INNER JOIN
(SELECT a_uid uid,MAX(b_foo) max_b_foo FROM B GROUP BY a_uid) C
USING (uid) SET A.foo = C.max_b_foo;

GIVE IT A TRY !!!


Audit Trail trigger can't detect if dates are the same I know this is a common problem with SQL language triggers, but I just cannot seem to get this right.

Basically I'm trying to write an "Audit Trail" trigger for all of my fields. I'm using an IF UPDATE()... branching scheme which, as I've discovered on other SO posts, is not a reliable function.

My problem is centered around a date field that may be NULL. I'm simply trying to write a trigger that will only write to my log table if the date field has changed (either in value or to/from NULL)

Right now my code in this branch is as follows, and will always fire no matter if the dates didn't change (but other fields did):

IF UPDATE(DueDate)
BEGIN
    DECLARE @insertedDate date
    DECLARE @deletedDate date

    SELECT @insertedDate = i.DueDate, @deletedDate = d.DueDate
    FROM inserted i, deleted d WHERE d.DueDate <> i.DueDate

    IF (@insertedDate = @deletedDate)
    BEGIN
        return
    END

    SET @IsModified = 1
    SET @LogText += ('Due: ')
    SELECT @TempText = '"' + ISNULL(convert(nvarchar(255), i.DueDate, 107), '[deleted]') + '" '
    FROM inserted i
    SET @LogText += @TempText
    SET @TempText = ''
    SELECT @TempText = '(was: "' + ISNULL(convert(nvarchar(255), d.DueDate, 107),'[not set]') + '"), '
    FROM deleted d
    SET @LogText += @TempText
    SET @TempText = ''

END

Which will incorrectly produce (instead of not running at all):

Due: "Mar 04, 2020" (was: "Mar 04, 2020"),

Both @Temptext and @LogText are of type nvarchar(MAX); and @LogText is a running string which contains the final log line to be written (and will concat through the whole procedure with +=)

Is there anything I'm missing with how I'm constructing these conditionals? I've tried several iterations and function methods but this one is just not making sense to me...

I believe you just have to drop the WHERE clause from this SELECT:

SELECT @insertedDate = i.DueDate, @deletedDate = d.DueDate
    FROM inserted i, deleted d WHERE d.DueDate <> i.DueDate


SO, Google answers it as :-

  Google Cloud SQL does not support SUPER privileges which means that GRANT ALL PRIVILEGES statements will not work. As an alternative, you can use GRANT ALL ON %.*.

They have mentioned it here

Thanks for helping anyways :)


Trouble with Ola Hallengren's @DatabasesInParallel parameter I am testing out this new parameter @DatabasesInParallel and it seems to only back up 1 databases at a time but it doesn't specificy this as a limitation in the documentation so I am wondering if I might be doing something wrong.

I have 4 DBs set up and I am manually running the following 4 commands:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'AdminDB',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'Pop',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DaveTest_StackOverflow2010',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DaveTest_SUPERUSER',
@Directory = N'\\499bsl01\sqldata_test$',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24,
@Compress = 'Y',
@CheckSum = 'Y',
@DatabasesInParallel = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'

When I check the QueueDatabase table, I can see that the DatabaseOrder and RequestStartTime column are the exact same for all 4 databases. However, when you look at the column DatabaseStartTime it shows the first 2 databases starting at the same time and then the next 2 databases dont start until the 2nd database has finished doing its backup and verify check.

Is this the intended functionality with the parameters that I am using? I would expect all 4 databases to be started at the exact same time with this parameter.

EDIT 11/14: Updated title/wording of post

Original post said it was backing up only 2 databases at a time but it was really only doing 1 database at a time. The first DB that is backed up in my test is so small that I didnt realize it finishes almost immediately and the second on starts. However you can see this

After reading Ola's last comment I had a light bulb moment and figured what I was doing wrong and got it working.

Since I was just testing these commands out I never actually put them into an Agent job - I figured they would work the same way by just manually executing them at the same time. I just had all 4 of the job commands pasted in a Query window and would just execute that. I thought this would work but it appears that it doesn't even though they all showed the same RequestStartTime in the QueueDatabase table.

Once I created the jobs and set a single schedule I could see the RequestStartTime varies by milliseconds but the DatabaseStartTime is the same for all DBs.

The screenshot below shows the QueueDatabase table from my test today (pink) which was successful when I created an Agent job. The test on 11/8 (blue) was when I manually executed the commands in a Query window.

I asked Ola in an email if this functionality makes sense that it doesn't work when executing manually in a query window and below is his response:

  When you execute all the commands in one query window, you can never
  get parallel executions (as T-SQL does not have support for that).

  If you would like to test it in SSMS, then you need to us 4 different
  query windows, and then quickly start the execution of all of them.


Environment Variable PATH exceeding recommended length I'm installing Oracle 12c on Windows 10 Pro and receiving the error message:

  "PRVF-3919 : Failed to retrieve value of environment variable "PATH""

I've read through several similar questions and followed recommended procedures:

I'm executing the installation setup as Admin
I renamed Path to PATH
I counted the existing chars in Path (834, well under the 1023
limit)
I deleted unnecessary Path statements to reduce size
I deleted all entries in Path to start fresh.

All to no avail.  I'm still getting the same error message.  Does anyone have any other ideas or suggestions?

Thank you,
mfc

Thank you all for your answers.  I believe @John Eisbrener had the correct answer.  I placed the installation in a root "C:\Oracle" directory instead of accepting the default User Directory installation.  It installed without issue after that.

Thank you all for your responses.


How to efficiently design MongoDB database for Uber-like application? I'm designing a MongoDB database for an Uber-like application, but since I'm new to NoSQL concept, I have several doubts.

I have three main collections - users, requests and messages. User can post a ride request, and if other user wants to give him a ride, accepts such a request and now text communication between those two users is started.

So, in SQL world I would have the following tables:

USERS
user_id
username

REQUESTS
request_id
request_details
gps_location
passenger_id
driver_id
timestamp

MESSAGES
message_id
message_body
request_id
sender_id
timestamp

But we're in NoSQL reality right now, and here things tend to look a little bit different. I know that I should forget about SQL mindset, about JOINs, and get familiar with embedded documents.

The question is - what's the proper way of designing the database in this case?

I could of course have such a design:

USERS
{
    "user_id": "001",
    "username": "John"
}

REQUESTS
{
    "request_id": "001",
    "request_details": "Chicago - NYC",
    "gps_location": [21.0,42.0],
    "passenger_username": "John",
    "driver_username": "Claire",
    "timestamp": "30-Mar-2016"
}

But there would be a data redundancy - passenger and driver usernames would exists both in Users and Requests model, and in case of username change, I would need to update all the requests for the given user. Using user_id instead of username could be a solution, but it would create a need for JOIN and in NoSQL we try to avoid it, don't we? And there are still messages left - how can we efficiently associate them with requests? I guess it's not a proper way and I need something else...

So maybe such a design?

{
    "user_id": "001",
    "username": "John",
    "requests": [{
        "request_id": "001",
        "request_details": "Chicago - NYC",
        "gps_location": [21.0,42.0],
        "passenger_username": "John",
        "driver_username": "Claire",
        "timestamp": "30-Mar-2016",
        "messages": [{
            "message_id": "001",
            "message_body": "Hi, how are you?",
            "timestamp": "30-Mar-2016-14-30-00"
        }]
    }]
}

But now several other things come to my mind. I will very often need to search for requests within a given radius and within a certain timespan, e.g. "show me all requests witin 10 miles not older than 24 hours", and for such a request I need to display all its details and username of the user who posted it. Actually, I will more often ask for requests than users (and each user will have more information, there will be not only his id and name, but also google/facebook username, photo, phone number, etc.). Isn't it a problem with this design?

Aren't all these collections (users/requests/messages) too coupled with each other?

What would be the best way to design a database here?

As you demonstrated in your question, there is a clear relational structure to your data.

There are different objects (Users, Requests, and Messages), which all relate to each other, potentially in multiple ways.
These objects have attributes (eg driver or passenger names) that would be referenced many times from other objects (Requests). This requires either data duplication or normalization (and thus joins)
The different objects are tightly coupled to each other, and your anticipated data access patterns reflect the need to query from multiple angles.

The title of this article is intentionally provocative, but there is a lot of good content: Why You Should Never Use MongoDB

In particular, the section "How MongoDB Stores Data" and the several sections after that discuss precisely the questions you ask regarding Normalization vs Denormalization, and issues with how to efficiently query your data.

Ultimately, you have one main question:

  What would be the best way to design a database here?

The answer is: Because your data is relational, don't use MongoDB.

MongoDB may be the right solution for storing unstructured (or less structured) data related to your application, but for the specific data you mention, it is the wrong choice.


Openldap Replication don't run after configuring I'm practicing OpenLDAP replication with 2 Centos 6.9 64bit virtual machines.

The setting-up process was fine, but after I insert data into the master server, nothing happens in the slave.

MASTER SETTING

slapd.conf

overlay syncprov
syncprov-checkpoint 100 10
syncprov-sessionlog 100
updatedn "cn=Manager,dc=example,dc=com"
updateref ldap://192.168.1.11:389

SLAVE SETTING

slapd.conf

   syncrepl     rid=2
                provider=ldap://192.168.1.10
                type=refreshOnly
                interval=00:00:00:01
                searchbase="dc=example,dc=com"
                filter="(objectClass=*)"
                attrs="*"
                scope=sub
                schemachecking=off
                updatedn="cn=manager,dc=example,dc=com"
                bindmethod=simple
                binddn="cn=manager,dc=example,dc=com"
                credentials=secret
updateref       ldap://192.168.1.10

I'm using OpenLDAP 2.4.21,  BerkeleyDB-4.8.

The directives updatedn and updateref do not make sense at the provider (master).

You probably also want type=refreshAndPersist for continous replication. Setting interval= to one second would not work well with many entries in the DB.

Make sure that attributes entryUUID and entryCSN are indexed at the provider.

Also note that OpenLDAP 2.4.21 is really ancient release. During the last 9 years many things were fixed in OpenLDAP, especially related to replication.

I'd recommend to use the RPMs built by LDAP Toolbox project. They provide YUM repos also for RHEL/CentOS 6.

See also: OpenLDAP Admin Guide -- Replication


Permission denied while trying to delete even when delete is granted I recently had this issue where I created a database user and gave delete and update permission. Yet, I was getting permission denied on a delete query.
Create user: create user username password 'whatever';
Grant: grant update, delete on table to username;
Query which gave permission denied:
delete from table
where condition1 and
condition2 and
condition3

After a bit of hunting I realized that maybe I am getting this error because I am adding a condition to delete. Which means the database is probably selecting those records. And I hadn't granted select on the table to the user.
Grant select: grant select on table to username
And it worked.


How to find which query is causing tempdb growing? Good day.
I'm trying to discover which query is causing tempdb growing too much (around 100gbyte).
In my new company, people is using a lot of dynamic Excel files with various query.
I'm pretty sure that one of them is "guilty" for tempdb growing.
The only solution I've discovered is to restart sql server service (it delete and recreate tempdb).
Is there a way to discover which query is allocating so much space ?
Thanks

--Lists the TempDB usage per each active session.
--It helps identifying the sessions that use the tempdb heavily with internal objects.

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text,
                   ERQ.statement_start_offset / 2,
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC

On a TEST SERVER you can start this query

--USE TempDB;
--GO
--SELECT TOP 1000000000
        IDENTITY(INT,1,1) AS RowNum
   INTO #StressTempDB
   FROM Master.sys.All_Columns ac1,
        Master.sys.All_Columns ac2,
        Master.sys.All_Columns ac3;
GO

and then start the above script to see which sessions are causing tempDB growth.


Logon failed for login due to trigger execution / LOGON TRIGGER I have this situation.
I created a logon script to block any session for specific logins. These logins are in a table.
So the behaviour of my script is simple.
Get the login from the table, compare it with the login who wants to access and then insert some information to another table.
The question here is:

I tested in one instance and everything went fine but in other instance and now in the previous instance when I enable the logon trigger appears a pop up and block my personal account.
My account is not included in the table so should not have this behaviour.
Obviously the part to insert date in a table, doesn't work.
Also other logins have been blocked

CREATE TRIGGER Accounts_V1
ON ALL SERVER WITH EXECUTE AS 'DBA'
 FOR LOGON
AS
BEGIN

DECLARE @Logon as varchar(50)
DECLARE @AppName as varchar(250)
DECLARE @ComputerName as varchar(50)
DECLARE @ServerName as varchar(50)
DECLARE @Original_Login as varchar(50)

SET @ComputerName =  HOST_NAME ()
SET @ServerName  =  @@servername
SET @AppName =  APP_NAME()
SET @Original_Login = ORIGINAL_LOGIN ()
SET @Logon = ( select LoginName FROM [dba]..[MissusesAccounts_test]

where LoginName NOT like '%WIN%' and ( @AppName like '%Microsoft%' or @AppName like '%PowerShell%' ) and @ComputerName not like '%sql%')

if (@Logon = @Original_Login)

    INSERT INTO [dba]..[LogonAccounts_test]
           ([ComputerName]
           ,[ServerName]
           ,[AppName]
           ,[Logon])
     VALUES
           (@ComputerName
           ,@ServerName
           ,@AppName
           ,@Logon)

        BEGIN
            print 'This login:  '+@Logon+' should not be used by individuals to run interactive queries in '
            +@AppName+' , and email has been sent to the Development Manager to investigate'

        END

END
REVERT

The trigger is failing because the query that is used to set @Logon is returning more than one row.  Since @Logon is a varchar variable, an error occurs if anything more than one row is returned.

To test if the login is in the table, eliminate the @Logon parameter and use IF EXISTS to test if LoginName and the other criteria match a row in the table:

IF EXISTS (
    SELECT LoginName FROM [dba]..[MissusesAccounts_test]
    WHERE LoginName = @OriginalLogin
    AND LoginName NOT like '%WIN%'
    AND ( @AppName LIKE '%Microsoft%' OR @AppName LIKE '%PowerShell%' )
    AND @ComputerName NOT LIKE '%sql%'
)

This is also safer from a logon trigger perspective because it always returns either true or false, so it will never cause an error (unless the MissusesAccounts table is dropped or modified).

You might also want to wrap this in some error handling so that if the MissusesAccounts or LogonAccounts_test table is accidentally dropped or modified, the error is handled, you are alerted, and it won't cause all logons to fail.


Cannot attach the AdventureWorks database I am trying to attach the AdventureWorks database with the following query:

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

But I get the following error:

  Create file encountered operating system error 5 (access is denied.) while attempting to open the physical file blah

Note: I am using Windows auth mode.

Any pointers as to what to do?

SQL Server service account don't have permission in the drive you are using to create database.Don't put you database in the root of drive. It would be problematic if you have UAC enabled, Create a folder in drive. Like D:\Data

Check this http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx

ERROR_ACCESS_DENIED
5 (0x5)
Access is denied.

Make sure that the SQL Server Service Account has read/Write permission to the folder where database file is kept or use the instance default folder of Data/Log where it will already have such permissions.