How to SELECT from three different tables using multiple foreign keys I have 3 tables:

languages

id | tag   | region        | language
--   -----   -------------   --------
1    en-US   United States   English
2    es-ES   Spain           Spanish
...

modules

id | module
--   ------
1    header
2    main
3    footer

contents

id | language_id | module_id | content
--   -----------   ---------   -------
1    1             1           This is my header
2    1             3           This is my footer
3    2             1           Este es mi encabezado

I want to select all content for all of the expected modules, even if there's no contents record for that module, (notice I'm missing a "main" module) so I've written:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id

...which nicely returns

module | content
------   -------
header   This is my header
header   Este es mi encabezado
main     null
footer   This is my footer

But now I need to select only the modules with the language_id that maps to 'en-US'. If I amend my JOINs I lose my "main" module in my result:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'en-US'

module | content
------   -------
header   This is my header
footer   This is my footer

I am very new to JOINs. What am I missing?

It's pretty obvious.  You JOIN to languages filter out the null rows because there's no matching to the languages table.  The combination of RIGHT JOIN and LEFT JOIN is bad, but it should work.

    SELECT m.module, m.content
    FROM `modules` m
    LEFT JOIN contents c ON m.id = c.module_id
    LEFT JOIN languages l ON c.language_id = l.id
    WHERE l.tag = 'en-US'


How to dig deeper into ASYNC_NETWORK_IO waits I've been handed a graph that comes from SolarWinds showing top wait events broken down by day.   The last 2 days there's a part of the daily color coded bar representing a total of 3 hours for ASYNC_NETWORK_IO.  Still trying to see if I can get access to Solar Winds panels to try and see if it offers a drill down into the graph or not.
I've been searching through google, websites and documentation for the last 2 hours and finding little of consequence to tell me how to drill down into this 3 hours.
Is it one large block, is it a few minutes here and there through out the day, does it match any particular window of high I/O activity (data warehouse refresh or something).
All I have is "here, there's a total of 3 hours of this wait yesterday and the day before, now tell me why" - and I must admit I really don't have any idea on how to drill down into this any further.
I've read all sorts of articles about "Well, it's usually a poorly designed application" or something along those lines.
Funny this is of the last 7 days, the first 5 are all clear.  No signs of this wait.  Suddenly there's a huge chunk of it.  I need to know how I can dig more.
As far as I'm aware there are no user complaints about system performance.
Is there a DMV or something to help out here ?
Anyone able to give me some pointers?
Thanks

ASYNC_NETWORK_IO is never a server side problem.
This wait indicates SQL Server has results to send to the client, but is waiting for the client to consume the results so it can finish sending.
It's possible (though unlikely) that a slow network is the bottleneck. This almost never is the reason, unless someone is using dial up quality network connections. The fix here is a faster network connection. (But that's not your problem.)
It's also possible that the application is explicitly stopping it's consumption of results due to bad design. This will happen if the application accepts the first 25 rows to display, then stops accepting results forcing the server to wait.... And only taking the next 25 rows when the user hits the "next page" button. In this case, you'd need to change the application to either consume the whole result set at once, then handle pagination just in the display, or to change the app to simply ask for one page of results and do a round trip for now 6 every time the user hits "next page".
Lastly, it could be a client machine that is underpowered, or a poorly performing application. SSMS is one of those poorly performing applications. If you do a SELECT * FROM dbo.HugeTrillionRowTable you'll see ASYNC_NETWORK_IO waits pile up for your session. The right fix here depends on the details. You might need to add more memory to the client machine, or scale out to more client containers, or have a code change in the app (that's the fix for SSMS, but it's not a trivial change).
In your case, if you can get read only access to SolarWinds DPA, you will be able to drill into that wait to detrimine the query, client, etc to get into those details. If you can't access DPA, you can monitor sys.dm_exec_session_wait_stats, though this will only show you info for currently running queries, so you'll need to catch the offending code in the act. There isn't a "time machine" DMV to go back after the fact.


Oracle RAC, unable to connect to node vip I have a new oracle RAC installation running 11.2.0.4 on redhat 6. I can connect to sqlplus from a client using the node name but it is timing out when i use the node vip

eg. first one works, second one doesn't

sqlplus system/xxxxxxx@csya-orarac1.xxxx-dmz.local:/dev1.xxxx-dmz.local
sqlplus system/xxxxxxx@csya-orarac1-vip.xxxx-dmz.local:/dev1.xxxx-dmz.local

i can successfully ping and run an nslookup against the VIP

the local_listener looks like this

local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=csya
                                                 -orarac1-vip.xxxx-dmz.local)(P
                                                 ORT=1521))))

i can successfully ping between the 2 node vip names from the servers themselves.
the VIP is defined in the /etc/hosts file, and the node vip shows when performing an ipconfig

eth1:5    Link encap:Ethernet  HWaddr 00:50:56:85:60:79
          inet addr:172.28.1.95  Bcast:172.28.1.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

the listener on the node, knows of the vip ip address

./lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-MAY-2015 11:13:45

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                11-MAY-2015 10:26:10
Uptime                    0 days 0 hr. 47 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/csya-orarac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.1.91)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.1.95)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "dev1.xxxx-dmz.local" has 1 instance(s).
  Instance "dev11", status READY, has 1 handler(s) for this service...
Service "dev1XDB.xxxx-dmz.local" has 1 instance(s).
  Instance "dev11", status READY, has 1 handler(s) for this service...
The command completed successfully

this is part of a bigger issue i have been having with connecting to the SCAN name, but using the oracle trouble shooting doc 975457.1 it looks like it may be a local listener problem with the vip

any ideas?

The issue turned out to be with the VIP addresses not being allowed to be accessed via port 1521 due to our firewall policy. These IPs are now permitted and the problem has been resolved. thanks to Balazs for all the help/


Recreated database or clear all data on import to it in MySQL How to recreate db during import of the dump?
By recreate, db drop or deletion of its data is meant.

mysql db -u root -p < dump.sql

It's possible of course to drop and create db manually or automate it using bash, but as import is run frequently, i would like it to be done automatically via mysql command if it's possible.

You need to add this mysql command in order to drop and create database in a dump script:

DROP DATABASE IF EXISTS `database_name`;
CREATE DATABASE `database_name`;


ORA-00604: error occurred at recursive SQL level 1 I was compiling a package on an Oracle Database just fine and suddenly this error occurred:

ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_IDENTIFIER$ by 8 in tablespace SYSAUX
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I believe it's a Tablespace issue and raised the issue to our DBA Team. However, my colleague tried the same package on the same database, using the same user and was able to compile it just fine. We checked and our Database credential details are the same as well.

Any idea why it's not compiling in my SQL Developer but his is working fine?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    0
PL/SQL Release 12.1.0.2.0 - Production  0
"CORE   12.1.0.2.0  Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production  0
NLSRTL Version 12.1.0.2.0 - Production  0

Even if you use the same credentials, your colleague may have a different environment with PL/Scope disabled.

SQL> create procedure p1 as
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

Compile the procedure without PL/Scope:

SQL> show parameter plscope

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plscope_settings                     string      IDENTIFIERS:NONE
SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

No change. Now compile with PL/Scope enabled:

SQL> alter session set plscope_settings='IDENTIFIERS:ALL';

Session altered.

SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2058

This created some extra metadata in the above table which happens to be in the SYSAUX tablespace. Now disable and compile again:

SQL> alter session set plscope_settings='IDENTIFIERS:NONE';

Session altered.

SQL> alter procedure p1 compile;

Procedure altered.

SQL> select count(*) from PLSCOPE_IDENTIFIER$;

  COUNT(*)
----------
      2057

Back to the original state.

This is the problem:

ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_IDENTIFIER$ by 8 in tablespace SYSAUX, which simply means the SYSAUX tablespace is full.

Being unable to compile the PL/SQL code with PL/Scope enabled is just a symptom of the above error. Ask the DBA to make/increase space in the SYSAUX tablespace. PL/Scope is a configurable option in SQL Developer (Tools - Preferences - Database - PL/SQL Compiler - PLScope identifiers).


Mysql Impossible WHERE and enum I'm using enum as an alternative to check constraint which is still not supported by mysql.
Could you clarify why optimizer doesn't take into account allowed values for enum ? For example,

CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
insert into test_1(val) values ('A');
1. explain select * from test_1 where val is null;
2. explain select * from test_1 where val ='C';

The first explain shows impossible where in Extra, but the second doesn't. There is no way val can contain 'C' (as well as null), and it can be deducted from table definition without looking into the table.

Thanks.

I create the sample you gave with this:
drop database if exists a1ex07;
create database a1ex07;
use a1ex07
CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
insert into test_1(val) values ('A');
explain select * from test_1 where val is null;
explain select * from test_1 where val ='C';

Here is it loaded:
mysql> drop database if exists a1ex07;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> use a1ex07
Database changed
mysql> CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_1(val) values ('A');
Query OK, 1 row affected (0.06 sec)

mysql> explain select * from test_1 where val is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> explain select * from test_1 where val ='C';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_1 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

In the first EXPLAIN plan, the Impossible where would be based on the definition of the column you gave, which is val enum('A','B') not null. This would help the MySQL Query Optimizer quickly rule out unneccesary evaluations, especially if the table had millions of rows.
In the second EXPLAIN plan, the Using where indicates that MySQL Query Optimizer would have to evaluate a potential value (C) to see if it resides in the table. I believe what you are looking for is for the MySQL Query Optimizer to check the definition of the column, which is enum('A','B'). It should but it probably doesn't. You should submit that as a bug report to see if Oracle, out of the goodness of their heart, will address this.
UPDATE 2012-01-12 14:08 EDT
I just looked around mysql website for any bugs regarding EXPLAIN and ENUM. At this time, there are no reported bugs of ths nature. You should submit this one ASAP.


PostgreSQL 9.6 log shipping replication stuck on startup Overview

We have been trying to restore replication between a Master and Replication server for a while, but the individual who had set up has left and we've been working at it for a while. In brief, the issue seems to be that the Replication server starts up and within a few minutes reports postgres: startup process    recovering [FILENAME] and never advances beyond that same FILENAME.

Configuration

Master

pg_hba.conf

local   replication             replicator                      trust
host    replication             replicator    10.1.20.181/32    trust

postgresql.conf

(only active settings related to replication)

wal_level = hot_standby         # minimal, archive, hot_standby, or logical
fsync = on                      # turns forced synchronization on or off
wal_sync_method = fdatasync     # the default is the first option
archive_mode = on               # allows archiving to be done
archive_command = '/bin/true'   # Temporary until replication is working
archive_timeout = 300           # force a logfile segment switch after this
max_wal_senders = 5             # max number of walsender processes
wal_keep_segments = 600         # in logfile segments, 16MB each; 0 disables
max_replication_slots = 3       # max number of replication slots

Replication Server

postgresql.conf

hot_standby = on

recovery.conf

standby_mode = 'on' # enables stand-by (readonly) mode

# Connect to the master postgres server using the replicator user we created.
primary_conninfo = 'host=10.1.20.180 port=5432 user=replicator'
primary_slot_name = 'oh_slot'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/tmp/pg_failover_trigger'

Replication Set up Process

On the Master, we clear out all old WAL files by using pg_controldata to find the current WAL file and delete anything older

On the Replication Server:

Stop the database
Unmount pg_xlog directory
Delete contents of data directory
Run pg_basebackup -Xs -U replicator -h #{SOURCE_DB} -D /var/lib/postgresql/9.6/main -v
Copy recovery.conf into new data directory
Move the pg_xlog directory, create a new one and mount it, then copy the archive files over to the new directory and fix permissions
Start up the database

At this point, I've seen the startup process process a number of WAL files, but it always (and rather quickly -- like within a few minutes) stops and continually reports that it is recovering the same file indefinitely. Checking tables between the two databases confirms that the replication database hasn't added new records that are in the master database.

I get the feeling that I am missing something fundamental and would greatly appreciate questions and guidance.

Updates

Luan Huynh: I had the false impression that there were no errors upon startup, but the log has the following 2017-04-16 06:38:32 EDT [13029-1] FATAL:  could not start WAL streaming: ERROR:  replication slot "oh_slot" does not exist; so clearly I did not create the replication slot on the Master as I thought I had.

KookieMonster: I had found advice to set the archive_command to true as a debugging technique. $FILENAME is the last of the files in the pg_xlog directory as it had been copied during the pg_basebackup. The Master indeed has additional WAL files that were created after $FILENAME, but the error in the log seems to clearly indicate why the system has not been been moving over the WAL files created after the pg_basebackup.

I'll verify the replication slot on the master and update.

Thanks to Luan Huynh, I checked the logs on the Slave and saw that it was not able to start WAL streaming due to the missing replication slot on the Master (I really was certain it had been created! :-) )

Creating the replication slot with pg_create_physical_replication_slot was all that was needed.  Verifying the replication slot with SELECT * FROM pg_replication_slots showed the slot had been created and WAL streaming was active.

I was able to verify that the latest records on highly active tables on the Master are being successfully replicated on the Slave.


SQL Server: test # of partitions == partition_functions.fanout I am attempting to write a test for already partitioned schemas.

The test should confirm that each schema was partitioned # number of times where  # = sys.partition_functions.fanout.

I have found the following code: (according to my understanding gives the number of partitions per schema)

select distinct t.name
from sys.partitions p
inner join sys.tables t on p.object_id = t.object_id
where p.partition_number <> 1

I am currently attempting to understand how to connect each table to the partition function 'fanout' value.

Ideally I would like to be able to create a table with the following columns:
table name, number of partitions, sys.partition_functions.fanout

Not sure why you want to do this, but some of the dmv links are a bit tricky to figure out from BOL. I believe the following meets your requirements:

SELECT t.name, MAX(pf.fanout) fanout, COUNT(*) cnt_partitions
FROM sys.tables t
INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.[type] IN (0, 1, 5) -- heap, CI, or CCI
GROUP BY t.name;

If I create a few partitioned tables:

CREATE PARTITION FUNCTION functioning_part_function
(BIGINT)
AS RANGE LEFT
FOR VALUES (
  0
, 5000000
, 10000000
, 15000000
, 20000000
, 25000000
, 30000000
, 35000000
, 40000000
, 45000000
, 50000000
);

CREATE PARTITION SCHEME scheming_part_scheme
AS PARTITION functioning_part_function
ALL TO ( [PRIMARY] );

CREATE TABLE dbo.PART_TABLE (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

CREATE TABLE dbo.PART_TABLE_2 (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

I get the following results:

╔══════════════╦════════╦════════════════╗
║     name     ║ fanout ║ cnt_partitions ║
╠══════════════╬════════╬════════════════╣
║ PART_TABLE   ║     12 ║             12 ║
║ PART_TABLE_2 ║     12 ║             12 ║
╚══════════════╩════════╩════════════════╝


What must be in place to validate a XMLTYPE against a schema? I have a procedure that generates an XMLTYPE and I want to validate it against a schema.  The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the procedure as AUTHID DEFINER it gives the error "ORA-31050: Access denied", but when I run it as AUTHID CURRENT_USER it actually returns a validation specific error (I'll deal with that separately).  CURRENT_USER is not an acceptable solution.

My supposition is that CURRENT_USER works because the user has the XMLADMIN role.  Granting the permissions the role includes does not resolve the issue, so it must be the roles ability to bypass the ACLs.

The thing is, querying RESOURCE_VIEW for the ACL that protects the resource shows that it is protected by /sys/acls/all_owner_acl.xml.  DBMS_XDB.getPrivileges shows that the xsd has all the following permissions:

  <read-properties/>
  <read-contents/>
  <write-config/>
  <link/>
  <unlink/>
  <read-acl/>
  <write-acl-ref/>
  <update-acl/>
  <resolve/>
  <link-to/>
  <unlink-from/>
  <dav:lock/>
  <dav:unlock/>
  <dav:write-properties/>
  <dav:write-content/>
  <dav:execute/>
  <dav:take-ownership/>
  <dav:read-current-user-privilege-set/>

Using DBMS_XDB.getAclDocument shows a principal of dav:owner has all privileges, so that must not be enough to allow the owner of the schema to create schema based XML.  With this thought in mind I created a block to run DBMS_XDB.createResource creating a new ACL.  I can successfully create the ACL and from SQLDeveloper I can see that it exists in the location I created it in.

There are any number of places I could be going wrong in this process, so the core of what I am looking for is this:

What must be in place to validate a XMLTYPE against a schema?

=== Update 4/3/2013 ===
I can set the acl for my xsd file to /sys/acls/all_all_acl.xml and back to /sys/acls/all_owner_acl.xml.  As before, neither of these resolve the permissions issue.  I also tried a createResource using the acl definition copied from all_owner_acl.xl and using the same path as those files of /sys/acls/.  This at least successfully sets the ACL.  I have run it for all my XSDs and queried RESOURCE_VIEW to confirm that they are set to the new ACL.  After all this though, createSchemaBasedXML still gives an access denied error.  Is my ACL correct or might there be another issue?

<acl xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
   xmlns:dav="DAV:"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
      http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
       <ace>
         <grant>true</grant>
         <principal>MY_ORACLE_USER</principal>
         <privilege>
           <all/>
         </privilege>
       </ace>
     </acl>

=== Update 4/9/2013 ===
I have an anonymous block that can successfully validate XML based on a schema.  This again points to a permissions issue that allows this to work when roles are enabled, but not when they are not available.

== Update 4/12/2013 ===
Every bit of evidence I get seems to indicate that there is something wrong with my ACL or perhaps more likely with the way I set the ACL.  Taking the XDBADMIN role away from the user causes the anonymous block to fail with an access denied error even though I have granted all the permissions the role gives according to dba_tab_privs.  My setACL follows this form:

DBMS_XDB.setACL('/sys/schemas/MY_ORACLE_USER/account.xsd', '/sys/acls/acl_acc.xml');

A complete test case can be found on Oracle Communities.

My test case ran fine on multiple other databases and multiple sources (including oracle support) asked if the ANONYMOUS user had been dropped.  Although it had not, there were some XML DB Repository changes that were made without a full understanding.  To try short cut the problem I re-installed XDB using note 1292089.1.  This allowed my test case to work correctly and after dropping the created types and tables and re-registering our XML schema, everything worked correctly.


Is splitting a 'users' table for authentication purposes a good idea? Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table.

For speeding up my login process, is it a good approach to split my user table, one for their information and one for their login.

If we can run a query similar to the one below from one table:

select username,password from users where username=`test` AND password=****

Is it necessary to split it, and does this speed up my site's login process?

IMHO You do not need to physically split it up. Yet, it would be nice to cache it.

If the users table uses the MyISAM Storage Engine, you have a nice advantage.

Since MyISAM only caches indexes, you could do two things

You could create a custom key cache just to load MyISAM index for the users table only
You could index the username and password to force the query to hit that custom key cache only

Make sure the following indexes exist for users

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

There are two(2) major reasons for the two indexes

REASON for index #1

The index username_ndx prevents a username from having multiple passwords, as well as prevents multiple users with the same name

REASON for index #2

The index username_password_ndx provides a covering index. Thus, your query will  lookup the username and password in the custom MyISAM cache only, instead of checking the table.

More Links on the Principles of Covering Indexes

http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/
http://shallop.com/2011/04/covering-indexes-in-mysql/
http://www.mysqlconf.com/mysql2009/public/schedule/detail/6796

Next thing is to actually create that custom key cache. Here are the commands to create an 8MB key cache and load that dedicated key cache (Example: If the table is mydb.users):

SET GLOBAL authentication_cache.key_buffer_size = 1024 * 1024 * 8;
CACHE INDEX mydb.users IN authentication_cache;
LOAD INDEX INTO CACHE mydb.users;

You should place these three lines in the file /var/lib/mysql/startup.sql

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/startup.sql

This will load the cache every time mysql is started up

Give it a Try !!!

UPDATE 2011-12-30 17:25 EDT

If you would like to get the exact size to set the cache, use the following query:

SELECT CONCAT('1024 * 1024 * ',ROUND(index_length/power(1024,2))) RecommendedCacheSize
FROM information_schema.tables WHERE table_name='users';

UPDATE 2011-12-30 23:21 EDT

Here is a method based on InnoDB

You still need the indexes

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

You have to make sure the InnoDB Buffer Pool has the usernames and passwords available. You may have to resort to doing a full index scan upon mysql startup:

Step 1) Create ReadUserPass.sql

echo "select username,password from users;" > /var/lib/mysql/ReadUserPass.sql

Step 2) Add that script to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/ReadUserPass.sql

Step 3) Perform one of the following

$ service mysql restart
mysql> source /var/lib/mysql/ReadUserPass.sql

Because both of these columns (username and password) reside in the username_password_ndx, all the index pages making up this index are reloaded into the InnoDB Buffer Pool. This is necessary because there is the possiblility of the index pages being flushed out. To minimize that happening, increase the Buffer Pool Size and restart mysql (one time).


Slony-I replication stopped working I inherited a 5 node postgres cluster running postgres 8.4 and slony 1.2.21. We are in the process of migrating the application to all new code and have not wanted to do very little maintenance on the cluster. Yesterday we decided to take down two nodes out of the cluster which were not being used. We used the slonik script to DROP NODE for the two nodes in the cluster. This seemed to work correctly and we shut down the nodes today. However I noticed this morning that our master database where the collect writes is not replicating the changes to the rest of the servers. I have tried everything I can think of but it appears but nothing seems to work.

When I run a query to collect the status I see that events are not being acknowleged since yesterday. The st_last_received has not changed at all.

 st_origin | st_received | st_last_event |      st_last_event_ts      | st_last_received |    st_last_received_ts     | st_last_received_event_ts  | st_lag_num_events |              now
-----------+-------------+---------------+----------------------------+------------------+----------------------------+----------------------------+-------------------+-------------------------------
        25 |          24 |      26196903 | 2016-11-29 17:39:06.859051 |         26187885 | 2016-11-29 12:51:45.396619 | 2016-11-28 11:11:48.909855 |              9018 | 2016-11-29 17:39:07.247598-05
        25 |          27 |      26196903 | 2016-11-29 17:39:06.859051 |         26187885 | 2016-11-28 11:11:49.203193 | 2016-11-28 11:11:48.909855 |              9018 | 2016-11-29 17:39:07.247598-05
        25 |          26 |      26196903 | 2016-11-29 17:39:06.859051 |         26187885 | 2016-11-28 11:11:50.253235 | 2016-11-28 11:11:48.909855 |              9018 | 2016-11-29 17:39:07.247598-05

I first restarted the slony daemons on all the nodes and have subsequently done this multiple times.  I have set debugging to level 4 for debug logging and have combed through them without finding a single issue.

I have looked through all the .sl_ tables for anything that might tell me why it is not working.

Our configuration is as follows for the important replication set.

select * from _ads.sl_set;
 set_id | set_origin | set_locked |   set_comment
--------+------------+------------+-----------------
      1 |         25 |            |  mgt tables

select * from _ads.sl_subscribe ;
 sub_set | sub_provider | sub_receiver | sub_forward | sub_active
---------+--------------+--------------+-------------+------------
       1 |           25 |           26 | t           | t
       1 |           25 |           27 | t           | t
       2 |           25 |           27 | t           | t
       1 |           25 |           24 | t           | t

select * from _ads.sl_listen ;
 li_origin | li_provider | li_receiver
-----------+-------------+-------------
        24 |          24 |          25
        26 |          26 |          25
        27 |          27 |          25
        27 |          25 |          26
        26 |          25 |          27
        27 |          25 |          24
        24 |          25 |          27
        24 |          25 |          26
        26 |          25 |          24
        26 |          24 |          25
        27 |          26 |          25
        26 |          27 |          25
        24 |          26 |          25
        27 |          24 |          25
        24 |          27 |          25
        25 |          25 |          24
        25 |          25 |          26
        25 |          25 |          27

Any advice assistance, or an idea on where to look would be greatly appreciated. I am in full on panic mode now.

OK, this was a nightmare but we figured but we resolved the problem. We were able to remove the servers using the slonik DROP NODE command. For a while the slony services was segfaulting repeatedly. We had to DROP NODE on all the slaves and then add and subscribe them again. This caused the tables to be dropped and then it ran a copy set to restore the tables. It is working again. We were very hesitant to run DROP NODE on these due to this being a destructive operation. However the replication servers are running as expected again.

I am so looking forward to turning off this service permanently.


MySQL indexing issue when trying to search for a part of a string/word What we're running into:

Engine: MySQL.

We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets.

We need to search for partial words; say we have the reference AB12345678 - the user wants to find this reference by either searching AB, B123, 1234 or any other combination which contains part of this string.

We have an INDEX on reference & date

We're currently using LIKE %STRING%, but this cannot be indexed - and performance is bad in certain situations:

(1) When we search for AB we get a fast result.

(2) When we search for AB12345678 we get a slow result.

Both situations have ORDER BY date & LIMIT.

When we turn off either ORDER BY or LIMIT with situation (2), we also get a fast result.

A slow result is around 14-15 seconds of query execution time.

The dataset contains around 300k results.

What we've tried so far:

We tried implementing FULLTEXT indexes and MATCH .. AGAINST queries, however MATCH .. AGAINST doesn't allow us to search for both sides, the * wildcard is only allowed at the end of the input string.

We've also tried removing the INDEX on date. This gave us faster results (around 1/3th of the time) however, the query still took around 4-5 seconds.

We're kinda lost on the best implementation that can improve our performance on this query and how we can best solve it right now. What method should we use to get our performance back and be able to search on both sides?

Below is part our query which has slow results (we removed the SELECT part):

SELECT SQL_NO_CACHE *
FROM orders o0_
WHERE 1=1
AND o0_.customer_id = 130
AND (o0_.reference LIKE '%AB12345678%')
ORDER BY date4 DESC
LIMIT 50
OFFSET 0;

If you can get rid of the leading wildcard (%), do so; then we can discuss better indexes.

Meanwhile, use INDEX(customer_id, date4); it has some chance of speeding up the query.

What version are you running?

Please provide SHOW CREATE TABLE if you want to discuss this further.


Static service not working when using SCAN? I know the correctly way to configure service is creating a resource in with srvctl add service command, but I've seen a behavior after configure a static listener in a RAC environment. It's not possible to connect in the static service using the SCAN, just with the vip/public IP. This behavior is correctly? or probably a bad configuration in my environment?
Usually a just configure a static listener in a RAC when using data guard broker, which broker uses the vip.

Static registration is no longer required starting with 12.1.0.2, as the DG broker uses Grid Infrastructure to restart instances. Of course, if you have a single instance database without Grid Infrastructure, there it is still required.
Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Also in the above document:

From 11.2 on, never register the entries with the SCAN listener as it
is not associated with a specific host.

I have never seen anyone using static registration in SCAN listeners, so I had to try this myself. On a 2-node cluster with 3 SCAN listeners, I added the below to listener.ora, first node:
SID_LIST_LISTENER_SCAN1,SID_LIST_LISTENER_SCAN2,SID_LIST_LISTENER_SCAN3=
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = TEST_SERVICE)
   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
   (SID_NAME = TEST1)
  )
 )

Second node:
SID_LIST_LISTENER_SCAN1,SID_LIST_LISTENER_SCAN2,SID_LIST_LISTENER_SCAN3=
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = TEST_SERVICE)
   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
   (SID_NAME = TEST2)
  )
 )

Yes, it worked, but I would still not do it because of the above reasons.


MySQL Query was killed, but caused mysql database to not accept new reads or writes This has happened a few times before and on all of those times I was not able to find an answer on the internet and I've had to completely uninstall MySQL and reimport the database from a backup. Although right now there's a little more than 1 billion rows so that's not something I see as an acceptable solution at this time.

In the processlist there are four queries showing as "sending data" and all of them are killed. Below that there are about 100 more listed as "statistics" which cannot be killed either.

Is there a way I could fix this without reinstalling MySQL?

Edit: Ubuntu 16, mysql newest

For future refference for myself and anyone else with this issue, run "pkill mysql", move "ibtmp1" (like 90G) inside of /var/lib/mysql to another location, then start mysql again.


FTS - Index rebuild causing CPU issues (SQL Server 2008 R2) I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and updates statistics like this:

alter fulltext catalog [ftCatalogName] rebuild
update statistics [tableNameWithFtIndex]

The job runs weekly, Monday at 9AM; it finishes in around 1minute and always succeeds;

Now the problem: everytime after the job finishes, the CPU of sqlserver.exe process goes up to 99%; regularly the CPU stays at around 70-80% (i know it is very high and we are trying to improve this); and the only way to make it go back to it's regular state (70-80%) is only if we stop the IIS on all front-end servers (we have a webfarm) and then start them up again - this way the CPU for sqlserver.exe goes to 0% when the IISs are stopped, and back to 70-80% when the IISs are restarted back up;

Also i've analyzed the behavior of CPU during the rebuild process:
- start FTS rebuild job (above statements)
- sqlserver.exe CPU=75% (normal); fdhost.exe=20-25% (normal during rebuild)
- job finishes in 51 secs and fdhost CPU goes to 0%, and sqlserver.exe goes to 99% and stays like this until i apply the flow described above (start/stop IIS on front-end servers)

Does anyone have any idea about why this happens? At least an explication... or some ways to optimize this index and make it less resource hungry at rebuild...

Thank you in advance!

Your statement just TRIGGERS the rebuild job, not DOING it.
So - you triggered the job by ALTER FULLTEXT statement, now you can wait until it completes like that:

WHILE (SELECT COUNT(*) FROM sys.fulltext_catalogs WHERE FULLTEXTCATALOGPROPERTY(name, 'PopulateStatus') IN (1, 6, 7, 9)) > 0
    WAITFOR DELAY '0:00:00.1'


Recover deleted oracle flashback point I have accidentally deleted a flashback point in a oracle 11g database,Is there a way to recover the restore point

PS:If I have a flashback point A which was created first, and flashback point B which was created later.If I drop flashback point B and flashback to flashback point A will I be able to recover flashback point B.

PPS: there are no other backups to recover from.

I don't think you can get the restore point back, but if you want to flashback the database you can use FLASHBACK DATABASE TO SCN <number> instead. If you know the approximate time you want to revert to you can get the SCN with TIMESTAMP_TO_SCN. This assumes that the data is still there, of course. If not it is gone.


Remote Desktop Session's Machine ID for SQL Server Query How to get machine name of a user, who is connected on an instance through Remote Desktop and used windows authentication login. I need it for each query she has executed.
sys.dm_exec_connections just provide me connectino type i.e. NTLM. And windows event id 4624, gives me all the information i need BUT how to relate this information with my session id in SQL Server ???

This information is not exposed to SQL Server. All SQL Server sees is the hostname of the machine they actually connected from (e.g. the RDP host).

Since they are connected using Windows Authentication, can you not infer from their login name who they are and what machine they are probably connecting from? You can find this information here:

SELECT session_id, login_name
  FROM sys.dm_exec_sessions;

You may be able to go out externally to Active Directory and somehow determine what machine(s) that user is currently logged into, or perhaps get their connection information from the Windows host, I'm not too sure, but you're going well beyond the built-in capabilities of SQL Server at that point, and I have no idea how you would then correlate that information with a specific session_id.

Another alternative would be to - instead of using RDP (assuming you are doing so because the server is in a different domain) - let the user connect via their local SSMS. Here is a way to do that across domains:

https://stackoverflow.com/questions/9671904/connect-to-sql-via-windows-authentification-over-vpn/9672170#9672170

In this case, the sys.dm_exec_sessions.host_name and sys.dm_exec_connections.client_net_address should be very meaningful.


Work out date difference when there is only one date I have 2 records, one details when the stop clock was stopped and the other started.  I only have the one date value in each record.  I'm trying to work out the difference between the stop and start dates.  I'm looking for pointers.  I thought by doing something like:

SELECT e1.ID, MIN(e1.EventTime) AS [StopTotalMinutes]
          ,MAX(e2.EventTime) AS [StartTotalMinutes]
  FROM event AS e1
  JOIN event AS e2
    ON e1.ID = e2.ID

I would have been able to work something out, but I'm failing.

So, Table and some sample data is:

ID    |    EventTime     |   Action
1       2012-01-01 10:00      Stop
1       2012-01-01 13:31     Start
1       2012-01-03 09:45      Stop
2       2012-01-01 11:12      Stop
2       2012-01-10 15:35     Start
2       2012-01-11 08:39      Stop

This should do it:

SELECT e1.ID, DATEDIFF(MINUTE,e1.EventTime,e2.EventTime) [Minutes]
FROM (SELECT * FROM event WHERE Action = 'Start') AS e1
LEFT JOIN (SELECT * FROM event WHERE Action = 'Stop') AS e2
ON e1.ID = e2.ID


PostgreSQL location with Postgres.app I am using the postgres.app. I get a terminal window when I start the postgres app. However, when I go into a bash shell and type in psql it says Command not found. I assume the path to the app is not set in my profile. How can I find out where the postgres.app is located and how do I tell that my profile?

You just need to set your $PATH up.

This is covered on the Postgres.app website.

Add this to your ~/.bash_profile:

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin

... then reopen your shell/terminal.


How to ORDER BY typical software release versions like X.Y.Z? Given a "SoftwareReleases" table:

| id | version |
|  1 | 0.9     |
|  2 | 1.0     |
|  3 | 0.9.1   |
|  4 | 1.1     |
|  5 | 0.9.9   |
|  6 | 0.9.10  |

How do I produce this output?

| id | version |
|  1 | 0.9     |
|  3 | 0.9.1   |
|  5 | 0.9.9   |
|  6 | 0.9.10  |
|  2 | 1.0     |
|  4 | 1.1     |

To produce your desired output, you can simply:

SELECT id, version
FROM   versions
ORDER  BY string_to_array(version, '.')::int[];

One can cast a whole text array to an integer array (to sort 9 before 10).
One can ORDER BY array types. This is the same as ordering by each of the elements. And shorter arrays come before longer ones with identical leading part.

db<>fiddle here
Old SQL Fiddle.


Refresh DDL changes to table I am trying to create giant script which updates bunch of tables by adding/removing/modifying columns of existing database. And one issue that has stumped me is, whenever I add new column in table in one statement and try to refer it in another statement it fails with error about invalid column xxx.

Follow is pseudo code that I am using to reproduce this issue,

--CREATE TABLE testTable (u_id int, u_name varchar(10))
--INSERT INTO testTable VALUES (1,'11')
--INSERT INTO testTable VALUES (2,'12')
--INSERT INTO testTable VALUES (3,'13')
--INSERT INTO testTable VALUES (4,'14')
--INSERT INTO testTable VALUES (5,'15')

BEGIN
ALTER table testTable ADD u_add varchar(10)
END

BEGIN
CREATE TABLE tmp_xx_xx_testTable (u_id int, u_name varchar(10), u_add varchar(10))

INSERT INTO dbo.tmp_xx_xx_testTable VALUES (6,'16','DDD'  )

INSERT INTO tmp_xx_xx_testTable (u_id,u_name,u_add)
SELECT u_id,u_name,u_add FROM TestTable

drop TABLE TestTable

EXECUTE sp_rename
                N'[dbo].[tmp_xx_xx_testTable]',
                N'TestTable';
END

--SELECT * FROM testTable
--DROP TABLE testable

First (and last) few lines are just to prepare test table that I am using.

When I run this code, I get an error about column u_add not being valid. But when I run same statements individually, they run without any issues. So it looks like that somehow I am not understanding what else needs to be done here so SQL Server can actually "see" that newly added column.

Add a batch separator between statements.  Typically, the batch separator of choice would be GO:

--CREATE TABLE testTable (u_id int, u_name varchar(10))
--INSERT INTO testTable VALUES (1,'11')
--INSERT INTO testTable VALUES (2,'12')
--INSERT INTO testTable VALUES (3,'13')
--INSERT INTO testTable VALUES (4,'14')
--INSERT INTO testTable VALUES (5,'15')

BEGIN
ALTER table testTable ADD u_add varchar(10)
END
GO

CREATE TABLE tmp_xx_xx_testTable (u_id int, u_name varchar(10), u_add varchar(10))
GO

INSERT INTO dbo.tmp_xx_xx_testTable VALUES (6,'16','DDD'  )

INSERT INTO tmp_xx_xx_testTable (u_id,u_name,u_add)
SELECT u_id,u_name,u_add FROM TestTable

drop TABLE TestTable
GO

EXECUTE sp_rename
                N'[dbo].[tmp_xx_xx_testTable]',
                N'TestTable';
GO

--SELECT * FROM testTable
--DROP TABLE testable

The reason it fails without separators is because, before the script can be executed, it must be compiled, and in order to be compiled, all the objects referenced by each statement must exist at the compile time. The parser, however, encounters statements that reference non-existent objects. The objects are supposed to be created by previous statements in your script, but, as the script is yet to execute, they are yet to be created as well. As the result, the missing objects fail the compilation of the script.

Adding separators is equivalent to your highlighting and executing the parts of the script between the separators, except that is done automatically by the management tool (SSMS or sqlcmd), which interprets the separators prior to sending the script (or, in this case, its portions) to the server.


Is there a CASE LIKE in pl/pgsql? I'm trying to write a dynamic after trigger function that does an update based on the TG_TABLE_NAME, but it seems that plpgsql's CASE cannot do "LIKE". Is it called differently in plpgsql, or impossible?
If not possible, my second idea would be to do a SUBSTR, or are there better ways?
CREATE OR REPLACE FUNCTION upd_sys_table()
RETURNS TRIGGER AS $$
DECLARE
    lm BIGINT := (EXTRACT(epoch FROM timezone('UTC', now()))*1000)::bigint;
BEGIN
    CASE TG_TABLE_NAME
        WHEN  LIKE 'candy\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_candy';
        END;
        WHEN  LIKE 'fruit\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_fruit';
        END;
    ELSE
        RAISE NOTICE 'No match';
    END CASE;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This simply updates a record in a table called sys_table, which keeps track of a last-modified date for a group of tables. The group of tables are prefixed with either candy_ and fruit_ (yes, I made that up).

As was pointed out, you got the CASE syntax wrong.
You also don't need sub-blocks, parameters, multiple distinct UPDATE statements or even the (broken) PL/pgSQL CASE construct. Just an SQL CASE expression in the WHERE clause of the UPDATE:
CREATE OR REPLACE FUNCTION upd_sys_table()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE sys_table
   SET    last_modified = EXTRACT(epoch FROM timezone('UTC', now())) * 1000
        , modified_by   = NEW.modified_by
   WHERE  sys_table_name = CASE WHEN TG_TABLE_NAME LIKE 'candy\_%' THEN 'xvw_candy'
                                WHEN TG_TABLE_NAME LIKE 'fruit\_%' THEN 'xvw_fruit'
                           END;

   IF NOT FOUND THEN
      RAISE NOTICE 'No match';
   END IF;

   RETURN NEW;
END
$func$;

Subtle difference: this assumes that rows for 'xvw_candy' and 'xvw_fruit' always exist in sys_table. Else, there is an additional path to NOT FOUND.
Don't confuse SQL CASE with PL/pgSQL CASE. Same keyword, different language, subtly distinct syntax. Among other things, SQL CASE does not require an ELSE branch and defaults to NULL.
If the use case is as simple as your example suggests, you can even just look at the leading 5 characters and use the simpler "searched" CASE like you had in mind:
WHERE  sys_table_name = CASE left(TG_TABLE_NAME, 5)
                           WHEN 'candy' THEN 'xvw_candy'
                           WHEN 'fruit' THEN 'xvw_fruit'
                        END;

Or even just:
WHERE  sys_table_name = 'xvw_' || left(TG_TABLE_NAME, 5)

See:

Issue with composite type in an UPSERT function

Aside: it's typically better to store an actual timestamptz instead of an epoch as bigint. See:

How to best store a timestamp in PostgreSQL?


Support for libprotobuf-c on Amazon RDS I created a PostgreSQL 10 instance on Amazon RDS with the PostGIS 2.4 extension in my database enabled. I want to test the performance of Amazon RDS as a dynamic vector-tile server.

This hinges on the ability to use the ST_AsMVT and ST_AsMVTGeom commands in the PostGIS enabled database.

These functions require the libprotobuf-c libraries to be installed on the system which hosts the PostgreSQL instance.

It seems that Amazon RDS instances have no support for libprotobuf-c and PostgreSQL/PostGIS on Amazon RDS cannot make use of the vector-tile functions which have been introduced with PostGIS 2.4.

Does anyone know about means to enable libprotobuf-c support on Amazon RDS?

Just an update to Dennis' comment from the AWS thread, this is now possible.
Quote from the thread:

  We just launched RDS PostgreSQL 10.5 which supports libprotobuf-c.
  https://aws.amazon.com/about-aws/whats-new/2018/10/rds-postgresql-supports-minor-versions/

The official release notes (with emphasis added):

  [...]
  With this update, we have also added support for extensions pglogical version 2.2.0 and pg_similarity version 1.0 in PostgreSQL 10.5 and 9.6.10; pageinspect version 1.6 extension is supported with PostgreSQL 10.5; Map Box Vector Tiles in PostGIS with libprotobuf-c is supported with PostgreSQL 10.5; wal2json has been updated to 01c5c1ec in PostgreSQL 10.5 and 9.6.10; and pg_hint_plan has been updated to 1.3.1 and 1.2.3.
  [...]

Reference: Amazon RDS for PostgreSQL Supports New Minor Versions 10.5, 9.6.10, 9.5.14, 9.4.19, and 9.3.24


sp_updatestats doesn't seem to work for non-sysadmin logins Microsoft SQL Server 2017 (RTM-CU16)

When trying to update statistics under a user which is a member of db_owner database role via

EXEC sp_updatestats;

SQL Server throws an error:

  Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15 [Batch
  Start Line 0] User does not have permission to perform this action.

Even though MS here states that "ownership of the database (dbo)" is enough seems that it is not.

You misunderstood the phrase

  Requires membership in the sysadmin fixed server role, or ownership of
  the database (dbo)

"ownership of the database" means that this login owns this database.

This is not the same as "is a member of db_owner database role"

Owner of a database is one single login, while there may be as many db_owners as you want.

And in fact if you execute

exec sp_helptext 'sp_updatestats';

in the first rows you'll find:

declare @dbsid varbinary(85)

select @dbsid = owner_sid
    from sys.databases
    where name = db_name()

-- Check the user sysadmin
if not is_srvrolemember('sysadmin') = 1 and suser_sid() <> @dbsid
begin
    raiserror(15247,-1,-1)
    return (1)
end

This controls if the executor is a member of sysadmin server role or he owns this database: suser_sid() <> @dbsid where @dbsid is owner_sid of current database.

To find out the owner of some database you should execute

select owner_sid
from sys.databases
where name = 'some_db';

And you also can find it using SSMS:


How to fix data discrepancies between columns from two tables The problem:
I am working for a retail site that sells various products for cars and trucks. We want to integrate eBay and Amazon API to list our products on these stores. The problem arises when I try to send Vehicle compatibility data.

Example from our DB:

Product ID | Other IDs | Make  | Model
  *number* | *number*  | Ford  | F150 Crew Cab
  *number* | *number*  | Chevy | Silverado 2500/3500
  *number* | *number*  | Dodge | Ram Pickup 2500-5500 HD

Example from eBay Master Vehicle List DB:

 Make      | Model
 Ford      | F-150
 Chevrolet | Silverado 2500
 Dodge     | Ram 2500

As seen from the examples eBay/Amazon doesn't recognize our Models. There is no easy solution like using a "split" function to split Models into Model and Sub-model for example, because every case is different. Sometimes Model fields have Submodel as well, sometimes they have a range of Submodels (2500-5500), sometimes Model is not correct (F150 instead of F-150) etc.

Solution:
Changing every Model in the database to a correct Model format is not an option, because our system already uses these incorrect Models for business logic.

Writing a million exceptions in the code to handle incorrect Models is not very appealing as well.

The only solution I see is to create a new table and fill it manually with correct model names and their corresponding IDs for our products. This would be very time consuming task though.

Could anyone suggest a good solution for this problem?

With extensive experience consulting in the automotive sector, eBay is likely using real models as they come from OEM. You're conflating models and trims in your own system.

F150 Crew Cab            -- Crew cab is a trim level.
Silverado 2500/3500      -- Likely two models here.
Ram Pickup 2500-5500 HD  -- likely two models here too, with HD as a trim.

You need to figure out what is a model and what is a trim. Or, use a third party data provider like Chromedata which provides styles that often determined by model and trim. If eBay provides you a list of models, you need to map those into your local data source. Usually you'll have like 60%-direct match, 20%-inferrable with accuracy, 20%-requires human Q/A.

This is just part of how to integrate with other systems.

Looking at the new eBay guide I can give you an idea of the workload if that's not an option (which I may try first because it's probably all in a day's work).

From the eBay motors listing:

This listing of category ids needs to be loaded into a hierarchical database., alternatively and better use the api
Each entry needs to be weighed with the parents. and flattened.
Collapse it into a full text search vector.

Local database

In your own database, select all of the distinct "models" and make sure that they all have the same make.
Create a tsquery of the make, model
Run this against the eBay motors database above.


Create database link on Oracle Database with 2 Databases on different machines. I have 2 oracle databases (db1 and db2) on different machines, lets suppose: db1 on 192.168.1.1 and db2 on 192.168.1.2

What I want to achieve is to create a link on db1 that will allow me to perform queries on db2.

This is the way I created the link on db1:

create public database link db2Link connect to db2username identified by db2password
using 'db2';

I think this should work if both that bases are on the same machine, but as I want to connect to a database that is on a different machine, how can I do that link?

Thanks
Best Regards

You can define the connection string via tnsnames.ora then reference the alias

remotedb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = remotedb.fqdn.com)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )

Then create a dblink referencing that alias:

CREATE DATABASE LINK remotedb
    CONNECT TO SYSTEM IDENTIFIED BY <password>
    USING 'remotedb';

Or facilitate the same inline with:

CREATE DATABASE LINK remotedb
CONNECT TO SYSTEM IDENTIFIED BY <password>
USING'(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = remotedb.fqdn.com)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL)))';

For details on syntax, check http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5005.htm

If you want to avoid DNS resolution, just substitute the IP address for remotedb.fqdn.com instead.  Of course, you'll want to check that the source can initiate TCP connections to the remote port 1521:

nc -zv remotedb.fqdn.com 1521


MySQL Historical Data Query very slow I have a MySQL table, which stores exchange rates for two currencies, with a specific date. This table is updated every 15 second by an external script, and the data it contains is rather large, and will get even larger in time (since 2017 september, we have gathered ~8 million rows, but the updates at the beginning where with a 2 minute time scale).

The tables structure looks as follows:

 |---------------------------------------------------------|
 | RateId | RateTime | BaseCurrency | QuoteCurrency | Rate |
 |---------------------------------------------------------|

There are the following indexes in place on this table:

PRIMARY         BTREE   Yes No  RateId        7534199   A   No
BaseCurrency    BTREE   No  No  BaseCurrency  5199      A   No
                                QuoteCurrency 17938     A   No
QuoteCurrency   BTREE   No  No  QuoteCurrency 14        A   No
RateTime        BTREE   No  No  RateTime      753419    A   No

When I query current data, for example:

SELECT  *
    FROM  ExchangeRates
    WHERE  BaseCurrency = 'EUR'
      AND  QuoteCurrency = 'USD'
    ORDER BY  RateTime DESC
    LIMIT  0,1

the query is very fast, around 0.0029 seconds.

When I try to query historical data, like:

SELECT  *
    FROM  `ExchangeTable`
    WHERE  CurrencyFrom = 'EUR'
      AND  CurrencyTo = 'USD'
      AND  ExchangeTime <  '2018-01-29 12:00:00'
    ORDER BY  ExchangeTime DESC
    LIMIT  0,1

it is still fast, around 0.0008 seconds.

The problem starts when I try to query data, that we are not refreshing anymore, but I would still need historical information about. For example, we stopped gathering the rate for EUR->RON 6 months ago. If I would like to do the following query:

SELECT  *
    FROM  `ExchangeTable`
    WHERE  CurrencyFrom = 'EUR'
      AND  CurrencyTo = 'RON'
      AND  ExchangeTime <  '2018-01-29 12:00:00'
    ORDER BY  ExchangeTime DESC
    LIMIT  0,1

it takes around ~9.5897 seconds to execute, with the row found dated in 2017-08-25 13:07:00.

Why is this query so slow, while the rest of the queries are fast? What could I do to improve these kind of queries?

We are using MariaDB, with InnoDB storage engine.

WHERE  BaseCurrency = 'EUR'
  AND  QuoteCurrency = 'USD'
ORDER BY  RateTime DESC

Needs

INDEX(BaseCurrency, QuoteCurrency, RateTime)

and

WHERE  CurrencyFrom = 'EUR'
  AND  CurrencyTo = 'USD'
  AND  ExchangeTime <  '2018-01-29 12:00:00'
ORDER BY  ExchangeTime DESC

needs

INDEX(CurrencyFrom, CurrencyTo, ExchangeTime)

Ditto for the third.

More discussion of how I came up with those indexes is in my Cookbook .

The reason for the sudden slowdown is buried in the size of the table, the value of innodb_buffer_pool_size, the number of rows that your too-short index touched, etc.

My indexes will be fast, regardless of any of those issues (for those queries).


Thread Count is too high-what can I do in short term? Before I posted this question read following articles.

Max. Worker Threads and when you should change it by Sakthivel Chidambaram
Are my actual worker threads exceeding the sp_configure 'max worker threads' value? by Tom Stringer
How do you measure CPU pressure? by Thomasda
SQLOS’s DMVs Continue by Slavao

I am familiar with investigating high thread count issue by looking at following items (not comprehensive):

Maxdop setting
Cost threshold of parallelism setting
Cpu pressure
Worker queue length
Execution plan

This investigation can take hours before I decide to make a change. What can I do in short term so the requests are not waiting for worker threads to be assigned?

We use SCOM to monitor many aspects of SQL Server and this is one of them. I understand this is more of a informational message but seeing is consistently from same server gives us opportunity to look at the things that I listed above. What I am trying to find out from the community, if they take certain action (can be temporary, till full evaluation is done) if work_queue_count is constantly greater than zero once alert is triggered by SCOM. There are times it is easy to detect a single query that is consuming lot of threads but not always.

Default formula for [max workers] (at or below 64 logical CPUs): 16*(logical CPUs - 4) + 512

Here are two potential justifications if you want to raise [max workers]. If CPU is not saturated when DOP downgrades occur due to reserved worker count, negative consequences are limited. YMMV.

General: in sql server 2019 (maybe before? i’ve Not checked) if above 64 logical cpu/user schedulers, the multiplier for default is changed from 16 to 32. for the systems i care about, expected intensity of work per scheduler is the same whether 8 vcpu or 80. So why not always use 32?
specific to positioning re: hyperthreading.
again, for my workloads, expected intensity of work per core is the same whether HT or not. But the VMs have 1 vcpu per pcpu. And some physical servers have HT disabled. In those cases, why not use the “theoretical” logical cpu/scheduler count for the system if HT were enabled?


What is happening when Seconds_Behind_Master oscillates between two very different sets ov values? I have a Percona 5.6 slave, newly set up, that is replicating from a server that is itself a slave to a third. The middle server (dbS2, the master for dbS3) is currently ~260000 seconds behind the transaction server, and dbS3 was configured from an innobackupex snapshot of dbS2, which put it about 45 minutes behind dbS2 when dbS3 started replicating.

This is what a loop showing some parameters from show slave status\G looks like:

Fri Jul 31 23:46:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 285744

Fri Jul 31 23:46:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 285763

Fri Jul 31 23:47:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2223

Fri Jul 31 23:47:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2226

Fri Jul 31 23:48:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2227

Fri Jul 31 23:48:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 284885

Fri Jul 31 23:49:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 284568

So it seems that Seconds_Behind_Master on dbS3 is jumping back & forth between the number of seconds dbS2 is lagging behind dbS1 and the number of seconds dbS3 is lagging behind dbS2.

Shouldn't show slave status\G on dbS3 always show the lag between dbS2 and dbS3 without regard for how far dbS2 is behind dbS1?

I have seen this since about 2002.  Ignore it; it will go away.  I have failed to find out what causes it, and it so elusive that I could not get anyone to look at as a "bug".

I do not know if this will have any impact on innobackupex.


Is there something to rescue from an Oracle Data Modeler design just with the 'logical' folder? First of all, I even don't know how naive is the question, we start studying this not long ago.

A friend of mine was working on Data Modeler, he saved the design and he only got the "logical" folder, without the other ones nor the .dmd file, is still possible to open this with Data Modeler and rescue something or all the work was lost?

It could be possible to rescue the ERD - if the files in the "logical" folder are intact.

Perform the following steps:

Load the datamodeler, create a new model (name here: new_model), leave it empty, save it.

{1} Operating system/file manager: Give the new_model's "logical" folder a new name.
(Avoid name clashes with the rescued "logical" folder.)

{2} Operating system/file manager: Copy the rescued logical folder into the new_model's folder.

{3} datamodeler: Close the new_model.

{4} datamodeler: Re-open the new_model.  Right-click Logical Model, click Show.

Example session (Linux)

# Directory listing _after_ the (empty) new_model has been created and saved.

[oracle@vbgeneric Documents]$ ls
new_model  new_model.dmd  erd_one_to_many erd_one_to_many.dmd

[oracle@vbgeneric Documents]$ cd new_model

[oracle@vbgeneric new_model]$ ls
businessinfo          datatypes        dl_settings.xml.merge_conflicts.local  mapping  rdbms
ChangeRequests.local  dl_settings.xml  logical                                pm

[oracle@vbgeneric new_model]$ mv logical renamed_logical

[oracle@vbgeneric new_model]$ ls
businessinfo          datatypes        dl_settings.xml.merge_conflicts.local  pm     renamed_logical
ChangeRequests.local  dl_settings.xml  mapping                                rdbms

[oracle@vbgeneric new_model]$ cd ../erd_one_to_many/

[oracle@vbgeneric erd_one_to_many]$ cp -R logical ../new_model

[oracle@vbgeneric erd_one_to_many]$ cd ../new_model

[oracle@vbgeneric new_model]$ ls
businessinfo          datatypes        dl_settings.xml.merge_conflicts.local  mapping  rdbms
ChangeRequests.local  dl_settings.xml  logical                                pm       renamed_logical

# Close the new_model.
# Re-open the new_model. Show Logical


indexing on multiple nvarchar(max) columns I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading.

What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed to do so because if 900 B limit.

Then I tried(after reading following link Indexing Wide Keys )

ALTER TABLE dbo.ProductDetail
   ADD ShipperNameHash AS HASHBYTES('SHA2_512', ShipperName) PERSISTED;

ALTER TABLE dbo.ProductDetail
   ADD ConsigneeNameHash AS HASHBYTES('SHA2_512', ConsigneeName) PERSISTED;

Then I got following error on following statement:

  Msg 8152, Level 16, State 10, Line 1
  String or binary data would be truncated.
  The statement has been terminated.

ALTER TABLE dbo.ProductDetail
   ADD ProductDescHash AS HASHBYTES('SHA2_512', Product_Description) PERSISTED;

But also above statements created column varbinary(8000). Which of course hasn't solved the problem for me.

Is there any way that I can built UNIQUE INDEX on the combination these columns ?

I know this is already answered, but I'm going to put in my vote for using HASHBYTES.
I talk a little about it in my post http://michaeljswart.com/2013/11/hashing-for-indexes/ and I describe what that looks like in this case below:

If you can get away with nvarchar(4000) instead of nvarchar(max), then, this schema becomes feasible:

CREATE TABLE ProductDetail
(
    ProductDetailId INT IDENTITY PRIMARY KEY,
    ShipperName NVARCHAR(4000),
    ShipperNameHash as HASHBYTES('SHA1', ShipperName) PERSISTED,
    ConsigneeName  NVARCHAR(4000),
    ConsigneeNameHash  as HASHBYTES('SHA1', ConsigneeName) PERSISTED,
    UNIQUE(ShipperNameHash, ConsigneeNameHash )
)
go

When creating it, SQL Server will warn you about the maximum key length for that unique index being over 900 bytes. But in practice, SHA1 never returns more than 20 bytes, so in this particular case, the warning is ignorable.

See that it works with this insert statement

insert ProductDetail (ShipperName, ConsigneeName)
values
(
  REPLICATE(N'A', 3999) + N'B',
  REPLICATE(N'A', 3999) + N'B'
),
(
  REPLICATE(N'A', 3999) + N'C',
  REPLICATE(N'A', 3999) + N'C'
)

See that it fails with this insert statement:

insert ProductDetail (ShipperName, ConsigneeName)
values
(
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)
),
(
  REPLICATE(N'A', 4000),
  REPLICATE(N'A', 4000)
)


SQL Server - Cursor and Dynamic SQL this query is working but not giving me the output I want.

I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But here I have always 'OK' as output in my updated table. Normally, I should have 'FAILED' for the row [AFUP] and 'OK' for the row [BICHATP].

Can you run it on your machine?

drop table [MyTable]
drop table [BICHATP]
drop table [AFUP]

CREATE TABLE [AFUP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
    ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
    ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
    [Tabs] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
    ('AFUP'), ('BICHATP');

DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
    SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @cmd = 'update [MyTable]
                set [Check_column] =
                    case when left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'' then ''FAILED''
                         else ''OK''
                    end
               from DB_SANOFI.dbo.' + @Tabs;

    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

Tables

    AFUP           BICHATP
------------     ------------
M006 | 1010      M000 | 1101
M054 | 1000      M090 | 1001
M015 | 2324      M012 | 1023

Expected result:

Tabs    | Check column
-----------------------
   UFAP | FAIL
BICHATP | OK

If I understand your question correctly, I think you want OK in the MyTable if you cannot find any row on the joined tables that meets your FAILED conditions.  I'm using NOT EXISTS and also made sure I'm only updating MyTable for the specific table being processed.

Let me know if I have misunderstood your requirements.

drop table if exists [MyTable]
drop table if exists [BICHATP]
drop table if exists [AFUP]

CREATE TABLE [AFUP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
    ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
    ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
    [Tabs] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
    ('AFUP'), ('BICHATP');

DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
    SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @cmd = 'update [MyTable]
                set [Check_column] = case when NOT EXISTS
                    (SELECT * FROM ' + @Tabs + ' where
                    left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')
                    then ''OK''
                         else ''FAILED''
                    end
               from ' + @Tabs + ' WHERE [MyTable].Tabs = ''' + @Tabs + '''';

               print @cmd
    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

| Tabs    | Check_column |
|---------|--------------|
| AFUP    | FAILED       |
| BICHATP | OK           |


How to grant permissions on a table-valued function Am I doing it right...?

I have a function that returns money...

CREATE FUNCTION functionName( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )  RETURNS money AS BEGIN

  DECLARE @v_dint money   set @v_dint = computation_here
     set @v_dint = round(@v_dint, 2)

  RETURN @v_dint
END
GO
Grant execute on functionName to another_user
Go

Im just wondering if this is possible to be converted to iTVF?

I've tried doing this but I got an error:

CREATE FUNCTION functionName ( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )
RETURNS TABLE AS
RETURN SELECT returnMoney = computation_here
GO
Grant execute on functionName to another_user  Go

ERROR:

  Msg 4606, Level 16, State 1, Line 2
      Granted or revoked privilege EXECUTE is not compatible with object.

This function is used like this:

update table_name set interest = functionName(col1,col2...) where...

Thanks in advance!

Scalar functions require EXECUTE permissions, however when you've converted to a Table Valued Function the permissions required change to SELECT.

You must now GRANT SELECT ON functionName TO another_user;

From BOL:

  Users other than the owner must be granted EXECUTE permission on a function (if the function is scalar-valued) before they can use it in a Transact-SQL statement. If the function is table-valued, the user must have SELECT permissions on the function before referencing it.


What known strategies, if any, can I use to validate a Slowly Changing Dimension? I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current).

As our daily load runs and I see the changes flowing in, I sometimes worry that it's creating new records when it shouldn't.

What kind of scripts can be run to compare each customer over it's respective history and make sure it's changing appropriately?

Also, and I think I'm touching on the gaps and islands problem, what strategies are there for verifying that the start dates and end dates are all contiguous?

I want to feel confident that the table is reporting correct data day to day without becoming a beast due to some null check causing a ton of records to be created each day.

Thanks for any help you can offer.

Checking whether the dates are contiguous
You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in window functions can be really handy for this kind of task. You can order the rows by the start date or by an incremental ID if you have one, and then use these to check on values in the next or previous row.
So for instance, you could set up a window function which is partitioned by your business key/surrogate durable key (I'll just use the term durable key from here on out), and ordered by your start date (ascending). You could then use LEAD to check that the start date on the next row was equal to the end date + 1 on the current row. You can't use window functions in the WHERE clause, so perhaps you could create a case statement which flags up those rows which are for the same customer (i.e. have the same durable key), but where the next row's StartDate isn't equal this row's EndDate + 1. You could do the same in reverse to flag up the following row as well. An outer query could then only select the rows which need investigation.
Checking the SCD is working correctly
To some extent the checks you can do are going to depend on your particular situation, but I'll try to tackle this from a generic standpoint that might be helpful to you (if you still need help, 7 months later!), and which might help other people having similar issues. If you return and give more specific detail, let me know and I'll be happy to have a read through and improve my answer if possible.
One option might be to run some queries which select a few COUNT values on different aspects of the dimension. So for instance, you could group by the durable key, and get a count of how many rows exist for that durable key. If you don't have an understanding of how many changes are reasonable, you could take an average count and then look for customers which are over that average (or over that average by some percentage or set amount).
From there, having a dig through the data belonging to a handful of suspicious-looking customers might turn up obvious issues which are worth further investigation. Without specifics I don't know what issues you might be running into, but as an example lets say you notice a customer which has hundreds of rows, with a value which is changing back and forth between two values. From there, you could write up some queries to look for that problem in the rest of the table - for instance, coming up with a query which would let you look at the number of rows for each customer vs. the number of unique rows for each customer (looking only at the actual attribute columns, so not the dates or any other metadata columns you might have).
Checking on whether the customer is changing correctly over time will depend on what historic data you have held in your source(s) or staging area. If you do have historic data in one or both of those places, you might be able to compare the values with your dimension rows either directly by date (if said historic data includes dates), or perhaps by the order those changes happened in.

On a slight tangent, but worth mentioning: I'm guessing from the fact that you're not sure whether this dimension is behaving correctly that this is something you've perhaps inherited from another developer? If you're really worried about the size of this dimension and if you don't know the full reason for its current design, it might be worth checking whether all of the Type II SCD fields really should be Type II SCD.
Some data warehouse developers will tend to presume SCD II by default, which can really be problematic. Aside from the space issues, as the link goes into, the use of SCD II can lead to some unexpected results for the people consuming the data. This might not be relevant in your case (the SCD II attributes might be well-thought-out and suitable for your users' requirements), but if it is relevant it might be possible to alleviate your concern about the size of the dimension and also remove the need to do those data quality checks.


Mysql query hangs on import I dumped all my databases in order to import them into a slave.

mysqldump --all-databases > dump.sql

The import seems to be very very slow.
Checking the processlist, I saw a simple insert on a 20 rows table that took 400+seconds.

I had to ignore some big and not critical tables from the dump  in order to import in a reasonable time.

I don't understand if I'm doing something wrong, the whole dump was about 800MB and the "big" table I ignored was no more than 170K records, which I don't think it's big at all...

Also I often dump a single database to load it on a testing environment, usually without problem. It seems the --all-databases dump is very slow.

Any suggestion welcome.

I suspect you're suffering 'out-of-the-box' syndrome. For the whole world to know... MySQL config out of the box sucks.

Go to https://tools.percona.com/wizard and create better configuration based on the questions asked in the form. This should provide you with a better starting point. Once the output is generated, add it to your configuration file (/etc/my.cnf) and restart MySQL; review the mysql error log to ensure that your mysql instance started cleanly (beware of paths, permissions and the innodb_log_file_size size differences since that will require disposal of the old ib_log* files before new ones can be created). Once done, try your import again.


SQL Server 2012 Remote Access to Certain Users Recently, I've started working on SQL Server 2012. As there is a risk in allowing anyone to access SQL Server remotely, I'm trying enable remote connections only to specific users within my developer team.

How can I allow only a specific set of users to connect to SQL Server remotely?

As of now I've disabled Remote Access by using following Query

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Remote access', 0;
RECONFIGURE;
GO EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

sp_configure 'remote access', 1 doesn't do what you think it does. The option name is misleading as it has nothing to do with allowing/disallowing users to access the server from a remote location, it's server-to-server connections. There's a ton of misinformation out there on this. Try it yourself, set the value to 0 then connect to the SQL Server from a different machine. So long as a network protocol is enabled and there are no firewall blocks, you will connect successfully.

As for your users, unless your users typically connect to the machine/vm where your SQL Server instance is hosted to access the SQL Server, all are remote users and that should be way. Unless your SQL Server is running on a network that is publicly accessible, there's little harm/risk in letting the server accept all incoming requests. These requests have to be authenticated and authorized anyway so it's not like you're letting random connections through.

If you really want to lock it down so only specific users can even attempt to connect to SQL Server, you can do it via the Windows firewall with Advanced Security Settings. You've probably already configured an inbound rule to allow connections from other machines so just modify that rule by restricting access to specific users only. Just look for the Users tab and add the authorized users from there (you'll need to require secure connections only in the General tab). If you haven't yet configured an inbound rule, just create a new one and add the restrictions on users mentioned above.

Again, if the SQL Server is running in a corporate network not directly accessible by outsiders/the internet, this type of lockdown is rarely needed. If you really have some highly sensitive data in there then this lockdown is probably insufficient.


GSE1015N A spatial reference system named "NAD83_SRS_1" does not exist I am following this tutorial

https://www.ibm.com/developerworks/data/tutorials/dm-1202db2spatialdata1/index.html

And, running the sql "Listing 3. Contents of import_counties.sql" in IBM DB2 (on Windows), the CLP returns this error:

Is there anyone experienced this error before, could you please tell me how I get it solved? Because I already googled, and tried out many solutions including reinstall DB2 but the problem is still there. Is there any place that I can download "NAD83_SRS_1" and install it separately.

I just realized that there was a bug when I enable the databases to support spatial function.

db2se enable_db sample

While running this command line, it was interupted, but DB2 still reports that it successfully enabled the spatial function. Actually, this command takes very long to finish (more than 5 mins in my case), which makes me thought it gets stuck and interupt it with Ctrl+C. So the solution is just to wait until it finish without touching any keyboard.


Select different time I looked on the internet and I found your post on the stackexchange.
I will try to explain in more detail what we really need
I do not know well to write English
It is a system for airporttaxi

id   date       time        direction

7   2014-04-15  21:35:00    to
8   2014-04-15  15:20:00    from
9   2014-04-15  17:35:00    to
10  2014-04-15  08:15:00    to
11  2014-04-15  14:40:00    to
12  2014-04-15  11:35:00    to
13  2014-04-15  14:35:00    from

from / to = from airport / to airport

I need a mysql script we calculate the difference between the time of driving the same day
example

script needs to sort the first time

08:15:00
11:35:00
14:35:00
14:40:00
15:20:00
17:35:00
21:35:00

then calculate the difference between each ride, starting with the first

I want something like this

start        end          dif_min

08:15:00     11:35:00     200
11:35:00     14:35:00     180
14:35:00     14:40:00     5
14:40:00     15:20:00     40
15:20:00     17:35:00     135
17:35:00     21:35:00     240
21:35:00     00:00:00     25

I much appreciate it if you help me, and I hope you understand what I want

THIS IS THE QUERY YOU NEED

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SET @nexttime = 0;
SELECT
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60)
;

SAMPLE DATA

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

mysql> create table airport_taxi
    -> (id int not null auto_increment primary key,
    -> date date,time time,direction varchar(10));
Query OK, 0 rows affected (0.38 sec)

mysql> insert into airport_taxi values
    -> ( 7,'2014-04-15','21:35:00','to'),
    -> ( 8,'2014-04-15','15:20:00','from'),
    -> ( 9,'2014-04-15','17:35:00','to'),
    -> (10,'2014-04-15','08:15:00','to'),
    -> (11,'2014-04-15','14:40:00','to'),
    -> (12,'2014-04-15','11:35:00','to'),
    -> (13,'2014-04-15','14:35:00','from');
Query OK, 7 rows affected (0.21 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from airport_taxi;
+----+------------+----------+-----------+
| id | date       | time     | direction |
+----+------------+----------+-----------+
|  7 | 2014-04-15 | 21:35:00 | to        |
|  8 | 2014-04-15 | 15:20:00 | from      |
|  9 | 2014-04-15 | 17:35:00 | to        |
| 10 | 2014-04-15 | 08:15:00 | to        |
| 11 | 2014-04-15 | 14:40:00 | to        |
| 12 | 2014-04-15 | 11:35:00 | to        |
| 13 | 2014-04-15 | 14:35:00 | from      |
+----+------------+----------+-----------+
7 rows in set (0.00 sec)

QUERY EXECUTED

mysql> SET @GivenDate = '2014-04-15';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
    -> FROM airport_taxi WHERE date=@GivenDate;
Query OK, 1 row affected (0.00 sec)

mysql> SET @nexttime = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     TIME(FROM_UNIXTIME(StartTime)) "start",
    ->     TIME(FROM_UNIXTIME(EndTime)) "end",
    ->     FLOOR((EndTime - StartTime)/60) diff_min
    -> FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
    -> FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
    -> FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
    -> UNION SELECT
    ->     TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    ->     FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    ->     UNIX_TIMESTAMP(@LastDateTime))/60)
    -> ;
+----------+----------+----------+
| start    | end      | diff_min |
+----------+----------+----------+
| 08:15:00 | 11:35:00 |      200 |
| 11:35:00 | 14:35:00 |      180 |
| 14:35:00 | 14:40:00 |        5 |
| 14:40:00 | 15:20:00 |       40 |
| 15:20:00 | 17:35:00 |      135 |
| 17:35:00 | 21:35:00 |      240 |
| 21:35:00 | 00:00:00 |      145 |
+----------+----------+----------+
7 rows in set (0.00 sec)

mysql>

Just remember to set the @GivenDate to whatever date you need

BTW Your last line is based on midnight the next day. Therefore, the diff_min is 145 not 25. If you have a knock-off time of 10:00 PM at th eairport, then change

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60)

to this

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) - 120

Subtracting 120 backs 2 hours away from midnight of the next day makes the last line's end 10:00 PM

Give it a Try !!!

UPDATE 2014-04-16 09:44 EDT

I cannot really test this, but you can try this:

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SELECT id INTO @LastID FROM airport_taxi
WHERE date=@GivenDate ORDER BY time DESC LIMIT 1;
SET @nexttime = 0;
SELECT
    id,
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT id,(@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT id,FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT @LastID,
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60)
;

I hope this helps !!!


Schema for 1 User to 0..N Apps? I'm trying to design a schema for a user having multiple accounts.

Example:

User ID 1 has Apps A and B
User ID 2 has App B only

More App's will be created, i.e. C, D, etc., in the future.

My first thought was to, then, create a schema of:

create table account (
  primary key user_id,
  string app_a,
  string app_b
);

However, what I don't like about this approach is that my app_* columns will have NULL marks.

So, then, I thought it'd be worthwhile to do:

create table users (
    primary key user_id
);
-- note: I'm not sure if it's necessary to include the 'primary key id'
create table app_a (
    primary key id,
    foreign key primary key user_id
);

create table app_b (
    primary key id,
    foreign key primary key user_id
);

What I like above approach is that I'm no longer dealing with NULLable columns. In addition, to create a new app, I simply need to create a new table; rather than adding another NULLable column.

Yet, it seems that, in the second approach, I'm simply treating the tables user, app_a and app_b as lists.

What's the best approach (including any I've not mentioned) to capture a relationship from 1 user to 0 to N apps?

If the ultimate number of apps possible is very small (4-5 would be the limit for me, I think), what you've got can work.

If, at some point, there will be other data to store for each user and each app, and that data would vary considerably from one app to another, then it may be the easiest structure to work with.

If there will never be a need to store additional data per user per app; or, if that data would be basically the same from one app to another, then you could have an app table to store unique information about the apps (not tied to individual users), and have a user_app table to link users to their apps, and store any common data that would apply to all apps (first sign-in, last sign-in, whatever you might need, if anything).

CREATE TABLE app (app_id int PRIMARY KEY, app_name varchar(128), ...);
CREATE TABLE user_app
     ( user_app_id int PRIMARY KEY
      ,app_id -- foreign key to table app
      ,user_id -- foreign key to table user
      ... - other columns, if needed
     );

This is probably the best solution if the number of apps grows large (say, if you're tracking users and their apps in Google Play, rather than users and 4 in-house apps, where you're not expecting more in the foreseeable future).

Update - as noted by ypercubeᵀᴹ, user_app should have a unique index on (user_id, app_id), assuming each user can only have one account per app.

If it was possible for a single user to have multiple accounts on a given application for some reason, then you would want to include the account_id as a column. Then, you would not want the (user_id, app_id) unique index; instead, you would want a unique index on (app_id, account_id) (assuming that duplicate account IDs are fine across different applications; otherwise, the unique index should just be on account_id).


Linking tables to extract value based on closest previous date I'm going to use an example that was previously posted but modify it to match my problem:

TOCs bu Date:

Location StatDate TOC
WC1  9/1/1900    5600
WC1  10/1/2010   5605
WC2  8/2/1980    4300
WC2  9/5/2005    4280
WC2  10/6/2011   4285

Water Levels by date:

Location  MeasurementDate DepthToWater
WC1         7/26/2006    5.5
WC1         9/30/2008    5
WC1         10/5/2010    6
WC2         7/30/2010    8
WC2         11/3/2015    8.5

Desired result:

Location MeasurementDate  DepthToWater   TOC    Elevation(TOC-DepthToWater)
WC1         7/26/2006    5.5         5600      5594.5
WC1         9/30/2008    5           5600      5595
WC1         3/5/2015     6           5605      5599
WC2         7/30/2010    8           4280      4272
WC2         11/3/2015    8.5         4285      4276.5

I modified the proposed solution that was previously posted, but that solution didn't JOIN the 'location' fields, and I couldn't get it to work anyway.
I would GREATLY appreciate some help with this.

You can use a (CROSS) APPLY operator for this purpose. But please have a look at data you have supplied, there is some incoherence in these dates.

CREATE TABLE TOC (Location VARCHAR(10), StatDate DATETIME, TOC INT);
INSERT INTO TOC VALUES
('WC1',  '19000901', 5600),
('WC1',  '20101001', 5605),
('WC2',  '19800802', 4300),
('WC2',  '20050905', 4280),
('WC2',  '20111006', 4285);
GO

CREATE TABLE WLEVELS (Location VARCHAR(10), MeasurementDate DATETIME, DepthToWater DECIMAL(5,1));
INSERT INTO WLEVELS VALUES
('WC1', '20060726', 5.5),
('WC1', '20080930', 5),
('WC1', '20101005', 6),
('WC2', '20100730', 8),
('WC2', '20151103', 8.5);
GO

SELECT
    Location,
    MeasurementDate,
    DepthToWater,
    TOC,
    (TOC - DepthToWater) AS Elevation
FROM
    WLEVELS
CROSS APPLY (SELECT TOP 1
                 TOC
             FROM
                 TOC
             WHERE
                 TOC.Location = WLEVELS.Location
                 AND StatDate <= MeasurementDate
             ORDER BY
                 Location, StatDate DESC) TC;
GO

Location | MeasurementDate     | DepthToWater |  TOC | Elevation
:------- | :------------------ | :----------- | ---: | :--------
WC1      | 26/07/2006 00:00:00 | 5.5          | 5600 | 5594.5
WC1      | 30/09/2008 00:00:00 | 5.0          | 5600 | 5595.0
WC1      | 05/10/2010 00:00:00 | 6.0          | 5605 | 5599.0
WC2      | 30/07/2010 00:00:00 | 8.0          | 4280 | 4272.0
WC2      | 03/11/2015 00:00:00 | 8.5          | 4285 | 4276.5

db<>fiddle here


Best way to get the latest Identity when inserting through View I am using View and View trigger to insert data into multiple tables.
In this case, how can I get the inserted ID of the specific table?
I was using IDENT_CURRENT but it's not recommended.
How can I implement this using SCOPE_IDENTITY?

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope