How to archive a whole database? I have a database that is not going to be used anymore but that needs to be kept around for a long time. I'd like to remove it from the production system. My goal is to save storage space when archiving that database. I need to preserve it at full fidelity, so a BCP export is not good enough. I also would not trust generated SQL scripts to preserve it fully.

Two options:

Take an uncompressed backup and compress it using 7-Zip.
Detach the database and compress the MDF and LDF files using 7-Zip.

Is option 2 safe? Which one is preferable? What are important considerations here?

You can save the index creation DDL statement and delete the indexes, to save more space before any archival path you choose. There's more advice on shrinking and fill factor here: http://www.brentozar.com/archive/2010/02/how-to-really-compress-your-sql-server-backups/

I've used option 2 to get better results than with the SQL built-in backup compression. Always start with DBCC CHECKDB ! Also take into consideration that your restore time will go up if you go on this path, as it takes a while to (de)compress with 7-Zip's best compression settings.


error in SSIS - [SQL query execution task] Error: The binding name of the results must be zero for the complete result set and the XML results I have an Execute SQL task that returns "Full result set". Despite that, the task fails. Please how can I fix it ?

[SQL query execution task] Error: The binding name of the results must be zero for the complete result set and the XML results.

Click on ResultSet in the Execute SQL Task, click Add. In the 'ResultSetName' column, enter the exact name of the columnname that you are retrieving or simply give it 0. In the 'variablename', select the variable you created to map the data returned.


group_concat in separate columns I have a players table

id  name
--  ----
1   jack
2   john
3   mike
4   pikachu

And I have a matches table where these players can be matched together.

id pair_id player_id
-- ------- ---------
 1       1         1
 2       1         2
 3       2         3
 4       2         4

So I tried something like select group_concat(name) group by pair_id
The end results is something like:

names
---------
Jack,John

But what I like to is to get players in their own columns like:

p1   p2
---- ----
Jack John

How can I do that?

Probably you do not want group_concat() at all, you want a join:

SELECT t1.name AS p1, t2.name AS p2
FROM
    players AS t1
INNER JOIN
    matches m1 ON t1.id = m1.player_id
INNER JOIN
    matches m2 ON m1.pair_id = m2.pair_id and m1.id < m2.id
INNER JOIN
    players t2 ON t2.id = m2.player_id

The m1.id < m2.id part ensures that each pair will be returned only once, with p1 being the one with smaller id in the matches table for a given pair_id.


Oracle 12C, cannot drop NOT NULL constraint on a DEFAULT ON NULL column In windows7 I Install oracle 12 C

Then I connect to database as sys user and create new user so:

CREATE USER C##USER
IDENTIFIED BY user_pass;

GRANT ALL PRIVILEGES TO C##USER;

Then I connect to database as C##USER and create table

CREATE TABLE autos (
    id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY UNIQUE,
    OWNER_NAME NVARCHAR2(50)
);

and when I am trying insert rows into table, I got problems:

INSERT INTO autos
(owner_name)
VALUES
('Tommy');

On this query, oracle returns: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column

When I connect as sys user, this error not occurred, this error happens only when I connect to database as new created user (in this case this is user: C##USER)

Can you tell me please what is wrong here?

P.S. This error returned both, in SQL Developer and in SQLPlus also.

Thanks

Seems to be a bug in Oracle 12.1.

Solution is simply:

SQL> purge recyclebin;


I wish to restore a gzipped tarball - what does mongodump --archive do? Howto do a mongorestore with --gzip? I created a backup with

mongodump -h $RANDOM_SECONDARY_SUP -u $BACKUP_USER -p $BACKUP_PASSWD --out /data/$BACKUP_USER/sup-repl/sup_$DUMPFILE
tar cvzf /data/$BACKUP_USER/dbt/st_$DUMPFILE.tar.gz /data/$BACKUP_USER/st-repl/st_$DUMPFILE

I tried to restore that with --gzip and receive erorr

mv st_$DUMPFILE.tar.gz test_restore.bson.gz
mongorestore --gzip test_restore.bson.gz
2016-09-08T18:05:14.975+0200    checking for collection data in tgdmead2_test_restore.bson.gz
2016-09-08T18:05:15.090+0200    restoring test.test_restore from tgdmead2_test_restore.bson.gz
2016-09-08T18:05:15.156+0200    Failed: test.test_restore: error restoring from test_restore.bson.gz: reading bson input: invalid BSONSize: 1635017060 bytes

In docs I read that the gzipped tarball should be created with --archive

What does this parameter do? Howto emulate that with tar and gz?

Which format does mongorestore expect with --gzip?

I think the problem you have is that the archive format created with --archive is not a tarball (and the docs don't say it is anywhere that I could find). Rather it is a custom packaging format which you can see the details of here. Based on a quick scan of the code, it looks like a lightweight format containing a series of headers, metadata which describes the raw BSON. Short of creating a standalone binary to create a compatible archive file, you can't do this manually.

If you do mongodump without archive, but with --gzip, it will actually gzip the individual files, and you can emulate that by doing the dump normally then gzipping each file in the folder separately. Those compressed files could then be restored theoretically with mongorestore --gzip.

Overall I would advise to just use --archive in the 3.2 tools and stay away from trying to recreate it manually, but the --gzip option is as close as it is going to get without a bunch of work.


Recover oracle 10g database from old data files I have an Oracle 10g database.

Unfortunately, our machine and system crashed and all the backups and Oracle archive logs, but we have old image copies of our data files and one of the control file (copy & paste)

How can I restore our database from these copies?

I tried to create a new database and replace the old files with new files, but I couldn't (I got message, we need to open the database with resetlogs, but we can't open it if we didn't make incomplete recovery)

Please help me

Try to "recover until SCN..." before doing "open resetlogs".

You'll need the online redo logs to perform this (not clear from your question whether you have them or not)


How to use FULLTEXT in mysql innoDB? Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance.

I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference of

FULLTEXT(title,body)

and

FULLTEXT(title),
FULLTEXT(body

Is there any effect on the search performance and the ranking of results.

For InnoDB,

FULLTEXT(title,body) -- useful only for MATCH(title, body) AGAINST ...

FULLTEXT(title) -- useful only for MATCH(title) AGAINST ...

So, if you are doing both types of MATCH, you need both indexes.


Restore Backup Fails - File 'Db' cannot be restored to '{...}.mdf'. Use WITH MOVE to identify a valid location for the file I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer.

RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak'

Any thoughts on what I can do to correct this?  It's looking for the mdf, so can I assume that the backup file I have is an incremental backup and not the full backup and that's why it's looking for the mdf?

I am not sure if 1) they have given all the data (the entire db) or 2) I have the right data - and I can do the restore with the right T-SQL commands - I just don't know how.

Help is appreciated.

Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 2
File 'Db' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 2
File 'DbDev_log' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 2
The operating system returned the error '21(failed to retrieve text for this error. Reason: 15105)' while attempting 'GetVolumeInformation' on 'D:\'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

RESTORE FileListOnly FROM DISK = N'C:\Backup\Backup.bak' shows me the following...

LogicalName PhysicalName    Type    FileGroupName   Size    MaxSize FileId  CreateLSN   DropLSN UniqueId    ReadOnlyLSN ReadWriteLSN    BackupSizeInBytes   SourceBlockSize FileGroupId LogGroupGUID    DifferentialBaseLSN DifferentialBaseGUID    IsReadOnly  IsPresent   TDEThumbprint
Db  D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db.mdf  D   PRIMARY 29622272    35184372080640  1   0   0   02925462-83CC-4222-8966-53229FA25B1C    0   0   29032448    512 1   NULL    269000000146900238  735C7F0E-F63D-4AA1-AC48-505A084AC00B    0   1   NULL
Db_log  D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db_log.LDF  L   NULL    40239104    2199023255552   2   0   0   71038B04-CBC9-4E4A-93AD-4E1268859CB2    0   0   0   512 0   NULL    0   00000000-0000-0000-0000-000000000000    0   1   NULL
DbDev_filestream    D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Db  S   PRIMARY_FILESTREAM  4691012 0   65537   18000000011800001   0   627C9AAA-97BD-4991-9C8C-90C400226A9F    0   0   4784128 512 2   NULL    269000000146900238  735C7F0E-F63D-4AA1-AC48-505A084AC00B    0   1   NULL

Take another look at the output from your first command. There is an error messag in it that points you to the fix.

Look for this section:

Use WITH MOVE to identify a valid location for the file.

Basically, the file path on your machine doesn't match the original machine. The MOVE option will let you fix that.

Here is an example of the RESTORE command using the MOVE option:

USE [master]
RESTORE DATABASE [AdventureWorks2008R2]
FROM  DISK = N'C:\SQL Backups\AdventureWorks2008R2.bak'
WITH  FILE = 1,
MOVE N'AdventureWorks2008R2_Data' TO N'C:\MyNewDataLocation\AdventureWorks2008R2_Data.mdf',
MOVE N'AdventureWorks2008R2_Log' TO N'C:\MyNewLogLocation\AdventureWorks2008R2_Log.ldf',
MOVE N'FileStreamDocuments2008R2' TO N'C:\MyNewFileStreamLocation\Documents2008R2',
NOUNLOAD,  REPLACE,  STATS = 1
GO

Hope this helps!


Group by range of years I've got a big table (~9M rows) and want to group the rows on a field containing the year. So far that's pretty easy:

// greatly simplified:
SELECT count(*), year FROM dataset GROUP BY year ORDER BY 2;

We defined some irregular time periods spanning multiple years:

<1945, 1946-1964, 1965-1974, 1975-1991, 1992-2005 and >2005

I've got no clue on how to group these results in the group by clause. I could make subquery's for every time period.

SELECT
  ( SELECT count(*) FROM dataset WHERE year <= 1945 AND ...... ) AS pre1945,
  ( ....) AS period2,
  ....
FROM dataset

But that feels not right and I'm wondering if it was possible to let Postgresql do it. Especially because the query is a strong simplification of the real query: it has multiple conditions, amongst them a ST_within clause spanning four tables. So choosing the subquery-approach results in a bloated query.

Is there a better way to create this result?

If you want the result as rows in instead of as columns as in @a_horse's answer then create the year ranges in a CTE and join the table to it

with years(year_range) as ( values
    (int4range(1900, 1945, '[]')),
    (int4range(1946, 1964, '[]')),
    (int4range(1965, 1974, '[]')),
    (int4range(1975, 1991, '[]')),
    (int4range(1992, 2005, '[]')),
    (int4range(2005, 2014, '[]'))
)
select year_range, count(*)
from
    dataset d
    left join
    years y on d.year <@ y.year_range
group by 1
order by 1

http://www.postgresql.org/docs/current/static/rangetypes.html


MAXDOP query for SQL Server 2012 I have a 8 core VM machine running SQL Server 2012. The current setting of MAXDOP is set as 5. There is recommendation from my colleagues to change the MAXDOP to 5 for better performance as there has been complaints on the same..

I might be very low on info in this question. But just curious, wont setting MAXDOP to 0 cause to degrade since all processors might not be needed for all the queries right? And is it right to set it as '0' rigth away?

Inputs please

This totally depends upon the load on the system, if its OLAP, OLTP or somewhere in between. What we have in our system is , generally higher MAXDOP for OLAP and lower, i.e 0 and 1 set for OLTP workload systems.

But above depends totally on the queries as well along with hardware configuration in terms of logical processors, hyper threading is enabled and NUMA node set up.

A good read on better understanding would be Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Also, what has helped me in the past is following the codes from the discussion on What is a good, repeatable way to calculate MAXDOP on SQL Server?, the one from @Kin and @Max , really helps you getting a start as what can be used initially, thereafter you need to monitor and conclude what setting suits best for you're environment.


A form including content from two tables I am working to create a database of all the places or administrative entities of one country.

Most work will be about adding new districts. I have to detail which region every district belongs to.

Two tables, one for 1/ regions 2/ districts.
Every region has its ID and Name.
Every district has a Name, and a column which refers to ID_Region (the one it belongs to).
The two tables are linked. ID_Region is primary key.

However, I am using a well-known open-source Database management system, (OpenOffice). It seems that it cannot allow me to create forms in which ID_Region could be automatically modified in Name_Region. This is all the more important since there are hundreds of regions.

Do you know if this is possible on any open-source DBMS?
What software should I use instead?

You probably got that I'm a database newbie. I do not know much about SQL.
Thanks for your replies. Feel free to redirect me to the right forum if I am on the wrong one.

Table 1: "Regions"

Column 1: idRegions
Column 2: RegionType (refers to another Table, not necessary here)
Column 3: Name
Column 4: Abbreviation

Table 2: "Districts"

Column 1: idDistricts
Column 2: Name
Column 3: Region (tied to Regions.idRegions)

I can create a form in which I can view and add Districts, but it seems like Districts.Region (an ID) can't "redirect" to Region.Name, which I would like to (no matter how: a drop-down list, or an additional text box).

Should this be possible, it would of course allow other types of join to show names and not id (such as RegionType, or any kind of join whatsoever).

You might think of making Region.Name the Primary key instead of using IDs, however, some Regions are called the same way (all the more when I will add the towns, many Towns' Districts are homonyms).

The way you have the database setup seems fine.  I am not sure this is actually a DB admin question as much as it is a front end question.

MySQL or MariaDB would be a fantastic DB to use this on.  You can also use MySQL Workbench, which is free, to manage the DB if you want.  I think for something this simple, I would just use the command line MySQL client.

If this is truly a front end question about how to make a form, I would recommend using the PDO functionality in PHP.

Once you decide this for sure, just ask about the proper queries to write and how to use PDO, if you need to.  (That might be more appropriate for Stack Overflow, though)


What is the difference between concurrency control in operating systems and in trasactional databases? Recently I'm learning concurrency control techniques in transactional databases. However, I am so confused about the differences between concurrency control in operating systems and in transactional databases.
Within my understanding, the concurrency control techniques introduced in database literature can be used in a multithreading program, whose threads share some variables with each other, and vice versa. The techniques used in multithreading programs for sharing variables between threads can also be used in databases for concurrency control.
Why do we bother to introduce this differently in database literature and in operating systems?

Databases have two concurrency requirements. One is the very short term physical management of memory blocks as they are referenced. These are known as latches in the DB world and can be implemented using mutexes and the like. The concern here is for the stability of a block of memory while a worker thread is accessing it.
The second controls the validity and isolation of the client data held within the DB. They control access to logical things (tuples, tables etc.) rather than physical hardware-related things (memory blocks).  These are refered to as locks. They last as long as the client transaction lasts, which can be an arbitrarily long time. The locks are typically held in a separate structure to the one which holds the object that is locked. Consequently the thing locked need not be in memory for the whole duration that the lock is held. For example, a page may be read in, a row inserted and a lock taken on it to ensure isolation. If the page containing the new row is evicted the lock would remain in effect. Indeed, the things locked may not exist when the lock is taken! A range lock protects key values from a lower bound to an upper bound. Neither of these values need be present in the data when the lock is acquired, nor must There be values that fall within the range. Nevertheless the lock will come into effect and allow isolation of clients' work.
The introductory sections of Goetz Graefe's "Survey of B-Tree Locking Techniques" covers these ideas nicely.
An OS deals with the first of these only (in my limited expertise). Any extension to disk is hidden behind virtual memory and the page file. In contrast a DBMS explicitly handles RAM and disk residence to optimise for its specific use-case. Different requirements lead to different treatments between OS and DBMS.


Same Parameter in MySQL stored procedure How do I cache dynamic query from a stored procedure?

Right now I have created my stored procedure like this :

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  ....
)
BEGIN

   SET @sqlQuery = CONCAT("SELECT Name From Users WHERE UserID > ", UserID, " AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ", UserID, " ) Order by Name")

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

END;

But this kind of query does not cached. So, every time it gets more time to execute/process query.

Now I have tried some other method like this:

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  ....
)
BEGIN
   SET @UserID = UserID;

   SET @sqlQuery = "SELECT Name From Users WHERE UserID > ? AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = ? ) Order by Name";

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt @UserID, @UserID; -- here i passed same variable twice.
   DEALLOCATE PREPARE stmt;
END;

In the above case I have to pass same variable (@UserID) twice, because it is used 2 times in my query. but this job is very hectic in long or complex query. so, how do I avoid this?

One another method I tried as follows:

CREATE PROCEDURE usp_MyProcedure (
  IN UserID INT,
  ....
)
BEGIN
   SET @UserID = UserID;

   SET @sqlQuery = "SELECT Name From Users WHERE UserID > @UserID AND UserID  IN ( SELECT UserID FROM OtherTable WHERE UserID = @UserID ) Order by Name";

   PREPARE stmt FROM @sqlQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
END;

But above query again does not cached. so, execution time is very long. And this type of variable declared as session global variable has may be conflict with another stored procedure's variable. Because I have call stored procedure within this stored procedure and variable name should be same in another stored procedure.

So, let me know what is the best solution for the same.

Thanks.

First of all, I don't think it's a good idea to use dynamic sql in the case when you have nothing really 'dynamic'.

If you still want to do it, it seems that you are missing an important point about prepared statements. You are preparing, executing, and then deallocating prepared statement in one procedure; thus, next time you call your procedure, you are repeating all the steps again. Take a look on https://stackoverflow.com/questions/11605783/mysql-permanently-prepared-statements , there is an example on how to get benefits from prepared statements inside stored procedure (briefly, you do not deallocate statement at the end, instead of this, you check whether a variable associated with the statement is not null and call prepare only once. That expands lifetime of the prepared statement to lifetime of session effectively caching execution plan).


Can I create a DOMAIN on a range of years I have to create a custom domain that is an interval between two years, but i actually need both years.
For example:

'2013/2014'
'2015/2016'

Is there a way to pack both years inside a single domain, or have i to separate them in two attributes of the relation?

A domain is a constraint. It doesn't hold any values whatsoever. It can only restrict a type to exclude values. If you need to store a range, use the range types.

As a side note, you can in fact, create a domain on a range type. In the event you want all of your ranges to start on a Wednesday

CREATE DOMAIN foo AS daterange
  CHECK ( EXTRACT(dow FROM lower(VALUE)) = 4 );

SELECT daterange('2018-01-02'::date,'2018-02-05'::date);

SELECT daterange('2018-01-02'::date,'2018-02-05'::date)::foo;
ERROR:  value for domain foo violates check constraint "foo_check"

Moreover, you can also ensure the range is always one year:

CREATE DOMAIN bar AS daterange
  CHECK (
    (lower(VALUE)::timestamp without time zone - upper(VALUE)::timestamp without time zone) = '-365 days'
  );

But if you're going to do that -- make it such that your range is always one year and a fixed size, I suggest you just store the year that you start as ::smallint and be done with it.


On the query planners's reasons for choosing merge joins vs nested loops I encountered a real-life version of this simplified sample on how the execution planner deals with a join. The schema and data is:

CREATE TABLE Fact (
    id INT not null,
    value1 INT not null,
    CONSTRAINT PK_Fact PRIMARY KEY CLUSTERED (id)
    );

CREATE TABLE Property (
    id INT not null,
    value2 INT not null,
    CONSTRAINT PK_Property PRIMARY KEY CLUSTERED (id)
);

DECLARE @batchsize INT = 10000;
DECLARE @i INT = 1;

WHILE @i < @batchsize
BEGIN
    INSERT INTO Fact VALUES (@i, @i % 1000);
    INSERT INTO Property VALUES (@i, @i);
    SET @i = @i + 1;
END

The we execute this query:

SELECT *
FROM Fact f
JOIN Property p ON f.id = p.id
WHERE f.value1 = 1

The execution plan makes this a nested loop:

If we reduce the the possible value count in Value1 to a hundred, by modifying a re-creating the schema and data like so:

WHILE @i < @batchsize
BEGIN
    INSERT INTO Fact VALUES (@i, @i % 100);
    INSERT INTO Property VALUES (@i, @i);
    SET @i = @i + 1;
END

... and re-run the query, it becomes a merge:

I have a real-life case where I believe the execution planner makes the wrong choice, as I can significantly boost performance by first selecting all the ids alone, without a join, and then fetch the actual rows with a second query.

I'm wondering:

Are there additional ways to ask Sql Server as to why exactly he chooses one plan or the other in cases like this? The general notion of using the number of possible of values is plausible, but can more exact information be retrieved? In particular, is the number of possible values the only information going in to the execution planner's reasoning, or is it more sophisticated than that? Does the average row size of the joined table matter?
Is there a way to hint Sql Server to do one or the other in cases where testing shows that Sql Server makes a bad guess?

In your sample case SQL Server made very good choice.
Here is what it was actually doing:
- In the first set of data it extracts 10 rows from Fact table based on the value column. Then it extracts 10 corresponding IDs from Property table using kind of SEEK operation for each ID.
- In the second scenario, when there are 100 matching rows in Fact table SQL decided not to do SEEK operation, but use SCAN instead, which is much cheaper from the I/O perspective.

Yes, you can use hints for your queries (https://msdn.microsoft.com/en-us/library/ms181714.aspx):

OPTION (LOOP JOIN)
OPTION (MERGE JOIN)

Try both of them for both of your data sets to see the difference in query cost and amount of I/O using SET STATISTICS IO ON.

For instance, when you force LOOP JOIN for the second data set I/O for Property table jumps 24 to 215 reads.
So, be VERY CAREFUL using any kind of these hints.


JOIN type for MS-Access report query I have 7 tables, 6 of which are in a one-to-one relationship with a table named SLD joined with SLD_ID. SLD has 17 records and I need them all displayed regardless of matches in other tables.

Which JOIN type should I use to do this?

SELECT SLD.SLD_Number,
       SLD.Date_Received,
       SLD.Time_Received,
       [Payment table].Date_Received,
       IIf(Payment_Status = 'NOT PAID', DateDiff("d",[Invoice table].Date_Received,Date())) AS Expr1,
       IIf(Payment_Status='PENDING', DateDiff("d",[Invoice table].Date_Received,Date())) AS Expr2,
       DateDiff("d",[Invoice table].Date_Received,
       [Payment table].Date_To_Payment) AS Expr3,
       DateDiff("d",[Order table].Date_Quotes_Requested,SLD.Date_Received) AS Expr4,
       DateDiff("d",[Order table].Date_Of_Order_Number,SLD.Date_Received) AS Expr5,
       IIf(Payment_Status = 'PAID', DateDiff("d",[Invoice table].Date_Received, [Payment table].Date_Authorised_Paid)) AS Expr6,
       IIf([Invoice table].Date_Received=NULL,'TRUE','FALSE') AS Expr7,
       Format(SLD.Date_Received,"mmm-yyyy") AS [Month Of SLD]
FROM   (([Payment table]
INNER JOIN [Invoice table]
ON     [Payment table].SLD_ID = [Invoice table].SLD_ID)
INNER JOIN [Order table]
ON     [Payment table].SLD_ID = [Order table].SLD_ID)
LEFT JOIN SLD
ON     [Payment table].SLD_ID = SLD.SLD_ID;

RIGHT JOIN is bringing up this error:

  Join expression not supported. (Error 3296)

This seems to have worked:

SELECT SLD.SLD_Number, SLD.Date_Received, SLD.Time_Received, [Payment
table].Date_Received, IIf(Payment_Status='NOT PAID',DateDiff("d",[Invoice
table].Date_Received,Date())) AS Expr1,
IIf(Payment_Status='PENDING',DateDiff("d",[Invoice
table].Date_Received,Date())) AS Expr2, DateDiff("d",[Invoice
table].Date_Received,[Payment table].Date_To_Payment) AS Expr3,
DateDiff("d",[Order table].Date_Quotes_Requested,SLD.Date_Received) AS
Expr4, DateDiff("d",[Order table].Date_Of_Order_Number,SLD.Date_Received) AS
Expr5, IIf(Payment_Status='PAID',DateDiff("d",[Invoice table].Date_Received,
[Payment table].Date_Authorised_Paid)) AS Expr6, IIf([Invoice
table].Date_Received=NULL,'True','No') AS Expr7,
Format(SLD.Date_Received,"mmm-yyyy") AS [Month Of SLD]
FROM (([Payment table] RIGHT OUTER JOIN SLD ON SLD.SLD_ID = [Payment
table].SLD_ID) LEFT OUTER JOIN [Order table] ON [Payment table].SLD_ID =
[Order table].SLD_ID) LEFT OUTER JOIN [Invoice table] ON [Invoice
table].SLD_ID = [Payment table].SLD_ID;


Sorting by month by chronological occurrence when the year doesn't matter? Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter (such as upcoming birthdays), how can the results be sorted by month in the correct chronological order for the selected time range?

For example, if the query grabs all dates between November 20th and February 20th regardless of year, it should sort them in the order:

Nov -> Dec -> Jan -> Feb

But a regular sort by month actually produces:

Jan -> Feb -> Nov -> Dec

Or an inverted sort produces:

Dec -> Nov -> Feb -> Jan

both of which are wrong.

Here's a sample query with a three day range at the end of the year:

SELECT EventDate
   FROM Events
   WHERE TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" + INTERVAL 3 DAY)
      > TIMESTAMPDIFF(YEAR, EventDate, "2012-12-28" - INTERVAL 1 DAY)
   ORDER BY DATE_FORMAT(EventDate, "%m-%d") DESC

It produces:

2012-01-01
2008-01-01
2008-01-01
1987-01-02
1994-12-28

Where it should produce:

1994-12-28
2012-01-01
2008-01-01
2008-01-01
1987-01-02

Is there a way to get it to sort by month and day in a "going forward from range start" order?

I searched here and on SO but didn't find an answer that dealt with both year end wrapping of months and a scenario where the year in the date field isn't relevant for sorting.

Update: The range should always sort forward, so if the range is Feb to Nov, it does a standard sort of Feb - > Mar -> Apr .... If the range is Nov to Feb, it sorts Nov -> Dec -> Jan ....

Although I don't anticipate that being an issue since I don't need to query more than a 3 month window.

Test data

create table dates ( d date);
insert dates values ('2012-01-01')
,('2008-01-01')
,('2008-02-01')
,('1987-01-02')
,('1994-12-28')
,('1987-05-02')
,('1994-11-28')

Query

select d, DATE_FORMAT(d, "%m-%d")
from dates
join (select 11 ms, 2 me) params
where ms<=me and MONTH(d) between ms and me
  or ms>me and (MONTH(d) >=ms or MONTH(d) <= me)
order by case when MONTH(d) < ms then 100+MONTH(d) else MONTH(d) end


How to enable query store on the SSMS object browser with Sql Azure? I am following this guide: Monitoring Performance By Using the Query Store

I am using SSMS 2016 CTP3.2 and Sql Azure V12
I have enabled the query store on the database properties.
I can use the query performance insight on the azure portal.

But when I open the azure database on SMSS 2016 CTP3.2 and expand the database tree, the Query Store / Regressed Queries or any other options are available.

I want to be able to take the most IO consuming queries and analyze the execution plan.

Anything else I have to enable?

The relevant is_query_store_on column in sys.databases shows 0, but the database properties says it is enabled. I executed ALTER DATABASE <DB> SET QUERY_STORE = ON; but is_query_store_on still stays at 0.

The following query:

SELECT
    actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Shows actual_state = 2;

This fixed my problem:

Disabling the query store by command:

ALTER DATABASE <DBNAME> SET QUERY_STORE = OFF;

Then enabling it again fixed the problem.

ALTER DATABASE <DBNAME> SET QUERY_STORE = ON;


SQL Server is caching query plans with the same query when using parameterized SQL I'm using parametrized SQL queries in SQL Server 2012 SP1.

Refer to sys.dm_exec_cached_plans and sys.dm_exec_query_stats, it seems that the optimizer creates same plans multiple times instead reusing one already existed.

The server have lots of cached plans with the same query_hash and query_plan_hash, (exactly 4700 plans at the moment and increasing).

My questions are:

Is this situation normal?
Why optimizer create a new plan again and again which is already exists?
Is there a good way to force the optimizer reuse query plans in terms of parametrized SQL?

You've got a few different questions in here:

Q: Why optimizer create a new plan again and again which is already exists?

These attributes all affect plan reuse:

Different databases each get their own plans
Different parameter lengths get their own plans (especially with this NHibernate bug, but similar issues can occur with other ORMs as well)
Different connection settings like ANSI NULL can get their own plans
Different spacing or comments can get their own plans (like if your code is dynamically adding comments to indicate where the query came from)

Q: Is there a good way to force the optimizer reuse query plans in terms of parameterized SQL?

Yes - think about the above things that will impact plan reuse. Obviously if you have a Software-as-a-Service type model where every client gets their own database, you can't really help things there, but you can help the other two factors. Be consistent with your application's connection settings, don't inject different spacing or comments dynamically into queries, and make sure your datatypes in the application match the datatypes in the database.

Q: Is this situation normal?

It's not unusual to see out in the field, especially with older NHibernate/EF versions that used different parameter lengths based on whatever parameter you were passing in (as discussed in that link.)

The performance symptoms are higher CPU use (due to increased query compilations) and higher plan cache memory use, but they're not usually the largest problem on a SQL Server.


View Postgresql Memory Usage I'm running Postgresql on a Ubuntu server and need to be able to monitor its memory usage.  Currently I have scripts running in a one-minute cron job that monitor/log various statistics and need to also monitor/log the current memory usage of Postgresql.  I've searched around and haven't been able to find much, other than the fact that Postgresql makes good use of shared memory so the values given by a program such as 'top' aren't accurate.

How can I monitor what the total memory usage of Postgresql is at any given time?  This data will later be used to create graphs for analysis.

Memory usage in linux in general and for Postgres in particular is a pretty complex subject, a good starting point is Bruce Momjians blog covering the subject and the usage of smem. It is well worth following the links in Chris Seibenmanns blog on the subject.


Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100 I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005).

The subscription databases are also SQL Server 2008 R2, however the destination databases are set to compatibility level 100 and replication is working fine.

If I change the compatibility level for the Published databases, will it affect replication in any way, or will it just be a case of reinitializing all the subscriptions and restarting replication?

I suspect that changing the published database compatibility level may change how the replication stored procedures function slightly, but I'm not 100% sure.

Is this the case?

You can follow below steps :

Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously.
Stop user activity on published tables.
Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
Execute sp_replcmds to verify that all transactions have been processed. The result set from this procedure should be empty.
Execute sp_replflush to close the connection from sp_replcmds.
Change the compatibility level of the database.
Start the Log Reader Agent.


sqloledb vs sqlncli - Performance difference or just feature set? I've been running through some Classic ASP code recently and was wondering if there was a performance difference between connecting using:

Provider=SQLOLEDB;server=NTSERVERNAME\SQLEXPRESS;database=DBNAME;uid=USER;pwd=PASSWORD;

Or

Provider=SQLNCLI;server=NTSERVERNAME\SQLEXPRESS;database=DBNAME;uid=USER;pwd=PASSWORD;

I seem to recall that there is a performance impact with some provider options (mostly from the days of SQL Server 2000 and earlier), but I've not been able to find if that is the case with these two providers.

The only thing I've been able to find are articles explaining that the latter will let me use some of the new features in SQL Server 2005 (XML data type, etc.) and some other feature support issues - but nothing about performance.

I talked to Erland Sommarskog about this and here is his response.

I attended a presentation as PASS 2005 about SQL Native Client. The
then-PM for SQL Server Client said that for ODBC they had achieved a
20% perf improvement, but for OLE DB, it was about even.

But since SQLNCLI provides more features, that is probably the provider
to use. Then again, with old ADO a lot of the features are unavailable
anyway.

HTH


How to set up mysql docker container with existing ib* files? Related to a previous issue, I'm trying to recover some data from existing ib* files from a crashed server. The database version is 5.1.69, so quite old. One of the responses was to install MySQL 5.5, because it should still be able to import 5.1, but even that is too old on current systems, and one just runs into ever deeper compatibility issues.
“NBK” in the same issue suggested in a comment to use a docker file with an older version of MySQL. I decided to try this approach. I was able to install docker and pull the vsamov/mysql-5.1.73 image, but I'm stuck now at how to get the ib* files into the docker container.
I think I need the image to run so that it has a container ID, but if it's running, then the ib* files are locked, so I'm not sure how to proceed. If anyone has experience in this, or can provide a reference, it would be much appreciated.

ib* locked is another process already is running on them.
Restore the entire datadir, not just the ib* data files, to a directory.
Use the volume mount option of the container with docker/podman -v localdir:/var/lib/mysql option on the startup to pass that data directory to the container.


How can I track database dependencies? As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods for identifying database dependencies, both within the SQL environment, and maybe onward into things like SSIS?

I've worked places where fairly brutal options have been taken such as:

Drop first, ask questions later (can kill a data warehouse build if it tries to extract a table that no longer exists)
Remove permissions first, and wait for the errors to be reported (can cause silent bugs, if the failiure isn't handled correctly)

I appreciate that SQL Server comes with tools for tracking dependencies within that instance, but these seem to struggle if you have databases on different instances. Are there options that make it easier to query dependencies, maybe answering questions like "Where is this column used?" with answers like "Over on this other server in this stored procedure" or "Over in this SSIS package"?

There is no easy way to do this.  Triggers don't work, as if you select from a table no trigger is fired.  The best way that I've found to do this is to have the developers track what they use.  When something is going to be dropped check with all the dev teams, and after everyone signs off, rename the object.  Then is nothing breaks for a month or to, the object can be safely dropped.


How to combine UNION and INNER JOIN? I'm fetching the grandparent of all nodes in a tree as follows:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P
INNER JOIN product_groups GP ON P.parent = GP.id

There is no entry with id=0 in the table, but some where parent=0, indicating roots of the trees. Those rows are not fetched by this query, as there's no grandparent to join them with. I'd like those rows returned, with 0 (or NULL) for both parent and gp_id.

I was planning to UNION the second table with (0,0), but can't figure out the syntax. How to do this?

Just do a LEFT JOIN instead:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P
LEFT JOIN product_groups GP ON P.parent = GP.id

That will return all rows from P and any non-matching rows in GP will have NULL values.


What is proper database design for a drupal table with an order field? I am adding a custom widget to an instance of Drupal 6.x & MySQL 5.5 and came across a problem with updating rows.

I have a table of recipe ingredients where multiple ingredients are tied to a single recipe by node id (nid) & version id (vid).

The primary key is vid, nid & order.

The vid & nid are related to the recipe nid & vid fields.

The table schema is:

+-----------------+------------------+
| Field           | Type             |
+-----------------+------------------+
| vid             | int(10) unsigned |
| nid             | int(10) unsigned |
| order           | int(10) unsigned |
| name            | varchar(255)     |
| unit_of_measure | varchar(32)      |
| quantity        | int(10) unsigned |
+-----------------+------------------+

The problem comes when attempting to reorder the ingredients.

For instance:

+-----+-----+-------+---------+-------------------+----------+
| vid | nid | order |  name   |  unit_of_measure  | quantity |
|  5  |  1  |   1   | Chicken |        Lb         |    1     |
|  5  |  1  |   2   |  Rice   |        Cup        |    2     |
|  5  |  1  |   3   |  Thyme  |        Tbsp       |    3     |
+-----+-----+-------+---------+-------------------+----------+

I want to move Thyme to the top of the list but I can't change the order for Thyme to 1 since that primay key already exists (Chicken).
I can't move Chicken down to order 2 because Rice is already there, etc...

My position is that we should add a unique auto-incrementing int field that will be the sole primary key.  Which will enable us to reorder the rows without incident with the possibility that two rows might end up with the same nid, vid, & order.

My coworkers position was that to add a unique auto-increment int field is bad design because there should never be two different rows that have the same vid, nid & order.  But following this belief there are two ways to implement a reorder of the rows

Update each row's order with some large number (ie- 1001, 1002, 1003) so that the original order is no longer conflicting, then update each row with the correct order values (1, 2, 3).
Delete each row that has the same nid & vid, then insert all the rows again in the correct order.

From the database's perspective, what is the correct approach?

The problem is similar to this (SO) question:
How to swap values of two rows in mysql without violating unique constraint?

Unfortunately, the answer is the same. Due to how MySQL checks UNIQUE (and primary and foreign key) constraints, i.e. row by row and not at the end of statements or at the end of transactions, swapping or rearranging values that are part of unique constraints, cannot be done easily.

I would go with your 1st suggestion:

1. Update each row's order with some large number (i.e. 1001, 1002, 1003) so that the original order is no longer conflicting, then update each row with the correct order values (1, 2, 3).

This way you can have the minimal (2) statements required to do the update and any other connection that reads the table between the 2 statements will still read a consistent state of values.


Python code in T-SQL environment gives "access a socket in a way forbidden by its access permissions" Error Help on the following please.

I have this code Python Code

  import requests
  r = requests.get("https://www.google.com")
  r.status_code
  print(r.status_code)
  print("Python Rawks!!!!!!")

which runs fine in command line mode and returns the 200 status code.

The same code in T-SQL query window fails with OSError: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissions

execute sp_execute_external_script@language = N'Python',@script = N'
          import requests
          r = requests.get("https://www.google.com")
          r.status_code
          print(r.status_code)
          print("Python Rawks!!!!!!")
'

  Error in execution.  Check the output for more information.
  Traceback (most recent call last):
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\requests\packages\urllib3\connection.py", line 141, in _new_conn    (self.host, self.port), self.timeout, **extra_kw)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\requests\packages\urllib3\util\connection.py", line 83, in create_connection    raise err
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\requests\packages\urllib3\util\connection.py", line 73, in create_connection    sock.connect(sa)

  OSError: [WinError 10013] An attempt was made to access a socket in a way forbidden by its access permissions
  During handling of the above exception, another exception occurred:

Open the Windows Firewall and look for a rule called "Block network access for R local user accounts in SQL Server instance MSSQLSERVER" (assuming you have a default instance).  This Firewall rule blocks network access from SQL Server external scripts.  You can disable the rule if you want to allow network access.


Checking if entities match an id in on relation where it could show up in either of two columns The problem
I'm trying to make a page for a website (with PostgreSQL database) that allows the owner of a group to add new members from their friends list.
Given the owner_id of a Group its group_id I need to retrieve only the user_ids from users who are both friends with the owner, and not in the group.
The relations being queried
I need to use the following relations

UserFriend: Stores two user_ids to connect those two users from the User relation to each other as friend.
UserGroup: Stores a user_id and group_id to connect the Group relation to its members in the User relation.

The two relations are described as follows:
UserFriend
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 user_id_a | integer |           | not null |
 user_id_b | integer |           | not null |

UserGroup
  Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
 user_id  | integer |           | not null |
 group_id | integer |           | not null |

My attempt so far with example data
For example, given the data below, I want to find all users that are friend with user 3, and not in group 3.
SELECT user_id_a, user_id_b FROM UserGroup UG, UserFriend UF
    WHERE (UF.user_id_a = 3 AND UF.user_id_b != UG.user_id)
       OR (UF.user_id_b = 3 AND UF.user_id_a != UG.user_id)
       AND UG.group_id = 3 GROUP BY user_id_a, user_id_b;

Expected Result
 user_id_a | user_id_b
-----------+-----------
         1 |         3

Even better if it could just show something like
 possible_members
------------------
                3

Actual Result
 user_id_a | user_id_b
-----------+-----------
         1 |         3
         3 |         2
         3 |         4

Example data
UserFriend
 user_id_a | user_id_b
-----------+-----------
         1 |         2
         1 |         3
         1 |         4
         3 |         4
         3 |         2

UserGroup
 user_id | group_id
---------+----------
       1 |        1
       2 |        1
       3 |        1
       4 |        1
       1 |        2
       2 |        3
       3 |        3
       4 |        3

Analysis
I'm fairly certain that the issue has something to do with the way I'm handling the fact that the user_id I'm trying to match could be in either column of UserFriend, but I'm not sure how to fix it. I've also tried using JOINs without any luck.

Use set operations. If the user is 42, and the group is 1001, that would be
(SELECT user_id_a FROM userfriend
 WHERE user_id_b = 42
    UNION
 SELECT user_id_b FROM userfriend
 WHERE user_id_a = 42
)
   EXCEPT
SELECT user_id FROM usergroup
WHERE group_id = 1001;

UNION creates a result set that is the union of two query results and removes duplicate rows. EXCEPT returns all result rows from the left side that do not occur on the right side.


how to convert this varchar to datetime2(7) format? Can any one help how to convert '20200309150920' string to Datetime like 2020-03-09 15:09:20.

There may be a better way, but you can do this by concatenating it into the proper form.

DECLARE @TimeString char(20) = '20200309150920'

SELECT CONVERT(datetime2(7),
    CONCAT(
        SUBSTRING(@TimeString,1,8)
       , ' '
       , SUBSTRING(@TimeString,9,2)
       , ':'
       , SUBSTRING(@TimeString,11,2)
       , ':'
       , SUBSTRING(@TimeString,13,2)
        )
    )


Selection of Indexes in SQL Query I have a table with following 3 fields

1) Id      bigint
2) Name    varchar(100)
2) Country varchar(50)
3) State   varchar(50)
4) City    varchar(50)

In a country there are no. of states and in a state there are no. of city

I have two queries

a) If I want to know name of persons who are living in a city='Nagpur'

should I use index only on column city
should I use composite index on city,State & Country
should I use composite index on country, state & city

b) If I know that person with ID 1 to 100 are in Nagpur, for such case

should I create index on ID field
should I create index on City field

If you will suggest to create index on id field that reason will be

Due to it is a bigint and bigint occupy only 4 bytes
Id field is unique so it has more selectivity, so even if output of both query will be same Index on Id will give faster respnose.

As Oleg mentions, you really should normalize.  For your scenarios, though:

A - I would use an index on City INCLUDE ([name]) - you don't need to sort by name as well  but you do want that field at the leaf level of the index.

B - I'm not sure what you are asking here.  If you have an auto-incrementing ID then that should be your clustered index.

There's a lot of reasons to use an auto-incrementing ID as your clustered key.  I won't go in depth on these (there are a ton of resources, and Kim Tripp is a really good place to start):

Will only insert new records at the end of the index
Narrow (since it will be a part of every other index)
Unique (keeps it narrow since non-unique indexes need a "uniqueifier" additional int added)
Non-nullable


Log file is about to be larger than disk, query still executing I have multiple databases with multiple millions of rows. I am trying to delete millions of those rows but am about to run into a problem.

My log files are getting huge and I am about to run out of disk space.

Database A has an .mdf file that is 10gb, and so far it's log file has grown to be over 100gb. It has been running the query for over 3 hours and I have no idea how much longer it will continue to run or how much larger that log file will grow.

What can I do about this? What happens to my query (that is still running!) if the log file consumes the entire disk?

I have a backup (shy the last 4 hours) of all the databases I am working with.

Any suggestions?

If you run out of space the Query will rollback. You need to use transactions and delete the rows in batches and depending on what type of recovery model the database is in you will need to do LOG backups or Checkpoints.

Here is a stored procedure i built to remove milions of rows from an AX database in a safe way. Note that if your database is using a DPM system or something like that you will have to figure out someway to handle the LOG backups. Feel free to use it and edit it as you please.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Daniel Björk
-- Create date: 2017-07-27
-- Description: Delete rows in batches
-- =============================================
CREATE PROCEDURE csp_DeleteINVENTSUMLOGTTS
    -- Input parameters
    @DeleteOlderThanDays INT,
    @MaxDeletedRows INT = -1,
    @MaxTimeMinutes INT = -1,
    @BatchSize INT = 25000,
    @LogLevel INT = 1 -- 0 = OFF, 1 = Only Total Values, 2 = Verbose
AS
BEGIN
    SET DEADLOCK_PRIORITY LOW;

    -- Logical Default Values
    DECLARE @StartMessage AS NVARCHAR(MAX);
    DECLARE @StartTime DATETIME = GETUTCDATE();
    DECLARE @DeletedRows INT = 1;
    DECLARE @TotalDeletedRows INT = 0;
    DECLARE @Error int = 0
    DECLARE @ErrorMessage NVARCHAR(max)

    -- Print startup parameters
    IF (@LogLevel >= 1)
    BEGIN
        SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)

        SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + 'Parameters:'  + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@DeleteOlderThanDays = ' + ISNULL(CAST(@DeleteOlderThanDays AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@DataAreaIdParm = ' + ISNULL(CAST(@DataAreaIdParm AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@MaxDeletedRows = ' + ISNULL(CAST(@MaxDeletedRows AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@MaxTimeMinutes = ' + ISNULL(CAST(@MaxTimeMinutes AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@BatchSize = ' + ISNULL(CAST(@BatchSize AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)
        SET @StartMessage = @StartMessage + '@LogLevel = ' + ISNULL(CAST(@LogLevel AS NVARCHAR(32)), 'NULL') + CHAR(13) + CHAR(10)

        PRINT @StartMessage
    END

    -- Validate input parameters
    IF (@DeleteOlderThanDays < 0)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @DeleteOlderThanDays' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END
    IF (@MaxDeletedRows < -1)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @DeleteOlderThanDays' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END
    IF (@MaxTimeMinutes < -1)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @MaxTimeMinutes' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END
    IF (@BatchSize <= 0)
    BEGIN
        SET @ErrorMessage = 'Invalid value of @BatchSize' + CHAR(13) + CHAR(10) + ' '
        RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
        SET @Error = @@ERROR
    END

    -- Delete Logic
    WHILE (@DeletedRows > 0
            AND (DATEADD(Minute, @StartTime, @MaxTimeMinutes) > GETUTCDATE() OR @MaxTimeMinutes = -1)
            AND (@MaxDeletedRows > @TotalDeletedRows OR @MaxDeletedRows = -1))
      BEGIN
       BEGIN TRANSACTION

        -- Delete from InventSumLogTTS
        DELETE TOP (@BatchSize)
        FROM [dbo].InventSumLogTTS
        WHERE
        DATEDIFF(DAY, @StartTime, DATEADD(DAY,@DeleteOlderThanDays, DATESTATUS)) < 1

        -- Compute totals
        SET @DeletedRows = @@ROWCOUNT;
        SET @TotalDeletedRows = @TotalDeletedRows + @DeletedRows

        IF (@LogLevel >= 2)
        BEGIN
            PRINT 'Rows deleted so far: ' + CAST(@TotalDeletedRows AS NVARCHAR(32))
            PRINT 'Total runtime in sec so far: ' + CAST(DATEDIFF(Second, @StartTime, GETUTCDATE()) AS NVARCHAR(32))
        END

       COMMIT TRANSACTION

      -- Only needed if you are using Recovery Model Simple or you have a small log disk that might fill up
      -- CHECKPOINT;    -- if simple
      -- BACKUP LOG ... -- if full
    END

    IF (@LogLevel >= 1)
    BEGIN
        PRINT 'Total rows deleted: ' + CAST(@TotalDeletedRows AS NVARCHAR(32))
        PRINT 'Total time in sec: ' + CAST(DATEDIFF(Second, @StartTime, GETUTCDATE()) AS NVARCHAR(32))
    END
END
GO


Primary key for link/junction table with time-dependent data I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change.

As an minimal example, suppose I want to list the leaders of various countries in a database that tracks many politicians, including those in positions other than 'leader'. I can create a table of politicians, and a table of countries, like so:

CREATE TABLE politician (
  id integer unsigned NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE country (
  id integer unsigned NOT NULL,
  name varchar(45) NOT NULL,
  PRIMARY KEY (id)
);

Now, if every country had only a single leader forever, I could just link these with a table such as:

CREATE TABLE country_leader (
  countryid integer unsigned NOT NULL,
  leaderid integer unsigned NOT NULL,
  PRIMARY KEY (countryid, leaderid),
  KEY fk_countryleader_country_idx (countryid),
  KEY fk_countryleader_politician_idx (leaderid),
  CONSTRAINT fk_countryleader_country FOREIGN KEY (countryid) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_countryleader_politician FOREIGN KEY (leaderid) REFERENCES politician (id) ON DELETE NO ACTION ON UPDATE NO ACTION
);

However, this situation obviously isn't the case, because existing leaders are sometimes re-elected (possibly even with some time between two different terms in office), and when they aren't then new leaders take over. So I think that a better link table would look something like this:

CREATE TABLE country_leader (
  countryid integer unsigned NOT NULL,
  leaderid integer unsigned NOT NULL,
  termstart date NOT NULL,
  termend date NOT NULL,
  PRIMARY KEY (???),
  CONSTRAINT ???,
  ....
);

So my question is, what is the best way to fill in the missing PK and constraint information in the junction table country_leader? Or should I be using a different approach to solve this problem?

Assuming that:

every country has at most 1 leader in a given point in time (no overlaps).
there may be gaps in a country's leadership (if you don't want to allow that, change the scond constraint to prevtermend = termstart)
termstart < termend, i.e. every term lasts at least 1 day (if that is not the case, you can alter the constraints below, either by making that <= and/or converting the date columns to datetime.)
you want to enforce all these constraints declaratively.

then I think the following (rather complicated) way will do - by simulating a circular linked list for every country):

    CREATE TABLE country_leader (
      countryid integer unsigned NOT NULL,
      seqno integer NOT NULL,
      leaderid integer unsigned NOT NULL,
      termstart date NOT NULL,
      termend date NOT NULL,
      prevseqno integer NOT NULL,
      prevtermend date NOT NULL,
      PRIMARY KEY (countryid, seqno),
      UNIQUE (countryid, prevseqno),
      UNIQUE (countryid, seqno, termend),
      FOREIGN KEY (countryid)
        REFERENCES country (countryid),
      FOREIGN KEY (leaderid)
        REFERENCES politician (politicianid),
      FOREIGN KEY (countryid, prevseqno, prevtermend)
        REFERENCES country_leader (countryid, seqno, termend),
      CHECK (termstart < termend),
      CHECK (prevtermend <= termstart OR seqno = 0),
      CHECK (prevseqno = seqno - 1 OR seqno = 0)
    );


In the same query INSERT, can two VALUES being NOW() return a different time? I met a query similar to
INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())

having column definitions
| Created   | datetime |
| Updated   | datetime |

Will in this case MySQL set the NOW() value to the current time and return that same values to both calls, or will that query run the (improbable) risk of having two slightly different times for Created and Updated?
Note: I can't change the field types.

The time returned by NOW(), and other date time functions, is derived from the start time of the query. The THD class here is used to contain all the information for the connection. The NOW() function implementation grabs this value and returns it into the now_time structure.
MySQL docs for NOW() also state:

NOW() returns a constant time that indicates the time at which the statement began to execute. ...

You will always get the same NOW() value from anywhere in the query.
MariaDB [test]> select now(),sleep(10),now();
+---------------------+-----------+---------------------+
| now()               | sleep(10) | now()               |
+---------------------+-----------+---------------------+
| 2021-03-22 14:17:05 |         0 | 2021-03-22 14:17:05 |
+---------------------+-----------+---------------------+

Worth noting, that this isn't per transaction:
MariaDB [test]> start transaction
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]>  select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:20:53 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

MariaDB [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:21:00 |
+---------------------+
1 row in set (0.00 sec)


Adding one record from master-table in addition to other records from detail-table to a MySQL-View I am facing a problem creating a MySQL-View. I am not really a pro and i have no clue how to make this work. The situation is the following:
I´ve got a table "Customers" (Master) with all the costumer companies in it and a suitable "Customers_Contacts" (Detail) with all the  company contacts. What i want to do is creating a view with all customer companies and also all of the company contacts.

I am struggling with finding a suitable SQL. I already tried stuff with sub-selects but the result never was the way i wanted it to be.
Here is some (as i hope) explaining material on what my goal is:

Customers

+-----+------------+-----------+------------+------------+-----------------+
| ID  | Matchcode  | FirstName | SecondName | Phone      | Email           |
+-----+------------+-----------+------------+------------+-----------------+
| 123 | Mayer GmbH | NULL      | NULL       | 0170/00000 | zentrale@gmx.de |
+-----+------------+-----------+------------+------------+-----------------+
...

Customers_Contacts
+-----+-------------+-----------+------------+------------+--------------+
| ID  | ID_Customer | FirstName | SecondName | Phone      | Email        |
+-----+-------------+-----------+------------+------------+--------------+
| 111 | 123         | Schmitt   | Else       | 0171/11111 | else@gmx.de  |
| 112 | 123         | Schulz    | Lisa       | 0172/22222 | lisa@gmx.de  |
| 113 | 123         | Marx      | Petra      | 0173/33333 | petra@gmx.de |
+-----+-------------+-----------+------------+------------+--------------+
...

v_Customers_Contacts (View)
+------------+-----------+------------+------------+-----------------+
| Company    | FirstName | SecondName | Phone      | Email           |
+------------+-----------+------------+------------+-----------------+
| Mayer GmbH | NULL      | NULL       | 0170/00000 | zentrale@gmx.de | <-- record from Customers
| Mayer GmbH | Schmitt   | Else       | 0171/11111 | else@gmx.de     |
| Mayer GmbH | Schulz    | Lisa       | 0172/22222 | lisa@gmx.de     |
| Mayer GmbH | Marx      | Petra      | 0173/33333 | petra@gmx.de    |
+------------+-----------+------------+------------+-----------------+
...

I really don't know if this is possible with a MySQL-View so please let me know that first.
I´d be really glad if you could post some SQL-code of your ideas how to make this work.

Thanks and greetings.

CREATE VIEW v_Customers_Contacts
AS

SELECT a.Matchcode as Company
     , b.FirstName
     , b.SecondName
     , b.Phone
     , b.Email
FROM Customers          as a
JOIN Customers_Contacts as b ON b.ID_Customer = a.ID

UNION

SELECT Matchcode as Company
     , NULL      as FirstName
     , NULL      as SecondName
     , Phone
     , Email
FROM Customers ;

When selecting from the view:

SELECT Company
     , FirstName
     , SecondName
     , Phone
     , Email
FROM v_Customers_Contacts
ORDER BY Company, SecondName, FirstName ;


Comparing multiple tables in Sqlite 3 using python: I am quite new to SQLITE3 as well as python. I am working on a project where I have one database with about 20 tables inside of it. One table is for user input and the other tables are pre-loaded with values. I want to be able to compare and match which values are in the pre-loaded tables with the user table. For example: Here is a link to the example tables

Now, I want to be able to compare each row in the Pre-Loaded Table to each row in the Users Table. They both have the Barcode column in common to be able to compare. As a result, during the query process, it should check each row: 1234 - milk - 1 (those columns are equal ) 5678 - butter - 1 ( those columns are equal) 9876 - sugar - 1 (those columns are equal) 1100 - - 1 ( this barcode does not exist in the Users Table)

so when a Barcode, in this case, 1100 doesn't exist in the Users Table, the code should print: You don't have all the items for the Pre-Loaded Table.

so far I have this:

import sqlite3 as sq

connect = sq.connect('Food_Data.db')
con = connect.cursor()

sql = ("SELECT Users_Food.Barcode, Users_Food.Item,     Recipe1.Ham_Swiss_Omelet FROM Users_Food INNER JOIN Recipe1 ON Users_Food.Barcode = Recipe1.Barcode WHERE Recipe1.Ham_Swiss_Omelet = '1'")
con.execute(sql)
data = con.fetchall()
print("You can make: Ham Swiss Omelet")
formatted_row = '{:<10} {:<9} {:>9} '
print(formatted_row.format("Barcode", "Ingredients", "Availability"))
for row in data:
    print(formatted_row.format(*row))
    #print (row[:])
    #connect.commit()

I figured it out. I was able to do a FULL OUTER JOIN to display what is missing from one table but included in another table.

SELECT d.type,
         d.color,
         c.type,
         c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
         d.color,
         c.type,
         c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;


How to query large sets of data using server resources to the max I am planning of retrieving large amounts of data (100+ million rows) from SQL Server to Azure Data Lake Store and I need it to be done in shortest time possible. I would like to ensure that when I query the SQL Server database, it's utilizing the CPU, memory, and network resources available to the maximum.

I was wondering: How do I ensure that SQL Server gives my task its top priority and uses all the power the server has to transfer the data as fast it can?

Are there any hints I can give to the database to tell it that this job needs all of the server's resources?
Would it help if instead of a single SELECT, I issue several SELECT statements in parallel, each retrieving just a chunk of the data (e.g. first select retrieving rows 1 to 50000000 and second select retrieving rows 50000001 to 100000000)?
Anything else that can be done to ensure data is transferred as quickly as possible?

The server is in the cloud, so in theory I can size it so all resources are matched perfectly. I understand in reality something will become the bottleneck.

there is no such hint, if your sessions are the only workload, server resources will be distributed equally among them
It could definitely help. Perhaps 1 session per core. Make sure that clustered index built on ID, so data retrieval can be distributed by chunks using that column and query table with the most efficient operator for such scenario: clustered index seek (with range scan)
Consider to increase TCP packet size: Performance Best Practice: Network Packet Size
Antivirus/Network Security agents on a database server. Some companies have it installed by default. Such middle-ware can hit both IO and network layers, therefore consider to add appropriate exception rules or even disable them during the export.


How to install SQL Server Express 2005 with Full text search I am in need to Install SQL server 2005 Express edition with Full Text Search capability. I don't know what to download and the order of installing the Database management system. Could someone provide me link to download the version with Full Text Search [as default 38Mb setup file does not have this feature].
Also step by step instruction to enable Full Text Search during the installation.

Full Text Search features are available in

Microsoft SQL Server 2005 Express Edition with Advanced Services
Microsoft® SQL Server® 2008 Express with Advanced Services

Installation and configuration are pretty simple, hope you have already installed express edition.

Give it a try!

UPDATE: 2011 - MARCH - 08

You will not be able manage Full Text Catalogs via SSMS.

  SQL Server Management Studio Express

  You cannot update the full-text catalog by using SSMSE. You can use sqlcmd utility or Windows Scheduler to update the full-text catalog.


Questions to Continuous Archiving and Point-in-Time Recovery with PostgreSQL I'm planning my database backup strategy and came across the continuous archiving concept of PostgreSQL which is exactly what I'm looking for.

I have two questions on this.

I'm planning to mount the WAL file archive directory to an external NFS storage space (e.g. /mnt/nfs/archivedir/). I read that this log shipping is asynchronous by default. Does that mean that I don't have any performance issues on this?

As I understand it right, I must not loose any of the WAL segment files, right? But what if any file is corrupt? Can I recover the database ever again then? I guess that a lot of things can go wrong, because what happens when the server is suddenly turned off, or what if the NFS is suddenly not reachable, and so on.

you can use the pg_receivewal utility on the server that will receive the logs, it will help you with this problem.

see more at:

pg_receivewal - Doc official


Roles assigned to each user POSTGRES i'm trying to make a query that shows the users and all the roles that they have, i already know how to ask about the roles of one particular user:

 SELECT oid, rolname FROM pg_roles WHERE
 pg_has_role( 'name_of_user', oid, 'member');

Any idea how to do it?

USERS are themselves ROLES that can login. CREATE USER is actually just an alias. What you probably want is some kind of query that psql issues with \du as a starting point. You can see what psql issues with -E,

********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;
**************************


benefit of using [md5] over [trust] authentication for local Unix sockets I am following a PostgreSQL installation guide that advises (subsequent to installing the database for the first time), to setup a password for the postgres user:

alter role postgres password '<<super-secret-password>>';

... and then change the pg_hba.conf file so that the line for local (Unix) sockets reads:

local all all md5

...  instead of:

local all all trust

It would seem to me that this only adds an extra layer of defense but for somebody to take advantage of local all all trust he would first have to gain access to the local machine, right?

Question 1: do I understand correctly that if access to the local machine is password protected (UNIX password) then there should be no worries over a local all all trust ?

Also, the same guide additionally recommends that a .pgpass file is located in my home folder (I guess for backup scripts that cannot be prompted for a password). The password is stored clear-text in that ~/.pgpass file:

echo 'localhost:5432:*:postgres:<<super-secret-password>>' > .pgpass
chmod 0600 ~/.pgpass

Question 2: So, what is the benefit of using md5 and not trust for local sockets? If one gains access to local machine, he can also read the .pgpass file. Am I missing anything?

For Question 1: using md5 auth for local connections is particularly helpful when you have a multi-user machine, or if you might add additional user accounts for friends/family/coworkers/whoever someday.

If you are and will remain the only actual user on your system, there is less benefit, but it could still be useful: suppose some bad guy manages to break into some system account like 'www'. He may not be able to do a whole lot from that account without root privileges. But if you've left your pg_hba.conf open to 'trust' all local users, the bad guy can do anything to your database, probably including taking over the 'postgres' user account as well, and moving on to attempt to root your system, steal your data, or whatever from there.

For Question 2: You could use a .pgpass file with, say, only a 'readonly' user's credentials, so if a bad buy took over that account he wouldn't have superuser-level access to the database. If you do store the password for the postgres user in your ~/.pgpass, then yes, it would be "game over" for your postgres instance if a bad guy took over that local user account. Again, that documentation was written with the possible use-case of a multi-user machine in mind, e.g. different local users might have or use various Postgres user accounts, not necessarily superuser accounts.


EXP-00085 warnings while doing full backup During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings

[...]
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6502 encountered
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 351
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling     SYS.DBMS_SCHED_JOB_EXPORT.create_exp for object 102269
EXP-00008: ORACLE error 6502 encountered
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 351
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_JOB_EXPORT.create_exp for object 102271
EXP-00008: ORACLE error 6502 encountered
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 351
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_JOB_EXPORT.create_exp for object 102273
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.

I looked for these three objects querying dba_objects with their object_id and it returns

ADV_SQLACCESS5150248
ADV_SQLACCESS9067727
ADV_SQLACCESS2676493

Are these warnings worrying?

How should I fix them and what is going on exactly?

I expect this is bug 5120780 fixed in 10.2.0.4 (a quick search on Metalink would have told you).


MySQL - Information_schema Events - How persistent or retention is managed I'm a MS SQL guy and understand agent job history.
I'm now working with MySQL.
I've searched hi and low but I can't find any solid info on how event data within information_schema.events is persisted, retained for.
I plan to setup some MySQL Events but need to understand how reliable the logs are.
I don't think we have anything in our my.cnf with regards to retention periods of information_schema.
If it's forever - should I try and clean this up with some house-keeping job?
I am making events to fire once a week. Ideally these would be retained for 180 days.
Events that fire more regularly every 5 minutes, perhaps should only be retained for a month.
how do I extend this/ manage this - do people log to a separate table that you can manage yourself?

In MySQL, an "event" is not a periodic data element. It's more like a cron job (sorry— an "at" job in Windows). It's a routine of code with a schedule.
The content of information_schema.events is metadata about the schedule job. For instance, the code body, the schedule, etc. This information should be unchanging no matter how many times you the event runs.
It does not store any history of executions of that job, nor results or data produced by that job.


SQLDeveloper: Unable to desc a table that exists I have a table, let's call it MYTABLE.

In SQLDeveloper, I can see the data in the table using:

SELECT * FROM MYTABLE

However, when I do DESC MYTABLE, it gives me an error:

  ERROR: object MYTABLE does not exist.

If I try the same thing from sqlplus, there is no error and I get a listing of the columns in the table.

I checked dba_all_tables and there is a row for the table.

select * from dba_all_tables where table_name = 'MYTABLE';

returns 1 row as expected.

Any ideas as to what I'm missing?

The command works fine with a different table (MYSCHEMA.MYTABLE2), so it's likely not a sql developer bug.

The same table exists in several schemas:

select owner, object_type, count(*) from dba_objects where object_name='MYTABLE' and owner='MYSCHEMA' group by owner, object_type;

MYSCHEMA    TABLE SUBPARTITION  128
MYSCHEMA    TABLE PARTITION 16
MYSCHEMA    TABLE   1

Unfortunately, I can't provide a set of steps to reproduce this independently because these tables already exist in the schema I'm working on. My best guess right now is that something went wrong during the table creation long ago, and I can't go back and look at the source code that was executed back then because of a SCCS change in the company.

MYTABLE is not a synonym or mview.

This must be because of MYTABLE being a synonym of a synonym, which is not supported on SQL Developer 4.1.3(must be a bug). Reference

I have three users user3, user2 and user1

SQL> conn user3/user3
Connected.
SQL> create table mytable(id number);

Table created.

SQL> grant select on mytable to user2 with grant option;

Grant succeeded.

SQL> conn user2/user2
Connected.
SQL> create synonym mytable for user3.mytable;

Synonym created.

SQL> grant select on mytable to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.

SQL>  create synonym mytable for user2.mytable;

Synonym created.

SQL> desc mytable;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> conn / as sysdba
Connected.
SQL> select table_name from dba_all_tables where table_name='MYTABLE';

TABLE_NAME
------------------------------
MYTABLE

SQL> select owner from dba_tables where table_name='MYTABLE';

OWNER
------------------------------
USER3

Now try to describe it on SQL Developer.

desc mytable;

ERROR: object MYTABLE does not exist


Using SQL Server TDE - Encryption 0 percent I'm using TDE for SQL Server. Querying certificate:

select database_name = d.name,
dek.encryptor_type,
cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

Output:

database     encryptor        cert_name
tempdb      ASYMMETRIC KEY    NULL
TDE_Test    CERTIFICATE       TestZert3

Querying encryption status:

select db_name(database_id), encryption_state,
percent_complete, key_algorithm, key_length
from sys.dm_database_encryption_keys

Output:

database  encrypt_state percent_complete key_algorithm
tempdb      3             0              AES 256
TDE_Test    3             0               AES 256

Why is the complet_percent 0?

Why is the complect_percent 0?

Your database is already encrypted - encrypt_state = 3 -- Encrypted.

When the encryption is in progress, you will see the percent_complete value. When the database is encrypted, the percent_complete will be 0.

you can refer to my script for listing out the databases, encryption status along with other useful information.


mysql Replication stalled I am having mysql 8.0 installation with replication between 2 servers in master-master configuration. While data is being replicated from server A to server B the replication is stalled from hours. Below is the snippet for show slave status \G on server B. I am not sure on what waiting for handler commit means and whether its the cause of my Replication stalling

        Master_SSL_Cipher:
       Master_SSL_Key:
Seconds_Behind_Master: 49808
        Last_IO_Errno: 0
        Last_IO_Error:
       Last_SQL_Errno: 0
       Last_SQL_Error:Replicate_Ignore_Server_Ids:
     Master_Server_Id: 1
     Master_Info_File: mysql.slave_master_info
            SQL_Delay: 0
  SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: waiting for handler commit
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:

After executing show processlist; i am getting a process that is waiting from a long time , having a wait time of 50071 and increasing

system user     |                               | NULL | Query       | 50071 | waiting for handler commit

What steps shall i take to bring my replication on track?

That's new with 8.0.13:

  A new Performance Schema stage, waiting for handler commit, is available to detect threads going through transaction commit. Thanks to Facebook for the patch. (Bug #27855592, Bug #90417)

https://bugs.mysql.com/bug.php?id=90417

Suggest you file a comment with that bug report; could be that it caused a regression.


What is the easiest way to backup PostgreSQL and send backups to Google Drive? I am new with PostgreSQL, So my question is: what is the easiest way to make regular backups and send them to Google Drive. Perhaps, there is a tool that can make it or something else.

You could try:

https://github.com/andreafabrizi/Dropbox-Uploader

to send your pg_dump output to Dropbox. I got this by Googling script load file dropbox. You may find some of the other scripts more to your liking.

Remember the dba's motto - backup or f*ckup and test, test and test again. Personally, I would recommend a local (speed of recovery) and remote (double-sure) copy (as the Americans say, "belt and suspenders").

It is a bash tool, but then I always assume PostgreSQLers are on a decent operating system :-).

Update - the bash shell can now be used natively on Windows as well since Microsoft appear to be doing more embracing and less smothering of *nix tools!


Add Gameweek Fixeture based on Datefield having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in a 3 days timespan (some datediff to be used, can;t figure it out)

Initial Table

Date            HomeTeam     AwayTeam
2018-08-10     Man United    Leicester
2018-08-11     Bournemouth   Cardiff
2018-08-11      Fulham       Crystal Palace
2018-08-11    Huddersfield   Chelsea
2018-08-11      Newcastle    Tottenham
2018-08-11       Watford     Brighton
2018-08-11       Wolves      Everton
2018-08-12      Arsenal      Man City
2018-08-12     Liverpool     West Ham
2018-08-12    Southampton    Burnley
2018-08-18   Cardiff         Newcastle
2018-08-18     Chelsea       Arsenal
2018-08-18     Everton       Southampton

Desired Output

Date            HomeTeam     AwayTeam          RoundNumber
2018-08-10     Man United    Leicester         1
2018-08-11     Bournemouth   Cardiff           1
2018-08-11      Fulham       Crystal Palace    1
2018-08-11    Huddersfield   Chelsea           1
2018-08-11      Newcastle    Tottenham         1
2018-08-11       Watford     Brighton          1
2018-08-11       Wolves      Everton           1
2018-08-12      Arsenal      Man City          1
2018-08-12     Liverpool     West Ham          1
2018-08-12    Southampton    Burnley           1
2018-08-18    Cardiff        Newcastle         2
2018-08-18     Chelsea       Arsenal           2
2018-08-18     Everton       Southampton       2

If the gap between matches is always more than 3 days, you could use this:

;WITH CTE AS
(
SELECT  DateField,
        CASE WHEN DATEDIFF(day,LAG(DateField) OVER(ORDER BY DateField),DateField)> 3 THEN 1 ELSE 0 END as newround,
        Hometeam,
        AwayTeam
FROM dbo.Matches

)
SELECT Datefield, HomeTeam,AwayTeam, SUM(newround) OVER(ORDER BY DateField) + 1 as [Round]
FROM CTE;

DDL & DML

CREATE TABLE dbo.Matches(DateField DATE,HomeTeam varchar(100),AwayTeam varchar(100));
INSERT INTO dbo.Matches
VALUES

('2018-08-10',   'Man United',    'Leicester'),
('2018-08-11',   'Bournemouth',   'Cardiff'),
('2018-08-11',   'Fulham',       'Crystal Palace'),
('2018-08-11',   'Huddersfield',   'Chelsea'),
('2018-08-11',   'Newcastle',    'Tottenham'),
('2018-08-11',   'Watford',     'Brighton'),
('2018-08-11',   'Wolves',      'Everton'),
('2018-08-12',   'Arsenal',      'Man City'),
('2018-08-12',   'Liverpool',     'West Ham'),
('2018-08-12',   'Southampton',    'Burnley'),
('2018-08-18',   'Cardiff',         'Newcastle'),
('2018-08-18',   'Chelsea',       'Arsenal'),
('2018-08-18',   'Everton',       'Southampton'),
('2018-08-25',   'Round3Example_1',       'Round3Example_2');

Result

Datefield   HomeTeam              AwayTeam            Round
2018-08-10  Man United            Leicester           1
2018-08-11  Bournemouth           Cardiff             1
2018-08-11  Fulham                Crystal Palace      1
2018-08-11  Huddersfield          Chelsea             1
2018-08-11  Newcastle             Tottenham           1
2018-08-11  Watford               Brighton            1
2018-08-11  Wolves                Everton             1
2018-08-12  Arsenal               Man City            1
2018-08-12  Liverpool             West Ham            1
2018-08-12  Southampton           Burnley             1
2018-08-18  Cardiff               Newcastle           2
2018-08-18  Chelsea               Arsenal             2
2018-08-18  Everton               Southampton         2
2018-08-25  Round3Example_1       Round3Example_2     3

Db<>fiddle


Copying CSV file to temp table with dynamic number of columns? I am wondering if there is a way to copy a csv file into a temp table where the number of columns is unknown in the csv file. The DB software I'm using is PgAdmin III. I figured out that if I do know the number of columns then I can create a temp table with that amount of columns and then copy the csv file in like so:

   CREATE TEMPORARY TABLE temp
   (
      col1 VARCHAR(80),
      col2 VARCHAR(80),
       ....
      coln VARCHAR(80)
   );

COPY temp FROM 'C:/Users/postgres/Boost.txt' CSV HEADER DELIMITER E'    '

However if I try to just simply copy the csv file to a temp table with no columns in the temp table, Postgresql (version 8.4) complains that I'm working with a table that has less columns than in the csv file. I've been researching and can't seem to find anything in the Postgresql docs about this. Does anyone know if copying a csv file into a temp table with an arbitrary number of columns decided at run time is possible in Postgresql? Once the temp table is loaded with the csv file I plan on doing some comparisons to other tables with the temp table before it is destroyed. Also the first row in the csv file contains headers.

Basics:

The 1st row of the CSV file has column names of defined format.
The PROGRAM clause of COPY and GET DIAGNOSTICS after COPY require Postgres 9.3+.
format() requires Postgres 9.1+
This works with pure standard Postgres - except for the head command that the shell is expected to provide. For Windows versions consider:

How to do what head, tail, more, less, sed do in Powershell?

Full automation

This function copies any table structure completely dynamically:

CREATE OR REPLACE FUNCTION f_dynamic_copy(_file    text
                                        , _tbl     text = 'tmp1'
                                        , _delim   text = E'\t'
                                        , _nodelim text = chr(127)) -- see below!
  RETURNS text AS
$func$
DECLARE
   row_ct int;
BEGIN
   -- create staging table for 1st row as  single text column
   CREATE TEMP TABLE tmp0(cols text) ON COMMIT DROP;

   -- fetch 1st row
   EXECUTE format($$COPY tmp0 FROM PROGRAM 'head -n1 %I' WITH (DELIMITER %L)$$  -- impossible delimiter
                , _file, _nodelim);

   -- create actual temp table with all columns text
   EXECUTE (
      SELECT format('CREATE TEMP TABLE %I(', _tbl)
          || string_agg(quote_ident(col) || ' text', ',')
          || ')'
      FROM  (SELECT cols FROM tmp0 LIMIT 1) t
           , unnest(string_to_array(t.cols, E'\t')) col
      );

   -- Import data
   EXECUTE format($$COPY %I FROM %L WITH (FORMAT csv, HEADER, NULL '\N', DELIMITER %L)$$
                , _tbl, _file, _delim);

   GET DIAGNOSTICS row_ct = ROW_COUNT;
   RETURN format('Created table %I with %s rows.', _tbl, row_ct);
END
$func$  LANGUAGE plpgsql;

Call variants:

SELECT f_dynamic_copy('/path/to/file.csv');
SELECT f_dynamic_copy('/path/to/file2.csv', 'tmp_file2');
SELECT f_dynamic_copy(_file  => '/path/to/file2.csv'
                    , _tbl   => 'tmp_file2');
                    , _delim => E'\t'); -- using assignment operator since pg 9.5

Answer:

Created table tmp_file2 with 123 rows.

Before the main COPY, run a preliminary COPY ... TO tmp0 to fetch the first row with column names, which are expected to be unquoted, case-sensitive strings like COPY ... TO ... (FORMAT csv, HEADER) would export them.

The structure of the actual target table is derived from it, all columns with data type text. The default name of the resulting table is tmp1 - or provide your own as 2nd function parameter.

Then COPY is executed. The default delimiter is a tab character - or provide your delimiter as 3rd function parameter.

Use any single-byte character for the non-delimiter _nodelim which does not appear in the first line of your CSV file. I am arbitrarily picking the control character "Delete" (ASCII 127). That character would be swallowed here on SO, so I generate with chr(127) instead, which is also valid. Assuming the character won't pop up - or provide your non-delimiter as 4th function parameter.

The function returns table name and number of imported rows.
Remember, a temporary table dies with the end of the session.

The manual:

  Executing a command with PROGRAM might be restricted by the operating
  system's access control mechanisms, such as SELinux.

Related answer on SO:

“ERROR: extra data after last expected column” when using PostgreSQL COPY

Postgres 8.4

That version is too old, I am not going to back-port that far.

GET DIAGNOSTICS  is an optional feature. You can just leave it away or replace it with a full count on the table
A primitive (expensive) alternative for the PROGRAM clause of COPY in pg 9.3 would be to import the complete table instead:

EXECUTE format($$COPY tmp0 FROM %L WITH (DELIMITER %L)$$, _file, _delim);

Or you prepare a second input file, or you can make it work by piping from the shell: COPY tablename FROM STDIN is available in pg 8.4.

format() can be replaced with plain string concatenation. Be wary of SQL injection though!

SQL injection in Postgres functions vs prepared queries


Do i need to rebuild the indexes on the table after changing column length on SQL table? I have increased the length of a column using alter table statement, there are 3 non clustered and 1 clustered index on the table.  Now do i need to rebuild the indexes on the table ?

As far as I know you won't run into issues with expanding the length of a VARCHAR column. However, there are certain types of datatype changes that do you require you to drop and recreate indexes. Perhaps you are thinking of those?

This is something that's fairly straightforward to test. You don't even need data in your test table. For example:

CREATE TABLE #X_TEST_IX (COLUMN1 VARCHAR(100) NOT NULL, PRIMARY KEY (COLUMN1));

-- runs without an error
ALTER TABLE #X_TEST_IX
ALTER COLUMN COLUMN1 VARCHAR(200) NOT NULL;

-- throws an error
ALTER TABLE #X_TEST_IX
ALTER COLUMN COLUMN1 INTEGER NOT NULL;

The error thrown by the last statement is:

  Msg 5074, Level 16, State 1, Line 19 The object
  'PK__#X_TEST___7116E265BDC9D4B5' is dependent on column 'COLUMN1'

  Msg 4922, Level 16, State 9, Line 19 ALTER TABLE ALTER COLUMN COLUMN1
  failed because one or more objects access this column.


FTS is slow when searched word appears frequently in rows I have a message table with a content column indexed for full text search:

create index message_fts on message using GIN(to_tsvector('english', content));

The problem I have and that I'd like to understand is that searches involving frequent words are very slow.

Here's a normal fast enough query on a rare "word":

miaou=> explain analyze select count(*) from message where (to_tsvector('english', content) @@ to_tsquery('fajre'));
                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=16808.35..16808.36 rows=1 width=8) (actual time=6.565..6.565 rows=1 loops=1)
   ->  Gather  (cost=16808.13..16808.34 rows=2 width=8) (actual time=1.608..6.556 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=15808.13..15808.14 rows=1 width=8) (actual time=0.456..0.457 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on message  (cost=293.28..15803.69 rows=1776 width=0) (actual time=0.453..0.453 rows=0 loops=3)
                     Recheck Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('fajre'::text))
                     ->  Bitmap Index Scan on message_fts  (cost=0.00..292.22 rows=4262 width=0) (actual time=0.954..0.954 rows=0 loops=1)
                           Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('fajre'::text))
 Planning time: 0.249 ms
 Execution time: 8.400 ms
(11 rows)

And here's a very slow query on a frequent term:

miaou=> explain analyze select count(*) from message where (to_tsvector('english', content) @@ to_tsquery('faire'));
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
<other identical notices removed for clarity>
                               QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=189901.34..189901.35 rows=1 width=8) (actual time=47192.053..47192.053 rows=1 loops=1)
   ->  Gather  (cost=189901.12..189901.33 rows=2 width=8) (actual time=47188.506..47192.042 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=188901.12..188901.13 rows=1 width=8) (actual time=47183.158..47183.158 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on message  (cost=2012.04..188702.23 rows=79558 width=0) (actual time=72.503..47152.312 rows=66781 loops=3)
                     Recheck Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('faire'::text))
                     Rows Removed by Index Recheck: 1329552
                     Heap Blocks: exact=12673 lossy=22516
                     ->  Bitmap Index Scan on message_fts  (cost=0.00..1964.30 rows=190940 width=0) (actual time=64.740..64.740 rows=200347 loops=1)
                           Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('faire'::text))
 Planning time: 0.268 ms
 Execution time: 47199.290 ms
(13 rows)

It can be noticed that the index correctly finds the relevant records (200347 of them).

What should I fix or look at ? I'm especially surprised by the many notices I get when just querying, given that the matching information is already given by the index.

Your bitmap has grown too large to fit in work_mem, and so it has "gone lossy" where it records just the blocks that contains matches (for some blocks), and then needs to recheck each row in those blocks.

                 Rows Removed by Index Recheck: 1329552
                 Heap Blocks: exact=12673 lossy=22516

Doing this is slow.  Especially so because you are using a functional index, and so it has to re-parse content for each of those rows as the parsed form is not stored in the table.  (That re-parsing is also why you get the NOTICEs).

Increase work_mem so that it can hold the entire bitmap.


How do I insert tens of thousands of rows without locking? I have a server that receives data from thousands of locations all over the world. Periodically, that server connects to my DB server and inserts records, one row at a time in rapid succession. There can be up to 6 of these processes (perl) running simultaneously and each may have over 50,000 insert statements, and subsequently, the tables are getting locked.

What I am trying to figure out is what causes locking? Am I better off creating a multi-insert with, say, 100 rows at a time and doing them end to end? What do I use for guidelines?

The DB server has 100GB RAM and 12 processors. It is very lightly used but when these inserts come in, everyone freezes up for a couple minutes which disrupts peopel running reports, etc.

Thanks for any advice. I know I need to stagger the inserts, I am just asking what is a recommended way to do this.

You cannot get rid of all locking.  However, there are ways to speed up what you are doing:

Use multi-row INSERTs.  100 rows at a time will run 10 times as fast.
As mentioned, switch to InnoDB using pt-online-schema-change.  Then put multiple INSERTs into a transaction (BEGIN ... COMMIT).
The 6 threads could write to 6 different tables (such as CREATE TEMPORARY TABLE), then INSERT INTO real_table SELECT * FROM tmp_table.  Even on MyISAM this will run significantly faster because of 6 locks instead of 6*50K.  Read about concurrent_insert to see how to avoid 50K table locks into the tmp tables.

Please show us the SHOW CREATE TABLE.  There could be significant other issues, especially with indexes.