MySQL: OR in SHOW TABLES? I've just installed MySQL and wondering if SHOW TABLES output can be combined. Btw, I'm not a DBA, please be patient if this question doesn't make sense to you. I just want to know how SQL query works, and what not.

E.g.

mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.00 sec)

mysql>

Certain column can be filtered with LIKE

mysql> SHOW TABLES LIKE 'SCHEMATA';
+-----------------------------------------+
| Tables_in_information_schema (SCHEMATA) |
+-----------------------------------------+
| SCHEMATA                                |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

Instead of typing this one by one ...

mysql> SHOW TABLES LIKE 'TABLES';
+---------------------------------------+
| Tables_in_information_schema (TABLES) |
+---------------------------------------+
| TABLES                                |
+---------------------------------------+
1 row in set (0.00 sec)

mysql>

I was wondering if SHOW TABLES output can be filtered with more than 1 parameter.

Probably something like SHOW TABLES LIKE 'SCHEMATA' or 'TABLES';, but this query doesn't work

Desired Output

+-----------------------------------------+
| Tables_in_information_schema            |
+-----------------------------------------+
| SCHEMATA                                |
| TABLES                                  |
+-----------------------------------------+

Update:

I've just noticed that SHOW TABLES also can be produced with SELECT statement

mysql> SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = 'information_schema';
+---------------------------------------+
| TABLE_NAME                            |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| PROFILING                             |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
17 rows in set (0.01 sec)

mysql>

Still didn't work

mysql> SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = 'information_schema' and TABLE_NAME = 'SCHEMATA' or 'TABLES';
+------------+
| TABLE_NAME |
+------------+
| SCHEMATA    |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql>

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
  AND TABLE_NAME IN ('TABLES', 'SCHEMATA');

or, for example (6 tables will be shown)

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
  AND (    TABLE_NAME LIKE 'TABLE%'
        OR TABLE_NAME LIKE 'SCHEMA%' );


Cannot change password from Oracle EM I am facing an issue as follows:
I login to em using SYS as SYSDBA. Enterprise Manager shows information screen saying my password is going to expire. SYS Password will expire on 7th Feb 2017 as confirmed with Sql Plus:

Now on EM side, when I go to preferences -> General -> Password and try to change my password, it says Current Password - Current password is invalid.

I am providing same password I used to login to EM as SYSDBA but no luck.

Can someone advice what the problem is? How can I access database from EM in this situation. Also, please briefly advice about how to extend password expiry date.

DB Version: Oracle 11g

That's not the best practice to use SYS user to use in Enterprise Manager. And you can change password for the user like SYSTEM or SYSMAN using OEM but not for SYS user.

You can change the password of SYS user using SQL*PLUS command called password which send the password in encrypted form instead of plain text.

[oracle@ora12c ~]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 24 13:37:49 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> password
Changing password for SYS
Old password:
New password:
Retype new password:
Password changed

First of all find out which profile is assigned to the user-

SQL> select profile from dba_users where username='SYS';

PROFILE
--------------------------------------------------------------------------------
DEFAULT

-then you can user ALTER PROFILE to change password life time.

Also password doesn't cause effect on SYS user(I couldn't find documentation link) as demonstrated below.

SQL> alter user sys password expire;

User altered.

But I still can connect to the database using old password.

C:\Users\user>sqlplus sys/oracle@192.168.30.133:1522/oracdb as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 24 13:52:46 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

However, if your password for sys user has expired and unable to login then-
 use ORAPW utility
 run sqlplus / as sysdba from the terminal of the user who owns the Oracle software.


Why can't pgAdmin3 connect to Postgres? I added a new user to posgresql (amanda) which is the same as my linux system user. At the console, I can open psql just fine -- I don't need a password because I'm already authenticated as amanda. But when I try to set up pgAdmin III without a password, I get an error:

An error has occurred:

01:18:47 PM: Error: Error connecting to the server: fe_sendauth: no password supplied

And if I provide my system password, I get a password error:

An error has occurred:

01:15:30 PM: Error: Error connecting to the server: FATAL:  password authentication failed for user "amanda"
FATAL:  password authentication failed for user "amanda"

How do I get pgAdmin3 talking to postgres as amanda?

This is all @ localhost. I read this:

PgAdmin III - How to connect to database when password is empty?

but the answer there discusses trust authentication. My read of the auth methods section of the manual suggests that what I want is for pgAdmin3 to use Ident Auth.

On Unix, when psql is used without the -h option or a PGHOST variable set, it uses Unix local domain sockets, which corresponds to local in the TYPE column in pg_hba.conf and no ADDR column at all.

That differs from putting localhost in the host field of pgAdmin's connection dialog. This one corresponds to host in the TYPE column and 127.0.0.1 in the ADDR column or whatever localhost is resolved to by the TCP stack (with IPv6 it could be ::1 for instance).

In general, this is what is confusing when psql and pgAdmin connection attempts do not trigger the same rules in pg_hba.conf. If that's the case in this question, try to leave the host field in pgAdmin empty.

On top of that, Windows does not have Unix local domain sockets so psql on Windows defaults on localhost as if -h localhost was used.


Do I want an index if all I care to query is field not null In SQL, do all the common considerations about indexes apply if everything I'll ever want to query (in the where condition) is when a field is not null?

Is there a special configuration of the index I can apply to cover this specific case?

Eg:

select id
from Accesses
where token is not null;

Is it worth to add the index on token as much as it would be if I had to search for specific token values?

My database is Postgres.

SELECT id FROM accesses WHERE token IS NOT NULL;

The perfect index for this specific query would be a partial index:

CREATE INDEX accesses_foo_idx ON accesses(id) WHERE token IS NOT NULL;

The index condition is the important part.
On top of it, since you only retrieve id which is covered by the index, you can get index-only scans out of this (if the table is vacuumed enough).

Effectiveness grows with the number of rows excluded from the index this way (and also with the number of columns in the table). I.e.: especially beneficial for few non-null values in token (in a table with many more columns).

Related:

Unexpected Seq Scan when doing query against boolean with value NULL
Index optimization with dates


Putting back online SQL AlwaysOn servers I got notification from System Administrators that they will upgrade some features on physical machines on which I have virtual machines (SharePoint and SQL servers).
SQL servers are in AlwaysOn mode, one primary and one secondary server.

Does it matter what SQL server they will put back online first or it doesn't matter?

Here is how I see it.

Patch & upgrade the secondary/passive node first

after this do a failover and check if everything is still working like it should.(this can mean waiting a week) If this is the case, then you can proceed with patching the other node.

If this is not the case you can fallback to your old node and revert the changes done on the other server, and get them back into sync.

I would definitely not patch both nodes at the same moment

IMPORTANT: make sure you can take the hit on your logspace because during the downtime of your secondary node you will not have any log truncation

Also make sure you have your Quorum settings configured properly, otherwise you might have some issues on your cluster level!


Can I add new tables to a transactional replication subscriber I'm looking into using transactional replication in MS SQL Server 2016 to create a reporting database from our production HR system.

I'm wondering if it's possible to create new stored procedures and tables on the reporting database which will not be dropped as part of the replication sync?

if it's possible to create new stored procedures and tables on the
  reporting database which will not be dropped as part of the
  replication sync?

Yes, you can create new stored procedures and tables on the reporting database.

When creating a publication, you choose the tables and other database objects that you want to publish. Objects that are included in the publications get replicated. You are free to add other objects in the subscriber database.

Read this document which details about publication and limitations:
Publish Data and Database Objects


SQL Server 2016 Nested JSON Array I have the following JSON stored in a column in SQL Server 2016.  I am trying to figure out if it's possible to create a computed column for a nested array.

{
"global": {
    "commands": [{
        "commandname": "hostname xxx-yyy"
    }, {
        "commandname": "boot-start-marker"
    }]
},
"interfaces": [{
    "name": "Loopback0",
    "commands": [{
        "commandname": "description Psuedo Interface used for Management Purposes"
    }, {
        "commandname": "ip address 178.100.100.0 255.255.255.255"
    }, {
        "commandname": "no ip redirects"
    }, {
        "commandname": "no ip proxy-arp"
    }]
}, {
    "name": "GigabitEthernet1/1",
    "commands": [{
        "commandname": "no ip address"
    }, {
        "commandname": "shutdown"
    }]
}]
}

I created a computed column for the global object with the following command:

ALTER TABLE [MyTable]
ADD vGlobalCommands AS JSON_QUERY(configinfo, '$.global.commands')

And a full text index to go along with it and it works great.

Now I want to create the same for the $.interfaces.commands but I can't seem to figure out how to create the computed column.  I've tried this path but it returns null entries for the computed column.  Interfaces is an array and global is not, so I guess I just don't know the syntax to get this to work. Or maybe it's not possible?

I basically want to be able to query all the interfaces to see which ones CONTAIN command 'xyz'.  The above JSON is just an example, the real data is much larger, hence the need for a full text index.

Any ideas?

Update: According to this Stack Overflow Q & A it isn't possible to index nested arrays.

If anyone can think of a way to "redesign" the json or other ideas I would love to hear them.

As there are two commands you have to specify the one you want, eg

SELECT
    JSON_QUERY( configInfo, 'strict $.interfaces[0].commands' ),
    JSON_QUERY( configInfo, 'strict $.interfaces[1].commands' )
FROM dbo.jsonWorking


Truncating table without using Alter Table permission So my DBA doesn't want developers to give Alter table permission. Now one of the procedures that we created requires to truncate tables (since apparently delete only removes records, but truncate frees up space too).

Now in order to use truncate minimum permission required is "Alter table". (Source)

But there was also a solution described to "create a stored procedure with execute as owner to only one table or a stored procedure to any table" (given by user3854427).

My question is in response to the code he provided. Embedding the code for easy reference:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Yimy Orley Asprilla
-- Create date: Julio 16 de 2014
-- Description: Función para hacer TRUNCATE a una tabla.
-- =============================================
ALTER PROCEDURE [dbo].[spTruncate]
    @nameTable varchar(60)

WITH EXECUTE AS OWNER
AS

SET NOCOUNT OFF;

DECLARE @QUERY NVARCHAR(200);

SET @QUERY = N'TRUNCATE TABLE ' + @nameTable + ';'

EXECUTE sp_executesql @QUERY;

My questions are:

Is the above code creating a stored procedure "on behalf of" OWNER?
Who has to create this procedure (DBA?) since I definitely can't create it.
Once the DBA create this procedure any member can use it to truncate the table even if they don't have the Alter table permission?

Please help.

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila.

There are basic issue:

table names are NVARCHAR, not VARCHAR
table names are length 128, not 60
sysname is a handy type to represent object names, is an alias for NVARCHAR(128)
tables are qualified by schema and name, not by name only
procedures that build dynamic SQL must properly quote the names, using QUOTENAME

Lets try again:

 CREATEPROCEDURE [dbo].[spTruncate]
    @schemaName sysname,
    @tableName sysname
 WITH EXECUTE AS CALLER
 AS
 SET NOCOUNT OFF;
 DECLARE @QUERY NVARCHAR(max);
 SET @QUERY = N'TRUNCATE TABLE ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName );
 EXECUTE sp_executesql @QUERY;

No need to use execute as OWNER. As CALLER is fine, as long as the procedure is properly signed. Ask you DBA to sign the procedure following the standard procedure. See Module Signing and Signing Stored Procedures in SQL Server. Your DBA should know how to do this.


Best approach to maintain a single set of DDLs and DMLs for multiple database type In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these different databases.

It is difficult to maintain these multiple set of DDLs/DMLs and moreover in future our application might support few more database type.

What will be the best approach if we want to maintain a single set of DDLs/DMLs?

The best way to support multiple database types is to optimize the sql statements for each on of them. Otherwise you will one day end up with huge performance problems on one or the other database types.

Additionally to that it's unfair for your customers to tell them "I support Oracle, DB2, MSSQL, etc.) and not optimizing the SQL for each one of them. Why? Because your customer pays a lot for the database and your application does not use the advantages and features the database provides.


When dropping a partition on InnoDB table does mysql flush pages in the whole table or only in a partition to be dropped? When issuing ALTER TABLE .. DROP PARTITION p1 mysql have to flush pages to disk. My question is: does mysql flush pages in the whole table (in each partition) or only in a partition to be dropped? MySQL server 5.7
table partition exp. is: PARTITION BY RANGE (UNIX_TIMESTAMP(dt))

According to the MySQL 5.7 Documentation under the subheading Affected DDL statements, here is what says about Locking for DDL on Partitioned Tables:

CREATE VIEW does not cause any locks.
ALTER TABLE ... EXCHANGE PARTITION prunes locks; only the exchanged
table and the exchanged partition are locked.
ALTER TABLE ... TRUNCATE PARTITION prunes locks; only the partitions
to be emptied are locked.
In addition, ALTER TABLE statements take metadata locks on the table
level.

As far as I can see in that page, it locks the table's metadata. There is nothing here suggesting that all the .ibd files of the other partitions need to be flushed. It would affect only those files involved.
In your case, it would involve the following steps:

Acquire metadata lock on the .par file of the partitioned table
Update list of partitions in the .par file
Remove locks of .ibd you are going to drop
Unlock the .par file

NOTE : Dropping a partition is DDL not DML.
DIRECT ANSWER TO YOUR QUESTION
Only partition to be dropped


How to improve performance for table JOINS involving millions of rows I am dealing with a performance issue for a very simple structure involving simple queries.

Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary key for TBL01 is called CONFIGURATION_ID. TBL02 has CONFIGURATION_ID as a foreign key to TBL01 which is also indexed. TBL01 has 2.8 million rows and TBL02 has maybe 10 million rows.

A simple INNER JOIN on TBL01.CONFIGURATION_ID = TBL02.CONFIGURATION_ID is kinda slow. By slow I mean it takes a second to return 10 rows with 15 columns in total from both TBL01 and TBL02. If I start using sorting etc, things get worse. I have dealt with very big tables in the past but never had an issue. Could there be something missing?

What should my next steps be in order to improve the performance? BITMAP index with JOIN on CONFIGURATION_ID? Would that help? Any other tips?

Note 1: I am using ORACLE 12.

Note 2: This might be obvious, but if I add a filter after the join things are better.

What you described is normal behaviour.

create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
delete from t1 where object_id is null;
delete from t2 where object_name = 'T1';
alter table t1 add primary key (object_id);
alter table t2 add constraint t1_fk foreign key (object_id) references t1(object_id);
create index t2_object_id on t2(object_id);
exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');

explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id);
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1838229974

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 23763 |  5337K|       |   516   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |      | 23763 |  5337K|  2952K|   516   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   | 23764 |  2668K|       |   115   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 23763 |  2668K|       |   115   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan

Here the database performs the join for the whole resultset with a HASH JOIN. By default, the optimizer generates a plan to return all rows from a query, which is configured with the optimizer_mode parameter.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

Your query says: return all rows from TBL1 and TBL2 that match on CONFIGURATION_ID. You never told the database you wanted only the first any 10 rows. If you want the optimizer to generate a plan that returns the first rows quicker, tell that to the optimizer: change the optimizer mode, example:

alter session set optimizer_mode=first_rows_10;

explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id);
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 209411957

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | T2          | 21604 |  2426K|     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C006383 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |   115 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

This time, the database performed a NESTED LOOPS JOIN.

The significant difference between HASH JOIN and NESTED LOOPS JOIN in this case: HASH JOIN has to read one of the tables completely to build the hash table for the join. NESTED LOOPS JOIN can work row by row and do index lookups by ID, then stop reading the tables after finding the 10 rows. So even if you fetch only 10 rows, with a HASH JOIN, the database will read the whole 2.8 or 10 million rows from TBL1 or TBL2, depending on the order the optimizer chose.

To make things even worse, if you want to get the first 10 rows by a speficied order, with a HASH JOIN like above, the database has to read both tables completely, perform the join for all rows, sort the ~10 million rows, and return only the first 10 of them.

With a NESTED LOOPS JOIN, if you have an index on the sort column, the database can read the rows even in the sorting order using that index, can do row by row processing, and stop after finding the first 10 rows.

The optimizer mode can be set with a parameter as above, or it can be defined at statement level as well, with a hint, like:

select /*+ first_rows(10) */ ...


Writing transactions to .MYD file to take Backup I'm trying to backup MySQL DB by archiving the data directory of required databases.

I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not written into .MYD files. cpio reports that there are few changes which got written recently to the files were not archived. Is there any command or options to make MySQL write all the transactions before lock to .MYD files

Your problem may have to do with the session you ran FLUSH TABLES WITH READ LOCK;.

If you launched FLUSH TABLES WITH READ LOCK; like this:

# mysql -uroot -p... -e"FLUSH TABLES WITH READ LOCK;"

all that would do is close all open tables, reopen those tables, and the session dies. The lock is not maintained.

You would have to launch FLUSH TABLES WITH READ LOCK; like this:

# mysql -uroot -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &

This forces the session to stay open and maintain the lock. You would have to open another session, get the process ID of the SELECT SLEEP(86400), start your backup, and then kill that DB Connection when your backup is complete.

Here is a sample script on how to mysqldump all databases locked with FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400):

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Here are my other posts where I discuss this and other backup variations involving FLUSH TABLES WITH READ LOCK:

Safest way to perform mysqldump on a live system with active reads and writes? (Jun 20, 2012)
Difference between backup methods: Oracle and MySQL (Apr 18, 2012)
Quick MySQL Backup (1 file per table) (Jan 31, 2012)
mysql db backup excluding specific tables (Nov 30, 2011)
Quick MySQL Backup (Oct 01, 2011)
How can I optimize a mysqldump of a large database? (Apr 17, 2011)


How to handle lock information? Due to analyzing several deadlocks I used sp_blitzIndex to get an overview about locks.
There is a high accordance between the tables (and views) affected by the deadlocks and the tables being diagnosed as

"Aggressive Indexes: Total lock wait time > 5 minutes (row + page)"
(Explanation).

There it is adviced to check missing indexes on those tables. Ok, there are indeed misssing indexes with high potential suggested by Sql Server for those tables...
But I am actually trying to understand the exact meaning of the information given by sp_BlitzIndex and the reasons for them appearing.
Here are some samples popping up in deadlock chains as well as "Aggressive Indexes":

dbo.O.IX_O1 (48): Page lock waits: 133; total duration: 5 minutes; avg duration: 2 seconds; Lock escalation attempts: 98; Actual Escalations: 19.
dbo.O.IX_O2 (57): Row lock waits: 468; total duration: 5 minutes; avg duration: 0 seconds;

dbo.O is a heap.

dbo.P.IX_P1 (18): Row lock waits: 6; total duration: 19 seconds; avg duration: 3 seconds; Page lock waits: 52; total duration: 4 minutes; avg duration: 5 seconds; Lock escalation attempts: 196; Actual Escalations: 19.
dbo.D.IX_D1 (14): Row lock waits: 587; total duration: 30 minutes; avg duration: 3 seconds; Lock escalation attempts: 30; Actual Escalations: 0.
dbo.S.PK_S (2): Row lock waits: 697; total duration: 44 minutes; avg duration: 3 seconds; Lock escalation attempts: 709; Actual Escalations: 1.
dbo.E.IX_E1 (18): Row lock waits: 108; total duration: 5 minutes; avg duration: 3 seconds; Lock escalation attempts: 4; Actual Escalations: 0.

Questions:

Brent Ozar unfortunately does not really explain their meaning and ways to workaround, besides re-engeneer indexes. So what does it mean and why is it there? Is it basically because READS and/or WRITES on those tables are simply too slow and the resulting locks take too much time?

In case I go ahead and add the missing indexes having the most impact - won't this just increase the time required to perform insert / update statements and maybe this way increase the chance to trap into deadlocks - or the other way round, will removal of unused indexes for a table reduce the chance of deadlocks because I/U statements will be faster?

I suggest you read Understanding how SQL Server executes a query.

I can't comment about what Brent's scrips, but I can tell you about the wait stats info in sys.dm_db_index_operational_stats. The per-hobt wait stats aggregate page and row lock wait info and page latch and io latch info. High numbers here indicate either contention (many concurrent transactions attempting to operate on the same page/row) or scans (operations locking and latching all pages). In your case looks like the problem is scans. As every operation is reading the entire table you cause a lot of locking, latching and possible IO. An index would dramatically reduce the numbers by turning scans into seeks.

Further read:

How to analyse SQL Server performance
SQL Server Index Design Guide


Why does user not have access to database? I have a user ls_readonly which is supposed to have db_datareader privileges on several databases. I thought I had set it up right:

But when I connect to the server as ls_readonly and try to open the db in the Object Explorer, I get an error:

  The database wtest is not accessible. (ObjectExplorer)

I open a query window in master and try run:

use wtest

This responds with:

Msg 916, Level 14, State 1, Line 1
The server principal "ls_readonly" is not able to access the database "wtest" under the current security context.

What am I missing?

UPDATE: here's a clue. If I delete ls_readonly as a user from under the database's security context, then I go to the user under the server's security context and under "User Mapping" I grant access to the database, then it starts working.

It could be that the database was originally restored from a different server, which also has a ls_readonly user. I guess then that the user identification is not based on the username?

It could be that the database was originally restored from a different
  server, which also has a ls_readonly user. I guess then that the user
  identification is not based on the username?

Normally, all the logins have the same sid with corresponding users. That is the sense of CREATE USER FROM LOGIN command: the database principal is created with the same sid (security identifier) that corresponding login has.

This user sid is now stored in your database, and when you backup/restore your database the sid is also preserved.

Imagine now that you have 2 servers and both of them have ls_readonly login. to make it simple, on the first server ls_readonly has sid = 1, on the second sid = 2. You have the database MyDB where this login is mapped. On the first server MyDB stores sid = 1, on the second sid = 2.

You backup MyDB database on the first server and restore it on the second. Now your login has sid = 2 and in the database MyDB it's sid = 1.

You log in as ls_readonly and try to access MyDB, server is checking if there is sid = 2 in the database, there is no, so for the server ls_readonly is not mapped to MyDB at all.

This can be fixed by doing

alter user ls_readonly with login = ls_readonly;

This command just update database's sid of ls_readonly user with the sid of ls_readonly login. All the permissions are preserved.

If you recreate user by dropping it, once you drop the user, all the permissions granted to it are dropped and you need to re-grant them.


MySQL import and export problem I am trying to export a table from one database and import it into another. The table in question has a definition like this:

CREATE TABLE `mydb`.`people` (
  `id` BINARY(16) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)

And I'm exporting it like this:

mysqldump -u root -pmypass -h x.x.x.x mydb people > /tmp/export.sql

And trying to import it like this:

mysql -u root  mydb < /tmp/export.sql

To which I get the error:

ERROR 1406 (22001) at line 1: Data too long for column 'id' at row 1

Looking at the file, It has exported the id as values like this:

'\0\0�\�\��DM�� �Ϥ6�'

Which seems to be interpreted as a string. Now, since I'm dealing with tremendous amounts of data and cannot go through it manually, is there a way I can fix the import/export to interpret this value as binary?

Use --hex-blob:

mysql> create database dbase;
Query OK, 1 row affected (0.00 sec)

mysql> use dbase;
Database changed
mysql> create table hexhex ( id binary(16) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into hexhex values (UNHEX('DEADBEEF'));
Query OK, 1 row affected (0.01 sec)

mysql> select * from hexhex;
+------------------+
| id               |
+------------------+
| ޭ▒               |
+------------------+
1 row in set (0.00 sec)

mysql> quit
Bye
root@ironforge:~# mysqldump --hex-blob -p dbase > dbase.sql
Enter password:
root@ironforge:~#

This produces INSERT statements in the format:

INSERT INTO `hexhex` VALUES (0xDEADBEEF000000000000000000000000);

.. which are import-safe.

Re-import it, and check it's right:

root@ironforge:~# mysql -p dbase < dbase.sql
Enter password:
root@ironforge:~# mysql -p dbase

mysql> use dbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from hexhex;
+------------------+
| id               |
+------------------+
| ޭ▒               |
+------------------+
1 row in set (0.01 sec)

mysql> select hex(id) from hexhex;
+----------------------------------+
| hex(id)                          |
+----------------------------------+
| DEADBEEF000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>


copy table with all constraint how I would copy a table with all constraints?

I run select * into myNewTable
from baseTable
where 1=0

This copy a table but no with constraints

You have to use the GUI management tool to create a script and then run that - you cannot do it directly - take a look here (or here) for relatively clear explanations.


How to get non repeat results I've two tables default_tb_persona and default_tb_envio, this are the DDL for those tables:

CREATE TABLE `default_tb_envio` (
  `codigo` varchar(7) NOT NULL,
  `fecha` date NOT NULL,
  `id_modalidad` int(10) NOT NULL,
  `costo` decimal(28,6) NOT NULL,
  `id_agencia` int(10) DEFAULT NULL,
  `no_envio` int(10) NOT NULL,
  `id_forma_pago` int(10) DEFAULT NULL,
  `bl` text,
  `id_destino` int(10) DEFAULT NULL,
  `id_persona_envia` varchar(255) DEFAULT NULL,
  `id_persona_recibe` varchar(255) DEFAULT NULL,
  `id_organismo` int(10) DEFAULT '1',
   PRIMARY KEY (`codigo`),
   UNIQUE KEY `envio_pkey` (`codigo`),
   KEY `fki_` (`id_persona_envia`),
   KEY `fki_1` (`id_persona_recibe`),
   KEY `envio_modalidad_fkey` (`id_modalidad`),
   KEY `id_foma_pago_fkey` (`id_forma_pago`),
   KEY `id_organismo_fk` (`id_organismo`),
   KEY `ndestino_fkey` (`id_destino`),
   KEY `tb_envio_id_agencia_fkey` (`id_agencia`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `default_tb_persona` (
  `nombre_apellido` varchar(255) NOT NULL,
  `ci` varchar(15) NOT NULL,
  `telefono` varchar(255) DEFAULT NULL,
  `direccion` text,
  `correo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ci`),
  UNIQUE KEY `tb_persona_pkey` (`ci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm trying to get non equal or repeat results from default_tb_persona using a JOIN sentence, this is what I've made for that:

SELECT DISTINCT * FROM
  `default_tb_persona`
LEFT JOIN
  `default_tb_envio`
ON
  `default_tb_persona`.`ci` = `default_tb_envio`.`id_persona_envia`

But I'm getting repeated results. Is that query right? If I have two records pointing to the same record on tb_persona should the query return those two records? I've a test data if any can help me, just say me where to send and I will to solve this problem. Also it's possible to get only fields from default_tb_persona and not from both tables?

Try

SELECT DISTINCT dtp.ci,
            dtp.nombre_apellido,
            dtp.telefono,
            dtp.direccion,
            dtp.correo,
            dte.codigo
FROM   default_tb_persona dtp
   LEFT JOIN default_tb_envio dte
          ON dtp.ci = dte.id_persona_envia;

Max.


How to save the design of an Access database -- not the content -- as text? How can I save the entire design or specifications of an Access 2007 database -- not the data content -- as text?

I have fallen foul of the "Enter Parameter Value" bug whereby a query pop-up shows an out-of-date query name -- despite having the Name AutoCorrect Options turned on and having run Compact and Repair Database.

I have read that Name AutoCorrection is buggy.
I've also read that it was possible to save an object as text (in an earlier version of Access) and think this would be a valuable means of making name changes everywhere -- queries and forms as well as tables.

Please do not try to convince me not to change names; that may only make me more determined to do so ;-)

In This Blog Post, Adi Nuryanto gives a great process by which you can accomplish this via a "legacy operation", which I have reproduced below:

  1.Open MS Access and open your database. (For this sample the CarsDB.MDB sample database installed with ASPxperience was used)

  2.Press ALT+T+Y+D to open the Documentor. (This is a legacy menu option that still works as long as you know the shortcut)

  3.Click the Table tabs

  4.In the Documentor Table tab check the Cars and CarScheduling tables (see Figure 1)

  5.Click the Options button

  6.In the Include for Table group check Properties only

  7.In the Include for Fields group select the Names, Data Types, and Sizes radio button

  8.In the Include for Indexes group select the Nothing radio button (see Figure 2).

  9.Click OK to close the Print Table Definition dialog

  10.Click OK to generate the documentation

  11.When the documentation report is finished the Print Preview tab will be displayed. Click Text File in the Data section to export the
  report to a text file

  12.In the Export – Text File dialog (see Figure 3)

  13.Check open the destination file after the export operation is complete

  14.Click OK

  15.For the encoding choose Windows default

An alternative to the above keyboard shortcut:
    Database Tools | Analyze | Database Documenter


Trying to restore 2008 backup to 2016 I backed up a 2008 R2 database and copied to a server with a 2016 SQL Server. I tried using the restore database dialog but it does not seem to want to work. When I use the dialog I have the following:

The drop down for Database is empty. Also when I grabbed that sibk file the open dialog was looking for *.bak, *.tm and *.log and the backup created in 2008 is none of those. So I chose All Files. Not sure that's a clue to the problem.

The fact that he database drop-down is blank suggests to me that the backup file is invalid/corrupt in some way.

First, try and restore the backup file header to confirm that the file is a valid SQL backup. In Management Studio, connect to your SQL 2016 instance and open a new query window. Run the following:

RESTORE HEADERONLY FROM DISK = 'C:\Users\nasscribe\Documents\sibk\sibk'

If you receive an error from this query, either it is not a valid SQL backup file (possible that there was a write error during the backup process on your 2008 R2 instance, or you just grabbed the wrong file), or it is a permissions issue where the SQL Service account cannot access the file, and you'll see something similar to below:

  Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file
  "C:\Users\nasscribe\Documents\sibk\sibk". Operating system error
  5: "5(Access is denied.)".

If you receive the above message, copy the backup file to somewhere like C:\Temp\ and retry.

If instead you see an error like this:

  Msg 3241, Level 16, State 0, Line 3 The media family on device
  'C:\Users\nasscribe\Documents\sibk\sibk' is incorrectly formed. SQL Server cannot process
  this media family.

  Msg 3013, Level 16, State 1, Line 3 RESTORE
  HEADERONLY is terminating abnormally.

Then the backup file is not a valid SQL backup. Provided you still have access to your old server, connect to it and re-run the backup:

BACKUP DATABASE TO DISK = '[Backup path here]\sibk.bak' WITH STATS=5

Once done copy it over to your 2016 server and run the restore again.


How to create function using another functions mysql I want to create a function in MySQL using another function but I am getting an error "Explicit or implicit commit is not allowed in stored function or trigger".

This is my code:

DELIMITER $$

CREATE FUNCTION `ufn_gettotalavg` (pUserID bigint,pclass bigint) RETURNS int(11)
BEGIN
  DECLARE learnavg BIGINT;
  DECLARE Assesavg BIGINT;
  DECLARE Pratiecavg BIGINT;
  DROP TABLE IF EXISTS AvgTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS AvgTemp (SType varchar(20),Percentage bigint);
  DROP TABLE IF EXISTS  assesTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS assesTemp AS (SELECT  COALESCE(ROUND(((SELECT uf_GetTotalScoreByAssessment( AssessmentID,pUserID,1)) /
  (SELECT ufn_getMaxMarks( AssessmentID))) * 100),0) AS Percentage FROM tr_t_assessments where AssessmentType='LiveTest');
  SET Assesavg= (SELECT COALESCE(round(AVG(Percentage)),0) FROM assesTemp);
  DROP TABLE IF EXISTS  learnTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS   learnTemp AS (SELECT IFNULL(ROUND((SELECT count(MaterialID) FROM tr_t_materialtracking
  where MaterialStatus=1 and UserID=pUserID and SubjectID = S.SubjectID and MaterialID in
  (SELECT MaterialID FROM tr_m_material where ClassID=pclass and SubjectID = S.SubjectID)) /
  (SELECT COUNT(TopicID) FROM tr_m_material where TopicID IN
  (SELECT TopicID FROM tr_m_subjecttopics where SubjectID = S.SubjectID))* 100) ,0) AS Percentage
  FROM tr_m_subjects S WHERE Status = 1 AND SubjectID IN (SELECT SubjectID FROM tr_m_subjects where ClassID=pclass));
  SET learnavg= (SELECT round(AVG(Percentage)) FROM learnTemp);
  DROP TABLE IF EXISTS  PratiecTemp;
  CREATE TEMPORARY TABLE IF NOT EXISTS PratiecTemp AS (SELECT COALESCE(ufn_getPracticePercentage(pUserID,pclass,S.SubjectID),0) as Percentage
  FROM tr_m_subjects S WHERE Status = 1 AND SubjectID IN (SELECT SubjectID FROM tr_m_subjects where ClassID=pclass)) ;
  SET Pratiecavg= (SELECT ROUND(AVG(Percentage)) FROM PratiecTemp);
  INSERT INTO AvgTemp(SType ,Percentage) VALUES('Learn',learnavg);
  INSERT INTO AvgTemp(SType ,Percentage) VALUES('Pratice',Pratiecavg);
  INSERT INTO AvgTemp(SType ,Percentage) VALUES('Assess',Assesavg);
RETURN (SELECT ROUND(AVG(Percentage)) as consolidatedAvg FROM AvgTemp);
END

Instead of DROP + CREATE, simply do TRUNCATE TABLE.  That should avoid the error about COMMIT.

Drawback:  Other code would need to originally do the CREATE and DROP.


Managing schema changes between transactional and analytical databases How would you manage changes in a transactional database that affect an analytical database?
Environment:

Transactional database is a current, or near current version of MySQL. Size of the transactional database appears to be around a few terabytes, but the growth of the database is just a few hundred megabytes a day.
Analytical database is a current, or near current version of Mondrian

Scenario: Given an analytical database that is automatically loading data from a transactional database -- when there are schema changes that result in large segments of the data being repositioned within the database transactional database that will result changes in the analytical database too. The only example I was given was that small portion of the schema for the transactional database changed in the database which to my understanding was about 300 tables; meaning I'd guess that 5-10% of the schema for the transactional database changes in some way either by adding/removing/moving column/tables/rows, and that in some cases there are global changes to how the data is formatted within the transactional database.
related meta question for this question.

You won't be able to automate this process.  Any release of the base system will need to include an impact analysis and change to the analytic system, and this will be a manual process.  You will have to update the ETL process to source data from the new structure and possibly modify the data mart as well.

This will add latency to the release process, and I've seen plenty of occasions where this was neglected and the analytic system broke.  The business or owners of the transactional system will have to do one of three things:

Incorporate impact analysis on the analytic system and ETL processes into their change control for the operational system.
Accept that releases will sometimes break the analytic system, possibly in ways that are not obvious (unrecognised semantic changes to the data).  Fixes to the analytic system may or may not be quick to implement.  It is possible they could do somethng that takes days or weeks to fix in the analytics.
Build a stable interface layer that the system exports data into and refactor the data mart ETL to populate via that interface.

Option 1 is almost always preferable unless the business cannot tolerate delay to the releases.  This might be the case with a .com type business where fast release schedules matter.

In the case where that impact on the release is not acceptable, then the business must either implement (3) or sign off that they are happy with (2) and accept responsibility for any downtime caused by changes that break the analytic system.

Option 3 will bog down changes to the analytic systems as they may become dependent on changes to the interface and therefore require releases of the operational system with changes to populate the updated interface.

If the business insists on sticking with (1) then make them sign off something accepting responsibility for unscheduled downtime on the analytics.  If you don't have a paper trail showing that you've raised this issue with them then you're at risk of copping the blame for something you have no control over.


Counting on Two Different Columns in the Same Table I run a pool league and have kept stats in Excel. I want to automate via a website and MySQL will be  my primary data analysis tool, though my knowledge there is only at the beginning of a long journey.

I have 2 tables (only showing relevant tables and relevant fields)

users:
--------------------
id            BigInt
display_name  VarChar(250)

match_results:
---------------------
id            BigInt
winner_id     BigInt
loser_id      BigInt

I want to produce a result set to show as a table of standings, like this:

NAME          WINS     LOSSES
-------------------------------
User2dn        12         7
User1dn        11         8
User5dn        10         8
etc.

I can manage the ORDER BY

I have other queries where I have successfully joined these two tables to get the display names for both winner_id and loser_id by using 2 different aliases for the same user table and that worked great, but I'm totally baffled here.

What I really need is more complicated than this, since I need to report from multiple other columns in match_results, the max of high_run (as a winner or loser) and bonus points which will be a sum irrespective of winner or loser status. I wanted to keep this question as simple as I could so I could understand it, and then build up the additional complications later.  Hope that's OK.

John,

If you have a table of users, and a table of matches, you should probably have a reference table between the two of them, that has a many-to-many relationship.

users:
--------------------
id (PK)       Int
display_name  VarChar(250)

matches:
---------------------
matchId (PK)   Int
...
...
  -- Other relevant attributes about the match

results:
---------------------
ResultId (PK)      Int
Name           Varchar(100)
  -- Win, loss, whatever else

match_results:
---------------------
matchId (PK, FK)  Int
UserId (PK, FK)   Int
ResultId (PK, FK) Int

For every match, you should have a record for every participant in that match, and their relevant result. Unless you plan on having more than 2 billion matches or users, I would stick to using Int.

Now you can join across these tables and SUM() or MAX() or whatever aggregate you would prefer.

SELECT u.Name ,
   COUNT( CASE
          WHEN r.Name = 'Win' THEN 1
          END )AS Wins ,
   COUNT( CASE
          WHEN r.Name = 'Loss' THEN 1
          END )AS Losses
FROM match_results AS mr INNER JOIN users AS u ON mr.userId = u.id
                       INNER JOIN matches AS m ON mr.matchId = m.id
                       INNER JOIN results AS r ON mr.resultId = r.id;

Edit:

Using your existing schema, you can use sub queries to aggregate your statistics

  SELECT u.display_name AS NAME,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.winner_id = u.id) AS Wins,
    (SELECT COUNT(1) FROM match_results mr WHERE mr.loser_id = u.id) AS Losses
  FROM users u


MongoDB - real disk usage is LOWER than dbStats.storageFile I'm a bit surprised by what I observe in my mongo server, so I would like to know if anybody understand how this is possible.

I have one collection of around 15K documents. For each of them, I store in GridFS 3 files, with an average size of 1MB per file. So the expected storageSize should be something near 60GB (if considering the size of the document itself to be 1MB too). I can't test all my data by hand, but I really want to be sure everything is here and that there is no missing data for some of the documents.

Here is what I get :

> db.stats();
{
    "db" : "myBase",
    "collections" : 6,
    "objects" : 348822,
    "avgObjSize" : 195475.27135329766,
    "dataSize" : 68186075104,
    "storageSize" : 69329817136,
    "numExtents" : 73,
    "indexes" : 14,
    "indexSize" : 42171808,
    "fileSize" : 67108864,
    "nsSizeMB" : 16,
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 5
    },
    "ok" : 1
}

so storageFile ~ 65GB, this is acceptable.

But when I look at the file system

$ ps -edf | grep mongo
user 20728     1  0  2014 ?        05:31:03 /opt/tools/bin/mongod --dbpath=/local/mongo-data

$ cd /local/mongo-data
$ du -h
 4.0K   ./_tmp
 721M   ./journal
 8.8G   .
$ du -h *
 721M   journal
 65M    myBase.0
 2.0G   myBase.37
 2.0G   myBase.5
 2.0G   myBase.7
 2.0G   myBase.8
 17M    myBase.ns
 4.0K   _tmp

How come the data on disk takes LESS space that what it says in mongo ?
Why doesn't appear anywhere a stat with a value of 8GB ?

I add stats from files and chunks if it can help understand what is happening

> db.fs.files.stats();
{
    "ns" : "myBase.fs.files",
    "count" : 48911,
    "size" : 14316208,
    "avgObjSize" : 292.69914743104823,
    "storageSize" : 18898944,
    "numExtents" : 7,
    "nindexes" : 2,
    "lastExtentSize" : 7647232,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 7873488,
    "indexSizes" : {
        "_id_" : 2076704,
        "filename_1_uploadDate_1" : 5796784
    },
    "ok" : 1
}

> db.fs.chunks.stats();
{
    "ns" : "myBase.fs.chunks",
    "count" : 283537,
    "size" : 67787872056,
    "avgObjSize" : 239079.45719958947,
    "storageSize" : 68819078704,
    "numExtents" : 48,
    "nindexes" : 2,
    "lastExtentSize" : 2146426864,
    "paddingFactor" : 1,
    "systemFlags" : 1,
    "userFlags" : 0,
    "totalIndexSize" : 27324192,
    "indexSizes" : {
        "_id_" : 11781616,
        "files_id_1_n_1" : 15542576
    },
    "ok" : 1
}

Edit : I add here the result of the command asked by Antonis

> db.fs.chunks.find({},{_id:1}).itcount()
283537

$ du -ah .
17M ./myBase.ns
2.0G    ./myBase.37
2.0G    ./myBase.7
2.0G    ./myBase.5
4.0K    ./_tmp
65M ./myBase.0
4.0K    ./journal/lsn
724M    ./journal/j._19
724M    ./journal
2.0G    ./myBase.8
8.8G    .

Whatever you do, do not shut down that mongod process until you back up your data (see below). There are missing files in that database directory, and I suspect they have been manually deleted at the OS level.  The data files should not have any gaps in them, ever.  In other words you should have files starting at myBase.0 all the way up to myBase.37, there should be no gaps in the numbers.

To explain, if you delete the files using rm or similar at the OS level it will succeed, the OS allows it, but because the mongod process that is running has an open file handle to the files they will not actually be deleted by the operating system until you stop the process.

Here's an example of what the lsof command shows for a normal data file called foo.0:

mongod     5786             adam  mem       REG                9,0   67108864
805306654 /data/db/test0/foo.0

And here is what it looks like when you have manually deleted the file:

mongod     5786             adam   24u      REG                9,0   67108864
805306654 /data/db/test0/foo.0 (deleted)

From within MongoDB that file still exists and is accessible, I can query, run db.stats() etc. successfully, but if that mongod process is restarted the file will be removed and the data is at that point essentially gone (barring efforts to undelete at the filesystem level).

So, what should you do?  Well, the first thing is to make sure you have a copy of the data before shutting down that process and losing it.  To do that you have a couple of options:

If this is a node in a replica set (even single), add a new secondary set member and let it sync - that will still succeed and then you will have a fully populated version of the data ready to take over on that secondary. (Note: If this is not a replica set you can't turn it into one without a process restart, and that would delete the data - my recommendation is to always run as a replica set, even a single node for anything in production)
Run mongodump to dump the data out somewhere else before it gets deleted.  This won't be fast, and you will need plenty of space, but at least it will give you an easily restorable version of your data

A repair on the database might work, but only if you have enough space to accommodate 2x the data plus index size on that disk.  It must be a repair command, not a restart with --repair because the restart would cause the files to be deleted.

Finally, you need to figure out what is deleting these files and stop it - is there a cron job or other process that is automatically deleting large files (the data files will usually be 2GB) over a certain age or similar?  I've seen things like that before wipe out MongoDB data files with similar results.


How to migrate MySQL Cluster (NDB) backup to a different ndb cluster? I have two MySQL Clusters (NDB), Prod and Dev. Each cluster contains SQL nodes, Management nodes and Data nodes. Backups are done on one cluster using the article below:

https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-backup-using-management-client.html

How do I import the created backup file to separate MySQL Cluster? i.e. refresh dev cluster with prod data.

The backup seems to be tied to the cluster it was created on. Is there a way to do a 'restore' on a separate cluster with the backup file?

Most certainly it is possible to restore a backup on a different
cluster. This is a standard usage of
backups and should be well covered by the documentation of
ndb_restore.


On docker volume restore: could not access file "$libdir/timescaledb-1.6.0": No such file or directory I am trying to restore TimescaleDB data from one ubuntu machine to another ubuntu machine. On the both machines we have deployed TimescaleDB through docker and using the same tag.
Docker image used: timescale/timescaledb:latest-pg11
I am creating a tar file for backup for the docker volume on the old machine, using below command:
docker run --rm -v $VOLUME_NAME:/volume -v /tmp:/backup busybox tar cvf /backup/$VOLUME_NAME.tar -C /volume ./

Also I am restoring using below command on the new machine:
docker run --rm -v $VOLUME_NAME:/volume -v $(pwd):/backup busybox sh -c "rm -rf /volume/* ; tar -C /volume/ -xvf /backup/$VOLUME_BACKUP_FILE"

Database restore was successful but when I see the docker logs I am getting below error:
2021-03-06 06:28:55.064 UTC [239] ERROR:  could not access file "$libdir/timescaledb-1.6.0": No such file or directory
2021-03-06 06:28:55.064 UTC [239] STATEMENT:  CREATE TABLE "__migration_lock" ("id" int NOT NULL, CONSTRAINT "PK_1312144c987e032cce38cfa00a2" PRIMARY KEY ("id"))

Do you know why this error is coming and how to resolve it?
Thanks

Docker images tagged with latest contain the latest release at the time you retrieve it (this is Docker's convention), thus the image in the source contains different TimescaleDB's version (1.6.0) than in the current latest image (2.1.0 is latest now). This leads into the error of missing binary, since the images are not the same and the current latest image doesn't contain 1.6.0, which was latest some time ago.
The easiest solution is to download the image with the same TimescaleDB version as in the source. I believe timescale/timescaledb:1.6.0-pg11 should work in your case.
You can double check, which TimescaleDB's version is in use, by calling \dx in each database.
Note that a TimescaleDB's image usually contains 5 older versions before the tagged one. So if you use image tagged 1.7.3-pg11 it is likely to work too, since it expected to contain binaries for 1.6.0-pg11. This is important to know if you are going to update TimescaleDB version from 1.6.0 to later, since you will need to use several images to get to the current latest version.
In general to avoid confusion it is better to use an image with specific version tag.


Preventing duplication on a Postgres table with 2 columns I am currently implementing a simple database schema (in PostgreSQL 11.1 (Debian)), with two int columns (say col1, col2).

My goal is to prevent duplicates appearing in any of the two columns.  If (1,2) already existed, then (1,2) or (2,1) would cause a "duplicate key value violates unique constraint" error.

For a practical example.  I have created a table someTable, with col1, col2, with a unique constraint (col1, col2).  Everything is fine when I insert (1,2) a second time:

CREATE TABLE someTable (
 col1 int NOT NULL,
 col2 int NOT NULL,
 unique (col1, col2));

- CREATE TABLE

INSERT INTO someTable (col1, col2) VALUES (1,2);
- INSERT 0 1

INSERT INTO someTable (col1, col2) VALUES (1,2);
- ERROR:  duplicate key value violates unique constraint "sometable_col1_col2_key"
DETAIL:  Key (col1, col2)=(1, 2) already exists.

INSERT INTO someTable (col1, col2) VALUES (2,1);
- INSERT 0 1

I would like the (col1, col2) unique constraint to work for swapped values (for example 2,1 is treated the same as 1,2).

Enforce a particular canonical ordering on the values:

alter table sometable add constraint sdljfsdfj check (col1<=col2);

If they are in the wrong order, the check constraint will reject them.  They are in the correct order the unique constraint will.

You could couple this with a BEFORE INSERT trigger to swap the order, if you don't want to force the application to deal with that logic.


Query user session length on a timestamped activity log I'm trying to retrofit some analytics on a customer's MySQL 5.5 table.

Given a simple but large table:  | user_id | timestamp |

A session_length is defined as the longest number of minutes without an hour of idle time.

When I run some SQL query, I get user_id, session_length for each span of time a user is using the system without an hour of idleness.

Example table:

1 | 01:10
1 | 01:11
1 | 01:12
1 | 01:13
1 | 01:14
1 | 01:15
              ...
1 | 03:59
              ...
1 | 12:10
1 | 12:20
1 | 12:30
1 | 12:40
1 | 12:50

Should result in:

user_id | session_length
1       | 5
1       | 1
1       | 40

I'm not worried about time delta granularity.

I found a hint that this is dealt with in other db's with LAG functions. No such luck in MySQL.

Plan A:

Create a table with an AUTO_INCREMENT PK, plus the 2 columns in question.  (It can't be a TEMPORARY table due to an unfortunate limitation.)
Do a "self-join" to pair up adjacent rows.  Put this (id, user, and pair of times) in another table.
Filter -- keep pairs that are more than 1 hour.  (Need to also keep first and last times; this may involve UNION.)
Subtract adjacent id pairs to get session_length.  (This probably involves another table and another AUTO_INCREMENT, but could be done with an @variable.)  (Note:  Be aware of auto_increment_increment; if it is not 1, the subtract will be 'wrong'.)

So, Yes, it can be done in SQL; but do you want to go to this much work?

Plan B:  Somehow use @variables to count the session_length, watch out for change in userid, test for 1 hour, etc.

Plan C:  Migrate to MariaDB 10.2 and use its Windowing Functions.


Failed to install Oracle JDeveloper 12c I want to install Oracle JDeveloper on my Ubuntu 18.04, and I have downloaded the installer file from here, there I download the file1 and file2 to Linux.

Here is the file name for file1, and the size is 2,0 GB:

jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427

And here is file name for file2, and the size is 432,1 MB:

jdev_suite_122130_linux64-2.zip?AuthParam=1561523346_b29a360806455b82e4a0db9e4e7767b9

I have tried to follow many tutorials how to install the JDeveloper, here is one of example tutorial that I try to follow.

There.., after I got the installer file, we should give permission access using this command:

sudo chmod +x jdev_suite*.bin

I ran the command to the file1 and file2, and here is permission:

harvest# ls -l jdev_suite_122130_linux64.bin\?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427
-rwxr-xr-x 1 tri tri 2045438518 Agu 26  2017 'jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427'
harvest# ls -l jdev_suite_122130_linux64-2.zip\?AuthParam=1561523346_b29a360806455b82e4a0db9e4e7767b9
-rwxr-xr-x 1 tri tri 432104829 Agu 26  2017 'jdev_suite_122130_linux64-2.zip?AuthParam=1561523346_b29a360806455b82e4a0db9e4e7767b9'

Then I start the installation using this command:

./jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427

I tried that command on normal user and also try as root, but I got this message after that:

Launcher log file is /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.
Extracting the installer . . . Done
Self extraction to /tmp/sfx_i5T36W failed.
The log is located here: /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.
ERROR: Installer execution failed (1).

After I looking at the log base on that message, here is the log:

harvest# cat /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log
2019-06-26 14:04:26,534 INFO  [1] com.oracle.cie.nextgen.launcher.LogUtils - Launcher log file is /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.
2019-06-26 14:04:26,544 INFO  [1] com.oracle.cie.nextgen.launcher.LogUtils - Extracting the installer . . .
2019-06-26 14:04:26,545 SEVERE [1] com.oracle.cie.nextgen.launcher.Launcher -
The jar file /home/tri/Downloads/oracle/jdeveloper_12c/jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d4-2.zip needed for this installer is missing.
2019-06-26 14:04:26,547 SEVERE [1] com.oracle.cie.nextgen.launcher.Launcher - Self extraction to /tmp/sfx_i5T36W failed.
2019-06-26 14:04:26,547 INFO  [1] com.oracle.cie.nextgen.launcher.LogUtils - The log is located here: /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.

I have tried to follow this instruction to Download both File1 and File2 and place it in the same directory, but still doesn't work.

So, please help me with this.

I resolved this following Kumar's comment:

  I fired up fresh Ubuntu 18.0.4 virtual box installed open jdk 8 and Oracle JDeveloper without any errors. From log files it looks like self extraction is failing something to do with parameter AuthParam and what exactly is that? What happens if you try without Authparam?

Just remove the AuthParam on that file.  Just Like this:

before:

jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427

after:

jdev_suite_122130_linux64.bin

And then install that as a usual way:

./jdev_suite_122130_linux64.bin


Shared memory error while connecting to Oracle I get this error message each time I try to connect to my Oracle XE local database under Ubuntu 15.10 :

user@user-X220:/u01/app/oracle/product/11.2.0/xe/network/admin$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Thu May 5 20:58:13 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: SYS
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

I have read on some forums that i have to delete my /dev/shm repository :

 sudo rm -rf /dev/shm
 sudo mkdir /dev/shm
 sudo mount -t tmpfs shmfs -o size=2048m /dev/shm

I tried actually to do that but it seems that /dev/shm is used by several processors including my firefox navigator.

Any help will more than welcome.

Looks like there are some hoops with fire you need to jump thru for installing Oracle XE on Ubuntu. You may want to try following the article here: http://meandmyubuntulinux.blogspot.com/2012/05/installing-oracle-11g-r2-express.html

Oracle is very finicky when it comes to installing on particular OS's. It's very specific as to what they support. However, that doesn't mean you can't install it on any OS you want. You'll just need to jump thru a few hoops... Some with fire.

Hope it helps!


Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper I have following example query.

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);

begin
      select '01-01-2000', '30-11-2011'

      into :pStartDateBegin,:pEndDateFinish
        from dual;

end;

-- SELECT  :pStartDateBegin,:pEndDateFinish FROM dual;

WITH EXAMPLE
AS
(
    SELECT OWNER,TABLE_NAME FROM DBA_TABLES T
    WHERE T.LAST_ANALYZED BETWEEN :pStartDateBegin AND :pEndDateFinish
)
SELECT * FROM EXAMPLE;

When I run this query using TOAD. It runs successfully.

XXXX rows selected.
Time End: 06.12.2011 12:05:37
Elapsed Time for Script Execution: 3 secs

In SQL Developer, I get following Error.

Error report:
ORA-06550: line 12, column 2:
PLS-00103: Encountered the symbol "WITH"
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

In sqlplus, I have similar error.

ATILLA@DENEME > @WithAndBindVariablesExample.sql
WITH EXAMPLE
*
ERROR at line 12:
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "WITH"

If I use following notation

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);

exec :pStartDateBegin := '01-01-2000';
exec :pEndDateFinish := '30-11-2011';

WITH EXAMPLE
AS
(
    SELECT OWNER,TABLE_NAME FROM DBA_TABLES T
    WHERE T.LAST_ANALYZED BETWEEN to_date(:pStartDateBegin,'DD-MM-YYYY') AND to_date(:pEndDateFinish,'DD-MM-YYYY')
)
SELECT * FROM EXAMPLE;

It runs successfully all three tools.

It seems that I lack a basic knowledge about this. I would like pointers to tutorials about this behavior, explanations etc.

In SQL*Plus and SQL Developer you need a / at the end of a PL/SQL block.

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);

begin
      select '01-01-2000', '30-11-2011'

      into :pStartDateBegin,:pEndDateFinish
        from dual;

end;
/

[...]


Select all rows that have all of the following column values I have the following tables Genres, Films, Directors. They have the following schema:
CREATE TABLE GENRES(
    GID INTEGER PRIMARY KEY,
    GENRE VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE Films(
    FID INTEGER PRIMARY KEY,
    Title VARCHAR(45) UNIQUE NOT NULL,
    DID INTEGER NOT NULL,
    GID INTEGER NOT NULL,
    FOREIGN KEY (DID) REFERENCES Directors(DID),
    FOREIGN KEY (GID) REFERENCES Genres(DID)
);

CREATE TABLE Directors(
    DID INTEGER PRIMARY KEY,
    First_Name VARCHAR(20) NOT NULL,
    Last_Name VARCHAR(20) NOT NULL
);

I want to write a query that will allow me to select all of Director information for every director that has made atleast one movie in the same genre(s) as another director. For example if Stanley Kubrick has made films in genres 'Sci-Fi', 'Thriller', and 'Crime', I want to select all the directors who have made at least 1 sci-fi AND 1 thriller AND 1 crime film.
I've tried the query seen below but this will give me directors who have made atleast 1 sci-fi OR 1 thriller OR 1 crime film.
SELECT DISTINCT D.DID, D.First_Name, D.Last_Name
FROM Directors D
LEFT JOIN Films F
ON F.DID = D.DID
LEFT JOIN Genres G
ON G.GID = B.GID
WHERE G.Genre IN (
  SELECT DISTINCT G1.Genre
  FROM Generes G1
  LEFT JOIN Films F1
  ON F1.GID = G1.GID
  LEFT JOIN Directors D1
  ON D1.DID = D1.DID
  WHERE D1.First_Name = 'Stanley'
  AND D1.Last_Name = 'Kubrick'
);

Additionally, I am not able to check before hand which Genres the director in question has been involved with. The query should work with the only given information being the Directors First and Last name.

This is one way to accomplish that, though there might be a simpler way:
WITH CTE_DirectorFilms_Distinct AS
(
    SELECT DISTINCT D.DID, F.GID
    FROM Directors D
    INNER JOIN Films F
        ON D.DID = F.DID
),
CTE_DirectorFilm_Matches AS
(
    SELECT DF2.DID, COUNT(DF1.GID) OVER (PARTITION BY DF1.DID) AS DF1_GID_COUNT, COUNT(DF2.GID) OVER (PARTITION BY DF2.DID) AS DF2_GID_COUNT
    FROM CTE_DirectorFilms_Distinct DF1
    LEFT JOIN CTE_DirectorFilms_Distinct DF2
        ON DF1.DID <> DF2.DID
        AND DF1.GID = DF2.GID
    WHERE DF1.DID = IdOfFirstDirectorGoesHere -- Or can filter on First Name and Last Name if you prefer
),
CTE_Director_Matches AS
(
    SELECT DISTINCT DID
    FROM CTE_DirectorFilm_Matches
    WHERE DF1_GID_COUNT = DF2_GID_COUNT
)

SELECT D.DID, D.First_Name, D.Last_Name
FROM Directors D
INNER JOIN CTE_Director_Matches DM
    ON D.DID = DM.DID

This first gets a distinct list of all Directors and the Genres they've created a film for. Then using a left outer self join it gets all Directors with their unique Genre counts and the Genre count for the Director you're interested in (this is based on that Director's DID but you can do First_Name and Last_Name instead if you prefer). Then it filters out all Directors who don't have a matching count of unique Genres to Director of the previous CTE. Finally it gets all the fields from the Directors table for those matching Directors.


Make sure MySQL is not accepting remote connections I am currently doing development for a LAMP-based website. I do not know what configuration changes have been made to MySQL since it was installed on our server. The way that we use MySQL, there is no reason for our database to accept any remote connections; it only needs to be accessed locally either via PHP or through the command-line shell over an ssh session.

For security reasons, I want to make sure that there is no way to connect to our database remotely. What settings do I need to check to make sure that this is the case? Is there a single option somewhere that I can set to prevent all remote connections?

Do:

netstat -an|grep 3306 | grep LISTEN

If something similar to the following line is returned:

tcp        0      0 0.0.0.0:3306                  0.0.0.0:*                   LISTEN

.. it means that it's listening on all interfaces.

If something similar to the following line is returned, and no other lines:

tcp        0      0 127.0.0.1:3306               0.0.0.0:*                   LISTEN

.. it's already configured to only listen on localhost.

If there are lines with other IP addresses before the :3306, it means that it's  listening on those interfaces.

To change MySQL to only listen on localhost, edit your configuration file (usually /etc/my.cnf), add the following:

bind-address = 127.0.0.1

Restart the service and voila!


How to find out if anything is running as or relies upon a user account in anyway before removing it? Running SQL Server 2008.

So I need to get around to removing an old users account, but I'm worried that just disabling or deleting it right away might cause some things to stop working properly if they were setup under that account and/or rely upon it in any way.

Is there a script I can run that will tell me if there is anything that might break if I get rid of a specific user account?

I don't think there's a surefire method to find everything. After all, they could have access to things merely by virtue of being in a specific server or database role, or even a Windows AD group (you didn't specify if this is a SQL auth login or a Windows login). There also isn't a surefire way to identify what might break if this is changed - for example, if they are the owner of a job or database, will changing that to a different owner cause any problems? Maybe, maybe not. I can think of a few things that are easy to find, but not really any way to verify they won't break without actually changing it and seeing what breaks.

For initial discovery, and this is by no means exhaustive (just a start for the most common things off the top of my head). Note that I've broken these into chunks for readability, but you'll want to fire them all at once to reuse the variables declared at the beginning.

SET NOCOUNT ON;
GO

USE [master];
GO

DECLARE @name NVARCHAR(128) = N'some login name',
---------- change this ---------^^^^^^^^^^^^^^^
        @sid VARBINARY(85),
        @pid INT,
        @sql NVARCHAR(MAX) = N'';

-- figure out their sid and *server* principal_id
SELECT @sid = [sid], @pid = principal_id
  FROM sys.server_principals
  WHERE name = @name;

-- linked server logins

SELECT [linked_servers] = COALESCE(remote_name,
  N'<<LOCAL>> (' + @@SERVERNAME + ')')
  FROM sys.linked_logins
  WHERE local_principal_id = @pid;

-- databases with authorization rights

SELECT [databases_owned] = name
  FROM sys.databases WHERE owner_sid = @sid;

-- jobs owned by this login

SELECT [jobs_owned] = name
  FROM msdb.dbo.sysjobs
  WHERE owner_sid = @sid;

-- jobs with this login having an execute as step

SELECT [jobs_with_steps_having_login_as_proxy] = j.name
  FROM msdb.dbo.sysjobs AS j
  INNER JOIN msdb.dbo.sysjobsteps AS s
  ON j.job_id = s.job_id
  INNER JOIN msdb.dbo.sysproxylogin AS proxy
  ON s.proxy_id = proxy.proxy_id
  WHERE proxy.[sid] = @sid
  GROUP BY j.name;

-- server roles they are member or owner of

SELECT [member_or_owner_of_server_roles] = r.name
  FROM sys.server_role_members AS rm
  INNER JOIN sys.server_principals AS p
  ON p.principal_id = rm.member_principal_id
  INNER JOIN sys.server_principals AS r
  ON r.principal_id = rm.role_principal_id
  WHERE p.[sid] = @sid OR r.owning_principal_id = @pid;

-- server permissions that have been granted or revoked

SELECT [server_permission] = [permission_name], state_desc
  FROM sys.server_permissions
  WHERE grantee_principal_id = @pid;

-- roles in database

SELECT @sql = N'';

SELECT @sql += ' UNION ALL
SELECT [database] = ''' + name + ''',
[member_or_owner_of_database_roles] = r.name
  FROM ' + QUOTENAME(name) + '.sys.database_role_members AS rm
  INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
  ON p.principal_id = rm.member_principal_id
  INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS r
  ON r.principal_id = rm.role_principal_id
  WHERE p.[sid] = @sid OR r.owning_principal_id =
    (SELECT principal_id FROM ' + QUOTENAME(name)
    + '.sys.database_principals WHERE sid = @sid)'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- permissions in database

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [permission] = state_desc
     + '' '' + [permission_name],
  [object] = COALESCE(QUOTENAME(s.name)
    + ''.'' + QUOTENAME(o.name) + '' (''
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS)
    + '')'', p.class_desc)
FROM ' + QUOTENAME(name) + '.sys.database_permissions AS p
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON p.major_id = o.[object_id]
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- modules in database with EXECUTE AS

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [object_with_execute_as] = QUOTENAME(s.name)
    + ''.'' + QUOTENAME(o.name) + '' (''
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS)
    + '')''
FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON m.execute_as_principal_id = dp.principal_id
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON m.[object_id] = o.[object_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- objects in database the login "owns" (ALTER AUTHORIZATION)

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [object_with_auth] = QUOTENAME(s.name)
    + ''.'' + QUOTENAME(o.name) + '' (''
    + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS)
    + '')''
FROM ' + QUOTENAME(name) + '.sys.database_principals AS dp
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON o.[principal_id] = dp.[principal_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

-- schemas in database the login "owns"

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT
  [database] = ''' + QUOTENAME(name) + ''',
  [schemas_owned] = QUOTENAME(s.name)
FROM ' + QUOTENAME(name) + '.sys.schemas AS s
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON s.principal_id = dp.principal_id
WHERE dp.[sid] = @sid'
  FROM sys.databases
  WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

I am sure there are others that could be added to the list - I have no problem helping figure out the query if you tell me what those things are.


Check and remove iteratively without an index Without sync priority or performance requirements on the task, I need to check all documents once per day and removing some of them.

The find filter is not static but can be defined within a db engine script function like function check_doc(){ }.

With .find() - no filter/condition in brackets - the function will check some non-indexed fields and decides whether the document must be removed or not.

.find().check_doc()/forEach the most effient way for iteratively checking each document, one by one, and remove justOne from there?

Furthermore, is there a variable available for checking the mongodb status, whether it is locked or sync delay (because of other queries and tasks on the DB)? If so, I'd prefer to delay the check_doc which has lowest priority. However, so far I could not find a way to decrease priority or a flag to check the status/delay.

The current version does not allow for specifying different priorities for different operations.  It is likely that maintaining an index on the collection to allow this periodic process to execute much quicker than a full collection scan will give you better overall performance.


SQL Server expensive nested loops join and lazy table spool I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is:

select distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
from datagatheringruntime dgr
 inner join processentitymapping pem on pem.entityid = dgr.entityid
 inner join document d on d.entityid = pem.entityid or d.unitofworkid = pem.processid
 left join PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
 where rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE)

I have updated the statistics for all the tables touched by the query (excluding the DataGatheringRuntime table which is quite big at ~100GB) and have tried re-factoring the query using a CTE but get the same execution plan and need some assistance.

The actual execution plan can be found here:

https://www.brentozar.com/pastetheplan/?id=ByUVIqlFE

It's clear from the execution plan that the problem lies with the outer input on the nested loop join specifically with the lazy table spool following the scan of the non-clustered IX_Camunda_1 index on the Document table but I have no idea how to tackle that issue and would appreciate any guidance.

I would try removing the OR clause in the join between document and processingentitymapping

You could do that with UNION

SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid
INNER JOIN document d on d.entityid = pem.entityid
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
UNION
SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid
INNER JOIN document d on d.unitofworkid = pem.processid
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE);

The reason being that the table spool is feeding the NESTED LOOPS operator

And on this nested loops operator is the OR predicate.

Filtering out until we have 9 rows remaining.

Changing the OR to a UNION should remove the spool, you might have to look into indexing after removing the OR.

Indexes that could improve performance after rewriting with UNION

CREATE INDEX IX_EntityId
on document(EntityId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

CREATE INDEX IX_UnitOfWorkId
on document(UnitOfWorkId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

See here for another example on this


Get every column in database that contains only Null records I am responsible for a poorly designed legacy application that has 700+ MSSQL tables and plenty of columns that are completely unused. I would like to run a script to return the column and table name of every column that has only null records. Does anyone have any suggestions on I could accomplish this?

As per my comment, the actual querying of the tables to check which nullable columns are completely NULL is the more difficult part to this problem.
To get you started you can use Dynamic SQL and the system DMVs to build the queries you'll need like this:
-- Temp table to store the results for later
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (TableName VARCHAR(1000), ColumnName VARCHAR(1000), RowsCount INT, NonNullCount INT)

-- Dynamic SQL variable to execute after we build it out recursively
DECLARE @DynamicSQL NVARCHAR(MAX) = ''

-- Uses the sys DMVs to recursively build which columns and tables to query
SELECT @DynamicSQL = @DynamicSQL +
'
     INSERT INTO #Results
     SELECT ' + S.[name] + '.' + T.[name] + ' AS TableName, ' + C.[name] + ' AS ColumnName, COUNT(*) AS RowsCount, COUNT([' + C.[name] + ']) AS NonNullCount
     FROM [' + S.[name] + '].[' + T.[name] + '];
'
FROM sys.Tables AS T
INNER JOIN sys.Schemas AS S
     ON T.[schema_id] = S.[schema_id]
INNER JOIN sys.Columns AS C
     ON OBJECT_ID(S.[name] + '.' + T.[name]) = C.[object_id]
WHERE C.is_nullable = 1;

-- PRINT @DynamicSQL -- Used for debugging
-- Executes the above dynamic SQL
EXEC sp_ExecuteSQL @DynamicSQL;

-- Get the final results of which columns only contain NULLs
SELECT TableName, ColumnName
FROM #Results
WHERE RowsCount > 0 -- If the RowsCount is 0 then the table is empty so can't determine if the column only contains NULLs when there's no rows to begin with (but you can remove this predicate if you want to include empty tables too)
     AND NonNullCount = 0 -- If the non null count is 0 then the column only contains NULLs
ORDER BY TableName, ColumnName

The above theoretically should answer the question. I had to type it on my phone and from memory, so if there's any minor syntactical errors, let me know and I can easily fix. You can also switch the comments around on the line that prints the dynamic SQL with the one that executes it, to help debug any syntactical issues with the dynamic SQL.


List all the MS SQL Server Objects ->LinkedServers->Providers using SQL How to List all MS SQL Server Providers List available using SQL.
like
 Microsoft.ACE.OLEDB.12.0
 Microsoft.Jet.OLEDB.4.0
 SQLXMLOLEDB.4.0
 SQLXMLOLEDB
 SQLOLEDB

Executing the stored procedure sp_MSset_oledb_prop without parameters will return a table of the available providers and their options:

EXEC sp_MSset_oledb_prop


SQL Server - query optimization for highly skewed data distributions I am trying to optimize a query which looks similar to this:

select top(1)
    t1.Table1ID,
    t1.Column1,
    t1....
    ....
    t2.Table2ID,
    t2....
    ....
    c.FirstName,
    c.LastName,
    c....
from BigTable1 t1
join BigTable2 t2
    on t1.Table1ID = t2.Table1ID
join Customer c
    on t2.CustomerID = c.CustomerID
join Table4 t4
    on t4.Table4ID = t2.Table4ID
join Table5 t5
    on t5.Table5ID = t1.Table5ID
join Table6 t6
    on t6.Table6ID = t5.Table6ID
where
    t4.Column1 = @p1
    and t1.Column1 = @p2
    and t3.FirstName = @FirstName
    and t3.LastName = @LastName
    and t6.Column1 = @p5
    and (@p6 is null or t2.Column6 = @p6)
order by t2.Table2ID desc
option(recompile);

BigTable1, BigTable2, Customer - are big transactional table (hundreds millions of rows), Table4,Table5,Table6 are relativly static and small lookup tables.
The problem is that data in those big tables has pretty skewed distribution and because of that very often this query performs very poorly (estimated number of rows in execution plan are very different from the actual). Updating statistics on those big tables does not help (200 steps in histogram are not enough to cover all the skews in data distribution). For example, in Customer table there are a few (FirstName, LastName) combinations that correspond to about 500k records.

I see 2 options to improve performance of such queries:

Split this query into smaller ones and saving intermidiate results
into temporary tables. With this approach of materializing
intermidiate results into temporary tables we can give the optimizer
a better opportunity  for cardinality estimations, but we add a
substantial additiobal load on tempdb (cause this query is executed
rather frequently, up to several times a second). And another
drawback is that it is not faster for all parameter values, it
appears to be significantly better for atypical parameter values
(when the original query can take minutes, this takes just few
seconds), but for parameters that correspond to a more o less unique
(or infrequent) rows the approach with temp tables is slower.
Create filtered statistics for the spike values, for example for
Customer table it would look like this:

declare @sql nvarchar(max) = N'', @i int, @N int;
select top (1000)
identity(int,1,1) as id,
FirstName,
LastName,
count(*) as cnt
into #FL
from Customer
where
FirstName is not null
and LastName is not null
group by
FirstName,
LastName
order by cnt desc;

set @N = @@ROWCOUNT;
set @i = 1;

while @i <= @N
begin
    select @sql = 'CREATE STATISTICS Customer_FN_LN_' + cast(id as varchar(10)) + ' ON dbo.Customer(CustomerID) WHERE FirstName = ''' + FirstName + ''' AND LastName = ''' + LastName + ''' WITH FULLSCAN, NORECOMPUTE'
from #FL
where id = @i;
exec sp_executesql @sql;
set @i = @i + 1;
end;

So if we create this filtered stats for those 3 big tables and re-create those stats, lets say, every week, we should be OK with row estimations in the original query.

From my previous experience I usually used approach with temp tables, but in this case it looks like the approach with filtered statistics is more attractive. I haven't use it in production yet though and I am curious what could be the drawbacks.

So my questions are:

Are there any other approaches to help optimizer deal with highly skewed data distributions?
What are the drawbacks of manually created and handled filtered statistics in the 2nd approach?

Are there any other approaches to help optimizer deal with highly skewed data distributions?

Filtered statistics and breaking the query up using intermediate temporary tables (correctly!) are the main options, but you could also consider how an indexed view might be used to help. Properly implemented, an indexed view should have approximately the same impact as an extra nonclustered index on the base table(s).

Using WITH (NOEXPAND) instead of relying on automatic matching (Enterprise Edition only) will allow the optimizer to create and use statistics on the indexed view as well.

More generally, if you are able to identify the 'safe' or 'unsafe' values ahead of time, you could consider a hybrid approach (including dynamic SQL) as detailed in Building High Performance Stored Procedures by Kimberly L. Tripp.

You could also consider multiple separate procedures optimized for different cases, using the appropriate approach in each, including hints like OPTIMIZE FOR.

Finally, you have plan guides and/or forced plans via Query Store (where available).

  What are the drawbacks of manually created and handled filtered statistics in the 2nd approach?

Mainly issues around the filtered statistics not being updated as frequently as one might hope. You can workaround this by refreshing these statistics manually.

You're already recompiling, so that should address the use of filtered statistics with parameterized queries.


Correct table quoting I want to generically use any SQL database(I'm using jdbc, it works if the driver and the URL are provided).

It seems, that different DBMS use different quoting for table names.

For example, I have the following in MySQL:

SELECT * FROM `tablename`;

while it is

SELECT * FROM [tablename];

in MS SQL Server.

Is there an "uniform" quoting that all databases support? Or is there a jdbc method that gives me tells me the correct quoting?

The SQL standard prescribes double quotes for quoted identifiers:

SELECT * FROM "tablename"

MySQL and SQL Server can be configured to comply with the standard, while other DBMSes comply out of the box. However, as others suggested in comments, you should avoid using quoted identifiers if possible, because it opens opportunities for bugs and unexpected behaviour in the SQL code.


What kind of changes does snapshot file store? Assume that I created a database called [DBS] and inside that database I created a table called [products].
Then I inserted 4 rows into [products].
Afterwards I took a snapshot of [DBS]. As a result, sparse file Snapshot_DBS.ss was created in my drive and in its properties, the size of file was 8 MB whereas the size on disk was 128KB (see attachment).
Let's go to the interesting part.
Whenever I made either INSERT or UPDATE operations on that table the size of Snapshot_DBS.ss on disk kept growing.
In the internet I found such an explanation: A snapshot contains the original version of pages created and changes in the source since the snapshot was created.

My 1st question is, what kind of changes does snapshot file store? Is it acting like t-log (storing every single changes made to [DBS])?

The 2nd question is, the part where it states "...and changes in the source since the snapshot was created" reminds me the working principle of Differential backup where latter takes all t-logs since the full backup was taken. Is their working principle similar somehow?

My 1st question is, what kind of changes does snapshot file store? Is
  it acting like t-log (storing every single changes made to [DBS])?

Database snapshots operate at the data-page level and not at log level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time. To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.

This is the point where log and data will change because log contains committed as well as uncommitted transactions.

  The 2nd question is, the part where it states "...and changes in the
  source since the snapshot was created" reminds me the working
  principle of Differential backup where latter takes all t-logs since
  the full backup was taken. Is their working principle similar somehow?

Differential backup and snapshot is not same and are not related. Snapshot doesn't consider last full backup and is a static view of source database.

Backup:
During a typical Full backup, all the pages of database are copied to a different location and this backup can be moved to a different Server, SAN, Tape, any other media. This can be used even after decades if required.
Back strategy can be designed and scheduled even for a point in time recovery of any time frame using Full, Differential, and transactional backup. This is a complete package with no gaps.

Snapshot:
A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation.

Major advantage of Snapshot over Backups is the time it takes to backup and restore especially when you want to take the backup and restore quickly in a short span of time. Referring to below image, Snapshot takes only seconds in the first step as there is no data change. In a typical deployment less than 1% of the data pages will be changed and restoration will also be very quick.

We also need to understand purpose of these two:

Snapshots are typically useful for purposes like audits and reporting. Another use for snapshot backups is that multiple snapshots can be created for a database, and these can be taken at different points in time. This helps with period-over-period analyses.

It is important to understand that database snapshots are directly dependent on the source database. Therefore, snapshots can never substitute your regular backup and restore strategy. For instance, if an entire database is lost, it would mean its source files are inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.

You may refer more details from below links:

https://www.sqlshack.com/understanding-database-snapshots-vs-database-backups-in-sql-server/

https://vijredblog.wordpress.com/2015/01/28/sql-snapshot-vs-backup-when-to-choose-snapshot-over-backup/

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-2017

Hope above helps.


Many tables v/s one table with different entities types My requirement is as follows: App with internationalization and mobile v/s desktop app text support. So, I have came up with below columns in the table which I will certainly need:

    "CODE" VARCHAR2(30 BYTE) NOT NULL ENABLE,  //Unique identifier
    "TEXT_EN" VARCHAR2(1000 BYTE),  //EN text for desktop version of app
    "TEXT_FR" VARCHAR2(1000 BYTE),  //FR text for desktop version of app
    "TEXT_EN_SHORT" VARCHAR2(500 BYTE), //EN text for mobile version of app
    "TEXT_FR_SHORT" VARCHAR2(500 BYTE),   //FR text for mobile version of app

Now, as per the device type and language I will fetch data from appropriate column, I am clear in this part.

Confusion: I will have several drop downs in the app like country, province, medicine names etc. So, should

I have one more field in the same table "FIELD_TYPE" which will identify whether this particular row is country or province etc. So, to fetch all country details my query will have  FIELD_TYPE = "Countries"
Or I should have different table for each - country, province, medicine names etc.

Which is more space efficient and query performance efficient and WHY?

Here is the related question posted by myself and kept is separate to isolate the concern, but you may choose to post your overall answer in either question.

I know the probable choice in each case, but I want to weigh and understand the pros and cons in each case, and why I should choose one and not other!

It is discussed here but for same w.r.t entities.

You could really use either option: one translation table with a FIELD_TYPE partitioning attribute, or a one translation table for each table requiring localization.

The pros and cons of either approach are not likely to be related to the criteria you ask about: space or query performance.  These aren't necessarily the most important criteria for you to be concerned about either.  Unless you have really substantial scalability requirements then you should focus on code maintainability as a differentiator rather than disk space or query performance.

Another factor to consider is how much time you want to spend explaining your choices to other developers.  If you use a single table with a partitioning attribute, then you are opting for something very much like Entity Attribute Value (EAV) which is widely frowned upon for many mostly good reasons.

On the other hand, having a translation table per translatable object lets you tailor your column sizes to the actual data rather than picking the largest common denominator and also use tools like declarative referential integrity which are generally seen as best practices.