Append hint with autotrace I'm doing a direct-path insert which executes successfully. When I try to autotrace the execution, I get the ORA-12838: cannot read/modify an object after modifying it in parallel.

SQL> insert into emp_big select * from emp_big_temp;

411843 rows created.

Elapsed: 00:00:00.89

Execution Plan
----------------------------------------------------------
Plan hash value: 3203427748

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |   357K|    45M|  1164   (1)| 00:00:14 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP_BIG      |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | EMP_BIG_TEMP |   357K|    45M|  1164   (1)| 00:00:14 |
-----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         15  recursive calls
      31909  db block gets
      12829  consistent gets
          0  physical reads
   34685216  redo size
        845  bytes sent via SQL*Net to client
        802  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     411843  rows processed

SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.18
SQL>
SQL> insert /*+ append */ into emp_big select * from emp_big_temp;

411843 rows created.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
          0  recursive calls
       4438  db block gets
       4283  consistent gets
          0  physical reads
       7648  redo size
        829  bytes sent via SQL*Net to client
        816  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     411843  rows processed

Questions:

Table emp_big has degree of parallelism set to 1, according to select table_name, degree from user_tables. Why does Oracle force parallel insert?
Why does using autotrace trace cause ORA-12838?

Answer to the original output:
This is not caused by autotrace. You will get the same error even without autotrace, just by repeating the same direct path insert. Issue a commit after the first insert.
This is a documented restriction: ORA-12700 to ORA-19400

ORA-12838: cannot read/modify an object after modifying it in parallel
Cause: Within the same transaction, an attempt was made to add read or
modification statements on a table after it had been modified in
parallel or with direct load. This is not permitted.
Action: Rewrite
the transaction, or break it up into two transactions: one containing
the initial modification and the second containing the parallel
modification operation.

Answer to the new output, after edit:
Ok, so you have a rollback, and you started a new transaction. In this case, autotrace really is the culprit, indirectly. The above rule still applies, and as the above description says, even a read reference will cause the above error. If autotrace is enabled, it implicitly runs something like this (after your original statement was finished):
EXPLAIN PLAN SET STATEMENT_ID='PLUSxxxxxx' FOR insert /*+ append */ into emp_big select * from emp_big_temp;
You can easily confirm this by enabling the errorstack trace for the error 12838 (it is fine in a sandbox environment for this short demo, but this is not how I would do this on a real database):
alter system set events '12838 trace name errorstack level 3';
If I run this now:
SQL> create table t1 as select * from dba_objects where 1=2;

Table created.

SQL> alter system set events '12838 trace name errorstack level 3';

System altered.

SQL> set autotrace on
SQL> insert /*+ append */ into t1 select * from dba_objects;

20079 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

I will find the error and a trace file in the alert log. The trace file contains this:
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-12838: cannot read/modify an object after modifying it in parallel
----- Current SQL Statement for this session (sql_id=5x12x8czsd3t9) -----
EXPLAIN PLAN SET STATEMENT_ID='PLUS730007' FOR insert /*+ append */ into t1 select * from dba_objects

And that is what causes the error, because it is still the same transaction. You can test it even without autotrace:
SQL> rollback;

Rollback complete.

SQL> set autotrace off
SQL> insert /*+ append */ into t1 select * from dba_objects;

20079 rows created.

SQL> explain plan for insert /*+ append */ into t1 select * from dba_objects;
explain plan for insert /*+ append */ into t1 select * from dba_objects
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


How does Amazon create the RDS for Postgres? Some kind of Docker? I am really curious about the infrastructure of Amazon. When I create a new instance of RDS, it gives me a host, username, password.. Everything. Behind the curtains, how do they do this? It´s some infrastructure like Docker to run multiple instances of PostgreSQL? How do I replicate this?

My problem is: I have many users and I would like to let then manage their own tables. I thought that it could be done with schemas, but they still can see the other users tables, and I dont want that. Any sugestion?

Amazon doesn't talk about this much and the servers are intentionally locked down, so it's hard to be completely sure.

They're EC2 instances that run a custom AMI and have automation tools - in-house, or something like Puppet/Chef/etc. These automation tools communicate with the AWS control panel over web service APIs, SSH push access, etc, and are responsible for managing the PostgreSQL configuration, starting/stopping/reloading the server, etc.

Each EC2 instance runs a single PostgreSQL database server, with its own users, roles, etc.

It's basically just a sealed AWS EC2 instance that you don't have much access to, you just get a locked down non-superuser PostgreSQL connection. Nothing magic.

This isn't the only way to do it. Heroku used to use OpenVZ on top of EC2 to partition EC2 instances into smaller containers, for example. I think these days they always have one EC2 instance per database though.

It sounds like what you want is multi-tenant hosting. You have many options for this:

One server per user with a single PostgreSQL instance on each server (EC2 or Heroku style)
one PostgreSQL instance per user on a single host server;
one database per user on a single PostgreSQL instance;
one schema per user in a single PostgreSQL database;
a single set of tables with your application limiting access to data within the tables based on enforced WHERE clauses or row-level security policies.

Which to choose depends on trade-offs involving isolation of users, performance, and cost.

There aren't currently any convenient canned recipes to do this that I know of, but searching for "multi-tenant postgresql" will help you find more information.


Easiest way of shutting down a PostgreSQL server on Ubuntu According to pg_lsclusters I have 2 servers running on Ubuntu 14.04:

    Ver Cluster Port Status Owner    Data directory               Log file
    9.1 main    5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
    9.3 main    5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

What is the easiest way of shutting down the 9.1 server forever?

Using:

    $/usr/lib/postgresql/9.1/bin/pg_ctl stop

I get the error:

    pg_ctl: kein Datenbankverzeichnis angegeben und Umgebungsvariable PGDATA nicht gesetzt

(no database directory given and environment variable PGDATA not set)

What is the meaning of this error?

I decided to go the route of a fresh install. So instead of trying to figure out what postgresql-command will shut down the server I used:

   sudo apt-get purge postgresql-9.1

and:

   sudo apt-get purge postgresql-9.3

A fresh install might be an easier solution to some of my other problems I had since upgrading to Ubuntu 14.04.

I found this at:

https://askubuntu.com/questions/451569/postgresql-broken-after-14-04-despite-succesful-pg-upgrade-solved


Postgresql unique history I am running several commands, some repeatedly, from the postgresql command line tool.  In Bash, the history function keeps one command for each set of sequential identical commands, it seems that postgresql command line does not do that by default.  So for example if I run command 'a' once, then command 'b' 100 times, I have to scroll up 100 times before running command 'a' again.  This is a user-unfriendly way of going about things.  Is there an option to have alternate, bash-like functionality from the postgresql command line, in which only unique commands are remembered in the history?

psql has a variable called HISTCONTROL. Quoting the manual:

  HISTCONTROL

  If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of
  ignoredups, lines matching the previous history line are not entered.
  A value of ignoreboth combines the two options. If unset, or if set to
  any other value than those above, all lines read in interactive mode
  are saved on the history list.

I have this configured to automatically take effect on psql startup by adding the line

\set HISTCONTROL ignoredups

to my .psqlrc.


MySQL: Capture every 5 values from column a in Table A to create one row in Table B I have submissions of a form from a web page records in a database.
the table has the following schema

submit_time    form_name   field_name  field_value field_order file

the form has 5 fields, each form submission results in 5 rows in the previous table, one row per field.
the following is an example of 5 rows created after a submission.

    submit_time       form_name     field_name  field_value field_order file
    1427715050.2890 Contact form 1  your-name       Jung            0   NULL
    1427715050.2890 Contact form 1  your-email      Jung@Jung.Jung  1   NULL
    1427715050.2890 Contact form 1  your-subject    Jung maybe :)   2   NULL
    1427715050.2890 Contact form 1  your-message    Jung :D         3   NULL
    1427715050.2890 Contact form 1  Submitted Login Mohammad    9999    NULL

I want the data to be written in another table in another database the new table has the following schema:

Name        email       subject         message         Login

What I tried
I thought about writing a trigger like the following:

CREATE
    TRIGGER copy_data
    AFTER INSERT
    ON TABLE_A FOR EACH ROW
    BEGIN
    # check if submission entry exists in table B

    # NO :
        # INSERT the data in the corresponding column

    # YES:
        # UPDATE the data in the corresponding column

    END

but I can't insert NULL values in the second table.

what can I do in such situation ?

Note: I can't guarantee the order of the rows in Table A ( which field will be inserted first is something I can't control)

The TRIGGER can be simplified down to

INSERT ... ON DUPLICATE KEY UPDATE ...

It is not proper to have "redundant" data in a database.  Why can't you just leave it in the original table?  When you need it, do a JOIN.


SQL Server - Check for opened Activity Monitor I'm looking for a way to send an alert when a DBA tool is connected for "too long" on a SQL Server Production database.

For most of the tools I listed out, I can find the program name with the following query.

select
    login_time,
    host_name,
    program_name,
    login_name,
    nt_domain,
    nt_user_name,
    last_request_start_time
 From sys.dm_exec_sessions
 where host_name is not null

However, I can't find the Activity Monitor tool even though it's running. I use a SQL Server 2012 Management Studio and I run it against a SQL Server 2012 instance.

Do you have an idea about the reason I can't see it ?
Does the Activity Monitor do "connect/disconnect" to show its content ?

If it's the case, it's absolutely normal I can't find it there and I would have to change my way to check it with an extended event or a logon trigger...

The Activity Monitor shows up as a standard user session, "sleeping" in tempdb (database_id = 2, generally), with the program_name of "Microsoft SQL Server Management Studio".

It only queries at the rate of it's refresh window, but those identifiers, plus the total_elapsed_time, should make it feasible to find your idling monitors.


How to avoid lock wait timeout exceed and improve MySQL InnoDB write speed I ran a multi-threaded client spawning 25 threads to make concurrent API calls and insert data to AWS Aurora server.

After some time, I started to see timeout error: lock wait timeout exceeded try restarting transaction.We run the same test for a server running MySQL 5.6.10, and no lock wait timeout happened.

Is there a way to avoid this timeout ?

On the AWS Aurora server, SHOW ENGINE INNODB STATUS showed:

---TRANSACTION 8530565676, ACTIVE 81 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 405, OS thread handle 0x2ae270b03700, query id 11045 10.50.101.56 app_migration
INSERT INTO contacts_contactaudit (action,
    contact_id,
    date_created,
    date_updated,
    external_contact_id,
    entity_name,
    first_name,
    last_name,
    middle_name,
    actor_created_id,
    actor_updated_id,
    email,
    phone_number_id,
    external_contact_guid,
    external_shared_contact_id,
    active_timezone, audit_date)
SELECT 'I' as action, new.id,
    new.date_created,
    new.date_updated,
    new.external_contact_id,
    new.entity_name,
    new.first_name,
    new.last_name,
    new.middle_name,
    new.actor_created_id,
    new.actor_updated_id,
    new.email,
    new.phone_number_id,
    new.external_contact_guid,
    new.external_shared_contact_id,
    new.active_timezone, now();

This is the trigger we created for INSERTs statement:

CREATE TRIGGER contacts_contact_insert_audit
AFTER INSERT ON contacts_contact
FOR EACH ROW
    INSERT INTO contacts_contactaudit (action,
    ...
    audit_date)
SELECT 'I' as action, new.id,
    ...
now();

And this is the audit table schema:

  CREATE TABLE `contacts_contactaudit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` datetime(6) DEFAULT NULL,
  `date_updated` datetime(6) DEFAULT NULL,
  `action` varchar(1) NOT NULL,
  `audit_date` datetime(6) NOT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `external_contact_id` bigint(20) DEFAULT NULL,
  `entity_name` varchar(128) DEFAULT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `middle_name` varchar(128) DEFAULT NULL,
  `actor_created_id` int(11) DEFAULT NULL,
  `actor_updated_id` int(11) DEFAULT NULL,
  `email` varchar(256) DEFAULT NULL,
  `phone_number_id` int(11) DEFAULT NULL,
  `external_contact_guid` varchar(128) DEFAULT NULL,
  `external_shared_contact_id` bigint(20) DEFAULT NULL,
  `active_timezone` varchar(128),
  PRIMARY KEY (`id`),
  KEY `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` (`actor_created_id`),
  KEY `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` (`actor_updated_id`),
  KEY `contacts_contactaudit_contact_id_9b809fe7_uniq` (`contact_id`),
  CONSTRAINT `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` FOREIGN KEY (`actor_created_id`) REFERENCES `actors_actor` (`id`),
  CONSTRAINT `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` FOREIGN KEY (`actor_updated_id`) REFERENCES `actors_actor` (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=21577
DEFAULT CHARSET=utf8;

We have identified the root cause: it's the innodb_autoinc_lock_mode = 1 .

Here is the summary from the official doc:

0:  traditional lock mode, provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics.
1: consecutive lock mode: In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time
2: interleaved lock mode: In this lock mode, no “INSERT-like” statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.

In our case, when multiple API called INSERT statements, if one API call is prolonged, that INSERT statement would hold a TABLE lock on the destination table, which then causes the timeouts.

We switch it to innodb_autoinc_lock_mode = 2, restart the server and voila.  Now we can call multiple APIs to insert into the same table without having time-outs.


Psycopg2 - PGBouncer - Postgresql > Server does not support SSL but SSL was required I've setup my Django application to use SSL while connecting to the Postgresql database via pgbouncer. I'm using Psycopg2 library.

I've done this before successfully, so I just did the same steps again.

But! After installing certificates to both servers and clients and making the installations, when I tried to run my application, I've got the error:

django.db.utils.OperationalError: server does not support SSL, but SSL was required

When I entered the command

psql --set=sslmode=verify-full -h DBHOST -p DBPORT -U USERNAME DBNAME

I can successfully connect to database by entering my password

But when I write the command like this;

psql "sslmode=verify-full host=DBHOST dbname=DBNAME port=DBPORT user=USERNAME"

or when I entered the code from python shell

import psycopg2
conn = psycopg2.connect(database='DBNAME', user='DBUSER', password='DBPASS', host='DBHOST', port='DBPORT', sslmode='verify-full')

I got the stack trace;

/virtual_environment_path/local/lib/python2.7/site-packages/psycopg2/__init__.pyc in connect(dsn, connection_factory, cursor_factory, **kwargs)
    128
    129     dsn = _ext.make_dsn(dsn, **kwargs)
--> 130     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
    131     if cursor_factory is not None:
    132         conn.cursor_factory = cursor_factory

OperationalError: server does not support SSL, but SSL was required

What may be the problem? I've compared the installated packages between previous installation which is succesful, versions of packages, certificates, file permissions etc. But I'm stuck in this issue.

Thank you very much for your help.

The reason that this command worked:

psql --set=sslmode=verify-full -h DBHOST -p DBPORT -U USERNAME DBNAME

Is that --set just creates a user-defined variable inside the psql program with the name of 'sslmode'.  That name is not special to psql, it does nothing with your connection options and you just connect without ssl.


Max Connection Pool capped at 100 I'm running SQL Server 2008 R2 SP1, on a Windows Server 2008 box. I have a .NET script running from Visual Studio 2010 that does the following:

Reaches into the database
Makes a change
Iterates

The total number of times it will iterate is 150, however it is stopping at 100 connections and I can't figure out why. I could adjust my script to just use a single thread, but I'd prefer to know where I'm missing a max connection setting as that will be more useful to know for future reference.

Here's where I've checked so far:

SQL Connection String in Visual Studio 2010 (it's set to 1000)
SSMS Database instance connection properties (it's set to 0 [infinity] user connections)
Googled some information on Server 2008, it looks like it can handle more than 100 connections
Stepped through my code alongside SP_WHO2 which gives more information on logical connections, seeing that the # of connections starts at 52 and the script errors with the "Max Pooled Connections reached" error at 152 logical connections.
Changed the connection string to use Data Source=PerfSQL02;Initial Catalog=Masked;Integrated Security=True;Max Pool Size=1000

I'm not sure where else to check, I know I have a lot of moving parts here but I'm getting the feeling I'm just missing a max pool setting somewhere.

In your connection string, specify max pool size=<your desired max pool size>.  So in other words, if you want to change max pool size to the value 500, your connection string could resemble this:

"data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500"

Obviously I'm assuming so much with your other parameters, but this should give you a good idea how to proceed.  Connection pooling is client-side provider enforcement.  It's the client that will need to specify this max pool size setting, via the connection string.

Also, make sure you are closing or disposing of your connections properly, otherwise you will tie up connections.  Something like this (C#):

string connectionString = "data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500";

using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    using (SqlCommand sqlCmd = new SqlCommand())
    {
        sqlCmd.Connection = dbConn;
        sqlCmd.CommandText = "select 1;";

        // ... so on and so forth
    }
}

The using block (in C#) calls IDisposable.Dispose() upon completion.  You could also implement SqlConnection.Dispose() or SqlConnection.Close() in the finally block of a try/catch/finally block.

Reference:  MSDN Documentation on SqlConnection.ConnectionString property


SQL Server 2014 SP3 Unexpected Service Stop SQL Server 2014 SP3 CU1 service terminates automatically with the error,

  Error: 17311, Severity: 16, State: 1.   SQL Server is terminating
  because of fatal exception c0000005. This error may be caused by an
  unhandled Win32 or C++ exception, or by an access violation
  encountered during exception handling. Check the SQL error log for any
  related stack dumps or messages. This exception forces SQL Server to
  shutdown. To recover from this error, restart the server (unless
  SQLAgent is configured to auto restart).

There are multiple instances on the server, but I am having this problem in one instance.

There's not much information in the logs to say why. Do you have any similar problems? What can I do in more detail to find the problem?

You might see a stack dump in error log. You might have hit a bug and would need to go through the logs to find more on it. You might need to apply latest patch CU4. The error message is documente in below link :

SQL Server reports errors for exceptions, assertions, and hang conditions

Too many mights above as these errors are quite tricky and never have just one cause/solution.


Why would a single ghost record remain in a few random indexes? I used the ghost_record_count column to find indexes/partitions that have ghost records. I found a few that have one such record. I then tried to queue them up for processing by scanning all pages:

SELECT COUNT_BIG(*)
FROM T WITH (READUNCOMMITTED, INDEX(idx))
OPTION (MAXDOP 1)

I then waited at least 10 seconds to let the ghost cleanup task run. But the ghost records do not disappear. I also tried restarting the server. No ghost related trace flags are in use.

This is not an actual problem that I'm having. I'm trying to understand ghost cleanup in general. Why do the counters not drop to zero in tables that have no writes?

You can find the answer in this article Inside the Storage Engine: Ghost cleanup in depth

When a record is deleted, apart from it being marked as a ghost
record, the page that the record is on is also marked as having ghost
records in one of the allocation maps – the PFS page (post coming
soon!) – and in its page header. Marking a page as having ghost
records in a PFS page also changes the database state to indicate that
there are some ghost records to cleanup – somewhere. Nothing tells the
ghost cleanup task to clean the specific page that the delete happened
on – yet. That only happens when the next scan operation reads the
page and notices that the page has ghost records.

So it's not cleaned up until the page is read again


Availability Group reporting disconnected replica I'm trying to configure availability groups in a VM environment so I can run some tests.

I think I've got the group created correctly, I can see the Always on group on both servers. However when I look at the dashboard for the group it has the following error

  Availability replica disconnected
  This secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

I've checked the endpoints on both servers and they look correct. There are no firewalls running and both servers can see each other. What's the best way to debug this sort of error?

Below is the TSQL I used to set all this up

Primary Server

CREATE ENDPOINT dbm_endpoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=7022)
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

Secondary Server

CREATE ENDPOINT dbm_endpoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=5022)
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

Primary Server

CREATE AVAILABILITY GROUP AG1
    FOR
        DATABASE TestDb
    REPLICA ON
        'SQL1' WITH
            (
                ENDPOINT_URL = 'TCP://sql1.sql.sandbox.net:7022',
                PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE),
                SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY),
                AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                FAILOVER_MODE = MANUAL
            ),
        'SQL2' WITH
            (
                ENDPOINT_URL = 'TCP://sql2.sql.sandbox.net:5022',
                PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE),
                SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY),
                AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                FAILOVER_MODE = MANUAL
            );

Secondary Server

ALTER AVAILABILITY GROUP AG1 JOIN;

Obviously I also restored the primary database to the secondary server as well.

One thought, I didn't install the SQL Agent on either server, I'm guessing this is not needed for always on availability groups?

This question is very old and likely no more troubleshooting will be possible, however for those of you stumbling upon this question having the same issue the following may be of some help. This isn't meant to be a complete list of all issues that could occur, but it will hit the main ones that do.

When the secondary replica is marked as disconnected by the primary, this means the primary hasn't heard or can't contact the secondary replica over the database mirroring endpoint. There are various reasons for this:

Endpoint configuration

One of the most common causes are misconfigured endpoints for the Database Mirroring role. There are various options and configurations that can be used and sometimes, especially when created by hand, those configuration options do not match. Take the OPs example:

CREATE ENDPOINT dbm_endpoint
    STATE=STARTED
    AS TCP (LISTENER_PORT=5022)
    FOR DATABASE_MIRRORING (ROLE=ALL)

This creates an endpoint called dbm_endpoint on tcp port 5022 for database mirroring. However it leaves out quite a bit of configuration options to which we assume are defaulted but we really do not know. This means the default options such as Windows authentication using either Kerberos or NTLM, it also doesn't specify which IP address (v4, v6, interface) is used, nor does it specify encryption options to which the default is REQUIRED and depending on the version of SQL Serve either RC4 or AES.

That is quite the assumption to make, especially when it isn't working. If you're not sure what the endpoint is set to, join sys.tcp_endpoints with sys.database_mirroring_endpoints to turn the resulting single row to individual configuration options.

There are advanced configurations, for example, that use certificates since they can't use Windows authentication. We don't know if the OP has a proper domain and if these servers are joined. Again, the assumption based off the endpoints is that they are, however we don't know for sure. When using certificates the largest issue is that the same certificate is used on multiple replicas yet the private key is not exported when copying the certificate to import to the replicas. Since the endpoints use the private/public key pair, using the same certificate will require that endpoint at some time to use the private key. Make sure the certificate is exported and imported with both keys if you're using it on multiple replicas.

When specifying the IP Address, the default is ANY which could be any of the IPv4 or IPv6 addresses. Though most places don't register or use IPv6 addresses, I do see it as an issue every once and a while. Checking to see what addresses are registered to a host in your DNS can help narrow the issue. You can also setup a network capture and filter based on the port. Every once and a while an IPv6 address is used and it's improperly setup, disabled, or firewalled on other servers but is returned via DNS.

Availability Group Replica Specification

When creating the availability group each replica will need to be specified and as such will need an endpoint url which will define the communications port (database mirroring endpoint).

Taking the OPs example:

REPLICA ON
        'SQL1' WITH
            (
                ENDPOINT_URL = 'TCP://sql1.sql.sandbox.net:7022',
                PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE),
                SECONDARY_ROLE (ALLOW_CONNECTIONS=READ_ONLY),
                AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
                FAILOVER_MODE = MANUAL
            ),

This replica, called SQL1 has a TCP endpoint on sql1.sql.sandbox.net port 7022. the address seems to be a FQDN and should be checked that the proper DNS lookups, (if using) Kerberos SPNs, and firewall rules exist or are created. If there are multiple interfaces or addresses that are returned on a lookup of the address, then it may make sense to narrow it down to a specific address in the endpoint url for testing or to narrow it down in the mirroring endpoint specification.

SQL Server Permissions

One of the last surface areas for the disconnected issues to appear is the endpoint permissions on each replica. Since each endpoint the account will need the CONNECT permission (similar if using certificates) in order to use the endpoints. This is a fairly common issue, so common that this is marked in the errorlog and gives you the fix in the error message which is to grant the connect permission to the endpoint.

Windows Clustering

Since the replica manager replies upon Windows Server Failover Clustering to be working (not counting Linux, Docker, or Read-Scale) in order to start up, do make sure the cluster service is running on each replica and each node shows an up status in the cluster (Powershell, Failover Cluster Manager, etc.). If the cluster service is not running, then the manager will stop until it comes back up - this will be logged in the errorlog and should be very evident.

Troubleshooting Methodology

When nothing else works or shows up, what do you do?

Availability groups are a distributed system, this means there are many individual parts (replicas) that are distributed among many areas (databases) that function through a common set of protocols (availability groups). Given this, the troubleshooting methodology differs from typical single instance or clustered instance troubleshooting.

Each part needs to be checked against itself and against the overall metadata available. Additionally, each part needs to have the intermediary items checked against each other intermediary (such as firewalls, network cards, load balancers, etc.).

Start by checking the errorlog on each replica as only some errors (due to where the error occurs) are only applicable when the replica is primary or secondary. After an overview of each errorlog, this should point in a direction - if it does not, then most likely the issue is under SQL Server which could mean networking, firewall, OS, etc. System and application logs can be helpful here as a next step.

Look out for NDIS drivers installed on the system as they could be blocking traffic or incorrectly working and dropping traffic. Additionally, do check the interface diagnostics for the network cards as things such as dropped/discarded packets may be an issue if the disconnected state is intermittent.

When all else fails, check every piece of metadata against the other and make sure all match and are the same. In the OPs metadata specified above, one port was on 7022 and the other on 5022. While this in of itself is not incorrect and can be used, it does make issues in other places such as firewall rules where someone can easily misread the port or assume and not read that the ports are the same.

Depending upon skill level and knowledge, a network trace from the primary side and the secondary side would be ideal. This can show the packets leaving the primary/secondary and hopefully arriving at the opposite side. If it does not, an intermediary is not configured correctly - look for firewalls, load balancers, routers, etc., that are stopping the traffic.


SQL DATEDIFF coercion differences between databases on same SQL instance? I have a data coercion mystery. I am seeing two different behaviors from DATEDIFF for the same query, and I cannot understand why.

This is an extract of the relevant part of the query in question, with fixed values. The first value represents "today" in our query, and was set up with the same data type with an explicit CAST. The other value being passed in is from C# DateTime.MinValue. I am aware that it is below the valid SQL DATETIME range, but I am interested in the differences here:

-- dates in ISO 8601 literal format to avoid ambiguity
SELECT DATEDIFF(dd,CAST('2014-03-24' AS SmallDateTime),'0001-01-01')

We have two different databases on the same SQL server instance.

Server one returns an error about the date range of the '0001-01-01':

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Server two returns a value that looks ballpark correct:

-735315

The problematic date is almost certainly '0001-01-01' and it fails as I expect for a datetime, as it is below the minimum SQL datetime of Jan 1, 1753.
(https://msdn.microsoft.com/en-us/library/ms187819.aspx)

According to the MSDN page for datediff (https://msdn.microsoft.com/en-US/library/ms189794(v=SQL.105).aspx), it can accept the following values:

 startdate is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset

Cast results for each of these are identical between servers, and are listed here:

SELECT CAST('0001-01-01' As time) -- works: 00:00:00.0000000
SELECT CAST('0001-01-01' As date) -- works: 0001-01-01
SELECT CAST('0001-01-01' As smalldatetime) -- error: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As DateTime) -- error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('0001-01-01' As datetime2) -- works: 0001-01-01 00:00:00.0000000
SELECT CAST('0001-01-01' As datetimeoffset) -- works: 0001-01-01 00:00:00.0000000 +00:00

The error explicitly calls out a failed DateTime conversion, so that appears to be the coercion of choice on database one.

It seems database two uses a different coercion that succeeds and does the datediff math correctly.

Because both of these databases are on the same SQL instance, I am ruling out instance settings.

Here are a few database settings we thought to check, and they appear identical between the two databases as well (checked in SQL Server Management Studio):

Database Collation (should be per server, but included for clarity):

(database) > Right Click > Properties > General > Maintenance > Collation
Database one: SQL_Latin1_General_CP1_CI_AS
Database two: SQL_Latin1_General_CP1_CI_AS

Date Correlation Optimization Enabled:

(database) > Right Click > Properties > Options > Misc. > Date Correlation Optimization Enabled
Database one: False
Database two: False

Two Digit Year Cutoff:

(database) > Right Click > Properties > Options > Containment > Two Digit Year Cutoff
Database one: 2049
Database two: 2049

User options date format

DBCC USEROPTIONS
Database one, dateformat: mdy
Database two, dateformat: mdy
(other settings appear identical)

I'm happy to provide other settings, or test query results, let me know what you'd like to see.

Why are the two databases behaving differently for this identical query? Why does it appear that the coercion chosen is different?

This will happen if you are in a lower compatibility level (80) and indirectly try to convert a value that is outside the supported range of the older types. I have added an example illustrating exactly this scenario in the following answer:

What is the actual behavior of compatibility level 80?

See the section of my answer entitled Conversions involving new date/time types.


Postgres, RDW, WHERE condition not pushed to remote (JSONB attribute, using postgres_rdw) I have a remote postgres table using RDW. It contains a JSONB column and I use values in that JSONB for the WHERE condition.

The remote table has a GIN index on the JSONB column and an index on the hostname-attribute that I use for filtering.

EXPLAIN ANALYZE VERBOSE
SELECT
*
FROM forein_table
WHERE details->>'hostname' = 'host-xyz'

shows that the "Remote SQL" does not contain the WHERE-clause and filtering is done local.
On the other hand

EXPLAIN ANALYZE VERBOSE
SELECT
*
FROM forein_table
WHERE details@> '{"hostname": "host-xyz"}'

sends down the 'WHERE' clause which is obviously much faster.

The documentation says:

  WHERE clauses are not sent to the remote server unless they use only built-in data types, operators, and functions. Operators and functions in the clauses must be IMMUTABLE as well.

Is there a difference between ->> and @>?

Is there a way to use WHERE details->>'hostname' = 'host-xyz' on a remote table and have it push down the WHERE clause?

(I found a way by creating a VIEW which extracts the hostname attribute as a column, and then creating the remote table on this view...but obviously this is not a very flexible/elegant approach)

I believe the reason for this is that in details->>'hostname', 'hostname' is of a text type, and PostgreSQL cannot prove to itself either that the same default collation is in use on both sides of the FDW, or that the ->> operator will always return the same answer regardless of collation of its right argument.  So it won't push it over.

There has been some talk of adding a way to mark functions and operators as not being sensitive to collation.  But that won't done until at v12 at the earliest.


Make a database inaccessible through "sa" user but should be accessible through another user I have a database BugTracker which is accessible from "sa" user.

I have just created new user AdminBugCatcher. I have also mapped "BugTracker" database with this user. But this database should not be accessible through "sa" user.

How can we achieve the same ?

I have already tried to unlink this database from "sa" user but getting an error "Drop failed for the User "dbo" ".

There is no way to prevent the "sa" login from accessing every database on the server.  If the "sa" login isn't needed (which it shouldn't ever be needed) then disable it.  If it is needed, then there's nothing you can do as anything that you put in place to block it, the "sa" login can remove.


Time Series data for controllers connected to other devices We have a relatively large number of controllers deployed to the field (4500). Each of these controllers have multiple other devices connected to them. These controllers are now going to be able to start sending us time series data. The data is not so much from the controllers as it is of each device that is connected to it. The data is sent every 15 minutes and includes all the parameters of the connected devices. Some of these controllers will have one device connected and some will have 10 devices connected. The controllers also have the ability to add devices down the line and devices can be removed.Each of the devices send data in a JSON format. How should we design SQL tables to handle this? We think since the time series data will grow over time, we can created one table for every controller and store associated data. We are looking for recommendations on how to store the dynamic number of device data that is sent. Since each controller can have a maximum of 40 devices connected, should we create 40 columns for each table? Also if a new device were to be added to a controller, how would we detect it from a database perspective? Thanks all for your help.

I just did a basic calculation: assuming a row size of 200Byte, you'll create more than one TB raw data per year, 3GB per day.
This raised a few questions:
What is your strategy for data compression (e.g. get the min/max values per day/month, store it separately and remove/archive the raw data)?
How will the data be used (what kind of queries, how often)?
How often will controllers be added or removed?
What is the data distribution of devices per controller?

These are the key question in order to modify the (normalized) design concept, if needed.

In a normalized form I'd go with the following design:
tables:
Controllers (ControllerId, ConSerialNumber,ConDescription,...)
Installations (InstallId, ControllerId, InstLocation, InstInstallDate, InstRemovedDate,..) -- to allow a reuse of controllers at different locations
Devices (DeviceId, DevSerialNumber,DevDescription,...)
Device_per_Installation (DpIId,InstallId,DeviceId,DevInstallDate, DevRemovedDate,...)
DeviceData (DpIId, SendDate, Param1, Param2,...) -- I assume you'll shred the JSON data to get the actual values instead of the JSON file as a whole in order to save overhead

Add a controller can be done automatically (without any manual code or even data manipulation): the new data can be inserted in the controllers and installations tables if the information does'n already exist.
In order to deal with the data volume I'd use horizontal partitioning (per Installation, most probably)

Alternatively, you could use a separate table per controller,
But this would either force you to use dynamic SQL or to modify the code whenever a new controller is added. This will reduce the size per table to about 30GB per year, but the overall data volume will still be about 1TB.

Your idea to have one column per device indicates that you're not planning to "shred" the JSON data.
If you ever plan to query those columns to aggregate the values of a specific parameter, it'll take a significant amount of time, since all rows have to be scanned and "shredded" to get the values.
But if you don't plan to query the data anyway, I'd question the need to store it in a database in the first place. Store it on the file system instead...

To summarize:
The database design might be influenced by the specific business case due to the data volume. I recommend to get a database expert involved to help you design the best performing solution, not only for the table design, but for the code to insert/query/aggregate/archive the data as well as database maintenance, too. Otherwise you'll end up with major performance or maintainability issues sooner or later.


Database Inner join For fetching Records I have two table in database. Basically these tables contains the question like subjective/objective. I want to fetch the data from these tables

table_1 :-
question_id        question_value  question-type
1                     test1          1
2                     test2          1
3                     test3          2

Table_2 :-
answer_auto_id   question_id  answer_value
1                   1             ans_sub
2                   2             ans_sub
3                   3             ans_obj_1
4                   3             ans_obj_2
5                   3             ans_obj_3
6                   3             ans_obj_4

result table

question_id     ans1_obj     ans2_obj    ans3_obj     ans4_obj  answer_subj
 1                NULL        NULL         NULL        NULL      ans_sub
 2                NULL        NULL         NULL        NULL      ans_sub
 3              ans_obj_1   ans_obj_2    ans_obj_3    ans_obj_4  NULL

This code will work in tsql- it doesn't even actually require a join between the tables:

    SELECT question_id,
       MAX(ans_obj_1) AS ans1_obj,
       MAX(ans_obj_2) AS ans2_obj,
       MAX(ans_obj_3) AS ans3_obj,
       MAX(ans_obj_4) AS ans4_obj,
       MAX(ans_sub) AS answer_subj
FROM table_2
PIVOT
(MAX(answer_value)
 FOR answer_value IN ([ans_obj_1], [ans_obj_2], [ans_obj_3], [ans_obj_4], [ans_sub])
 ) A
 GROUP BY question_id


Howto (async?) preview intermediate resultset Is it possible to retrieve parts of the resultset from SQL Server queries in advance?
For instance if I run a query that will return 20,000 rows, is it possible to throw the results in packages of 100 to the requesting instance (.net code for instance) and allow using them instead of waiting for all 20,000 rows to be read and returned?

A similar behaviour I have observed using SSMS: The resultset is filled ongoing even before the query is completed. Don't know if this is the same, but I am pretty sure if I run an SqlClient ExecuteNonQuery command, I would have to wait for all data being returned before I can fill e.g. a datagridview.

I do not know the ideal wording for this scenario, maybe this is the reason I can't find similar questions.

I have read about OPTION FAST that seems to have no effect. Does this really address the question?

What you describe is the default behaviour of SQL Server's query execution and client connection protocols. Rows are delivered to the server's network buffer as they are produced from an executing query plan. The network delivers them to the client, which makes them available to the application.

I think the pause you see is caused by how the application consumes the dataset returned. It's been a while, but I think SqlDataReader.Read() allows stream-like behaviour.

The risk is that the application falls behind SQL Server and the connection and server resources remain locked up until the application explicitly releases them.


What is the permission needed to add permissions? I'm creating an automated backup, upload, restore and user creation utility for a process here at my company. I've got everything up until the user creation all set. I've got a freshly restored database that I need to create a user in so that the application can connect to it.

So my problem is two-fold - I've got a user with sufficient permissions over master to restore the database, but I can't seem to figure out what permissions that user needs to be able to USE the newly restored database.

Once I've got that, I think I can use sp_change_users_login to reconcile the user with the database, but I can't seem to crack that.

BTW, if it wasn't clear, this needs to be done through T-SQL, unless it's a one-time permission that can be added through SSMS.

To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role.  That'll give you rights to create a user within the database and give that user permissions within the database.

Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.


SQLite or CouchDB for Locational Data Mining Project? I am currently designing a Data Mining Project where I am going to harvest rather large volumes of Twitter data in order to analyse locational data (geocoded tweets) and do some machine learning with it.

What I want to do: I'll have some scripts that run 24/7 on a small Samsung Netbook (<2GHz,1GB RAM, 200GB Disk), limited in frequency only by the query limit of the Twitter API. These scripts will save various sorts of data in a database, which in turn will later be used as a base for analysing data.

I am quite experienced in RDBMS, thus I also know their limitations. I just read about CouchDB and its ability to store JSON in so called documents - this would come in handy because the responses from the Twitter API are in JSON, and some of those strings are quite nested and complex.

On the other hand, I don't really want to miss relational functionality, since I have for example a table user which saves general data about a Twitter account and a table geo which saves place-time-Tuples which always reference a particular user.

For analysis, the content of geo will be used in any possible way - I have not yet thought about geospatial analysis in depth, but there will aggregation, distance calculation, all that sort of stuff. This might be done with CouchDB's reduce-Functions in Javascript, I read? If I used a SQLite DB, I would just stick to Python and do everything there.

I don't really know what is possible in CouchDB since I am really new to that concept. I just saw that it is easy to store JSON and that the structure of the database is not fixed at all, so I could easily introduce new types of data or destroy old ones (DROP COLUMN is not possible in SQLite). Also, since I know Javascript pretty well (actually better than Python), it might be easier for me to do analysis on the data.

What do you think? Is there a striking advantage in using NoSQL for doing that sort of thing or should I stick to what I can  do best?

I think the answer mainly depends on how much time do you want to spend learning new databases vs how much time to you want to spend learning machine learning.

For example PostgreSQL has lots of GIS stuff built-in which I assume could be very useful for your queries. CouchDB has many useful features with their map/reduce stuff but I find it a bit limited. If you think you're going to add many columns later on based on new algorithms you might want to look into the column-oriented databases like Apache Cassandra.

However, I would recommend splitting the problem into two parts:

Data gathering
Data analysis

The first part you seem to have a good plan for already. I would write a super simple app that would just suck in the data from twitter and put it in a table as a BLOB. Or just a file on disk.

When you have that you can go from the raw data and insert it into any kind of backend. Now you can choose backend based on the current problem (algorithm) you're working on, instead of having one solution to fit all your problems. The key point here is to extract exactly the data you need. That way you shouldn't have to consider the fact that the data is nested inside a document from Twitter as you only pick out the parts you need. If you go that way I think an RDBMS would perform very nice as they can run all kinds of queries.

Make sense?


You confuse what with index  means.  Use of an index in no way directly drives the order of the results.  A table does not have an inherent order.  Even a table with a clustered index may not return results in the order of clustered index.  Now a sort will use an index but the index alone does not force the sort.  In your case the index made the sort efficient and I suspect if you look at the query plan the sort used the index.


The best way to query a partitioned table in Oracle I have a table in an Oracle database, partitioned by a field. In our application we need to query data that is scatered across 2 partitions. Can you please tell me which method is better:

making multiple selects with UNION ALL like:
select * from table where field = 'A' UNION ALL select * from table where field = 'B'
making one query:
select * from table where field in ('A','B')

Oracle will take care of partition pruning when using
** making one query: select * from table where field in ('A','B')

Here is an example:

CREATE TABLE q1_sales_by_region
      (deptno number,
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

explain plan for
select * from q1_sales_by_region
where state IN ('OR','FL')
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2523828506

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |    41 |     2   (0)| 00:00:01 |       |    |
|   1 |  PARTITION LIST INLIST|                    |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL   | Q1_SALES_BY_REGION |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------------------

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

   2 - filter("STATE"='NY' OR "STATE"='OR')

The KEY(INLIST) designation for the partition start and stop keys specifies that an IN list predicate.


Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data? I'm a data scientist who's more on the math and modeling side of things than the engineering side, and I don't know much about the logic, data structures and design principles that underly database design, beyond various platitudes like "Use a relational DB for structured data and a NoSql DB for unstructured data", etc...

I often hear that you have to choose whether a db is going to be used for transactional data that gets frequent updates and requires low latency, or wether it is destined to be more of an analytical database that gets occasional large batch updates and where latency is not a constraint. It is usually implied that there is some tradeoff that has to be decided between going with one or the other?

What I don't understand is why is this tradeoff either/or choice inevitable? I've worked with OLAP cubes (true OLAP platforms, not ROLAP) that could handle near realtime updates and commits to the main data cube from users, as well as batch processing for some of the larger modeling tasks, and could also do realtime slicing and dicing, and aggregation/disaggregation on the fly of various metrics based on predefined aggregation rules and hierarchies.

If such a system is possible (the platforms I worked with have been around since the mid-2000s), why is a backend that can handle both transactional data and analytical data such a difficult to attain objective?

Since there is a limited number of resources (cpu, memory, disk) and you want to use them as efficiently as possible.

For OLTP you typically have many small transactions and concurrency is important. Your data model is typically normalized and most data access is done via index lookup. A subset of your data is used over and over so you want that to be cached in memory.

For OLAP/DW you typically have fewer transactions, but in return, they are larger. Writes are usually done via some ETL process. Your model is often de-normalized and in some sense prepared for your OLAP queries. Keeping all used data in memory is often not possible so it has to be read from disk, sorting is often involved so this memory area is often important here.

Realtime updates to cubes is not a problem as long as there is a limited number of users that update. Throw in millions of users from the OLTP scenario, concurrency will come in to play.

That said, most systems I've seen contains a little bit of both worlds. You are likely to see some compromises in both models as well as physical implementations of those.

When it comes to SQL vs NoSQL, the trend for the last years has been that they are approaching each other. NoSQL has changed from No SQL, to Not Only SQL. Most SQL databases on the other hand now have support for unstructured data like XML and JSON that can efficiently be queried from within SQL.


getting the total of amount of money of each user between two dates even if no record is found I am trying to get the total amount of money which the user spent between a certain period. The code that I have come up with can successfully get the result but I also want to show the users who didn't spend any money on that certain period.

The code I have come up with:

select guest_id,
    sum(spent)
from shopping_t
left join
(
    SELECT ADDDATE( CURDATE() - INTERVAL 3 MONTH, INTERVAL @i:=@i+1 DAY) AS days
    FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE
    @i < DATEDIFF(NOW(), NOW() - INTERVAL 3 MONTH)
) as target
on shopping_date = target.days
where shopping_date = target.days
group by guest_id

The code above gives me this result:

id  spent
0   220000
2   10000

My desired output is:

id  spent
0   220000
1   0 <----- I also want to display users who didn't suspend anything on that period
2   10000

guest_t table:

id  name    address     telphone
0   George  Summerwind  042968084
1   Lisa    Laplace     042968084
2   Anne    Windhill    042968084
3   Mike    Fairview    042168084

shopping_t table:

id  spent   date
0   150000  2016-03-02
0   50000   2016-01-03
0   20000   2016-02-16
1   1500    2014-01-02
1   3400    2014-01-03
2   9000    2016-01-02
2   1000    2016-01-03

I have tried crossing date list query and guest_t and left join to shopping_t but I can't figure out what is wrong: http://sqlfiddle.com/#!9/57f9d/3

Because you want to see every single user, table guest_t should be the main table. You can then LEFT JOIN shopping_t to it for existing shopping records.

This query use the period from 01-01-2016 to 01-04-2016. It seems to be what has been used for your desired output. I remove the calendar query because it does not seem required in your desired output.

Query:

SELECT gt.name, gt.id, coalesce(sum(st.money), 0)
FROM guest_t AS gt
LEFT JOIN shopping_t AS st
    ON gt.id = st.guest_id
        AND st.shopping_date >= '2016-01-01'
        AND st.shopping_date < '2016-04-01'
GROUP By gt.id, gt.name;

Coalesce replace NULL by 0.

Output is similar to your desired output:

   name | id | coalesce(sum(st.money), 0)
 george |  0 |                     220000
   Lisa |  1 |                          0
   Anne |  2 |                      10000
   Mike |  3 |                          0

If you want data for the past 3 months starting NOW(), the calendar subquery is not needed. You only need to check the boundaries (NOW and NOW - 3 months) in the ON clause:

SELECT gt.name, gt.id, coalesce(sum(st.money), 0)
FROM guest_t AS gt
LEFT JOIN shopping_t AS st
    ON gt.id = st.guest_id
        AND st.shopping_date >= (CURDATE () - INTERVAL 3 MONTH)
        AND st.shopping_date < CURDATE ()
GROUP By gt.id, gt.name;

This query gives the same output.

Both query are in this SQL Fiddle.

Your query with dates in the WHERE clause didn't work because a LEFT JOIN returns NULL for rows with no match in the shopping table. NULL cannot be compared to the dates and the result of the condition is false.  Therefore the whole row (guest+shooping) is discarded by the WHERE clause:

| id |   name |       city | address |       tel | point | etc | guest_id |  money |              shopping_date
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 | 150000 |    March, 02 2016 00:00:00
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 |  50000 |  January, 03 2016 00:00:00
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 |  20000 | February, 16 2016 00:00:00
|  2 |   Anne |   Windhill |         | 042968084 |  -100 |     |        2 |   9000 |  January, 02 2016 00:00:00
|  2 |   Anne |   Windhill |         | 042968084 |  -100 |     |        2 |   1000 |  January, 03 2016 00:00:00
|  1 |   Lisa |    Laplace |         | 042968084 |     0 |     |   (null) | (null) |                     (null)
|  3 |   Mike |   Fairview |         | 042168084 |     0 |     |   (null) | (null) |                     (null)

From this output, the last 2 row are discarded.

When the date condition is move to the ON clause, shopping_t is checked before the LEFT JOIN. If the condition is not true, they are not used in the JOIN and replaced by NULL. However because a LEFT JOIN guest_t data stay in place whether shopping_t returns nothing (NULL).

If you want to see each days whether there is data or not, you have to CROSS JOIN the calendar subquery with guest_t and then use a LEFT JOIN:

select guest_id, t.days
    coalesce(sum(money), 0)
from (
    SELECT ADDDATE...
) as t
CROSS JOIN guest_t gt
left join shopping_t
    on gt.id = st.guest_id
        AND shopping_date = t.days
group by guest_id, t.days;

I remove the final WHERE clause with is similar to ON clause.


mysql join 3 table retain order of data order by used in subquery I have a query like this

SELECT a.pmid,a.sendid,a.sendtime,a.recid,a.message,b.unread,r.avator1 ra,s.avator1 sa,s.username sun,r.username run
FROM pm a
JOIN
(
  SELECT SUM(IF(recid = '347' and hasread=0, 1, 0)) AS undread,
         LEAST(sendid,recid) user1 ,
         GREATEST(sendid,recid) user2 ,
         MAX(pmid) pmid
  FROM pm
  WHERE sendid='347' OR recid='347'
  GROUP BY user1 , user2
) b
ON b.pmid = a.pmid
JOIN
  users s ON s.userid = a.sendid
JOIN
  users r ON r.userid=a.recid
ORDER BY unread DESC, pmid DESC LIMIT 0,10

I have performance issue with this query it's taking some 8-10 seconds to complete execution if I alter by removing  order by unread desc from the past of the query

query complete execution with in 1 sec but result is wrong,not what i need.

if i move order by unread desc inside the subquery example

SELECT a.pmid,
       a.sendid,
       a.sendtime,
       a.recid,
       a.message,
       b.unread,
       r.avator1 ra,
       s.avator1 sa,
       s.username sun,
       r.username run
FROM pm a
JOIN
  (SELECT SUM(IF(recid = '347'
                 AND hasread=0, 1, 0)) AS unread,
          LEAST(sendid,recid) user1,
          GREATEST(sendid,recid) user2,
          MAX(pmid) pmid
   FROM pm
   WHERE sendid='347'
     OR recid='347'
   GROUP BY user1,
            user2
   ORDER BY unread DESC,pmid DESC) b ON b.pmid = a.pmid
JOIN users s ON s.userid=a.sendid
JOIN users r ON r.userid=a.recid
LIMIT 0,
      10

then it is still not giving result what i need.

if i run the subquery ``

SELECT SUM(IF(recid = '347'
              AND hasread=0, 1, 0)) AS unread,
       LEAST(sendid,recid) user1,
       GREATEST(sendid,recid) user2,
       MAX(pmid) pmid
FROM pm
WHERE sendid='347'
  OR recid='347'
GROUP BY user1,
         user2
ORDER BY unread DESC,
         pmid DESC

separately then its giving correct data but then if i join user table once with on either recid or sendid then result is correct but when i join user  table twice with both sendid  and recid then the result order is incorrect.

table pm structure here

CREATE TABLE `pm` (
 `pmid` bigint(40) unsigned NOT NULL AUTO_INCREMENT,
 `sendid` bigint(20) unsigned DEFAULT NULL,
 `recid` bigint(20) unsigned DEFAULT NULL,
 `subject` mediumtext DEFAULT NULL,
 `message` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `hasread` int(10) unsigned DEFAULT 0,
 `sendtime` int(10) unsigned DEFAULT NULL,
 `ack` int(10) unsigned NOT NULL DEFAULT 0,
 PRIMARY KEY (`pmid`),
 KEY `sendid_2` (`sendid`,`recid`),
 KEY `recid` (`recid`,`hasread`),
 KEY `recid_2` (`recid`)
) ENGINE=InnoDB AUTO_INCREMENT=1900982 DEFAULT CHARSET=utf8

table users structure here

 CREATE TABLE `users` (
 `userid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
 `username` text CHARACTER SET latin1 DEFAULT NULL,
 `password` text CHARACTER SET latin1 DEFAULT NULL,
 `pass` varchar(30) CHARACTER SET latin1 NOT NULL,
 `groupid` bigint(20) unsigned NOT NULL DEFAULT 2,
 `email` text CHARACTER SET latin1 DEFAULT NULL,
 `rank` varchar(30) CHARACTER SET latin1 NOT NULL DEFAULT '0',
 `validated` int(10) unsigned NOT NULL DEFAULT 0,
 `avator1` varchar(30) CHARACTER SET latin1 NOT NULL DEFAULT '0.gif',
 PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=33744 DEFAULT CHARSET=utf8mb4

please help me.

This kludge allegedly works:  Tack LIMIT 999999999 onto the subquery.  That forces the optimizer to use the ORDER BY.  Normally (in newer versions of MySQL), the Optimizer simply throws away the ORDER BY in certain subqueries -- because the SQL Standard says that a subquery delivers an unordered set.  A reference.

(Not relevant in your case.)  Meanwhile, explicitly add ORDER BY in the subquery; don't depend on GROUP BY providing an ordering.  That MySQL 'extension' is in flux.  (If the GROUP BY and ORDER BY are the same, the Optimizer is smart enough to do them together.)

Because of the OR, the only practical way to do the subquery is with a full table scan of the 2M rows.

Optimization tip:  Smaller --> faster, especially if the table is too big to be cached.  So...

Don't use BIGINT (8 bytes) when something smaller would suffice.
Don't use INT (4 bytes) for a 'flag'.  TINYINT is 1 byte.
Don't use TEXT when VARCHAR(255 or less) would suffice.  (There is an extra savings when a tmp table is needed.)


How to implement a dynamic string into a (prepared) sql statement? I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used different IDs for the same datasets. So the IDs in the GeoNetwork differ from the IDs in the PostGIS database. They only have the same name.

If someone uploads a new dataset into GeoNetwork, the same dataset is uploaded into a PostGis database, too. I want to add a ID column into the database to give the field the same ID like the ID in GeoNetwork. I think I can only do this with using the filename cause they are the same in both cases.

I want to alter the table in eclipse with the following code:

  Connection con = null;
            PreparedStatement ps = null;
            final String sqlps = "ALTER TABLE ? ADD COLUMN ?";

            String filen = filename.substring(0, filename.indexOf('.'));
            try {
                con = DriverManager.getConnection("jdbc:postgresql_postGIS://localhost:5433/geonetwork?user=postgres&password=dominik1");
                try {
                    ps = con.prepareStatement(sqlps);
                    ps.setString(1, filen);
                    ps.setString(2, "GN_ID");

                    ps.execute();

But I get an error:

Exception:org.postgresql.util.PSQLException: ERROR: syntax error at »$1«
  Position: 13

It seems that Postgres has problems with the ? in the prepared statement.
Does anyone know a solution for this problem?

I also tried using a normal statement, but this is giving me a findbug error that I try to pass a non-constant string to an execute method on an sql statement. So I really don't know how to get rid of this problem.

The bad news: Prepared statements do not work like that. Parameters can only be data, not code or identifiers.

The good news: you do not need parameters for a ALTER TABLE statement at all.

final String sqlps = "ALTER TABLE filen ADD COLUMN gn_id integer";

...
    ps = con.prepareStatement(sqlps);
    ps.execute();

Your column needs a data type. I added integer on suspicion. Possibly add a NOT NULL constraint after that.
Friendly advice: do not use quoted upper-case column names in Postgres if you can avoid it. Use legal, lower case, unquoted names. Makes your life easier.


Log Reader Agent is not running - The job failed I created a transactional replication on PRE-PROD (via TSQL) and everything works as expected. On PROD platform replication is not working. While inspecting Replication monitor, I get this error message for Log Reader Agent:

The job failed.  The Job was invoked by User sa.  The last step to run was step 2 (Run agent.).

Any idea why I get this error?

SQL Agent is running under context of "NT AUTHORITY\NETWORKSERVICE". Configuration and security seems to be identical on PROD and PRE-PROD.

The publication and subscription have been correctly created. Everything is running on a single server, in the context of a user that is sysadmin and db_owner for publication and subscription DB:

The recommended approach is to have replication agents run under Windows accounts, not the SQL Server Agent service account, and the accounts should be granted only the required permissions.

Create a dedicated Windows account for the Log Reader Agent, grant it the appropriate permissions covered in Replication Agent Security Model, and use this account for your Log Reader Agent Security.


Friends relations in MySQL I'm developing a friendship relation in MySQL where friend relation is mutual. If A is friend of B, then B is friend of A. If one of the users end friendship then relation drops. I want to learn which way is better.

I have a running system;

user
-----------
userid p.k
name

friends
-------
userid
friendid
primary key (`userid`,`friendid`),
key `friendid` (`friendid`)

1 2
2 5
1 3

To get all of my friends;
SELECT u.name, f.friendid , IF(f.userid = $userid, f.friendid, f.userid) friendid
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = $userid, f.friendid, f.userid))
WHERE ( f.userid = '$userid' or f.friendid = '$userid' )

This query works well.  Maybe I can add a UNION.  The Query is more complicated than the one below and the table contains half as many records as the one below.

Another way is to keep relations in separate rows;

1 2
2 1
2 5
5 2
1 3
3 1

SELECT u.name, f.friendid
FROM friends f inner join user u ON ( u.userid = f.friendid )
WHERE f.userid = '$userid'

This query is simple, although the table takes twice as much space.

My concern is; assuming that there are millions of users; which way will work faster?

What are the advantages and disadvantages of both ways?

What should I keep in mind or change for these ways? And what problems can I face for both ways?

The first thing that catches my eye is the index setup for friends.

You have this at the moment:

friends
-------
userid
friendid
primary key (`userid`,`friendid`),
key `friendid` (`friendid`)

When crosschecking for mutual friendship, it could incur a little expense because the userid may be retrieved from the table when traversing the friendid index. Perhaps you could index as follows:

friends
-------
userid
friendid
primary key (`userid`,`friendid`),
unique key `friendid` (`friendid`,`userid`)

This might remove any need to access the table and search the index only.

Now, in terms of the queries, both of them may improve with the new unique index. Creating the unique index also eliminates the need to insert (A,B) and (B,A) into the table because (A,B) and (B,A) would be the index anyway. Thus, the second query would not have to gore through the table to see if someone is a friend of someone else because another person initiated the friendship. That way, if the friendship is broken by just one person, there are no orphaned friendships that are one-sided (seems a lot like life these days, doesn't it?)

Your first query looks like it would benefit more from the unique index. Even with millions of rows, locating friends using the indexes only would avoid touching the table. Still, since you did not present a UNION query, I would like to recommend a UNION query:

SET @givenuserid = ?;
SELECT B.name "Friend's Name"
FROM
(
    SELECT userid FROM friends WHERE friendid=@givenuserid
    UNION
    SELECT friendid FROM friends WHERE userid=@givenuserid
) A INNER JOIN user B USING (userid);

This will let you see who are the friends of each userid

To see all friendships, run this:

SELECT A.userid,A.name,B.friendid,C.name
FROM user A
INNER JOIN friends B ON A.userid=B.userid
INNER JOIN user C on B.friendid=C.userid;

First, here is some sample data:

mysql> drop database if exists key_ilyuk;
Query OK, 2 rows affected (0.01 sec)

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

mysql> use key_ilyuk
Database changed
mysql> create table user
    -> (
    ->     userid INT NOT NULL AUTO_INCREMENT,
    ->     name varchar(20),
    ->     primary key(userid)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into user (name) values
    -> ('rolando'),('pamela'),('dominique'),('carlik'),('diamond');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create table friends
    -> (
    ->     userid INT NOT NULL,
    ->     friendid INT NOT NULL,
    ->     primary key (userid,friendid),
    ->     unique key (friendid,userid)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into friends values (1,2),(2,5),(1,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;
+--------+-----------+
| userid | name      |
+--------+-----------+
|      1 | rolando   |
|      2 | pamela    |
|      3 | dominique |
|      4 | carlik    |
|      5 | diamond   |
+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from friends;
+--------+----------+
| userid | friendid |
+--------+----------+
|      1 |        2 |
|      1 |        3 |
|      2 |        5 |
+--------+----------+
3 rows in set (0.00 sec)

mysql>

Let's look at all the relationships

mysql> SELECT A.userid,A.name,B.friendid,C.name
    -> FROM user A
    -> INNER JOIN friends B ON A.userid=B.userid
    -> INNER JOIN user C on B.friendid=C.userid
    -> ;
+--------+---------+----------+-----------+
| userid | name    | friendid | name      |
+--------+---------+----------+-----------+
|      1 | rolando |        2 | pamela    |
|      1 | rolando |        3 | dominique |
|      2 | pamela  |        5 | diamond   |
+--------+---------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

Let's look at all 5 userid's and see if the relationships are correctly shown

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

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| pamela        |
| dominique     |
+---------------+
2 rows in set (0.00 sec)

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

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| rolando       |
| diamond       |
+---------------+
2 rows in set (0.00 sec)

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

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| rolando       |
+---------------+
1 row in set (0.01 sec)

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

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
Empty set (0.00 sec)

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

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| pamela        |
+---------------+
1 row in set (0.00 sec)

mysql>

They all look correct to me.

Now, let's use your second query to see if it matches up...

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

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+-----------+----------+
| name      | friendid |
+-----------+----------+
| pamela    |        2 |
| dominique |        3 |
+-----------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 2;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+---------+----------+
| name    | friendid |
+---------+----------+
| diamond |        5 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SET @givenuserid = 3;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

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

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

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

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

mysql>

Why don't match up? That's because I did not load the (B,A) for every (A,B). Let me load the (B,A) relationships and try your second query again.

mysql> insert into friends values (2,1),(5,2),(3,1);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SET @givenuserid = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+-----------+----------+
| name      | friendid |
+-----------+----------+
| pamela    |        2 |
| dominique |        3 |
+-----------+----------+
2 rows in set (0.00 sec)

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

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+---------+----------+
| name    | friendid |
+---------+----------+
| rolando |        1 |
| diamond |        5 |
+---------+----------+
2 rows in set (0.00 sec)

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

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+---------+----------+
| name    | friendid |
+---------+----------+
| rolando |        1 |
+---------+----------+
1 row in set (0.00 sec)

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

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

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

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+--------+----------+
| name   | friendid |
+--------+----------+
| pamela |        2 |
+--------+----------+
1 row in set (0.00 sec)

mysql>

They still don't match. That's because your second query is only checking one side.

Let's check your first query against every value with only (A,B) and not (B,A):

mysql> SET @givenuserid = 1;
SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+-----------+--------+----------+
| name      | userid | friendid |
+-----------+--------+----------+
| pamela    |      2 |        2 |
| dominique |      3 |        3 |
+-----------+--------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 2;
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+---------+--------+----------+
| name    | userid | friendid |
+---------+--------+----------+
| rolando |      2 |        1 |
| diamond |      5 |        5 |
+---------+--------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 3;
SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+---------+--------+----------+
| name    | userid | friendid |
+---------+--------+----------+
| rolando |      3 |        1 |
+---------+--------+----------+
1 row in set (0.00 sec)

mysql> SET @givenuserid = 4;
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Empty set (0.01 sec)

mysql> SET @givenuserid = 5;
FROM friends f
Query OK, 0 rows affected (0.00 sec)

    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+--------+--------+----------+
| name   | userid | friendid |
+--------+--------+----------+
| pamela |      5 |        2 |
+--------+--------+----------+
1 row in set (0.00 sec)

mysql>

Your first works well. I am sure it is benefiting from the unique index as I said earlier, but IMHO I think the UNION is simpler. With than unique index, it would appear to be six of one and half dozen of the other in terms of execution and output.

You would have to benchmark your first query against my suggestion UNION and see.

This was a good question you asked today. +1 for your question.


Field/datatype for searchable address components? I'm using Django with Postgres, and I'm looking for a field/datatype to store address components that come from Google Places API in this format:

"address_components": [
  {"long_name": "27", "short_name": "27", "types": ["street_number"]},
  {"long_name": "Dromore Crescent", "short_name": "Dromore Crescent", "types": ["route"]},
  {"long_name": "Westdale South", "short_name": "Westdale South", "types": ["neighborhood", "political"]},
  {"long_name": "Hamilton", "short_name": "Hamilton", "types": ["locality", "political"]},
  {"long_name": "Hamilton Division", "short_name": "Hamilton Division", "types": ["administrative_area_level_2", "political"]},
  {"long_name": "Ontario", "short_name": "ON", "types": ["administrative_area_level_1", "political"]},
  {"long_name": "Canada", "short_name": "CA", "types": ["country", "political"]},
  {"long_name": "L8S 4A8", "short_name": "L8S 4A8", "types": ["postal_code"]}
],
"formatted_address": "27 Dromore Crescent, Hamilton, ON L8S 4A8, Canada",
"place_id": "ChIJtZiMplGbLIgRcGQ4Anc337s",

The goal is to allow users to see this row in their results if they search for westdale, L8S 4A8, l8s4a8, 27 dromore, hamilton, etc.

How should this data be saved?

Save it as-is in jsonb? There's a lot of duplicate text, for instance 'long_name' and 'short_name' keep appearing over and over, wasting space.
Strip all the keys and only keep the actual meat of the components in a varchar[], ex. ['27', 'Dromore Crescent', 'Westdale South', 'Hamilton', 'Hamilton Division', 'Ontario', 'Canada', 'L8S 4A8']?
Normalize this data in layers of foreign-key related tables? Four tables are required to normalize this: address_to_component, component, component_type, component_to_component_type. (I actually started with this, but couldn't wrap my head around how to go about querying it.)
Something else?

UPDATE: see this answer for a working example of the below

How would I store it? What you've got is

User data (formatted_address)
Normalized address breakdown (address_components)

I would store only the formatted_address or whatever the user provided. Why?

I don't trust Django or whatever you're integrating with
I want the ability to re-normalize addresses when new data comes out.

PostGIS is a PostgreSQL extension that provides address_standardizer. You can install standardize_address like this,

CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;

From there you can provide the formatted address to standardize_address

SELECT standardize_address( formatted_address )

This will return a composite type stdaddr. I may cache this against the version of the extension and the timestamp of the call to standardize_address.

The stdaddr composite type will also be much easier to geocode which you always seem to need at some point if you're going down this path.

All of that said, Django appears to be using Google Maps to normalize their addresses. If that works for you, you can run with it. I'm sure that's not a bad source of address normalization. How you store that, will depend entirely on how you want to query it. If you're even querying it at all and not just presenting a profile. If you're not JOINing based on any of that I think a new column django_google_maps jsonb is a great starting point.


Active Directory to authenticate user access to Postgres DB through ODBC driver. Is this possible? I need for a user to logon to a Postgres DB through either MS Excel or Power BI, without entering any credentials, using solely their Active Directory login. I'm mainly a BI Developer and not usually part of the infrastructure side of things, but I've been tasked with finding out if this is at least feasible.

Am I right in thinking that this would be a pretty normal task within Microsoft infrastructure?

User signs on with AD to domain/their machine
AD grants access to something like SQL server (I take through some native MS protocol, not ODBC)
Excel(MS Query)/PowerBI from the users desktop rides that connection to allow the tool(s) seamless access to data as defined by their AD access rights.

The aim is to remove any additional credential entry by the user into either of the tools, because there'd be a high user turnover and user access to data sets must be stringently managed.

I imagine there would be back and forth between the AD admins and the DBA to facilitate user creation and access control at both ends, and I know postgres itself is AD/LDAP compliant, so some kind of access is possible. I'm just not sure if point 3, the seamless connection is possible.

Active Directory has 3 kinds of authentication: NTLM (NT LAN Manager), Kerberos (GSSAPI), and LDAP. Active Directory is essentially a fancy LDAP server with extra authentication mechansims

LDAP is centralized authentication, but not single sign-on. Your password is the same as when you login to your computer, but you need to login to the database again

NTLM is single sign-on, but only works on Windows. PostgreSQL supports it via SSPI

Kerberos is also single sign-on, but works with Windows and UNIX/Linux/Mac too. You login to your computer, get a Kerberos ticket, and Postgres uses this ticket to authenticate you. Postgres needs to be setup as Service Principal in Active Directory

I would say that Kerberos or SSPI is what you want.

Start with https://www.postgresql.org/docs/current/static/auth-methods.html


Can't "SET TRUSTWORTHY ON" With Non-SA User Account I'm trying to run the following command with a user account(XXX_Admin):

ALTER DATABASE [XXX] SET TRUSTWORTHY ON;

This works with the "SA" account, but with my user account(XXX_Admin) I get:

  Msg 5011, Level 14, State 5, Line 1 User does not have permission to
  alter database 'XXX', the database does not exist, or the database
  is not in a state that allows access checks. Msg 5069, Level 16, State
  1, Line 1 ALTER DATABASE statement failed.

I've checked, and this user account should have the correct permissions:

(XXX Database)

(Login - Server Rules)

(Login - User Mapping)

I've also verified that the login and user are synced with the following commands:

EXEC sp_change_users_login 'update_one', 'XXX_Admin', 'XXX_Admin'

EXEC sp_change_users_login 'AUTO_FIX', 'XXX_Admin'

GRANT ALTER ON DATABASE:: XXX TO XXX_Admin

Now I'm really at a loss as to what to check next... As so often with SQL Permissions issues, I grant all access to my account that I can think of but am still unable to run commands. Am I missing something?

Oops, Look like I got one of my X's mixed up in the database name, apparently if you run:

ALTER DATABASE [XXX_DATABASEDOESNOTEXIST] SET TRUSTWORTHY ON;

You'll get this error... :(


How to replace an empty date value? I am trying to replace many null date values in a single column using this query:

UPDATE animal SET
  BirthDate = '1900-01-01 06:00:00 AM'
WHERE BirthDate = '''

MySQL shows this error:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE animal SET
    BirthDate = '1900-01-01 06:00:00 AM'
  WHERE BirthDate = ''' at line 1.

How can this be resolved? I use Toad for MySQL interface editor.

For starters, your GUI shouldn't matter, but it's normally good to use the CLI - i.e. the mysql client - or at least as a backup in case of results you are having problems interpreting.
Second, have you tried IS NULL instead of the = comparison?
Third, what's wrong with NULL? Normally, you should use the appropriate datatype - why is NULL a problem? If you don't know the date of birth, there's nothing wrong with it. For example, if you want to perform an AVG calculation, you'll have problems. Your population will appear much older than it really is. NULLs however will be ignored in such a calculation, so no anomalous results (the same goes for all such calculations).


SQL Server 2012 Agent Job not providing full failure message for SSIS Packages (this was also posted at http://www.sqlservercentral.com/Forums/Topic1576641-2799-1.aspx)

We are having an issue with 2012 SQL Servers not reporting why a job has failed, this is happening for jobs running SSIS packages in integration services (not the Integration Services Catalog). I have tried to reproduce the error without success and am looking to see if anyone else is seeing this behavior or sees what I am missing.

Usually when you look at the job history for the specific step that failed it will display the error like so:

  Source: Execute SQL Task Execute SQL Task     Description: Executing the query "select * from madeUpTable" failed with the following error: "Invalid object name 'madeUpTable'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:38:05 AM  Finished: 11:38:05 AM  Elapsed:  0.421 seconds.  The package execution failed.  The step failed.

However on some of our jobs we now get a very generic error which makes it very difficult to troubleshoot. If you add an text file in the output for the job step that does receive the whole error, but is set to override at each run (so we lose the error message if the next run is a success).

  Executed as user: domain\username. Started:  3:00:01 AM  Finished: 4:01:47 AM  Elapsed:  3706.97 seconds.  The package execution failed.  The step failed.

I have tried the following to reproduce the problem, but every time I get the full error message:

Changed LoggingMode property in the package to disabled, currently set as UseParentSetting
Changed to execute a stored procedure
Changed procedure code to be wrapped in a try..catch with raiserror(‘test’,16,1)
Changed to raiserror(‘test’,18’1) (wondered if there was a smaller range of suitable error numbers)
Added a Cozyroc task (3rd party) and made that fail

We have this happening on multiple servers, including one that was a new 2012 named instance and one that was upgraded from 2008 R2. We were curious whether the 2008 packages were not upgraded fully/properly with the wizard but this wouldn't be the case for the new 2012 instances.

We are now wondering if this is only happening with certain types of task, but have not managed to find the tasks during testing. Is anyone else experiencing this? Or any suggestions on how to resolve this issue?

SSIS handles errors in two ways.

One is raising an error.
The other is failing a task/container/package.

The two are actually quite separate, and you can easily see one without the other.

To bubble an error up to the client (in this case, SQL Agent and its log) you would have to avoid catching the error somewhere. I suspect you might have an error handler there in some, or you have some of those convenient red arrows in there...

Of course, I see many times when people have errors that aren't causing anything to actually fail, so the package just keeps running. But that's another story...