Extend Query to include additional column I have the following query, which gives me (in addition to other columns) the Direction ID only:

SELECT Users.Name, Users.Surname, Transactions.Timestamp, AccessPoints.Description as Door, Readers.Direction
    FROM (Readers INNER JOIN (AccessPoints INNER JOIN Points_Readers ON AccessPoints.PointID = Points_Readers.PointID)
    ON Readers.ReaderID = Points_Readers.ReaderID) INNER JOIN (Transactions INNER JOIN (AccessNumbers INNER JOIN Users
    ON AccessNumbers.AccessNumberID = Users.AccessNumberID) ON Transactions.Number = AccessNumbers.Number)
    ON Readers.ReaderID = Transactions.ReaderID
    WHERE (Transactions.Timestamp>#2018-08-15  06:50:16#) ORDER BY Transactions.Timestamp;

What I want to do is extend the query to include Directions.DirectionsText by joining Readers.Direction to Directions ON Directions.Direction. I'm having trouble with exactly where to place the join so that I don't keep getting errors in the FROM clause. (I still haven't figured out how to get INNER JOIN clauses working with Access' need to use parentheses for joins.)

The columns in Directions are Direction (numeric) and DirectionText (String)

I'm having trouble with exactly where to place the join

Directions is joined to Readers - so you must place it adjacent to this table enclosind this pair in additional parenthesis:

SELECT Users.Name,
       Users.Surname,
       Transactions.Timestamp,
       AccessPoints.Description as Door,
       Readers.Direction,
       Directions.DirectionsText
FROM ( ( Readers
         INNER JOIN
         Directions ON Readers.Direction = Directions.Direction
       )
       INNER JOIN
       ( AccessPoints
         INNER JOIN
         Points_Readers ON AccessPoints.PointID = Points_Readers.PointID
       ) ON Readers.ReaderID = Points_Readers.ReaderID
     )
     INNER JOIN
     ( Transactions
       INNER JOIN
       ( AccessNumbers
         INNER JOIN
         Users ON AccessNumbers.AccessNumberID = Users.AccessNumberID
       ) ON Transactions.Number = AccessNumbers.Number
     ) ON Readers.ReaderID = Transactions.ReaderID
WHERE (Transactions.Timestamp>#2018-08-15  06:50:16#)
ORDER BY Transactions.Timestamp;

  that I don't keep getting errors in the FROM clause

Build the query text using Query Builder - it always creates correct code or produce error message if there are any logical problems.

Or (if you want write query text "by hands" without using the master) rewrite the query to cartesian form (with comma-separated tables list and all joining conditions in WHERE clause) - it do not need the accuracy in parenthessis:

SELECT Users.Name,
       Users.Surname,
       Transactions.Timestamp,
       AccessPoints.Description as Door,
       Readers.Direction,
       Directions.DirectionsText
FROM Readers,
     AccessPoints,
     Points_Readers,
     Transactions,
     AccessNumbers,
     Users,
     Directions
WHERE AccessPoints.PointID = Points_Readers.PointID
  AND Readers.ReaderID = Points_Readers.ReaderID
  AND AccessNumbers.AccessNumberID = Users.AccessNumberID
  AND Transactions.Number = AccessNumbers.Number
  AND Readers.ReaderID = Transactions.ReaderID
  AND Readers.Direction = Directions.Direction
  AND Transactions.Timestamp>#2018-08-15 06:50:16#
ORDER BY Transactions.Timestamp;

The order of either tables in FROM clause or conditions in WHERE clause does not matter in those form.


Change LAST_NUMBER value in a sequence (NEXTVAL) My question seems easy but i haven't found a solution.
I'm using the nexval sequence in a insert SQL script but we have errors because the LAST_NUMBER value haven't been updated and its value is not correct with tabla data. For this case for example the value is 5 and the table has 62 rows.

I've searched on google but don't found the command or the way yo change the LAST_NUMBER parameter of the sequence.

ALTER SEQUENCE rs2qnes0 START WITH 62;

or ALTER SEQUENCE rs2qnes0 LAST_NUMBER 62; don't work

Change increment by, select next value, set increment back to original value:

SQL> create sequence s1 start with 5;

Sequence created.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
          5

SQL> alter sequence s1 increment by 57;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
        61

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
         62

Or use RESTART:

SQL> alter sequence s1 restart start with 77;

Sequence altered.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
         77


Reasons against having "concrete" values in own table with only an ID column and the value itself, and composing bigger objects of IDs This is a follow up question to How to insert values into a highly normalized database without excessive duplicate checking?

The current situation:
We have an "massive" object that has around 30 columns worth of details (while it could be slightly improved, a lot of it sadly does really belong together, and can't easily be semantically separated). Every column simply contains an Integer, an ID key.
Now we have 30 more tables, each only have 2 columns: "id" and "value", where ID is an auto generated key, and value is the actual concrete value.

Whenever we want to get back data from our massive objects we need to do 30 joins from these "value" tables onto the main table.

Reasons given were "that a lot of data is going to be duplicated over the next 2-3 years" (it'd be a lucky stroke if one value gets used more than thrice, and at most 20 times imho, but that's besides the point), and any future changes to those values would be much easier then. Also saving storage space (which might be true, but for perhaps at most 200k records over 2 years nothing that should be noteworthy).

Now I know intuitively a few reasons why this design is bad. But I'm still a novice myself, and I would like proper detailed reasons that I could actually present to change a mind, and perhaps learn something new myself.

Friendly reminder that the database commands we're talking about here are mostly very basic Insert, Select and Update (and very rarely joins) methods. No one in our department is experienced enough in SQL (or willing enough to learn except me) to go above those commands, and almost all logic is done server-side after getting data from the database.

OK, I won't rant about what is 'wrong' in your schema.  I will provide an efficient way to batch the normalization for a batch insert.

I will point out two common flaws when "over-normalizing":

Don't do it if the id is bigger, on average, than the data.  Note: INT is 4 bytes; BIGINT is 8.
Don't normalize "continuous" values -- anything numeric or date-related.  It makes it very costly to do a range test.

OTOH, 100K rows/year is rather small.  If they come in evenly it is a rate of about 5 inserts (plus 30 normalizations) per hour.

If they come in batches, see http://mysql.rjweb.org/doc.php/staging_table#normalization

One thing of note:  Things like INSERT IGNORE will "burn" ids.  That is, an AUTO_INCREMENT id will be bumped even if it is not needed.  The code in that link avoids such.


I need to log activity of the sa account I need to log the activity of the 'sa' database account via Microsoft SQL Server 2012 so I can review the activity periodically and look for strange behaviour for audit purposes.

Unfortunately I'm completely new to SQL Server and searching Google and Stack Exchange turned up blank. Maybe I'm just looking for the wrong thing. Can anyone assist?

If this sort of logging is enabled, will the log have a tendency to spiral out of control?

Your immediate answer predominantly resides here, the official SQL Server Audit documentation. The larger discussions of performance, requirements, actual security issues addressed, threats mitigated, and what-the-hell-is-wrong-with-your-auditors has been alluded to in the comments and isn't a great candidate for Stack Exchange.

For your scenario you could do something like this:

1) Create the Server Audit

USE [master]

GO

CREATE SERVER AUDIT [Audit-GeneralServerAudit]
TO FILE
(   FILEPATH = N'\\NetworkStore\AuditLogs'
    ,MAXSIZE = 1024 MB
    ,MAX_FILES = 70
    ,RESERVE_DISK_SPACE = ON
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = SHUTDOWN
)
GO

ALTER SERVER AUDIT [Audit-GeneralServerAudit]
WITH (STATE = ON);

2) Define the Server Audit (modify events as needed)

CREATE SERVER AUDIT SPECIFICATION [ServerName - Audit]
FOR SERVER AUDIT [Audit-GeneralServerAudit]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD(DBCC_GROUP)
WITH (STATE = ON)
GO

3) Create Database Audit(s) as needed. Create additional Server Audits to separate log files for easy use/permissions restrictions and to implement different retention/storage strategies

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]
FOR SERVER AUDIT [Audit-DB Specific Audit]
ADD (DELETE ON DATABASE::[DBName] BY [public]),
ADD (SELECT ON DATABASE::[DBName] BY [public]),
ADD (UPDATE ON DATABASE::[DBName] BY [public])
WITH (STATE = ON)
GO

SQL Server automatically controls log rollover and space as per your file size and number of file definitions. Or the Windows Log settings control this if you elect for that design. Still a yes though - it absolutely can get out of control in terms of log size and over-writes (loss of audit data) if you don't setup the correct capacities.

If you're auditing a single user, why not audit other users and issues? You know, in-case someone else or another account is performing unwanted behavior.

Even basic log files are probably massive... as stated in comments, auditors need a clear definition of "unusual activity" or a clear methodology to automate finding it.

Intrusion Detection is unlikely to be caught by undefined anomaly detection. An actual audit strategy needs more than just the log files. I haven't seen much in the way of pre-built solutions for SQL Audit log files, but there are a lot of web, network, server log file solutions out there... maybe one is adaptable. Some example reports and setup guides here to get started on very basic reporting.


Perform TABLESAMPLE with WHERE clause in PostgreSQL I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL.

This runs fine:

select * from customers tablesample bernoulli (1);

But I cannot figure out how to embed the condition in the script. This for example

select * from customers where last_name = 'powell' tablesample bernoulli (1);

throws this error:

  SQL Error [42601]: ERROR: syntax error at or near "tablesample"
  Position: 71

tablesample is an "attribute" of a table, not a query. So you need to write it write after the table name:

select *
from customers tablesample system (1)
where last_name = 'powell';

Note that the where clause will be applied after the table has been sampled. It will not return 1% of all customers that have the last name 'powell'. But instead it will apply the filter on the sampled table.


Error 41145 after joining database to availability group I keep getting from my SQL monitoring service the following alert:

Error: 41145, Severity: 16, State: 1. Cannot join database to
availability group. The database has already joined the
availability group. This is an informational message. No user action
is required.

This error is costantly coming approximately every 2 hours but I can't understand why.
Also, in the SQL error log, I have thousands of this log entry that I've never noticed on any of my other SQL Server installations:

This is on a new SQL Server installation that will become my production environment, so I want to make sure that there's nothing wrong with it before we perform the switch of the servers.

I've seen that recurring error about joining the database when automatic seeding was used to initialize the secondary replicas.  My solution has always been to disable automatic seeding once the secondary databases have all been created and synchronized:
ALTER AVAILABILITY GROUP YourAgName
MODIFY REPLICA ON 'YourReplicaName'
WITH (SEEDING_MODE = MANUAL);

Those information messages are normal, but I haven't ever seen thousands of them in a row.  My experience is that you'll see several of those messages per database per replica when the AG connection to that replica is established.
I just started up some VMs in my home lab environment that have:

1 AG
3 databases in the AG
3 replicas total (primary, sync secondary, async secondary)

I ended up with 90 of those "DbMgrPartnerCommitPolicy" messages (10 messages * 3 databases * 3 replicas).


Running multiple instances on the same host I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with:

mysqld_multi
MySQL Sandbox

This should be a long term solution for an enterprise class production environment.

We need to migrate an already existing environment which currently uses the RedHat Cluster with active/passive nodes to a new VM without the clustering software (VMware HA solutions will be used, it will be different, I know).

There are 3 separate MySQL instance/cluster resources. I don't know the original motivation for this separation, but I suppose that the customer wants to be able to stop/start (patch, upgrade ...) the different resources autonomously.

Any comments and suggestions - pros, cons and real world experience are very welcome!

To answer your immediate question on how to set it up, I and Randy Melder addressed this question May 31, 2011.

DTest and Laurynas Biveinis answered this more recently (Sep 20, 2011).

Some swear by its usage these days.

Years ago, mysql used to provide a script called safe_mysqld which made it simple to have multiple instances of mysql. All you did was create a my.cnf for the what instance on whatever port you wanted. Then called:

safe_mysqld custom_my.cnf

MySQL stopped distributing safe_mysqld in favor of mysqld_multi.

However, I have learned how to use mysqld_safe. Believe it or not, because of understanding mysqld_safe, I actually wrote my own mysql multi-instance engine back in Feb 2011. It is in production use right now with many of my employer's clients. Here is how I did it:

First, make this service engine called /etc/init.d/mysqlservice

#!/bin/bash

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

WHICH=/usr/bin/which
ECHO=`${WHICH} echo`
GREP=`${WHICH} grep`

#
#  Check for a four digit number greater than 3306
#  Make sure the my.cnf for the Port Number Exists
#
P1=${1}
if [ "${P1}" == "" ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^[Hh][Ee][Ll][Pp]$"`
if [ ${X} -eq 1 ]
then
  ${ECHO} "Usage: service mysqlservice <port-number [3307-3399]> {start|stop|restart|status|mycnf}"
  exit
fi
X=`${ECHO} "${P1}" | ${GREP} -c "^33[0-9][0-9]$"`
if [ ${X} -eq 0 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYSQLD_PORT=${P1}
if [ ${MYSQLD_PORT} -lt 3307 ]
then
  ${ECHO} "Please Specify a Port Number for MySQL (3307 - 3399)"
  exit
fi
MYCNF=/etc/my${MYSQLD_PORT}.cnf
if [ ! -f ${MYCNF}  ]
then
  ${ECHO} "${MYCNF} Does Not Exist"
  exit
fi
MYCNF_BACKUP=${MYCNF}_backup

NOHUP=`${WHICH} nohup`
SLEEP=`${WHICH} sleep`
TAIL=`${WHICH} tail`
AWK=`${WHICH} awk`
CAT=`${WHICH} cat`
RM=`${WHICH} rm | ${TAIL} -1 | ${AWK} '{print $1}'`
MYSQLD_SAFE=`${WHICH} mysqld_safe`
MYSQLADMIN=`${WHICH} mysqladmin`
MYSQL=`${WHICH} mysql`
MYSQL_CONN="-uroot -p<rootpassword> -P${MYSQLD_PORT} -h127.0.0.1"
MYSQL_PING_FILE=/tmp/MySQL${MYSQLD_PORT}Ping.txt
MYSQL_STAT_FILE=/tmp/MySQL${MYSQLD_PORT}Status.txt
MYSQL_ERROR_LOG=/var/log/mysqld${MYSQLD_PORT}.log
MYSQLD_START="${MYSQLD_SAFE} --defaults-file=${MYCNF} --port=${MYSQLD_PORT}"
MYSQLD_STOP="${MYSQLADMIN} ${MYSQL_CONN} shutdown"
MYSQLD_PING="${MYSQLADMIN} ${MYSQL_CONN} ping"
PROGNAME="MySQL (Port ${MYSQLD_PORT})"

#
#       This service will use mysqld_safe to run mysql server instances on other ports
#

start() {
  cd /tmp
  ${ECHO} -n $"Starting ${PROGNAME}"
  ${NOHUP} ${MYSQLD_START} 2>/dev/null 1>/dev/null &
  ATTEMPTS=0
  STARTING_MYSQLD=1
  MINUTES_TO_TRY=3
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STARTING_MYSQLD} -eq 1 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    READY_FOR_CONNECTIONS=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "ready for connections\.$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STARTING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STARTING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STARTING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Started ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

stop() {
  ${ECHO} -n $"Stopping ${PROGNAME}"
  ${MYSQLD_STOP}
  ATTEMPTS=0
  STOPPING_MYSQLD=1
  MINUTES_TO_TRY=10
  (( TICKS_TO_TRY = MINUTES_TO_TRY*240 ))
  while [ ${STOPPING_MYSQLD} -eq 0 ]
  do
    ${ECHO} -n "."
    ${SLEEP} 0.25
    MYSQL_HAS_BEEN_SHUTDOWN=`${TAIL} ${MYSQL_ERROR_LOG} | ${GREP} -c "Shutdown complete$"`
    (( ATTEMPTS++ ))
    if [ ${ATTEMPTS} -eq ${TICKS_TO_TRY} ] ; then STOPPING_MYSQLD=0 ; fi
    if [ ${READY_FOR_CONNECTIONS}  -eq 1 ] ; then STOPPING_MYSQLD=2 ; fi
  done
  ${ECHO}
  if [ ${STOPPING_MYSQLD} -eq 2 ]
  then
    ${ECHO} "Stopped ${PROGNAME}"
  else
    ${TAIL} -30 ${MYSQL_ERROR_LOG}
  fi
}

# See how we were called.

${MYSQLD_PING} 2>/dev/null > ${MYSQL_PING_FILE}
MYSQL_RUNNING=`grep -c "mysqld is alive" ${MYSQL_PING_FILE}`
${RM} ${MYSQL_PING_FILE}

case "$2" in
  start)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      start
    else
      ${ECHO} ${PROGNAME} is Running
    fi
    ;;
  stop)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      ${ECHO} ${PROGNAME} is Not Running
    else
      stop
    fi
    ;;
  restart)
    stop
    ${SLEEP} 1
    start
    ;;
  status)
    if [ ${MYSQL_RUNNING} -eq 0 ]
    then
      ${ECHO} ${PROGNAME} is Not Running
    else
      ${MYSQL} ${MYSQL_CONN} -A -e"status" > ${MYSQL_PING_FILE}
      ${GREP} "Server version:" < ${MYSQL_PING_FILE}  > ${MYSQL_STAT_FILE}
      ${GREP} "UNIX socket:"    < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${GREP} "Uptime:"   < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${GREP} "Threads:"  < ${MYSQL_PING_FILE} >> ${MYSQL_STAT_FILE}
      ${CAT} ${MYSQL_STAT_FILE}
    fi
    ;;
  mycnf)
    less ${MYCNF}
    ;;
  edit)
    cp ${MYCNF} ${MYCNF_BACKUP}
    vi ${MYCNF}
    ;;
  *)
    ${ECHO} $"Usage: $0 {start|stop|restart|status|mycnf}"
    ${ECHO} $"Usage: $0 help"
    exit 1
esac

exit

Next, create mysql instance file for a specfic port.

For example, here is the service mysql3307

#!/bin/sh
#
# readahead:    Prereads programs required for startup into memory
#
# chkconfig: 2345 4 99
# description:  This service causes the programs used during startup \
#               to be loaded into memory before they are needed,\
#               thus improving startup performance
#
#

# Sanity checks.
[ -x /usr/sbin/readahead ] || exit 0

# Check for > 384 MB
#free -m | gawk '/Mem:/ {exit ($2 >= 384)?0:1}' || exit 0

# Source function library.
#. /etc/rc.d/init.d/functions

WHICH=/usr/bin/which
SERVICE=`${WHICH} service`

MYSQL=`${WHICH} mysql`
PORT_NUMBER=3307

# See how we were called.
case "$1" in
    start)      ${SERVICE} mysqlservice ${PORT_NUMBER} start    ;;
    stop)       ${SERVICE} mysqlservice ${PORT_NUMBER} stop     ;;
    status)     ${SERVICE} mysqlservice ${PORT_NUMBER} status   ;;
    restart)    ${SERVICE} mysqlservice ${PORT_NUMBER} restart  ;;
    mycnf)      ${SERVICE} mysqlservice ${PORT_NUMBER} mycnf    ;;
    edit)       ${SERVICE} mysqlservice ${PORT_NUMBER} edit     ;;
    *)
        echo $"Usage: $0 {start|stop|restart|status|mycnf}"
        ;;
esac

Make sure that the mysql instance file has the port number defined in PORT_NUMBER between 3307 and 3399.

As mysql instance file calls mysqlservice, please note that mysqlservice will check for the config file for the specfic port.

In the case of mysql3307, mysqlservice will look for /etc/my3307.cnf.

Make sure you have the following explicitly defined in /etc/my3307.cnf

[mysqld]
datadir=/var/lib/mysql3307
socket=/var/lib/mysql3307/mysql.sock
port=3307

[mysqld_safe]
log-error=/var/log/mysqld3307.log

The datadir and socket can be at places of your choosing.

To create mysql3308 and other services, following the same paradigm but use the exactly port number defined throughout.

Give it a Try !!!


Specify a different username for default Postgres user I have the same issue as:
Specifying a username while running initdb causes error in log on start of PostgreSQL process

but I am working on setting up PostgreSQL on Ubuntu 17.04 instead of CentOS. I've read that calling initdb directly in this environment is discouraged because the suite of tools packaged with the software such as pg_createcluster, etc. What differences should I consider if I try initializing with a user other than the default postgres user?

I've read that calling initdb directly in this environment is
  discouraged because the suite of tools packaged with the software such
  as pg_createcluster

Yes, but pg_createcluster can pass through options to initdb, so it makes no difference on the configurability.

From the manpage:

  SYNOPSIS
        pg_createcluster [options] version name [-- initdb options]

Example:

# pg_createcluster 9.5 test -- --username=daniel
Creating new cluster 9.5/test ...
  config /etc/postgresql/9.5/test
  data   /var/lib/postgresql/9.5/test
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433

# pg_ctlcluster 9.5 test start
Redirecting start request to systemctl

# su daniel -c 'psql --cluster 9.5/test -l'
                               List of databases
   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
-----------+--------+----------+-------------+-------------+-------------------
 postgres  | daniel | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | daniel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/daniel        +
           |        |          |             |             | daniel=CTc/daniel
 template1 | daniel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/daniel        +
           |        |          |             |             | daniel=CTc/daniel
(3 rows)

# pg_dropcluster --stop 9.5 test
Redirecting stop request to systemctl


Clarification -- Disabling SQL Server 2014 Agent Job in AOAG Secondary replicas We have AGs set up (1 primary and 2 secondaries (Includes DR)).

We have an archival job set up in each replica with same schedule and enabled in all three replicas.

This job runs in a frequency of about 15 minutes each day.

My question is:

Since the job in each replica is pointed to primary server while running :

Can I let the job run only in Primary Replica and disable the Job in secondary replicas ? Will it affect otherwise ?
In AlwaysOn AGs will the jobs also be failed over ?
If jobs not failover

My thinking is once the primary is failed over to other node (which is would be primary or current secondary synchronized) and since the secondary replica is now primary and the job will be in disabled state (If at all we can disable the jobs in secondary ?), I just need to enable it on the current replica.

Kindly correct my thinking ?

Regards

just need to enable it on the current replica.

You will need to modify you sql agent jobs to include below step

CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN
DECLARE @PrimaryReplica sysname;
SELECT @PrimaryReplica = hags.primary_replica
FROM
sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE
ag.name = @AGName;

IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
RETURN 1; -- primary

RETURN 0; -- not primary
  END;

GO

So you have to create the above function in all your replicas  and add as first step in sql agent jobs you wish to execute

If the replica is not primary,you can modify the job to Quit

This has been covered in depth here :
AlwaysOn Availability Groups and SQL Server Jobs, Part 7: Detecting Primary Replica Ownership


Why is our SQL Server instance writing a mystery text file to our C: drive? On a dev-test WINTEL server, our C: drive has almost run out of disk space due to a SQL.LOG file that is 133 GB in size. I used SysInternals Handle.exe tool to determine which process was writing this SQL.LOG file and found that the culprit is sqlservr.exe.

As this SQL.LOG file does not appear to be any of the standard SQL Server log/error log files (they are all configured to write elsewhere), my question is: how can I query this SQL Server 2012 Enterprise instance to determine which job, stored procedure, assembly, etc is writing to this mysterious C:\SQL.LOG file..?

Note: the file itself doesn't contain any clues. It is too large to open in Wordpad but if I query for the first few lines in Powershell, nothing helpful appears...

So I need to find a way of asking SQL Server which bit of itself is writing this C:\SQL.Log file. I guess I need a way of searching within SQL Server for either configuration information (somewhere this filename and path must be set) or for details of file output operations (other than IO operations on database data/log files). Can anyone help me with drafting such a T-SQL query?

Thank to the comment from Martin Smith above, I was able to determine that the C:\SQL.LOG file was being created by ODBC Tracing that had been started and forgotten by a developer.


How do I safely upgrade xampp without losing database i have created? Also when i try to upgrade the with older version being installed, i get this error that port is being used or similar sorts. Do I complete installation by uninstalling?

I am searching for complete answer just to be sure about upgrading.

First thing is to listen to the doctors' motto - "Do no harm".

Take a backup of your database before doing anything else - that way, if you make a mistake, you can always retrace your steps and you won't have lost any important (or even unimportant) data.


Check if property exists in an object inside array in JSONB field in Postgres Here is my table data:

Corresponding sqlfiddle - http://sqlfiddle.com/#!17/e3f09

create table jsonData (
    id serial primary key,
    data jsonb
);

insert into jsonData (data) values (

$${
  "b": 2,
  "c": [
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true,
        "f": "I'm here"
      }
    }
  ]
}
$$::jsonb),
(
$${
  "a": 1,
  "c": [
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true
      }
    },
    {
      "d": {
        "e": true
      }
    }
  ]
}$$::jsonb);

The question is - how do I select all records, where "f" property (path is the same everywhere - 'c -> d -> f') exists?

Use an EXISTS in which you use jsonb_array_elements() to get the arrays' elements and check ->'d'->'f' for NULL.

SELECT *
       FROM jsonData
       WHERE EXISTS (SELECT *
                            FROM jsonb_array_elements(data->'c')
                            WHERE value->'d'->'f' IS NOT NULL);

SQL Fiddle


Deadlock on inserts to a single table with a single key. (Mysql) I have a simple game server.
One of the requirement i received was to handle a single request for a single player at a time. so in order to achieve that i decided to create a lock table:

CREATE TABLE `GameRoundLock` (`theKey` varchar(255) NOT NULL,
PRIMARY KEY (`theKey`))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Before I handle any request from any player I am inserting a row into that table like so:

// save and flush == acquiring the lock
GameRoundLock lock = this.gameRoundLockRepository.saveAndFlush(somePlayerKey);

   /** Play busniess logic here...  **/

// deleting == releasing the lock
this.gameRoundLockRepository.delete(lock);

This actually works.

In the following scenario the whole system works as expected:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request... (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) thread of 2) completes

5) thread of 3) now continuing

6) DONE, playerA total played rounds == 2.

****Pay attention that the id (playerKey) is identical.****

The problem arises when the playerA tries another play request while the handling of the first thread still occurs. here is the scenario that resulting in a weird deadlock:

1) PlayerA plays, therefore a new lock is inserted.

2) handling playerA request... (takes 30 seconds)

3) While 2) is still executing, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2) completes.

4) While 2) is still executing and 3) is still waiting, PlayerA plays again - This ofcourse resulting in trying to create a lock for him, and the thread now awaits till 2)/3) completes.

5) thread of 2) completes

6) ERROR - a deadlock is thrown!!!!!

Here is the deadlock info:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-11-23 17:05:26 0x1d98
*** (1) TRANSACTION:
TRANSACTION 5493356, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5191, OS thread handle 5152, query id 15582091 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table      `game_server`.`gameroundlock` trx id 5493356 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) TRANSACTION:
TRANSACTION 5493355, ACTIVE 12 sec inserting, thread declared inside InnoDB    1
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5193, OS thread handle 7576, query id 15582083 localhost    127.0.0.1 root update
insert into GameRoundLock (theKey) values ('1,1,1,64')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table     `game_server`.`gameroundlock` trx id 5493355 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info     bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4865 page no 3 n bits 72 index PRIMARY of table    `game_server`.`gameroundlock` trx id 5493355 lock_mode X locks rec but not gap    waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info    bits 32
 0: len 8; hex 312c312c312c3634; asc 1,1,1,64;;
 1: len 6; hex 00000053d266; asc    S f;;
 2: len 7; hex 790000030903d1; asc y      ;;

*** WE ROLL BACK TRANSACTION (2)

It seems like two "close\near" inserts for the locks of the 2nd and 3rd play requests causes this. why a deadlock and how to mitigate it?

I guess you have following issue.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.
http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html


SSMS 2008 / 2014 showing different values for TempDB file sizes Here's a fun one.  Last week I reconfigured the initial file sizes in our tempdb database to 1GB each.  We had an event a few weeks back that locked up our server due to a table with no indexes getting absolutely hammered.  This caused our 3 of our tempdb files to grow.  I re-sized them using SSMS 2014 and they should have reset to their configured size this past Sunday when we rebooted the server.  They didn't and now I'm seeing a difference in the information SSMS is returning for those files.

Our server is 2008 R2, here are the different SSMS views:

2008:

2014:

Here are the files as they currently sit on the server itself:

It seems that SSMS 2014 is returning the correct information, but SSMS 2008 is not.  Is this a known issue that anyone has seen before?  If so, what's causing it?  Does 2014 attempt to store this information differently than 2008 R2 and negate the 2008 configured values?

Now, there were no auto-grow events that happened once the server was restarted (checked the default  trace and there were no events)  Given this information, what do you think my best course of action is to get these files re-sized appropriately.  I'm totally fine with growing all the files to 2GB but those tempdb files are hardly being used (<5% usage on all of them).

The servers are not on fire or anything and production is running fine but we definitely want all those tempdb files configured to the same size.

EDIT: Result of requested query

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb.  NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at:

https://sqlblog.org/2009/10/14/bad-habits-to-kick-using-the-visual-designers


MySQL replication - ibdata1 huge I'm trying to implement MySQL replication in production after I successfully implemented it on my dev environment. Basically, what I did is export the dump using the following :

mysqldump --single-transaction --flush-logs --master-data=2 --disable-keys --no-autocommit --databases mydatabase > mydatabase.sql

When I imported the file the configuration on the slave looked like this :

[mysqld]
datadir=/data1/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

The problem is that the ibdata1 file is HUGE (384G). After I restored it, I added the following in my.cnf and restarted MySQL :

# replication implementation
log-bin=mysql-bin
binlog_format=mixed
read-only=1
relay-log=mysql-relay-bin

My question is : should I go ahead with this if the ibdata1 file is so big or should I add these options before I perform the import? Thanks in advance for any insight.

If you want to shrink ibdata1, so that it should only contain the metadata, You may try these steps

To shrink ibdata1 once and for all you must do the following:

MySQLDump all databases into a SQL text file (as bkp_all_db.sql)
Drop all databases (except mysql schema)
Stop MySQL
/etc/init.d/mysql stop
Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Now remove ibdata1, so that there should only be the mysql schema in /var/lib/mysql

rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

Start MySQL instance, This will recreate ibdata1, ib_logfile0 and ib_logfile1 at 1G each

service mysqld start
Reload bkp_all_db.sql into mysql (import). ibdata1 will grow but only contain table metadata

http://mysqlrockstar.blogspot.in/2014/07/mysql-ibdata1-file-shrink.html


Oracle: How can I track the progress of an index rebuild? How can I track the progress of a command such as this?

alter index myindex rebuild reverse online;

You should Query the V$SESSION_LONGOPS

select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$session_longops ops
where ops.sid=&your_sid
and ops.serial#=&your_serial
and time_remaining>0;

If the index rebuild is parallel, than you you must find the parallel slaves that do the work.
This can be done by querying the V$PX_SESSION. Your session that issued the 'alter index' statement is the query coordinator

select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$px_session par, v$session_longops ops
where par.qcsid=&your_sid
and par.qcserial#=&your_serial
and ops.sid=par.sid
and ops.serial#=par.serial#
and ops.time_remaining>0;

But an index creation consists of more than one longops (scan the table, sort the data,...)


Simple left outer join leaving me with inner join So I have a table with each record being a change to an object.

e.g. in the app

you open your project with a project id
in this project you edit an object name
hit save

that generates new record with datetime stamp and object name change value, related to the project id that you work on

tricky part is, project_id can be null. Still the object_id is unique. So I know from the object id, on which project I am.

table change
with object_id and datetime being a composite primary key.

e.g.

object_id | datetime            | objectname  | project_id
55        | 2014-03-24 09:55:40 | somevalue 1 | 10
77        | 2014-02-24 09:55:40 | somevalue   | 88
55        | 2014-10-24 09:55:40 | somevalue2  | null
55        | 2014-11-24 09:55:40 | somevalue3  | 10
22        | 2014-12-24 09:55:40 | somevaluex  | 10

What I want to get is all the records, that belong to project 10

that would be

object_id | datetime            | objectname  | project_id
55        | 2014-03-24 09:55:40 | somevalue 1 | 10
55        | 2014-10-24 09:55:40 | somevalue2  | null
55        | 2014-11-24 09:55:40 | somevalue3  | 10
22        | 2014-12-24 09:55:40 | somevaluex  | 10

What I tried and failed horribly is an inner join on the same table

I tried this:

SELECT  * FROM change
LEFT JOIN (SELECT * from change AS x WHERE x.project IS NULL) AS change2
ON change2.object_id = change.object_id
WHERE change.project = 10

This gives me only records with null elements for project id, like for an INNER JOIN.
I tried as well:

SELECT  * FROM change
LEFT JOIN change AS change2
ON change2.object_id = change.object_id
AND  change2.project is NULL
WHERE change.project = 10

Funny, it gives the same result (like an inner join would, e.g. only records with null as project)

What am I missing?

I think you need a UNION or an OR with EXISTS:

SELECT c.*
FROM change AS c
WHERE c.project_id = 10
   OR c.project_id IS NULL
  AND EXISTS
        ( SELECT *
          FROM change AS x
          WHERE x.project_id = 10
            AND x.object_id = c.object_id
        ) ;


Two-column index (pretty unequal columns) and database optimisation I'm thinking about creating an index on a table like this:

primary | type | id
      1   a      10001
      2   a      10002
      3   b      10001
      n   b      1....

type will contain four values, ~ 95 % will be "a" (or 1, please don't care about the exact value/normalization). Also, 95 % of all queries will be looking for "a", too, in combination with a list of ids (~ one to five or so), like:

SELECT id FROM table WHERE type = 'a' AND id IN (...)

Basic task is to check a list of given id's for existence, so a "covering index" is the thing I basically want.

Now, as far as I understood, it's better to have the column first which reduces the result set most, which would be id. But on the other hand, an intelligent database engine could detect that most of the queries are about "a" and could keep only this branch of the index tree in memory, or, enough memory provided, all three indices as a "different" index each, as it would be better not to bother with all the "b" values.

Is a MySQL/MariaDB (just InnoDB backend) doing this kind of optimizations? Or any other thoughts? I'm not so interested in this table, more a general view on this case.

Thx in advance for all your comments ;)

You're right. The more selective column should come first unless it happens to be in a range condition.

Having type in the index will provide absolutely no benefits on the performance when you query for type='a'. It will however put some extra overhead on your indexes which may result in actual degradation of performance.

If id is always in the query with this cardinality I wouldn't bother having type in your index at all. Especially if id is unique.


How to fix double-encoded UTF8 characters in postgres I have a dataset (shapefile) with the same problem as the post below:
https://stackoverflow.com/questions/11436594/how-to-fix-double-encoded-utf8-characters-in-an-utf-8-table
"A previous LOAD DATA INFILE was run under the assumption that the CSV file is latin1-encoded. During this import the multibyte characters were interpreted as two single character and then encoded using utf-8 (again).
This double-encoding created anomalies like ÃƒÂ± instead of ñ."
However, the solution given is in mysql and not postgres, i tried it on postgres and it didn't work, just worked on mysql:
UPDATE tablename SET
    field = CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8);

I need to import and fix this shapefile using postgres because I will need to use postgis to do various spatial analyzes.
How can I solve this using postgis?

That is pretty similar in PostgreSQL:
convert_from(convert_to(textcol, 'LATIN1'), 'UTF8')


Pressing Ctrl+C during UPDATE query in Postgres, was the query partially run? I accidentally launched unwanted UPDATE query inside psql (9.4) and killed it with CTRL+C a few seconds later. Were there any records modified, or it's all or nothing?

A statement is always atomic. That is true for all DML statements and nearly all DDL statements.

So either everything succeeds or nothing. If you interrupt it halfway through, everything that was changed until then will be rolled back.


How to design a database where parent table has default values I have following tables (Its not actually accessories but I am using it as an analogy),

Accessories {AccessoriesId, Name, other accessory related attributes...}
ClientAccessories (ClientAccessoryId, AccessoriesId, CLIENTID, LastModified, CreatedDate, Username, isDeleted)
EmployeeAccessories (EmployeeAccessoryId, AccessoriesId, EMPLOYEEID, LastModified, CreatedDate, Username, isDeleted)

Now problem is that each Employee and each client will get a set of default accessories, which they can delete/update as well, which will set a flag in there specific table. They will add there own accessories as well in future.

I am not sure about if this is a valid design ?

It might not be the most elegant model - but your description points to 2 (independent) m:n relations between Clients and Accessories and Employees and Accessories.
The usual way to resolve those m:n relations IS to create mapping tables like you did - so nothing wrong from that view point.
Of course it needs joins to request data over the 3 tables, when you want to know what accessories are still held by a certain Client or employee.

If your clients or employees receive the same initial sets of accessories you can go like @Akina wrote in the comments. Fill a small template table with the sets you want to give new clients / employees and copy the lines over - while filling in the correct clientID / employeeID to the corresponding mapping table.


SQL Windowing Functions: Are the results ordered? If I have a window function which uses something like

SELECT *, row_number() OVER (ORDER BY something) FROM table

should the results be ordered?

I am currently testing out my queries in Microsoft SQL Server, and it certainly seems to be ordered, but I know that this product has the tendency to order rows when it hasn’t been asked.

Is this the standard behaviour?

Without a presentation ORDER BY clause, standard behavior returns a set in no particular order.

I think Itzik Ben-Gan explains it best and even without one of his books, you can find the explanation in this article. About 12 paragraphs down, next to the first code sample, the text describes how the ORDER BY in the window function serves a logical function to describe the set, and how that is completely separate from a presentation ORDER BY clause.

A related question appears here
Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?


Can mirror and witness have same port number? When configuring database mirroring, the mirror and the witness have generated the same port number. When I hit next, it errors out with:

  End points for instances for the same server must use different port numbers. Your instances have conflicting port numbers....

Can they have the same port number?

As detailed in this MSDN document, the port number must be unique within any given computer:

  Note that if more than one server instance on a given computer requires a database mirroring endpoint, specify a different port number for each endpoint.

TCP ports can only be served by a single process.  When you try to create two endpoints with the same port number on the same computer, you are attempting to have two processes monitor the same port.

This image is contained on that page, and makes it quite clear:

If you are using unique computers for all machines in the mirroring configuration, you can configure each computer with the same port.


What free database software support sequences I have a requirement that a database must support sequences. I know oracle and PostgreSQL support it. Are there any other free database software that support database sequences? it would be great if it had a .NET driver too

If with "free" you mean "open source", then Firebird comes to mind (but I personally would prefer PostgreSQL over it - the much better documentation alone is a reason).

Ingres also supports sequences, but I don't know if there is a .Net driver for it.

There are other open source ones like Apache Derby, HSQLDB and H2 Database but as they are Java only, I'm pretty sure they do not have a .Net driver

If with "free" you mean "free of charge", then Oracle Express, DB2 Express-C and SQL Server 2012 Express are an option as well (as all three of them support sequences)


Can I use mysql to serve 100's of millions of small image files? I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the small files).

For the time I have dumped the files all into large 12GB binary files and have an index that allows me to seek to the right point and read the correct # of bytes.

But that solution was a stop-gap, I need to also update the files daily, generally this means adding new files, and once-in-a-while (monthly/quarterly) do housekeeping and get rid of old ones.

So I wonder if I'll be shooting myself in the foot by trying to create a massive MyISAM table to store these 2-10k blobs?

On a 64 bit linux system there's a 4TB limit on DB file size right? Can I split such a massive table into multiple physical files as my dataset grows? Eventually we might serve 6+ TB of small image files.

My options right now are between:

Dump the binary data to a large flat file and manage the indexes on my own (either with metadata in the URL or a separate index file on the webserver)
Use a MySQL table, add new data via fast LOAD DATA INFILE, which we have had good experience with on another 100GB reference table we use.

Thoughts? Cautions?

I wonder why any kind of DB is needed. It sounds like you'd like it if these were just files, but that the number of them and their small size are problematic.

There are two strategies that can help here, one is to use a few levels of directories to help partition the files into smaller clusters.

Now, some file systems may have inode issues or become very wasteful of space if they are tiny files and large block sizes.  Indeed, if you're on Linux it may be worth spending some time looking at the alternative file systems that are available and see if one meets your use case well.

A 'simply' way to avoid the issue is, for the last layer, rather than having files in a leaf directory, just have files in a zip file with zero compression.  This pretty much works almost like a directory with files, but can circumvent the above two issues.  It effectively gives you what you've been doing by hand, but with a more standard tool-chain.

From what you've described so far, I personally wouldn't be putting this anywhere near any kind of DB, NoSQL or traditional.

If you really want a DB solution, you could look at some form of partitioning across multiple tables to avoid any file size limits, but it wouldn't be my first option!


Find highest sold item name for each date I have three tables

table 1: items (int,itemName)
table 2: orders(order id,oDate)
table 3: orderDetails(orderId, itemId, itemQnt)

Here is the complete schema with some demo values

What is the best way to find top sold itemName for each day.
Output would be like date,itemName.

If you want ties:

;WITH od AS
(
  SELECT o.oDate, od.itemId,
    r = RANK() OVER (PARTITION BY o.oDate ORDER BY SUM(od.itemQnt) DESC)
  FROM dbo.orderDetails AS od
  INNER JOIN dbo.orders AS o
  ON od.orderId = o.orderId
  GROUP BY o.oDate, od.itemId
)
SELECT od.oDate, i.name, r, rn
FROM od INNER JOIN dbo.items AS i
ON od.itemId = i.itemId
WHERE r = 1
ORDER BY od.oDate;

If you don't want ties, change RANK() to ROW_NUMBER(), just note that it will pick an arbitrary winner unless you add some kind of tie-breaker to the ORDER BY.

If you need to fill in gaps in some date range, for example if yesterday had no sales but you still want a row, you can generate a set of dates first, and then use an outer join (this example returns rows from January 1 -> January 15 inclusive):

DECLARE @startDate DATE, @endDate DATE;
SELECT @startDate = '20150101',
       @endDate   = '20150115';

;WITH [days](d) AS
(
  SELECT TOP (DATEDIFF(DAY, @startDate, @endDate)+1)
    DATEADD(DAY, ROW_NUMBER() OVER
    (ORDER BY [object_id])-1, @startDate)
  FROM sys.all_columns
  ORDER BY [object_id]
),
od AS
(
  SELECT o.oDate, od.itemId,
    r = RANK() OVER (PARTITION BY o.oDate ORDER BY SUM(od.itemQnt) DESC)
  FROM dbo.orderDetails AS od
  INNER JOIN dbo.orders AS o
  ON od.orderId = o.orderId
  GROUP BY o.oDate, od.itemId
)
SELECT oDate = d.d,
  name = COALESCE(i.name, '<n/a>')
FROM [days] AS d
LEFT OUTER JOIN od
ON d.d = od.oDate
AND od.r = 1
LEFT OUTER JOIN dbo.items AS i
ON od.itemId = i.itemId
ORDER BY d.d;

(Also, do you really think it's productive to make slightly shorter but far more obscure and difficult-to-remember column names like oDate and itemQnt? And why isn't there a unique constraint on orderDetails(orderId, itemId)? Finally, please stay away from regional, ambiguous formats like mm/dd/yyyy.)


Can I force mysql server to resolve the external ip to localhost? I'm finally succeeded with ssh tunneling. My point is to open the mysql server to local users only (user@localhost etc.) while providing a remote control to my customers through ssh tunneling.

The problem is when I connect through the mysql command line tool (mysql.exe -u root -h 127.0.0.1 --port=8600) I've got a permission denied error for user root@my.servers.ip.address (the tunnel redirects from 127.0.0.1:8600 to my 3603 remote server)

Well it makes sense since my root exists in mysql.user only with the localhost host.

But then, is there a way for me to somehow tell mysql that this ip address is actually the server's address and that the root should be allowed to connect?

I've got no clue where to start. To me if such a thing existed it would be some kind of ip resolving table...

Great, here is the solution I found. I'm chosing to post it here instead of just removing the question since I believe it may be relevant for further reference.

Basically the idea is to leave mysql alone but to tell the server to resolve the ip. In the case of my centos6 configuration it means editing the /etc/hosts file and appending a my.servers.ip.address localhost line into it.

Hope it helped!
S.


Recommendations to prevent failover (scheduled) I have a two node SQL Server failover cluster with availability group. This is used for SharePoint.
SharePoint rebuilds the indexes where necessary every night. This routinely causes failover.
I can't get this resolved with the infrastructure team, so I want to mitigate.
Since the index rebuild is scheduled, I am considering:

Suspend secondary node for duration (1 hour)
Change failover from automatic to manual for duration (1hour)

Anything else? Which is best? I know it's not ideal and the root cause should be fixed but I am out of options.

Thanks
Andrew

The AG usually fails over when the secondary and witness can't talk to the primary for a length of time. (I'm simplifying that a little, but for more technical details, check out MS's documentation on cluster quorum.)

To fix this, I'm going to suggest a few different things. You can do one, two, or all of 'em - just start with the things that are easiest for you, and add more as necessary.

First, leave some breathing room between index rebuilds. If you're using the built-in maintenance plans to rebuild indexes, consider switching to Ola Hallengren's maintenance scripts instead. (They do backups, checkdb, and index rebuilds, but for now, let's only focus on your index rebuilds.) They operate via a stored procedure, and one of the parameters for the IndexOptimize stored proc is Delay. By default, there's no delay between index rebuilds, but you would want to add a 5-10 second delay. This will give your IO subsystem and network some time to clear the air.

That by itself may not be enough, so keep reading.

Next, consider tweaking the default AG timeouts. In sections 7-8 of this Microsoft article, they explain how the default timeouts may be too quick for low-quality network connections. In the comments, you also noted that you're hitting lease timeouts, so check out these additional resources:

Microsoft support post: How It Works: SQL Server Always On Lease Timeout - in which they write, "If the lease timeout is expiring you likely have a system wide, impacting event taking place that needs to be resolved.  If you are experiencing lease timeouts troubleshoot the entire computer you are likely encountering a broader issue than just a SQL Server lease timeout." My money's on this - since you're seeing it during index rebuilds, I'm guessing you have an issue that makes SQL Server unresponsive during that time. For community help with that, follow the instructions in Getting Help with a Slow SQL Server, especially the last section around including sp_Blitz's results in Markdown format in your question.
Improvements in 2012/2014 patches for lease timeouts - KB #3112363 adds better diagnostic data for lease timeouts in 2012 SP3 and 2014 SP2. This is only going to give you more troubleshooting data - it's not going to fix the timeouts. (The link I posted above is more about fixing the timeouts long term by fixing the underlying performance problem.)

Then, consider adding network capacity. I'm guessing your servers have just one 1Gb Ethernet port each, and it's getting saturated during your index rebuilds. You could consider upgrading to teamed cards with load balancing, or to 10Gb cards, or to separate heartbeat network cards. I'm not usually a big fan of separate heartbeat networks because they increase complexity, but that might be the cheapest/easiest solution here. (Start with teaming first though.)

The above steps will let you keep your automatic failover AND your index rebuilds. However, I'd be a bad database guy if I didn't suggest that maybe rebuilding every index, every night, is a little bit overboard. Not only is it causing your AG problems, but it's also inflating your tranaction logs, causing those backups to take longer too. For more on that, watch my GroupBy session, Why Defragmenting Your Indexes Isn't Helping.


Auto-Shrinking All Databases Files To Their Minimum Size In Sql Server I restore my all databases in production to my development environment monthly. Development server has 4 TB disk space and there are 4 different instances over it which containt databases on the production server. In Development, i can truncate and shrink some big-sized tables to expand free disk space. The purpose of doing this process is the complaint of software developers about the development server slowness. I talked to system administrator team and they informed me that the slowness was because of the insufficient disk space area. So, i make this work. My question is about how i can shrink all database files to their minimum size after truncating related tables. So, should i make it with powershell, and how?

My question is about how i can shrink all database files to their minimum size after truncating related tables

To answer your question directly, this is done using DBCC SHRINKFILE command -

DBCC SHRINKFILE (YourDatabaseLogicalFileName,<desired size in MB>)

Here you can read more information on how to use this commnand
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15

But, as other comments already mentioned, the reason of poor performance lies somewhere else and you need to start a methodical process of collecting and analyzing performance information (Performance Counters, Waits, Top heaviest queries, etc.)

Among other reasons, possible that poor performance in your Dev environment could be simply because system specification of Dev environment (CPU, RAM, Storage, Network etc.) is much weaker/worse than your Production; or Developers could be running some heavy and not tuned queries in Dev environment, that they do not run on Production


Blocking in AG that causes app not to launch If lag between primary and secondary is huge, then can it cause persistent blocking on app sessions? Coz recently we had an issue where blocking sessions were having wait type as HADR_SYNC_COMMIT and there was lag for one of the DB in AG.

So we failover to Secondary even though the lag was pretty high. After DB coming online on new Primary, issue got resolved.

But today the issue happened again. Can you please help.

As addition to Tony's comment this MSDN post can help you with performance counters and latency factors.

Also, verify one of the known issue (lag) "redo blocking on secondary due to schema changes on primary", you can verify that via built-in Xevent's session or follow these steps for validation of redo blocking and quick fix.


MySQL binary log - write operation Consider a scenario, where you have a MySQL master and slave server in replication. As we know, binary log enabled on the master and the slave reads from the binary log of the master. From MySQL documentation I understand that, only the fully committed transaction will be written to binary log, so there is no chance of missing any transaction in replication (if everything works well). So my assumption is that, once the transaction is completed and written to tablespace, it will be written to binary log.
My doubt is whether the write operation of the binary log is concurrent or sequential?
It means, for example, before writing to binary log and after written to tablespace, if the server shuts down or crashes, what happen to the transactions which happened in mean time?
Also, I am using mysqlbinlog utility and set with stop-never parameter. I need to know, when stop-never enabled the binary log will be in sync on the destination server (I tested it with flush logs - it creates new log on destination too).
QUESTIONS

How the copying of binary log works?
Is it first writes to master binary log and copied to destination log?
Is it writing on both the source and destination binary log simultaneously?
What will be impact of MySQL performance and disk IO performance?

If I misunderstood the concept, kindly correct me. Thanks in advance.

Here are the answers to each of your question

  1 How the copying of binary log works?

I wrote a detailed list of the steps taken by MySQL Replication in my answer to  Is MySQL Replication Affected by a High-Latency Interconnect?.

Someone else answered that same post, but from the point of view of DB Client

  2 Is it first writes to master binary log and copied to destination log?

There are many middle steps

Master completes a SQL update (single update or group of updates (AKA group commit))
Master writes one of the following to its binary log

the SQL command if binlog_format=STATEMENT
the row changes if binlog_format=ROW

IO Thread from the Slave copies the SQL Update from Master's binary log to its relay logs
SQL Thread on slave reads updates from its relay log and processes. If log_slave_updates is enabled, then the Slave writes that SQL command to its local binary log.

  3 Is it writing on both the source and destination binary log simultaneously?

Never. Not with semisync replication, not with GTID replication either. Just look at the above steps

  4 What will be impact of MySQL performance and disk IO performance?

That depends where binary logs and relay logs are stored. If data and logs are on the same disk mount, you will both random writes for data and sequential writes for logs. Both will be competing for the same disk space. You should separate them (See my post from last year Should the Redo log on a production MySQL server be configured to another location?)


Overcome SubQuery Returned More Than 1 Result I found this syntax already answered to a diff question, but I am getting the error of

  More than 1 result returned

what do I need to alter in my syntax in order to defeat this error?

Declare @ONE Table
(
    employee varchar(250)
    ,eod int
)

Declare @Two Table
(
    employee varchar(250)
    ,amtpaid decimal(10,2)
)

Insert Into @One (employee, eod) VALUES
('Frank',   '1122')
,('Frank',  '1188')
,('Bill',   '1100')
,('Larry', '1199')
,('Bill',   '2211')
,('Jerry',  '3311')
,('Jerry',  '3322')
,('Stan',   '3388')
,('Stan',   '3300')
,('Larry',  '3377')

INSERT INTO @Two (employee, amtpaid) VALUES
('Frank', '11.11'),('Bill', '10.10')
,('Larry', '44.44'),('Jerry', '20.32')
,('Stan', '100.33')

SELECT a.employee
    ,[EOD] = b.eod
    ,[Paid] = CASE
        WHEN b.eod = (
                SELECT min(b.eod)
                FROM @Two
                WHERE b.employee=a.employee
                )
            THEN COALESCE((
                        SELECT TOP 1 SUM(ISNULL(amtpaid, 0))
                        FROM @Two b
                        WHERE a.employee = b.employee
                        ), 0)
        ELSE 0
        END
FROM @One b
INNER JOIN @Two a
ON a.employee = b.employee
GROUP BY a.employee, b.eod, b.employee
ORDER BY a.employee ASC

EDIT
My desired output is like below - amtpaid showing for only 1 employeename not all of them

Employee    EOD Paid
Bill    1100    10.10
Bill    2211    0.00
Frank   1122    11.11
Frank   1188    0.00
Jerry   3311    20.32
Jerry   3322    0.00
Larry   1199    44.44
Larry   3377    0.00
Stan    3388    0.00
Stan    3300    100.33

No GROUP BY, no subqueries needed. The answer assumes that @Two has only one row per employee:

SELECT
    a.employee,
    [EOD] = a.eod,
    [Paid] = CASE WHEN a.eod = MIN(a.eod) OVER (PARTITION BY a.employee)
                 THEN b.amtpaid
                 ELSE 0
             END
FROM
    @One a
    JOIN @Two b
    ON  a.employee = b.employee ;

Another way would be to use window functions and a left join:

SELECT
    a.employee,
    [EOD] = a.eod,
    [Paid] = COALESCE(b.amtpaid, 0)
FROM
    ( SELECT a.employee, a.eod,
             rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                     ORDER BY a.eod)
      FROM @One AS a
    ) AS a
    LEFT JOIN @Two b
    ON  a.employee = b.employee
    AND a.rn = 1 ;

which would be easier to modify if @Two has more than one rows per employee and we want the sum:

SELECT
    a.employee,
    [EOD] = a.eod,
    [Paid] = COALESCE(b.amtpaid, 0)
FROM
    ( SELECT a.employee, a.eod,
             rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                     ORDER BY a.eod)
      FROM @One AS a
    ) AS a
    OUTER APPLY
    ( SELECT amtpaid = SUM(b.amtpaid)
      FROM @Two b
      WHERE a.employee = b.employee
        AND a.rn = 1
    ) AS b ;


Is SQL Server Thread Safe? If I need to update same row with two concurrent thread then is it possible in SQL Server?

Suppose I have one table that contains Id and Name columns and now I want to update the third number Id with Name 'xyz' using one thread. Using the other thread, I want to update the same name which I am updating using the first thread, but it gives error like your connection is in connecting state. I can't update the data.

Does anyone know how to solve it? Do I need to assign a lock?

Is Sql server thread-safe?

This issue is not with that SQL Server being thread safe or not.The issue is there with SQL Server Connection Pooling

In Short:- Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. More reading from the link above.

Things to try with Connection object to fix this

Don't reuse connections or any ADO.NET objects at all and don't make them static.
Always create, open(in case of Connections), use, close and dispose them where you need them
Use the using-statement to dispose and close(Connections) implicitely

Now update on same row:
If the update statement are to acquire a row/table lock, for instance, any other update process to the same row/table at the same time would be blocked.

Further Reading:

Locking and Row Versioning
and Table Hints


How to tell if SQL is autogenerated by some tool I am looking into some stored procedures and the code is more or less non-readable e.g. there are 15 main joins in one statement and the level of nesting of the subqueries is e.g. 7 in order to simply define one of the tables used in the joins... If one counts the joins in the subqueries then there are many more joins... Hope it makes sense...
I do not think a human has written these SPs and I think they have generated the code and copy-pasted it in the SP template... Does this sound right?
What are these tools called? So I am trying to refactor these and I was first annoyed and thought what idiot writes code like this but now like I said I think it is autogenerated... By autogenerated I mean a drag and drop interface.
Is my inference correct based on the description above? If you could comment of performance that would be great. My understanding is that these tools are performance killers...

Trying to reverse engineering to find the tool will take too much time and you will possibly never find the solution. Debug this through an Agile methodology:
“The most efficient and effective method of conveying information to and within a development team is face-to-face conversation”
So go to your colleagues and don't be afraid to ask:
"Are we using an automated tool or a drag-n-drop tool to create queries? I ask this because they really look machine-made to me"
And the result can be:

Yes, they are written by a tool: and show to your colleagues how painful this choice can be for your performances
No, they are written by Tom: and talk to Tom and say "Hey, Tom, I was having an hard time reading through your code, is there any way I can help you..."

Be nice to people. Machines cannot change but humans can.


MySQL deducing fields dynamically I'm trying to deduce a value in a select statement, then reuse that calculation a few times per row, as well as group by that value. In my case, I'm doing time_to_sec(timediff(created, lastHit)) a bunch of times:

select
     time_to_sec(timediff(created, lastHit)) diff
    , if(
         time_to_sec(timediff(created, lastHit)) > 1800
         OR time_to_sec(timediff(created, lastHit)) = 0,
         1, 0
      ) newSession
    , sum(1) as sessions
    , sum(
          if(
             time_to_sec(timediff(created, lastHit)) > 1800,
             0, time_to_sec(timediff(created, lastHit))
          )
      ) as totalDuration
    from pageviews
    group by newSession;

How can I improve that query without storing redundant data in my schema (such as the pre-calculated time difference between created and lastHit)?

I thought about using variables (select @diff := timediff(...)), but this post mentions that I should avoid assigning a variable and reading from it within the same statement.

In most situations, the recalculation of an expression is much less of a performance burden than any solution.  So, don't worry about it.

You have another issue -- The GROUP BY does not include the first thing in the SELECT, so the value you will get for that is unpredictable.


AWS Aurora RDS 2nd reader gets no queries Running a Postgres Aurora RDS with one writer and two readers.

One reader is used heavily, but the other is barely used at all according to CloudWatch.

The Laravel application this is connected to points the read host to the reader cluster endpoint so that Aurora can do the balancing internally, but it does not appear to be doing that, instead favoring the 1st reader exclusively.

Is there some misconfiguration in the RDS or something else going on?

We have also faced the same issue and it got resolve after reduced the DNS TTL value in our Java application.

Amazon Aurora for MySQL or PostgreSQL distribute workload between reader nodes as below (From AWS Manual)

  Aurora uses a  DNS based round-robin load balancing mechanism for
  balancing connections between the reader instances.  This load
  balancing is handled through Amazon Route 53's DNS service.  This
  means that your application makes a DNS query for each new connection
  request. Each time the reader endpoint is resolved, the instance IP of
  one of the reader is provided for the connection. In order to get the
  instance IP of a different reader each time, one must keep resolving
  the endpoint without caching DNS. Aurora DNS cache uses a short Time
  to Live (TTL) of  5 seconds. To ensure both the readers are equally
  utilized, care must be taken not to increase the TTL more than 5
  seconds for a client and network configuration. Caching DNS responses
  for longer than 5 seconds can result in overutilization of one reader
  over others as you have been experiencing.

In your application check the DNS TTL set for the application and adjust it down to 5 seconds.


Preventing users who are dbo from dropping their own databases We allow our users to be dbo in their own databases, however this does give them the ability to drop their own databases. This doesn't happen very often, but I want to prevent them from doing this.

I've devised the following trigger that prevents this happening and it restricts the dropping of databases to sysadmin role members:

CREATE TRIGGER [deny_customer_db_drop]
ON ALL SERVER
FOR DROP_database
AS
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
    PRINT 'You are not permitted to drop this database'
    ROLLBACK
END
GO

This works, but I'm wondering if there's a more elegant way to do this using permissions without tweaking each login's own specific permissions?

That is your only option.

As a side note, you may also want to check for server role db_creator = 0 as well.


How does SELECT FOR UPDATE behave with views in PostgreSQL? To retrieve domain model data from PostgreSQL, I sometimes select against a view rather than a table. I do this so that I can return as much information as possible in one query, rather than fire off multiple queries to resolve all relational dependencies. Mostly this is because there is a 1-to-1 relationship between tables, or they are lookup tables and I just need certain columns, etc. To prevent certain race conditions, one strategy I'm using is SELECT ... FOR SHARE/UPDATE queries. I'm wondering how these behave when we start using them against views.

Suppose we have two tables A and B, each with a column ID. Now, suppose we have a view AB that is a join operation between those two tables. Let's assume this is a Cartesian join:

SELECT
    "A"."ID" AS "AID",
    "B"."ID" AS "BID"
FROM "A"
CROSS JOIN "B"

If we then SELECT FOR UPDATE against AB what rows of what tables will get locked in the following instances?

SELECT * FROM "AB" WHERE "AB"."AID" = :ID
SELECT * FROM "AB" WHERE "AB"."BID" = :ID
SELECT * FROM "AB" WHERE "AB"."AID" = :ID1 AND "AB"."BID" = :ID2

Short answer

The following rows will be locked for update in the three cases:

a row with "ID" = :ID in table "A" and all rows in table "B"
a row with "ID" = :ID in table "B" and all rows in table "A"
a row with "ID" = :ID1 in table "A" and a row with "ID" = :ID2 in table "B"

The results are as one can expect after reading this statement in the documentation:

  If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query.

How to test similar cases

Install the extension pgrowlocks..

create extension if not exists pgrowlocks;

Run two instances of psql. In the first one start a transaction and execute a query.

psql #1:

begin;

select *
from "AB"
where "AID" = 3 and "BID" = 103
for update;

You can see locked rows in the other instance of psql.

psql #2:

select * from pgrowlocks('"B"');

 locked_row | locker | multi |  xids   |     modes      |  pids
------------+--------+-------+---------+----------------+--------
 (0,4)      |  88577 | f     | {88577} | {"For Update"} | {8068}
(1 row)

Do not forget to close the transaction at the end (before a next test).

psql #1:

rollback;

The first column of the above result is ctid. You can select the original row with the query:

select *
from "B"
where ctid = '(0,4)';


mysql innodb buffer pool instances always 1 I set in my.ini

innodb_buffer_pool_instances=8

However, no matter how I change the innodb buffer pool size. I check the
innodb_buffer_pool_instances in mysql workbench, it always equal to 1. I cannot find a way to change the instances number. What is wrong? I am using Mysql 5.7.12.

The default values for innodb_buffer_pool_instances is explained in the official MySQL Documentation:

  Default Value (Other) :   8 (or 1 if innodb_buffer_pool_size < 1GB)

  Default Value (Windows, 32-bit platforms) : (autosized)

So, if your innodb_buffer_pool_size is less than 1 GB, innodb_buffer_pool_instance will always be reset to 1.

Moreover, since you mentioned my.ini file, it indicates that you are using Windows. If your Windows is 32-bit, then MySQL auto-determines this value, and thus changing it in my.ini won't affect anything. The rules are:

  If innodb_buffer_pool_size is greater than 1.3GB, the default for innodb_buffer_pool_instances is innodb_buffer_pool_size/128MB, with
  individual memory allocation requests for each chunk. 1.3GB was chosen
  as the boundary at which there is significant risk for 32-bit Windows
  to be unable to allocate the contiguous address space needed for a
  single buffer pool.
  Otherwise, the default is 1.


If a table with a surrogate key has a column known to have unique non null values (eg SSN), does it violate 3NF? As I understand it, third normal form (3NF) basically means there should be exactly one key.

If a table with say an auto-increment id column also has a column known to be unique and not null, eg social security number, this other column could be used as the key.

Ignoring practical/business issues (eg ecurity/privacy risk when passing around SSN as a key/FK), from a strictly schema design aspect, would such a table not be in 3NF because there are effectively 2 keys?

Would the answer vary on whether there was a unique key on the other column? If so, why?

A relation R is in third normal form if every non-prime attribute of R
  is non-transitively dependent on each candidate key of R

E.F.Codd, 1971, Further Normalization of the Data Base Relational Model

It is implicit in the definition of a relation that a relation must have at least one key. Nothing about 3NF or any other Normal Form requires that a relation should have only one key.

Unfortunately books on database design and normalization have plentiful examples of relations with only a single key and rather fewer examples with more than one key. This strikes me as odd given that multiple keys appear to be very common practice these days. The dearth of practical examples in non-academic literature seems to be one cause of confusion about the role of keys in database design. Another cause of confusion is the popular mnemonic "nothing but the key". That phrase is usually attributed to Bill Kent but it isn't an accurate definition of 3NF.


SQL Job for Monitoring Blockings on SQL Server Is it anyway possible to create a SQL Job for monitoring blocking on SQL Instance, if the blocking are still happening after some interval of time?

We are using SCOM Monitoring tool to monitor events from the event viewer and generated alert on the basis of this that. Kindly guide as to what can I do generate an event in case of blocking persists for more than a minute.

I tried enabling the blocking process threshold and created blocking, but I see no event either in event viewer or in SQL Error Logs.
We are using Sql Server 2008 R2 Enterprise.

Thanks in advance.

One way to do this is to set Blocked Process Report to 60 seconds, then an event will be raised by SQL.  This event can be picked up in a number of ways.

1) Via SQL Profiler, this isnt a great way as you have to run trace etc to find them

2) Via Extended Events, again not great you would have to have a job constantly polling the target.

3) Via Event Notifications.  This is a great way and I think the best way of doing it.  You can create an Event Notification and a Service to direct the notification into a Service Broker queue, then you can process the queue messages with a stored procedure.  It is a fantastic way of getting a free monitoring tool for yourself.

http://i.zdnet.com/whitepapers/Quest_How_to_Use_SQL_Server_Extended_Events_and_Notifications_to_Proactively_Resolve_Performance_Issues.pdf

Just follow page 24, but change the event from ERRLOG to BLOCKED_PROCESS_REPORT

By Default blocked process report is set to 0 (disabled) so to enable you will have to:

SP_CONFIGURE'blocked process threshold',60;
GO
RECONFIGURE;
GO


Adjusting the my.cnf file for DB servers after an instance resize I am trying to optimize the DB server of one of our production environments (MySQL DB, centos7), and I've noticed that there is a very high memory consumption of the DB.
After further investigation I noticed that we should resize the DB from 8 GB RAM to 16 GB RAM due to increased usage. But when looking at the my.cnf file, I found the following settings for the buffer pool parameters:
#Pool size configuration
innodb_buffer_pool_size=6G
innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_instances=48

Now after checking 15.8.3.2 Configuring Multiple Buffer Pool Instances and a few other websites, it seems to point out that innodb_buffer_pool_instances should be set to 1 per CPU core.
For some reason ours is 48, and I couldn't find anyone in charge who could give me the reason for this.
I'd like to know if this setting could remain the same, which means it's probably there, because of a certain usage pattern?
This is a relatively small DB.
I will be resizing to 16 GB and adjusting the pool size to 12 GB, so it makes sense that innodb_buffer_pool_instances should be also set to 12, from my very basic understanding. Am I correct with my assumption?

The most important change would be
innodb_buffer_pool_size=6G --> 12G

The number of buffer_pool_instances should be 1 per GB, not one per core.  So, 12 in your new machine.  However, there is a limit of 16.  As for why...

I suspect it makes very little difference -- perhaps single-digit-percentage when the server is extremely busy.  (If a server is that busy, it is about to keel over; you have worse problems.)

Essentially nothing else requires changing.
After you have been running normally for at least a day, grab SHOW GLOBAL STATUS and SHOW VARIABLES; then get an analysis here:  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


Is it a bad backup strategy in Oracle to have only Level 0 backups and Archive Logs in between with no incremental? If you backup the database with a Level 0 every six hours, then do the archive logs in between, every 15 minutes perhaps, what benefit would I have to incorporating a differential backup?  Essentially, full backup only with archive logs x minutes.  Particularly cumulative.  Is it only because of disk space?

I have read this,

Is it enough to have only full backup and transaction log backup only for a good backup plan?

But it did not help, as it includes a differential.

Edit:  I ask because I thought you had to have a full backup to use a differential in the first place.  So why not just have the full backup to begin with?

Do you have enterprise edition or standard edition? If you have enterprise edition you can create a block change file and do a true level 1 incremental backup. Doing a level 0 backup is a FULL backup. There is not reason for you to do a FULL backup every 6 hours. Most people do a FULL backup every day and possibly send the archive logs to more than one server. I do two level 0 backups per week and level 1 backups on the days when I am not doing a level 0 backup. If you are new to Oracle you may want to do a backup/recovery class. You should practice backup and recovery on development databases.


How to improve performance of SP with many optional filtering parameters? I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem and the way to solve it is through dynamic SQL. Found it in his slides http://blogs.msdn.com/b/turgays/archive/2013/09/07/sqlsaturday-243-cape-town-top-10-performance-tips-for-sql-server-developers.aspx

So my questions is it the only and the best approach or are there other options?

WHERE (@EmployeeFirstName IS NULL OR emp.First_Name LIKE @EmployeeFirstName )
AND (@EmployeeLastName IS NULL OR emp.Last_Name LIKE @EmployeeLastName )
AND (@EmployeeNetworkLogOn IS NULL OR emp.Network_ID LIKE @EmployeeNetworkLogOn )
AND (@EmployeeStatus IS NULL OR emp.Employee_Status = @EmployeeStatus )
AND (@EmployeeType IS NULL OR emp.Employee_Type = @EmployeeType )
AND (@SupervisorFirstName IS NULL OR sup.First_Name LIKE @SupervisorFirstName )
AND (@SupervisorLastName IS NULL OR sup.Last_Name LIKE @SupervisorLastName )
AND (@SupervisorNetworkLogOn IS NULL OR sup.Network_ID LIKE @SupervisorNetworkLogOn)

Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it.

Since you will be sending many varying queries to the server, it is best used in combination with the Optimize for Ad Hoc Workloads server setting. This prevents any plan from being fully cached and taking up valuable memory until the same query text has been submitted twice. And if you are seeing wide deltas in performance due to parameter sniffing, you may consider adding OPTION (RECOMPILE) to the query in all cases anyway (or at least in the cases that end up with huge plan skews). For example, you probably won't see any skew when the parameter is the PK and the query ends up being an exact match using a seek. But you might see greater skew when a datetime range is being used, or an exact match to a column with its own asymmetrical skew, and particularly when stats are not being religiously maintained (or no indexes support those specific queries).


New query taking over 60% of running time As part of a daily automated reporting system, we run a series of queries to check different operating parameters from our customers' assets tables.

Last Tuesday I added a new check that works, but triggered a few performance alarms that led me to analyze what might be wrong and I ended up realizing that the new check actually increased the overall checks time about 160%.

Drilling down this new check I narrowed it to actual DB query that is affecting the whole process:

select case COALESCE(max(driverid),-1) when 0 then false when -1 then null else true end
from reports.avl_historico_354676052451142
where DATE_PART('day', now() - fecha ) < 5;

And when doing an explain analyze verbose, I get:

Aggregate  (cost=75004.56..75004.58 rows=1 width=4) (actual time=544.460..544.461 rows=1 loops=1)
  Output: CASE COALESCE(max(driverid), (-1)) WHEN 0 THEN false WHEN (-1) THEN NULL::boolean ELSE true END
  ->  Seq Scan on reports.avl_historico_354676052451142  (cost=0.00..73782.38 rows=488873 width=4) (actual time=535.565..541.467 rows=9661 loops=1)
        Output: fecha, latitud, longitud, altitud, velocidad, cog, nsat, tipo, utc_hora, fix_fecha, imei, registro, input1, input2, input3, input4, hdop, adc, ignicion, adc2, power, driverid, ibutton2, ibutton3, ibutton4, trailerid, adc3, adc4, horometro, odometro, panico, bateria, bateriaint
        Filter: (date_part('day'::text, (now() - avl_historico_354676052451142.fecha)) < 5::double precision)
        Rows Removed by Filter: 1457143
Planning time: 0.078 ms
Execution time: 544.582 ms

So I'm guessing that this query might need some kind of help / improvement, but I'm completely clueless about it. So I'm asking: what improvements would you recommend for this scenario? Indexes? Query rephrasing? Others? What else should I provide for deeper understanding of the model?

Please consider that the sample table in the query has a large number in its name, which stands for a deviceId and we have around 30k tables of those which are queried regularly...it's some kind of a table partitioning model.

Update!

New results from the indexing suggested by @a_horse_with_no_name :

Aggregate  (cost=5478.72..5478.74 rows=1 width=4) (actual time=8.302..8.302 rows=1 loops=1)
  Output: CASE COALESCE(max(driverid), (-1)) WHEN 0 THEN false WHEN (-1) THEN NULL::boolean ELSE true END
  ->  Index Scan using reports_avl_historico_354676052451142_index_fecha on reports.avl_historico_354676052451142  (cost=0.43..5473.68 rows=2017 width=4) (actual time=0.026..4.644 rows=12249 loops=1)
        Output: fecha, latitud, longitud, altitud, velocidad, cog, nsat, tipo, utc_hora, fix_fecha, imei, registro, input1, input2, input3, input4, hdop, adc, ignicion, adc2, power, driverid, ibutton2, ibutton3, ibutton4, trailerid, adc3, adc4, horometro, odometro, panico, bateria, bateriaint
        Index Cond: (avl_historico_354676052451142.fecha > (('now'::cstring)::date - 5))
Planning time: 0.181 ms
Execution time: 8.332 ms

I'll time the overall execution and keep you posted.

The expression where DATE_PART('day', now() - fecha ) < 5 can not make use of an index on the column fecha.

Rewrite the expression so that it can use an index:

where fecha > current_date - 5


Generic SQL Job Scheduler for multiple RDBMS's? I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex dataflows between the databases. Right now, the scheduling is done through independent systems, but I want to have one scheduler that controls the dataflows from beginning-to-end, and is able to script flows from MS SQL to RedShift, etc. Is there a system that can accomplish this already? I'm not a DBA, so I am guessing someone has had this problem before...

Thanks in advance!

EDIT: So one of our dataflows might look like this - file posted on SFTP --> run normal ETL routines -->
compile final complete file --> send to customer/push to S3 --> Run SQL commands on Redshift to load* --> Nightly batch processing on RedShift* --> Unload to S3* --> Load into MySQL*

*These are manually run using a tool that just connects via jdbc (can't remember the program)

My DB-related experience is very light, so I was about to write some python scripts and schedule them in CRON, but that is custom and hard to expand - surely someone has had this problem before. We would like to be able to see a status of the job in one place, create new dataflows/ETL's between all three systems (like an SSIS job).

What you are looking for is an Enterprise Job Scheduler.  Computer Associates has a good one called AutoSys.  There's also JAMS, OpCon, Tivoli as well as others.  There's a decent list available on wikipedia.  I've only had experience with AutoSys, and it's expensive, but VERY powerful, but it takes some work to get setup.  I don't know the cost or complexity of the other options.

These sort of schedulers allow you to schedule commands to run on just about any OS or application that the scheduler supports without using the native schedulers (so you don't need SQL Agent or anything).


Balancing history with performance This is a general design question, not related to a specific DBMS.

I will use a basic employee record to illustrate.  I'm not worrying about the specific syntax, I am asking for help with the approach.

CREATE TABLE employee(
id SERIAL,
name TEXT,
manager_id INT4,
modified TIMESTAMP DEFAULT NOW(),
modified_by INT4
);

Some ways to track changes to the manager_id in the employee table -

A database trigger to an audit table.  Requires no additional application code, but must be kept current with the source table to be of value
An audit log file that saves only the changed values in a JSON string.
Move the manager_id into a separate table and add an entry for every change.  The separate table would have an id, employee_id, manager_id and perhaps a current flag.  Retrieval would mean the whole table would have to be searched for the most current manager.

I can't use an ORM, which is what I would really like to do.

My question:

Is it better to keep a distinct, single record of the current data or sift through a table with history of the data's state each time?

If you want to know who was an employee's manager back in 2015, you need a separate table for that; employee_id, employee_id_of_manager, start_date, end_date.

The relationship may be more complex; what if a manager takes paternity leave?  Or job-shares, so that two or more people are sitting at that desk in rotation?

Having said that, a separate table of relationship history AND a column of employee_id_of_manager in the employee table may be a useful design.  For the job share or paternity leave, it may be better that the "manager_id" doesn't represent one person, but, for instance, the id is "John" but the database also knows that "John" is on leave and "Andy" is the replacement for "John".  Or "Peter and Paul" is the replacement, a composite person whose distinct parts are "Peter" and "Paul".

If we forget about employees and managers, and make the question about generally tracking changes to data, then it does become just a generic question about preserving an audit trail and history of data.  Of course, in a Microsoft SQL database for instance, when you update the manager_id then any previous value is destroyed and can't be recovered (except by the FBI or CIA who have been tapping all your data anyway ever since "Operation Paperclip").  The data is gone, unless you have in place any of the systems you refer to, or you have enabled the other feature in SQL Server 2016 which does the same thing but I've forgotten what it's called.  "Backup"?  No, it wasn't that, but that works too......

You could run an overnight job which looks for data which has changed, and copies it to a history table then - actually, that sounds like "Replication".

Some data you'd want to protect this way, some you wouldn't, so, you could split the table into two tables, one of which gets the audit treatment.  And gets the "Everlasting Transaction Log" or "Infinite Undos" facility applied to it.

I would prefer to design the historic record keeping specifically, but, if your SQL Server has an automatic tool for it (and we've covered several, with less or more "automatic" involved), then you may as well use that... maybe.

Another condition though that may apply to holding people's personal data - you may be under a legal obligation to -not- keep data that is old and no longer relevant.  To destroy data that, in fact, you have no good reason to keep.  This won't apply if you ARE the FBI or the CIA, of course.

Otherwise, this is awkward if your data retention plan consists of a 50 year cellar of backup tapes, and the law says that you need Giant Magnetic Bridget to pay you a visit.  (Please look up Giant Magnetic Bridget on Google.  She's an interesting subject that I learned about recently.)

So... maybe plan for necessary selective deleting of old records.

You could encrypt, and throw away the key to make certain data unusable - but then someone cracks the encryption, inevitably.  So the data can be read again.
 There is another interesting subject called a "One Time Pad", but the authentic thing is expensive and impractical - although simple: you use a file of random bytes the same size as your database, and every data byte that you store is first transformed by "Exclusive Or" (xor) operation with the corresponding random byte.  To read the data out, perform the "Exclusive Or" again.  To "destroy" the stored data, destroy the random data bytes.  That code can't be broken... but your system may have other points of leakage in it.  But in order to "delete" your copy of the data and all its backups, it'll do.

Although, in Microsoft SQL Server, the bytes in the file that represent one employee's data aren't fixed in place - so you'd need to invent a way to do that.


ORA-00942 when trying to select from gv$session inside of procedure I have been tasked with allowing developers to kill their own sessions in the Development database. I've written a procedure, but for some reason I'm getting PL/SQL: ORA-00942: table or view does not exist when trying to call gv$session. Here's the proc, being compiled as SYSTEM. Running on an 11.2.0.4.3, 2 node RAC database:

create or replace procedure kill_dev_session (v_osname in varchar2)
is
  lv_env     VARCHAR2(30 char);
  li_inst_id number;
  li_sid     number;
  li_serial  number;
begin
  select replace(global_name, '.DOMAIN.COM', '')
    into lv_env
    from global_name;

  select inst_id, sid, serial#
    into li_inst_id, li_sid, li_serial
    from sys.gv$session
   where upper(osuser) = upper(v_osname)
  group by inst_id, sid, serial#;

  EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION'||''||li_sid||','||li_serial||','||li_inst_id||'''';

  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => lv_env||'@db01.domain.com',
              recipients => 'dba@domain.com',
              subject => 'Session killed by '||v_osname||' user in '||lv_env,
              message => 'This user killed a session using the kill_dev_session procedure.',
            mime_type => 'text; charset=us-ascii');

end;
/

So here's the rub: every single developer logs in with the same user, so I can't really use username. I don't want to open up blanket alter system for every single user. This would allow a dev manager to pass in a username (i.e. jsmith) and kill that session, and would email us DBAs that this happened, so we can see if the devs are doing this a lot.

Any thoughts as to why I'm seeing this:
    Errors for PROCEDURE KILL_DEV_SESSION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/3     PL/SQL: SQL Statement ignored
14/10    PL/SQL: ORA-00942: table or view does not exist

Thanks in advance!

Try doing this:

GRANT SELECT ON gv_$session TO system;

Because you are accessing a sys table by system in a stored
procedure you need a direct grant on the underlying table.


select rows having condition met for group (without temporary table) Having the table with 3 columns:

ID  category    flag
1       A       1
2       A       0
3       A       0
4       B       0
5       C       0

I want to select all rows that have flag = 1 at least once per category.

Expected results:

ID  category    flag
1       A       1
2       A       0
3       A       0

It can be solved using a temporary table like this:

select ID into #tempTable from someTable where flag = 1
select * from someTable join #tempTable on someTable.ID = #tempTable.ID

But I'd prefer a solution with grouping, which I struggle to came up with. Any help will be appreciated.

GROUP BY cannot be used alone because it only returns 1 row per group (category).

You can use a sub query with flag = 1 and INNER JOIN:

SELECT d1.ID, d1.category, d1.flag
FROM data d1
INNER JOIN (
    SELECT DISTINCT category FROM data WHERE flag = 1
) d2
    ON d2.category = d1.category ;

You can use the EXISTS clause:

SELECT d.ID, d.category, d.flag
FROM data d
WHERE EXISTS (
    SELECT 1 FROM data WHERE flag = 1 AND category = d.category
) ;

You can use IN clause (although EXISTS is better):

SELECT d.ID, d.category, d.flag
FROM data d
WHERE d.category IN (SELECT category FROM data WHERE flag = 1) ;

You can also use CROSS APPLY with a sub query on flag = 1:

SELECT d.ID, d.category, d.flag
FROM data d
CROSS APPLY (
    SELECT TOP (1) category
    FROM data
    WHERE flag = 1 AND category = d.category
) ca ;

DISTINCT aren't needed if, for each category, only 1 row can have flag = 1.

Output:

ID  category    flag
1       A       1
2       A       0
3       A       0