Execute a script after transactional replication setup completes We successfully set up transactional replication with 15 articles.
After successful of article changes Done in subscriber, we need to execute ".SQL File"  at subscriber database. Please help on this.

Create the below table in Publisher database:

Create Table DwDimTable (
Ino int Primary Key
,Item Varchar(10)
,Usage numeric(16,2)
,StartDate date
,EndDate date)

Create the below table and run the query in the Subscriber database:

Create table DwFactTable(
Ino int,
Item    varchar(15),
PerDayUsage numeric(16,5),
[Day]   int,
[Month] int,
[Year]  int,
[Date]  date)
Go
SELECT TOP (1000) IDENTITY(INT,0,1) AS N
INTO dbo.Multi
FROM sys.columns c1, sys.columns c2

Insert the data in the Publisher with below query:

insert into DwDimTable
values
    (1,'Gas Bill','9500.00',cast(GETDATE() as date),
    cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as date))

Set up transactional replication for the article ‘DwDimTable’. After successful of transaction replication.

Here, I need assistance to execute/run automatically below .SQL file/Query
automatically at subscriber once publication is done:

insert into DwFactTable
SELECT DISTINCT
Ino
,Item
,sum(Usage)/(DATEDIFF(DD,StartDate,EndDate)+1) [PerDayUsage]
,DD as [Day]
,MM as [Month]
,YY as [Year]
,CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATE) AS [Date]
FROM   DwDimTable
CROSS APPLY (SELECT DAY(DAYDT) AS DD, MONTH(DAYDT) AS MM, YEAR(DAYDT) AS YY
             FROM (SELECT TOP (DATEDIFF(DAY,StartDate,EndDate) + 1)
                          DATEADD(DAY, N, StartDate) DAYDT
                   FROM DBO.Multi) AS R
            ) F
GROUP BY Ino,Item,Usage ,DD,MM,YY,StartDate,EndDate,
CAST(CAST(MM AS VARCHAR)+'/'+CAST(DD AS VARCHAR)+'/'+CAST(YY AS VARCHAR) AS DATETIME)

I need assistance to execute/run automatically below .SQL file/Query
  automatically at subscriber once publication is done

You should use sp_addscriptexec tsql to run sql script on all subscribers of a publication once the snapshot is applied to subscribers. Make sure you use @skiperror = 0 to stop the distribution agent when an error is encountered.

Brandon Williams has a good blog post on - Executing scripts with sp_addscriptexec


Date of birth saving to MYSQL DB with timestamp or date column type i am using MYSQL database and would like to know which of this is a good practice.

way 1 - use column type in DB as timestamp. i.e will contain values like 2012-11-13 00:00:00 way 2 - use column as Date. i.e will contain values like 2012-11-13 in the database.

The question here is, to Store DOB. do we really need timestamp as the column type ? also the time section (00:00:00) is not required for a DOB in my opinion.

Please let me know the correct way to store DOB in database.

You should just use Date as datatype to store Date field.

Additionally, Specify Timestamp over Datetime datatype if records are often updated every time the record is changed. If you want to store a specific value you should use a Datetime as datatype.


The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION Below is an excerpt of an 800 line stored procedure that is failing with this error now. I say now because this is not new code. This script is scheduled to run every few minutes and has worked for a year.

ALTER PROCEDURE [dbo].[bsi_transfer_reallocate] @transfer_no int
AS
BEGIN
BEGIN TRY

[a few lines of code]

  BEGIN TRANSACTION REALLOCATE;

(over 700 lines of code)

  COMMIT TRANSACTION REALLOCATE;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION REALLOCATE;
    SET @dbg_msg = 'Transfer ' + CAST(@transfer_no AS varchar(8)) + ' aborted due to error condition with error number ' +
       CAST(ERROR_NUMBER() AS varchar(200)) + ' and message ' + ERROR_MESSAGE()
    INSERT INTO BATTERY_debug VALUES ('REALLOCATION Failure', GETDATE(), @dbg_msg)
    select  @dbg_msg
    RETURN (1)
END CATCH
END
return(0)

Some errors automatically rollback the transaction.  See generally
Error and Transaction Handling in SQL Server

And if your error already rolled back the transaction, the first line of your catch block will throw this error.

So replace

  ROLLBACK TRANSACTION REALLOCATE;

with

 if @@trancount > 0 ROLLBACK TRANSACTION;

(Remove the 'REALLOCATE' label because this will NOT roll back only the nested transaction.  A Nested Transaction can have a name, but cannot be individually rolled back.  For that you would need a savepoint, but that's probably not the issue here).


SQL Server 2008 R2 Restore a back up to a new database I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records.

What is the best way to do this?

Basically I want to get some records from the old backup update old values to the current database without doing a restore, what are my options?

You cannot access the records in a database backup without restoring it to a database. You can however restore it to a different database from the one that it was backed up from.

The following link illustrates how to do this using Transact-SQL: http://msdn.microsoft.com/en-us/library/ms190447(v=sql.105).aspx

and in Management studio: http://msdn.microsoft.com/en-us/library/ms177429(v=sql.105).aspx

Then once you have the new database you can write SQL to join the two instances (obviously security considerations will need to be taken into account first). E.g

SELECT Table1.*, Table1_Backup.* FROM Table1
INNER JOIN DB_Restore.dbo.Table1 AS Table1_Backup ON Table1.ID = Table1_Backup.ID

or similar.

As @Flem mentions you could then use the MERGE statement to merge the two datasets, however you want.


Which technology to synchronise SQL Server and MySQL data? Which technology should be used if I want to move my data from SQL Server to MySQL server when data is entered to SQL Server row by row and getting feedback from MySQL server?

Should it be  1. Linked-Server or 2. SSIS?

Also,

Each time I insert a row in SQL Server it should also insert into MySQL and when it is done successfully I should update a flag column in SQL Server to 1 for acknowledgement of data insertion.
What if I update MySQL table. Would it be reflected in SQL Server also?

Given the above comments, I'd recommend going with an SSIS package.
If you go with the linked server option you are injecting database dependency between the replication and the MSSQL server objects.
Linked servers are also a security liability, anyone with sufficient permissions on the MSSQL database can access the target database on the linked server.
Choosing SSIS gives complete control over the datasources, execution, scheduling and effectively decouples the data transfer from the actual databases.


How to search whole MySQL database for a particular string is it possible to search a whole database tables ( row and column) to find out a particular string.

I am having a Database named A with about 35 tables,i need to search for the string named "hello" and i dont know on which table this string is saved.Is it possible?

Using MySQL

i am a linux admin and i am not familiar with databases,it would be really helpful if u can explain the query also.

$ mysqldump -pPASSWORD database --extended=FALSE | grep pattern | less -S

More: http://winashwin.wordpress.com/2012/08/28/mysql-search/


Select the Scheme and Table based on Select result in Postgresql I have this query on postgree...

SELECT c.schemaname AS MySchema, a.relname AS MyTable, b.attname AS MyColumn
FROM pg_class a
INNER JOIN pg_attribute b ON (a.relfilenode = b.attrelid)
INNER JOIN pg_tables c ON (a.relname = c.tablename)
GROUP BY c.schemaname, a.relname, a.relfilenode, b.attname
HAVING b.attname ILIKE('%PartNameColumn%');

The Result is:

MySchema, MyTable, MyColumn
Schema0   Table0   PartNameColumn0
Schema0   Table0   PartNameColumn1
Schema0   Table1   PartNameColumn0
Schema0   Table1   PartNameColumn1
Schema1   Table0   PartNameColumn0
Schema1   Table0   PartNameColumn1
Schema1   Table1   PartNameColumn0
Schema1   Table1   PartNameColumn1

Now I want to get some similar, but introducing a value:

SELECT * FROM MySchema.MyTable WHERE MySchema.MyTable.MyColumn = 'MyValue';

I don't know how pass the before select and create a new query according results IN ONLY ONE SENTENCE.

Please tell me how to do it in only one Query?

Thank you.

You can't. Columns can tell information that's useful but none of it will get you to a value without sending a query. So you'll have to have two queries,

Getting the column name,
Selecting rows where a specific column name is set to a value.

That second query requires planning. Some queries on column names don't require visiting the heap -- such as an index-only scan. Some are greatly sped up by visiting an index first, so you don't have to visit the entire heap and the values for the column are not stored on the column anyway, they're stored in a heap in PostgreSQL.

This may not be ideal, but it's what you've got to work with. See also

Search all columns as a single text per row
Hide columns that are constant across a small number of rows


Multiple lock owners in SQL Server 2014 I've encountered a deadlock on SQL Server 2014, and created a deadlock report using extended events. Below is an excerpt from the report.

What does it mean for objectlock(objid="554979041") to have multiple owners (namely, process14f3f2108 and processf32b5848 (twice)). I understand how a lock may have multiple waiters, but what does it mean to have multiple owners? I thought that a lock could only be owned by a single process, and that all other processes interested in the lock would have to wait for the lock. What am I missing?

<deadlock>

    ... content deleted ...

  <resource-list>
      <keylock hobtid="72057633537982464" dbid="15" objectname="BasketHeader" indexname="UX_BasketHeader_BasketID_Account" id="lock2d9b59f80" mode="X" associatedObjectId="72057633537982464">
        <owner-list>
          <owner id="process203dad848" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process14f3f2108" mode="RangeS-U" requestType="wait" />
        </waiter-list>
      </keylock>
      <objectlock lockPartition="0" objid="554979041" subresource="FULL" dbid="15" objectname="BasketItem" id="lock1e64cbc80" mode="IX" associatedObjectId="554979041">
        <owner-list>
          <owner id="process14f3f2108" mode="IX" />                     !!!     OWNER 1
        </owner-list>
        <waiter-list>
          <waiter id="processf32b5848" mode="X" requestType="convert" />
        </waiter-list>
      </objectlock>
      <objectlock lockPartition="0" objid="554979041" subresource="FULL" dbid="15" objectname="BasketItem" id="lock1e64cbc80" mode="IX" associatedObjectId="554979041">
        <owner-list>
          <owner id="processf32b5848" mode="IX" />                      !!!     OWNER 2
          <owner id="processf32b5848" mode="X" requestType="convert" /> !!!     OWNER 3
        </owner-list>
        <waiter-list>
          <waiter id="process203dad848" mode="IX" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
</deadlock>

The processes use and request different types of locks on the tables

Exclusive (X)
Shared (S)
Intent exclusive (IX)
Intent shared (IS)
Shared with intent exclusive (SIX)

And the compatability matrix looks like this:

      (X)  (S)  (IX) (IS) (SIX)
(X)    ✗    ✗    ✗    ✗    ✗
(S)    ✗    ✓    ✗    ✓    ✗
(IX)   ✗    ✗    ✓    ✓    ✗
(IS)   ✗    ✓    ✓    ✓    ✓
(SIX)  ✗    ✗    ✗    ✓    ✗

process203dad848 (A) has a X (exclusive) lock on the BasketHeader table
and is requesting an IX (Intent Exclusive) lock on the BasketItem table

process14f3f2108 (B) has an IX (Intent Exclusive) lock on BasketItem and is waiting to get a RangeS-U on BasketHeader.

processf32b5848 (C) has an IX lock on BasketItem and is waiting for it to be turned into a X lock

As you can see on the table above IX locks are compatible so seeing two of those on the BasketItem table is perfectly normal.

The RangeS-U makes this interesting as range locks only happen when you are running transactions if you are using serializable isolation level

Whats happening is that (A) holds an exclusive lock on BasketHeader and is waiting for an IntentExclusive lock on BasketItem. (B) is running in serializable isolation level and and is waiting for getting an exclusive Shared lock on BasketHeader and holds an IntentExclusive lock on BasketItem While (C) is Converting its IX lock to X lock.

(B) who is running in serializable will not be able to continue unless it gets its RangeS-U on BasketHeader and will not yield for (C). (A) cannot continue until it can get its lock on BasketItem and there you have your deadlock


MongoDB update object in Array I have the following document:

{
    "name" : "student_1",
    "courses" : [
        {
            "name" : "Algebra",
            "grades" : [ 98, 96, 92 ]
        },
        {
            "name" : "Computers",
            "grades" : [ 80 ]
        }
    ]
}

How can I update the grades of a specified course?
For example, I want to increment some test in the course Algebra by 10 points.

'some test', so let's say you want to add 10 to 96 :

db.student.update({name:'student_1','courses.name':'Algebra'},{$inc:{"courses.$.grades.1":10}})

In the find part, you point to the courses with name 'Algebra'. The .$ points you to that found element. Of that found element, it will change the second (.1) value in the grades array. Using $, you don't need to know the position of the course in the array.


Postgresql Vacuum and replication I have an architecture of cascading replication with 3 postgresql db

Because the master is becoming too big (~ 170Gig), I wanted to run a "cleaning" job over the week-end that would do multiple DELETE operations on Millions of rows by batch and VACUUM table right after.
Unfortunately my cleaning script could not complete because disk of DB2 got filled (pg_xlogs?)

2019-06-21 17:41:08.770 UTC [1136] FATAL:  could not extend file "base/34163166/44033600.20": No space left on device
2019-06-21 17:41:08.770 UTC [1136] HINT:  Check free disk space.                                                                                2019-06-21 17:41:08.770 UTC [1136] CONTEXT:  xlog redo at 662/6A087C30 for Heap/INSERT+INIT: off 1                                                         2019-06-21 17:41:09.188 UTC [13036] FATAL:  could not write to file "pg_xlog/xlogtemp.13036": No space left on device

My disks size are all the same (db1 is bigger because the server has other files on it):

DB1: /dev/sda1       496G  266G  210G  56% /
DB2: /dev/sda1       496G  177G  299G  38% /
DB3 /dev/sda1       496G  177G  299G  38% /

size of the main table is the same on master and replicas

Size              | 173 GB

Databases are all running on different VMs. The commands I ran are simple, SELECT count(*) FROM TABLE1 WHERE X, then DELETE FROM TABLE1 WHERE X, then VACUUM TABLE1. And this script is iterating by batch of 5 days on a timestamp column.
Hence my VACUUM command is split per table. I've been doing backups before starting the script (with a pg_backup that is then sending the back up files to another backup server)
I am actually not sure why the disk of db2 got filled :/ , would you have any idea ? I need to run my script so I think I might need to stop the replication to run it.
Thank you for your help, cheers

The standby server has to write the WAL that it gets from the primary server, it needs enough disk space for that.

Since DB2 is not only a standby, but also a primary server for DB3 and DB4, it might need to retain WAL for a while (I don't know how you configured that).

Just give all standby servers as much disk space as the primary has, then you should be good. Be generous with disk space!

It could be that extra disk space is used if you use replication slots and a standby cannot keep up, perhaps because of a slow network connection. That could be the cause of your out-of-disk condition.

Remember that DELETE and VACUUM do not free any disk space.


Need help understanding Postgres materialized views under the hood postgres 9.6.20
Need some help getting an accurate mental model of whats happening with postgres materialized views.
Based on this from postgres docs (https://www.postgresql.org/docs/9.6/rules-materializedviews.html) "The information about a materialized view in the PostgreSQL system catalogs is exactly the same as it is for a table or view. So for the parser, a materialized view is a relation, just like a table or a view. When a materialized view is referenced in a query, the data is returned directly from the materialized view, like from a table; the rule is only used for populating the materialized view." (emphasis mine)
My assumptions based on the above (and what else I've been reading):

that making a mat view will then allow me to query against that new table
The query that generates that view is only run on the REFRESH call
That if the result of the query used to generate the mat view returns ~30000 rows, then that is how big the materialized view table will be

Something in my assumptions is wrong, and I'm trying to understand what.
I have a nice big (to me) query - some CTEs, some joins, some aggregating info into jsonb columns as my materialized view.
WITH
durations AS (SELECT DISTINCT ON(song) song, song_source_files.duration
    FROM song_source_files WHERE song_source_files.type = 'Full'
    ORDER BY song, updated_at DESC),
...<truncated more CTEs>
SELECT DISTINCT
    published_song.song,
    songs.bpm,
    ...<truncated>...
    COALESCE(durations.duration, 0) as duration,
    ...<truncated>
FROM
    published_song
    LEFT JOIN songs ON songs.id = published_song.song
    LEFT JOIN durations ON durations.song = published_song.song
    ...<truncated>...
    LEFT JOIN vocals ON vocals.song = published_song.song

CREATE UNIQUE INDEX IF NOT EXISTS "idx_{mv_name}_uuid" ON "{mv_name}" ("uuid");
...<truncated>

When I run the query, it takes about 30 seconds, and returns ~30000 rows.
In the mat view, when I query using a WHERE aimed at an indexed column, it's fast, as expected.
When I do EXPLAIN (ANALYZE, FORMAT JSON) SELECT count(*) FROM published_song_mv; this takes about 2.5 mins, and I see "Plan Rows": 12606570 and "Actual Rows": 29536.
So this is where I'm confused - my expectation is that the mat view only works with the Actual Rows. Not sure why the Plan Rows of 12 million even enters the equation (in the sense of, why it would apply on the final result set of ~30000 rows - i know i have some gnarly joins to generate the view)?
So I'm looking for help to correct the mental model I have in my head of whats happening under the hood.
Using Postgres 9.6 - can this be a factor - should I consider upgrading sooner rather than later?

All your assumptions are correct.
You need to ANALYZE the materialized view to get correct estimates – this should be done automatically by autovacuum, so I am not sure what could be wrong there.
It also looks like your materialized view is terribly bloated. You would need to run VACUUM (FULL) on it to fix that.
Both point in the direction of autovacuum not doing its job properly. You should investigate that.


General question on dbms_stats General question on dbms_stats and auto sample size?

what is the impact of having IOTs with overflows and you switch to auto_sample_size and enable approximate NDV .

How does it impact?

For the most part, the primary impact is that the stats gather faster. For the vast majority of cases it produces equally good stats, but does it way faster. There are a few edge cases where you can end up with inferior stats from an approximated NDV. But for the vast majority of datasets it's a superior method due to greatly reduced computational intensity.

To compensate for the problem of occasionally inferior stats, you can manually increase the sample size of the stats gather job to increase its accuracy.

If you want to know how it works in great detail, here is a white paper by a subject expert:

http://jonathanlewis.files.wordpress.com/2011/12/one-pass-distinct-sampling.pdf


How to compare timestamps with different time zones? For example I have a table with timestamps:

    2018-04-05 06:00:00 +01
    2018-04-05 06:00:00 +00
    2018-04-05 06:00:00 -01
    2018-04-05 04:00:00 -01

And I want to get entries that are ...

BETWEEN 2018-04-05 06:00:00+01 AND 2018-04-05 07:00:00+01

So my output would be:

    2018-04-05 06:00:00 +01
    2018-04-05 04:00:00 -01

Will the operator BETWEEN handle time zone differences automatically and give me the needed output?

This just works:

SELECT *, ts AT TIME ZONE '-01'  -- see below about '-1' vs '+1'
FROM  (
   VALUES
   (1, timestamptz '2018-04-05 06:00:00 +01')
 , (2,             '2018-04-05 06:00:00 +00')
 , (3,             '2018-04-05 06:00:00 -01')
 , (4,             '2018-04-05 04:00:00 -01')
   ) t(id, ts)
WHERE  ts BETWEEN '2018-04-05 06:00:00+01'  -- coerced to timestamptz
              AND '2018-04-05 07:00:00+01'; -- derived from context!

But note several pitfalls and potential misconceptions here!

The SQL construct BETWEEN ... AND (not a function, strictly speaking; more like an operator in practice) is not concerned with timezones. That would be a misconception of how things work.
Be aware of the two distinct data types timestamp (timestamp without time zone) and timestamptz (timestamp with time zone). See:

Ignoring time zones altogether in Rails and PostgreSQL

In particular, the time zone offset in timestamptz literals only serves as input / output modifier to the value and is not stored at all. Only the according UTC time is stored internally. See:

Time zone storage in data type “timestamp with time zone”

Be aware of the odd syntax difference between timestamp literals and time zone specifiers (flipped sign) due to disagreement between POSIX and SQL standards:

Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

The output you show (with different time zone offsets) cannot be achieved  by simply returning timestamptz values:

2018-04-05 06:00:00 +01
2018-04-05 04:00:00 -01

timestamptz values are always displayed according to the timezone setting of the session. To get the strings you display (for varying time zones), you would have to preserve the time zone offset of the input and use it to format the output. (Or store complete input literals as text)
Finally, note that '2018-04-05 04:00:00 -01' also passes the test, since it's exactly the same timestamptz value as '2018-04-05 06:00:00+01', just formatted differently (same point in time, displayed for different time zones).
So three rows pass the test in your example, not just two.

If your head is spinning right now, consider this demo:

WITH tbl AS (
   SELECT *
        , split_part(tstz_string, ' ', 3) AS tz_string
        , tstz_string::timestamptz AS tstz
   FROM  (
      VALUES
      (1, '2018-04-05 06:00:00 +01')
    , (2, '2018-04-05 06:00:00 +00')
    , (3, '2018-04-05 06:00:00 -01')
    , (4, '2018-04-05 04:00:00 -01')
      ) t(id, tstz_string)
   )
SELECT *
     , to_char(tz_string::numeric * -1, 'SG00')                                       AS tz_posix
     ,  tstz AT TIME ZONE (tz_string::numeric * -1)::text AS ts_at_org_tz
     , (tstz AT TIME ZONE (tz_string::numeric * -1)::text)::text || ' ' ||  tz_string AS tstz_org
     , (tstz AT TIME ZONE (tz_string::numeric * -1)::text)::text || ' ' ||  tz_string
      = tstz_string                                                                   AS strings_equal
FROM   tbl;

id | tstz_string             | tz_string | tstz                   | tz_posix | ts_at_org_tz        | tstz_org                | strings_equal
-: | :---------------------- | :-------- | :--------------------- | :------- | :------------------ | :---------------------- | :------------
 1 | 2018-04-05 06:00:00 +01 | +01       | 2018-04-05 06:00:00+01 | -01      | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 +01 | t
 2 | 2018-04-05 06:00:00 +00 | +00       | 2018-04-05 07:00:00+01 | +00      | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 +00 | t
 3 | 2018-04-05 06:00:00 -01 | -01       | 2018-04-05 08:00:00+01 | +01      | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 -01 | t
 4 | 2018-04-05 04:00:00 -01 | -01       | 2018-04-05 06:00:00+01 | +01      | 2018-04-05 04:00:00 | 2018-04-05 04:00:00 -01 | t

db<>fiddle here


Master Slave Replication in Mysql I have generated a dump from Master  and I have to construct slave and give privileges and binary log position and file name of master for it to obsorb from master. Master is a high transaction OLTP application having TPS of 74.21 inserts/s, 113.17 updates/s, 0.00 deletes/s, 77468.29 reads/s. Now when DML operations are about to operate on SLAVE via binlogs would there be any impact on Master's performance. Say for example Load Avg or IO operations on Master that might get affected. As it allows slave to read the binlog info .

Note: Master and Slave have same server configuration.

Mysql Version : 5.0.77

OS : CentOS 5.4

Mem: 16GB RAM (80% allocated to INNODB_BUFFER_POOL_SIZE)

Database Size: 380 GB

This question strikes me when I wanted to scp some of the files to Slave. Whereas it has just 34MBPS avg speed within the network and it took reads IO operations a lot and Load Avg was hitting more than 6, 7 , 11 etc .. Ofcourse its not going to use SSH as it uses mysql socket to connect. Just a measurement to have slave in place by not hindering master's perf.

My questions are:

Could there be any load spiking in cpu processors or IO, that which can slow down Master Perf?
If any medium that I can trfr data that uses less cpu/IO utilization ?

Regards,

Mannoj Kumar

The simple answer is No.

In Mysql replication, Master copies the bin log files to slaves, and after that, it's work is over. Now the Slave will run the bin files and execute them, but there won't be any performance on Master.

There might be scenario where you are using full synchronous replication, in which master will wait for the slave to execute the query, but again it won't impact the performance in terms of memory or CPU, but the master will wait for the query to be executed.

Also, for your second question, Phil already answered it, that ssh sends data through encryption which uses a lot of CPU, hence if you want other ways, use the other methods which are described by Phil.


Files copying to different machine not happening from batch file executed from SQL Jobs I am trying to execute a batch file from SQL Server 2012 JObs with the following command

cmd.exe /c "D:\Batch Files\SQLDBBkupCopy.bat".

The objective of the batch file is to copy SQL DB backup to a different machine in the network. The Job successfully ran, but there was no files copied.

From the Jobs history I could able to see this error message:

  Executed as user: SLC\sqlagentservice. Invalid drive specification  0
  File(s) copied.  Process Exit Code 0.  The step succeeded.

Here is my Batch file script:

***********Start******
@echo off
cls
echo.------------------------>>CopyLog.txt
echo.%Date% %Time%:- INITIATING FOLDERS......>>CopyLog.txt
set folder1="B:\SLCBackup\ForSymantec"
set folder2="\\testsvr\c$\SQLBKUP\SLCDBAREN"
echo.%Date% %Time%:- Initiation Complete.....>>CopyLog.txt
echo.%Date% %Time%:- Copying from %folder1% to %folder2% >>CopyLog.txt
xcopy /E /C /R /I /K /Y %folder1% %folder2%
echo.%Date% %Time%:- Complete...>>CopyLog.txt
*****End*********

However, if I try to copy in the same machine like:

set folder2="c:\test1"

it then works.

For any interaction with the Operation System, insure that the user services of SQL Server has the privileges


How do I add versioning to this CMS I'm designing? As you can see, my CMS consists of several widgets (for example an Articles widget), of which you can create several instances, which I call blocks, and those blocks are linked to pages and the containers on those pages. The actual data for a block (e.g. an article instance) is stored in other tables which are not shown here. Containers are set up in a tree-form and every page links to a parent-container and therefore has that container and all of its children. I made it this way, because I'd like to make it very very modular. A bit like Concrete5.

Now I'd like to add versioning to this, so that people can get an old version of their page back if they messed it up. But I have absolutely no idea how to go about it. I've tried to Google for versioning tutorials, but I couldn't find any. Right now the only way I can imagine to do it, would be to make snapshots of pages with all of their associated containers, blocks and widgets and save that as a serialized php-array in a blob in my database. But that just seems like a horrible way to do it.

The standard way is to use a History Table pattern.

If you have a table like this:

create table foo (
  int foo_id primary key,
  name varchar(x) not null
);

You would have a corresponding table

create table foo_history (
  int foo_id,
  int version,
  name varchar(x),
  inserted_at timestamp not null default current_timestamp,
  operation_code char(1) not null, -- D=deleted, U=updated [, I=inserted]
  by_username varchar(x) not null,

  primary key (foo_id, version)
);

The data in this table would be filled by triggers on foo.

If you use Java, you could use Hibernate Envers to automate this for you.


NoSQL Databases Performance I have been engineering and developing a NoSQL database engine (using C#.Net) for .Net technologies. Similar to the available NoSQL database, I store all documents in JSON format, and I keep all related document records (i.e. Album1, Album2, Album3) in a single file.

I have been testing the solution to see its actual performance in a real world scenario, and using Visual Studio Unit Testing framework, my solution has managed to query and search 2,000,000 documents in approximately 12 seconds.

My first question is, how good is that performance if it is a good result at all?

Secondly, since all records are eventually saved in files, I have implemented a singleton design pattern that buffers up all physical documents in memory in order to prevent the need for concurrent file processing. At 2,000,000 and assuming there are 10 document categories (Album, Genre, Artists,...) this will consume more than 8GB of RAM which is not good. On the other hand, if I disable the buffering and make all queries dependent on searching the physical files, assuming that I get 100 concurrent request and each take 12 seconds to complete, these 100 request may take up to 1,200 seconds to complete which is I think is terrible.

The second question is, how could this be optimized? I mean, a NoSQL database, unlike a SQL database, is meant to preserve massive data, and such massive data cannot be fully buffered in RAM or be searched over and over on disk. In theory, how should this be implemented?

100,000-ish records per second doesn't sound too bad as a headline number. Without context, however, it meaningless. What's the spec on the storage array? Are you getting even close to the manufacturer's IOPS and bandwidth numbers? How does it compare to a disk performance testing tool's reported values? When profiling the application under heavy load, where is the time taken? Is the system IO or CPU bound?

Some top-end servers do have ridiculous amounts of memory. 64TB is the largest I've seen. But you're right, holding absolutely everything in memory won't work in the general case. There is the concept of the working set. These are the rows that are being read and written by current users. The system holds these in memory and synchronizes them to disk from time to time. When users move on to new work the system makes room by purging the least recently used data from RAM.

For finding specific records you will need indexes. These trade additional disk and pre-processing time for reduced lookup time. There are many different indexing algorithms, each with benefits and drawbacks.

For large OLAP work it might be that you simply must read all of the data off disk to perform the query. It may be possible to pre-process to get summary values (cubes and similar).

This is a huge undertaking. Whole careers are spent trying to get this right. That you have a working product at all is impressive.


What Current Continuous SQL Server Backup Solutions Are there? With Mirroring being deprecated in SQL Server 2012 and up along with AlwaysON Availability Groups costing $7k per core, I am wonder what options are there for constant reliable backups with a somewhat reasonable ($2k per server or less) price tag?

I understand that not every solution is going to do everything that Availability Groups.  I am just looking for what solutions there are available that will duplicate changes to another live server as each change happens.  Also, the less intensive the process the better.

Some other sources site Double-Take as a good solution.  That question refers to file system backups rather than SQL but Double-Take does boast that it will work with SQL.  I'm not sure of how reliable that is because Double-Take just sounds like it is file replication and I'm not sure if that is the best choice.

Also, I have looked at solutions like ApexSQL and Toad for SQL and I am not sure which solutions of these would do what I am looking for.

Any solutions with a clear answer as to whether the solution will post SQL changes to a backup server as they occur would be nice.  Any other expertise on the surrounding matter would also be much appreciated.

Well, you can keep using mirroring. "Deprecated" does not mean "doesn't work any more" - it just means that, at some point in the future, it won't be supported when you move to some version > 2012 (probably at least 3 versions later, maybe more given the backlash this announcement has caused).

By then, I am sure they will have a replacement for mirroring for Standard Edition, or will have simply deprecated Standard Edition, too. At that time, not today, you can make a decision to (a) use the replacement (or upgrade to Enterprise), (b) stay on the version you're on, or (c) switch to a different RDBMS.

Deprecation isn't exactly the jump-the-ship right now or sink ultimatum a lot of people infer. For example, they deprecated "statements that don't end with semi-colons" back in 2008. Are you adding semi-colons to every statement you write, today, on current versions of SQL Server? Nope, I really doubt it. Do you need to abandon mirroring this instant, or even in the next three years? Nope, not even close.


Grant schema permissions effective for everything existing and everything that will exist I'm looking to do what this answer explains but in an Oracle environment:

https://dba.stackexchange.com/a/125400

Essentially what I'm looking to do is

"GRANT schema permissions that are effective for everything existing and everything that will exist in that schema."

with emphasis on "everything that will exist."

The programming department where I work is small (two of us), but the higher-ups have decided to turn on DB auditing as an added measure of security. We currently share one schema (let's call it MAIN) that has read only privileges on the production instance of the company's main database. In addition to be read only, it houses all the functions, packages, and procedures we use on a day-to-day basis to do our work.

So now instead of using one schema, we want to each use our own (USER1 and USER2) that looks at MAIN and sees everything it sees in real time. If USER1 goes in and creates a table/function/procedure called EXAMPLE in MAIN, we want USER2 to be able to go in and immediately see EXAMPLE without grants needing to be run. We're looking for this functionality because it's pretty common that months down the road after creating a query or something for someone, they'll come back and ask for that data again. If that query is stored in USER1's schema, and USER2 is the one fielding the request, they'll have to start from scratch to get the results for the person asking.

Is there a way to do this in Oracle?

One way to do this is to create a custom role.  Many places use the DBA role out of the box but this often grants way more permissions than are really needed such as "DROP DATABASE".

Create a role, for example, LIMITED_PRODUCTION_DBA_ROLE, and grant

select, insert, update, delete any table;

This allows "Query tables, views, or materialized views in any schema except SYS". You can grant other privilege as required to the role.  Then grant the role to your users.  For development you will want to have more privileges so in your refresh script you can add a DEVELOPMENT_DBA_ROLE and grant that.


Values of created_tmp_disk_tables increases I noticed that the values of Created_tmp_disk_tables increased.
At a certain time, MYD files are created in /tmp directory and my site run very slowly.
I am forced to restart the mysql service to runing my site.

What is the cause of the creation of disk table in tmp directory and the stop of the mysql?

My server:

Ubuntu 12
RAM : 32 G
Processors Number: 12

my.cnf
key_buffer_size         = 1024M
max_allowed_packet      = 512M
thread_stack            = 256K
thread_cache_size       = 8
read_buffer_size        = 2M
join_buffer_size        = 2M
tmp_table_size          = 256M
max_heap_table_size     = 256M
bulk_insert_buffer_size = 256M
open-files-limit        = 2048
query_cache_type = 0
query_cache_limit       = 0
query_cache_size        = 0
innodb_file_per_table
innodb_table_locks              = true
innodb_lock_wait_timeout        = 60
innodb_thread_concurrency       = 8
innodb_commit_concurrency       = 2
innodb_support_xa               = true
innodb_buffer_pool_size = 16G
innodb_log_file_size    = 64M
innodb_additional_mem_pool_size = 16M
innodb_data_file_path   = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size  = 64M
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
[myisamchk]
key_buffer                = 16M
myisam_sort_buffer_size   = 16M
myisam_max_sort_file_size = 2147483648
myisam_repair_threads     = 1
myisam-recover            = BACKUP

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25461 |
| Created_tmp_tables      | 29134 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29327 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29328 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25566 |
| Created_tmp_tables      | 29329 |
+-------------------------+-------+
2 rows in set (0.00 sec)

mysql> show global status like 'Created_tmp_%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 25570 |
| Created_tmp_tables      | 29357 |
+-------------------------+-------+
2 rows in set (0.00 sec)

Created_tmp_disk_tables is an indication of poorly written queries.  Especially when Created_tmp_disk_tables/Created_tmp_tables is a high percentage.

The settings cannot necessarily prevent it.  You already have tmp_table_size and max_heap_table_size as high as the should be for 32GB of RAM.

The issue may not be a real issue -- If the tmp table is only a few rows long, you probably won't notice an issue.

The real issue is whether queries are running "too slow".  That is best judged by turning on the slowlog.

When a query is too complex, it needs to generate a tmp table.  The first choice is to make a MEMORY.  But that cannot be done if any referenced columns are TEXT or BLOB.  The second choice is MyISAM, which implies "disk tmp table", although it may stay cached in RAM and never actually touch the disk.  Another cause for it is when the MEMORY table exceeds those two max... settings, it will be converted to MyISAM.  This is unlikely in your case because of how big they are.

So, once you have discovered which queries are slow, we can discuss how to add composite indexes and/or reformulate the queries to avoid the need for disk tmp tables.  Or, at least try to speed them up.


MySQL Insert with Select Trying to insert data to a table with values from different tables.

  INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
   select (select class.id as class_ref from class where class.name = 'pe'),
   (select teachers.id as teachers_ref from teachers where teachers.last_name
    = 'lenevo'),
   (select section.id as section_ref from section where section.name = 'grade 4')

It's only inserting class_ref values, what's wrong with my code?

Tried this.

INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
    values( (select class.id as class_ref from class where class.name = 'pe'),
           (select teachers.id as teachers_ref from teachers where
            teachers.last_name    = 'lenevo'),
          (select section.id as section_ref from section
           where section.name = 'grade 4'))

teachers_ref can not be null error.

Please try this:

INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
SELECT a.class_ref, b.teachers_ref, c.section_ref
      FROM
      (select class.id as class_ref from class where class.name = 'pe') as a,
      (select teachers.id as teachers_ref from teachers where teachers.last_name = 'lenevo') as b,
      (select section.id as section_ref from section where section.name = 'grade 4') as c;

I assume it will be quiet slow, but should to the trick.


SQL regex PostgreSQL Let's consider a table like that:

id |            name
 1 | This is a test sentence
 2 | is a sentence TeST
 3 | This a IS test sentence
 4 | iS a tEst sentence tHis
 5 | This a test sentence is

How could I achieve to get all the rows, searching for "is a sentence test"?

I am using:

SELECT *
FROM table
WHERE name ILIKE '%is%' AND name ILIKE '%a%' AND name ILIKE '%sentence%' AND name ILIKE '%test%'

But that doesn't seem to be the best approach.

You have LIKE and ILIKE and ~ and ~*. This covers the SQL-LIKE conditionals and the more beefy and more powerful regex-like features.

SELECT
 s,
 s LIKE '%is a sentence test%' as "like",
 s ILIKE '%is a sentence test%' AS "ilike",
 s ~ '.*is a sentence test.*' AS "~",
 s ~* '.*is a sentence test.*' AS "~*"
FROM ( VALUES
 ( 'This is a test sentence' ),
 ( 'is a sentence TeST' ),
 ( 'This a IS test sentence' ),
 ( 'iS a tEst sentence tHis' ),
 ( 'This a test sentence is' )
) AS t(s);

            s            | like | ilike | ~ | ~*
-------------------------+------+-------+---+----
 This is a test sentence | f    | f     | f | f
 is a sentence TeST      | f    | t     | f | t
 This a IS test sentence | f    | f     | f | f
 iS a tEst sentence tHis | f    | f     | f | f
 This a test sentence is | f    | f     | f | f
(5 rows)

You're not using any advanced regex powers, so I'd go with LIKE, or ILIKE.


How to determine which `Foos` have no associated `Bars` I want to find all Foos that do not have any associated Bars.

Here's the query I'm using.  It's returning no records:

select * from foos f where f.id not in(select b.foo_id from bars b);

However, I know that the foo with id = 1583 has no associated Bars, so there should be at least one result in my previous query.

Can somebody point out a mistake I am making?  Thanks.

There are probably bars that have NULL in foo_id.

select 1 where 55 not in (select 1 union all select null)

The above query returns 0 rows.

The solution is to change the query to use NOT EXISTS instead of NOT IN as follows:

select * from foos f where not exists(select * from bars b where b.foo_id = f.id)

Not only is this safer as you don't have to manually exclude NULLs from the subquery, but also this construct will generally provide better performance. So for the most part, I recommend getting into a habit of doing it this way instead of using NOT IN.


SQL Server Failover cluster installation fails `RequireKerberos` to value '1' Hi I am trying to create a SQL Server Failover cluster. Windows Cluster is configured and working as expected but now when I try to install SQL Server it throws the following error:

  OS:   Window Server 2008 R2 DataCentre x64 Bit
  SQL:  SQL Server 2008
  Enterprise Edition x64 Bit

I have seen some posts online an suggests that I need SQL Server 2008 with SP1. I can install SQL Server 2008 and then apply the SP1 but it would not let me even install it and throws the error.

Any suggestions or pointers in the right direction are much appreciated. thank you.

Finally found the a solution online which lead me to the actual solution of the problem.

Apparently it is an issue with SQL Server 2008 and installing the Service Pack is the answer to this. How can we install a service pack while installing the application:

See this article Slipstream Service Packs and Cumulative Updates on a SQL Server 2008 Installation by Edwin Sarmiento.

I tried to install the Service Pack 1 but it failed with a different error messages:

  The installed language for the SQL_Engine_Core_Inst feature is
  inconsistent between the nodes of the cluster. To cluster an instance,
  the language of the instance on each node must be the same.

for me details about the error message read this article FIX: You cannot add a node to a SQL Server 2008 cluster by running the Setup program if there are multiple instances of different languages installed on the same node

I used the method suggested by Edwin Sarmiento but installed SQL Server 2008 Server Pack 4 and wala it worked.

Had to do a lot of reading and tried a lot of different methods to fix it, thought it will be nice to share it with you guys.

cheers thank you.


Alter table add column query blocking a table or taking so much time I'm trying to add a new column to an existing table but it takes so much time or the query is blocking the table, I'm really confused about what's happening exactly.

ALTER TABLE MYTABLE1 ADD MYEXPORT9 NUMBER(38,10) DEFAULT 0;

This simple query doesn't finish yet and using the following query to see if the query is blocked or blocking I found it. Query:

SELECT
decode(L.TYPE,'TM','TABLE','TX','Record(s)') TYPE_LOCK,
decode(L.REQUEST,0,'NO','YES') WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
s.logon_time,
p.spid,
concat(' ',s.PROGRAM) PROGRAM,
O.OWNER OWNER
FROM v$lock l,dba_objects o,v$session s, v$process p
WHERE l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND p.addr=s.paddr
AND l.TYPE IN ('TM','TX');

And looking for in the v$session table I see the following line:

29  ANC oracle  46465270    clay925p6       sqlplus@myserveur (TNS V1-V3) SQL*Plus db file sequential read  file#   5   0000000000000005    block#  1701088 000000000019F4E0    blocks  1   0000000000000001    1740759767  8   User I/O    0   0   WAITING

And it's so annoying to see the word WAITING at the end in the column named STATE.

The other option, add a column operation takes so much time, could be possible according to the number of rows of the tables, there are more than 7 million of entries. But from now the query takes more than 3 hours.

What am I doing wrong? What's happening?

The table will need to be physically rewritten on disc to include the extra column.   For this to happen the table has to be exclusively locked while this is happening.  If the applications running on this database are not shutdown then you may be waiting sometime before this exclusive lock on the table can be given.

Given you mentioned that this table has several million rows the alter table statement is going to take quite some time (this also depends how much data is stored in each record.  You will in all likelyhood need to stop all access to this table during this time (well it will be locked anyway).

Sorry if the answer is a little vague you did not really give many details about the size of the table and how much data is currently stored there.


Percona XtraDB Cluster and MySQL Replication I've setup a 3-node multi master replication with percona xtradb cluster, it works perfectly.

Now I've tried to add some read-only slaves setting up replication as usual but it seems the binlog doesn't include the new inserts,

I've set binlog_do_db on the master of a database, slave says the log position is the same as the master ones but the new data isn't there.

Is there a special way to do replication on xtradb cluster?

it seems the binlog doesn't include the new inserts

I'm not sure whether you're saying the binlog actually doesn't include them, and you have confirmed this with mysqlbinlog, or that it "seems" like it doesn't, because they don't replicate.

PXC needs log_slave_updates turned on at the node serving as master to the asynchronous slave, otherwise, not everything will be written to the master's binary log.  This is very different than an ordinary MySQL server as master, where log_slave_updates will do nothing at all (unless the master is actually a slave to another master).

If that's not it, remove replicate_do_db and binlog_do_db and all of their related options from your configuration and then remove them from your brain.  They should never be added unless you know exactly how they work, in your sleep.  The simplest and by far most reliable replication configuration is, and will always be, replicate everything, which is the default.

Forget about binlog_format on the slave.  It makes absolutely no difference unless the slave, itself, has other, subtended slaves... and if the master is using ROW format, the slave will still log in ROW format if you do indeed have it configured with subtended slaves.  Also, the slave's binlogs (not to be confused with the relay logs) will not log statements received from an upstream master unless log_slave_updates is enabled on the slave.

The same thing goes for innodb_flush_log_at_trx_commit.  It does not impact actual replication.  It's a setting the determines a tradeoff between ACID compliance and performance.


Aws RDS cpu Spike to 100% because of running set isolation command and set My application running with the AWS RDS as a backend.
currently i am doing a performance test.
While doing that i am seeing RDS cpu usage goes 100% after 20min.
While seeing the performance insights of the RDS i am seeing the below metrics,

and which shows setting the transaction isolation level are the culprit.
I have set the auto commit is false in my application.
Please let me know why it causing these much spike. Do i need to configure any other stuffs to reduce this.

Those are probably bogus.

If you have more than a few dozen connections, they will be stumbling over each other and everyone will progress very slowly.  Keep the number of threads to a realistic number.

autocommit=0 should be outlawed.  It begs the programmer to forget to issue COMMIT.  Without COMMIT, a disconnect (or certain other things) will lead to a ROLLBACK.  Meanwhile, the connections may be blocking each other unnecessarily.

To discuss this further, let's see more details about the "performance test".


What are some best practices for using schemas in SQL Server? I understand the capabilities of SQL Server Schemas, but what are the best practices?  Sure they give another layer of security, and provide logical grouping of database objects within the database, but what is typical out there?  I often, in my experience, don't see many custom made schemas utilized.  Is this typical?  Is it the less-frequent scenario where custom schemas should be used?

We use them

to separate permissions per client (eg we have desktop, WebGUI etc schemas)
for logical grouping eg (Data and Staging schemas for our tables).

Useful and practical observations past the white paper mentioned by Marian:

GRANT on the schema: no more permissions per object. So a new proc in the WebGUI schema automatically has the permissions of the schema
Nice groupings in SSMS Object Explorer
OBJECT_SCHEMA_NAME
You are forced to qualify object names (which is best practice)


dynamic data masking read uncommited I have read about Dynamic Data Masking and one question is still in my mind. I am wondering what will happen is such scenario:
1. Dynamic Data Masking is set up on table
2. ETL process is loading new rows using NOLOCK hint (read uncommited)
3. During data load, execute select statement with read uncommited isolation level to get "dirty read" rows.

Does this "dirty data" will masked or not?

Dynamic Data Masking doesn't affect stored pages and it will work the same way with any transaction isolation level.

from Microsoft documentation(https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15):

  Dynamic data masking is easy to use with existing applications, since
  masking rules are applied in the query results.


How to fix a messed up replication on MS SQL Server I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-create it (we have a script to re-create it from scratch). I'm not sure exactly what I did, but now it is in a completely messed up state and I can't fix it.

First, I try to get rid of the subscription (on the publisher server):

EXEC sp_dropsubscription @publication = 'PublicationName', @article = N'all', @subscriber = 'SubscriberServerName'

This seems to work. SELECT * FROM syssubscriptions shows no results. Looking on the subscriber server, SSMS > {SubscriberServer} > Replication > Local Subscriptions - the subscription is not there.

So then I try to delete the publication. SSMS > {Server} > Replication > Local Publications > {PublicationName} > Delete. This gives the following error message:

Could not delete publication 'PublicationName'.
Could not drop article. A subscription exists on it.
Changed database context to 'DatabaseName'. (Microsoft SQL Server, Error: 14046)

Ok, so I try to drop the articles:

EXEC sp_droparticle @publication = 'PublicationName', @article = N'all'

and get this error:

Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.
Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

Ok, so I try starting the Snapshot Agent and I get this internal SQL exception:

The SQL command 'sp_MSactivate_auto_sub' had returned fewer rows than expected by the replication agent.

So I tried an alternative method of deleting the article, DELETE FROM sysarticles. This seems to have worked - I have now got rid of the articles, but I still get the same 'Cannot drop the publication because at least one subscription exists for this publication' error when I try to delete the publication.

I have also restarted SQL Server - didn't help.

I don't know what is going on here and how do I fix it?

BTW this is what happens when you give a software developer who knows just enough to be dangerous the keys to the database. Fortunately, this isn't a production environment...

TLDR:

It appears that disabling and re-enabling replication probably fixed the issue:

exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false'
exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true'

I guess this is the equivalent of switching it off and then back on again...

Longer version:

A workmate had a go at trying to fix it. He tried a few things but didn't get very far. The one change he did make before giving up was to disable the replication.

I then tried Cody's suggestion. The sp_dropsubscription command complained that no subscriptions exists. So I tried the sp_droppublication command. This complained that replication was not enabled on the database. So I enabled it and re-ran the command. This time it complained that the publication did not exist. I refreshed the Local Publications node in SSMS and sure enough it had gone. I ran the replication set up script, generated a new snapshot and every thing is now working properly. Joy!

I'm not 100% certain that disabling and enabling the replication is what actually fixed the problem, but it is definitely worth trying if the replication gets messed up.


stored procedure vs. function in postgresql and which is better for executing dml statements I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure?

My basic steps are:

RENAME TABLE FOOBAR_1234.table1_org TO FOOBAR_1234.table1_old;
DELETE FOOBAR_1234.table1_old WHERE 1=1;
RENAME TABLE FOOBAR_1234.table1 TO FOOBAR_1234.table1_org;

What I also need is the flexibility to parameterize the ID as in 1234 with any ID, example 5678.

If you have an example, that would be helpful too.

There is no such thing as a stored procedure in PostgreSQL. All PostgreSQL has are SQL-callable functions defined in a number of languages - LANGUAGE sql, LANGUAGE plpgsql, LANGUAGE plperl, etc.

Additionally, there's no RENAME TABLE command. You want ALTER TABLE ... RENAME TO ....

This particular example must be written in PL/PgSQL using EXECUTE because it relies on dynamic SQL. See the numerous existing examples on Stack Overflow of using PL/pgSQL EXECUTE. Something like a PL/PgSQL function with:

EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
    'FOOBAR_'||_schema_id, 'table1_org',
    'FOOBAR_'||_schema_id, 'table1_old');

Edit: OK, more complete example:

CREATE OR REPLACE FUNCTION myfunc(_schema_id integer)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
        'FOOBAR_'||_schema_id, 'table1_org',
        'FOOBAR_'||_schema_id, 'table1_old');

    -- Now TRUNCATE the table and do your second `RENAME`
    -- in subsequent EXECUTE statements.
END;
$$;


Postgres Array into Geoserver I'm working with OS Highways data, which when loaded into Postgres using ogr2ogr has a few columns that are set as arrays. Geoserver does not like this, so I need to transform the arrays into a simple list of comma separated values.

For example, currently we have:

{Buses,"Goods Vehicles Exceeding 7.5T"}

What I need instead is:

Buses, Goods Vehicles Exceeding 7.5T

And of course the data type needs to be changed to normal 'character varying' instead of the array type 'character varying[]'.

I could probably split the array into separate fields, then recombine, but I was interested to see if anyone had a more elegant/efficient solution.
Thanks!

You could just alter the table and convert the column in one step:

alter table ogrtable
  alter column arrcolumn
    type varchar
    using (array_to_string(arrcolumn, ', '));


What is the difference in performance between a two single-field indexes and one compound index? Suppose you have Users with an email and name, and you want to search by both email and name.

What is the difference in performance between having two indexes (on email and name) and having one index on both fields?

I read the following but did not find the answer:

Multicolumn Indexes (PostgreSQL documentation)
Multicolumn index and performance (DBA Q & A)
PostgreSQL single vs. multi-column indexing for optimal SELECT performance (Stack Overflow)
Multiple column index vs multiple indexes (Percona)

I am interested in the difference in performance for Postgres and MongoDB.

I'll answer on the MongoDB side (no Postgres knowledge, sorry). The classic answer is that a query only ever uses a single index, so if you have a compound index, good data cardinality and are not using multiple $or clauses, then the compound index will significantly outperform the two single indexes. Some exceptions to this would be those $or clauses (each clause uses a single index and merges the results, so for small data sets this might be a wash) and a data set where a single index fits in memory but the compound index does not.

MongoDB supports intersection as of version 2.6 but the only reliable way to tell if it will select intersection as a viable strategy is to have a sample data set and indexes and try it out. Compound indexes are a far more reliable strategy for now, and would be my recommendation for searching on two fields generally with MongoDB.


Is it ok for MySQL to always use 18-25% of Memory (I have 2 GB Ram)? I have MySQL 8 in my Server with Ram of 2GB. And MySQL is always using 18-25% of Ram even when everything is off. Is it normal? If not then plz let me know how to debug it.

It's normal. Memory usage is determined by various configuration parameters, some of which are shared (e.g. innodb_buffer_pool_size), while others are per thread.


Normalization: Is it considered compliant to split static, numeric values like a year into their own table? I am having an interesting discussion with another database designer about normalization.  In this example, we have a GameTitles table and each record must contain the year in which the game was released.  He says 2NF mandates that everything must be normalized, so, to be compliant, the year field should be split off into a ReleaseYears table with its own primary key that is referenced by the GameTitles table.  I say it should remain as a field on the GameTitles table itself.

My argument for this is that a year is just a non-primitive numeric value that is static by its very nature (ie, 2011 will always be 2011).  Due to this, it serves as its own identifier and needs nothing to reference it since it is what it is.  This also introduces additional maintenance since you now have to add a new year to the table just to reference it.  If you prepopulate the table with a large range of years then you have extra records that potentially won't have references to them at all.  This also increases database size since you now have an extra table, record overhead, and the additional primary key for the year itself.  If you keep the year as a field on the GameTitles table, you eliminate all this additional maintenance and overhead.

Thoughts on this?

edit: Meant to post this on StackOverflow.  Can someone vote to delete this or flag it for attention?

The other database designer is simply wrong, but your reasoning is wrong as well. Assume you start with this table, which has a single candidate key, "game_title".

Table: game_titles

game_title                      year_first_released
--
The first game                  1998
The second game                 1999
Best game: the third one        2001
The fourth game                 2003
Forty-two, the end of games     2011

You evaluate whether it's in 2NF by asking yourself these questions.

Q: First of all, is it in 1NF?

A: Yes, it is.

Q: What are the prime attributes (attributes that are part of a candidate key)?

A: "game_title" is the only prime attribute.

Q: What are the non-prime attributes?

A: "year_first_released" is the only one.

Q: Is "year_first_released" functionally dependent on the whole of "game_title", or on just a part of it?

A: The sole candidate key, "game_title", is a single column; it doesn't even have parts. So "year_first_released" is functionally dependent on the whole of "game_title".

Voilà. You've found 2NF.

You can cut through some of the formal terms by asking first whether it's in 1NF, and then answering this question.

Q: Are there any composite candidate keys?

A: No.

Voilà. You've found 2NF again.

By definition, for a table to violate 2NF, it has to have at least one candidate key that has more than one column.

Here are your reasons for rejecting your friend's opinion.

A year is just a non-primitive numeric value.
A year is static by its very nature.
A year serves as its own identifier.
A table of years introduces additional maintenance.
A table of years might have extra rows that aren't referenced.
A table of years increases database size.

None of these reasons have anything at all to do with whether a table is in 2NF.

In designing a database, it's not wrong to consider maintenance issues, database size, unreferenced rows, range constraints, and so on. It's just wrong to call those things normalization.

Oh, and that two-column table that I provided above--it's in 5NF.


Can MySQL Workbench generate data? Is there a way to generate mass rows of dummy data in a table for testing in workbench?

If not, are there any free tools out there that is able to do that?

You can do this quickly with SQL if the actual data inserted doesn't matter. Create your table and insert one record manually. If the table has a unique/primary key then remove this for the moment.

Then do a insert into table select * from table; This will double the number of records. Do it again to double that (having so 4 records) and so on. Each run will double the (same) records giving you qickly a huge number of them. To make this even faster you can duplicate the query string in the editor. Eg. having 30 insert queries in the editor you can get 2^30 records with a single run of the full editor content.

Once you have enough rows and you want your primary/unique key back run an update over the table, setting the key column to some unique value and then add the primary/unique key back.


sql server query execution plan via jdbc driver is there a way to fetch the XML generated by the "Actual Execution Plan" in SQL server 2012 - 2014, but via JDBC driver for Java -- sqljdbc_6.0.8112.100_enu? (preferable forward compatible with 2016)

in reference to https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan
-->SET STATISTICS XML ON

Running SET STATISTICS XML ON prior to running the query will result in the query being processed, and the XML "actual" execution plan being returned to the client.  This works regardless of the client you are using to connect to SQL Server.  Requesting this via JDBC will result in several resultsets returned to the client, the first result is the query output, the next resultset will be the XML output for the actual query plan.
See the Microsoft Docs for details about the command.
I'm using SQL Server Management Studio (SSMS) below since it provides a nice graphical interface I can use to show the output.
SSMS will show the resulting XML in a resultset that you can simply "click" to see the graphical plan in a new window, for example:
SET STATISTICS IO, TIME, PROFILE, XML ON;
GO

SELECT *
FROM sys.objects

Will show the following results:

In the image above, the last row shows the XML for the actual execution plan.  Clicking it shows this:

You may notice I've also turned on STATISTICS PROFILE which contains a wealth of interesting details as shown in the middle results in the image above.
Specifically for jDBC, the statistics and plan will be returned in additional Result Sets to the client that can be obtained from the Server via Statement.getMoreResults(). See https://docs.microsoft.com/en-us/sql/connect/jdbc/using-multiple-result-sets for details.


Can SQL Server 2000 and SQL Server 2012 CoExist? We have OLTP Databases on SQL Server 2000 on my client's Prod Server. Planning to create Data Warehouse on the same Server but use SQL Server 2012. Can I install without any issues?

As stated on MSDN side-by-side installation of SQL 2012 with earlier version is supported. However take note of the instance name, if your SQL 2000 instance is a default instance then your SQL 2012 install will have to be a named instance.

I would take note also that some issues may occur with SQL 2000 tools as your SQL Server 2012 installation may attempt to upgrade any shared components it finds. Although I don't know what of SQL 2000 would still exist in SQL 2012.

Now, my opinion and not the opinion of StackExchange, or my dog...I would not suggest configuring a side-by-side install with a SQL 2000 instance. Not knowing the setup, I would highly doubt the server configuration of a SQL 2000 instance is very well suited to what Microsoft suggest for a SQL Server 2012 installation. Especially considering the fact that SQL Server 2012 is only supported on Window Server 2008 SP2 and higher. This all depends on how old the server is and such but just my opinion.


How to know exactly when my Database / Log files grew? Is there a way to see when (history / log records) of .mdf / .ldf files grew?

Ultimately, is it possible to setup alerting and know immediately when my database / log files grow?

Here is a script that I use. I don't remember the source, but it is handy, as it will show you the growth events.

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime


Exclude rows when a column has matches How would I exclude duplicate 'Clients' who are enrolled in other 'Programs'? Example- Client A is enrolled in Program 36 and 37. I only need them in the list for one of those programs.

Here is the query I am running-

SELECT DISTINCT
  LastFirstName AS Client,
  DOB,
  LastFirst AS Worker,
  DATEADD(DD,839,DOB),
  DATEADD (DD,1058,dob),
  Name AS Program,
  EndDate

FROM Intakes INNER JOIN
        Client ON Intakes.ClientId = Client.ClientId INNER JOIN
        ProgramLkp ON Intakes.Program = ProgramLkp.Code INNER JOIN
        Employees ON Intakes.CaseMgr = Employees.StaffId INNER JOIN
        CaseWorkerHist ON Intakes.KeyId = CaseWorkerHist.IntakeKey

WHERE (Program = 36 OR
      Program = 37 OR
      Program = 9) AND
      EndDate = '2079-12-31'

Thanks.

You can achive this situation using ROW_NUMBER() function. In the example below it will return rows with minimal Program number.

;WITH cte AS (
    SELECT
      LastFirstName AS Client,
      DOB,
      LastFirst AS Worker,
      DATEADD(DD,839,DOB) AS [Date1],
      DATEADD (DD,1058,dob) AS [Date2],
      Name AS Program,
      EndDate,
      [rn]=ROW_NUMBER() OVER(PARTITION BY Intakes.ClientId ORDER BY Intakes.Program)
    FROM Intakes INNER JOIN
            Client ON Intakes.ClientId = Client.ClientId INNER JOIN
            ProgramLkp ON Intakes.Program = ProgramLkp.Code INNER JOIN
            Employees ON Intakes.CaseMgr = Employees.StaffId INNER JOIN
            CaseWorkerHist ON Intakes.KeyId = CaseWorkerHist.IntakeKey

    WHERE (Program = 36 OR
          Program = 37 OR
          Program = 9) AND
          EndDate = '2079-12-31'
)
SELECT  Client,
        DOB,
        Worker,
        [Date1],
        [Date2],
        Program,
        EndDate
FROM cte
WHERE rn=1

Of course it depends, which verion of SQL Server you are using.


Unknown on duplicate key update error This is my current statement:

INSERT INTO groups (groupName, alias, isDefault, inheritance, permissions)
VALUES ('Admin', 'admin', '0', 'Player', 'test.test') ON DUPLICATE KEY
UPDATE alias = VALUES(alias), isDefault = VALUES(isDefault),
       inheritance = VALUES(inheritance), permissions = VALUES(permissions);

When I execute this, I get a single row that is expected. But when I execute this again, it creates another row. Why does it do that?

Between, I have another column called id, and it is auto-incremented.

EDIT: Results of show create groups:

CREATE TABLE 'groups' (
 'id' int(16) NOT NULL AUTO_INCREMENT,
 'groupName' varchar(64) NOT NULL,
 'alias' varchar(32) NOT NULL DEFAULT '',
 'isDefault' tinyint(1) NOT NULL DEFAULT '0',
 'inheritance' text NOT NULL,
 'permissions' text NOT NULL,
 PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

ON DUPLICATE KEY only works when constraint violation. In this cases no constraint will be violated because the primary key is auto incremented column, so you have to add one unique key constraint in your table.


Update table based on reference values from another table I have two tables structured like so:

Table1 (reference table)
| Name UNIQUE | Value(Double) |

Table2 (data table)
| Name1 | Name2 | Number(Int) |

I want to add 10 to the number in each row of Table2 where the value of Name1 (from Table1) is greater than the value of Name2 (from the Table1). How can this be done in Sqlite3?

You need to use an UPDATE statement to do this. Unfortunately SQLite does not support using a JOIN clause in an UPDATE statement so you will have to emulate the JOIN within the WHERE clause like so:

UPDATE Table2
   SET Number = Number + 10
 WHERE (SELECT Value FROM Table1 WHERE Name = Table2.Name1) >
       (SELECT Value FROM Table1 WHERE Name = Table2.Name2);

Similar questions that might also help you:

How do I make an update while joining tables on sqlite

Update with JOIN in sqlite


How speed up mysql dump without data? There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in parallel and restoring them in parallel too.

Btw, SET FOREIGN_KEY_CHECKS=0 is not a solution, becase standart mysql dump creates dump file with that statement.

File creation is the main part of the sluggishness, especially on Windows.  So, decreasing the number of files is key to speeding up your process.

Each MyISAM table needs 3 files.  Each InnoDB table needs 1 or 2 files.  Switch to InnoDB if not already using it.

Each PARTITION is a "table", so it counts as above.  More than about 50 partitions is inefficient, and this is one of the reasons.

Having more than a thousand tables is usually (not always) a sign of a poorly designed schema.

VIEWs, TRIGGERs, Stored routines, etc each take at least one file.

Parallel loading may or may not help; it depends on whether the OS can create multiple files in parallel.


GHOST_CLEANUP not freeing up space on SQL Server Availability Group Had a problem in my SQL Server Cluster for 2 weeks, I had the GHOST_CLEANUP process running 24/7 never stopping even if the users were doing nothing.

I was checking the "Free Space" parameter in the Database and always was at the same value (Around 1GB).

I did a backup of the database and restored in the same server with the same name to see if running sp_clean_db_free_space would free up the space that GHOST_CLEANUP was not able to. After 2 hours...TADA!...the space was cleaned and I had 80GB free from 100GB Database.

So I decided to run sp_clean_db_free_space stored procedure on the original database to see what happens. After almost 30m it finished not freeing any space. The only different thing was that the original database was synched under an Availability Group. I disconnected the database from the Availability Group and now I can see that the GHOST_CLEANUP task is slowly freeing space.

I will assume that the problem was the Availability Group, but does anyone know WHY!?

I can confirm no blocking on primary by read operations on secondary because the secondary node is not even used for read only operations as the application doesn't allow to configure this. There are no users connected to the secondary node.

Today, after checking I found that each 5 seconds SQL Server jumped from 0-1% CPU to 10-15% CPU then dropped again to 0-1% CPU after 1 second. No I/0 usage, only CPU. Decided to disconnect all my Availability Replicas, after 1 minute, SQL Server dropped to a stable 0-1% CPU, connected the replicas again and remains at 0-1%. I have a problem with this GHOST_CLEANUP and I don't know how to solve it.

I am using Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64).

There is a connect item. It is fixed in SP4.


MSSQL - Primary Key Clustered specifying multiple columns I've inherited a database (MSSQL 2008R2) with a lot of tables that either have NO primary key or primary key's that look like this:

 ALTER TABLE [dbo].[Distribution_Batch] ADD  CONSTRAINT
 [PK_Distribution_Batch_1__23] PRIMARY KEY CLUSTERED
 (
    [BATCH_ID] ASC,
    [DATE_CREATED] ASC,
    [CONTACT_ID] ASC,
    [LAB_CODE] ASC,
    [DISTRIBUTION_TYPE] ASC,
    [CREATOR] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Here is the table itself, these are the columns:

 SELECT TOP (1000) [BATCH_ID]  --- this is a varchar(100) field
       ,[LAB_CODE]
       ,[DISTRIBUTION_TYPE]
       ,[CONTACT_ID]
       ,[CREATOR]
       ,[DESCRIPTION]
       ,[DATE_CREATED]
       ,[DATE_COMPLETED]
       ,[DATE_DEADLINE]
       ,[DATE_CONFIRMED]
       ,[ERROR_CODE]
       ,[CONTENT]
       ,[ADDED_BY]
       ,[SOURCE]
       ,[STATUS]
       ,[DELIVERY_METHOD]
       ,[FILE_SIZE]
       ,[DISTRIBUTION_FORMAT]
       ,[CONTACT_ID_ORIGINAL]
   FROM [dbo].[Distribution_Batch]

All this table does is queue up distribution jobs to be run by our application.  Nothing fancy.

They have indexes on numerous columns:

 [DistributionBatch-ContactId-DistributionType-20161108-121538]
 [idx_date_completed]
 [idx_date_created]
 [idx_DistribBatch_BatchId]
 [idx_Distribution_Contacts]
 [IX_Distribution_Batch]
 [IX_Distribution_Batch_LAB_CODE_DISTRIBUTION_TYPE_STATUS_DATE_COMPLETED]
 [IX_Distribution_Batch_LAB_CODE_STATUS]
 [IX_Distribution_Batch_STATUS]
 [IX_Distribution_Batch_STATUS_DELIVERY_METHOD]
 [PK_Distribution_Batch_1__23]

The system overall is slow and the admins response has always been to add more indexes to each table.   This table spits out data sequentially based on whether it is completed or not.  I can't understand why they would have so many indexes on columns that are never sorted on.  Am I missing something?

My question is 2 parts:

Part 1) Does that primary key make any sense?  Shouldn't the primary key just be an ID (int), starting at 1 with identity spec and auto incrementing?

Part 2) I need confirmation that none of those indexes make sense and are not necessary.   There are a lot more tables that have this same issue.

This database has 190 GB of data and 101 GB of indexes.
All comments and opinions greatly appreciated.

Primay key doesn't take any extra storage, its just the way data is stored in that sort order. Whether Primary key(clustered index) should be id(identity column) or something else depends on the database designer and the requirement of application. I would suggest you to start with non-clustered index and check whether they are utilized or not by your select queries. Indexes are good only for select however bad for all DML operations(mostly) i.e. Insert/Update/Delete.

Non-clustered indexes are the ones which takes separate storage, since more than 50% of your database is stored as index, you may need to check their usage(reads). It is possible that they are simply creating overhead in writing and not very useful for scan/seek operation. Just to add, having 50% or more storage for index is not always bad, it just depends whether you have more read/write operation - Dataware house/reporting/OLTP.

You may download first responder kit provided by Mr. Brent Ozar from here and can run sp_blitzindex for your database and it would check everything regarding your index - whether they are too much, too less or some other issue.

In a normal scenario, there is rule of 5 non-clustered index per table however again depends on so many other factors. Kindly provide more details on the issue you are facing due to these indexes. for your two questions - I have tried to answer them.

Kindly let us know if that helped.


SQL Limit + count group? I'm tracking all the visitors of my site using this table:

Visitors table

In my admin panel, I have an overview of which countries visited my site, where they came from, ..., but because over 20+ different countries have visited my site already, the chart looks like this:

Messed up chart

To solve this, I want to select the top 5 countries, but I have no clue on how to do this.

I've tried using LIMIT & COUNT but no luck.

You can separate the problem into multiple steps.

First lets create an example table:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Visitors(
  id INT,
  -- other columns
  country CHAR(2)
);

INSERT INTO Visitors(id,country)VALUES(1,'DE');
INSERT INTO Visitors(id,country)VALUES(2,'DE');
INSERT INTO Visitors(id,country)VALUES(3,'DE');
INSERT INTO Visitors(id,country)VALUES(4,'BE');
INSERT INTO Visitors(id,country)VALUES(5,'BE');
INSERT INTO Visitors(id,country)VALUES(6,'BE');
INSERT INTO Visitors(id,country)VALUES(7,'BE');
INSERT INTO Visitors(id,country)VALUES(8,'BE');
INSERT INTO Visitors(id,country)VALUES(9,'BE');
INSERT INTO Visitors(id,country)VALUES(10,'US');
INSERT INTO Visitors(id,country)VALUES(11,'US');
INSERT INTO Visitors(id,country)VALUES(12,'US');
INSERT INTO Visitors(id,country)VALUES(13,'US');
INSERT INTO Visitors(id,country)VALUES(14,'HR');
INSERT INTO Visitors(id,country)VALUES(15,'HR');
INSERT INTO Visitors(id,country)VALUES(16,'SE');
INSERT INTO Visitors(id,country)VALUES(17,'SE');
INSERT INTO Visitors(id,country)VALUES(18,'SE');
INSERT INTO Visitors(id,country)VALUES(19,'SE');
INSERT INTO Visitors(id,country)VALUES(20,'RU');

The first step now is to count the impressions per country using the GROUP BY clause:

Query 1:

SELECT country,COUNT(1) cnt
FROM Visitors
GROUP BY country

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      DE |   3 |
|      HR |   2 |
|      RU |   1 |
|      SE |   4 |
|      US |   4 |

From there we can get the top 5 countries using LIMIT:

Query 2:

SELECT *
FROM(
  SELECT country,COUNT(1) cnt
  FROM Visitors
  GROUP BY country
)V
ORDER BY cnt DESC
LIMIT 5

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      SE |   4 |
|      US |   4 |
|      DE |   3 |
|      HR |   2 |

Now we just need to join the result back to the visitors table:

Query 3:

SELECT V.*,c.cnt
FROM Visitors V
JOIN(
  SELECT *
  FROM(
    SELECT country,COUNT(1) cnt
    FROM Visitors
    GROUP BY country
  )V
  ORDER BY cnt DESC
  LIMIT 5
)C
ON V.country = C.country

Results:

| ID | COUNTRY | CNT |
----------------------
|  1 |      DE |   3 |
|  2 |      DE |   3 |
|  3 |      DE |   3 |
|  4 |      BE |   6 |
|  5 |      BE |   6 |
|  6 |      BE |   6 |
|  7 |      BE |   6 |
|  8 |      BE |   6 |
|  9 |      BE |   6 |
| 10 |      US |   4 |
| 11 |      US |   4 |
| 12 |      US |   4 |
| 13 |      US |   4 |
| 14 |      HR |   2 |
| 15 |      HR |   2 |
| 16 |      SE |   4 |
| 17 |      SE |   4 |
| 18 |      SE |   4 |
| 19 |      SE |   4 |


Stored Procedure with Optional Parameters I have a View that grabs a lot of data that I want to be able to Search through.  The View is set up with the following columns:

  Part_Number, Description, Information, Supplier, Manufacturer, Category, Subcategory, Currency, Price, Discount.

I am looking to create a Stored Procedure which lets me search through the View for the data I am specifically looking for.  Something like:

SELECT * FROM PartsData
WHERE Part_Number = '0010.05.20.20'

Now my first thought, was to set up the procedure so that the only parameter it was looking for was a VARCHAR which would essentially contain whatever the entire WHERE clause was.  This sounds like it would work.

However, would I be able to instead set up a Stored Procedure with optional parameters?  I am not sure if this is something you can do in SQL though.

If not, is my initial idea for implementation my best bet?  Because it seems like there should be a better way to go about doing this. :\

Sometimes the WHERE clauses can be rather long, such as:

WHERE Description LIKE '%cap'
AND Supplier LIKE 'A2A Systems'
AND Manufacturer LIKE 'Vario'

I wouldn't create a proc on a view, but instead just use the proc against the underlying query itself.

In your example you use LIKE instead of = which adds a level of complexity to the mixture. Usually this could be handled like:

WHERE
(Description = @desc or @desc is null)
AND (Supplier = @supplier or @supplier is null)
AND (Manufacturer = @manufacturer or @manufacturer is null)

With LIKE you'd have to concatenate the % on the head and tail end (or just one end if that suffices). Brent has a post about performance about LIKE

WHERE
(Description like '%' +  @desc + '%' or @desc is null)
AND (Supplier = '%' + @supplier + '%' or @supplier is null)
AND (Manufacturer = '%' + @manufacturer + '%' or @manufacturer is null)

Lastly, this is commonly referred to a kitchen sink query, or catch all query. Here's Aaron's blog about performance with this type of query.


Very Slow MySQL Queries, even with indexes I have a relatively large 4-deep relational data setup like this:

client_applications: (potentially 1,000's of records)
   - ...
   - account_id
   - deleted_at

client_application_versions: (potentially 10,000's of records)
   - ...
   - client_application_id
   - deleted_at

cloud_logs: (potentially 1,000,000's of records)
   - ...
   - client_application_version_id
   - deleted_at

logs: (potentially 1,000,000,000's of records)
   - ...
   - cloud_log_id
   - time_stamp
   - deleted_at

I am still in development so the structure and setup is not set in stone, but I think it is setup ok. Using Rails 3.2.11 and InnoDB MySQL. The database is filled with a small (compared to the eventual db size) set of data (logs only has 700,000 rows) I have 4 queries, 3 of which are problematic, to retrieve logs.

Grab first page of logs, ordered by timestamp, limited by account_id, client_application_id, client_application_version_id (Over 100 seconds)
Grab first page of logs, ordered by timestamp, limited by account_id, client_application_id (Over 100 seconds)
Grab first page of logs, ordered by timestamp, limited by account_id (Over 100 seconds)
Grab first page of logs, ordered by timestamp (~2 seconds)

Here are the EXPLAIN statements. I have indexes on all applicable fields already. Would it be better to duplicate the various ...id fields on the logs table to prevent joins? Or is there some magic sauce I am missing when making these queries? I have never dealt with this quantity of data before so perhaps my standard way of approaching the setup and queries just doesn't scale? How can I alter my setup or statements to make these queries return in a reasonable time?

UPDATE
I have added a few combined indexes on the Logs table and shaved a tiny bit off of the time. Here are the explains for that. I have concluded that the ORDER method is the source of the delay. Removing the order by timestamp desc causes the query to return in a second or so. So the new question is why, when indexed on timestamp, does it still take over a minute to run this query?

UPDATE 2
Using a subquery to fetch the 100 id's increased performance to 14 seconds, but that is still far too long. The optimizations I have tried so far have all shortened the time a bit, but I feel that they are not getting at the root of the problem. Here is the EXPLAIN for the subquery approach.

Not a DBA or MySQL expert here, but let's try :).
So let's take your second query - a bit smaller than the 1st one - and simplify the table names.

We have something like : (LO = logs, CL = cloud_logs, CAV = client_application_versions, CA = client_applications)

 SELECT LO.* FROM LO
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
 INNER JOIN CA      ON CA.id    = CAV.client_application_id
 WHERE (LO.deleted_at IS NULL)
 AND (CA.account_id = '3')
 AND (CA.id = '5')
 ORDER BY timestamp DESC LIMIT 100 OFFSET 0

And so you say it takes about 100 seconds, correct ?

When you say :

  I have indexes on all applicable fields already.

Yet I believe that's where the flaw is. You don't have that much joins, and you may have 7 billion data or just 700, that should be performing well if indexing is correctly thought, and I think that's probably the order by / limit that is messing with your performance because of poor indexing.

1/ Have you tried :

SELECT LO.* FROM LO WHERE (LO.deleted_at IS NULL)

or

SELECT * FROM CA WHERE (CA.account_id = '3') AND (CA.id = '5')

See how these requests perform in time, if everything ok with these 2 tables ?

2/ Have you indexed timestamp as well ? Indexing the column you are making the "order by" on is crucial as well.
In fact, you should even think about your data and how many values you're gonna have for each of the data you're querying on. This is very well explained right there : http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/  and will certainly help you.

3/ From what I've read on MySQL a few mins ago you could also try a MySQLCheck see if everything ok with your tables if you think your indexing is OK  http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html . I know that in older versions of oracle we had to compute stats after creating indexes, maybe something similar here ?

Hope this helps.

[EDIT : 12/01/13 After comments ]

Ok, glad to see you already divided the time by 4 but indeed 25s is way too long.

1/ Have you tried to play with indexes by creating one that would make sense, like explained by Peter here (http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/) ? Like an index on (CA.account_id, CA.id, timestamp) etc ?

2/ How long does it take when you get rid of the order by / limit like below ?

SELECT LO.* FROM LO
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
 INNER JOIN CA      ON CA.id    = CAV.client_application_id
 WHERE (LO.deleted_at IS NULL)
 AND (CA.account_id = '3')
 AND (CA.id = '5')

To check if this would be the order by/limit that mess up with your performance ?

3/ In case 2 is verified, you could try something like :

SELECT LO.* FROM LO
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
 INNER JOIN CA      ON CA.id    = CAV.client_application_id
 INNER JOIN
 (
    SELECT LO.id FROM LO
    INNER JOIN CL      ON CL.id    = LO.cloud_log_id
    INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
    INNER JOIN CA      ON CA.id    = CAV.client_application_id
    WHERE (LO.deleted_at IS NULL)
    AND (CA.account_id = '3')
    AND (CA.id = '5')
    ORDER BY timestamp DESC LIMIT 0,100
  ) AS PERF  ON PERF.id = LO.id

Where you replace LO.id by the column that makes sense with Logs (I suppose you have some sort of Logs id . This is based on : http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
Note you can change the LIMIT 0,100 and keep the OFFSET keyword instead in case you need it (if PostgreSQL compatibility is required).


When range partitioning, should I drop primary UUID-like key? I'm aware of the limitation "every unique key on the table must use every column in the table's partitioning expression". So given that my primary key is a UUID, it's going to require  a change.

Background: I have a transaction history table that grows at about 1.5G per month (more so than I can keep upgrading server memory). The table has a PRIMARY key (uuid_short) and a datetime column, among others. There are two types of queries hitting the table: those that return a list of transactions in a date-range, and those that refer to a specific transaction through its primary key.

Question: Since vast majority of queries don't care for transactions more than 18 months old I thought datetime could be a candidate for range partitioning. Does this mean I'm better off dropping the PRIMARY key and converting it to a non-unique key? (dups are not a problem, the app servers guarantee there cannot be any).

The system is currently a hybrid of OLTP and OLAP scenarios which is probably why it doesn't scale.

Is partitioning going to make a positive impact on memory footprint?

If not, then I may be better off reorganizing the system by moving historical transactions to a data warehouse like AWS Redshift. It would be quite a refactoring effort, but if that's the preferred solution then I'd like to plan early.

This is MySQL 5.6.

Assuming

Some queries involve a date range, usually "recent" dates,
Other queries are point-queries, which involves a unique key
You can purge data older than 18 months.

Anything else?

UUIDs are terrible for performance when the dataset size becomes too big to cache.

It would take some work, but this is what I recommend:

id INT UNSIGNED NOT NULL AUTO_INCREMENT -- or `BIGINT` if might go past 4 billion
dt DATETIME NOT NULL  -- or TIMESTAMP if you prefer
PRIMARY KEY(dt, id)
INDEX(id)

Characteristics:

The data is in datetime order, thereby making those queries very efficient,
Tacking id onto the PK assures that the it is unique,
id replaces your uuid_short in the application (so, code change),
There is a PK, which InnoDB really likes to have,
You won't need to keep adding RAM -- most of the activity is near the "end" of the table,
INSERTs are very efficient -- both the data (& PK) and the secondary key (id) are being added to at the end.

Now, I will add on the purging.

PARTITION BY RANGE(TO_DAYS(dt)) is the optimal way to do it.  Define the ranges as months.  Each month: DROP (instantly) the oldest month and ADD a new month.  See my blog for more details.

How to transition from uuid_short to id?

Add the AUTO_INCREMENT id and INDEX(id) to the existing table in a single ALTER,
Convert the app's use of uuid_short to id,
Drop the uuid_short column and change the indexes (all in a single ALTER),
Add PARTITIONing.

Steps 1,3,4 will each require copying over the entire table; the sooner you do it, the less downtime you will suffer.  Or consider pt-online-schema-change or gh-ost for doing them "online".

Another tip:  See if you can shrink any of the datatypes of other columns.


what are steps should i do to make my local sql server connects to a remote sql server on my server? I want my sql server to remote connect to the server sql server, so that I can access databases from my local machine.

How to do that in steps, or if there is an article online that will be nice as well.

What I am doing now is trying to connect to the remote db from my local sql server using the server IP, but it tell me "Cannot connect to ..."

Are you talking about just accessing the databases and the instance? Or do you mean implementing high availability?

If its the former, then all you technically need to do is ensure tcp/ip is an enabled protocol, install SSMS, and if you can see the server then you should be able to login to the remote instance, provided you have the proper credentials.

Edit: if its not the default instance then you need to ensure the SQL Browser service is started.


MySQL trigger: if record exists just update There is a MySQL database with the following table reviews(reviewer, product, score, title, comment, creation_date, last_modified).
It is needed a trigger before insertion to check if the user has already reviewed the same product. If so, just update the fields score, title, comment and last_modified. This mean no insertion.
Otherwise no operation is needed from the trigger and the review is inserted as usually.

N.B. primary key is bold, foreign key is italic

I tried something myself but I am confident enough to say it does not work:

DELIMITER /
CREATE TRIGGER update_review BEFORE INSERT ON reviews
FOR EACH ROW
BEGIN
IF ((SELECT reviews.reviewer, reviews.product FROM reviews WHERE reviews.reviewer=new.reviewer AND reviews.product=new.product)) THEN
SET old.score=new.score AND old.title=new.title AND old.comment=new.comment AND old.last_modified=new.last_modified;
END IF;
END /
DELIMITER ;

check if the user has already reviewed the same product. If so, just update the fields score, title, comment and last_modified.

No trigger! Use INSERT .. ON DUPLICATE KEY UPDATE.

INSERT INTO reviews (reviewer,
                     product,
                     score,
                     title,
                     comment,
                     creation_date,
                     last_modified)
VALUES (@reviewer,
        @product,
        @score,
        @title,
        @comment,
        @creation_date,
        @last_modified)
ON DUPLICATE KEY UPDATE score = VALUES(score),
                        title = VALUES(title),
                        comment = VALUES(comment)
                        last_modified = VALUES(last_modified);

Additionally - define creation_date field as DEFAULT CURRENT_TIMESTAMP and last_modified field as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, then remove them from all INSERT/UPDATE queries - they will be updated automatically.