Stored procedure big where clause or multiple procedures Is it better to have one big query or N small ones?

For example to select all the customers with multiple parameters:

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Name is null or name like @Name)
and (@FirstName is null or firstname like @FirstName )
and (@City is null or name city @City)
and (@Birthday is null or birthday like @Birthday)

Or too have multiple stored procedures like

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Name is null or name like @Name)
and (@FirstName is null or firstname like @FirstName)

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@City is null or name city @City)

select
   id,
   name,
   firstname,
   city,
   birthday,
   ...
from dbo.Customers
where (@Birthday is null or birthday like @Birthday)

I would create One stored procedure with optional parameters. Also instead of using WHERE @Var is NULL or Column = @Var use dynamic sql to build your query.

This kind of query where clause results in a very unefficient execution plan.

Also Sql server doesn't do any shortcircuting when evaluating OR conditions in where clause, for example even if @Var is null evaluates true sql server may still go ahead with the other Or condition and starts evaluating the whole column against a null variable.

Also with these optional parameters, if you pass a specific set of parameters and there is no execution plan for this procedure in the proc cache sql server will create an execution plan best for these specific set of parameters and uses it, but what if you pass a very different set of parameters on the next execution of this procedure, sql server looks into the proc cache and if it find an execution plan there it just goes ahead and uses that execution plan, even though it was a good execution plan for the first set of params passed but a very bad execution plan for these very different set of params (known as Parameter sniffing).

Using dynamic sql also eliminates this parameter sniffing since it stores parameteised execution plans.

I would use one procedure with dynamic sql something like this...

CREATE PROCEDURE dbo.usp_ProcName
 @Name          VARCHAR(100)
,@FirstName     VARCHAR(100)
,@City          VARCHAR(100)
,@Birthday      VARCHAR(100)
AS
BEGIN
  SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(MAX);

SET @sql = N'  select  id
                       ,name
                       ,firstname
                       ,city
                       ,birthday
                from dbo.Customers  WHERE 1 = 1'
       + CASE WHEN @Name IS NOT NULL
              THEN N' AND Name = @Name' ELSE N' ' END
       + CASE WHEN @FirstName IS NOT NULL
              THEN N' AND FirstName = @FirstName' ELSE N' ' END
       + CASE WHEN @City IS NOT NULL
              THEN N' AND City = @City' ELSE N' ' END
       + CASE WHEN @Birthday IS NOT NULL
              THEN N' AND BirthDay LIKE ''%'' + @Birthday + ''%''' ELSE N' ' END

EXECUTE sp_executesql @Sql
                    ,N'@Name VARCHAR(100) , @FirstName VARCHAR(100)
                       @City VARCHAR(100), @BirthDay VARCHAR(100)'
                    ,@Name
                    ,@FirstName
                    ,@City
                    ,@Birthday
END


Cannot use "ON CONFLICT" with postgres updatable view and partial index I have an updatable view pointing to an underlying table that has a partial index. It looks something like this
CREATE TABLE if not exists foo (
    a INT,
    b INT,
    x INT,
    y INT,
    z BOOLEAN,
    CONSTRAINT x_or_y CHECK (
      (z and x is not null and y is null)
      or
      (not z and x is null and y is not null)
    )
);
CREATE UNIQUE INDEX ux ON foo (x, a) WHERE z=TRUE;
CREATE UNIQUE INDEX uy ON foo (y, a) WHERE z=FALSE;
CREATE OR REPLACE VIEW  foo_view AS
    SELECT * FROM foo;

That is, for each row, y must be null if z is true; x must be null if z is false; and, only one of x and y may be not null. (x, a) and (y, a) must be unique. (Sorry if this is overly complicated. I'm translating from my real table that has a lot of other cruft.)
My problem comes when I want to update with ON CONFLICT. I believe I ought to be able to do this.
INSERT INTO foo_view(x, y, a, b, z)
  VALUES
  (5, null, 1, 2, true),
  (null, 5, 1, 2, false);

select * from foo_view;

INSERT INTO foo_view(x, y, a, b, z)
  VALUES
  (5, null, 1, 2, true)
ON CONFLICT (x, a) where z=true
  DO UPDATE
  set b = EXCLUDED.b;

But, I get the exception:
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

I can insert into foo instead of foo_view with the same ON CONFLICT without error.
Here is a fiddle: https://www.db-fiddle.com/f/cX2HXg91Q7yKoPeMBYzVLg/0

After debugging through the PostgreSQL code, I'd say that there is a problem in the infer_arbiter_indexes function in src/backend/optimizer/util/plancat.c. It compares the index predicate to the WHERE condition in ON CONFLICT, but seems to ignore that they are on different objects.
I'm not sure if PostgreSQL is willing to consider that as a bug, but you might report it.


How I can monitor Amazon RDS Postgresql table size once the `hot_standby_feedback` is on I have a postgresql Master-Slave replication scheme and because of query cancelation due to conflict with wal I need to set the following setting to each replica:

hot_standby_feedback = on

But because this can cause the tables on my master server to have overly large amounts of data that are about to be deleted but not deleted yet, I need to be able to monitor the table size in order to force manually an autovacumm policy on mergency situations (storage starvation or overprice because of storage size).

Therefore, how do I monitor the table size on my database? Also, how I can enforce the AUTOVACUMM in case of my tables having way too much size.

force manually an autovacumm policy on mergency situations

This is not going to work. hot_standby_feedback doesn't prevent autovacuum from running. It lets it run but prevents it from removing rows that are not "dead enough".  No amount of vacuuming is going to help if you don't get the long running queries on the replicas to go away first.

You can use pg_total_relation_size to get the table size.  You can take a query like the below, and just plug it into our favorite monitoring solution.

select pg_total_relation_size('pgbench_accounts');

But that still leaves the question of what to do about it.


Error import postgres database I have a databse dump db.sql.schema
I import it using psql -U user dbname < ~/db.sql.schema
After import in my DB I see table mytable but not mytable_id_seq.

# \d mytable
        Table mytable
      Column       |     Type     | Collation | Nullable | Default
-------------------+--------------+-----------+----------+---------
 id                | integer      |           | not null |
 field1            | integer      |           | not null |
 field2            | integer      |           | not null |
 field3            | integer      |           | not null |

I got the follwong error:

# INSERT INTO mytable (field1, field2, field3) VALUES (1, 1, 1) RETURNING mytable.id;
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 1, 1, 1).

My postgres version is 10.12
It looks like auto-increment of id doesn't works.
Is it a postgres issue or issue with my dump? Is it loo old or too new?

You are trying to import a dump from a recent PostgreSQL version into an older PostgreSQL server. That is not supported.

Your only option is to manually edit the dump and fix any syntax that the older server doesn't understand.

Check for the first error message you get when restoring the dump, later ones can be consequences of the first one.


Listener listening without listener.ora I have an Oracle DB working in production for more than a year.
I have my listener working:

[oracle@base admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-NOV-2016 14:22:38
Uptime                    1 days 20 hr. 3 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /o/app/oracle/diag/tnslsnr/base/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=base)(PORT=1521)))
Services Summary...
Service "BASE" has 1 instance(s).
  Instance "BASE", status READY, has 1 handler(s) for this service...
Service "BASEXDB" has 1 instance(s).
  Instance "BASE", status READY, has 1 handler(s) for this service...
The command completed successfully

But I have no listener.ora:

ls /o/app/oracle/product/DB_11.2_SE/dbhome_2/network/admin/
samples  shrept.lst  tnsnames.ora

This is the content of my tnsnames.ora:

$ cat tnsnames.ora
BASE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.20.19)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BASE)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.20.20)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

How is this possible? As far as I know, for the listener to work, I have to have a listener.ora file.

I have another Oracle database installed, and if I execute lsnrctl status, in the output I can see a line containing Listener Parameter File   /o/app/oracle/product/11.2/dbhome/network/admin/listener.ora, and I am not seeing it here. Also I have executed locate listener.ora, and nothing.

You don't need a listener.ora. The listener will happily start without one, but will just start without supporting any services. pmon will periodically register databases with the listener, but you can force registration manually.

Move the file away:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>move listener.ora listener.foo
    1 file(s) moved.

Start the listener:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>lsnrctl start

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-NOV-2016 13:50
:03

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

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\host\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                04-NOV-2016 13:50:08
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\host\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))
The listener supports no services
The command completed successfully

Register the database with the listener:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 4 13:50:38 2016

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> alter system register;

System altered.

SQL>

The listener now supports the database:

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-NOV-2016 13:51
:20

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                04-NOV-2016 13:50:08
Uptime                    0 days 0 hr. 1 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\host\list  ener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>


Stress test MySQL with queries captured with general log in MySQL Is there any tool available which can do stress testing using the log file created by MySQL general log?
After a lot of search on google I found few stress testing tools which only use some benchmarks for stress test. One solution is using Apache JMeter, but it does not create test plans from MySQL log file and creating custom test plan for all the queries I have is too time consuming.
Or is there a tool which can at least create .sql file from MySQL log?

Here is a more direct approach using brute force mysql: Setup the general as a MyISAM table. On installation, you will find the table mysql.general_log as a CSV table.
Rather than reinventing the wheel and doing a whole lot of typing, please read my past posts:

Nov 16, 2011 : Log MySQL DB changing queries and users
Feb 11, 2012 : MySQL general log
Jan 08, 2012 : How to enable MySQL general log?

Once you get mysql.general_log converted to MyISAM and queries are getting recorded, simply access the log like any other MyISAM table.
Here is the structure of the MyISAM version of mysql.general_log
mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

From here, just write all content starting with a query:
SQLSTMT="SELECT argument FROM mysql.general_log"
SQLSTMT="${SQLSTMT} WHERE LEFT(argument,6) IN"
SQLSTMT="${SQLSTMT} ('INSERT','UPDATE','DELETE','SELECT')"
mysql -uroot -p -ANe"${SQLSTMT}" > SQLCommands.sql

There you go.
Give it a Try !!!


How much disk space was made available to Postgres by VACUUM that is not available to the OS? VACUUM does not return disk space to the OS, instead it marks dead rows to be reused for future writes. My server has almost ran out of disk space, afterwards I ran DELETE FROM big_table to clear about 30% of rows, followed by a manual VACUUM.

No space was given back to the OS, which is fine. How many rows can I still insert before running out of space?

df -h is a useless metric for obvious reasons. Is there a query to see postgres' used disk space vs available to postgres' disk space?

The two extensions pgstattuple and pg_freespacemap might be helpful.

pg_freespacemap is faster, and gives a view of free-space which matches how PostgreSQL itself hunts for freespace when it is doing a tuple insertion.

SELECT sum(avail) FROM pg_freespace('foo');

But it doesn't give you view of the average tuple length, which would be needed to convert space into tuples (on the assumption new tuples will be the same length as existing ones).  pgstattuples gives you info to compute the current average length.

Neither one of them accounts for the possibility that your new rows will be an awkward size which does not fit into the holes currently available.

Also, neither works very well for indexes.  New entries have to go into an index where they belong, even if some other page has plenty of free space in it. So the reusability of index space depends on how the new indexed values relate to the deleted away values.


Creating a global temp table in MySQL I am working on a MySQL stored procedure.

I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.

I know we can't access the temp table in another request in MySQL.

Is there a way to create a temp table like globally, or how can I access the temp table across the multiple requests?

It is not temporary, if you want to access it from multiple requests, because there is no way for mysql to know when to delete it. So you must create it manually and delete it manually. You can use a MEMORY engine, which will have similar characteristics to temp table.


Binlog commits and flushes on SQL Server (compared to MySQL) With MySQL secondary/primary, sync_binlog and innodb_flush_log_at_trx_commit control log and disk flushing, and are important to maintain consistency between primary and secondary (so that the secondary can never be ahead of the primary in case of a crash). An admin is free to set this (and mess this up), for performance reasons for instance. I was wondering, how is this arranged in Microsoft SQL Server in mirroring or replication mode? Does it even have the same tunables?
Specific questions would be:

Being more 'click-and-go', does SQL server set everything to safe values?
What are equivalent settings, or settings one must know when dealing with primary/secondary consistency, and their performance impact?

Being more 'click-and-go', does SQL server set everything to safe values?

It's baked in to Availability Groups (physical) and Transactional Replciation (logical) that a replica cannot commit changes ahead of the primary.
In AGs transaction log records are streamed to the replicas as they are hardened on the primary, but on a failover, any transaction that failed to commit on the primary will be rolled back on the replicas.
In Transactional Replication changes are harvested from the transaction log on the primary (called the "Publisher"), but only committed transactions are sent to the Distribution database.

What are equivalent settings, or settings one must know when dealing with primary/secondary consistency, and their performance impact?

Since consistency is always guaranteed, the main thing is to understand the difference between Sync and  Async replicas.


DTC Timeout when trying to update oracle table through link that contains a text index Problem

Given is a SQL Server connected to an ORACLE Instance as a Linked Server. It’s using the Oracle Client OLEDB Provider. When doing an update in a transaction on a table having a CTXSYS.CONTEXT Text Index the DTC Connection aborts after approx. 10 Min with Error: 8522, Severity: 18, State: 1 on the commit. As soon you remove the CONTEXT index it works again.

Is this a bug in OraOLEDB or can we do something to fix this issue, while keeping the indexes?

Setup

On ORACLE

We use an ORACLE 11g 11.2.0.2.0

Create this table for this test.

CREATE TABLE docs (id NUMBER PRIMARY KEY, text VARCHAR2(200));
INSERT INTO docs VALUES(1, 'California is a state in the US.');
INSERT INTO docs VALUES(2, 'Paris is a city in France.');
INSERT INTO docs VALUES(3, 'France is in Europe.');
INSERT INTO docs VALUES(4, 'Los Angeles is a city in California.');
INSERT INTO docs VALUES(5, 'Mexico City is big.');
COMMIT;
CREATE INDEX idx_docs ON docs(text)
    INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
    ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');

On MSSQL

We use a SQL Server 2012 Express and Developer Edition both produce the same error.
Setup a linked server to above Oracle Server with Oracle Provider for OLE DB (OraOLEDB)  Release 12.1.0.1.0 Production.

Reproduce

Working Update

This updates work:

set xact_abort off;
update DBLINK..REMOTE_USER.DOCS set text='Mexico City is massive.' where id = 5;

(1 row(s) affected)

This Update Statement produces the error, after the DTC seems to run in a timeout.

set xact_abort on;
begin transaction t1
update DBLINK..REMOTE_USER.DOCS set text='Mexico City is huge.' where id = 5;
commit transaction t1;

(1 row(s) affected)
Msg 8522, Level 18, State 1, Line 4
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.

Trace

This the end of the trace of a successful update:

This is the end of the trace of the update that runs into the timeout:

Turns out (after an Oracle SR) this was an already known problem we needed to patch the Oracle Server -> ORACLE 11G 11.2.0.2 PATCH 27 BUG FOR WINDOWS helps.


Best way to back up databases on a Microsoft SQL Server Availability Group DR Site node I have an Availability Group (Primary only is readable) with 2 nodes locally and 1 node at a DR site.  Both sites have a backup system.  Locally, I’m using the local backup system to back up the AG data bases on the Primary node.  But what is the best strategy for backing up the AG data bases when the primary node fails over to the DR site?  Use the backups system at the DR site?  Use the backup system from the local site (if the data center hasn’t been hit by an asteroid)?  Both?  Something else?  And for System data bases (not in the Availability Group)?

My concerns are having local backups at the DR site (in case the other site is hit) vs. keeping the log chain intact to avoid full backups unnecessarily (1 of our data bases is almost a TB).

Thanks for any help or advice.

You can use some system dynamic management views (DMVs) and functions in your backup script, for example:

sys.dm_hadr_availability_replica_states
sys.fn_hadr_backup_is_preferred_replica
sys.availability_databases_cluster
sys.dm_database_encryption_keys

to determine the current node's assignment (primary, secondary, etc) and direct where the full/diff/t-log backups should be coming from. If you use that in conjunction with a file share (as @Steve mentioned), then all backups can be pointed to the same target. You can get creative from there (eg, copying the backup files from the share to another site) to bolster for DR purposes, etc.
You may also glean some good script setup insights from Ola Hallengren's backup scripts (https://ola.hallengren.com) which take AGs into consideration.
Hopefully that helps some.


Efficient way to copy/sync a table - ideas on different approaches We have an ISV database which we try to minimize contact with. It is an old 2008 R2 DB. We want to keep a copy of a number of tables in a different database. We are looking for a T-SQL solution to sync tables. This may need to take place when the system is being used.

I appreciate the answer will require further research and testing.

These tables are prone to locking from ISV app.
This is a low powered server and not really Enterprise.
Also we think the number of tables that we sync will increase
We want avoid anything like replication (the supplier of the ISV has habit of blaming any issues on us with no investigation whatsoever!)

These are my options (the ones I've thought of)

I've read bad things about MERGE in terms of performance, bearing in mind it's an older db, would this be fair?
tablediff.exe would be perfect but I'm concerned about locking the source table.
Is a straight truncate/insert with minimal locking/logging very inefficient - sounds stupid but I thought I'd include it.

Would just truncating and inserting a couple

You have many options, depending on what you want to achieve and the constraints you have on the source/copy database:

Merge Replication: can be bi-directional. Can be filtered and implement complex JOIN filters. The subscriber database is writable. Needs to have a rowguidcol on each table (uniqueidentifier UNIQUE NOT NULL ROWGUIDCOL column), so it can be a deal breaker if you can't touch the schema of the source database. Ships incremental changes quite efficiently.
Snapshot replication: uni-directional. Can be filtered. The subscriber database is writable. No schema changes needed. Doesn't ship incremental changes: it's all or nothing.
Transactional Replication: can be bi-directional (but updateable subscriptions is deprecated and needs an additional column on each updateable article. Peer-to-peer is enterprise-only, so I'm mentioning it so that you know it exists, but it won't work for you). Can have static filters. Every table needs to have a PRIMARY KEY defined, which is not typically the case in ugly, hairy, legacy third-party applications.
Mirroring: the database at the mirror server can be read from a snapshot, which is an Enterprise features, unless you use SQL Server 2016 SP1 or later, where snapshots have been included in all editions. Wouldn't work for HA, just for scale-out. Uni-directional, no filters, no schema changes needed. The snapshot is a static copy of the data at a given time, so you need to take snapshots at different times to see new data. This means you have to kill connections to older snapshots before you create the new one.
Log Shipping: can be used to obtain a read-only copy of the primary database by using RESTORE WITH STANDBY. Needs to kill all connections before restoring additional t-logs. No filters, no schema changes needed. Interferes with existing transaction log backup schedules.
Custom sync process: you could combine different techniques to build your own sync process, but I really advise against that. The complexities are countless and it's hard to outsmart existing scale-out or data distribution tecnhnologies built in the product.


What is a 'Target Server' in a SQL Server Agent Job? Can someone explain what a 'Target Server' is in this instance.  Also, what it means to "Target Multiple Servers"?

A Target server allows the job to be run against a different server than the one it is defined on.

Multiple Target Servers allows you to define a job once (say a routine backup job) and have it execute against many servers (thus backing each of them up).

UPDATE:

MSDN Documentation here:  http://msdn.microsoft.com/en-us/library/ms180992.aspx


MySQL 5.6 replication causes 'waiting for table lock' All of the sudden queries on slave server stopped with status "Waiting for table level lock"

I restart mysql service and stop replications and locking does not show up anymore. Once I turn replications back on I see huge increase in "waiting for table level lock" status for queries (show full processlist)

Replication is crucial for our situation and we can't keep it turned off.

What might cause this problem? Replication was running fine for last 5 months or so.

MySQL 5.6

It is possible that current replication thread is taking a lot of time updating a (big) change to a (big) table causing a long lock on the table. You may want to examine what the sql thread is trying to execute using mysqlbinlog with --start-position parameter. Hope this helps.


sp_publication_validation procedure doesn't seem to work properly I am trying to use sp_publication_validation stored procedure to validate that my replication setup is working correctly.  This procedure is pretty straightforward and is described here.

I am running this command:

EXEC sp_publication_validation @publication = 'MyPublication'
    ,@rowcount_only = 1     -- (1 - rowcnt only, 2 - rowcnt and checksum)
    ,@full_or_fast = 0      -- (0 - full count using COUNT(*), 1 - use sysindexes.rows, 2 - conditional)

Initially everything seems to be ok and the results indicate that all of the tables match.  Next I go over to the subscriber and manually delete a few rows from some of the tables.  I manually verify that the row counts in my tables are now different between the publisher and the subscriber.  Finally I run the sp_publication_validation procedure again and .... it says that everything is still OK.  This is wrong!  I also tried to return both rowcnt and checksum and it still doesn't detect the fact that there are differences between the publisher and subscriber.

I appreciate any ideas.

Thank you!

My explanation is that sp_publication_validation validates a Publication: set of Articles sent by Publisher and received by Subscriber. Since there were no data corruption in transit this SP does not report any errors.

To detect differences in replicated tables use SP_TABLE_VALIDATION.

  Either returns rowcount or checksum information on a table or indexed
  view, or compares the provided rowcount or checksum information with
  the specified table or indexed view. This stored procedure is executed
  at the Publisher on the publication database and at the Subscriber on
  the subscription database.

SP_TABLE_VALIDATION on MSDN


Mapping Fields based on unknown relationship I have a challenging SQL query to work out (SQL Server).
I have one table that will show mappings between the two tables.
So below, field F1 is a scanner id

and table two, has the scanner Id in F1. F2 has the Location, and so on.

The top table can be updated to change mappings at any time.
So my question is how do I create a query so I know what field name relates to what data in table two.

I started off with an UNPIVOT, but didn't get too far as yet. If I could get the field name in the unpivot results then I am a long way to solving it.

SELECT ID, Data
FROM
(
select
ID,[F1] ,[F2]  ,[F3]  ,[F4]  ,[F5]  ,[F6]  ,[F7]  ,[F8]  ,[F9] ,[F10]
FROM [Inventory].[dbo].[ScannedDataCapture]

) AS cp
UNPIVOT
(
Data FOR Datas IN ([F1] ,[F2]  ,[F3]  ,[F4]  ,[F5]  ,[F6]  ,[F7]  ,[F8]  ,[F9] ,[F10])
) AS x

Thanks upfront. Scott

You can use FOR XML PATH to help construct a dynamic query:

-- Generate a list of field names to be used in the SELECT statement
DECLARE @Sql VARCHAR(MAX) =
(
    SELECT '[' + FieldNumber + '] AS [' + FieldName + '],'
    FROM Datas
    ORDER BY FieldName DESC -- Sample column ordering
    FOR XML PATH('')
)

-- Generate a SELECT query; make sure to remove the comma after the last field
SET @Sql = 'SELECT ' + SUBSTRING(@Sql,1, LEN(@Sql) - 1) + ' FROM dbo.ScannedDataCapture'

-- Show SQL before execution
PRINT @Sql
EXEC sp_sqlexec @Sql


How to prevent redundant data that is hard to detect, e.g. John Smith I have a database table to store technicians (employees) which is structured like this:

| firstName | lastName | phone | alternatePhone | email | canadaTravel | canDrive | notes |

In this table, only the first two columns are required. Any other values can be null. I would like to prevent against duplicate employees, but I realize this key cannot be made based on name; There may be two John Smiths that we hire.

My initial thought was to expand a key to (first, last, phone) or (first, last, email) but because those are nullable fields, it is not guaranteed to be unique.

Is there a way to design the table to allow for two different people with the same name, but prevent the same person being entered twice?

Perhaps you will solve this problem with little help from your UI person. Check for the presence of the name in the DB before making a new entry. If similar name is found in the DB, show some kind of warning in the UI that the user should be able to override.

Otherwise, as you perhaps realize, you can either have a unique constraint in the DB or not.


How can I manage PL/SQL package source at the method level? In our ERP application suite, we have a very small number of PL/SQL packages that are large and monolithic in nature, containing a wide assortment of functionality. (This is because each package requires additional configuration in the ERP suite itself.) Putting the entirety of an API in source control is prohibitive because simultaneous development projects can leapfrog along the pipeline. Right now, we manually deploy to QA and PROD at the method level, modifying and adding methods to the package by copying them out of text files. (We work with them using PL/SQL Developer by Allround Automations.) This is time-consuming and error-prone, and I'd like to automate this process in a DevOps fashion. Using an existing utility, is there a way to store package methods as discrete files in source control and deploy them to their package individually?

Some pondering if you're in fact having a XY problem. YMMV.

  Using an existing utility, is there a way to store package methods as discrete files in source control and deploy them to their package individually?

To me this sounds a peculiar requirement. In database a PL/SQL package is a single atomic unit (in fact there is two units (a specification and a body) but that's irrelevant in this context) that is modified and deployed as a whole.

Let's rephrase the question:

  Using an existing utility, is there a way to store Java class methods as discrete files in source control and deploy them to their class individually?

I'd say that's simply silly. IMO one should treat Oracle PL/SQL source code in the same love than any other programming language source code.

(I'm not ware of such a tool, but you very likely can make that by yourself.)

Your real problem are monolithic PL/SQL packages:

  [...] PL/SQL packages that are large and monolithic in nature, containing a wide assortment of functionality.

That conflicts your parallel development paths.

Reading the fine manual:

  A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions.

And finally the root cause:

  [...] because each package requires additional configuration in the ERP suite itself.

IMO tail wagging the dog.

Resolve this problem, refactor your PL/SQL to have a modular structure and your development/deployment conflict problems mostly disappear.

I don't know the "ERP suite configuration" details but I'm positive it's PITA-factor is much smaller than the current mess.


Postgresql Logging Questions I'm seeking some help understanding my PostgreSQL 9.3 log file.

(Note: maybe this should be multiple questions, they are all involve the same general topic - which is understanding my log file).  In my postgresql.conf file I have the following log related settings:

log_min_duration_statement = 0
log_line_prefix = '%m session_id: %c; trans_id: %x '
log_statement = 'all'

If I have the following in my log:

2014-01-15 21:39:22.217 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.217 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.132 ms
2014-01-15 21:39:22.218 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT t.oid, typarray
    FROM pg_type t JOIN pg_namespace ns
        ON typnamespace = ns.oid
    WHERE typname = 'hstore';

2014-01-15 21:39:22.219 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 1.651 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: ROLLBACK
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.043 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.028 ms
2014-01-15 21:39:22.220 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: select version()
2014-01-15 21:39:22.221 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.204 ms
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: select current_schema()
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.136 ms
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: show transaction isolation level
2014-01-15 21:39:22.222 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.065 ms
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.281 ms
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-01-15 21:39:22.223 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.063 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT 'x' AS some_label
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.053 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: ROLLBACK
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.032 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: BEGIN
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.023 ms
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: show standard_conforming_strings
2014-01-15 21:39:22.224 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 0.035 ms
2014-01-15 21:39:22.225 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  statement: SELECT api_log_entry.id AS api_log_entry_id
    FROM api_log_entry
2014-01-15 21:39:22.227 PST session_id: 52d7708a.eb7; trans_id: 0 LOG:  duration: 1.319 ms

Questions:

In this log snipped, does each log entry (everything with a duration) represent a call from the client app (and is a round trip)?

Given my log_line_prefix, why is trans_id showing up as zero?  It scare me to say but I thought Id see a new trans_id for each "BEGIN" that was logged.

Everything there is probably a client round-trip, but you'll find that multistatements show individual durations for each component statement. The times are for the time spent executing it on the server, not the time including network access.

Entries with no transaction ID appear because PostgreSQL uses "virtual transaction IDs" to reduce the rate of consumption of transaction IDs, only assigning a real txid when there's a write or lock operation.


Why am I getting ORA-00937 error using only aggregate functions? Here is a sample of what I'm trying to do:

SELECT
  COUNT(T1.FIELD) +
  (
    SELECT
      COUNT(T2.FIELD)
    FROM
      TABLE2 T2
  )
FROM
  TABLE1 T1

I don't see a need for group by clause in here.

Aggregate functions, but not on the same object, in a subquery, with addition. Use this:

SELECT
  (SELECT COUNT(T1.FIELD) FROM T1) +
  (SELECT COUNT(T2.FIELD) FROM T2)
FROM DUAL;


Using dynamic sql inside Oracle stored procedure Assuming I have the following procedure

CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type,
                     table1_val IN TABLE1.table1_value%type)
AS
  SQL_UPDATE VARCHAR2(500) := 'UPDATE TABLE1 SET table1_value =:1 WHERE table1_id = :2';
BEGIN
  --.....
  --1 :
    EXECUTE IMMEDIATE SQL_UPDATE USING foo.table1_val, foo.table1_id;
  --2 :
    UPDATE TABLE1 SET table1_value = foo.table1_val WHERE table1_id = foo.table1_id;

END;

Beside the style/readability, is it any performance penalty for using dynamic query (1) compared to (2)  in such cases (I mean when it's absolutely avoidable) ?

Thank you.

The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required.

As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the database.


Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work? I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). Since it's the Django internals, the query cannot be changed.

There are 3 tables, A, B, and C. The query looks like:

SELECT *
FROM A
   INNER JOIN B ON (A.b_id = B.foo)
   INNER JOIN C ON (B.foo = C.id)
ORDER BY A.id DESC
LIMIT 100

A simple join-3-tables together.

The id fields are primary keys and have indexes. A.b_id, B.foo both have their own indexes.

However the query plan looks wrong and says it's not using any keys on B (but it is using the keys for the other joins). From reading lots of MySQL performance stuff it should in theory be using indexes, since it's various const joins that can 'fall through'. It says it has to scan all ~1,200 rows of B.

The weird thing is that I OPTIMIZEed each one on my local machine and re-ran the queries (with SQL_NO_CACHE) and it was much faster, 0.02sec vs. the original 20sec. EXPLAIN on the same query gave a different, and much more sensible result, showing that it can use an index on each one, and that it doesn't have to scan the whole lot. A co-worker ran OPTIMIZE for each one on a testing machine with approximately the same data (which was recently recreated from a loaded dump file) and it also showed a speed increase, and a sensible-explain.

So we ran that on the live system… and it didn't change anything (the speed nor explain). I recreated my MySQL database (DROPed the database and reloaded from a dump), and now the OPTIMIZE doesn't change anything (i.e. ~20sec run time, bad query plan).

Why does this happen? How can I get MySQL to use the correct indexes and get back my 0.02s query time? This blog post ( http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/ ) implies that OPTIMIZE only really optimizes the primary key (not that b_id, foo are not primary indexes). How can I "rebuild secondary index"? I tried doing ALTER TABLE A ENGINE=InnoDB (and the same for B & C) and there was no change.

Actually the more I look at this, the more it seems to be a MySQL query plan fail. It's doing the wrong query plan and not using indexes that it could use. After doing an OPTIMIZE TABLE etc., it is sometimes able to use the correct indexes. When there's a bad query plan like this, it can be sorta randomly selected, that's why (I think) different machines have different results.

OPTIMIZE rebuilds the table.  This (for InnoDB) squeezes out some of the fragmentation and wasted space.  This is unlikely to make a noticeable difference in any query.

Also, OPTIMIZE does an ANALYZE.  This has a chance of changing the statistics, thereby leading to a different (better or worse) EXPLAIN plan.

Since ANALYZE is much faster (on InnoDB) than OPTIMIZE, just do the ANALYZE.

Various non-ANALYZE actions cause an ANALYZE to be done.

ANALYZE randomly probes the BTrees, gathering stats.  Sometimes the resulting stats are poor.  There is effectively no way to prevent this from happening.  Several partial hacks have been created over the years; 5.6.7 gets close to eliminating this problem with ANALYZE. Here's one of them: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages


Running select affects performance of stored procedure called from code In what scenario would running the same select statement in SSMS as that in a stored SP which is currently timing out start making the SP (which is being called from a service every minute (30 sec timeout)) start working?

The select in SSMS doesn't return any results and can be canceled after 60 seconds. At which point the previous failing SP is now returning results. If you run the select while the SP is being called from code it takes about 2 seconds to run.

No parameters are passed into select, there is a join to a busy event table.

To sum I'm looking for explanation as to how running one select can affect (for the better) another select.

You would have to do some digging but one way I can imagine is the SELECT is pulling data pages into cache from disk and subsequent SP runs are just getting their data straight from the buffer pool without having to hit disk.


Store Decimal values without Trailing Zeros I am using SQL Server 2008 R2 version.
Is there any method to store decimal value in SQL without extra trailing zeros(like Float data type format).
I have created a column with decimal data type with highest precision.
Actual values:
2.50000000
4.55530000
1.85333310

Expected:
2.5
4.5553
1.8533331

Is it possible to store  values in above expected format with highest precision ?

The storage of Data shouldn't concern  you, as long you have the wanted precision
and representation of the data you can do on your application.
If you want a select that will remove the trailing Zeros
you can use
SELECT convert(DOUBLE PRECISION, 20.10)


Scheduled optimization of tables in MySQL InnoDB What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the tables which is actually the largest one in my database and frequently being updated. After I run OPTIMIZE on the table, it has solved the problem.

OPTIMIZE TABLE performs the following steps internally on a table (mydb.mytable)

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

Since there is DDL involved, there is no way around queries taking a big performance hit during the operation. Additionally, not performing any optimization would be just as bad.

What you need is to have MySQL Master/Master (aka Circular) Replication set up

You could then try this:

For Servers M1 and M2 and DB VIP pointing to M1

On M2, run the following

STOP SLAVE;
SET sql_log_bin = 0;
Perform OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB on all InnoDB tables
START SLAVE;
Wait for replication to catch (Seconds_Behind_Master = 0)

The SET sql_log_bin = 0 would prevent the DDL commands from replicating over the Master.

Once those steps are complete, promote the Slave to Master, and demote the Master to Slave (can done by just moving your DB VIP from M1 to M2). You could perform this maintenance every day and production will not feel any effects with the exception of the Master Promotion and Slave Demotion.

You could create the script and run it on M2 like this:

echo "SET sql_log_bin = 0;" > InnoDBCompression.sql
echo "STOP SLAVE;" >> InnoDBCompression.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBCompressionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" >> InnoDBCompression.sql
echo "START SLAVE;" >> InnoDBCompression.sql
mysql -u... -p... -A < InnoDBCompression.sql

From here, just wait for Seconds_Behind_Master to be 0 on M2, then move the DBVIP from M1 to M2. Now if you know the specific names of the tables you want optimized, you could adjust the query to fetch just those tables.

Give it a Try !!!

CAVEAT

Here is a fair warning: If you have innodb_file_per_table disbaled, every time you run OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB; the ibdata1 file just grows. You would need to cleanup the InnoDB infrastructure to prevent ibdata1 from growing out of control.


Mysql: Problem using GREATEST with IFNULL and join I have a query selecting the greatest value of 2 timestamps.

To achieve it I'm calling:

SELECT GREATEST( IFNULL( tableb.create_time, 0 ) , IFNULL( tablea.create_time, 0 ) )

I'm using IFNULL because the create_time value can be null.
The problem is that the result is something like '2014-0' instead of '2014-08-14 17:04:39'

Here's a fiddle to illustrate my problem

The result is OK when:

I'm not using IFNULL
I'm using IFNULL but I have no join in the query (so the GREATEST is performed on 2 timestamps in the same table)

So for me the problem comes really from the use of GREATEST and IFNULL in a join query.

SELECT
    GREATEST
    (
        IFNULL(tableb.create_time,FROM_UNIXTIME(18000)),
        IFNULL(tablea.create_time,FROM_UNIXTIME(18000))
    );

It just so happens that FROM_UNIXTIME(18000) is '1970-01-01 00:00:00'


Binary path in the pgAdmin preferences I installed PostgreSQL because I need to restore a large .backup file I received for upload to another platform (it's 38 million rows which was apparently too large for the person to export any other way). When I try to "restore" in pgAdmin 4 I get the message:

  Please configure the PostgreSQL Binary Path in the Preferences dialog.

Is this just the path to the PostgreSQL folder? I can't find documentation on this on the PostgreSQL site. All I can find is:

  Use the fields in the Binary paths node to specify paths to the PostgreSQL binary utilities and EnterpriseDB Postgres Advanced Server binary utilities.

Is there an easier way to restore the database other than through pgAdmin?

Configure pgAdmin->Paths->Binary paths and set "PostgreSQL Binary Path" as shown in the screenshot.  Depending on your OS and installation details, the binaries may be located elsewhere.  Try

`which psql`

from the command line on linux/Unix systems.

A Windows example:
PostgreSQL Binary Path: "C:\Program Files\PostgreSQL\9.6\bin"


How to concatenate psql variables? How can I concatenate two psql (PostgreSQL client) variables? I want to generate an absolute path by concatenating a directory path variable and a filename variable.

I've tried this:

\set path '/tmp/'
\set file 'foo'
\echo :path:file

But psql puts a space between the path and the file, and outputs:

/tmp/ foo

\set path '/tmp/'
\set file 'foo'
\set pf :path:file \echo :pf
/tmp/foo

Why does this work? I quote the manual here:

  \set [ name [ value [ ... ] ] ]

  Sets the internal variable name to value or, if more than one value is given, to the concatenation of all of them. [...]

Emphasis mine.


How to remove duplicates from any array and preserve ordering in PostgreSQL? I'm looking for a way to eliminate duplicates from a PostgreSQL array
while preserving the ordering of its elements. What I currently
have are the following functions:

create function array_unique( anyarray )
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) ); $$;

create function array_unique_sorted( anyarray )
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) order by 1 ); $$;

/* ### TAINT there ought to be a simpler, declarative solution */
create function array_unique_stable( text[] )
  returns text[] immutable strict parallel safe language plpgsql as $$
  declare
    R         text[] = '{}';
    ¶element  text;
  begin
    foreach ¶element in array $1 loop
      if not array[ ¶element ] && R then
        R :=  R || array[ ¶element ];
        end if;
      end loop;
    return R; end; $$;

In the above, array_unique takes an array of any type and returns a copy
with all duplicates removed; their relative ordering is arbitrary.
array_unique_sorted is like array_unique, but the elements are sorted
relative to each other; this is sometimes useful as all arrays with the
same set of of distinct elements will compare equal after being
normalized by this function.

array_unique_stable already does what I'm looking for: given an array
(which in this exampe must be a text[] array), it scans elements from
left to right; whenever it encounters a previously unseen element, it adds
that one to the result. Thus, only the first occurrence of each value
is kept.

However, the implementation has some drawbacks: First, there seems to be no
way to write it so it accepts the pseudo-type anyarray.

Second, while
the first two functions are written in SQL, they may presumably be inlined,
array_unique_stable is written in PL/pgSQL, and so it cannot be inlined.

Third, it bugs me that I couldn't come up with a solution in pure SQL...

This can indeed be done using pure SQL:

create function array_unique_stable(p_input anyarray)
  returns anyarray immutable strict parallel safe
  language sql
as
$$
select array_agg(t order by x)
from (
  select distinct on (t) t,x
  from unnest(p_input) with ordinality as p(t,x)
  order by t,x
) t2;
$$

The unnest(p_input) with ordinality will return the original index of the element in the array which is then used to aggregate them back in the outer query.

select array_unique_stable(array['a','x','x','b']) as text_array,
       array_unique_stable(array[10,1,1,5,8,8]) as int_array

returns

text_array | int_array
-----------+-----------
{a,x,b}    | {10,1,5,8}

and


IS NOT DISTINCT FROM vs row-wise equality with = Looking at the below query,

SELECT null IS NOT DISTINCT FROM null AS indf,
  null = null AS eq;
 indf | eq
------+----
 t    |

From this we can see the result of the IS NOT DISTINCT FROM is true, and the result of the eq is false. The following then logically follows from that,

-- 1 rows
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON t.a IS NOT DISTINCT FROM g.a;

-- 0 rows
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON t.a = g.a;

Because if the condition returns null the join fails. But, this throws me off, with row-wise comparison

-- returns 1 row.
SELECT *
FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON (t) IS NOT DISTINCT FROM (g);

-- also returns one row.
SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON (t) = (g);

Why does row-wise comparison treat nulls different than scalar comparison? And is there a point of IS NOT DISTINCT FROM in row-wise comparison?

This behavior is documented under Composite Type Comparison,

  The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two row constructors (as in Section 9.23.5) or comparing a row constructor to the output of a subquery (as in Section 9.22). In other contexts where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.

This essentially means that the standard behavior can only be seen in the following syntax (ROW() is the row-constructor):

SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON ROW(t.*) = ROW(g.*);

versus,

SELECT * FROM ( VALUES (null) ) AS t(a)
JOIN ( VALUES (null) ) AS g(a)
  ON ROW(t.*) IS NOT DISTINCT FROM ROW(g.*);

Just using the ROW constructor with the type will not work either ON ROW(t) = ROW(g)


Execute permission denied on object sp_start_job I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified):

ALTER PROCEDURE [dbo].[RunJob]
    @job_name nvarchar(200)
WITH EXECUTE AS 'sysadminaccount'
AS
BEGIN
    --SET NOCOUNT ON;
    BEGIN TRY
        EXEC msdb.dbo.sp_start_job @job_name = @job_name

        -- Wait for job to finish
        DECLARE @job_history_id AS INT = NULL
        DECLARE @job_result AS INT = NULL

        WHILE 1=1
        BEGIN
            SELECT TOP 1 @job_history_id = activity.job_history_id
            FROM msdb.dbo.sysjobs jobs
            INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
            WHERE jobs.name = @job_name
            ORDER BY activity.start_execution_date DESC

            IF @job_history_id IS NULL
            BEGIN
                WAITFOR DELAY '00:00:01'
                CONTINUE
            END
            ELSE
                BREAK
        END

        -- Check exit code
        SET @job_result = (SELECT history.run_status
        FROM msdb.dbo.sysjobhistory history
        WHERE history.instance_id = @job_history_id)

        RETURN @job_result;

    END TRY
    BEGIN CATCH
        THROW;
        RETURN;
    END CATCH
END

However, when I call this procedure (having verified it is running via "sysadminaccount"), I get the following error message:

  Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
  The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

The account is a member of the sysadmin role, so I as understand it there shouldn't be any problems kicking off jobs. I've verified that it is a member of the three sqlagent roles in msdb, and those roles all have execute permission on sp_start_job.

How can I give this account the appropriate permissions? Is there something else that needs to be done because of the impersonation?

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

The TRUSTWORTHY Database Property Explained – Part 1
The TRUSTWORTHY Database Property Explained – Part 2
The TRUSTWORTHY Database Property Explained – Part 3
TRUSTWORTHY Database Property
KB #2183687 : Guidelines for using the TRUSTWORTHY database setting in SQL Server

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.)
So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

Create the procedure in msdb.
Create a user for this user's login in msdb:

USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

Grant the user execute privileges on the stored procedure:

GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;

Test it - either by calling the procedure from another database:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

Result should be:

I am really...
---------------
sysadminaccount

Validate that this doesn't expose anything else in msdb to this user:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;

Result should be...

  Msg 229, Level 14, State 5, Line 21
  The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).


Wrong Last Execution Time sys.dm_exec_procedure_stats I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I am missing here or is this an issue on the server? Everything I have found online refers to the opposite issue where people are getting reports that the proc is running when it isn't.

SELECT * FROM sys.dm_exec_procedure_stats AS s
INNER JOIN sys.procedures AS p
ON s.[object_id] = p.[object_id]

The core problem is that your query can potentially match on two different procedures in two different databases that happen to have the same object_id. While there isn't a reliable way to reproduce it on demand, I've seen it enough times to know it is common, and your query needs to protect against it. You can do that with a WHERE clause:

WHERE s.database_id = DB_ID();

When pulling stats for other databases, you can use:

...
FROM sys.dm_exec_procedure_stats AS s
INNER JOIN [other_database].sys.procedures AS p
ON s.[object_id] = p.[object_id]
WHERE s.[database_id] = DB_ID(N'other_database');

Note 1: The stats DMV does not persist data beyond service restarts, failovers, or any action that causes the procedure cache to be flushed (for the instance, for the database, or for a specific procedure). You also won't see any entry in the DMV for stored procedures that are intentionally not cached, e.g. EXEC dbo.procedurename WITH RECOMPILE;. So it isn't necessarily reliable to conclude that procedure A is unused simply because it isn't present in the DMV.

Note 2: It's possible for a database named foo to have different database_id values over its lifetime (consider the case where you drop foo, create another database, then create another instance of foo). This is unlikely, but is still worth mentioning, because you could potentially miss executions of a stored procedure that happened in the older copy of foo.

Note 3: If you want to truly measure determine the set of procedures that are unused, you will have to use heavy methods like auditing / extended events / server-side trace, or manually add logging to your stored procedures as I describe in this tip:

Simple process to track and log stored procedure use

And even then, you need to leave that running for a full business cycle, to be sure you don't miss reports that are only run, say, once a quarter or other fiscal period.

Once you have identified the set of stored procedures you don't think are being used, don't drop them; put them in a separate schema (I'm using zzz here so it sorts at the bottom of the list in Object Explorer, but the name is unimportant - just make sure none of your users have it set as their default schema, and maybe even implement DENY EXECUTE).

CREATE SCHEMA zzz;
GO
ALTER SCHEMA zzz TRANSFER dbo.UnusedProcedureName1;
ALTER SCHEMA zzz TRANSFER dbo.UnusedProcedureName2;
...

This doesn't alleviate clutter quite as effectively as dropping the procedures, but is kind of like a very low-cost insurance policy (only the storage for the stored procedure text in the data file and backups), making it very easy to restore a procedure that was deemed unused in error.


Inside a trigger create database is not working I tried using trigger concept. If any new row inserts in the user table my trigger will fire and try to create a dynamic database for that new user. But Inside a trigger create database is not working. How can get it done. Can anybody have any suggestions.Please help of on this

As the documentation says (under Trigger Limitations):

  The following Transact-SQL statements are not allowed in a DML trigger:
  (...) CREATE DATABASE (...)

There are further limitations, consulting the documentation is highly recommended.

This all means, to my understanding, that you should solve this problem at application level*: check whether your user is successfully created, and then issue a CREATE DATABASE statement.  Preferably these two are enclosed in a transaction, so that either both succeed or both fail - this way you can be sure that your data are in a consistent state.  You may have to issue a DROP DATABASE if the transaction fails.  (Thanks to Jon Seigel for pointing this out.)

*: From this point of view, the 'application' may be a T-SQL block of code (or a stored procedure) as well.


Log shipping restore speed & index rebuild vs reorganize Does the log shipping server restore a transaction log backup faster if the index on primary server are rebuilt or reorganized? On the log shipping server the restores are fast in phase one but seem to take a long time in the redo & undo phases. Like a tlog backup that took a few minutes on the primary is taking 1-4 hours to restore. I am using Ola Hallogren's routines on the primary server with the default 5% reorg & 30% rebuild levels but I'm wondering if switching to only rebuild and increasing the % will help speed up the restores. I'm not really concerned with how long the defrag takes on the primary but how fast they can be restored on the secondary. Does bulk-logged help vs full recovery?

If you want to improve the speed of your tlog restores, you will want to look into the bulk-logged recovery model.  I would never suggest running a database under this recovery model exclusively, though switching to it during your maintenance window will help significantly in your case.

The key here is to be very explicit on the approach you take when switching recovery models.  From the source, anytime you wish to utilize the bulk-logged recovery model, you should follow these steps:

Before you switch to the bulk-logged recovery model, take a tlog backup
Switch the recovery model of the db to bulk-logged
Perform your minimally logged operations (e.g. index maintenance, etc.)
Switch the database back to the full recovery model
Immediately take another tlog backup

Here's a handy little infographic that outlines the approach:

In an ideal situation, MS also encourages the following:

Users are currently not allowed in the database, or their activity is kept to a minimum.
All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

These are not hard-line requirements, but good practice recommendations and the reason behind them is that all activity that completes during the period where the database is in bulk-logged mode (once a minimally logged operation occurs) will either have to be redone if you roll back to the tlog backup taken in step 1 or will be committed if you restore the tlog backup taken in step 5.  The yellow window identified in the infographic is an all-or-nothing sort of process, and you are unable to do any point-in-time recovery during that time-frame.  The moment you convert the database back to the full recovery model and take another tlog backup is the moment you can once again utilize point-in-time recovery.

Finally, there are some restrictions to using the bulk-logged recovery model, such as considerations for databases with read-only filegroups and online recovery scenarios, so do some testing to make sure you're not causing yourself more headache than necessary.

I've used the bulk-logged recovery model for years and it's quite helpful so long as you understand how to use it properly.


DB2 connection rejected with JDBC and ODBC Good day to all,
Currently I have 4 as400 servers, with db2 installed, is the production server and development server with its two replicas.

The development java team uses IBM Toolbox for Java and JTOpen for conection to db2, in a Weblogic middleware, for direct connections to the database uses DbVisualizer with JTOpen (jt400.jar)

The team of RPG no has problem, at connect with IBM Access for Windows and work with DB2

Mysteriously on Sunday the development server connections began to be rejected for Java team

The error log is:

Long Message:
The application requester cannot establish the connection. (Not able to pass connection to server job. Server job program was not found.)

Details:
   Type: java.sql.SQLException
   Error Code: -99999
   SQL State: 08001

None of the other three servers has this problem. In this page of support recommend 3 possible solutions

The system name is incorrect. One way to verify this is to use ping
with the specified system name. If ping fails, then there is a
TCP/IP configuration problem between your client and the system.
The user ID or password is incorrect.
The IBM i database host server is not started. You can start this by running STRHOSTSVR *DATABASE on the IBM i command line. You
can verify if the IBM i database host server is running using the
utilities.JPing application.

In step one send a ping to the server and it responds correctly in all machines of java team
The step 2 was extensively tested, and 3 was able be verified, RPG dev team was not problem

A test was made more with an ODBC client and the connection was rejected also with this error log

Error in the connection test in initializing provider CWBSY1008

There was an error overall security rc=8007

I tried to find solution, but I have not found anything.

Other things I made

IPL Partial and Full - Result not work
Resetting router tables - Result not work
Power off firewall and antivirus - Result not work
Resetting password of QUSER - Result not work

Could you please help?

As I understand it, your development team got the error when using DbVisualizer with JTOpen (jt400.jar) for direct connections to the database.

I got the same exception with DBvis ver 9.1.10. The tool took a long time when clicking the connect button, then finally showed error code -99999.

What I do to fix this is to disable the Proxy setting of DBvis:

Menu: Tool/Tool Properties
Go to General Tag->Proxy Setting
Check "No Proxy" and restart DBvisualizer.


MS Access .mdb & .ldb database corrupted I am using a A.mdb database. You know when connecting the database Microsoft automatically create a .ldb file. Unfortunately my pc shutdown when connecting the database. As a result after restart the PC I couldn't open the A.mdb file. The message shown A.mdb database is corrupted.

A.mdb database was password lock but after corrupted if I give the valid password the database not open and showing the password not valid.

Now I couldn't open the database. Now it shown A.mdb file with A.ldb file. Please advise me if PC shutdown with the connected .mdb database then why the database not recover after restart? Also i tried with the option Database Repair and Recovery.

Open in Safe Mode
Open the Access application with the following option:
C:\> "C:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe" /safe "C:\path to mdb file\your.mdb"

Open and compact/repair
Open the Access database with the /compact parameter:
C:\> "c:\program files\microsoft office\office12\msaccess.exe" /compact "C:\path to mdb file\your.mdb"

Reference: Command-line switches for Access
Open the database and repair

In Microsoft Access 2000 and later, compact and repair functionality is combined under /compact. The /repair switch is supported for backward compatibility

Open the Access database and repair it with the following parameter:
C:\> "c:\program files\microsoft office\office12\msaccess.exe" /repair "C:\path to mdb file\your.mdb"

Bypass Startup Options
Alternatively try opening the Access database while bypassing any startup options:

Hold down SHIFT key
Open Microsoft Access database with a double-click on the MDB file
If messages pop up, keep SHIFT depressed and confirm any messages
Wait for Access Database to open

Reference: Bypass startup options when you open a database
Last possibility
Buy a repair software


# of key locks while updating I am using SQL Server 2008, RTM. I have a simple table defined as

create table t(id integer not null primary key)

This table has 50,000 records, just in case. When I issue an update transaction like

begin transaction
Update t
set id=50001
where id=5;

with no commit/rollback, it shows me one row affected. When I take a look at the result of the sp_locks, I and see there are two X locks on the KEY type granted, the Resource values for them are different from each other, the spid is the same.
Why there are two X locks on the key while there is only one row updated? Thanks.

Because you're taking one key value, and assigning it another key value.

It's 2017, so I'm not going to use sp_lock. I'm going to use sp_WhoIsActive.

If we run it like this: EXEC sp_WhoIsActive @get_locks = 1 we can get lock information.

CREATE TABLE t
(
    id INTEGER NOT NULL PRIMARY KEY
);

INSERT dbo.t ( id )
SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY @@DBTS)
FROM (SELECT 1 AS n FROM sys.messages AS m CROSS JOIN sys.messages AS m2) AS x

If we update one key value:

BEGIN TRANSACTION;

UPDATE t
SET    id = 50001
WHERE  id = 5;

ROLLBACK

The lock information looks like this:

<Object name="t" schema_name="dbo">
      <Locks>
        <Lock resource_type="KEY" index_name="PK__t__3213E83FE76849A4" request_mode="X" request_status="GRANT" request_count="2" />
        <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="PK__t__3213E83FE76849A4" request_mode="IX" request_status="GRANT" request_count="2" />
      </Locks>
    </Object>

Two key locks! One key lock to get the key value we're modifying, and one lock to reserve the key value we're modifying it to.

If we modify two values...

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10)

ROLLBACK

We end up with four key locks. This pattern will continue if we do 3 or 4. There will be two key locks for each ID touched.

<Object name="t" schema_name="dbo">
  <Locks>
    <Lock resource_type="KEY" index_name="PK__t__3213E83FE76849A4" request_mode="X" request_status="GRANT" request_count="4" />
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="PK__t__3213E83FE76849A4" request_mode="IX" request_status="GRANT" request_count="2" />
  </Locks>

Hitting three values:

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10, 15)

ROLLBACK

Hitting four values:

BEGIN TRANSACTION;

UPDATE t
SET    id += 50001
WHERE  id IN (5, 10, 15, 20)

ROLLBACK

At least up until we modify everything.

BEGIN TRANSACTION;

UPDATE t
SET    id += 1

ROLLBACK

Then the lock type changes!

   <Object name="t" schema_name="dbo">
      <Locks>
        <Lock resource_type="ALLOCATION_UNIT.BULK_OPERATION_PAGE" index_name="PK__t__3213E83FE76849A4" request_mode="S" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>

This is due to the magic of the split, sort, and collapse operations, which you can see in the query plan here.

Hope this helps!


Space is removing at end of the line While running the insert script in sql*plus, space is removing. Can you please help us is there any plus command available in Oracle to not remove space at end of the line.

Ex: After the text 'Ameerpet,' we have space in the insert script. I have run this command through sql*plus it was success but in output space got removed.

insert into emp values(1,'
Mithrivanam, Ameerpet,
Hyderabad, Telangana, 500018
');

Output:

Mithrivanam, Ameerpet,
Hyderabad, Telangana, 500018

If you really want to have that specific space and line break, you should not rely on enviroment settings, but explicitly specify it:

insert into emp values(1,'
Mithrivanam, Ameerpet, ' || chr(10) ||
'Hyderabad, Telangana, 500018
');


Dynamic relations in MongoDB I'm designing a document with MongoDB where users have relationships with each other. Easy enough, right?

{
    _id: "012",
    username: "happy4u",
    // ...
    related: [
        '123', // user ids
        '456',
        '789'
    ]
}

But then I want to check if some user (the above), has some attribute in common with other guys in his relation. This is also easy, right?

But what if I wanted to check what he has in common with other guys related to the users in his own related list, not just one level, but multiple levels?

I thought of having a loop over the related list and then checking the result, then have another find in those results and have another one in the result's results, ... but this makes no sense to me.

Then I thought of having a separate document for each user and storing their relations, perhaps 4 levels of relation. Something like an index of my own. This also is very hard to keep clean and every update in some user's list will require updates in this document. Not just one place but in multiple places. This way also makes no sense to me.

How can I achieve my goal? Do I need to change my schema? How can I design  my schema to be more dynamic?

The pattern you describe is known as "friend of a friend." Here are a couple of answers on SO which may guide you.

In the relational database world the friends would be found by following foreign keys.  Document DBs, however, eschew explicit foreign keys enforced in the DBMS and join-like functionality in favour of other benefits. It is down to your application to recurse over successive results, even if that "makes no sense."

If you are able to expand your technology stack, a graph database would provide an excellent solution to this particular use-case. You could hold just the user-user connections to get the total list of UserIDs to whatever depth was required, then look each up in MongoDB; or you could hold the attribute values, too, and filter on like-values in the graph DB.

To speed the like-value search in MongoDB you could store a document for each attribute value, each with an array of all users who have that value.  For a given user you would then retrieve his record and the record for the given user's attribute value. The intersection of the user's friends and the attribute's user list are the friends-with-the-same-value.  Two documents only must be read rather than however many friends the given user has. Similarly on update only two documents must be written.  Sadly this does not help with finding friends-of-friends, of course.


Books to start with big data I would like to start learning about the big data technologies. I work in this area in the future. Does anyone know good books to start learning about it? Hadoop, HBase.

Beginner -
intermediate -
advanced -

Thanks in advance

The first primer which will save you research is by Martin Fowler.
NoSQL Distilled.  Available on Kindle from Amazon.

Reviews the types of NoSQL databases.

Key Value Pair {Redis, Raik}, Uses {Web Sessions, Shopping Cart, User Profiles}
Document {MongoDB, CouchDB...}, Uses {EventLogging, CMS, Blog}
Column {Cassandra, Hbase...}, Uses {EventLogging, CMS, Blog}
Graph {Neo4j,FlockDB...},Uses {Connected data, location based}

Each of the databases have specific use cases.  Document and Column will store more of Domain models like you would see in your code.  (Customer, Orders, OrderDetails)

You can get Mongo Up and running in short order.
View at docs.mongodb
- Windows
- OSX
- Linux

Once you code up a few command line apps to push data and query it out you can start to build your RESTful services against the mongodB.

To move to the intermediate and advanced you can visit 10gen for Videos and books for monogdB.

mongly.com - Has some free items for you to read as well.

Start with one NoSQL and then you can move to a few others.  Document and Column would be a good starting point, we choose mongo and then Redis.  That's the path we took when moving off of SQL Server.


SQL query to identify missing hinted indexes We have a fairly large number of queries using index hints in our 11.2.0.4 database.  I recently uncovered some performance issues and found that some of the indexes referenced by the query hints were missing.  I'm concerned that there may be more cases of this, and I'm wondering if it's possible to construct a query to do the following:

Search the cursor cache for all queries with index hints
Extract the index name from each hint and search the data dictionary for the index.
Return back the name of each index that is missing.

The indexes all follow the same naming scheme: 6 letters, followed by 6 digits, followed by the string "$INDXn", where n is a digit 0-9.

Any suggestions how to approach this?

Personally, I would start with DBA_HIST_ACTIVE_SESS_HISTORY, and look at all statments that contain that type of hint.

From there, you can pull the index name coming from that hint, and then do a lookup on dba_indexes to see if the index exists, is valid, etc.

You should be able to do this via PL/SQL if you want to make it really fancy and do it all in one step, otherwise a few pieces of SQL and a spreadsheet application can be your friend.


What are these Errors in a Previously Successful MySQL Script? Several years ago I began work on a project that uses MySQL for the database. Much of the development happened prior to MySQL 8, but I last deployed the project on an early version of MySQL 8. And, have updated the deployment to 8.0.15 since then.

That prior deployment went smoothly including just running a large DB creation sql script. And it's still working (though hidden / not default) at codehawkins.com/index.php.

I haven't worked on this (or anything SQL) in about a year, but went to deploy a dev instance of it in order to explore continuing development. On a fresh install of MySQL 8.0.19 and using MySQL Workbench, my previously working code is full of errors and odd warnings...

For example, warnings and errors for one function are indicated in comments below:

-- The initial drop function line warns me that the function doesn't exist.
--   I don't remember seeing that when using "IF EXISTS".
-- More importantly, the warning at this point tells me that code
--   prior to this point is not likely the cause of later errors,
--   as it parsed this DROP command correctly.
DROP FUNCTION IF EXISTS fnGetLUID;
CREATE FUNCTION fnGetLUID(newUse VARCHAR(128))
  RETURNS BIGINT
BEGIN
-- The following line has an error indicating that a semi-colon is expected.
--   Yet there is one, and again, this used to work.
  INSERT INTO LUID (usedFor) VALUES (newUse);
-- An error here indicates that "RETURN" is not valid at this position.
  RETURN LAST_INSERT_ID();
-- Likewise, error reports "END" is not valid at this position.
END;

The entire script is at https://github.com/HumanJHawkins/CommunityPress/blob/master/documentation/v4lSchema.sql if useful to understanding this.

Were there changes in mySQL between 8.0.15 and 8.0.19 that break this, or is there some configuration on how to parse the language that I am missing?

Thanks in advance for any help.

I didn't realize this was a two-part question when I asked it:

What are the errors?
Thanks to @Akina for the comment that answered this part of it... MySQL and/or MySQL workbench is unable to distinguish the delimiters that end individual statements from the delimiters that end the creation of the function. So typically one must set a different delimiter outside the creation of the function, then return the delimiter to normal after. I.e.

DELIMITER $$;
-- Create DB object here, using semi-colon at end of all statements except the last one.
DELIMITER ;

See https://www.mysqltutorial.org/mysql-stored-procedure/mysql-delimiter/ for more detail. As one who has never had to do that, it sounded like an annoying bit of extra work. But...

Why did this always work before?
Previously, I was running my script via JetBrains DataGrip. I believed the interface there to basically be just a terminal into mySQL. However, it turns out that DataGrip was automatically handling the necessary parsing of statements. So the manual delimiter handling was unnecessary within that environment.


SSMS cannot connect remote server by IP through Windows authentication I'm trying to connect a remote server by IP,port via Windows authentication.
It will end out with the following error

  Connection Timeout Expired.  The timeout period elapsed while
  attempting to consume the pre-login handshake acknowledgement.  This
  could be because the pre-login handshake failed or the server was
  unable to respond back in time.  The duration spent while attempting
  to connect to this server was - [Pre-Login] initialization=41306;
  handshake=27677;  (Microsoft SQL Server, Error: -2)

However, if connect to IP,port but login with sa through SQL Server authentication then I can connect to the server correctly.

Moreover, if I switch to MachineName,port and login via Windows authentication then I can successfully connect too.

Not sure what kind of setting/adjustment I need to make this use case work.

Environment

My laptop is Win10 and the SQL Server 2012 is on a Windows server 2012 R2
These two machines are in the same domain.
We are using another port instead of default 1433.

Case I tried

remote IP,port via Windows authentication - failed
remote MachineName,port via Windows authentication - successful
remote IP,port via SQL Server authentication(sa account) - successful
remote IP\MSSQLSERVER,port via Windows authentication - failed
RDP to remote then use ssms there IP,port via Windows authentication - successful

Setting I have tried

Add the SQL Server exe to the inbound rule
Launch SQL Server browser
Change IP1 to the IPv4 I'm using and Active/Enabled it(restart service too)
Make sure TCP/IP are enabled on Protocols of MSSQLSERVER and Client Protocols(/32bit)
Add the port I'm using to IPAll/TCP Port and keep Dynamic ports blank

So it turns out the timeout is actually a timeout...
After I expend the connection timeout to 90 sec it connected after about a minute.

Infrastructure guys say this might since it's trying to resolve the machine name or something? (I'm not really familiar with network and setting stuff).
I will update this answer if they figure out any way to speed up the process.


MySQL code to show specific rows based on another...? I'm having a hard time figuring out how to do this, and I can't explain it well.
Since I don’t know how to explain it I can’t seem to locate an answer online by simple research. So, I have to draw it out and ask for assistance.
Here are the details:

Columns for the example: My original SQL query created this table (Data specific to this problem comes from one HUGE table)

Sample code:

SELECT
inventory .01 as  `Item#`,
inventory .02 as `warehouse`,
inventory .03 as `qty`,
inventory .04 as `price`,
inventory .05 as `weight`,
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5

Returns this:

Item# | warehouse | qty | price | weight | other
1           1        1      3        4       5
1           2        6      3        4       5
1           3        3      3        4       5

I've re-written the table using group by to make the data look like this:

SELECT
inventory .01 as  `Item#`,
inventory .03 as `qty (whse1)`,
inventory .03 as `qty (whse2)`,
inventory .03 as `qty (whse3)`,
inventory .04 as `price`,
inventory .05 as `weight`,
inventory .06 as `other`
FROM
Inventory
WHERE
Inventory.other = 5
GROUP BY
inventory .01

Returns This:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            1             1           3        4       5

Here’s where I’m stuck. I don’t know how to tell write the SQL code to make the 3 warehouse fields to show like this:

Item# | qty (whse1)| qty (whse2) | qty (whse3) | price | weight | other
  1         1            6             3           3        4       5

I’m sure this is easy to accomplish. I just can’t figure it out.

My DB contains 30K rows, and each item is included 10 times (once per warehouse.) I want to be able to see 3K rows instead, based on the item no, with the qty per warehouse. This is just a sample of the data, the real tables and fields are clearly labeled. This is just a sample of the issue I'm dealing with.

The price, weight and other are all the same for all the items. The only real difference for these parts is the qty per warehouse.

Adding to give real code example. The responses I've received so far seem to come from creating a table from scratch vs. selecting data from an existing table. I've tried implementing the example and I'm getting errors. I can attach a screenshot of some of the data to give you an example of the content included in the database.

SELECT
inventory.`CODE` AS `Item No.`,
inventory.INV_DESCRIPTION AS Description,
COALESCE(inventory.ONHAND,0) AS `On Hand (LA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (SF)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (HP)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (CHINA)`,
COALESCE(inventory.ONHAND,0) AS `On Hand (INDO)`,
pricing.BVRTLPRICE01 AS `SF Warehouse`,
pricing.BVRTLPRICE02 AS `LA Warehouse`,
pricing.BVRTLPRICE03 AS `HP Warehouse`,
pricing.BVRTLPRICE04 AS `FOB China/Indonesia`,
pricing.BVRTLPRICE05 AS ECOMM,
inventory.CUBE,
inventory.WEIGHT,
inventory.DIMENSION,
inventory.PROD AS `Status`
FROM
inventory
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (LA)`
AND inventory.WHSE=00)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (SF)`
AND inventory.WHSE=10)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (HP)`
AND inventory.WHSE=20)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (CHINA)
` AND inventory.WHSE=50)
LEFT JOIN inventory.ONHAND on (inventory.`Item No.` = inventory.`On Hand (INDO)
` AND inventory.WHSE=70)
INNER JOIN pricing ON inventory.`CODE` = pricing.BVSPECPRICEPARTNO
WHERE
inventory.WHSE IN (00, 10, 20, 50, 70) AND
inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY
inventory.`CODE`,
inventory.WHSE ASC
ORDER BY
`Item No.` ASC,
inventory.WHSE ASC,
inventory.PROD

Given this test data: (this is a hint on how to make a more readable question in the future):

create table inventory(
  id int unsigned primary key auto_increment,
  item int unsigned,
  warehouse int unsigned,
  qty int unsigned,
  price int unsigned,
  weight int unsigned,
  other int unsigned);

insert into inventory(item, warehouse, qty, price, weight, other) values
  (1,1,1,3,4,5),
  (1,2,6,3,4,5),
  (1,3,3,3,4,5);

> select item,warehouse,qty,price,weight,other from inventory;
+------+-----------+------+-------+--------+-------+
| item | warehouse | qty  | price | weight | other |
+------+-----------+------+-------+--------+-------+
|    1 |         1 |    1 |     3 |      4 |     5 |
|    1 |         2 |    6 |     3 |      4 |     5 |
|    1 |         3 |    3 |     3 |      4 |     5 |
+------+-----------+------+-------+--------+-------+

You can have the desired output with the following query:

> select distinct
  i.item,
  COALESCE(w1.qty,0) as w1,
  COALESCE(w2.qty,0) as w2,
  COALESCE(w3.qty,0) as w3,
  i.price,
  i.weight,
  i.other
from inventory i
  left join inventory w1 on (i.item = w1.item AND w1.warehouse=1)
  left join inventory w2 on (i.item = w2.item and w2.warehouse=2)
  left join inventory w3 on (i.item = w3.item and w3.warehouse=3);

+------+------+------+------+-------+--------+-------+
| item | w1   | w2   | w3   | price | weight | other |
+------+------+------+------+-------+--------+-------+
|    1 |    1 |    6 |    3 |     3 |      4 |     5 |
+------+------+------+------+-------+--------+-------+

COALESCE function and LEFT JOINs are for the case that there is no row for item, warehouse combination


Finding all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle The AWR report provides the SQL information for top 10-20 SQL statements. Is it possible to get information on SQL statements that do not make it to the top list?  How do we find all sql id, sql text, number of executions, execution time between two AWR snap ids in oracle?

You can select from the below views.

Historical SQL statistics: DBA_HIST_SQLSTAT
All SQL text: DBA_HIST_SQLTEXT
Map snap_id to actual time: DBA_HIST_SNAPSHOT


Why ".my.cnf" file is empty? I want to edit some of InnoDB variables ,I go to the .my.cnf which is located in home/.my.cnf I found the file just empty.

And when I check SHOW VARIABLES in MySQL it gives me all the variables and their values.

Why the file is empty and how can I edit the needed variables ? and can that affect my database or damage it in anyway?

NEEDED Variable:

innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 2G
innodb_log_file_size = 1G
innodb_log_buffer_size = 8M

The ~/.my.cnf file is usually used for client options (for example changing the default port or host).

mysqld will likely start as a different user and will not read your .my.cnf file.  It is recommended to set these options in /etc/my.cnf instead.


Is tlog backup on replica clearing VLFs on the primary? Is it true that a transaction log backup made on the secondary database is clearing the inactive VLFs on the tlog file on the primary database (in case of Availability Groups)?

I was not able to find an official documentation/statement/article that confirms this. This is the most detailed article I have found, but the next parts that are concerning this aspect are missing.

Thomas Stringer has answered a very similar question at:

Transaction Log Maintanance While Using AlwaysOn Availability Group

The answer is that the Availability Group replicas are aware of each other and when either the primary or secondary does a backup, both logs are able to free log space.

The article you referenced also offers the explanation that Log Blocks, not the VLFs, are what get hardened between the Primary and Secondary media. A VLF can contain several log blocks and this will influence when the space can be recovered.


Architecture design for a large number of db records upgrade Honestly struggled a bit with the title of the question. Here is the deal. I have a specific set of db operations on various tables across an db. In a simpler sense it's kind of a migration, that I want to roll out in production i.e. among live users.

So, the requirement is how I can do this in transaction without actually using transaction? There are a few assumptions and may be incorrect, please feel free to help me figure it out.

I think we can't use transaction in db level cause this process may take too long, have around 1k independent operation that may require to validate the existing state of a record and updating it.
Then again if for any reason, something thing goes wrong in between, I don't want to end up with partially executed migrations.
In another note, this feels like an software upgrade, when they put in new version changes and in case of an issue rolls back. If I strictly based on that, I have to get the current records that are going to be affected from migration, keep them for restoring purpose, then apply the migration. If anything fails, re-apply with original records (kind of reverting) with the same risk.

With a server (node.js) and live user involved, it sounds more risky and difficult to control the whole thing. Is there any idea on level of architecture that can help? Thanks in advance!

Updates

  Are you expecting that a transaction will lock a table for several
  seconds or minutes? Can it be broken into small batches?

  When you say it will "take too long," does that mean it has to
  complete within a certain timeframe for business requirements, or
  you're concerned it will block table updates for too long

Yes, it can take a significant time and indeed, we can divide the process into small batches. There are 2 things that we need to be ensure of. The whole operation (even broken into batches) should be transaction in nature. I am not really concerned about the time it's going to take, instead the tables/rows will stay locked for that time, in case anyone is trying out something.

You should test performing all the changes in a single transaction.  If you can make it work, this is by far the best option.

To minimize the transaction runtime, get all the data loaded into SQL Server before you start the transaction, using staging tables or temporary tables (or JSON, or XML or Table-Valued Parameters).

And consider turning on the READ COMMITTED SNAPSHOT database option to enable the application to continue to read tables affected by uncommitted transactions.

But this:

  1k independent operation that may require to validate the existing
  state of a record and updating it.

seems like something that can be performed in a second or two, at most.  If running entirely on the server-side.

If you determine that the transaction has too great an impact on the application then you have to pick an option that has a some significant downsides, such as:

1) Perform the changes during application downtime

2) Copy the affected tables and keep the copies in sync with triggers or replication.  Apply the changes to the copies, and then perform an ALTER TABLE .. SWITCH or table renames in a transaction to replace the production tables with the copies.

3) Write "undo" scripts to back-out incomplete changes on failure.


Is there a oracle SQL query show me how many time I had Downtime in my Database through a year? I want to see log for how often my oracle Database goes down ,so is there a SQL query can give me this info..

Example for Oracle 11.2

Prepare

sqlplus / as sysdba
CREATE VIEW X$_DBGALERTEXT AS SELECT * FROM X$DBGALERTEXT;
GRANT SELECT ON X$_DBGALERTEXT TO <USERNAME>;
CREATE SYNONYM <USERNAME>.X$DBGALERTEXT FOR SYS.X$_DBGALERTEXT;

select startup and shutdown time database.

select  to_char(originating_timestamp, 'YYYY-MM-DD HH24:MI' ), message_text
from X$DBGALERTEXT
where  message_text like '%Starting ORACLE instance%' or
       message_text like '%Instance shutdown%'

Example output

2018-07-31 10:45    Starting ORACLE instance (normal)

2018-07-31 11:12    Instance shutdown complete

2018-07-31 11:13    Starting ORACLE instance (normal)

2019-01-25 10:16    Instance shutdown complete

2019-01-25 14:15    Starting ORACLE instance (normal)