Log MySQL DB changing queries and users For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out

the General Query Log because of performance issues (it logs everything and that's too much IO)
the Binary Log because it doesn't log the user's name.
use a tool like ngrep to catch the network traffic and filter for UPDATE, DELETE etc. because this will get me in a mess with transactions and I can't know if a received query has really been executed.

I couldn't find any settings that would have let me change the behavior of the MySQL-inherent logs, so I'm looking for other solutions. I've come up with two possibilities so far:

write the general query log to a named pipe and attaching a filter and writer to the other end of the pipe – but I'm concerned about the performance of this…
transmitting the relevant logs separately to the server but that way I'd have to send the queries twice (once for the DB and again for logging), it would be difficult to assure the logs are in sync with the DB (transactions, locks etc.), and for security reasons it may not be wise to trust the client to really send the logs

Backgound:

the users access the DB via a Java Desktop Application that opens an SSH tunnel to the MySQL server
I'm using EclipseLink as persistence provider
the application makes heavy use of transactions
the server is running in a shared environment

Do you have a better idea on how to perform my logging?

You could utilize the binary log method by adding a init_connect to log the user into a table*.

CREATE TABLE admin.connections (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    connect_time DATETIME NOT NULL,
    user_host VARCHAR(50) NOT NULL,
    connection_id INT UNSIGNED NOT NULL,
    INDEX idx_connect_time_user_host (connect_time, user_host)
);

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user_host, connection_id)
     VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

Some caveats:

Most importantly, this will only work for users without the SUPER privilege, since the init_connect is bypassed for those users.
Make sure all your users have INSERT privilege into the admin.connections table

You can then use the values in admin.connections to match up with the binary_log threadID and timestamp

*This idea was taken from here and I haven't personally tried it.

Originally this had a unique index on connect_time/user_host. But this would cause issues with same user connecting in same second. If that is not a problem in your scenario, leave it that way. If it is an issue, just make it a normal index.


I have to create tables where both have each as the foreign key . Which one should be created first DDL QUERY FOR CREATING MAINTENANCE_PROBLEM

CREATE TABLE MAINTENANCE_PROBLEM (
MP_ID int primary key,
MP_Description varchar2(250),
Standard_Min_Labor_Cost number(4,2),
Standard_Min_Labor_Hours int,
PART_ID int,
Constraint MP_PART_ID_FK foreign key (PART_ID) references Vehicle_Part (PART_ID)
);

DDL QUERY FOR CREATING VEHICLE_PART

CREATE TABLE VEHICLE_PART (
 PART_ID int primary key,
PR_Description varchar2(250),
PR_Cost number(4, 2),
MP_ID int
Constraint VP_MP_ID_FK foreign key (MP_ID) references MAINTENANCE_PROBLEM (MP_ID)
);

It doesn't matter--create both of them without the foreign key constraint, and then alter the tables to add the constraints.


How to install MySQL 5.1 on Ubuntu 10.10? I researched a lot but could not find any link which gives step by step procedure. Everywhere, there are vague answers. Can someone please give me steps to be followed to install Mysql 5.1 ?

It gives me an error and stop the execution even when i use -f.
This is the message from command prompt.

The following packages have unmet dependencies:
 mysql-server-5.1 : Depends: mysql-client-5.1 (>= 5.1.61-0ubuntu0.10.10.1) but it is not going to be installed

sudo apt-get install mysql-server-5.1 mysql-client-5.1

... from the command prompt should just work.

If not, edit your question with the full error messages you are seeing.

Post-installation steps are summarized in this blog post.


SQL Server 2012 Replication Backup and Restore I am using SQL Server 2012. We have setup a replication using 'transactional with queued updating'.

If the publisher database crashes and some records in the subscriber database have not been replicated to the publisher database (and in the publisher database backup), how would we have to re-sync the data after restoring the publisher database and setting up replication without losing the new data in the subscriber database?

(And, also apply to Vice Versa case)

Thanks.

Initialize SQL Server replication using a database backup

More steps at
https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/


SSAS Deployment Wizard - Issue with multiple connection strings I've observed an issue with the SSAS Deployment Wizard.

I have two environments DEV and PROD.
A few weeks ago when I deployed anything on PROD I had one connection string only there to PROD (similar to this screen):

But today I've observed that the SSAS Deployment Wizard window looks like this:

Multiple connection strings to DEV and PROD.

Deployment went successfully but when I've tried to process the tables in the SSAS Management Studio I get an error like below:

Any ideas how to solve this issue ?
Is there any method to keep only one connection string to PROD in the DeploymentWizard instead of multiple to DEV and PROD ?

What happened is that developers added new data sources instead of adding tables from an existing connection. In the future go to the Model menu... Existing Connections... Open. Then choose another table or query to import. This process is documented here.

I don't believe there is a UI way of switching an existing table to use a different data source. The best approach is probably to just delete and recreate that table with the process above.

You should be able to delete the extra unused data sources from the Existing Connections dialog.


MySQL how to make a backup while the server is not running I made a update on my mac to catalina few days ago. After this my MySQL-Server was broken and I would like to reinstall it. Before doing this I would like to make a backup of my database. Because the server is not running I can't use mysqldump or phpMyAdmin. Is it enough to save the directory /usr/local/mysql/data/ or is there anything else I should do?

Thanks Thomas

You can take physical backup of mysql data directory.

Step-1 Take backup of mysql data directory.

cd /datadir/
cp -r /datadir/ /backup_path/

Step-2 Once you re-install it, just verify data are whether data are purged or not ?

Step-3 If data are not purged from datadir, Simple you need to start mysql service & ready go.

Step-4 Otherwise you are suppose to copy backup datadir from backup path.

cd /datadir/
cp -r  /backup_path/ /datadir/


Get memory usage of mysql query during runtime Is possible to obtain used memory in a running query?

Something like:

mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql before run query
mysql> SELECT id FROM table WHERE id>2; #run query
mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql after run query

and return something like (return 17kb):

+----------+
|  memory  |
+----------+
|  20100   |
+----------+

+------+
|  id  |
+------+
|  3   |
+------+

+----------+
|  memory  |
+----------+
|  2026767 |
+----------+

or

mysql> USAGE SELECT id FROM table WHERE id>2;

and return something like (return 17kb):

+---------+
|  USAGE  |
+---------+
|  16767  |
+---------+

Is this possible?

I need it to do various tests and see which engines queries and performs better (personal experience).

In MySQL 5.7, assuming you're using mostly INNODB tables, you can see how much memory is currently being used by running this query:

SHOW ENGINE INNODB STATUS

You can see the maximum amount of memory your DB will ever use by running this query:

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
    @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;


Storing sensitive information in a SQL Server project I am very new with encryption in SQL Server so I am stumbling over things that require prior experience. I have done my googling, but I need reassurance so here we go:

Scenario

I have a SQL Server Project (*.sqlproj) where my team and I put all of our:

schema related changes
post deployment scripts
seed scripts

Best practices?

However I have run into a best practices situation:

Should I store my MASTER KEY in my SQL Project?

I'm leaning towards no because of the password.

Should I store my CERTIFICATE in my SQL Project?

Seems okay to me

Should I store my SYMMETRIC KEY in my SQL Project?

Seems okay to me

Overall

Should these sensitive items be a part of my SQL Server Project and checked into source control?

It is usually best practice to NOT store anything with a password in Source Control.

Does the Master Key qualify for what I am talking about?

On a side note - I am not storing column level seed data passwords in source control. I have already decided this would be a bad idea.

Example for context

I am pulling these examples from Microsoft's examples here.

USE AdventureWorks2012;
GO

-- 1. Should this be stored in my SQL Server Project? The password concerns me.
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '<some strong password>';
GO

-- 2. Should this be stored in my SQL Server Project? Seems harmless.
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO

-- 3. Should this be stored in my SQL Server Project? Seems harmless.
CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;

You've got to put these things somewhere, where they are:

accessible only to the people who should have access to them;
protected from the people who shouldn't; and,
recoverable in the event of any disaster (be it people moving on or machines getting thrown in a tar pit).

But only you can know where that somewhere is in your environment. It could very well be source control (but probably not); Azure Key Vault is an option, depending on how cloud-averse your business is; some companies use a service like Enterprise LastPass or 1Password Business; or maybe a sticky note on Milton's red stapler is more your speed... as your peers and not business stakeholders, we just don't know.


Rebuild or Reorganize indexes based on fragmentation I am using an Index Defrag Script, which provides a nice way to rebuid or reorganize indexes based on fragmentation percentages. I like to rebuild my database indexes if their fragmentation is over 30%, and reorganize if < 30%.

If an index doesn't allow page locks (@AllowPageLocks=0) or contains LOB objects, then the script will reorganize instead of rebuild, even if the index fragmentation value exceeds the rebuild threshold value.

I have an index with 35% fragmentation which doesn't rebuild. I just need to know , what is reason behind this logic. i mean what is reason behind to reorganize if index table contains LOB objects and (@AllowPageLocks =0) , allow page locks.

Is the table too small?

Fragmentation figures are meaningless for small tables that span up only a few extents

This is captured by @minPageCount in the script. The default is 8: meaning it will be skipped if one extent in size.


Postgres \copy from CSV: how to format range variables? Consider a Postgres table containing a range variable:

CREATE TABLE my_table (t_range tsrange, field1 text);

If I want to manually insert a row via psql, I can run:

INSERT INTO my_table (t_range, field1) VALUES ('[2014-06-02 07:00:00,infinity)'::tsrange, 'sometext');

Instead of inserting rows manually, I want to perform a bulk insert from a CSV file. I created a CSV file called my.csv using a similar format (using pipe as the delimiter to avoid having to escape the comma in the tsrange variable, and removing the explicit quote characters) with some content:

t_range|field1
'[2014-06-02 07:00:00,infinity)'::tsrange|'sometext1'
'[2014-06-02 07:00:00,infinity)'::tsrange|'sometext2'

Then from psql I ran

\copy my_table FROM 'my.csv' DELIMITER '|' QUOTE '''' CSV HEADER;

but this failed with the error

ERROR:  malformed range literal: "[2014-06-02 07:00:00,infinity)::tsrange"
DETAIL:  Junk after right parenthesis or bracket.
CONTEXT:  COPY my_table, line 2, column t_range: "[2014-06-02 07:00:00,infinity)::tsrange"

How should I format a CSV file with range variables for use with \copy?

Removing the explicit cast to tstrange worked. The new CSV file looks like

t_range|field1
'[2014-06-02 07:00:00,infinity)'|'sometext1'
'[2014-06-02 07:00:00,infinity)'|'sometext2'

It seems to me that \copy is fine with automatically attempting to cast to the appropriate datatypes, but the explicit cast causes it to choke.

One can also simplify the CSV file a bit in this case by removing the quotes:

t_range|field1
[2014-06-02 07:00:00,infinity)|sometext1
[2014-06-02 07:00:00,infinity)|sometext2

and modifying the import command:

\copy my_table FROM 'my.csv' DELIMITER '|' CSV HEADER;


MySQL Mistake with GRANT OPTION I am unsure reading the MySQL documentation if creating a user with the GRANT option will give them the power to create users and grant privileges, or change the privileges of other users databases.

I have been creating databases for users like this

CREATE DATABASE user;
USE user;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password'
WITH GRANT OPTION;

Is this the best way of doing it or have I just given my users too much control? They are people I am hosting sites for. Thankfully at this point they are trustworthy. I use quotas.

Edit: I have realized I have been granting users access to all databases. This is obviously stupid I should be using this:

GRANT ALL PRIVILEGES ON database.* to 'user'@localhost' IDENTIFIED BY 'password'

What is the simplest way to revoke privileges for every user except root so I can quickly end this catastrophic rookie mistake?

There is no "mass revoke" statement so you best option to change this is to update the users table:

UPDATE mysql.user SET Grant_priv = 'N' WHERE user != 'root';

Not that occasionally, there are users that should have grant privileges (and should have that) which are not named root. On my system, for example, I have debian-sys-maint (which I actually wonder if they should have grant privileges, but that's how it's installed).

Do not forget to reload the privileges after changing the user table:

FLUSH PRIVILEGES;

You can find more information on http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html.


Oracle: Select on data from a CSV file I have a CSV file with many integers in the form:

id
---
1
2
3

I need to write a statement that selects ids from a table that are in the list in my CSV.

I could just convert the data 1000 elements at a time into a SQL IN like:

SELECT * FROM TABLE WHERE ID IN (....)

but that would take hundreds of queries, is there a better way I can do this?

You can do this using an Oracle External Table.

First, create a directory pointing at the directory holding the .csv file:

CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/path/to/yourdirectory';

Grant read access to your user:

GRANT READ ON DIRECTORY MYDIRECTORY TO YOURUSER;

Create a table that'll read from your CSV file:

CREATE TABLE mytable
(
  id NUMBER
)
ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY MYDIRECTORY
    ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE
        FIELDS (id NUMBER)
      )
    LOCATION ('yourfile.csv')
 );

Your query would then be:

SELECT * FROM TABLE WHERE ID IN (SELECT id FROM MYTABLE);


Foreign Key - One Child Table to Multiple Parent Tables Maybe I'm thinking of this problem in the wrong way, but we maintain an AccessControl table that is a child to several parent tables (e.g. CompanyItems, SystemFiles, etc.). I'm trying to make sure we don't create orphaned AccessControl recs, but I'm not sure how to properly set up this relationship. Any tips?

AccessControl
  ItemId
  AccessId (for user access)
  ItemType
  OwnerCd (1 for owner, 0 for read-only access)

CompanyItems
  CompanyId
  ItemId
  ItemType
  ItemName

SystemFiles
  FileId
  FileName
  FileType

Sample Data:
AccessControl: ItemId=1, AccessId=1 (UserId), ItemType=0(for a CompanyItems rec), OwnerCd=1 (owner)
AccessControl: ItemId=1, AccessId=2 (different UserId), ItemType=0, OwnerCd=0 (read-only access)
AccessControl: ItemId=10, AccessId=1, ItemType=1 (for SystemFiles entry), OwnerCd=0
CompanyItems: CompanyId={whatever company}, ItemId=0, ItemType=0, ItemName='Test Item'
SystemFiles: FileId=10, FileId='Test File', FileType='pdf', etc.

If my CompanyItems is deleted, the two corresponding AccessControl recs should also be deleted. If my SystemFiles rec is deleted, then its one corresponding AccessControl rec should be deleted.

I'm not sure I've seen any DBMS support multiple parents for foreign keys like this, it is probably assumed that something needed this is not in good form (the linking field is explicitly overloaded as it can refer to several distinct types of thing, which means it dos not fit an accepted "normal form").

You could instead have a general "AccessableThings" table that contained just the IDs, and then have the UserItems/SystemItems/OtherItems/etc refer to that via FK (to ensure everything in those tables also appears in the IDs table) as well as AccessControl doing so. If you have other generic fields that apply to all types ("display name" for instance) then you might include them in the AccessibleThings table to so you can avoid joining in all the other tables when all you need are generic details.

CompanyItems            AccessibleThings          AccessControl
============            ================          =============
CompanyID PK FK ------> ThingID PK      <--.      AccessRuleID PK
<companyfields>   |     <genericfields>    |----- ThingID FK
                  |                               <AccessRuleFields>
SystemFiles       |
===========       |
FileID PK FK -----|
<filefields>      |
                  |
SomethingElse     |
=============     |
SomethingID ------|

This will enforce things being correct as entered, though can not delete AccessControl on the delete of an Item, just on delete of the AccessibleThings row (which can also cascade to the Items tables of course) so this table becomes the central object in that regard. Obviously your data access layer (or business logic, but the data layer is more likely the correct place) will need to know about the new structure at each relevant point.

Alternative:

You could also enforce your more complex data integrity relationship entirely via triggers, though you need to be very careful with both complexity and efficiency here otherwise you could create a veritable zoo of nightmares for later.


What can speed up a SQL count query? When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems?  I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so I'm sure I'll be getting a few answers here.  I migrated about 157 million rows to a SQL Server database, and this query is taking forever.  But in my source Netezza database, it takes seconds.

For example:

Netezza 6:

SELECT COUNT(*) FROM DATABASENAME..MYTABLE

Oracle 11g:

SELECT COUNT(*) FROM MYTABLE

SQL Server 2012:

SELECT COUNT(*) FROM DATABASENAME.[dbo].[MYTABLE]

Netezza is an appliance that is designed to excel at large table scans, so that's why you're getting such fast results on that system.

For your SQL Server, you can greatly speed up the row count by querying from the sys.dm_db_partition_stats DMV.

SELECT s.name AS [Schema], o.name AS [Table], SUM(p.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats p JOIN sys.objects o
ON p.object_id = o.object_id JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE p.index_id < 2
AND o.object_id = object_id('MyTable')
GROUP BY o.name, s.name;

In a high transaction environment, this DMV is not guaranteed to be 100% accurate.  But from your question, it sounds like you are just doing row counts to verify each table after your migration, so this query should work for you.


Syntax to not write single statement to binlog? I know you can SET SQL_LOG_BIN=0; to disable writing your session to the binlogs.

I was wondering if there was some single serving query syntax to prevent just that one query from being written w/o setting that session var to 0 and back to 1 afterwards?

If you do the following:

connect to mysql
perform SET sql_log_bin=0;
execute the query (INSERT, UPDATE, or DELETE)
close the DB connection

then there is no need to SET sql_log_bin=1; afterwards
As an alternative, you may want to try this:

open a second DB Connection
set the SET sql_log_bin=0; (this done once in the lifetime of the second DB connection)
keep that second DB Connection open
use only this second DB connection for writes that are not be recorded in the binary logs

Give it a Try !!!
UPDATE 2012-01-06 14:55 EDT
Here is something interesting you may not have realized: Only those SUPER privilege can set one's own session to disable binary logging.
According to the MySQL Documentation on SUPER:

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL
or mysqladmin kill to kill threads belonging to other accounts (you
can always kill your own threads), PURGE BINARY LOGS, configuration
changes using SET GLOBAL to modify global system variables, the
mysqladmin debug command, enabling or disabling logging, performing
updates even if the read_only system variable is enabled, starting and
stopping replication on slave servers, specification of any account in
the DEFINER attribute of stored programs and views, and enables you to
connect (once) even if the connection limit controlled by the
max_connections system variable is reached.
To create or alter stored functions if binary logging is enabled, you
may also need the SUPER privilege, as described in Section 18.7,
“Binary Logging of Stored Programs”.

Having users with SUPER privilege gives them additional fire power that they may not need or may want to abuse.


AlwaysOn - Problem with adding secondary replicas I installed AlwaysOn on my laptop (testlab) with three VMs, of which one is a domain controller and two are hosting SQL Server instances (planned to be one primary and one secondary replica). Everything went well until I wanted to add the secondary replica to the Availability group.

Please find a screenshot attached. I have already changed the port to 1433 in the TCP/IP settings and have also added an exception for SQL Server in the Windows Firewall. Both of the servers are in the same cluster network. Not sure what else I am missing here.

Did you add the browser service to the firewall? UDP 1434. Because you're using instance names so they have to get resolved. Otherwise remove the instance name to use that 1433 port.


Slow running query using date range on non-clustered index column I have a very slow running query, and I've isolated the problem down to the following SQL.

SELECT
  ...
FROM
    TableA a(nolock)
    inner join TableB b with(nolock) on (b.Id = a.Id AND b.Date>= isnull(@timestamp_start, '17530101') AND b.Date < isnull(@timestamp_end, '99991231'))

a = 2million+ rows
b = 2million+ rows
b.Id => Index, unique, non-clustered
a.Id => Index, unique, non-clustered
b.Date  => Index, non-unique, non-clustered

This takes several minutes without returning a result even for a single day date range.

UPDATE:

The estimated execution plan on the production server is showing me this, which shows that a complete scan of a huge table (TableA) is occurring near the beginning of the query, rather than the TableB.date range being used to filter in the WHERE. Why is this happening and how can I force the query to filter first on my date range?

The plan relates to this query, which in detail is:

SELECT
  ...
FROM
    TableA a(nolock)
    inner join TableB b with(nolock) on (b.Id = a.Id AND b.Date>= isnull(@timestamp_start, '17530101') AND b.Date < isnull(@timestamp_end, '99991231'))
    inner join TableC c with(nolock) on c.Id2 = s.Id2 and c.Id3 = a.Id3 and c.Id = b.Id
    inner join TableD d with(nolock) on (a.DealId = d.Id and d.Id3 = s.Id3 AND (@myparam is null OR d.ProviderName = @myparam))
    inner join TableE e with(nolock) on (e.Id = d.Id AND (@myparam2 is null OR e.Id = @myparam2))

You should look at the actual plan, not to the estimated one that you posted here.

Your plan uses NL with lookups in the clustered index + sorts, so it's clear the server estimated small number of rows for your filters. When you look at the actual plan and instead of 100-1000 rows see there 500.000 rows go out from your first index seek, there is a problem. It means that full scan should be used, and hash join instead of merge.

So update your post with the actual plan please


Get value of two instances of a secondary table I have two tables, users and permissions.  Users have a username.  Permissions have a name (e.g. cards, pages, players) and a type (e.g. read only, read/write) and a foreign key relationship to users.  Both tables have an basic integer primary key.

For various reasons I would like to query all user to see the status of their cards permission and pages permission at the same time.  My ideal output would be something like...

|------------------------------------------------|
| Username | Cards Permission | Pages Permission |
|------------------------------------------------|
|   Bob    |   Read / Write   |   Read Only      |

I have already thought of two ways I can do this but neither is ideal.

First, I can query twice, each time collecting User.Id, User.Username, Permission.Type.  The first for cards permission and the second for pages permission.  I can then correlate the two queries in the application.

SELECT u.Id, u.Username, p.Type
  FROM User u
  INNER JOIN Permission p ON p.UserId = u.Id
  WHERE p.Name = '?';

Second, I can query once, collecting User.Id, User.Username, Permission.Name, Permission.Type.  The query would join the permissions table and take all permissions whose name is Cards or Pages (essentially I'd get 2 times as many records, each User.Id would show up twice).  I could then correlate the results in the application.

SELECT u.Id, u.Username, p.Name, p.Type
  FROM User u
  INNER JOIN Permission p ON p.UserId = u.Id
  WHERE p.Name = 'cards' or p.Name = 'pages';

Is there any query I can use that would give me the output I desire?  I'm asking mainly for my own edification.  If I JOIN the permissions table twice I end up with a cross product and run out of memory (there are 1,000,000 users and 10,000,000 permissions).

UPDATE: Detailed Schema

User
----
Id bigint not null identity primary key
Username varchar(64) not null

Permission
----------
Id bigint not null identity primary key
UserId bigint not null foreign key
Name varchar(64) not null
Type int not null
-Index non-clustered non-unique Id, UserId, Name

It looks like you are just trying to pivot some data.

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then p.Type else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then p.Type else null end)
  --, [Players Permission]=max(case when p.Name='Players' then p.Type else null end)
  from user u
    inner join Permission p ON p.UserId = u.Id
  group by u.Id, u.username;

or if we want the name of the type for type integer, something like:

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then t.Name else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then t.Name else null end)
  --, [Players Permission]=max(case when p.Name='Players' then t.Name else null end)
  from user u
    inner join Permission p ON p.UserId = u.Id
    inner join [Type] t ON p.Type = t.Id
  group by u.Id, u.username;

If your nonclustered index on permissions is (Id, UserId, Name), it's not doing you any favors. unique nonclustered (UserId, Name, Type) or nonclustered (UserId, Name) include (Type) would be more useful.


Availability Strategies in MS SQL Server 2008 I need some assistance in developing a backup strategy for my SQL Server deployments. Currently I have two classes of customer: Hosted customers, whose data is housed in our datacenter, and On Premises customers who host their own data locally.

We are currently exploring whether to use Log Shipping or Database Mirroring for our Hosted customers as data loss of up to 15 minutes is acceptable (Log shipping interval would be 15 minutes, Mirroring would be in High Performance Mode), but we are still conducting testing on that.

My question is, if we choose a Mirroring strategy for our hosted customers, I doubt we would be able to do that with our our OnPrem customers - they will likely be using log shipping. Is it possible to have multiple recovery strategies database-by-database, or would I need a new instance of SQL Server for the OnPrem customers?

EDIT - Changed title to "Availability Strategies" because, well, this is more about availability than recovery.

Database Mirroring isn't a database recovery strategy.  It's a high availability strategy.  If you can afford to loose up to 15 minutes worth of data then you take transaction log backups every 15 minutes.

If you need to look into high availability that's a totally different conversation, but it doesn't remove the need to be taking transaction log backups.

As for your self-hosted customers, they are responsible for setting up their own backup solution unless your contract states that you'll work with them to set one up for them.


Fill missing dates within groups I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date to each station. I'm working with PostgreSQL 10.9

This is my table:

CREATE TABLE stations (station_id text, value integer, date date);
INSERT INTO stations (station_id, value, date) VALUES
('001', 10, '2014-01-01'),
('001', 30, '2014-01-03'),
('002', 40, '2014-01-01'),
('002', 50, '2015-01-02'),
('003', 20, '2014-01-01'),
('003', 10, '2015-01-02'),
('003', 70, '2015-01-04');

I also have a table holding unique stations with identifiers.

And I want something like this:

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| 001     | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| 001     | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| 002     | NULL  | 2014-01-03 |
| 002     | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| 003     | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

Following some DBA Exchange (questions)1, I tried a combination of a LEFT JOIN with a LATERAL JOIN:

WITH complete_dates_station AS (
    select station_id,
           generate_series(DATE '2014-01-01', DATE '2014-12-31', INTERVAL '1 day')::DATE as dt
    FROM stations
    GROUP by station_id
    ), temp_join AS (
        SELECT station_id,
               dt,
               s.value
        FROM complete_dates_station
            LEFT JOIN LATERAL (
                SELECT s.value
                FROM stations s
                WHERE s.station_id = complete_dates_station.station_id
                AND s.date = complete_dates_station.dt
                ORDER by s.station_id, date desc
                LIMIT 1) as s on TRUE
             ORDER BY station_id, dt
         ) SELECT * from temp_join

This works like a charm, but this join is really slow for my complete table, which has more than 2M rows and the date range goes over 18 years (I stopped after 4 hrs of running). I tried a simpler approach by using a regular LEFT JOIN, but the table outputs the not-joined groups as missings:

WITH complete_dates_station AS (
    SELECT station_id,
           generate_series(date '2014-01-01', date '2014-12-31', interval '1 day')::date as dt
    from stations
    GROUP BY station_id)
SELECT s.station_id,
       c.dt,
       s.value
FROM complete_dates_station c
    left outer join stations s
    on c.station_id = s.station_id and
    c.dt = s.date;

which yields the following:

| station | value | date       |
|---------|-------|------------|
| 001     | 10    | 2014-01-01 |
| NULL    | NULL  | 2014-01-02 |
| 001     | 30    | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 002     | 40    | 2014-01-01 |
| 002     | 50    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| NULL    | NULL  | 2014-01-04 |
| 003     | 20    | 2014-01-01 |
| 003     | 10    | 2014-01-02 |
| NULL    | NULL  | 2014-01-03 |
| 003     | 70    | 2014-01-04 |

There is any way to optimize the first query, or use a simpler approach to fill my station gaps in the second query? I tried already using multicolumn indexes in my source table, but the query is still taking a lot of time.

You also have a table holding unique stations with identifiers, Could look like this:

CREATE TABLE uniq_stations (station_id text);
INSERT INTO uniq_stations VALUES
('001'),
('002'),
('003');

There will be more columns, which are irrelevant for us.

This should be much faster then:

SELECT station_id, s.value, date
FROM   uniq_stations u
CROSS  JOIN (
   SELECT generate_series (timestamp '2014-01-01'
                         , timestamp '2014-01-04'
                         , interval  '1 day')::date
   ) d(date)
LEFT   JOIN stations s USING (station_id, date)
ORDER  BY station_id, date;  -- optional

db<>fiddle here

You do not need a LATERAL join at all, the date series is the same for every station. Only a CROSS JOIN to build the complete Cartesian product of stations and days, then a LEFT [OUTER] JOIN to existing combinations in table stations (an unfortunate table name for its content, btw.). LATERAL joins are great, when needed. But plain joins are faster.

Also, this fills in stations with all days missing, which would not work at all without uniq_stations. You may or may not have such cases.

One of the expensive pieces in this puzzle is identifying unique stations. A task we can skip completely if we can use the added uniq_stations providing what we need. Else we might use DISTINCT ON or a recursive CTE to make use of a matching index. See:

Optimize GROUP BY query to retrieve latest row per user
Select first row in each GROUP BY group?

Still more expensive than reading unique rows from a table, but much faster already than what you had. Which was a grand waste of CPU cycles, frankly.

Finally, a multicolumn index on stations (station_id, date) should deliver top notch performance even with a big table stations. (Using a higher percentage of rows from that table makes the index less important.)

There is a reason I use generate_series (timestamp, timestamp, interval):

Generating time series between two dates in PostgreSQL

Related:

Filling in missing dates in record set from generate_series()
Join a count query on a generate_series in postgres and also retrieve Null-values as “0”

Aside: Your station_id should probably type integer. Faster than text, and smaller, too if numbers go beyond 999.


How to find foreign keys that don't have corresponding indexes? I have installed Oracle database 11g R2 with a customized application. In my environment we are experiencing locking on tables. I want to check if there are  foreign keys in my database without the corresponding indexes. Is there any query or procedure to find un-indexed foreign keys?

Oracle SQL Developer ships with a report to find these unindexed columns.

Here's the SQL behind this report:

select a.owner            "Owner",
                                 a.table_name       "Table_Name",
                                 a.constraint_name  "Constraint_Name",
                                 a.columns          "Foreign_Key_Column_1",
                                 b.columns          "Foreign_Key_Column_2",
                                 a.owner             sdev_link_owner,
                                 a.table_name        sdev_link_name,
                                 'TABLE'             sdev_link_type
                            from
                            ( select a.owner, substr(a.table_name,1,30) table_name,
                                 substr(a.constraint_name,1,30) constraint_name,
                                   max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
                                from sys.dba_cons_columns a,
                                     sys.dba_constraints b
                               where a.constraint_name = b.constraint_name
                                 and a.owner = b.owner
                                 and (:OWNER is null or instr(b.owner, upper(:OWNER))>0)
                                 and b.constraint_type = 'R'
                               group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
                            ( select table_owner,
                                     substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
                                   max(decode(column_position, 1,substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
                                   max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
                                from sys.dba_ind_columns
                               group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
                      where a.owner      = b.table_owner (+)
                        and a.table_name = b.table_name (+)
                        and substr(a.table_name,1,4) != 'BIN$'
                        and substr(a.table_name,1,3) != 'DR$'
                        and b.table_name is null
                        and b.columns (+) like a.columns || '%'
                      order by a.owner, a.table_name, a.constraint_name


Isn't a tab (char(9) ) whitespace in the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3)? If there is any sane reason to ignore trailing blanks in varchar comparisons, why doesn't that apply to tab-characters too ?

On SQL Server I get

SELECT
    case when '' = ' ' then 'true' else 'false' end,
    case when '' = CHAR(9) then 'true' else 'false' end

yields

---- -----
true false

Is this a problem of the ANSI specification or a problem of the SQL Server implementation?

Edit:

I do not have access to the text of the specification. I only see it mentioned in this answer. I can't tell if it addresses only blanks or if it refers to whitespace in some vague sense.

The same code above on MySQL 5.5 gives the same result

Tab isn't "space", it is a character.
The same applies to hard/non-breaking space (char(160) in "normal" collations)

The ANSI-92 standard states in 8.2. item 3.a

  If the length in characters of X is not equal to the length
                in characters of Y, then the shorter string is effectively
                replaced, for the purposes of comparison, with a copy of
                itself that has been extended to the length of the longer
                string by concatenation on the right of one or more pad char-
                acters, where the pad character is chosen based on CS. If
                CS has the NO PAD attribute, then the pad character is an
                implementation-dependent character different from any char-
                acter in the character set of X and Y that collates less
                than any string under CS. Otherwise, the pad character is a <space>.

CS is "collating sequence". I'll assume no collation uses tab or non-breaking spaces as padding characters. So space it is...

Note that LIKE is section 8.5 (I mention this because of your previous question)


how to delete all rows with empty field via function/trigger combo in Postgres v 9.3? In PostgreSQL 9.3 I am trying to write a trigger function that will be called after updates to table test2 and that will delete rows of test2 where field "email" is empty. Here's my attempt:

CREATE OR REPLACE FUNCTION clean_emp() RETURNS trigger AS
$$
BEGIN
DELETE FROM test2 WHERE email=null;
return test2;
END;
$$
LANGUAGE plpgsql VOLATILE;

Then I set up the trigger like so:

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2 FOR EACH ROW EXECUTE PROCEDURE clean_emp();

This gives me an error when I then do an update on table test2:

update test2 set name = name || 'fesgses';
ERROR:  column "test2" does not exist

And here's my data table for completeness:

  name  |    email
--------+-------------
 hi     | hiemail
 shoe   |
 shoe2  |
 cathy  |

If anyone can give me some pointers on where I'm going wrong, I'd really appreciate it. Thank you.

There are several things wrong with this trigger.

First: your delete statement. You can't compare NULL using =. You need to use IS NULL:

DELETE FROM test2 WHERE email IS NULL;

Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for."

So return test2; should be return new;.

Third: you created a row level trigger, which is going to be very bad for performance. As you are not dependent on the actual values that are changed by the update, a statement level  trigger will be much more efficient, because it only fires once for each UPDATE statement rather than once for each row that has been changed.

In a statement level trigger the return value is ignored, so the suggested return new; from above becomes a return null; as there is no new or old record available in a statement level trigger.

Putting it all together:

CREATE OR REPLACE FUNCTION clean_emp()
  RETURNS trigger AS                                                                           $$
BEGIN
  DELETE FROM test2 WHERE email IS NULL;
  return NULL; -- as we are now using a statement level trigger, null is fine
END;
$$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2
  FOR EACH STATEMENT --<< fire only once for each statement, not row
  EXECUTE PROCEDURE clean_emp();

If you also want to disallow empty values ( '' is something different than 'NULL') you would need to change the condition to where coalesce(email, '') = '')

But the trigger approach is wrong to begin with: you should declare the email column as NOT NULL and then nobody will ever be able to put NULL values into that column and therefore you don't need the trigger at all.


DBForge can't connect with MySQL server, server is running I recently installed the latest version of MySQL Server.

I initialized it with --initialize-insecure so I have a "root" user with no password set.

The server is running, with the command mysqld --console I am able to start it.

I can connect to it through MySQL workbench just fine, port 3306, user "root", password not set

However if I try to use DBForge v7.2.34 the connection is refused, how can I solve this or at least debug the problem?

After hours of research I figured out that it was a problem of user authentication, I had only a user 'root@localhost'. Turns out I needed an user 'root'@'127.0.0.1'

After all of that, I discovered my version of DBforge does not support MySQL server v8.0


ERROR: please confirm the keyfile is match and keyring plugin is loaded I want to create a copy of my Production DB to a new instance.

There is a innobackupex running on the prod every night looks like this:

innobackupex --user=username --password=password --keyring-file-data=/var/lib/mysql-keyring/keyring --target-dir=mybackup-path

I copied the mybackup-path contents to my new instance and then prepared it by as following:

xtrabackup --prepare --keyring-file-data=/var/lib/mysql-keyring/keyring --target-dir=mybackup-path-in-new-server

then copy over files --copy-back and chowned the data folder to mysql & everything is working

BUT I get "please confirm the keyfile is match and keyring plugin is loaded".

I have added updated my.cnf to early-plugin-load but still nothing

early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

When I start mysql, and run SHOW PLUGINS, Keyring is ACTIVE,

My both instances are the same and both have ubuntu and Percona Server 5.7

I'm sure I'm missing something, and I believe it is related to keyring,
can someone help?

I have solved my issue:

Make sure that the backup taken by xtrabackup and the keyring file in /var/lib/mysql-keyring/keyring are from the same server.

Also after copying the keyring file to the destination server, make sure to chown mysql:mysql to it.

The Keyring file should be copied as is not the contents of the file.

To verify if things are working, you can create a new DB and some new tables, also insert some data, that are encrypted then run the following

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';

You need to get the result tables. Now try to select from those tables, you should see the data inserted.


Understanding the use of bitwise operators in MySQL? Can someone explain the purpose of using bitwise operators(like BIT_OR) in MySQL queries. For example, if have a table such as following:

What is the purpose of aggregate operation like:

SELECT name, value FROM table GROUP BY name HAVING BIT_OR(value) = 0;

As an aggregate function the BIT_OR is evaluated as a bitwise OR of all the values in the group. If it equates to 0, none of the value have been set.


Can PostgreSQL UPDATEs in autocommit mode deadlock? Is it possible for UPDATEs in PostgreSQL to deadlock when in autocommit mode? If so, how, and why? (Or, why not?)

That is certainly possible if the update affects more than a single table row.
This can come to pass in two ways:

The query modifies more than a single row in the target table.

The updated table has a foreign key constraint to another table. In that case, you may end up with a FOR KEY SHARE lock on the referenced table.

The deadlock can happen if your statement successfully locks a couple of rows and then blocks on a lock on some other row.


DB Design Payments Table I'm working on a Desktop application that uses a DB to store information i.e (customers, houses, proprietors, payments). So the application must accept payments from customers to company, company to customers(in case of a refund), company to proprietor, proprietor to company. At this moment i'm thinking about this Payments table(there is more columns like dates, quantity).

| id            | int(11)     | NO   | PRI | NULL    | auto_increment |

| type          | varchar(20) | YES  |     | NULL    |                |

| reference_id  | int(11)     | YES  |     | NULL    |                |

With this table i was going to use id as the unique payment id, type to tell if it's a payment from company, proprietor, customer,etx.. and the reference_id is the unique id of the proprietor, customer, etx (the reason i store a reference_id is to find all the payments sent or received to that person)

For some reason it feels like an ugly design not only because i'm not using many-to-many relationship but also it gets kinda tricky to fetch data because you have to specify the type.

What would be a better design?

A better design would be to have a single table to hold all the parties to a payment.  If necessary, sub-type this table so companies, proprietors and customers have their own, unique sets of columns (or a separate, related table).

The payments table will then have two foreign keys to Parties, let's call them PaymentFromPartyID and PaymentToPartyID, along with amount, date etc.  This way all permutations of money transfer can be tracked.

To find all the monies a person has received use

select
    sum(p.Amount)
from Payments as p
inner join Parties as y
    on y.PartyID = p.PaymentToPartyID
where y.Name = 'The person/company/proprietor of interest';

Additional Detail

A party is anyone or anything who has in interest in a payment.  This would be a proprietor, customer or company, distinguished by a type:

Parties(
    PartyID
    PartyName
    PartyType     -- customer or company or proprietor
    <customer-fields>
    <proprietor-fields>
    <company-fields>)

A payment relates a payer (of any type) to a payee (of any type):

Payments(
    PaymentFromPartyID  -- FK to Parties.PartyID
    PaymentToPartyID    -- FK to Parties.PartyID
    Amount
    Date
    etc.)

The IDs used in From and To says in which direction the money flows.  There is no need to have any information about a party in the Payments table, such as a type, other than the two PartyIDs.

To get all the payments made to customers (for example) add Parties.PartyType = 'Customer' to the query given above.

The Payments_Track table you list has all the attributes I list here (bar a few surrogate IDs) but is normalised.


Deleting old postgresql log files (Ubuntu) In a Ubuntu machine hosting a postgresql database, I have 16G space taken up at /var/lib/postgresql/9.3/main/pg_log. Apparently, log files from many months ago are being stored as well. I manually deleted a few to get rid of my disk full error. What's a robust way to delete old postgresql logs every week? Anything I can do in postgresql.conf to automate this process?

Currently I have the following in postgresql.conf:

#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 100MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

I'm thinking I'll enable log_rotation_age and set its value to 30d. But will this auto delete older log files?

You can use the configuration below;

log_truncate_on_rotation = on
log_rotation_age = 1d
log_filename = 'postgresql-%a.log'
log_rotation_size = 0  #just rotate daily

This says create a log file with a name like 'postgresql-Mon.log' and when rotation occurs override the old file with the same name.


Why is OracleDB locking up in Docker? I am trying to set up an Oracle database in Docker, and once the database is up and running, I can exec into sqlplus just fine, but if I try to connect with another application like Oracle SQL Developer or SQuirreL, either it will stall out while trying to connect, or it will manage to connect and then all future queries will stall. What is wrong with this?

Here is how I set up the container:
I used the docker image available from Oracle on Docker Hub.

Here is the command I ran to create the container:

docker run --name someName -p 1521:1521 -p 5500:5500 -e ORACLE_SID=SOMESID -e ORACLE_PDB=SOMEPDBNAME -e ORACLE_PWD=12345 -it 12a359cd0528

Here are the commands I ran once the container was up and running:

docker exec -it npsIG /bin/bash
sqlplus / as sysdba
alter session set "_ORACLE_SCRIPT"=true;
create user username identified by 12345;
grant dba to username;

At this point, sometimes I can connect on the created user, sometimes it just stalls out. If I manage to connect on the created user, no further queries will process.

IF that is actually your script, then after you connect "/ as sysdba", then sqlplus is simply waiting for input.  It doesn't see the 'alter session', etc, because those aren't being redirected into sqlplus.  Rather, they are simply treated as the next command for the shell to process after sqlplus exits.

Try this:

sqlplus / as sysdba <<EOF
alter session set "_ORACLE_SCRIPT"=true;
create user username identified by 12345;
grant dba to username; exit
EOF

BTW, you should not be using 'as sysdba' if you can accomplish the same thing with a less privileged user, such as SYSTEM.  Connecting to oracle as SYS is like connecting to Linux as root.  You are running with scissors, knives, and blowtorches, all without a net.


Postgres DB user permission I'm logged in as a user in postgres with PgAdmin tool in Linux connecting remotely but I don't think the user has enough rights. I'm a MySQL experienced user but not so much in Postgres. When I select a table when logged in as the user I get

An error has occurred:

  ERROR:  permission denied for relation acs_activities

how do I add my user "gainpm" to have access to all tables in "projop" database?

You should log in as the table owner (the user you used to create the tables) and then grant the necessary privileges to your user:

grant select on acs_activities to your_user;

If you want to do that for all tables, you can use:

grant select on all tables in schema public to you_user;

Or maybe you created them as the superuser, but your regular user should be the real owner, then you should do:

alter table acs_activities owner to your_user;


Calculate difference between SUM over last 30 days and the SUM for 30 days preceding this period (days 30-60) in Postgres Every day we store the number of jobs posted for hundres of thousands of companies.

We need to query:

The SUM of jobs_posted for the last 30 days for each company;
The DELTA between jobs posted in the last 30 days to the previous 30 day period for each period.

Is there a faster, more efficient way in Postgres 9.6+ to create this query and are there indexes that would be benefitial?

CREATE TABLE    metric_data (
    id              integer PRIMARY KEY,
    company_id      integer NOT NULL,
    jobs_posted     integer NOT NULL,
    recorded_at     timestamp
);

WITH period_previous AS (
    SELECT      metric_data.company_id,
                SUM(metric_data.jobs_posted) AS jobs_posted
    FROM        metric_data
    WHERE       metric_data.recorded_at BETWEEN CURRENT_DATE - INTERVAL '30 day' * 2 AND CURRENT_DATE - INTERVAL '30 day'
    GROUP BY    metric_data.company_id
), period_current AS (
    SELECT      metric_data.company_id,
                SUM(metric_data.jobs_posted) AS jobs_posted
    FROM        metric_data
    WHERE       metric_data.recorded_at > CURRENT_DATE - INTERVAL '30 day'
    GROUP BY    metric_data.company_id
)
SELECT          period_current.company_id,
                period_current.jobs_posted AS current_jobs_posted,
                period_current.jobs_posted - period_previous.jobs_posted AS delta
FROM            period_current
JOIN            period_previous
ON              period_current.company_id = period_previous.company_id

Especially the CTEs might be a problem. So it needs to be rewritten anyway. And while at it, I'd rewrite this to use conditional aggregation. That should get the data in "one run", without two temporary results and a join on them.

SELECT metric_data.company_id,
       sum(CASE
             WHEN metric_data.recorded_at > current_date - INTERVAL '30 day' THEN
               1
             ELSE
               0
           END) current_jobs_posted,
       sum(CASE
             WHEN metric_data.recorded_at > current_date - INTERVAL '30 day' THEN
               1
             ELSE
               -1
           END) delta
       FROM metric_data
       WHERE metric_data.recorded_at >= current_date - INTERVAL '60 day'
       GROUP BY metric_data.company_id;

(You might want to check the comparison operators (>=, >, etc.), if I picked the ones you wanted.)

An index on recorded_at and company_id should support it.

CREATE INDEX metric_data_recorded_at_company_id
             ON metric_data(recorded_at,
                            company_id);

Like that the entire query should be answerable from the index alone, without using the table at all. But maybe you also want to try it with an index just on recorded_at alone.


MySQL: Loop over cursor results ends ahead of schedule I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that:

EDIT: Sorry, stupid mistake. The trigger is executed after deleting on "one_table". This is not the same table in which I perform the 'delete' or 'update' operations inside the trigger's code (see second listing below)

create trigger my_trigger after delete on one_table
for each row
begin

declare my_value int;
declare num_rows int default 0;
declare done int default false;
declare my_cursor cursor for select value from table where condition;
declare continue handler for sqlstate '02000' set done = 1;

open my_cursor;
select found_rows() into num_rows;

-- This is just for debugging
insert into log_table(key, value) values('foo', num_rows);

if num_rows > 0 then:
  repeat
    fetch my_cursor into my_value;
    -- Do stuff
  until done end repeat;
end if;

close my_cursor;

end

The loop should be executed 11 times, because the query returns 11 values. This is checked by the 'insert' clause. The var 'num_rows' is equal to 11. But the problem is the loop is only executed 3 times.

These numbers (11, 3) are not important. If I change the query to return a different amount of results, the problem remains: the loop ends before scheduled.

Does it make any sense? I may do something inside the loop (the 'do stuff' part) that causes the end of the loop. It's the only thing that sounds logic for me.

EDIT: I include the 'Do stuff' part. The problem is probably caused because, inside this part, some 'select' sentences are executed. When one of those sentences returns an empty result, the defined handler is executed, setting done to 'true' and breaking the loop.

The "Do stuff" part is like this:

select some_value into some_field from other_table where some_conditions;

if (some_field is null) then
  delete from my_table where my_condition;
else
  update my_table set key1 = value1 where condition1;

Thanks in advance, and best regards,

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table
for each row
begin

declare my_value int;
declare num_rows int default 0;
declare done int default false;
declare my_cursor cursor for select value from table where condition;
declare continue handler for sqlstate '02000' set done = 1;

open my_cursor;

-- This is just for debugging
insert into log_table(key, value) values('foo', num_rows);

cursor_loop:repeat
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value;
    -- Do stuff
    until done
end repeat;

close my_cursor;

end

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table
for each row
begin

declare my_value int;
declare num_rows int default 0;
declare done int default false;
declare my_cursor cursor for select value from table where condition;
declare continue handler for sqlstate '02000' set done = 1;

open my_cursor;

-- This is just for debugging
insert into log_table(key, value) values('foo', num_rows);

cursor_loop:repeat
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value;
    -- Do stuff

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat;

close my_cursor;

end

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.


Oracle Wallet Connection setup for SSIS How to configure SSIS package to connect to oracle using oracle wallet? Specifically for using OLEDB to connect to oracle and load data to oracle tables.

When using OLEDB, I believe you should choose the Integrated Security option which would have your connection string look like this:

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;

Since you're using wallet, you have no username and passwords.

This is assuming your wallet is configured properly in the first place, meaning test with sqlplus is positive.

I hope this helps.


SQL Server super slow, no queries My DEV instance of SQL Server is super slow for some reason from time to time. It doesn't happen all the time, just sometimes and there are queries that take a very long time. But it's not just that, sometimes just expanding the "Stored Procedures" or the "Tables" folder in the Management Studio takes several seconds. Sometimes doing a simple query also takes several seconds.

I'm working on a small database (~300mb) and in a new laptop with an i7, 8GB of memory and a SSD drive.

Part of the time the SSD goes to 100% when performing slow. However, I don't see a slow down in any of the other applications I use.

I believe the problem is only in one database, but I ran DBCC CHECKDB already and everything was fine. The LOG file is pretty small and both the DATA and the LOG files have space available, so it's not expanding.

It's not just a problem with the Management Studio as the ASP.NET application I'm working on works slow too sometimes. Also, it's not a problem with indexes or anything like that since I'm running a much bigger copy of the database and it works fine (needless to say, such a small database shouldn't have that problem even if it hasn't any indexes).

Any ideas?

Some configurations I've already checked:

Max server memory: 2.5GB
Auto-close/auto-shrink db: False
Auto Create/Update Statistics: True
DB compatilibity level: MSSQL 2008 (100)
Recovery model: Full
Allow snapshot isolation

UPDATE:

I ran Blitz and couldn't find the problem either, these message appear and might or might not be related:

At least one NUMA node is reporting THREAD_RESOURCES_LOW in sys.dm_os_nodes and can no longer create threads.
7 forced grants reported in the DMV sys.dm_exec_query_resource_semaphores, indicating memory pressure has affected query runtimes.

And running BlitzFirst:

THREADPOOL (avg ms per wait): 130832.4
RESOURCE_SEMAPHORE (avg ms per wait): 17202.5

If you want a fast, easy, free health check that flags common issues
  in seconds, and for each warning, gives you a link to a web page with
  more in-depth advice.

First steps for you:

Sp_Blitz
SP_BlitzFirst


MySQL table proper indexes for performance optimization I have a database table (created by someone else). This table consists over billions of records and records are being inserted every second or so.
I need to optimize this table and hence the queries to fetch stuffs faster. Following is the table ProductCatalog structure.
id                  int(10)
SerialNumber        varchar(20)
BasePrice           decimal(4,1)
BatchCode           tinyint(3)
Type                varchar(5)
ItemCode            varchar(5)
ArrivalDate         datetime
InsertTimestamp     int(10)
BrandID             tinyint(3)
CompanyID           tinyint(4)
Model               varchar(10)
Description         text

Here is the script to create table
CREATE TABLE `ProductCatalog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SerialNumber` varchar(20) DEFAULT NULL,
  `BasePrice` decimal(4,1) DEFAULT NULL,
  `BatchCode` tinyint(3) unsigned DEFAULT NULL,
  `Type` varchar(5) DEFAULT NULL,
  `ItemCode` varchar(5) DEFAULT NULL,
  `ArrivalDate` datetime DEFAULT NULL,
  `InsertTimestamp` int(10) unsigned NOT NULL,
  `BrandID` tinyint(3) unsigned DEFAULT NULL,
  `Model` varchar(10) NOT NULL DEFAULT 'RX209',
  `Description` text,
  PRIMARY KEY (`id`),
  KEY `SerialNumber` (`SerialNumber`,`ArrivalDate`,`ItemCode`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

Here are the sample insert queries.
insert into `productcatalog` (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`, `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`) values('1','345618761','23','25','ACC','ABCD','2021-02-14 12:24:44','1613371259','37','RX209',NULL);
insert into `productcatalog` (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`, `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`) values('2','345618761','24','25','RAF','ABCD','2021-02-15 12:55:17','1613373031','45','GA317',NULL);
insert into `productcatalog` (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`, `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`) values('3','569014575','43','21','DAT','TPRS','2021-02-13 12:56:34','1613373082','34','PX452',NULL);

There could be many many entries for same SerialNumber and ItemCodein Different ArrivalDate
Initially there were three indexes
1. id => Primary
2. SerialNumber
3. ArrivalDate

Following are the queries i run against this table.
SELECT *
FROM ProductCatalog
WHERE SerialNumber='1234567890'
  AND ItemCode!="ABCD"
ORDER BY id DESC LIMIT 1; //This Query Seems slower

SELECT BasePrice
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1 //This Query Seems Slower

SELECT *
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND (ArrivalDate>='2019-01-01 00:00:00' AND ArrivalDate<='2020-12-31 23:59:59')
  AND ItemCode='ABCD'
ORDER BY ArrivalDate ASC //This query looks ok

SELECT BatchCode
FROM ProductCatalog
WHERE SerialNumber='123456789'
  AND ItemCode!="ABCD"
  and ItemCode!="PQRS"
  AND ItemCode!="MNOP"
ORDER BY ID Desc LIMIT 1 //This Query Seems Slower

Then I changed the indexes such that we have only two indexes now. The primary and the composite one.
1. id => Primary
2. SerialNumber, ArrivalDate, ItemCode

Mysql Information
MySQL Version 5.7
Engine: InnoDB

Problem
The results are still not that satisfactory.

Are the indexes I changed correct to get performance gain?
Are the order of columns in index Correct?
Column SerialNumber contains 16 digit numeric value, shall i changed it to int instead of varchar to gain performance?

"billions of records" and id int(10) unsigned NOT NULL AUTO_INCREMENT -- Beware!  That will top out at about 4 billion.
To speed up the inserts, see if you can batch them something liek this:
insert into `productcatalog`
    (`id`, `SerialNumber`, `BasePrice`, `BatchCode`, `Type`, `ItemCode`,
        `ArrivalDate`, `InsertTimestamp`, `BrandID`, `Model`, `Description`)
    values
    ('1','345618761','23','25','ACC','ABCD','2021-02-14 12:24:44','1613371259','37','RX209',NULL),
    ('2','345618761','24','25','RAF','ABCD','2021-02-15 12:55:17','1613373031','45','GA317',NULL),
    ('3','569014575','43','21','DAT','TPRS','2021-02-13 12:56:34','1613373082','34','PX452',NULL);

For the SELECTs, then indexes (with the columns in the order specified) should help:
INDEX(SerialNumber, ID, ItemCode, BasePrice)  -- for queries 1,2
INDEX(ItemCode, SerialNumber, ArrivalDate)    -- for query 3

Query 1 will (I think) use SerialNumber, ID from the first index and stop after one or two rows.
Query 2 is "covering", so even it needs to scan several rows, it would have to bounce between the index's BTree and the data's BTree.
Query 3 -- The = columns need to be first and the "range" column (Arrival Date) last.
The following changes won't improve performance; I just think they are clearer.  Instead of
AND ItemCode!="ABCD"
AND ItemCode!="PQRS"
AND ItemCode!="MNOP"

AND (ArrivalDate>='2019-01-01 00:00:00' AND ArrivalDate<='2020-12-31 23:59:59')

I prefer these:
AND ItemCode NOT IN ("ABCD", "PQRS", "MNOP")

AND ArrivalDate >= '2019-01-01'
AND ArrivalDate  < '2019-01-01' + INTERVAL 1 YEAR

The following will explain some of my suggestions:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql
This seems odd for a "price":  decimal(4,1)


SQL Server: Enable Full-Text Search on model Database I want to use the Microsoft Data-Tier Application framework and its associated .Net API to manage the schema of several SQL Server databases, including the model database. However, when I try deploy an updated schema to the model database (using either a SQLCMD script or the DacServices.Deploy(...) method), I receive the following error...

  Cannot use full-text search in master, tempdb, or model database.

Is there any way around this?

EDIT

So, I was going over the generated SQLCMD script with my boss, and he saw this...

IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';

GO

Why would that be needed for a schema deployment? It would be easy enough to strip that block out of the script, but what if I want to use the DacServices.Deploy(...) method to update the schema? I don't see anything in the DacDeployOptions class that seems like it would affect this deployment option.

Edited: It looks like the answer is:  No, you cannot create a full-text index in a system database.

You can, of course, run a script to turn on a full-text indexing after the new database is created from model, if that is necessary.  But by default all user databases in the more recent versions of SQL Server have full-text indexing turned on automatically.

Note that from SQL Server 2008 forward, the sp_fulltext_database command is a no-op, just supported for script compatibility.

So, there is certainly no reason, unless your are supporting SQL Server 2005 or earlier version, to have this code included in your standard scripts.  It is obsolete.


Sharding state recovery process found document - contains wrong list of config servers I've been stuck on this issue for the most part of the day, feel like I am hitting a wall. I hope someone more knowledgeable can help me.

Historical Setup

Mongo Shard (4x replica sets)
Config Servers (3x)
Mongos (2x)

This setup got migrated to a different DC. Process involved setting up servers in new DC, connecting them as members and subsequently shutting down instances in the original DC.

Migration got completed successfully, without any issues.

The Problem

From time to time, Mongo instance fails to start as it is trying to connect to config servers in the OLD DC.

Log line from mongod:

SHARDING [rsSync] Sharding state recovery process found document { _id: "minOpTimeRecovery", configsvrConnectionString: "config_cluster/mongo-config-1:27017,mongo-config-2:27017,mongo-config-3:27017", shardName: "shard_cluster", minOpTime: { ts: Timestamp 1536309187000|2, t: 26457 }, minOpTimeUpdaters: 1 }

Problem is, those mongo-config-1, mongo-config-2 and mongo-config-3 are domains from OLD DC. They are not in the new Mongos instance's config file.

I've found the following entries in Mongo PRIMARY's db.system.version

  { "_id" : "authSchema", "currentVersion" : 5 }

  { "_id" : "minOpTimeRecovery", "configsvrConnectionString" : "config_cluster/mongo-config-1:27017,mongo-config-2:27017,mongo-config-3:27017", "shardName" : "shard_cluster", "minOpTime" : { "ts" : Timestamp(1536309187, 2), "t" : NumberLong(26457) }, "minOpTimeUpdaters" : 1 }

My question is:

Is it safe to delete {"_id": "minOpTimeRecovery"} document?
If not, what is the safest way to resolve this?

As per below documentation you can delete the document.

https://docs.mongodb.com/manual/tutorial/restore-sharded-cluster/#remove-the-minoptimerecovery-document-from-the-admin-system-versions-collection


Efficiently select beginning and end of multiple contiguous ranges in Postgresql query I've got about a billion rows of data in a table with a name and an integer in the range 1-288.  For a given name, every int is unique, and not every possible integer in the range is present--so there are gaps.

This query generates an example case:

--what I have:
SELECT *
FROM ( VALUES ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) AS baz ("name", "int")

I'd like to generate a lookup table with a row for each name and sequence of contiguous integers.  Each such row would contain:

name   --  the value of the name column
start  --  the first integer in the contiguous sequence
end    --  the final value in the contiguous sequence
span   --  end - start + 1

This query generates example output for the above example:

--what I need:
SELECT *
FROM ( VALUES ('foo', 2, 4, 3),
              ('foo', 10, 11, 2),
              ('foo', 13, 13, 1),
              ('bar', 1, 3, 3)
     ) AS contiguous_ranges ("name", "start", "end", span)

Because I have so many rows, more efficient is better.  That said, I only have to run this query once, so it isn't an absolute requirement.

Thanks in advance!

Edit:

I should add that PL/pgSQL solutions are welcome (please explain any Fancy Tricks--I'm still new to PL/pgSQL).

How about using with recursive

test view:

create view v as
select *
from ( values ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) as baz ("name", "int");

query:

with recursive t("name", "int") as ( select "name", "int", 1 as span from v
                                     union all
                                     select "name", v."int", t.span+1 as span
                                     from v join t using ("name")
                                     where v."int"=t."int"+1 )
select "name", "start", "start"+span-1 as "end", span
from( select "name", ("int"-span+1) as "start", max(span) as span
      from ( select "name", "int", max(span) as span
             from t
             group by "name", "int" ) z
      group by "name", ("int"-span+1) ) z;

result:

 name | start | end | span
------+-------+-----+------
 foo  |     2 |   4 |    3
 foo  |    13 |  13 |    1
 bar  |     1 |   3 |    3
 foo  |    10 |  11 |    2
(4 rows)

I'd be interested to know how that performs on your billion row table.


Use Database to Store Data Extracted From Internet I'm mining 500 million users, and their "followers" from a social network using their API. The extraction of data itself is not a problem, since I can do it with my scripts. However having 500 million users and their followers in a list in memory can be very costly.

My script created two lists,one with the users that I already got their followers, and one with the users to be looked at (I would get each user, put their followers in the queue, write to file, and then go to the next one.) So it would be 2 long lists that I cannot handle in memory. So I thought of a database.

So finally to my question, is it better for me to use a relational database, or a NoSQL, graph, database, like Neo4j. The only information I'm getting now is the user ID and the ID of the followers, which later I want to analyse (for graph theory research.) I thought of a database because I might try add more information later as well.

Thank you.

Sounds on the surface like a graph database problem. If you're going to be walking the edges between users, neo4j or such like may be the one for you.

You might be able to do more generic processing using a document db where every user has an _id of user_id and an array of followers _ids.

Perhaps you could output to MongoDb, then use Neo4j for creating the graph(s) for specialised work, and mongodb for more general work. MapReduce and the aggregation framework in MongoDb are pretty good (speaking from experience, although MapReduce is much more powerful than aggregrtion framework (currently)).

Since the schema is likely to morph, and you do not know what the additional data will be, you might prefer a doc or graph db over a RDB. If you prefer to work in a relational manner at a later point, you can generate csv extracts to upload to your RDBMS of choice after you have defined a schema.


Improve performance of SUM query I have an aggregate query that has a lot of columns and generate a huge data set:

SELECT      column1,
            column2,
            ...
            column20,
            sum(column21),
            ...
            sum(column40)
INTO        #Output
FROM        #Ledger
GROUP BY    column1,
            ...
            column20

The input table (#Ledger) has 18m rows and the result table (#Output) has 600k rows. The query took 9 minutes. Is there anything I can do to make it faster? Here's the execution plan:

There's no index on the #Ledger table and the exclamation point in Sort gives the following warning:

  Operator used tempdb to spill data during execution with spill level 1 and 4 spilled thread(s), Sort wrote 1541592 pages to and read 1541592 pages from tempdb with granted memory 3752160KB and used memory 3681824KB

Looking at your query plan, it seems that the optimizer has chosen a Stream Aggregate operator to handle your GROUP BY. The downside of these is that they require ordered data, which a HEAP is not.

David's suggestion is perfectly valid. Adding a clustered index may remove the Sort operator, so performance testing that vs. my suggestion is up to you.

If indexing the temp table doesn't give you the performance you need, applying an OPTION(HASH GROUP); hint to the query will influence the optimizer to use a Hash Match Aggregate rather than a Stream Aggregate.

Hash Match Aggregates don't require sorted data, but may also spill to disk the way the Sort spilled in the Stream Aggregate plan.


Enabling audit in Oracle I have a table 'class' owned by the schema 'test' and I need to audit all operations on the particular table.

audit INSERT,UPDATE,DELETE ON test.class --  will this be sufficient to do the trick?

Should I enable any other kind of Fine grained auditing to capture all the details.

Oracle12c/RHEL - I have set audit trail to DB. And sys operations to true.

Inputs needed here

I know I'm late for party, but someone else with similiar issue may find it useful so anyways: I would say that you don't have AUDIT_TRAIL set to DB,EXTENDED but perhaps only to DB or something.

If you want SQLTEXT to be populated it is mandatory to have AUDIT_TRAIL set to DB,EXTENDED.

Check your current AUDIT_TRAIL status:

select * from v$parameter where name like '%trail%';

If it's not set to DB,EXTENDED, you set it with the following command:

alter system set audit_trail=db,extended scope=spfile;

Bear in mind that after setting it to DB,EXTENDED, you have to restart your instance for changes to take effect.

Afterwards after each audit record your SQLTEXT in SYS.AUD$ will also be populated.

From Oracle's documentation you will see more:
https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams016.htm#REFRN10006


Import daily csv with varying available headers I receive a csv daily from an external company capturing feedback for us.
Unhelpfully the csv headings vary. There could be any combination of headers.
For example:
day1: heading1, heading2, heading3, heading4, heading5
day2: heading1, heading2, heading4, heading6
day3: heading1, heading2, heading7, heading8
day4: heading1, heading2, heading3, heading4, heading5
We have 18 months (no one has done anything with this data until now) of backlog files and so far I have found 22 different combinations...
I know what all the potential headings will be so I can have an SQL table that contains all the appropriate columns but I am struggling to handle the variance for import.
Is there a way to handle this in SSIS?
I have looked at bcp and openrowset but I can't seem to make it work.
As this file is received daily I want an sql job that imports them to my table for reporting. I can only create an SSIS package that will import a fixed flat file but I need it to be dynamic. Worst case scenario would be to have 22+ different SSIS packages and run a specific 1 for the given headings available but then I would need a way of automatically reading the structure of the csv to decide which package to use.
I'm lost, so does anyone have any direction?
SQL 2016 (13.0.5622.0)
Many thanks

I would do it like this:

There exists some working table where the data must be placed into. It contains columns for all data which may present in CSVs, with proper names and datatypes.

Create names table with 2 columns: column name used in CSV; according column name in working table. Like

CSV_name    | table_name
------------|-----------
ID          | id
user_id     | id
Employee_id | id
name        | first_name
FirstName   | first_name
...         | ...

Each column is defined as unique.

Create a procedure which:

Loads first row from CSV file (the file is renamed to some pre-defined name or its is got as a parameter), parses it to separate names, then adds them into CSV_name ignoring duplicate errors, then counts the amount of names in names table with NULL in table_name.

If there are CSV_name rows with table_name IS NULL then procedure types a message and breaks. Operator looks names table and fills table_name column with according values, then calls the procedure again.

If all CSV_names have according table_name then procedure creates temporary table and loads the whole data into it. After data loading the procedure builds INSERT INTO query text and copies the data into working table. Alternatively correct importing query text may be created/executed in one step.


How can I narrow down the cause of a deadlock after the fact? We're using a .Net 4.5 web application with SQL Server 2012 on the same machine. We will sporadically see a cluster of "Wait operation timed out" exceptions while using the site. Occasionally we will receive Deadlock exceptions as well. These are always on Select queries, not updates.

I have run sp_lock a few times and noticed there are sometimes as many as 4500 locks acquired by a single process. These are selects with multiple joins. Even though they are shared locks, could that be the source of our problems? Could adding nolock to these queries reduce blocking and timeouts and possible deadlocks? If that's not the right answer, is there a way to know what queries were being run when the timeouts occurred? I have not been able to catch it in action as it usually only lasts a few seconds before the database returns to normal funciton.

When the timeouts occur, do you see any blocking?  You can run sp_who2 to determine that.  It sounds like you are having intermittent performance issues.  You need to look at your wait types to see if there is a bottleneck.  Activity Monitor will show you the top resource waits.  The waits will help you determine where to focus your attention.  Check the SQL Errorlog, System & Application event log for any dumps or entries that provide clues into what is happening.


Are these two queries logically equivalent? Are these two queries logically equivalent?

DECLARE @DateTime DATETIME = GETDATE()

Query 1

SELECT *
FROM   MyTable
WHERE  Datediff(DAY, LogInsertTime, @DateTime) > 7

Query 2

SELECT *
FROM   MyTable
WHERE  LogInsertTime < @DateTime - 7

If they are not logically equivalent, can you give me the logical equivalent of the first query so that the WHERE clause can effectively use an index (i.e. eliminate function wrapping)?

Whether the two queries you posted are logically equivalent is irrelevant; you shouldn't use either of them. I will try to steer you away from a couple of things:

Whenever possible, try to avoid applying functions to columns. It's always as good, and mostly better, to keep those calculations against constants and not columns - this can destroy SARGability and render indexes on those columns useless. In this case, I much prefer query 2, especially if LogDateTime is indexed (or might ever be).
I don't like the shorthand date math and I recommend against it. Sure, it's faster to type, but try that with a DATE data type and you will get an ugly error. Much better to spell it out, e.g.:

WHERE LogInsertTime < DATEADD(DAY, -7, @DateTime);


Many to many between lots of tables? TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended?

Each body type has its own table
Each car in each body type table can has several features

As you can see from my "schema", each car can have several features but I need to connect features table to several car body types tables (many to many relationships). I've thought about it for several days but Im still not sure how to achieve this.

Why all body types are in separate tables? This seems to follow normalization rules, it should also speed up queries because I will never have to query several body types at once.

Is it possible or should I rethink it? Should I just make another table about body types and combine all the cars? I expect to have a maximum of 1M entries (all body types combined). There's going to be a lot more reads than writes.

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for subtyping in datamodeling Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping .


Index for date column along with multiple other columns in where clause I have a 'joinings' table which lists the joining date of all the employees along with their office location, department no and the job advertisement through which they were selected. I want to be able to query this table by their joining dates and group them using one of the other columns. I also want to be able to apply a where clause on any of the columns.

It could be like this -

SELECT month(joining_date) as month, count(*) as entries
     , branch_name
FROM joinings
WHERE joining_date >= '2017-01-01'
  AND joining_date <= '2017-05-31'
  AND department_no in (5,4,7)
  AND source_of_appliction IN ('glassdoor', 'linkedin')
GROUP BY month(joining_date), branch_name

OR could be like this -

SELECT year(joining_date) as month, count(*) as entries
     , source_of_application
FROM joinings
WHERE joining_date >= '2017-01-01'
  AND joining_date <= '2019-12-31'
  AND department_no IN (5,4,7)
  AND branch_name IN ('ZEIT','DUS')
GROUP BY year(joining_date), source_of_application

This table can contain thousands of records for several years. The range of the joining date that I would query could be between a single month, range of months or range of years.

I would like to know what indexes need to be created to get me optimized performance for my select queries. If not the exact indexes, I am at least looking out for pointers to get me started to create the right indices.

What I have-
I currently have a multi-column index created on joinings(joining_date, department_no), joinings(joining_date, branch_name), joinings(joining_date, source_of_application) and also have individual indexes for all the single columns. But my select performs a full table scan for the queries that I have listed above.

This is a "range:  joining_date >= '2017-01-01' AND joining_date <= '2017-05-31'

This is sort of a range:  department_no IN (5,4,7) .  There are cases where it acts efficiently like =, there are cases where it should be considered a "range".

This is an =:  department_no IN (5) .  That is, the Optimizer turns it into department_no = 5, which has much more optimization potential.

http://mysql.rjweb.org/doc.php/index_cookbook_mysql says to put range columns last, not first in an index.

In your queries, it is hard to predict what is optimal.

If you are likely to sometimes have single-item INs, then you need

INDEX(department_no, joining_date) -- in THIS order

Also, newer versions of MySQL may work well with that index for the multi-item IN by leapfrogging across the table.  So add that index.

As for

INDEX(joining_date, ...)

When using a 'range', that index will not get past joining_date to whatever other column(s) follow it.  So you may as say just

INDEX(joining_date)

As for the GROUP BY...  Neither of your queries has any hope of using an index for the GROUP BY.  So there will be some form of extra effort after the WHERE -- a tmp table, a sort, something.

Hiding a column in a function month(joining_date) -- the Optimizer can't use the joining_date column an any index for this.
The WHERE left the results in some messed up order (assuming a multi-item IN)

Unrelated... I like this pattern:

AND joining_date >= '2017-01-01'
AND joining_date  < '2017-01-01' + INTERVAL 1 MONTH

It avoids computing the end, leap year, DATE vs DATETIME vs DATETIME(6) issues, etc.

Bottom line: Have this one index:

INDEX(branch_name, department_no, joining_date)

Then get the EXPLAIN SELECT ... to see if it did the leapfrogging.  If that fails, let's rethink.


SQL Server restore full backup + partial differential backups I created a full backup of my database using SQL Server Management Studio wizard, and then I perform differential partial backup each day via cmd to the same file.

SQL Server version is 2008 R2.

Here is the command I use to perform backup:

BACKUP DATABASE [db_name]
TO DISK = N'path_to_backup\db_name.bak'
WITH DIFFERENTIAL,
     DESCRIPTION = N'Differential backup of the staging database',
     NOFORMAT, NOINIT,
     NAME = N'db_name-Differential_Backup', SKIP, NOREWIND,
     NOUNLOAD, STATS = 10
GO

I can't figure out how to restore the entire database (full backup and all of the partial backups).

I tried restoring full backup with NORECOVERY and PARTIAL options:

RESTORE DATABASE [db_name]
FROM DISK = N'path_to_backup\db_name.bak' WITH
FILE = 1,
  MOVE N'db_name' TO N'path_to_sql_server_installation\MSSQL\DATA\db_name.mdf',
  MOVE N'db_name_log' TO N'path_to_sql_server_installation\MSSQL\DATA\db_name_1.ldf',
  NORECOVERY, PARTIAL, NOUNLOAD,  STATS = 10
GO

This works ok, but then I try to restore the partial backup using the following command:

RESTORE DATABASE [db_name]
FROM DISK = N'path_to_backup\x12parsedb.bak' WITH
FILE = 2
GO

This also completes without errors but it overwrites the data from the full backup and I'm left with only small fragment of data that was in this partial backup.

All of my backup data is stored in a single file, and there are around 130 partial backups.

I've tried to use Management Studio wizard but the result is the same.

Not sure what you expect from partial backups, but they are only useful for read-only filegroups:

  partial backups are designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups.

Differential backups are a different story, but they are not used as you use them. They are supposed to speed to up a full restore by skipping over some log backups, not as a replacement for full backups.

Is rather unclear what you actually have. I guess you have a full backup and 130 differential backups. In such case you can restore the original full and then apply the last differential.

My suggestion is to implement a correct backup plan (full recovery mode, periodic full backup and frequent log backups). There are many examples how to do this. Start with reading Introduction to Backup and Restore Strategies in SQL Server. Go over some tutorials like the ones from SQL Server Backup
Best Practices and Articles. Understand what you're doing, don't guess.


Simple PostgreSQL lookup table is inexplicably slow I'm trying PostgreSQL 8.4.14 for storing triples, pieces of data of the form (String, String, String).
For speed, I'm not repeatedly storing strings but rather using two tables:

main table triples (subject BigInt, predicate BigInt, object BigInt)
lookup table entities (entityId BigInt, name Varying(40000))

I've added indexes and foreign keys on triples:

"nsubject_idx" hash (subject)
"npredicate_idx" btree (predicate)
"nobject_idx" hash (object)
"triples_subject_fkey" FOREIGN KEY (subject) REFERENCES entities(entityid)
"triples_predicate_fkey" FOREIGN KEY (predicate) REFERENCES entities(entityid)
"triples_object_fkey" FOREIGN KEY (object) REFERENCES entities(entityid)

and also indexes on entities:

"entities_pkey" PRIMARY KEY, btree (entityid) CLUSTER
"name_idx" hash (name)

Now it would be reasonable to assume that lookups are fast. They aren't.

With 151M elements in triples and 44M in entities, the following query is immensely slow:

SELECT entityId FROM entities WHERE entityId in (SELECT object FROM triples LIMIT 10000);

It's only 10.000 lookups, so I'd expect this to complete at high speed. Query plan:

 Nested Loop  (cost=288.69..3856.26 rows=43806140 width=8) (actual time=25.226..40110.699 rows=6959 loops=1)
   ->  HashAggregate  (cost=288.69..290.69 rows=200 width=8) (actual time=19.445..24.087 rows=6959 loops=1)
         ->  Limit  (cost=0.00..163.69 rows=10000 width=8) (actual time=0.013..15.792 rows=10000 loops=1)
               ->  Seq Scan on triples  (cost=0.00..2474009.68 rows=151135968 width=8) (actual time=0.012..14.101 rows=10000 loops=1)
   ->  Index Scan using entities_pkey on entities  (cost=0.00..17.82 rows=1 width=8) (actual time=5.756..5.759 rows=1 loops=6959)
         Index Cond: (entities.entityid = triples.object)
 Total runtime: 40112.383 ms

What would be happening here?

Note that this is even a trick query: because of the foreign key constraint, it is actually equivalent to SELECT object FROM triples LIMIT 10000.
For my use case, I'd need the actual lookup.

Rather than "VARCHAR(40000)" why not use "TEXT"?
HASH index use is discouraged (see docs at http://www.postgresql.org/docs/current/static/indexes-types.html).
Have you run "ANALYZE" on your tables before running the query?
Giant IN lists can be performance killers.

How do the following queries perform?

SELECT e.entityId
FROM entities e
INNER JOIN triples t ON (t.object = e.entityId)
LIMIT 10000;

or

SELECT e.entityId
FROM entities e
WHERE EXISTS (SELECT 1 FROM triples t WHERE t.object = e.entityId LIMIT 10000);