Sort table so that linked rows show up as subsets in overall alpha sort I have this member table where memberships are either single or family, and if family they can be family head or dependent. When outputting a view of the membership, it currently sorts alpha ascending, but I'd like to find a way to put all of the dependents of a family head below the head.

The relevant columns are:

isFam ( tinyint(1) )
isFamHead ( tinyint(1) )
memKey ( int(11) )

Is there some way, in a mysql query, to sort the table alphabetically for single members and family heads, but put the dependents of the family head directly beneath the family head?

(This sort of works right now if all family members have the same last name, but in many cases I find they don't. And different families can have the same last name as well, so ...)

From the comments:

memKey identifies a membership. For a single membership, there will be only one row with that memKey; for a family membership, all members of a given family will have the same memKey.
For a single membership, isFam is 0; for all members of family memberships, isFam = 1.
The "family head" of each family membership has isFamHead = 1; there should only be one row where isFamHead = 1 per memKey. (Ignore value of isFamHead for single members)

So - making the following assumptions:

memKey is the same for a family head and all subsidiary memberships for the family;
There is no direct pointer to the family head record in a subsidiary member row.
isFamHead acts as a Boolean, where "1" means the row represents a family head, and "0" means it doesn't.
There is only one row for any given memKey where isFamHead = 1

From the comments: Members with "single" memberships (1 person, no family) will have isFam = 0. Members who are part of a family membership will have isFam = 1; only the primary member from the family will have isFamHead = 1, it will be 0 for the rest.

Something like this should work. Column and table names are made up, of course, since none were provided except for the three relevant columns.

SELECT CASE WHEN `isFamHead` = 1 OR `isFam` = 0 THEN CONCAT(lastname,', ',firstname) ELSE '' END
       as FamilyHead
      ,CASE WHEN `isFamHead` = 0 AND `isFam` = 1 THEN '' ELSE CONCAT(lastname,', ',firstname) END
       as FamilyMember
      ,'blah blah' as Additional_Columns
  FROM (
        SELECT fh.lastname as fh_lastname
              ,fh.firstname as fh_firstname
              ,m.*
          FROM member m
                 INNER JOIN member fh ON (m.memKey = fh.memKey AND fh.isFamHead = 1)
       ) sq
 ORDER BY fh_lastname, fh_firstname, memKey, isFamHead DESC, lastname, firstname;

Our subquery adds the first and last name of the isFamHead row for the current row's memKey to the current row's data.

Then, we sort by:
 - the family head's last and first names
 - memKey (just in case there are two family heads with the same name - this pulls each actual family group together)
 - isFamHead in descending order - this makes the family head the first row of each family group
 - finally, the members' last and first names.

If we have the following families:

John Smith (head), Jane Levy, Paul Smith-Levy, Frank Smith (memKey = 10000)
John Smith (head), Patty Smith, Jack Smith, Alvin C Monk (memKey = 10001)
Adrian Monk (head), Trudy Monk, Ambrose Monk
Steve Rogers (a single member)

This should give us the following output:

Family Head          Family Member
------------------   -------------------
Monk, Adrian
                     Monk, Ambrose
                     Monk, Trudy
Rogers, Steve
Smith, John
                     Levy, Jane
                     Smith, Frank
                     Smith-Levy, Paul
Smith, John
                     Monk, Alvin C
                     Smith, Jack
                     Smith, Patty

NOTE: code is untested.


Does anyone have a stored procedure to truncate temporal table? Does anyone have or know of stored procedure that will do generic temporal table manipulation so that history and main table are both truncated?

You have to alter it to set system_versioning = off, then drop the history table, then truncate your main table, then re-enable versioning on your main table. I was going to build a generic stored procedure to do this for any database/schema/table name passed to it but sure be nice if someone already had one. I never like re-inventing the wheel if I can avoid it.

MSSQL documentation has a good sample stored procedure (though far from perfect, as Martin noted in the comments about how it does not use QUOTENAME, for one) for cleaning a temporal table:

DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;
GO

CREATE PROCEDURE sp_CleanupHistoryData
         @temporalTableSchema sysname
       , @temporalTableName sysname
       , @cleanupOlderThanDate datetime2
AS
    DECLARE @disableVersioningScript nvarchar(max) = '';
    DECLARE @deleteHistoryDataScript nvarchar(max) = '';
    DECLARE @enableVersioningScript nvarchar(max) = '';

DECLARE @historyTableName sysname
DECLARE @historyTableSchema sysname
DECLARE @periodColumnName sysname

/*Generate script to discover history table name and end of period column for given temporal table name*/
EXECUTE sp_executesql
    N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s.name, @period_col_nm = c.name
        FROM sys.tables t1
           JOIN sys.tables t2 on t1.history_table_id = t2.object_id
        JOIN sys.schemas s on t2.schema_id = s.schema_id
            JOIN sys.periods p on p.object_id = t1.object_id
           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id
                  WHERE
                 t1.name = @tblName and s.name = @schName'
                , N'@tblName sysname
                , @schName sysname
                , @hst_tbl_nm sysname OUTPUT
                , @hst_sch_nm sysname OUTPUT
                , @period_col_nm sysname OUTPUT'
                , @tblName = @temporalTableName
                , @schName = @temporalTableSchema
                , @hst_tbl_nm = @historyTableName OUTPUT
                , @hst_sch_nm = @historyTableSchema OUTPUT
                , @period_col_nm = @periodColumnName OUTPUT

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1

/*Generate 3 statements that will run inside a transaction: SET SYSTEM_VERSIONING = OFF, DELETE FROM history_table, SET SYSTEM_VERSIONING = ON */
SET @disableVersioningScript =  @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'
SET @deleteHistoryDataScript =  @deleteHistoryDataScript + ' DELETE FROM  [' + @historyTableSchema + '].[' + @historyTableName + ']
     WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) +  ''''
SET @enableVersioningScript =  @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '

BEGIN TRAN
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

Just add a few extra lines to handle removing data from the parent table and you should be in business.

Bonus: it uses sysname for variables which I am a huge proponent of but is generally an overlooked datatype!


In Postgres, how do I adjust the "pgsql_tmp" setting? I"m using Postgres 9.5.4 on Ubuntu 14.04.  I have my Postgres data on a separate disk partiion, which is getting pretty full.

myuser@myproject:~$ df -h /mnt/volume-nyc1-01/
Filesystem Size Used Avail Use% Mounted on
/dev/sda 99G 93G 413M 100% /mnt/volume-nyc1-01

I want to delete some data from my partition, but this has become challenging.  In particular, when I run some queries, i get results like this

myproject_production=> select count(*) FROM my_object_times rt1, my_object_times rt2 where rt1.my_object_id = rt2.my_object_id and rt1.name = rt2.name and rt1.time_in_ms = rt2.time_in_ms and rt1.id > rt2.id;;
ERROR:  could not write block 52782 of temporary file: No space left on device

I want to free up some temp space so I can run queries and identify what data I need to delete.  I have some other free space on another partition. How do I point my pgsql_tmp variable there so that I can run the queries I need?

Edit:

As the symlink option seemed to be the least invasive, I gave it a go, setting up things like this

myuser@myproject:~$ sudo ls -al /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp
lrwxrwxrwx 1 root root 14 Apr 10 18:01 /mnt/volume-nyc1-01/postgresql/9.5/main/base/pgsql_tmp -> /opt/pgsql_tmp
myuser@myproject:~$ cd /opt
myuser@myproject:/opt$ df -h /
Filesystem                                              Size  Used Avail Use% Mounted on
/dev/disk/by-uuid/050e1e34-39e6-4072-a03e-ae0bf90ba13a   40G   24G   15G  62% /

You can see that I have over 16GB available on the partition where I pointed it to, but still I get the errors

ERROR:  could not write block 1862514 of temporary file: No space left on device

From the manual

  temp_tablespaces (string)

  This variable specifies tablespaces in which to create temporary objects (temp tables and indexes on temp tables) when a CREATE command does not explicitly specify a tablespace. Temporary files for purposes such as sorting large data sets are also created in these tablespaces.

In order to be able to use that, you need to create a tablespace on that volume, create a directory there (owned by the postgres user!), then run this:

create tablespace temp_space LOCATION 'mnt/volume-nyc1-01/pg_temp';

You need to grant the necessary privileges on that tablespace:

grant create on tablespace temp_space to public;

After that you can e.g. use

alter system set temp_tablespaces=temp_space;

(alternatively you could just change that in your session)

Note that for the alter system to have any effect, you need to reload the configuration:

select pg_reload_conf();

Alternatively just change the $PGDATA/base/pgsql_tmp to be a symbolic link to the directory on the other partition.


How to dump a large database? I try to take a backup of a huge MySQL database via mysqldump. The database has millions of rows with a total size of 1.3TB.

CREATE TABLE PageContents
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
FOREIGN KEY(ID) REFERENCES AllPages(ID) ON DELETE CASCADE,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1;

but I receive an error after 8 hours of dumping:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `PageContents` at row: 20946782

I had increased max_allowed_packet in mysql.cnf

max_allowed_packet  = 16G

First, make sure you have set max_allowed_packet in the appropriate section [mysqldump] of the config file my.cnf and that it does not exceed the maximal value of 1G:

[mysqldump]
max_allowed_packet=1G

Or you can also specify it at runtime (without having to change my.cnf):

mysqldump --max-allowed-packet=1G ...


Physical vs Logical data independence I am new to information systems and was working on a homework. I am not sure of the difference between physical and logical data independence. What is meant by physical and what is meant by logical ?

I keep thinking of physical as like a harddrive or something but its not. Is it more like a data structure ? So what is logical ?

Logical data is how your applications see the data, and how they query data.

In the case of a relational database, the design of tables, along with their columns and relationships, is the logical model. In other database models the logical level could be queues, collections, or any type of data structure used by applications.

But how are those data structures written to disks (and to memory)? That is the physical layer. It is the set of files written to disk to contain data, and the format used to represent data in such files.

So what is the independence between physical and logical layers? It is the principle that states that programs which query a database don't need to know how it stores data physically. For example, an SQL query mentions table and columns, not files and bytes from that file.


Informix instance not starting When I tried to start the Informix instance in Linux using oninit, it says

WARNING: server initialization failed, or possibly timed out (if -w was used).
Check the message log, online.log, for errors.

Log says:

13:10:30 IBM Informix Dynamic Server Started.
13:10:30 Insufficient free huge pages in /proc/meminfo for shared memory segment.
Requested: 2610302976 bytes. Available: 0 bytes.
The default memory page size will be used.
13:10:30 Segment locked: addr=0x44000000, size=2610302976
13:10:30 Insufficient free huge pages in /proc/meminfo for shared memory segment.
Requested: 1048576000 bytes. Available: 0 bytes.
The default memory page size will be used.
13:10:30 Segment locked: addr=0xdf961000, size=1048576000

13:10:33 Event alarms enabled. ALARMPROG = '/opt/informix/etc/alarmprogram.sh'
13:10:33 Booting Language  from module
13:10:33 Loading Module
13:10:33 Booting Language  from module
13:10:33 Loading Module
13:10:39 DR: DRAUTO is 0 (Off)
13:10:39 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
13:10:39 shmget: [EEXIST][17]: key 52564803: shared memory already exists
13:10:39 (2) shm creation of shmem segment failed
13:10:39 Trusted host cache successfully built:/etc/hosts.equiv.

onclean shows:

 - Looking for semaphore ID: 950274
 - Looking for the shmem key: 52564801
 - Looking for the shmem key: 52564802

oninit -v shows:

 6136
Reading configuration file '/opt/informix/etc/onconf.cars'...succeeded
Creating /INFORMIXTMP/.infxdirs...succeeded
Checking config parameters...succeeded
Allocating and attaching to shared memory...succeeded
Creating resident pool 1494788 kbytes...succeeded
Allocating 1024016 kbytes for buffer pool of 2K page size...succeeded
Creating infos file "/opt/informix/etc/.infos.jensuse02"...succeeded
Linking conf file "/opt/informix/etc/.conf.jensuse02"...succeeded
Initializing rhead structure...succeeded
Writing to infos file...succeeded
Initialization of Encryption...succeeded
Initializing ASF...succeeded
Initializing Dictionary Cache and SPL Routine Cache...succeeded
Bringing up ADM VP...succeeded
Creating VP classes...succeeded
Forking main_loop thread...succeeded
Initializing DR structures...succeeded

WARNING: server initialization failed, or possibly timed out (if -w was used).
Check the message log, online.log, for errors.

It looks like one (or more) of the shared memory segments already exists; there wasn't a clean shutdown.  Use onclean — it should clean up for you.

When you restart, consider using 'verbose' mode: oninit -v.  It will report what it is doing until the server is up and running.


Cannot rename database (the user in --username is the owner) I connect with psql and list my databases:

                                          List of databases
           Name            |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
---------------------------+----------+----------+-------------+-------------+-----------------------
 accyem_20150422           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 accyem_template           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 clinicama_template        | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 intercontinental_template | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 pijalarga                 | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 postgres                  | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sbu_template              | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sf_20150505               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sf_20150527               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 sf_template               | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 tarpuq_template           | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |
 template0                 | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | =c/postgres          +
                           |          |          |             |             | postgres=CTc/postgres
 template1                 | postgres | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 | =c/postgres          +
                           |          |          |             |             | postgres=CTc/postgres
 yucha                     | openerp  | UTF8     | es_EC.UTF-8 | es_EC.UTF-8 |

I am interested on sf_template database.

I want to run the following command:

PGPASSWORD=mypassword  psql --username=openerp --dbname=sf_template --no-password --command="alter database sf_template rename to sf_template_20150605111128"

But get the following error:

  ERROR:  current database cannot be renamed

As I see in the returned results, the user I log in with is the owner of the database. Why can't I rename the database?

You cannot rename a database you're connected to. I did not know that point or even considered it.


Uninstalling Oracle Components on Production So according to the standard I am tasked with bringing my databases in line with, no unused Oracle components should be installed on our databases. The Oracle versions are varied. From reading the Oracle documentation, it seems that components cannot be uninstalled after database creation, but rather have to not be installed in the first place during database creation. Whoever created these databases before me didn't do that.

What is the most efficient way to do this that results in the least disruption to production? My (inexperienced) gut instinct is that a parallel database would have to be created with only the used components installed, have the datafiles from the currently active production database restored on the new database, then change over to the newly populated database on production.

have the datafiles from the currently active production database
  restored on the new database, then change over to the newly populated
  database on production

This will not work at all, unless you meant the transportable tablespace method and you intend to restore user tablespaces, and you create the new database using different binaries without the unnecessary options.

Sure, you can do this with TTS. Or export/import. These methods require downtime.

Or you could just remove options from the existing database. It all depends on the components you want to remove if you need downtime for this.

Some components you can remove at binary level (which requires a database shutdown) using chopt starting with version 11.2 (which is the lowest currently supported version):

Chopt Tool

For example, do disable Partitioning, shutdown the database, then:

chopt disable partitioning

The removal of other options ususally include running scripts provided by Oracle and/or dropping schemas.

For example to remove APEX, you run the below in a regular (non-cdb) 12.2 database:

$ORACLE_HOME/apex/apxremov.sql

More details at (referring this blog post of an Oracle employee, because I could not find all this nicely collected together in the official public documentation):

Component Clean Up Series


T-SQL query to show table definition of sys.time_zone_info Following query could be used to show definitions of custom table.

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PostHistory'

not work for sys tables

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'sys.time_zone_info'

I guess the info is in another table in another database what are they?

I've also tried sp_help 'sys.time_zone_info', and got

  The EXECUTE permission was denied on the object 'sp_help', database 'mssqlsystemresource', schema 'sys'.

sys.time_zone_info is a system view with the definition:

CREATE VIEW sys.time_zone_info AS
    SELECT  name,
            current_utc_offset,
            is_currently_dst
    FROM OPENROWSET(TABLE TIME_ZONE_INFO, SYSUTCDATETIME())

The OPENROWSET call is a wrapper over an internal structure. You cannot get information about it directly.

The columns returned by the view are shown in the documentation

+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+
|    Column name     |  Data type   |                                                          Description                                                           |
+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| name               | sysname      | Name of the time zone in Windows standard format. For example, Cen. Australia Standard Time or Central European Standard Time. |
| current_utc_offset | nvarchar(12) | Current offset to UTC. For example, +01:00 or -07:00.                                                                          |
| is_currently_dst   | bit          | True if currently observing daylight savings time.                                                                             |
+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+

and from nchar and nvarchar (Transact-SQL):

  sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.

You can also obtain the information you want from the system catalogue views, for example:

SELECT
    AC.column_id,
    AC.[name],
    data_type = T.[name],
    AC.max_length,
    AC.[precision],
    AC.scale,
    AC.collation_name,
    AC.is_nullable
FROM sys.all_objects AS AO
JOIN sys.all_columns AS AC
    ON AC.[object_id] = AO.[object_id]
JOIN sys.types AS T
    ON T.user_type_id = AC.user_type_id
WHERE
    AO.[object_id] = OBJECT_ID(N'sys.time_zone_info');

db<>fiddle demo


row count incorrectly displayed as 0 in redshift I have a table in redshift with columns user_id and some_property, neither of which are unique.

The following query returns 0:

SELECT COUNT(DISTINCT user_id) FROM table
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM table WHERE some_property = 1)

Suggesting that for every user_id in the table, there is an entry with some_property=1.

However if I run

SELECT COUNT(DISTINCT user_id) FROM table

then it returns a number greater than

SELECT COUNT(DISTINCT user_id) FROM table WHERE some_property = 1

To me this is a logical contradiction. What am I missing here?

Given the following table:

CREATE TABLE
    t
AS
SELECT
    *
FROM
(
    VALUES
        (   1,    1),
        (   2,    0),
        (NULL,    1)
) AS x (user_id, some_property) ;

The SELECT COUNT from the original poster returns 0, the second one returns 2, and the last one returns 1.

Let's see why:

If you take out the counts and retrieve the rows, we get:

(1)

SELECT DISTINCT
    user_id
FROM
    t
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM t WHERE some_property = 1) ;

|---------|
| user_id |
|---------|

(that is: nothing. The count, in this case, is clearly 0)

(2)

SELECT DISTINCT user_id FROM t ;

|---------|
| user_id |
|---------|
|   NULL  |
|---------|
|    1    |
|---------|
|    2    |
|---------|

(there are 3 rows, but the count is 2, because you don't count NULL)

And last,

(3)

SELECT DISTINCT user_id FROM t where some_property = 1 ;

|---------|
| user_id |
|---------|
|    1    |
|---------|
|   NULL  |
|---------|

(There are 2 rows, but Count is 1, because, again, you don't count NULL).

You have to take into account that, according to SQL standard:

a NOT IN (x, y, z)                is equivalent to
NOT (a IN (x, y, z))              which in turn, is equivalent to
NOT (a = x OR a = y OR a = z)

If a IS NULL, you get NOT (NULL = x OR NULL = y OR NULL = z) which is NULL, regardless of the values of x, y or z.


Investigating/Troubleshooting I/O Spikes We have been experiencing some relatively large I/O (IOPS) spikes on the SAN that supports our production SQL Server.

The spikes seem to fall at an exact time after the hour, each hour.  We have investigated every known scheduled task source (SQL Agent, backups, scheduled SSRS Reports, etc.) and can't find any rhyme or reason to it so far.

We have use Activity Monitor as well, but it has not yielded any answers to date.

How would one go about definitively finding the source of I/O spikes?  Are there monitoring tools (commercial or otherwise) that would help pinpoint the problem?

I have experianced a similar issue a few of times in the last year, and each time it has been due to an external issue, normally anti-virus software with incorrectly configured exclusions. I would check this.

In the mean-time, the following query will let you examine the level of physical IOs comming from SQL Server:

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
                   WHEN a.file_id = 2 THEN 'Log'
                   ELSE 'Data'
                   END,
UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files b ON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC

Pay particular attention to IO_Stalls, Reads and Writes. This query cam from http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx


Error 1456 when trying to SET a Witness using TSQL Trying to run the following command:

ALTER DATABASE myDB SET WITNESS = 'TCP://myServer.local:7024';

I keep getting the following error:

  Msg 1456, Level 16, State 3, Line 21
  The ALTER DATABASE command could not be sent to the remote server instance 'TCP://myServer.local:7024'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

Tried checking firewall ports, making sure certs are valid, making sure endpoints are granted access.

SQL Logs show the following message:

  Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'

I have partner-partner quorum working fine but need the Witness to achieve full quorum. Using certificates to authenticate (no Domain). Has anyone come across this before?

Unfortunately I do not have the reputation to comment, actually I would rather add this as a comment and ask you about the output of your sys.database_mirroring_endpoints DMV first...

I'm assuming that you have verified the connection to be working using telnet or PS Test-NetConnection...

This sounds like the endpoint is not configured for the same authentication type and algorithm.

Run

select dme.*,c.name as cert_name,c.thumbprint,dbp.name as db_principal_name,spr.name as s_principal_name from sys.database_mirroring_endpoints dme
inner join sys.certificates c on dme.certificate_id=c.certificate_id
inner join sys.database_principals dbp on c.principal_id=dbp.principal_id

on your Principal and Secondary instances. Verify STATE_DESC to read "STARTED" and note down the following items which should both be the same on both instances.

role_desc
connection_auth_desc
encryption_algorithm_desc
thumbprint

Run the same statement on the witness instance and also note down the db_principal_name (last column).

Compare your values from the Principal/Mirror and the Witness. If the Witness has any values other than your mirror, change the endpoint on the Witness accordingly. Also verify the thumbprint of the certificate to be the same on your instances.

Warning: changing your endpoint on the Witness might break any Availability Groups or other database mirroring configurations there, so use caution. You can script your existing endpoint configuration in the Object Explorer before you change anything (Server Objects --> Endpoints --> Database Mirroring).

For example, if your connection_auth_desc on Principal/Mirror says "CERTIFICATE" and encryption_algorithm_desc "AES" using certificate SomeCert:

ALTER ENDPOINT endpoint_mirroring
AS TCP(LISTENER_PORT=7024, LISTENER_IP=ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE [SomeCert], ENCRYPTION=REQUIRED ALGORITHM AES, ROLE=ALL)

The login for the user that is stated in db_principal_name should also be allowed to connect to your endpoint. Verify this e.g. using the GUI by going to Security --> Logins --> double click the login and under Securables you should see your endpoint with "CONNECT" permission.

If not and if your Login is named "SomeLogin":

grant connect on endpoint::endpoint_mirroring to [SomeLogin]


What can I add to a server to make SQL restores faster? I have a 2.8TB SQL database (mostly data files, some 400GB of log files) that currently takes around 9 hours to restore. This database is used for testing purposes and must be deleted and restored from a backup between each run, to make sure we're always starting from the same point.

My question is, the server currently has 12 cores and 92GB of RAM, with a RAID 5 disk subsystem that the database is on. What areas usually cause bottlenecks for SQL restore processes? Is it the disk, memory, or CPU?

Don't do a backup and restore; use SQL Server Snapshots. It takes a lot of disk space to store a sparse file the same size as the files you've snapshotted, but rolling back is hundreds of times faster.

They are available in SQL Server Enterprise and SQL Server Developer editions.


Page life expectancy very low on certain time? Everyday we receive an alert on a specific period (03:00 AM - 04:00 AM) indicating that Page life expectancy is too low :

SCOM :Alert: SQL DB 2008 Engine Page Life Expectancy is too low

We have reviewed if any Memory intensive operation is running on this period but nothing was found.

We assume that the cause is a query run through a windows service that cause pages life in the buffer cache to reach a very low value (8 - 22 seconds)

what is a good way to troubleshoot this issue ?

Thanks
Osama Waly

It's almost certainly going to be full backups or indexing. The queries won't show up as a bunch of memory allocated but rather as intensive disk IO. If you are tracking those counters then have a look. Some other easy ways to confirm that these are the cause of that disk IO:

Looking at agent job schedules. If it wasn't checkdb, or indexing, then apps often do their own internal database clean-ups (e.g. deleting records) late at night also.
When checkdb runs it will be written to the ERRORLOG, so run sp_readerrorlog and look for anything at the PLE drop time. This is also useful because some apps like OpsMgr will run their own indexing and checkdb routines at random times without using Agent jobs.
There are also other queries you can run to identify if indexes or stats were recently updated (you'd run it in each database and cast an eye over the results). They aren't 100% accurate but if you saw lots of indexes or stats updated at the PLE drop time it would be an indicator.

Otherwise just stay up late one night and run sp_WhoIsActive interactively to see what's happening. You can also make sp_WhoIsActive output to a table, which means you can schedule an Agent job to run it to a table a few times during your PLE drop window, and then check the output in the morning.

If it turns out to be checkdb or indexing this PLE behaviour is fairly normal and doesn't cause a problem unless you have a very high throughput or time sensitive system that's very busy that early in the morning; if nobody is complaining (and you don't see an increase in query timeouts at that time if you're even tracking them) then likely it's just smoke and no fire.

What if it's not checkdb or indexes? You could have a bad query compilation combined with special parameters suddenly chewing up disk IO and wrecking the buffer. If you're lucky it'll be obvious when you run sp_WhoIsActive, but if you're unlucky then you'd attempt to go through the plan cache using DMVs to find it.

That's a very tough thing to do and there are entire books on it; even if you have the scripts they often don't really aggregate how a plan changes for a query over time along with execution times. You can waste days and weeks trying to work it out while setting up extended events only to find they didn't capture the plan handle for some reason or it's a cursor and you don't know what it's really doing.

If you reach that point then tell your boss you need a tool to assist and buy a license of a proper monitor like SQL Sentry to deploy just during the real tricky problems like this. That's what I did. You can also use an evaluation or use another similar tool to drill down.

Anyway if it does turn out to be a false alarm, surprise, that's a common downside of OpsMgr. It may be amazing for some tasks but for SQL Server it's never been a good fit no matter how they try to shoehorn it. I haven't been able to tune its alerting to a functionally acceptable level so can't comment on improving that.


Enforcing a "disjoint or identical" constraint Suppose that F is an Oracle table with two columns, X and Y, such that the constraint

CONSTRAINT unique_xy_pairs UNIQUE (X, Y)

is satisfied.

For each value [x] of F.X, let S[x] represent the set consisting of the values returned by the following (pseudo-SQL) query:

SELECT Y FROM F WHERE X = [x];

Now, I want to enforce the constraint that for any two arbitrary values [x1] and [x2] of F.X, their corresponding sets S[x1] and S[x2] must be either disjoint or identical.

How can I do this?

EDIT: A uniqueness constraint on F.Y would not be a valid solution, since such a constraint could be violated by tables that nonetheless satisfy the constraint described in the original statement.

For example, the following is an F table that does not satisfy a unique F.Y constraint, but satisfies the "disjoint or identical" constraint in the original post:

 X  Y
10  0
10  1
20  2
20  3
20  4
30  0
30  1

After I posted my question, I thought of a possible solution.

For concreteness, let's say that this is the table definition for F:

CREATE TABLE F (
    X NUMBER(10) NOT NULL,
    Y NUMBER(10) NOT NULL,
    CONSTRAINT xy_unique UNIQUE (X, Y)
);

We could replace this F with tables G, H, and I, defined as follows

CREATE TABLE I (
    ID NUMBER(10) PRIMARY KEY
);

CREATE TABLE G (
    X NUMBER(10) NOT NULL,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_g_yset_id FOREIGN KEY (YSET_ID) REFERENCES I(ID),
    CONSTRAINT x_unique UNIQUE (X)
);

CREATE TABLE H (
    YSET_ID NUMBER(10) NOT NULL,
    Y NUMBER(10) NOT NULL,
    CONSTRAINT fk_h_yset_id FOREIGN KEY (yset_id) REFERENCES I(ID),
    CONSTRAINT unique_y UNIQUE (Y)
);

The idea is that H is now a table of "y-sets".  Each y-set consists of all the [y]'s corresponding to a given value of H.YSET_ID.  The fundamental difference between F and H is that for H we can define a uniqueness constraint on H.Y, which ensures that these y-sets are disjoint.

Table I's only function is to relate G and H.  (It would be convenient if G.YSET_ID could refer directly to H.YSET_ID, rather than indirectly via I.ID, but my understanding is that a foreign key must always refer to a primary key...)

(I don't know much about Oracle's version of SQL, so this solution is bound to be clumsy, or even syntactically incorrect.)

For example, if F is

 X  Y
10  0
10  1
20  2
20  3
20  4
30  0
30  1

...then the corresponding tables G, H, and I could be

G:
X  YSET_ID
10 100
20 101
30 100

H:
YSET_ID Y
100     0
100     1
101     2
101     3
101     4

I:
ID
100
101

As pointed out by @ypercubeᵀᴹ, the original F can be recovered from the new G, H, and I tables with

SELECT G.X, H.Y FROM G JOIN H ON G.YSET_ID = H.YSET_ID;

EDIT: Yet another afterthought: it turns out the there is a surprisingly symmetrical variation of the solution above:

CREATE TABLE I (
    ID NUMBER(10) PRIMARY KEY
);

CREATE TABLE G (
    X NUMBER(10) PRIMARY KEY,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_g_yset_id FOREIGN KEY (YSET_ID) REFERENCES I(ID)
);

CREATE TABLE H (
    Y NUMBER(10) PRIMARY KEY,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_h_yset_id FOREIGN KEY (yset_id) REFERENCES I(ID)
);

Even though it is not clear from my problem description, a table satisfying the desired constraint expresses a bijective function whose domain and codomain are both sets of disjoint sets.  My second solution above makes this symmetry manifest.


How can I generate an Entity-Relationship Diagram and SQL DDL statements from natural language text? I am using LaTeX or another text-macro tool/library. How can I (1) use natural language text to describe relationships, then (2) generate an Entity-Relationship Diagram and (3) generate SQL DDL to create the database schema from it?

There are many ways to do this, but the most important thing to know is that there is a name for what you want to do: Object Role Modeling. It's a process for creating conceptual models using natural-language semantics.

This is a pretty good (if old) overview of the process.

The Wikipedia page for ORM has a pretty complete list of the available tools, many of which are free.

The most up-to-date solution is NORMA, since it is actively maintained by the ORM Foundation, but that's a plug-in that requires Visual Studio.

If you don't have Visual Studio, Infagon looks like it is still pretty actively developed and may be your best bet.

CaseTalk is also worth a look. Free to students and educators, but commercial otherwise.


I cannot add a new Server Role "SQL Server 2008 R2" I want to add a new user-defined Server Role to my SQL Server instance, but the menu doesn't show all options, it only shows this:

Instead of this :

and I also tried the T-SQL command to create the server role but it keep showing the message

  Incorrect Syntax near 'AUTHORIZATION', expecting id ,quoted_id or to.

Please Help ASAP.

CREATE SERVER ROLE only apply to SQL Server 2012 through current version.

See on MSDN: CREATE SERVER ROLE (Transact-SQL)


How can I run a proc against all records in a temp table? I have 4000 records in a temp table with one column. The column is a customerid that I need to update a record for. As of right now the only thing that comes to mind is running an exec statement for all 4000 records. Is there a more efficient way to do this without the use of a cursor?

If you need to improve the performance of your code it probably makes sense to focus on converting the stored procedure into a set-based operation. For example, instead of issuing 4000 individual UPDATE queries you could issue one UPDATE that acts on 4000 rows. Changing from a cursor to some other row by row method benefit is very unlikely to speed up your code.
If you truly need to loop over all of the rows in a table just use a cursor. That's what they're designed for. This is especially true when you're doing with a small number of rows in the table like 4000. The cursor won't take a meaningful amount of time as long as you don't increase the number of rows or have many concurrent processes.
Test procedure and data:
CREATE OR ALTER PROCEDURE dbo.DO_NOTHING (@input_id INT)
AS
BEGIN
    DECLARE @i INT;
END;

GO

CREATE TABLE #t (ID INT);

INSERT INTO #t
SELECT TOP 2000 number
FROM master..spt_values

UNION ALL

SELECT TOP 2000 number
FROM master..spt_values;

Here's the code that calls the procedure for each row in the temp table:
DECLARE @id INTEGER;
DECLARE @rows_processed INTEGER = 0;
DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT id
from #t;

PRINT 'Start time = ' + CAST(SYSDATETIME() AS VARCHAR(30));
OPEN cur;

FETCH NEXT FROM cur INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.DO_NOTHING @id;

    FETCH NEXT FROM cur INTO @id;
    SET @rows_processed = @rows_processed + 1;
END;
CLOSE cur;
DEALLOCATE cur;
PRINT 'Rows processed = ' + CAST(@rows_processed AS VARCHAR(5));
PRINT 'End time = ' + CAST(SYSDATETIME() AS VARCHAR(30));

The output on my machine:

Start time = 2017-06-02 22:39:23.2709937
Rows processed = 4000
End time = 2017-06-02 22:39:23.3255551

The code ran in 0.05 seconds.


Log shipping restore: Only members of the sysadmin fixed server role can perform this operation On my secondary, I am seeing many messages like these in sysjobhistory from the LSCopy and LSRestore jobs for each database:

Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping)
Error: Only members of the sysadmin fixed server role can perform this operation.(.Net SqlClient Data Provider)

The job steps are being run by "SQL Server Agent Service Account". That service account is a domain service account which has a login with the sysadmin role.
What else should I be looking for to find the true cause of the messages?
The msdb.dbo.log_shipping_monitor_secondary table hasn't been updated in months based on the values in last_copied_file, last_copied_date, etc., yet the LSCopy and LSRestore jobs are succeeding (and show current file names in the messages). (The LSAlert job is disabled.)
One interesting thing is that the names in the log_shipping_monitor_secondary.secondary_server column is the name of the primary server. From what I can tell, the secondary server was cloned from the primary and renamed before log shipping was set up, but the SQL instance name wasn't changed.
I'd like to resolve all this without completely rebuilding this secondary server.

I found your question while googling the same errors and solved my issue by giving the monitoring server's agent account sysadmin (sysadmin was not needed for the agent accounts on the primary and secondary)

I think the problem might be caused by having the monitoring server on a separate machine but I could not find any documentation on this.

Running profiler during each logshipping job showed that the jobs explicitly ask if the user is sysadmin before continuing on to update the history tables:

select IS_SRVROLEMEMBER(N'sysadmin')

Initially I tried adding sysadmin to the primary and secondary server's agent accounts but the same errors continued. In the end I discovered that it is only the monitoring server's agent account that needs sysadmin. This allows the msdb.dbo.log_shipping_ tables to be updated with the last backup, copy and restore dates as well as the lsn numbers on the three servers. Then the LS_Alert_ job and the Transaction Log Shipping Status report are populated with the latest values.


MySQL Workbench - question about creating FK relationships Forgive me a little new at databases and I came across something while working with Workbench that I have a question on.

In a very simple testing database, with three tables: Company, Department, Employee

Company          Department      Employee
 - id (PK)(AI)   - id (PK)(AI)    - id (PK)(AI)
 - Name          - Name           - Name

I created a 1 to many relationship between Company and Department and since Employees can only have one Department and One company, I created a 1 to many relationship between Company and Employee and Department and Employee.

When I did this, Workbench created a third Relationship column in Employee Called Company_Department_id and all of my columns that have FK all have now been check as PK as well, tables now looks like this:

    Company       Department             Employee
    - id (PK)(AI) - id (PK)(AI)           - id (PK)(AI)
    - Name        - Name                  - Name
                  - Company_id (PK)(FK)   - Company_id (PK)(FK)
                                          - Department_id (PK)(FK)
                                          - Company_Department_id (PK)(FK)

Questions

Why does workbench automatically create the Company_Department_id table? I feel like I should be able to delete it, but I just want to make sure I'm not missing something
How come Workbench automatically tagged each related column as a primary key?  Is it because they are primary keys in their own tables?  Should I keep them as primary keys?

It looks like you've used the option to create an Identifying relationship (this is the one with the unbroken line in the toolbox).

An Identifying relationship assumes that the primary key in the referenced table should be part of primary key of the referencing table.

Because Company_id is now part of Department's Primary Key, it must also be part of the foreign key of Employee, hence your problem #1.

What you're probably wanting to do is create a Non-identifying relationship. This can be done by either selecting the dashed line from the toolbox, or unticking "Identifying relationship" in the Foreign Key tab of the Relationship properties.

A Non-identifying relationship is a classic foreign key constraint, and simply ensures that any value in the referencing table exists in the referenced table.

Technically this should resolve your problems, but there are still potential issues with the underlying database design.

For example, there's nothing to stop multiple companies existing with the same name, or multiple departments with the same name, even within the same company.

This could be solved by adding unique key constraints, but another way to tackle the same problem would be to use the Company and Department names as primary keys.

If you were doing this, you'd actually want to use an Identifying relationship, so that the Company name becomes an integral part of the Department.

(You may wish to read up on database theory and database normalisation, as it will help you avoid a lot of traps that you're likely to come up against when building a database)

I'm not suggesting that this is a better database design, simply that this is one where an Identifying relationship is valid / useful.


Accessing Administrative Share Using Listener Name Can a SQL Server AG listener name be used to access an administrative share
i.e  \\DB-listener\c$\Somefolder?

I can RDP using the listener name but can't use the listener name to access the $ shares (as mentioned above).

You cannot use the listener name to access administrative shares on the cluster nodes.  When accessing a cluster network name (which is what a listener is), shares are scoped to clustered shared folders only.

Please see Deploying a two-node clustered file server for reference.


How to model exclusive options A fictional case: a participant is asked which days of the week s/he goes jogging or swimming. The answer can be Mon, Tue, Wed, Thu, Fri, Sat, Sun as well as None, RF (Refuse to answer), DK (Don't know). A participant can choose any day or a combination of them. But if a participant chooses None, RF or DK, other options cannot be selected. How do I model this in an table, ensuring data integrity.

An example:

+-------------+----------+------+
| Participant |   Type   | Day  |
+-------------+----------+------+
| Alice       | Jogging  | Mon  |
| Alice       | Jogging  | Tue  |
| Alice       | Swimming | Wed  |
| Alice       | Swimming | Thu  |
| Bob         | Jogging  | Tue  |
| Bob         | Jogging  | Wed  |
| Bob         | Swimming | Tue  |
| Bob         | Swimming | Wed  |
| Bod         | Swimming | Sun  |
| Chuck       | Jogging  | Mon  | <-- A*
| Chuck       | Jogging  | Mon  | <-- A*
| Chuck       | Jogging  | Tue  |
| Chuck       | Jogging  | Sun  |
| Chuck       | Swimming | Sun  | <-- B*
| Chuck       | Swimming | None | <-- B*
| Dan         | Jogging  | Mon  | <-- C*
| Dan         | Jogging  | RF   | <-- C*
+-------------+----------+------+

A* - A unique constraint on (Participant, Type, Day) can prevent this duplication.

B*, C* - How can these be prevented if a participant choose None or RF, no other options can be entered.

Another example would be diet choices. Options include: low fat, low carb, gluten free, vegan, seafood, etc. A combination of low fat, low carb, gluten free is possible. But a combination of vegan + seafood is not possible.

In my opinion, these restrictions are best maintained within the UI for said survey/questionnaire because a survey is designed to be situation-specific and highly volatile whereas a data model should be built to be robust and flexible, but not so rigid it become grossly limited in functionality.  Regarding the rest of the answer though, these features primarily pertain to Microsoft SQL Server.  Most of this functionality can be found in other RDBMS platforms, but the links I'll include are specific to MSSQL.

If you must push this sort of logic down to the database, you have a few options.  The first one you've already touched on is the Unique Constraint.  This is a quick check to execute, but it is applied to the entire table, so in your example if another "Chuck" comes and takes the survey his selection of Jogging on Monday (while a valid decision because it's a different Chuck) will throw a constraint error.  Obviously the table would need to be designed so it could track unique users, etc. but hopefully you get where I'm going with the limitations of Unique Constraints.

The sibling of the Unique Constraint is the Check Constraint.  These can be a little more fleshed out as you can create User Defined Functions wherein nested logic can be coded.  So for scenarios B and C above, you could conceptually code up some logic to check for those conditions.  Check Constraints with Scalar UDFs are often horribly slow and will cause performance issues, so this isn't an approach I'd ever recommend... but you can do it.

Another option within the database to handle these situations are Triggers, and in this situation specifically, DML Triggers.  These can be designed to be even more complex than Check Constraints and given the right situation can perform relatively well.  Again, these can also fall prey to poor performance if they're not written well, but this is another way to force logic down to the data model layer.

Hopefully this helps, but forcing this much business logic into the data layer is often frowned upon unless there is some sort of performance benefit, which in this case is unlikely.


event get not executed I'm on 2 debian8 box's with mysql-5.7.19.

I have a master slave replication running, replicating one db (db1.1) from master to slave (db2.1).
Replicated db's on master and slave have same names.

Master has another db (db1.2) that pulls some statistical data out of db1.1.
That happens by means of some views (getting data from db1.1) in db1.2 and an event in db1.2 that calls a procedure.

Now I like to take that statistical workload of from master and put it on the slave.

I created another db, db2.2 on slave with identical table and views as in db1.2.
Names of db1.2 and db2.2 are different.

Created same procedure on slave as on master and an event with another name on slave.db2.2 that calls that procedure.

If I call the procedure by hand on slave it does what it supposed to do. Copies all data as expected.

But calling that procedure by an event on slave doesn't run.
Names of the events on master and slave are different.

What I'm doing wrong?

After having a nap I found solution by myself.

The Event Scheduler was not running on the slave machine.

SET GLOBAL event_scheduler = ON;

did the trick.

It's running now.


When performing a cross-database transaction, in which transaction log(s) is the information stored? Given the following snippet:

-- error checking omitted for brevity
begin tran

exec database1..my_stored_procedure
exec database2..my_other_stored_procedure

if (@@error <> 0)
  rollback

commit

Into which database's transaction log(s) will the transactional information be inserted?

I would expect both logs would get all the data, since it wouldn't make sense if you attempted to replay database1's transaction log and it only affected that DB. I'd also expect that you would be unable to replay database1's transaction log on a server where database2 wasn't present, and vice versa.

.. but I'm open to corrections!

The transaction log isn't recording the SQL statements being executed, as you might be expecting. Instead, it's recording the changes to the raw data in each database, independently.

It's possible for a stored proc from one database to be working entirely in the transaction log of another database.

... database1..my_stored_procedure AS
BEGIN
INSERT INTO database2..table1 (col1) values (1);
  ^^ changes written to database2's tlog
INSERT INTO database2..table2 (col1) values (2);
  ^^ changes written to database2's tlog
END
^^ when this transaction is committed, COMMIT is recorded in database2's tlog

Or for it to make changes to both.

... database2..my_other_stored_procedure AS
BEGIN
INSERT INTO database1..table1 (col1) values (1);
  ^^ changes written to database1's tlog
INSERT INTO database2..table1 (col1) values (2);
  ^^ changes written to database2's tlog
END
^^ when this transaction is committed, COMMIT is recorded in BOTH database1's and database2's tlog

What's recorded in the transaction log are the actual data changes, not the SQL statements which caused them to happen. The entries to each transaction log file are entirely independent, except insofar as the COMMIT is written to the two log files at the same time, once the transaction is committed.

The same logic applies if you have a larger transaction running a number of stored procedure across multiple databases. Once you COMMIT your transaction, the COMMIT will be recorded in the log of every database that participated in the transaction.

It's perfectly possible to restore a backup of database2, and replay its transaction logs on a server that doesn't have database1.

This behaviour allows some flexibility in how procedures and views are laid out in SQL Server. Many database administrators keep their stored procedures - especially maintenance ones - in a database (e.g. Admin) that's completely separate to application/user databases, and write the results of the maintenance operation to that database. Thankfully, it's possible to restore one of the user databases to a development server without needing to copy across Admin as well.


How do I check if a constraint exists on Firebird? I'm about to publish a script which will update a lot of Firebird databases all at once. Some will not have this constraint, so I would like to check for the existence of a constraint before I try to drop it.

ALTER TABLE PROCESS_CATEGORY DROP CONSTRAINT INTEG_669;

You can query RDB$RELATION_CONSTRAINTS table, if you know the name of the constraint.
Something like this:

set term ^;
execute block as
begin
    if (exists(
        select 0 from rdb$relation_constraints
        where rdb$constraint_name = 'INTEG_669'
    )) then
        execute statement 'alter table process_category drop constraint INTEG_669';
end
^
set term ;^

Used execute statement because alter table not allowed inside the block and if statement not allowed outside.


Tracing oracle database I was working with sql server databases for years and I am used to tools like sql server profiler for tracing all queries on particular database.

My question is - how to do this same thing with oracle database. I am using sql oracle developer version 19.2 and I don't see any option there for tracing DB.

I've also checked this question and tried several tools there (the question is quite old) with no success. Do I really need oracle enterprise manager for doing such a basic thing? (that is suggestion from one of the answers).

My application is written in .NET - why is it so hard in oracle to track queries that this app is triggering?

Tracing is difficult in Oracle Database for several reasons:

you need to identify the involved database sessions which is more or
less easy
you need to use DBMS_MONITOR package (only granted to SYSDBA
by default)
you need to have access to the raw trace files (they are
generated on database server side - see V$DIAG_INFO view)
you need to use TKPROF on the raw trace file to have something
readable (TKPROF is part of Oracle Client but not Oracle Instant Client).

If you need to have only SQL statements without execution plan and detailed timings you could modify your code to enable SQL trace with "ALTER SESSION SET SQL_TRACE=TRUE" (raw trace file generated on db server side).

If you have your dedicated database instance (like Oracle XE) you could just enable SQL trace on database level (ALTER SYSTEM SET SQL_TRACE=TRUE) and get all trace files.

Here is one of the best summary of SQL tracing options in Oracle:
https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

You don't need OEM to enable trace but you need DBA privileges or to work with your DBA to use SQL trace.


How to create a new table from other tables that are related to each other? I need to create a new table with all columns from other 4 tables.

My idea for creating the table would be to use the structure:

CREATE TABLE IF NOT EXISTS table_name AS SELECT ~select from other tables~

So far, so good. But I can't find a proper way to do the joins without the foreign key / primary key columns in the table repeating.

Is there a way to use something as distinct, but for columns, no for values?

I know I could do a simpler way, which would be to select exactly the fields I need (like select column.field1, column2.field2), but the 4 tables I need to use to create the new one, have several fields, would be a long code, I don't know if it would be the best way, I wanted something more optimized.

I was also wondering if using this select table structure as per the example would there be any way to set a primary key on creation?

I just tried typing the primary key (column) command after the end of select in various ways (as it would be if I was creating the table by normally setting the fields) and was unsuccessful. Creating the table with AS SELECT, can I only define primary and foreign keys with analter table after creation?

A sample:

USE test

-- Create source tables ...
CREATE TABLE table_1 (id INT, field_1 INT);
CREATE TABLE table_2 (id INT, field_2 INT);
CREATE TABLE table_3 (field_1 INT, field_3 INT);
CREATE TABLE table_4 (field_1 INT, field_4 INT);

-- ... and fill some sample data
INSERT INTO table_1 VALUES (1,1), (2,2);
INSERT INTO table_2 VALUES (1,11), (2,22);
INSERT INTO table_3 VALUES (1,111), (2,222);
INSERT INTO table_4 VALUES (1,1111), (2,2222);

-- Build SQL statement text ...
SELECT CONCAT('CREATE TABLE IF NOT EXISTS new_table AS',
              ' SELECT ', GROUP_CONCAT(fields),
              ' FROM table_1',
              ' NATURAL JOIN table_2',
              ' NATURAL JOIN table_3',
              ' NATURAL JOIN table_4;') INTO @sql
FROM ( SELECT CONCAT(MIN(table_name),'.',column_name) fields
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = 'test'
       AND table_name IN ('table_1', 'table_2', 'table_3', 'table_4')
       GROUP BY column_name ) fields;

-- ... and execute it ...
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

-- ... then check the result
SELECT * FROM new_table;

    mysql> SELECT * FROM new_table;
    +------+---------+---------+---------+---------+
    | id   | field_1 | field_2 | field_3 | field_4 |
    +------+---------+---------+---------+---------+
    |    1 |       1 |      11 |     111 |    1111 |
    |    2 |       2 |      22 |     222 |    2222 |
    +------+---------+---------+---------+---------+
    2 rows in set (0.00 sec)

-- Drop sample tables
DROP TABLE table_1;
DROP TABLE table_2;
DROP TABLE table_3;
DROP TABLE table_4;
DROP TABLE new_table;


Is this a good structure for a table? Each user/person could know one or more languages.

All I can think is a table like

+----------+------+-----+------------+-----+-----+-----+-------+
| PersonID | Java | PHP | Javascript | C++ |  C  | CSS | HTML  |
+----------+------+-----+------------+-----+-----+-----+-------+
|        1 | Yes  | Yes | No         | Yes | No  | Yes | No    |
|        2 | No   | Yes | Yes        | No  | Yes | No  | No    |
|        3 | Yes  | No  | Yes        | Yes | Yes | Yes | No    |
+----------+------+-----+------------+-----+-----+-----+-------+

Considering I'm going to need at least 100 columns for all the languages, is it normal to have that many columns? Something tells me this is the wrong approach.

Thank you very much and sorry about my english!


Inconsistency in Tables - Mariadb Galera Cluster MariaDB cluster Version : mysql Ver 15.1 Distrib 10.0.24-MariaDB,

I have a two node + arbitrator cluster which is live and replicating data across nodes.

Unfortunately we found some inconsistency in some of the tables in Databases.

For instance:

Node1:

MariaDB [(none)]> select count(*) from example_db.reports;
+----------+
| count(*) |
+----------+
|      299 |
+----------+
1 row in set (0.00 sec)

Node2:

MariaDB [(none)]> select count(*) from example_db.reports;
+----------+
| count(*) |
+----------+
|      285 |
+----------+
1 row in set (0.00 sec)

Note: Not find any noticeable errors in mysql error log

What could be the reasons for these kind of inconsistency ?

Is that a bug or a known issues ?

Thank you.

pt-table-checksum supports Galera. Try it to see if there are any inconsistencies.


mysql 5.7.9-log set session mode gives warning about NO_AUTO_CREATE_USER is deprecated When changing the session mode in mysql 5.7 I get the following warning.

"(Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release."

I don't exactly understand what it means. Is mysql removing the ability to be able to set a session mode?

Mysql terminal

mysql> \W
Show warnings enabled.
mysql> SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
mysql>

"Deprecated" means that the feature works as intended, but it is not recommended anymore because it will probably be disabled in a future realease. MySQL backwards compatibility works usually by deprecating, showing a warning on version X+1 (e.g. 5.7) an an error on the next one (e.g. 5.8). That way you have time to adapt your database and scripts.

In particular, your error is that you are not using NO_AUTO_CREATE_USER as the SQL Mode. In other words, NO_AUTO_CREATE_USER should be enabled by default and you should not change it (disable it) because the option will probably disappear in 5.8, being always enabled.

As a consequence, you cannot any more assume that GRANT creates a user automatically, and instead you should execute explicit CREATE USER statements. This is done because if you run:

CREATE USER root@'10.0.0.11' IDENTIFIED BY 'secret';
GRANT SELECT ON 'wordpress.%' TO root@'10.0.1.1';

You would not have created a user with a password and provided him with access to the wordpress database - you have created a user with a password, and then created another, different user without password with access to that database. That is a security issue so NO_AUTO_CREATE_USER is a very recommended configuration.

I would not recommend you to set the sql mode at session level, but if you do, write:

SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER";

or an ever more strict mode.

The reason why I do not recommend this is double:

You are disabling the stricter defaults (stric_trans_tables, etc.):

mysql> SET SESSION sql_mode=DEFAULT; SELECT @@sql_mode\G

Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************

@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in set (0.00 sec)
This is unnecessary if you were able to do it globally (SET GLOBAL or on configuration file). Adding it on session level means you have an extra roundtrip to the database, adding latency. Executing one SET takes very little time, but if you do it on every single query/session, the time adds up. However, if you cannot change it at server level, the session change is the only way to do it.


How can a datapump export have its resource usage limited on linux? Doing an export of a number of users causes high CPU and I/O usage.  I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how can I limit the disk I/O?  I am not running the operation in parallel and have already read Oracle's Datapump Performance documentation.  Is there anything else I can do?

Ionice can limit disk usage

Take a look at the ionice utility for linux, as it seems to suit your needs quite well.


INFORMATION_SCHEMA.GLOBAL_VARIABLES alternative in 5.7? More info about show_compatibility_56 mysql> SELECT * FROM GLOBAL_VARIABLES;
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

mysql> show global variables;
ERROR 1146 (42S02): Table 'performance_schema.global_variables' doesn't exist

I googled that and found links to tocker.ca which seems down.

$ curl  http://www.tocker.ca/2015/08/18/a-followup-on-show_compatibility_56.html
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

<title>404 Not Found</title>

Not Found

The requested URL /2015/08/18/a-followup-on-show_compatibility_56.html was not found on this server.

Where do I see what settings/variables have changed from default one (in my setup)?

Any background about this change?

You need to enable the PERFORMANCE_SCHEMA.

See the documentation for details:

  The Performance Schema is disabled by default. To enable it, start the
  server with the performance_schema variable enabled. For example, use
  these lines in your my.cnf file:

[mysqld]
performance_schema=ON

*Please check updated syntax from mysql website

Migrating to Performance Schema System and Status Variable Table has all the information you'd ever need on the matter.


Extended event not staying on I've created an extended event to debug a very rare issue in a web application. Very occasionally data from forms is not making it to a stored procedure, so I want to verify that when the SP is being called, it is actually lacking the missing data. I'm using to Extended Event to view the passed parameters.

I went in today to check the logs, and found that the Event has not been capturing data, and indeed, the session was stopped in SSMS.

How do I ensure this even doesn't turn itself off? I've set it to start on server startup. Here is text of the event:

CREATE EVENT SESSION [DB_Name SP execution] ON SERVER

ADD EVENT sqlserver.rpc_starting
(
    ACTION
    (
        sqlserver.database_id,sqlserver.database_name,
        sqlserver.server_instance_name,
        sqlserver.session_id
    )
    WHERE
    (
        [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
            AND [sqlserver].[database_id]=(34))
),

ADD EVENT sqlserver.sp_statement_starting
(
    SET collect_statement=(1)
    ACTION
    (
        sqlserver.database_id,sqlserver.database_name,
        sqlserver.server_instance_name,
        sqlserver.session_id
    )
    WHERE
    (
        [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
            AND [sqlserver].[database_id]=(34)
    )
),

ADD EVENT sqlserver.sql_batch_starting
(
    ACTION
    (
        sqlserver.database_id,sqlserver.database_name,
        sqlserver.server_instance_name,
        sqlserver.session_id
    )
    WHERE
    (
        [package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [package0].[equal_boolean]([sqlserver].[is_system],(0))
            AND [sqlserver].[database_id]=(34)
    )
)

ADD TARGET package0.event_file
(
    SET filename=N'D:\path\to\log.xel',
    max_file_size=(5120)
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=ON
)

GO

Edit
I started it when I set it up, and it captured data for two days (8/9 - 8/11). Haven't checked in again until today, found it was off and there has been no data since 8/11. It is currently on and capturing data, but I don't want to have to manually check that it's on every day.

Since both ALTER EVENT SESSION and DROP EVENT SESSION are DDL statements that have Server Scope, I would suggest setting up a Server DDL Trigger to capture when your EE is stopped/dropped.

This generates a simple notification when either DDL statement is executed, but feel free to alter it to your liking:

CREATE TRIGGER EE_Change_Notifications
ON ALL SERVER
FOR ALTER_EVENT_SESSION, DROP_EVENT_SESSION
AS
    DECLARE @msgBody NVARCHAR(MAX)

    SET @msgBody = N'Extended Event [' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10)
            + N'Event Type: [' + EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10)
            + N'Login: [' + EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)') + N']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
            + N'Statment:' + CHAR(13) + CHAR(10) + EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')

    EXEC msdb..sp_send_dbmail
          @profile_name = 'DB Mail Profile Name'
        , @recipients = 'person@place.com'
        , @body = @msgBody
GO

If this doesn't provide notification that the EE has been stopped, you may be running into a situation where the event is failing for whatever reason.  At that point review your ERROR logs and see if anything occurred and dig from there.  If you can narrow down a specific error code, you could then configure a SQL Agent Alert to email you if/when that specific error happens again.


Debugging sleeping connections I'm troubleshooting an app that has a lot of sleeping connections to my MySQL (MariaDB) server. I'd like to see just what the queries are on these sleeping connections, but when I run SHOW FULL PROCESSLIST, the Info column holds only NULL.
I suspect theses connections represent long-running queries, but it's a sizeable app, and I'm not sure what parts are creating the offending queries.
Is there a way for me to discover what the queries are?

Sleeping connections are not running queries, sleeping connections represent connections that the client/app failed to close successfully, most of the time due to bad programming practices (developers nos closing connections after querying the DB) or bad connectors configuration (not sending a close connection signal or even using an old/outdated connector).
Most of the cases this is not a big issue, but sometimes connection pool can grow out of control a represent a real hazard for real new requests traying to create new connections to the DB, so the best approach to "fix" this issue is to review the application/client and send a close() to the connection or check the connector.
You can also play with wait_ timeout variables to limit the time sleeping connections can remain active.


Is it possible to record all the SQL executed by a session? There is a web application which is connected to my Oracle database server via JDBC.

When a user does some actions on that web application, I want to know what SQL statements were submitted to database. Is this possible?

You can find all SQLs that were executed in the sys.v$sqltext view. Together with the sys.v$session view you can see who executed it. Is this what you are looking for?

Otherwise check to see if you can get the information from the application. See if you can change the log level for the classes that access the database.


MS-SQL: unique constraint case-insensitive How do I add a case-insensitive constraint without altering the column collation?

I've tried

CONSTRAINT [AK_User_Username_UserType] UNIQUE (LOWER([Username]) [UserType])

and

CREATE UNIQUE INDEX [AK_User_Username_UserType] UNIQUE (LOWER([Username]) [UserType])

But it won't compile...

Do I need to add a computed column to achieve this??

Yes a computed column would achieve this for you. Or you could create an trigger that will fire on insert or updates. Here is link to a question like yours on stackoverflow.


How to involve table index in filtering of 3 conditions? I have table with 30 mln records and I want to get data by some query very fast. I have these fields: id (int), field1 (int), field2 (int), field3 (int). So I want to query data by this condition

SELECT *
FROM mytable
WHERE field3 = 1
AND (field1 < field2 OR field2 < 0)
LIMIT 1

Query plan I got

explain (analyze, buffers) select * from mytable mt where (mt.field1 < mt.field2 OR mt.field2 < 0) and mt.field3 = 1 limit 1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=13) (actual time=525.050..525.051 rows=1 loops=1)
   Buffers: shared hit=245 read=35166
   ->  Seq Scan on mytable mt  (cost=0.00..359373.97 rows=983846 width=13) (actual time=525.048..525.048 rows=1 loops=1)
         Filter: ((field3 = 1) AND ((field1 < field2) OR (field2 < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=245 read=35166
 Planning time: 0.163 ms
 Execution time: 525.077 ms

I have index set on field3 but it used by postgres only with

SELECT *
FROM mytable
WHERE field3 = 1
LIMIT 1

I tried to set index on fields (field3, field1, field2), then on the (field3, field2, field1), but it only works without field1 < field2.

Then I tried to set index only for condition field1 < field2. Indexes (field1, field2) and (field2, field1) don't work too.

What index do I need to use it with condition field1 < field2?

Update.
I add partial index by condition

CREATE INDEX idx_mytable_condition_1 ON mytable (field3) WHERE (field1 < field2 OR field2 < 0);

but anyway there's no using index, only seq scan

explain (analyze, buffers) select * from mytable mt where mt.field3 = 1 and (mt.field1 < mt.field2 OR mt.field2 < 0) limit 1;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=37) (actual time=492.787..492.787 rows=1 loops=1)
   Buffers: shared hit=277 read=35134
   ->  Seq Scan on mytable mt  (cost=0.00..359373.97 rows=983846 width=37) (actual time=492.785..492.785 rows=1 loops=1)
         Filter: ((field3 = 1) AND ((field1 < field2) OR (field2 < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=277 read=35134
 Planning time: 0.110 ms
 Execution time: 492.806 ms

You have a problem with correlations of the expressions.  The cases where field3 = 1 are selectively depleted of cases where ((field1 < field2) OR (field2 < 0)).  PostgreSQL thinks that those are independent, but they aren't.  So it thinks you will very quickly find a qualifying row by doing a seq scan.  But instead it had to dig through 4 million before finding the first one.

A quirk of PostgreSQL is that it collects special statistics on functional indexes, but not on plain partial indexes.  So if you add a dummy function to your partial index, you may get a better plan:

create index on mytable (abs(field3)) WHERE (field1 < field2 OR field2 < 0);

And then your query would have to use abs(field3)=1 in the condition.  Of course you can only do that if field3 is never negative, otherwise pick some other dummy function to use.


Separate archive tables or soft delete for inventory database I'm building an inventory database that tracks computer equipment and other hardware devices. At some point in any device's life it is retired and gets archived. After it becomes archived it needs to be tracked as it is removed from service and properly disposed. I originally designed the archiving mechanism using an exact replica of the active database that would receive its data using a trigger on delete from the active database. The archive database includes replicas of all the related tables because as certain foreign related records are no longer pertinent, they should not be accessible to users to use with new devices, but are required for referential integrity and querying with the archive tables. Keep in mind that the concept of archive here is not just to keep a history or a log. The archive is a part of the business process, and users will need to query and update devices that are both active and archived.

The ERD below uses the Inventory.DeviceType table as an example where all entries and updates are copied to the Archive.DeviceType table. When users should no longer be able to enter inventory records of a certain device type, it is deleted from the Inventory.DeviceType table, but remains in the archive table. This pattern is used on all tables to ensure the archives refer to valid data, hence the replica of all tables.

Active Table Example (Other related tables omitted)

Archive Table Example (Other related tables omitted)

Problem

I'm trying to figure out how I would query the database if I don't know if a device is active or archived? For example, if a user has a serial number and wants to find out information about the device, and they are unaware of whether it has been archived.

Option 1: Create a view based on a union all?

Option 2: Query the active database and then query the archive if the first query returns nothing?

The saga continues...

An associate suggested that I eliminate the archive database and use a soft delete scheme. I built a model using this idea, and then started running into a host of other problems.

Here are the same tables using the soft delete scheme.

Soft Delete Example

With this setup, devices are archived by setting IsArchived field to true and entering an ArchivedDate. I could query any device easily whether it is active or archived. (Please ignore the IsActive field, as this is used for an unrelated concept).

Take notice of the Phone subtype table, where I had to propagate an index of DeviceID and IsArchived flag because phone numbers must be unique for active devices. I have to do this with other subtype tables as well. I don't know if this is a good or bad design.

This part really confuses me...

What are best practices for handling soft deletes where foreign key values can be marked as deleted. The only thing I can think of is create a routine that searches for all records that are related to deleted data, and create a report for users to resolve the discrepancies. For example, if a table of locations is related to the devices table, and some locations are soft deleted, then the devices refer to locations that no longer exist and must be moved.

By the way, I'm using MS SQL Server 2008 R2, and I plan on using Entity Framework 4 in my application. I value database maintainability over performance.

Thank you for reading.

I would say that if your users are going to need to query the Archive data, then using the bit flag or soft delete is easier.  If the users don't need the data any longer, then I would go with the archive tables.

Based on your description above, I would suggest going with the Soft Delete version.  I can tell you from experience in one of our systems, we went with an archive schema to move older data to and it lead to nothing but issues because the users needed access to the data. So it lead to using UNION ALL on every query we had to run.

As a result of the issues, we stopped that route and moved to the soft delete, which is much easier.

We added a bit flag to all of the tables it was needed and then we just included this in the WHERE clause when querying the data.

A suggestion would be to make sure that this field has a default value when you INSERT data. If you are using IsArchived then the default value on the column would be false since you do not want it archived immediately.


Postgres or MapReduce to Match Reciprocal Needle Pairs from a Stack of Needles? I have a Postgres (latest version) table with about a million lines (a_lat, a_lon, b_lat, b_lon) that each have about a dozen criteria.

These lines go together in reciprocal pairs (A - B, B - A), which I need to determine. There may be multiple lines with similar A and B lat/lon, with similar but different criteria.

The criteria matching isn't exact, so many passes are done with less and less strict criteria (exact match on early rounds, +/- 1 one on later rounds, then +/- 1.5, etc). Matches in each pass are removed from subsequent passes.

I'm currently doing the matching in Postgres by looping over each row in the table and doing a query to find matches:

SELECT
    id
FROM
    locations
WHERE
    a_lat BETWEEN SYMMETRIC (38.5565 - 0.00055533333334) AND (38.5565 + 0.00055533333334) AND
    a_lon BETWEEN SYMMETRIC (-77.2797222222222 - 0.00055533333334) AND (-77.2797222222222 + 0.00055533333334) AND
    criteria_1 BETWEEN SYMMETRIC (42.7 - 1.5) AND (42.7 + 1.5) AND
    criteria_2 = 23 AND
    criteria_3 = 'ABCD' AND
    (criteria_4 = '0014980726' OR criteria_4 IS NULL) AND
    criteria_5 = 'A' AND
    criteria_6 = 1

If there is exactly one match then it's recorded and that row removed from future passes.

If you do the math its a ridiculous amount of queries (billions) and takes days to run. All fields have been optimized (floats and integers where possible) and indexed.

Is this type of problem suitable for a MapReduce type solution?

Can MapReduce handle multiple criteria, and criteria ranges (+/- 1)?  Can a performance gain be expected on the same hardware (384 GB RAM, 64 core Xeon), or is the performance increase only due to the ability of MapReduce solutions to scale to multiple machines?

Thanks!

I think it could be helpful for you to think of MapReduce as (essentially) a distributed query engine.  I know it isn't one-to-one, but with SELECT and aggregate functions such as sum(), max(), etc being very much like a Map() operation, and GROUP BY being very much like a Reduce(), there is quite a bit of similarity.

In your case, on the same hardware, I think the only benefit you will be able gain from using MapReduce is the distribution to multiple cores.  Remember that query processes in PostgreSQL only use a single core per query, so there is a lot of waste of your 64 cores if you're running this one query at a time.

Parallelization

Perhaps you'd be able to break up the query and run it over only segments of your locations table, and then run these queries in parallel using a connection pooler like pgBouncer?

For some good info about access data in sequential chunks (pagination), check out this amazing blog post by Markus Winand.

An example of how you might break this into chunks might be (warning:untested SQL ahead)

SELECT
id
FROM
(SELECT * FROM locations LIMIT x OFFSET y) AS locs_fragment
WHERE
a_lat BETWEEN SYMMETRIC (38.5565 - 0.00055533333334) AND (38.5565 + 0.00055533333334) AND
a_lon BETWEEN SYMMETRIC (-77.2797222222222 - 0.00055533333334) AND (-77.2797222222222 + 0.00055533333334) AND
criteria_1 BETWEEN SYMMETRIC (42.7 - 1.5) AND (42.7 + 1.5) AND
criteria_2 = 23 AND
criteria_3 = 'ABCD' AND
(criteria_4 = '0014980726' OR criteria_4 IS NULL) AND
criteria_5 = 'A' AND
criteria_6 = 1;

Note that the sub-select SELECT * FROM locations LIMIT x OFFSET y, given no additional criteria, will extract rows in ctid order. By selecting appropriate LIMIT and OFFSET values for a rnage of queries, you can essentially run the query in parallel.

Denormalization

Don't rule out denormalization as an option as well. In this case, it can help you to much more quickly get you the results you want. Of course, denormalization will only really help if your query criteria will be fairly stable over time, so that you can effectively pre-calculate results sets.

I'd say there are two decent options:

(1)  Use partial indexing, where a given index is designed around some set of criteria, as

CREATE INDEX idx_locations_criteria_set_0
ON locations (id)
WHERE
a_lat BETWEEN SYMMETRIC (38.5565 - 0.00055533333334) AND (38.5565 + 0.00055533333334) AND
a_lon BETWEEN SYMMETRIC (-77.2797222222222 - 0.00055533333334) AND (-77.2797222222222 + 0.00055533333334) AND
criteria_1 BETWEEN SYMMETRIC (42.7 - 1.5) AND (42.7 + 1.5) AND
criteria_2 = 23 AND
criteria_3 = 'ABCD' AND
(criteria_4 = '0014980726' OR criteria_4 IS NULL) AND
criteria_5 = 'A' AND
criteria_6 = 1;

With such an index defined, if you make a query with a matching predicate, an index scan is performed rather than having to perform all the calculations for the SELECT statement.

(2)  Another denormalization option is to use triggers during INSERT on your locations table to verify if certain conditions are met, and when they are met, store the id of that locations entry in another table, thus making a simple table scan all you need to do at a later time to find matching entries.

Both of these cases will require additional storage space, as well as some minor INSERT overhead for predicate checking, but it allows you to distribute your predicate checking over time, rather than being forced to do it all at once when a query is issued.

Conclusions

As with any advice on the internet, take mine with a grain of salt! :P  I've done some work in both of these areas, but that doesn't make me an expert in your application area. Run some small scale tests on either approach to see what works best for your application.


Remote MongoDB Dump Failure I hope you can help me. I am trying to make a data dump on a MongoDB remotely, but am getting an error.

I use the following command from my Windows CMD console to create the backup:

C:\Program Files\MongoDB\Server\3.0\bin\imports>mongodump --host 10.13.9.210 --port 29039 --out E:\Mongo\Data\Backup\Dump

It all seems to run and dump and write,

2015-11-13T15:56:30.159+0200    writing IOWA.VENDORITEM to E:\Mongo\Data\Backup\Dump\IOWA\VENDORITEM.bson
2015-11-13T15:56:30.162+0200    writing IOWA.CALLCLOSURECODE metadata to E:\Mongo\Data\Backup\Dump\IOWA\CALLCLOSURECODE.metadata.json
2015-11-13T15:56:30.171+0200    writing IOWA.PRICELISTITEM metadata to E:\Mongo\Data\Backup\Dump\IOWA\PRICELISTITEM.metadata.json
2015-11-13T15:56:30.193+0200    done dumping IOWA.CALLCLOSURECODE (0 documents)
2015-11-13T15:56:30.194+0200    writing IOWA.BULLETINBOARD to E:\Mongo\Data\Backup\Dump\IOWA\BULLETINBOARD.bson
2015-11-13T15:56:30.205+0200    done dumping IOWA.PRICELISTITEM (0 documents)
2015-11-13T15:56:30.205+0200    writing IOWA.BINLOCATION to E:\Mongo\Data\Backup\Dump\IOWA\BINLOCATION.bson

But then I get this ERROR:

2015-11-13T15:56:30.270+0200    Failed: error creating bson file `E:\Mongo\Data\Backup\Dump\IOWA1\db.getCollection('MASTER').distinct("_t").bson`: open E:\Mongo\Data\Backup\Dump\IOWA1\db.getCollection('MASTER').distinct("_t").bson: The filename, directory name, or volume label syntax is incorrect.

Any help would greatly be appreciated!

Even i had this issue and came to know its an bug. workaround for this would be dumping by passing the DB

mongodump -d "database_name"

try this may resolve your issue. Make sure you have enough space in the File system.


postgres performance tuning - what to try next? I have a simple (but large) postgres database.

I have a table defined like this….

CREATE TABLE TABLE_A (
  MessID      SERIAL UNIQUE,
  rawData     CHAR(250),
  loadedTime  TIMESTAMP)

And a process that continuously loads millions of records (each one a variable length ASCII string) into the table. There’s an index on loadedTime.
That’s it, no triggers, nothing fancy. All day records get loaded in, at night they get processed. The night-time processing only takes a few hours, and since I’ve got all night I’m not worried about the performance then.

What’s is of critical importance is the rate at which I can insert records during the day.

I’ve managed to get the time down from about 10ms/record to about 1.5m/s record. I’d like improve performance further (maybe 1 ms/record? Or quicker?)

The following changes have made improvements….

Partitioned my data by date/time range.
Kept ‘current’ data on an SSD tablespace, and ‘archive’ data on a magnetic drive
Tweaked both the number of records added in a single INSERT statement, and the number of INSERTs in each transaction

I’ve also tried tweaking postgres.conf using pgtune.leopard.in.ua. To be honest, none of the changes to this file have made any noticeable difference. The current settings are…

# DB Version: 10
# OS Type: windows
# DB Type: oltp
# Total Memory (RAM): 16 GB
# CPUs num: 1
# Connections num: 10
# Data Storage: ssd

max_connections = 10
shared_buffers = 512MB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 541764kB
min_wal_size = 2GB
max_wal_size = 4GB

My questions are…

Why haven’t the hardware and config changes made any noticeable difference?
Are there any other areas for performance tuning I should be looking at?
If I were to upgrade the server the database is on, what factors should I look for in new one – bearing in mind that increasing the memory doesn’t seem to make the slightest bit of difference.

Other information that may be relevant.

I’m only interested in the speed at which records get inserted during the day.
During daytime operations there is a single connection to the database (doing the INSERTs) and no other processing.
The existing server is a 16 GByte Windows pro single processor with Postgres 10 installed on an SSD and archive tablespaces on a magnetic drive.
TABLE_A is partitioned by day. Todays data is held on a tablespace on the SSD, at night it’s archived to tablespace on magnetic disc
Incoming records are added using a powershell script. The INSERT statement is wrapped in a stopwatch function which allows me to accurately record how long each INSERT takes and work out the time per record.
Incoming records are arriving one-by-one from an external server, over which I have no control, so using COPY wouldn't help.
Spooling the records to a temporary file prior to loading hasn't worked either since it seems to take longer to append to a file than it does to insert into a database.
It currently takes 1.5ms to insert a record, so the system can cope with a maximum of between 5 and 700 hundred records/sec. The problem is that the source can, at busy periods, generate 800-900 records per second.

UPDATE in response to horse_with_no_name. Is it number of processors, or number of cores that effect the number of simultaneous INSERTS that Postgres can cope with? My processor is (I quote) "Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz, 2601 Mhz, 4 Core(s), 8 Logical Processor(s)"

Having 4 Core(s), does that mean I ought to be able support 4 simultaneous processes doing INSERTs at the same time? Could I re-write my powershell script to round-robin the incoming records across 2 (or more) connections and improve INSERT performance that way?

I think some of these suggestions may help, esp. using COPY instead of INSERT.

https://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql


Adding Column value from Previous Row based on Where Condition. I have a table with symbol, epoch and Volume. The epoch is stored based on Symbol and Epoch in ascending order.

I want to get cumulative volume which is sum of volume and prev timestamp and the volume at current timestamp for the same day and for same symbol.

I am new to SQL and this already seems to be quite complicated. Pls help.

Try using over clause.
Over Clause Documentation

Select Symbol,
       Epoch,
       Volume,
       Sum(Volume)Over(Partition by Symbol,Substring(Epoch,1,6) Order by Epoch) AS CuVol
From YourTableName


Unexpected Second Instance of SQL Started We had an occurrence of our main production server C:\ filling unexpectedly, and our support guy fixed it by fixing the size of the page file and rebooting.  I'm trying to diagnose why this grew from almost no usage to 100gb over the space of a couple of days.

I'm currently going back through logs, and a Spotlight server we run over it - and I noticed that the server had a second instance of sqlservr.exe started.

Our main instance runs "c:...\sqlservr.exe" -sINSTANCE as a service MSSQL$INSTANCE.

The second instance showing in the task listing shows "c:...\sqlservr.exe" -sINSTANCE, but not as a service.

I can't imagine why a second instance would have started - could it be an agent job, such as our Ola Hallengren scripts working externally, but reporting in our task manager as a second instance?

The thing here is that this is one artifact I'm seeing to diagnose the problem, but in itself it isn't reporting major memory usage - I am seeing regular pagefaults, pagefile growth, etc, starting when this appears however.

This is a curveball that I wasn't expecting and I'd appreciate any help, ideas, thoughts, or other diagnosis points you think I should be looking for.

SQL Server 2014, working on a Windows Cluster.

It turns out - when reviewing all the logging that I could, that the Windows Event Log has a crash of SQL recorded - however, SQL itself has no crash in its logs, and the log wasn't closed and a new one created as you'd expect when SQL restarts.

In our monitoring, the second instance never grows beyond 2gb of RAM - and the RAM usage itself never grows, but the pagefile grew at 11gb per day.

I can't see the cause of the crash, and I'd probably have to open a Microsoft support ticket to see why it crashed, created a new instance, but never closed the existing instance - never released it from being the instance as the service, and never died or took over that I can see.

We can see some timeouts for a minute from an API that talks to the server, but no "unavailable" messages.

It seems like the crash is the underlying cause - but that's as far as I can determine for now, and it seems unusual to me.

None of our monitoring, or our consultants monitoring picked up the crash.


JOIN Call table to grouping by Origination and Destination It's a call system.

  tables structure and data sample :

tbl_countries

CREATE TABLE tbl_countries
(
    country_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    country varchar(100) NOT NULL
);
INSERT INTO tbl_countries (country) VALUES ('Austria');
INSERT INTO tbl_countries (country) VALUES ('Brazil');
INSERT INTO tbl_countries (country) VALUES ('Denmark');
INSERT INTO tbl_countries (country) VALUES ('France');
INSERT INTO tbl_countries (country) VALUES ('Lebanon');

tbl_destination_clients

CREATE TABLE tbl_destination_clients
(
    des_client_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    des_client varchar(100) NOT NULL,
    country_id int(11) NOT NULL,
    CONSTRAINT tbl_destination_clients_ibfk_1 FOREIGN KEY (country_id) REFERENCES tbl_countries (country_id)
);
CREATE INDEX country_id ON tbl_destination_clients (country_id);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('961123456', 5);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('55123456', 2);
INSERT INTO tbl_destination_clients (des_client, country_id) VALUES ('45123456', 3);

tbl_origination_clients

CREATE TABLE tbl_origination_clients
(
    orig_client_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    orig_client varchar(100) NOT NULL,
    country_id int(11) NOT NULL,
    CONSTRAINT tbl_origination_clients_ibfk_1 FOREIGN KEY (country_id) REFERENCES tbl_countries (country_id)
);
CREATE INDEX country_id ON tbl_origination_clients (country_id);
INSERT INTO tbl_origination_clients (orig_client, country_id) VALUES ('33123456', 4);
INSERT INTO tbl_origination_clients (orig_client, country_id) VALUES ('43123456', 1);

tbl_calls

CREATE TABLE tbl_calls
(
    call_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    orig_client_id int(11) NOT NULL,
    des_client_id int(11) NOT NULL,
    call_duration int(11) NOT NULL,
    CONSTRAINT tbl_calls_ibfk_1 FOREIGN KEY (orig_client_id) REFERENCES tbl_origination_clients (orig_client_id),
    CONSTRAINT tbl_calls_ibfk_2 FOREIGN KEY (des_client_id) REFERENCES tbl_destination_clients (des_client_id)
);
CREATE INDEX des_client_id ON tbl_calls (des_client_id);
CREATE INDEX orig_client_id ON tbl_calls (orig_client_id);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 3, 5);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 3, 6);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (2, 4, 9);
INSERT INTO tbl_calls (orig_client_id, des_client_id, call_duration) VALUES (1, 1, 10);

 Attemps :

I try to use JOIN between the same table tbl_calls, like this :

select
  co.country Origination,
  cd.country Destination,
  sum(c.call_duration) TotalDuration
from tbl_calls c
inner join tbl_destination_clients d on d.des_client_id = c.des_client_id
inner join tbl_origination_clients o on o.orig_client_id = c.des_client_id
inner join tbl_countries cd on cd.country_id = d.country_id
inner join tbl_countries co on co.country_id = o.country_id
group by
  co.country,
  cd.country;

But it's not working ;(

The Result of this sample data must be :

Origination  Destination  SUM(call_duration)
------------+------------+----------------
France       Brazil       11 -> (5+6)
Austria      Denmark      9
France       Lebanon      10

Thanks ..

I think you need to change one join column on the inner join from tbl_calls to  tbl_origination_clients. You have currently c.des_client_id but it should be c.orig_client_id.

Query with adapted join column

select
  co.country Origination,
  cd.country Destination,
  sum(c.call_duration) TotalDuration
from tbl_calls c
inner join tbl_destination_clients d on d.des_client_id = c.des_client_id
inner join tbl_origination_clients o on o.orig_client_id = c.orig_client_id
inner join tbl_countries cd on cd.country_id = d.country_id
inner join tbl_countries co on co.country_id = o.country_id
group by
  co.country,
  cd.country;

Result

| Origination | Destination | TotalDuration |
| ----------- | ----------- | ------------- |
| Austria     | Denmark     | 9             |
| France      | Brazil      | 11            |
| France      | Lebanon     | 10            |

DB-Fiddle