Why would a long running query remain sleeping and with null wait_info The following query was run on SQL Server 2008 R2 and took over 1 hour to run:

select log_id, type, instance_id from ts_cache_log where log_id > 146400 order by log_id

It produced 0 rows returned. I know this because I restored the database to the point in time that the query started running and re-ran the query. When I did this, the query ran very fast (less than 1 second).

While this query was running for over an hour, sp_WhoIsActive was kicked off every 5 minutes via a SQL Agent job and logged the details to a table. The below image, which you should able to download and view zoomed in, shows what was running during that time.

The long running query is highlighted in green. The wait_info column is null and the sleep_status is always sleeping.

Any idea why this query took so long to run and/or any thoughts on what I can do to troubleshoot it further?

I'll agree with @Ian for the most part.  Let me expand, though.

It is probably IIS keeping the connection open, but it is by design due to connection pooling, which is almost always used by default these days.

The other point is that the query was not running for over an hour.  Pay very close attention to the awesome documentation that Adam includes in sp_WhoIsActive. Run it with @help =1. On the time attribute you are using to measure run time, Adam writes this:

  For an active request, time the query has been running

  For a sleeping session, time since the last batch completed

And for the sql_text attribute, Adam writes:

  Shows the SQL text for active requests or the last statement
  executed for sleeping sessions, if available in either case.

What's actually happening here is the query completes very quickly and the connection is released back to the pool (in 'sleeping' state) to be picked up when it is demanded again.  The results in your table reflect that the connection that last ran the query is simply going unused for a long period of time. So, nothing to worry about, everything is functioning as designed.

However, you may want to modify your Agent job to run sp_WhoIsActive with @show_sleeping_spids = 1, which will only show sleeping spids if they have an active transaction, so that you don't see these pooled connections anymore.

UPDATE:

In looking at the screenshot again I see you actually do have an open transaction maintained on that spid.  It doesn't really affect my answer, other than the last paragraph, but you do need to find out why that transaction is being held.  Do you have implicit transactions turned on?  Are you failing to commit an explicit transaction in the same batch?


Difference between ASYNC & SYNC real time apply What is the difference between a data guard (Oracle 11g) operating in max performance ASYNC mode and enabling real time apply and a data guard operating in maximum availability SYNC and enabling real time apply?

What is the overhead on performance in both?

Also, does it make sense in enabling real time apply in max performance mode?

In this Oracle page entitled Database High Availability Best Practices it discusses Data Guard Deployment Options:

Requirement of Zero data loss protection and availability for Oracle Database:

Data Guard Maximum Protection or Maximum Availability (SYNC transport) and Redo Apply (physical standby)

Requirement for Near-zero data loss (single-digit seconds) and availability for Oracle Database:

Data Guard Maximum Performance (ASYNC transport) and Redo Apply
NOTE: Asynchronously committed transactions are not protected by Oracle Data Guard against loss until the redo generated by those transactions has been written to the standby redo log of at least one synchronized standby database.

So, yes Maximum Performance (ASYNC transport) and Redo Apply have real meaning, but part of the meaning is that some data can be lost in case of a failure.

You should also consider the network latency for the ASYNC transport target server, since that will also affect responsiveness.


how to pull data source information on data-driven subscriptions I would like to know more about the data-driven subscriptions on our ReportServer.  I am specifically looking for what shared data sources are being used, but the connection string would also suffice.

I have poked around the Subscriptions and DataSource tables, but nothing seems to give me the information I need.  Any ideas?

I kept poking around and... found it.  For the future reference of others:

select
  Catalog.Name
, Subscriptions.[Description]
from
  Subscriptions
  join DataSource
    on Subscriptions.SubscriptionID = DataSource.SubscriptionID
  join Catalog
    on DataSource.Link = Catalog.ItemID


SSMS Cannot show the editor for this task (Notify Operator Task) I'm getting an error message whenever I double click on a Notify Operator Task in our Maintenance Plans in SSMS, which says "Cannot show the editor for this task."

  Property IsMailHost is not available for Database '[msdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

Database mail is configured and working OK.

I'm using SQL Server 2016 (13.0.4411.0) and SSMS 13.0.16106.4.

Interrogating the IsMailHost property via PowerShell seems to indicate that the IsMailHost property is true.

$db = SqlServer\Get-SqlDatabase -ServerInstance sqlserver1 -Name msdb
Write-Host $db.IsMailHost

The above script returns True.

If I try to open a Notify Operator Task on our old SQL 2008 server, with SSMS 10.0.5538.0, it works fine, but with SSMS 13.0.44110.0, I get the same error message.  I'm beginning to think it's a fault with this build of SSMS rather than a configuration issue with the server.

According to this
SSMS on Microsoft Docs

Build number 14.0.17099.0 will likely fix your issue.

  Fixed an issue where "Maintenance Plans Tasks Toolbox" items may be
  missing in SSMS.


Logminer archived redo logs in other database Can I move my archived redo logs to other database and apply logminer there to analyze them?

Yes. If you read the documentation it clearly states:

  There are four basic objects in a LogMiner configuration that you
  should be familiar with: the source database, the mining database, the
  LogMiner dictionary, and the redo log files containing the data of
  interest:

  The source database is the database that produces all the redo log
  files that you want LogMiner to analyze.
  The mining database is the database that LogMiner uses when it
  performs the analysis.


Sorting issue in EXEC sp_executesql I have follow code and after I ran them in SQL Server Management Studio, I expect to get same results because those 3 parts created exact same SQL Statement. Part 3 SQL statement is created by part 1.
Part 1: run SQL statement using EXEC sp_executesql passing parameters.
Part 2 and 3 just run Select statement
I got same records, however results show different sorting sequence for column FSONUM, anyone knows why?
Any suggestions/helps would be greatly appreciated.
Thanks

Code:

declare @startDate datetime = cast('2019-07-17' as datetime),

    @endDate datetime =cast('2019-07-17' as datetime),

    @sortQualifier varchar(20)='date',

    @sortOrder varchar(20) ='D',

    @sort varchar(100),

    @SQLString nvarchar(max) ,

    @ParamDefinition nvarchar(500) = N'@startDate datetime, @endDate datetime'

IF @sortOrder <> '' SET @sort = ' DESC'

IF @sortQualifier = '' SET @sort = ''

SET @SQLString = N'SELECT manifestDate, FSONUM, FPIECES,FWEIGHT,FREFERENCE,FINSTRUCT FROM psorderh WHERE manifestDate >= @startDate AND manifestDate <= @endDate ' +

        CASE @sortQualifier

            WHEN 'date' THEN N'ORDER BY manifestDate'

            WHEN 'tracking' THEN N'ORDER BY FSONUM'

            WHEN 'service' THEN N'ORDER BY FSERVICE'

            WHEN 'pieces' THEN N'ORDER BY FPIECES'

            WHEN 'weight' THEN N'ORDER BY FWEIGHT'

            WHEN 'consignee' THEN N'ORDER BY FSCOMPANY'

            WHEN 'city' THEN N'ORDER BY FSCITY'

            WHEN 'state' THEN N'ORDER BY FSPROV'

            WHEN 'zip' THEN N'ORDER BY FSPOSTAL'

            WHEN 'customer' THEN N'ORDER BY FREFERENCE'

            WHEN 'part' THEN N'ORDER BY FINSTRUCT'

        END + @sort

EXEC sp_executesql @SQLString, @ParamDefinition, @startDate, @endDate

SELECT manifestDate, FSONUM, FPIECES, FWEIGHT,FREFERENCE,FINSTRUCT
FROM psorderh WHERE manifestDate >= @StartDate AND manifestDate <= @EndDate ORDER BY

    CASE @sortQualifier

        WHEN 'date' THEN manifestDate

        WHEN 'tracking' THEN FSONUM

        WHEN 'service' THEN FSERVICE

        WHEN 'pieces' THEN FPIECES

        WHEN 'weight' THEN FWEIGHT

        WHEN 'consignee' THEN FSCOMPANY

        WHEN 'city' THEN FSCITY

        WHEN 'state' THEN FSPROV

        WHEN 'zip' THEN FSPOSTAL

        WHEN 'customer' THEN FREFERENCE

        WHEN 'part' THEN FINSTRUCT

        END  DESC

    END

SELECT manifestDate, FSONUM, FPIECES, FWEIGHT,FREFERENCE,FINSTRUCT FROM psorderh WHERE manifestDate >= @StartDate AND manifestDate <= @EndDate ORDER BY manifestDate DESC

You are only ordering by one column. If there is a tie, you haven't told SQL Server how you want those rows with the same value of that column to be sorted, so they can be returned in any order SQL Server likes (why would it sort by FSONUM and not some other column, when you haven't indicated that that's a column whose order you care about?). And yes this can change between executions.

If you order by manifestDate and expect all of the values for FSONUM to be sorted when the date column has the same value, then just hard-code FSONUM as the second order by:

SET @sort = N', FSONUM';

This is okay to do even when FSONUM is also the first choice.

For a lot more on dynamic sorting, see Conditional Order By.


Understanding JOIN Syntax Given:
postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | text    |           | not null |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (a)

postgres=# \d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | text    |           | not null |
Indexes:
    "bar_pkey" PRIMARY KEY, btree (a)

postgres=# select * from foo;
 a |  b
---+-----
 1 | one
(1 row)

postgres=# select * from bar;
 a |  b
---+-----
 2 | two
(1 row)

I then join'd using the following JOIN syntax:
postgres=# select * from foo, bar;
 a |  b  | a |  b
---+-----+---+-----
 1 | one | 2 | two
(1 row)

Then, I compared it to full outer join:
postgres=# select * from foo full outer join bar using (a);
 a |  b  |  b
---+-----+-----
 1 | one |
 2 |     | two
(2 rows)

and cross join:
postgres=# select * from foo cross join bar;
 a |  b  | a |  b
---+-----+---+-----
 1 | one | 2 | two
(1 row)

Is it always true that the from a, b, c will produce a cross join?

Like @Akina says in his comment, "," and CROSS JOIN is equivalent:
A, B   <=>    A CROSS JOIN B

This is also known as CARTESIAN PRODUCT. The result will have a cardinality (size) of:
| A, B | = | A | * | B |
We can restrict the result so that it becomes a subset of the CROSS JOIN like:
A, B WHERE <some condition>

The corresponding operation becomes
A INNER JOIN B ON <some condition>

INNER can be left out:
A JOIN B ON <some condition>

OUTER JOIN is a bit more complicated to explain in terms of sets, but let's start with a LEFT OUTER JOIN:
A LEFT OUTER JOIN B ON <some condition>

This means:
A JOIN B ON <some condition>
UNION
A WHERE it does NOT EXISTS a corresponding B

I deliberately left out any information regarding columns in the resultset.
A RIGHT OUTER JOIN B ON <some condition>

is equal to
A JOIN B ON <some condition>
UNION
B WHERE it does NOT EXISTS a corresponding A

Finally FULL OUTER JOIN is a UNION between a LEFT and a RIGHT outer JOIN:
A FULL OUTER JOIN B ON <some condition>

is equal to:
A LEFT OUTER JOIN B ON <some condition>
UNION
A RIGHT OUTER JOIN B ON <some condition>

To get it completely right we would need to extend the explanation with the columns involved.
I use to think that the "," notation was short and sweet, but after spending years and years of tuning, modifying and extending existing SQL I now prefer the ANSI JOINs over the "," ones. As shown in the comments by ypercubeᵀᴹ fiddle strange things can happen if you have a "," join and the extend it with an ANSI join. I also find it much easier to convince myself that I have gotten the join conditions correct when they are closely located with the join, instead of the WHERE clause:
SELECT ...
FROM A
JOIN B
    ON ...
   AND ...
JOIN C
    ON ...
   AND ...

vs
SELECT ...
FROM A, B, C
WHERE ...
  AND ...
  AND ...
  AND ...


Aurora / MySQL 5.6 process list thousands of threads in state 'cleaned up' I have a scaling issue with an application, it uses Aurora as the DB and uses Slick as the ORM. I am not sure where the issue lies but I have a question releated to MySQL / Aurora that I hope will shed some light on my issue.

The application needs a significant number of connections in the pool otherwise the application starts failing. However the when I look at INFORMATION_SCHEMA.PROCESSLIST I can see a significant number of threads in the 'cleaned up' state.

I can't see 'cleaned up' on the list of general states - https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

What does 'cleaned up' mean exactly? ( I can guess but I can't find any documentation )

below is a count of the number of threads total vs the number in 'cleaned up'

mysql> select count(*) from INFORMATION_SCHEMA.PROCESSLIST where STATE = 'cleaned up' order by time desc ;
+----------+
| count(*) |
+----------+
|    11982 |
+----------+
1 row in set (0.08 sec)

mysql> select count(*) from INFORMATION_SCHEMA.PROCESSLIST order by time desc ;
+----------+
| count(*) |
+----------+
|    12007 |
+----------+
1 row in set (0.08 sec)

Finally found the answer, its an Aurora specific state that has the same meaning as the blank state in MySQL.

Cleaned Up state means it has completed and cleaned up and waiting for the connection to close.

https://forums.aws.amazon.com/thread.jspa?messageID=708499


Performance of JSONB vs. integer field storage in PostgreSQL I need to store some data with values like 0, 1 (true / false). I am considering grouping the different integer fields into one JSONB field with a structure like this:

{
  "status": 1,
  "approved": 0,
  "notified": 0
}

Is the speed of queries (with WHERE) the same when using JSONB vs. normal integer fields to filter the data?

Is the speed of queries (with WHERE) the same when using JSONB vs. normal integer fields to filter the data?

Of course not. The JSONB adds indirection. You have to resolve the key to the value.

That said, if you create an index on a boolean column, or a jsonb's boolean key it should be the same speed for an index lookup.


Why does Oracle APEX Shared_Servers = 0 Break Apex 3.2 Installed APEX 3.2 and set the shared_servers = 5 and APEX works great, but breaks other processes with our DB. So I set the shared_server = 0 again and now APEX won't show.

I'm using Oracle 11g and APEX 3.2. Does anyone have any ideas why APEX can't work with shared_servers=5?

Any/All help appreciated!

-JFV

Configuring the SHARED_SERVERS Parameter

  The embedded PL/SQL gateway uses the shared server architecture of the
  Oracle Database. To achieve acceptable performance when using the
  embedded PL/SQL gateway, ensure the  SHARED_SERVERS database
  initialization parameter is set to a reasonable value (that is, not 0
  or 1). For a small group of concurrent users, Oracle recommends a
  value of 5 for SHARED_SERVERS.

It is a requirement for EPG (which is suitable only for a development environment). Better use OHS (or a J2EE web server for newer APEX versions).


Does taking transaction log backups every 15 minutes consume more disk space than log backups every 6 hours? In our environment the network storage is low on space. At the same time I would like to make sure that we take transaction log backups every 15 minutes instead of current every 6 hours. My question is will changing the log backup interval from 6 hours to every 15 minutes consume more disk space?

No, but it may make your transaction log smaller - so your SQL Server will use less space.

To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery model.)

If you back up that log file right away, and you don't have open transactions (or any of a host of other things that will cause SQL Server to keep the log file in use), you'll be able to clear out portions of it, keeping your log file small.

However, if you wait 6 hours, that's 360MB of logged activity - your transaction log file (LDF) would be larger.

I'm using small, easy numbers here - you'd want to change them for your own environment to see the impact on your own transaction log space.


Getting max values from MySQL tables I have three tables:

competitions (id, name, date)
athletes (id,name)
results (place, id_athlete, id_competition, ranking_points)

where:

results.id_athlet=athlet.id
results.id_competition=competitions.id

I need a query to select the latest ranking_points of each athlete based on competitions.date.

The answer to this depends on what date range you pick

EXAMPLE : Ranking Points Since The Beginning of the Month for Competition 'Kids Only'

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Kids Only' AND `date` >=
        DATE(NOW()) - INTERVAL (DAY(DATE(NOW()))-1) DAY + INTERVAL 0 SECOND;
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;

EXAMPLE : Teenage Rankings For the Week of 2012-10-08 - 2012-10-15

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Teenage'
        AND `date` >= DATE('2012-10-08') + INTERVAL 0 SECOND
        AND `date` <  DATE('2012-10-15') + INTERVAL 0 SECOND
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;

I hope these give you a basis to start...


Which field type is better to store specific play time in video for postgres? Friend and I are making video service.

In video table, we store opening start point and opening end point of each video. For example,

opening start time: 00:01:35

opening end time: 00:03:04

At first, I made each column with time(6) type. But my friend told me each column should be interval type.

So in following case, which is better?

1) Each column is time(6) type.

2) Each column is interval type.

3) opening start time column is time(6) type, and opening elasped time is interval type

Time is for representing time of day

Interval is for representing durations

Interval is probably the best fit for locating instants inside a video recording,  but an integer or bigint count of frame number is another candidate.


Remotely shutdown a MySQL server I have MySQL 5.0.27 running on a Linux server. It is consuming too many resources (it is running about 80 tasks loading a database). I am unable to log into the server to reduce the number of tasks because SSH, while accepting a password, will time out while requesting a new channel because of the system load.

Can I remotely send a shutdown command to the server? It is possible from another server to either use mysql or mysqladmin with a -h option or use MySQL workbench to shut the server down gracefully?

If I reboot the server I will need to repair two large tables (>200m records) which takes hours. I'd like to avoid this option.

You can send commands via ssh (that is you don't have to actually log in and run the command):

ssh user@host 'mysqladmin shutdown'


Does SQL Server 2012/2014 Standard support Hot Add memory on a Hyper-V guest? In this support article:

https://support.microsoft.com/en-us/kb/956893

The answer to Q7 says:

  Hyper-V Dynamic Memory is fully supported with SQL Server. Only SQL
  Server versions and editions that support Hot Add Memory (Enterprise
  and Datacenter) can see memory that is added by using Hyper-V Dynamic
  Memory. SQL Server 2012 Standard edition also recognizes Hot Add
  memory when running in a virtual environment.

The two sentences seem contradictory. Can anyone confirm if SQL Server Standard supports hot add memory when running on a Hyper-V guest?

The phrasing might be confusing, however it means exactly what it says.

The Hot memory adding is a feature. Not a subject of limitations of the environment.

So what the article actually says is:

You can only use the Hot add Memory feature in SQL Server Enterprise and Datacenter
Standard version of SQL Server 2012 could detect memory being added to the system, however it does not allow for hot add memory to the SQL Server so the feature is turned off

In other words, no it doesn't work.
I guess somewhere in the Microsoft office there is a dev who really really wanted people to know it wasn't his fault that the feature is limited to Enterprise and Datacenter editions. But rather a part of the licensing.


Query tuning to meet timeout settings We have an web app with a 30 second time out on search screens. I've analyzed the original query and added a missing index for one of the query's 9 joins. Other things like expected and actual row-counts are good. No table or index scans. But it still didn't come in under 30 seconds. It was close.

I rewrote the query by decomposing into smaller steps with temp tables and no more than 2 joins per step. It screams in at 5 seconds now. All good.

My specific question is this a normal/common approach to solving this kind of problem? Why do I have to help out the optimizer in this way?

Is this a normal/common approach to solving this kind of problem?

Yes it is a common approach (though not common enough, perhaps).

  Why do I have to help out the optimizer in this way?

Primarily because the optimizer is cost-based, and relies on cardinality estimations (expected number of rows) as a main input to that calculation.

Assuming the right statistics are available, and they are reasonably up-to-date, estimates on base relations may be quite accurate. These derived estimates are the input to the next operation (for example, a join), which produces another cardinality estimation as its output, and so on. The nature of this process (and the statistical inputs) means that the accuracy of the estimations will tend to degrade as derivations continue up the tree. Poor estimations lead to poor cost-based choices, and other side-effects such as an insufficient memory reservation for a hashing or sorting operation.

Materializing intermediate results (using temporary tables, or another method) gives the optimizer a new starting point, perhaps with new statistics automatically built on the materialized data, and maybe even with helpful indexes. These considerations mean that simpler queries (using operations with accurate estimation logic) tend to produce higher-quality execution plans.


Mysql might have too many indexes I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD.

But I am not sure if I have too many indexes.

My application allows users to upload products for sale. So the table that will be queried the most, Select / inserts is my products table.

The Table looks like this, (Removed a few rows in this example).

Product

p_id - (int) Primary key Used to filter products, new/old - asc/desc

state_id - (int) index key & foreign key Links to primary key in state Table in order to get the state name, also used for filtering when searching products

city_id -  (int) index key & foreign key Links to primary key in city Table in order to get the city name, also used for filtering when searching products

cat_id - (int) index key & foreign key Links to primary key in category Table in order to get the category name, also used for filtering when searching products

subcat_id - (int) index key & foreign key Links to primary key in subcategory Table in order to get the subcategory name, also used for filtering when searching products

title - (varchar) FULLTEXT search Used for text search, when searching/filtering products

seller_id - (int) index key & foreign key Links to primary key in users table in order to get the sellers username

product_type - (tinyint) index key Used when searching/filtering products, if a product is an ad or auction

price - (int) index key Used when searching/filtering products

end_time - timestamp

is_active - (tinyint) index key Used when searching/filtering products

So when doing a search for products there will be a total of 4 inner joins , I am not displaying the sellers name in search results. Only inner joining, state / city / category / subcategory.

As for now I have a total of 8 index keys and 5 foreign keys.

My first question: Do I need to put a index key on all columns that has a foreign key?

I am also thinking of adding a few composite index.

composite index 1: (title, state_id, city_id, cat_id, subcat_id, product_type , is_active)

composite index 2: (title, state_id, city_id, product_type, is_active)

As for now it looks like most users only do a text search. So the most common query is a fulltext search where is_active = 1

Rather than try to guess, you should download Percona Tools.

You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it:

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

If you not sure, look at the content of the index removal SQL script first

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
vi -R ${ETL_INDX_SCRIPT}

If the resulting script has no ALTER TABLE commands, you don't need anything done.

If there are ALTER TABLE commands and if you are ready, run the script with this

mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

GIVE IT A TRY !!!


MySQL master master replication, safe to delete rows on both masters? We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So I've written a script that does that. — Is it safe to run this script on both masters? It deletes data only. What I'm wondering is what will the MySQL replication module do, if the row it's about to delete has already been deleted? Will replication break, or is it safe to delete rows on both masters? Does it matter if we use row based or statement based replication?

Example: The log data deletion script deletes row 123 on Master A. At the same time, the script deletes that same row on Master B. Then master B reads its relay log, and sees: "I should delete row 123, because it was deleted on Master A and I'm its slave. Let's delete it... ... ...but it's already gone!" — now what will Master B do? Will it halt replication, as if there was a duplicate key error? Or will it think "Fine, I need do nothing", and everything is okay.

Your question already has the key to your answer. It does depend on row based or statement based replication.
STATEMENT-BASED REPLICATION
If you run DELETE FROM tblname WHERE blahblahblah; and the rows matching blahblahblah do not exist, no big deal. Replication will just carry on. It will  just take you a lot longer to realize your data draft on the Slave (or in your case, either Master) if there were slight differences at all.
ROW-BASED REPLICATION
If you run DELETE FROM tblname WHERE blahblahblah; and the rows matching blahblahblah do not exist, that can break replication because the exact row info is embedded in the relay logs on slave. That row is expected to exist for the delete to happen. There is an error code for it:
sh-4.1# perror 1032
MySQL error code 1032 (ER_KEY_NOT_FOUND): Can't find record in '%-.192s'

You will likely see error log messages like these:

2014-02-27 22:03:00 4070 [ERROR] Slave SQL: Could not execute Update_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000166, end_log_pos 93114177, Error_code: 1032
2014-03-17 10:50:15 11596 [ERROR] Slave SQL: Could not execute Delete_rows event on table mydb.mytable; Can't find record in 'mytable', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000337, end_log_pos 427881, Error_code: 1032

Here are other links that discuss this

Some MySQL slave errors?
MySQL Forums : Replication stops with Error 1032
MySQL Forums : Error 1032 - Can't find record in table
Bug #44873 : Replication failure error code:1032

For more insights, please read Advantages and Disadvantages of Statement-Based and Row-Based Replication from the MySQL Documentation.


Tempdb growing, however it has a lot of free space On a sql server dedicated to a Dynamics CRM database, I've been seeing some odd behavior.

While monitoring growth using the following query  as basis, I've noticed tempdb has grown quite frequently in the last few hours:

DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT databasename,
       e.name   AS eventname,
       cat.name AS [CategoryName],
       starttime,
       e.category_id,
       loginname,
       loginsid,
       spid,
       hostname,
       applicationname,
       servername,
       textdata,
       objectname,
       eventclass,
       eventsubclass
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER  BY starttime DESC

Gives several lines of growth of the data files:

tempdb  Data File Auto Grow Database    2015-03-02 09:50:33.187 2

However, when I look at the space occupied by tempdb I'm not seeing where the space crunch is:

USE tempdb
GO
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

Gives the following output:

DbName  FileName    CurrentSizeMB   FreeSpaceMB
tempdb  tempdev     7500.000000     7492.625000
tempdb  templog     156.132812      93.820312
tempdb  tempdev2    7250.000000     7245.625000
tempdb  tempdev3    7250.000000     7245.312500
tempdb  tempdev4    7366.500000     7360.875000

The person maintaining the CRM has asked for us to shrink the tempdb.
My colleague has, in the past, obliged. However I'm unwilling to do so without an explanation. Especially considering that the shrinking has become an almost weekly occurence.

Can anyone give me an indication as to why the tempdb has so many growth events, and how to handle this properly?
I'm currently considering asking for more storage, and increasing the tempdb drive by 50%.
However this feels like treating the symptom, not the cause.

What is the autogrow model for the TempDB files? Tiny increments will incur many small autogrowths whereas large increments will incur fewer, but more intensive, growth routines.

Presumably TempDB is growing because queries that are running on the server are constructing a lot of temporary tables, table variables, or cursors and so on?

Is it known what queries are running, and do you have historical baseline to compare the autogrowths and sizes to?

If things have suddenly changed, and the organisation has not grown the amount of actual data it uses significantly, I would guess that a developer or reporting user has changed the way in which they run their queries.

I think we need a bit more information from you, regarding the above.


Running Powershell Code embedded in SSIS or TSQL, not in external file Is there a method to embed powershell code in SSIS or T-SQL, without saving the code in an external file *.ps1? We know SSIS/Tsql can run powershell, however the code always seems to be saved somewhere else. We want powershell code embedded in SSIS package or t-sql stored procedure, which then can be executed.

Time issue with Dbas, setting up file share folders, and then deploying these powershell files into our Dev, QA, Prod environments (we have multiple servers), this can alleviate some of the Devops process.

1) The T-sql route may use xp_cmdshell which can be security issue, not sure if this is true. Is there safe way with xp_cmdshell or totally different route in T-SQL area?

2) The article below explains running Powershell in SSIS, however it is calling another file. Can we embed code in SSIS, and run powershell?

https://blogs.msdn.microsoft.com/ssis/2017/01/26/run-powershell-scripts-in-ssis/

At the end, I am trying to run a workflow parallel process.
Powershell Run stored procedures in parallel in database

Workflow TestRunParallelExecute
{
    $ServerName = "localhost"
    $DatabaseName = "testrun"
    $Procedure_Query = "select name from sys.procedures"
    $Procedure_List = (Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure_Query)

    ForEach -Parallel ($Procedure in $Procedure_List.Name)
    {
         Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure
    }
}
TestRunParallelExecute
cls

If you choose to use xp_cmdshell to invoke your workflow, see this answer for guidance (tl;dr: replace all your line feeds with spaces).

If you want to use the SSIS Execute Process Task to invoke powershell.exe without referencing a file, you can use the existing guidance, but instead of putting "-F D:\myScript.ps1" in the Process > Arguments dialogue you can put an ad-hoc script to be run (test with -C 'hello' | Out-File C:\temp\foo.txt) - although I'm sure there's a list of caveats and gotchas for actual implementation of this approach.

You can also create a Powershell SQL Agent job step and execute it either ad-hoc or on a schedule.


SQL Server database : moving from 2008 to 2005 - encrypted procedure issue I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.

Neither do I have older 2005 backup of the database.

I do not have decrypted procedures to restore manually.

Is there any way to just copy/move them over?

I ran into this problem a few years back myself. However because SQL Server doesn't actually "encrypt" the objects that you create (it obfuscates them) you can quite easily reverse the process of obfuscation to get the definition back.I use the following procedure to script out encrypted objects:

CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)
AS
--Jon Gurgul 27/09/2010
--Adapted idea/code from shoeboy/joseph gama
SET NOCOUNT ON
IF EXISTS
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e
on (ec.[endpoint_id]=e.[endpoint_id])
WHERE e.[name]='Dedicated Admin Connection'
AND ec.[session_id] = @@SPID
)
BEGIN

DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)
SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000)

    BEGIN TRANSACTION
        EXECUTE (@b)
        SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
    ROLLBACK TRANSACTION

SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 ))
SET @i=1
WHILE @i<=(DATALENGTH(@a)/2)
BEGIN
SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))
SET @i=@i+1
END

SELECT @d [StoredProcedure]

END
ELSE
BEGIN
    PRINT 'Use a DAC Connection'
END

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The original article where this can be found is http://social.msdn.microsoft.com/Forums/en/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330

I hope this is of help to you.


Alter table and update in sql server transactions I'm trying to do an alter table to add a new column on a table, then I would like to do some update on the existing rows in the table to add a value to the recently new added column and finally do a select * to check that everything is ok and then make a rollback of the entire thing.

Something like

declare @transactionNameA varchar(20);
declare @transactionNameB varchar(20);
select @transactionNameA = 'TransactionA';
select @transactionNameB = 'TransactionB';

begin transaction @transactionNameA
alter table A add Column int;

begin transaction @transactionNameB
update A set Column = 1 where ID=1;
select * from A;

rollback transaction @transactionNameB;
rollback transaction @transactionNameA;

When I write that code I get an error saying the the column doesn't exist

What am I doing wrong?
Thanks for your time and help.

You have to break it up into batches because the [Column] does not exist since the statements are being parsed as one batch so it cannot see the new column, hence you receive the error.

Once you break it up into batches with GO statement the column will be added to the table and then your next batch updating the new column will be successful.

Full example:

DROP TABLE #A;
GO
CREATE TABLE #A (ID int IDENTITY(1,1), column1 varchar(1))
GO
INSERT INTO #A (column1)
VALUES('a'),('b'),('c')
GO

begin transaction
alter table #A add [Column] int;
go

begin transaction
update #A set [Column] = 1 where ID=1;
select * from #A;

rollback transaction;
GO

SELECT *
FROM #A


How does a integer data record store in SQL Server page I have seen the 8KB (8192 = 96 Header + 36 Row Offset + 8060 Free space) page architecture in SQL Server. When it comes to storing a data record in Page i am confused.

In Below table i have create Integer for column ID it should take 4 Bytes but each record size/length is showing in DBCC PAGE Command 11 Bytes.

I have created a simple table as below:

CREATE TABLE PAGETEST
(
    ID int primary key
)
GO
INSERT INTO PAGETEST values (200)

Q 1). Why a simple integer data is taking 4 actual bytes of data + 7 Bytes extra = 11 Bytes.
Q 2). Can anybody explain how does a record store in a page.

Kindly find the images below:

One important thing to keep in mind is that rowstore tables have a minimum row size of 9 bytes. You can see some details about that in the answer and comments here. If you're going to be creating sample data and digging around in pages I recommend creating at least a few columns to make what you're looking at more clear. Otherwise you can run into a case where a table with a single TINYINT column appears to take up as much space as a table with a single SMALLINT, which doesn't make sense at first.

CREATE TABLE dbo.X_TINYINT (ID SMALLINT NOT NULL);
CREATE TABLE dbo.X_SMALLINT (ID SMALLINT NOT NULL);

INSERT INTO dbo.X_TINYINT WITH (TABLOCK)
SELECT TOP (100000) 0
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.X_SMALLINT WITH (TABLOCK)
SELECT * FROM dbo.X_TINYINT;

sp_spaceused 'X_TINYINT'; -- 1096 KB reserved

sp_spaceused 'X_SMALLINT'; -- 1096 KB reserved


jsonb and numeric precision and scale I'm using a jsonb column, where the JSON documents contain large integers (PostgreSQL 9.5). I've noticed that when the value stored has too many significant digits, it gets truncated.

As an example, I insert this into my table:

{"value": 7598786232076607106}

And when I select back the same row, I get:

{"value": 7598786232076607000}

According to the documentation, numeric fields in jsonb are stored using the standard numeric type from postgres, and numeric normally allows:

  up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

Is it possible to actually get this behavior with jsonb, instead of truncation?

The issue wasn't with PostgreSQL itself, but with pgAdmin 4, which apparently is not capable of parsing JSON properly, and truncates the response from PostgreSQL.

I will be using a different tool from now on.


Do the showplan (any or all) events add more overhead than a tuning trace should have How long (order of magnitude) do the various showplan events take to record? Also is the time these events require counted as part of the "duration" column of the corresponding SQL query?

There is no way to tell you any exact order of magnitude - this will depend greatly on how many plans you are collecting, how big they are, etc.

That said, yes, capturing actual showplan information will absolutely have an impact on your tracing. This article demonstrates that and this article shows that it is still a problem with Extended Events as well.


Missing data in SQL Server I need to find the data that is present in Oracle, but missing from SQL Server.

I have tried the below query, but am getting the following error:

  The multi-part identifier "PS.TXN_ID" could not be bound.

SELECT TXN_ID
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ')
WHERE NOT EXISTS (SELECT TXN_ID
                  FROM  PA_TRANSACTIONS PT
                  WHERE PT.TXN_ID = PS.TXN_ID)

Can someone help out please?

You have not aliased the OPENQUERY result set as "PS" in your supplied query. Without the alias "PS", SQL Server does not know to what this refers, so PS.TXN_ID is meaningless.

Try this:

SELECT TXN_ID
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ') ps
WHERE NOT EXISTS (select TXN_ID from PA_TRANSACTIONS PT WHERE PT.TXN_ID =PS.TXN_ID)

Alternatively, try using the EXCEPT operator:

SELECT TXN_ID
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ')
EXCEPT
SELECT TXN_ID FROM PA_TRANSACTIONS

EXCEPT Operator


Combine or Separate Tables I have a single table that holds the following data columns:

id = Auto Increment
provider = Twitter or Facebook (depends what they use to login with)
uid = User ID
name = Their full name (e.g. Chris Burton)
image = Profile Image
link = Social network profile link

In that same table I also have the following columns

comment = Comment text
article_id = Article ID
date = Date of when comment was submitted

My question is, should I keep the user login data separate from my comment data? Or is it more efficient to keep it together?

Use separete tables. If one user can make comments on many articles you will have huge repeat of same information.


Does it make sense to create index with all columns included We have a table with columns

 ID
 OtherTableId
 DataA
 DataB
 DataC
 DataD

Does it make sense to create index on OtherTableID INCLUDE (ID, DataA, DataB, DataC, DataD) when we need all columns, or is it enough to create index just on (OtherTableID)?

I know that index with include will effectively duplicate table size, but will it give performance improvement?

Query is:

select * from Table Where OtherTableID = ?

If

your query uses all those fields,
the sort order needs to be different than that of the clustered index, AND
you can't change the clustered index,

an additional (covering) index with a different column order may work for you. As with anything index-related, more (and wider) indexes means slower write/update performance. This is particularly true if you're effectively doubling the size of the table.

If you just index OtherTableId without including the other columns, you'll get a smaller index but your query may end up either just using the clustered index with expensive Scan or Sort operators, or using Key Lookups from the non-clustered index, which may be great if you're querying just a few rows at a time but disastrous if you're scanning the entire table.

This is general advice. For specifics, edit your question to include query, table definitions and a query plan.


Replication Access denied in Replication I have a database on-prem, and I have an Azure account.

I need to establish transactional replication from the on-prem to Azure.

First of all I created a publication and after that I made a subscription to it.

Then I went to the publication and started the job.

It gives me the following error.

Any solution for that?

I had the same problem and solved with the following steps:

Right click shared folder and click properties
Go to Advanced Sharing > Permissions
In dialog "permissions for "
Add snapshot user (\repl_snapshot in my case)
Grant "Full Control"
All should be ok now, and to verify
Go to "View Snapshot Agent status" and click start, you should now see something like "[100%] A snapshot of 1 article(s) was generated."


PostgreSQL not using index I have a very simple table:

CREATE TABLE content
(
  id serial NOT NULL,
  text text,
  fullfilename text,
  CONSTRAINT "PK_ID" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE content
  OWNER TO postgres;

CREATE INDEX content_idx
  ON content
  USING gin
  (to_tsvector('danish'::regconfig, text));

Where text is a rather long aggregated text to perform full text searching.

When I run the following query it resorts to a seq scan:

EXPLAIN ANALYZE SELECT id
FROM content
WHERE to_tsvector('danish', text) @@ to_tsquery('danish','felter')

"Seq Scan on content  (cost=0.00..164.57 rows=249 width=4) (actual time=41.147..7235.823 rows=289 loops=1)"
"  Filter: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
"  Rows Removed by Filter: 1149"
"Planning time: 0.366 ms"
"Execution time: 7235.914 ms"

That is absolutely not okay. But when disabling seq scans with SET enable_seqscan TO 'off' I get the following result:

"Bitmap Heap Scan on content  (cost=17.94..168.53 rows=249 width=4) (actual time=0.145..0.323 rows=289 loops=1)"
"  Recheck Cond: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
"  Heap Blocks: exact=70"
"  ->  Bitmap Index Scan on content_idx  (cost=0.00..17.87 rows=249 width=0) (actual time=0.121..0.121 rows=289 loops=1)"
"        Index Cond: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
"Planning time: 0.373 ms"
"Execution time: 0.383 ms"

What exactly is going on, and what parameters needs tuning for it to run better. I don't like the idea of removing a tool from the query planners toolbox. As it stands, the setup is running a stock PostgreSQL 9.4.4

The problem here is that it thinks to_tsvector is a very cheap operation to perform, even when performing it on an entire table.  Basically, it think it is the same cost as adding together two integers.  But of course it is not.  In the upcoming release 9.5, the default cost for that function has been increased 100-fold (which is probably still not enough to be accurate...but it should be enough to tip the scales for your case, which is all you need).

You can change the cost of that function yourself:

alter function to_tsvector ( regconfig, text) cost 1000;

(Note that because this is a built-in function, changes you make will not survive a pg_upgrade, or a dump, initdb and restore cycle)

This is much safer than fiddling around with the global cost parameters.

The general (global) cost parameters are described here, and ones for specific functions are described here.  The way the two interact with each other don't seem to be well-described anywhere in the docs.


Filter not working on Showplan XML in SQL Server trace I have setup a SQL Server trace to capture long running queries on my SQL Server 2014 box and added Showplan XML event to capture the execution plan of the queries. The trace was created with duration filter. During the testing, I found that Showplan XML didn't take duration filter into account and was showing execution plan for all queries. I was expecting only the queries with my duration filter and their execution plans in the trace. However, this was not the case. It was showing duration filter queries correctly but the execution plan was getting logged for all the queries.

Does anyone know how to get around this problem in SQL Server trace?

SHOWPLAN XML event does not have a column with cpu duration so you cannot filter by duration. You can only filter by columns mentioned in this article.

https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/showplan-xml-event-class

Few alternatives:

use filter by other allowed columns.
capture everything for as minimum time as practical, load in table and filter. Be careful about load generated by this process, especially on production server.
Trace for long running queries. Use that list to filter SHOWPLAN XML  event.


difference of left join and left outer join In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.

There is no difference, they are absolutely 100% identical in function - the OUTER keyword is optional in the syntax. Personally I prefer LEFT OUTER JOIN as it's a bit more explicit.

You can see more details here:

MSDN : Using Outer Joins

and here:

MSDN : FROM (Transact-SQL)

The relevant bit in the latter document:

LEFT [ OUTER ]

  Specifies that all rows from the left table not meeting the join
  condition are included in the result set, and output columns from the
  other table are set to NULL in addition to all rows returned by the
  inner join

The same description applies whether you use the OUTER keyword or not.


db2 range partitioning attach and detach issue We have a table which is used in many select Queries, as it is be accessed by customers to fetch their information. Also they can change their information too whenever they want.

We had to retrieve records only less than 2 years of age. So whatever is older than 24 months gets deleted.

Also, we have a script which actually runs every month and adds (not ATTACH) a partition to the table.

The problem is, we were testing the add and DETACHING by loading the table with some dummy data and running 'Select * from tab'. The ADD and DETACH scripts seems to fail.
Can you please elaborate the reason for this and also how can I achieve the above, without having to force connections and bringing the table down as its very critical and must be available at all times.

db2 "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"

now when table is not being used it goes on well. But when I load the table and just run db2 "select * from tabname" and then run the same (as above) command to add partitions following error comes. Here is the error:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001

Use this:

db2 +c "lock table tabname in share mode"
db2 +c "alter table tabname add partition <partname> starting 'date' ending 'date' exclusive"
db2 "commit"

Here is the explanation:

+c : disable auto commit, so the command you send will not be committed until an explicit commit command sent or connection closed.

locking table in share mode: means that you want to acquire a shared lock which will prevent other transactions to make changes to the table. So, following command won't be blocked while working.

In this approach you say that: lock the table for me, then issue partition command, while issuing this command other transaction will wait till you finished your job and send commit statement.

I am not sure if it works or not. But it worth a try.


This is more complicated, you need to find consecutive rows with the same reserved value first and assign a common group number to it. There are several ways to achieve this, the following is the easest one to understand by submitting the Derived Tables individually:

SELECT min(dt) AS from_date, max(dt) AS to_date, reserved
FROM
 (
   select dt, reserved,
      -- assign a number to al rows with the same value
      sum(flag) over (order by dt rows unbounded preceding) as grp
   from
     ( SELECT dt, reserved,
          -- assigns 1 whenever reserved changes
          case when lag(reserved) over (order by dt) = reserved
               then 0 -- value of the previous same as current
               else 1 -- different value
          end as flag
       from reservations
     ) as dt
  ) as dt
GROUP BY reserved, grp


High availability on a subset of the data On my site, I have a set of big databases (+6TB total) that is running on a SQL Server 2012 instance. One of these is the main DB and the other ones are archived data that are queried sometimes (unions on the main db stored procedures) but do not have to be 24/7 online. Only the main db is mirroed to another datacenter because of storage constraints.

To mantain all this there is a job that keep moving old data from the main to the archive dbs, but this is slow and ineficient. I plan to improve all that with a sliding window paritioning solution but I couldn't find a way to switch an archived partition or table to another database without data movement on the datafiles.

I can put everything on a single database but I couldn't find an HA solution that I could choose the objects that actually need the high availability.

I'm currently doing some tests with replication but it will be tricky to pull it off and it is not a true HA solution.

Any ideas on how I can parition my data and only provide HA to a subset of the whole data?

Thanks in advance.

Switch the partition to a separate table in the main database, then use bulk copy to move those rows into the secondary database.  ALTER TABLE ... SWITCH can be used to make meta-data only moves between tables, not just partitions within a table.


Replication of database used by two programs I have a peer to peer transactional replication set between two identical databases, each one of the databases is being used by a different program on  two different servers. Since we implemented the replication I keep getting a PK violation on a table both programs update although the insert statement doesn't enter a primary key and lets SQL server take care of it. In this case the table name is Statistics:
Violation of PRIMARY KEY constraint 'PK_Statistics'. Cannot insert duplicate key in object 'dbo.Statistic'.

My insert statement:

INSERT INTO [StackExchange].[dbo].[Statistic]
       ([UserId]
       ,[CreateDate]
       ,[IsError]
       ,[CustomData]
       ,[ModuleId]
       ,[StatisticTypeId])
 VALUES
       (19,GETDATE(),'true','test replication',1,1)

Also, the event viewer in the servers is filled with conflicts data:

This is the frequent error I get on server 2:

  A conflict of type 'Update-Delete' was detected at peer 2 between peer 1 (incoming), transaction id 0x000000000031dea3  and peer (null) (on disk), transaction id (null)

This the the same transaction error I get on server 1:

  A conflict of type 'Delete-Update' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000245950  and peer 1 (on disk), transaction id 0x000000000031dea3

What would you advise me to do in that case?

Thank you!

For the PK violations:

You need to ensure that each node in your Peer-to-Peer topology has an identity range island which will not conflict if inserts originate at that node.  To do this you'll need to run DBCC checkident(talbename, reseed, start of range), ie. DBCC checkident(tablename, reseed, 100000) for Node 1, DBCC checkident(tablename, reseed, 200000) for Node 2, and so on..

For the Update-Delete conflicts:

You need to add a location specific identifier column to your table and extend this column to your PK.  This will reduce the chances of conflicts occurring.  If this is not an option, you will lose data as the change that occurred on the node with the highest Originator ID will win the conflict.  This may or may not be okay for you.


Need to join two similar queries that represent different years Rather new to Access (using 2016) and very sketchy on my SQL skill set.

First query totals sales by ALL THREE (user-supplied) criteria:

Salesman
Customer
Product_Code
[Year]

Second query does same except for [Year-1] where [Year] is a user supplied variable. This shows me current and prior total sales grouped by the three criteria shown above.

First Query will produce:

AKER   Cust1   Prod1    CurSales
AKER   Cust1   Prod2    CurSales
CLUT   Cust1   Prod3    CurSales
DELO   Cust3   Prod1    CurSales

etc.... in any combination of the three fields - all producing a total sales number for that combination for the CURRENT YEAR.

Second Query produces same, except for the PRIOR YEAR and may contain more (or fewer) combinations of the three fields.

I want third query to combine the two and show each combination that has a value of "Total Sales" in EITHER YEAR. I can then do comparisons using calculated fields.

I'm using ACCESS 2016 - primarily using the GUI.

For reporting, I need to do full outer join (I think) of these queries (i.e. show all records in both, joining rows where appropriate). I will then use this combined query to report and show comparative date.

I need help creating this second query. Do I need to create a unique "key" perhaps? Chasing my tail.....

Also, a crosstab can give me the exact results I want but I cannot then create a report because the field names are dynamic and I can't see myself writing VBA code at this juncture. I anyone knows how to accomplish this without VBA, I'm all good .

As you note, you need to do a full outer join between the two queries.

Let's assume your first query (for the user-specified year) is:

SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
  FROM SalesTable
 WHERE <User-specified criteria>
   AND Year = <user-specified year>

Then, the combined query should look like this:

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       FULL OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )

NOTE: COALESCE() may be ISNULL() in Access, or fall under some other name - it returns the first value of the values passed to it that isn't NULL (or NULL, if all values are NULL).

Also: as I'm not particularly familiar with Access itself, this is in SQL Server syntax. I think modern versions of Access should be OK with that, but I'm not sure.

How you would convince a GUI query builder to do this for you, I have no idea - most of them have difficulty with more complex queries. Hopefully, Access will let you create the query in a text editor and paste it in, if nothing else.

Well, since apparently MS Access doesn't have FULL OUTER JOIN, we need to get trick it into giving us we what want.

A FULL OUTER JOIN of A and B on A.col = B.col gives you three things:

The combination of all rows from A and B where there's a matching row in both tables;
All rows from A where there's no matching row in B (with the columns from both A and B, where all B columns are NULL); and
All rows from B where there's no matching row in A (with the columns from both A and B, where all A columns are NULL).

Now, A LEFT OUTER JOIN B ON A.col = B.col gives us the first two of the three items above.

So, we need to a way to get just item 3 above, and then combine that with the results of A LEFT OUTER JOIN B.

See if this (overly complicated) query seems to work:

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       LEFT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )

UNION ALL

SELECT COALESCE(cy.Salesman, py.Salesman) as Salesman
      ,COALESCE(cy.Customer, py.Customer) as Customer
      ,COALESCE(cy.Product_Code, py.Product_Code) as Product_Code
      ,COALESCE(cy.CurSales,0) as Sales
      ,COALESCE(py.PYSales,0) as PY_Sales
  FROM (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as CurSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year>
       ) as cy
       RIGHT OUTER JOIN
       (SELECT Salesman, Customer, Product_Code, SUM(SaleAmt) as PYSales
          FROM SalesTable
         WHERE <User-specified criteria>
           AND Year = <user-specified year> - 1
       ) as py
       ON (    cy.Salesman = py.Salesman
           AND cy.Customer = py.Customer
           AND cy.Product_Code = py.Product_Code
          )
 WHERE (    cy.Salesman IS NULL
        AND cy.Customer IS NULL
        AND cy.Product_Code IS NULL
        AND (   py.Salesman IS NOT NULL
             OR py.Customer IS NOT NULL
             OR py.Product_Code IS NOT NULL
            )
       )

UNION ALL combines the results of two queries (with compatible SELECT lists) into one result set.

The first query, A LEFT OUTER JOIN B, gives us items 1 and 2 from our bullet list above.

If we simply created a query, A RIGHT OUTER JOIN B, we'd get items 1 and 3 from the bullet list.

By adding the WHERE clause, where we only accept rows from the RIGHT JOIN where our join columns from A are NULL, and at least one of our join columns from B is not NULL. The rows from item 1 will all have matching values in A and in B - so, either they will have a non-NULL value in at least one of the join columns, or all the join columns from both A and B will be NULL. So, this should just give us the rows from item 3 in our bullet list.

NOTE: this is all untested - please confirm that it works before using in production code!


How to recover a crashed innodb table...? I Have a Innodb table i got the error as

mysql> check table City;
+-------------------+-------+----------+----------------------------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                                 |
+-------------------+-------+----------+----------------------------------------------------------+
| world_innodb.City | check | Error    | Incorrect information in file: './world_innodb/City.frm' |
| world_innodb.City | check | error    | Corrupt                                                  |
+-------------------+-------+----------+----------------------------------------------------------+
2 rows in set (0.00 sec)

I don't have changed anything in my.cnf.I don't know how it get crashed.
How can i recover it
...

Content of error log are as

120207 11:50:47 InnoDB: highest supported file format is Barracuda.
120207 11:50:47  InnoDB: Waiting for the background threads to start
120207 11:50:48 InnoDB: 1.1.8 started; log sequence number 5713326
120207 11:50:48 [Note] Event Scheduler: Loaded 0 events
120207 11:50:48 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.5.20-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
120207 11:50:57 [ERROR] /usr/local/mysql/bin/mysqld: Incorrect information in file: './world_innodb/City.frm'

EDIT

Someone has opened the City.frm file and modified it's content.So How will I recover it.? I am using innodb_file_per_table.

UPDATE: Adding my.cnf as asked by some peoples.

Innodb Specific variables  in My.Cnf

innodb_file_per_table
innodb_data_home_dir            = /usr/local/mysql/data
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_log_group_home_dir       = /usr/local/mysql/data
innodb_buffer_pool_size         = 1G
innodb_additional_mem_pool_size = 2M
innodb_log_file_size            = 128M
innodb_log_files_in_group       = 2
innodb_log_buffer_size          = 8M
innodb_flush_log_at_trx_commit  = 1
innodb_lock_wait_timeout        = 50
innodb_force_recovery           = 4

I created a table called test2

CREATE TABLE `test2` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then inserted one record

insert into test2 values(1);

I stopped MySQL and then added a bunch of characters to the test2.frm file with vi.

I then started MySQL and saw the following

mysql> show table status \G;
*************************** 1. row ***************************
           Name: test2
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: Incorrect information in file: './test2/test2.frm'

I then created another table with the same structure

CREATE TABLE `test3` (   `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I then stopped mysql and copied test3.frm over test2.frm

cp /var/lib/mysql/testdb/test3.frm /var/lib/mysql/testdb/test2.frm

I restarted MySQL and had access to test2

mysql> show table status \G;
*************************** 1. row ***************************
           Name: test2
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 25675431936
 Auto_increment: NULL
    Create_time: 2012-02-07 16:24:20
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

Here is the record that I inserted

mysql> select * from test2;
+------+
| t    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

If you know the structure of the table City , you could create an identical table and then copy the .frm file over the existing corrupted one


Why does my Query use a Bitmap Heap Scan my simple query is not really fast and I don't know why my query is using Bitmap Heap Scan.
explain analyze verbose
SELECT connections, epoch_time
FROM connections
WHERE host_name = 'xyz.z'
ORDER BY epoch_time;

Output:
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3257.93..3316.47 rows=23415 width=13) (actual time=8.607..9.624 rows=23259 loops=1)
   Output: connections, epoch_time
   Sort Key: connections.epoch_time
   Sort Method: quicksort  Memory: 1859kB
   ->  Bitmap Heap Scan on public.connections  (cost=545.88..1558.57 rows=23415 width=13) (actual time=1.341..5.840 rows=23259 loops=1)
         Output: connections, epoch_time
         Recheck Cond: ((connections.host_name)::text = 'xyz.z'::text)
         Heap Blocks: exact=651
         ->  Bitmap Index Scan on idx_host_name  (cost=0.00..540.03 rows=23415 width=0) (actual time=1.266..1.266 rows=23259 loops=1)
               Index Cond: ((connections.host_name)::text = 'xyz.z'::text)
 Planning Time: 0.171 ms
 Execution Time: 10.405 ms
(12 rows)

The table has 97806 rows and I have created an index on the field epoch_time.  The table has 4 columns (id, host_name, connections, epoch_time)

It is doubtful whether this query needs speeding up, but if anything can improve the performance, it would be
CREATE INDEX ON connections (host_name, epoch_time) INCLUDE (connections);

This assumes that there is actually a column connections; if that is a whole-row reference, you cannot get an index-only scan.
Make sure the table is vacuumed often.
Also, bear in mind that each index slows down data modifications. So use it only if query speed is paramount.


Should I move ORDER BY to app? I noticed this discussion on NOT using ORDER BY in SQL query unless really necessary.

https://www.brentozar.com/archive/2019/10/how-to-think-like-the-sql-server-engine-adding-an-order-by/amp/

The author suggests that looking at "Query Bucks" which I think in my exec plan is "Estimated CPU cost", that the Sort is very expensive.

Here are the individual operations:

and

So, up to now I have been looking at exec plan and trying to make the most expensive "Table Scan 97%" more efficient.

BUT maybe I should move to sort completely out of the query and into my C#?

It's actually a very good question and can go a lot deeper but I'll give you the short version.
It all boils down to your scenario.

If your requirements demand this dataset to be always sorted in the same way, consider creating an index to tune the query.

If your requirements don't demand any kind of sorting, don't bother yourself (and the engine).

If your requirements ask for a dynamic sort, there are lots of techniques you can try, sometimes even a dynamic query can save the day here.

If there's a dynamic sort thing in your app and the dataset is the same, just upload the data and let the app worry about sorting, with the added bonus of not hitting the SQL Server again just to bring the same dataset in a different order.

If you are actually getting paginated data from the DB and just fetching one page at a time from it so you must sort it in the query, there's no magic here.


Splitting SQL Server tempdb to use a second drive when first drive is full My SQL Server instance has a small 32 GB SSD which is mostly unusedw while the remaining drives are SAN storage. I considered moving my tempdb to the SSD but my fear is the odd occasion when the tempdb exceeds the SSD size will cause the query to stop. I've read that multiple files will allow the tempdb to split usage across drives but is there a way to put a preference on the SSD drive until full in which case it will resume on the SAN storage?

there a way to put a preference on the SSD drive until full in which case it will resume on the SAN storage?

You can limit the tempdb autogrowth for the primary datafile (that you have on SSD) to 30GB
and then you can create additional datafiles on the SAN.

The drawback will be that you will see imbalance in the tempdb datafile growth.

SQL Server allocates space in a round-robin fashion.

Important Trace flags when dealing with Tempdb :

-T1117 (helps keep data base files equally sized over their lifetime, by triggering all database files in a filegroup for autogrow at the same time.). Note that you wont need this trace flag as you will be eventually restricting the tempdb to NOT grow on SSD once you reach the threshold.

-T1118 (this trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion.) for tempdb.

Refer to Misconceptions around TF 1118 by Paul Randal and SQL Server 2008 Trace Flag -T 1117


Are three equalities on a join operation legal in relational algebra? Let be the following database:

Film(Title,Director,Actor)
Produce(Producer,Title)

Express in relational algebra the following questions:

  Which actors produce at least a Film they directed?
  Which actors produce every film they directed?

For the first question, it leads me to an issue as far as I have three equality on the join operation and I don't know if it is legal.

If it were I would have tried the following:

ΠActor(Film)⋈(Producer=Actor=Director)ΠProducer(Produce)

But I'm not sure that's a true join operation in relational Algebra.

For the second one I tried something totally different:

ΠActor(Film)÷ΠDirector(Film)÷ΠProducer(Produce)

Responses to comments:

A film may have several actors.
For a film with several actors, there are multiple rows in the relation Film
A Director may, or may not, also be an Actor in the same Film

The notation Producer=Actor=Director is wrong. Besides that, neither ΠActor(Film) nor ΠProducer(Produce) has an attribute Director.
The first projection has only an attribute Actor, the second projection has only an attribute Producer.

In the second answer what is meant if you divide two relations that have the same number of attributes. I think that does not make sense because the result is a relation with no attributes.

You first relation Film(Title,Director,Actor) is very strange.
It defines a relation between three attributes actor, directors and title.
But from my understanding of a film there does not exist such a relation.
There is a relation between actor an title, if an actors plays in a movie with this title.
And there is a relation between a director and a title if a person is a director of this movie.
But I don't understand the tuple (actor, director, title).
Does an actor have a director in a movie and another actor has another director in the same movie?
If that is not the case then you should better use two relations:
A relation Acts(Person, Title)and a relation Directs(Person, Title).

Here are the answers using only natural join, which makes them a little bit clumsy.

The first question

  Which actors produce at least a Film they directed?

has the following answer, using the notation from wikipedia:

   Latex of Formula: $$\pi_{\text{Actor}}(\text{Film}) \bowtie \pi_{\text{Actor}}\left(\rho_{\text{Actor}/\text{Director}}\left(\pi_{\text{Director},\text{Title}}(\text{Film}) \bowtie \rho_{\text{Director}/\text{Producer }}(\text{Produce})\right)\right)$$

Here is an equivalent SQL code (Oracle):

select F2.Actor
from Film F2 join (
  Film F1 join Produce P
    on (F1.Director=P.Producer and F1.Title=P.Title)
  ) on F2.Actor=F1.Director

Example

For the following data the result is Donald Duck

Film

    Title              | Director    | Actor
    -------------------+-------------+-----------
    Mickey Mouse Revue | Donald Duck | Minnie Mouse
    Mickey Mouse Revue | Donald Duck | Mickey Mouse
    Duck Tales         | Walt Disney | Donald Duck

Producer

    Producer    | Title
    ------------+------------------
    Donald Duck | Mickey Mouse Revue
    Walt Disney | Duck Tales

The second question

  Which actors produce every film they directed

uses additionally the set difference operator \:

   Latex of Formula: $$\pi_{\text{Actor}}(\text{Film}) \bowtie \rho_{\text{Actor}/\text{Director}}(\pi_{\text{Director}}((\pi_{\text{Director},\text{Title}}(\text{Film}) \bowtie \rho_{\text{Director}/\text{Producer }}(\text{Produce}))\setminus \pi_{\text{Director}}(\pi_{\text{Director},\text{Title}}(\text{Film}) \setminus  \rho_{\text{Director}/\text{Producer }}(\text{Produce}))))$$

These are all (Director,Title) pairs that were not produced by the director of the title:

and therefore these are all directors that haven't produced at least one of their titles:

Similar, these are all directors that produced at least one of their titles:

The difference are the directors that have produced all of the titles the directed.
Now we have to join it to the set of all actors to filter out the directors that are also actors (but not necessary of  the films they directed).

Here is an equivalent SQL Code (Oracle):

Select F2.Actor
from Film F2 join
(
  (Select F1.Director,F1.Title
  from Film join Produce on (
      F1.Director=P.Producer
      and F.Title=P.Filem))
  minus
  (Select Director, Title
  from Film
  minus
  select Producer,Title
  from Produce)
) F3
on(F2.Actor=F3.Director)


Increased performance from single statement, multiple row inserts? I'm currently obsessing over bulk inserts via prepared statements with libpqxx.

I am far from a database expert and even further when it comes to Postgres, but just thinking superficially, it doesn't seem like it would make a difference.

In my MySQL days, it made a huge difference, but it strikes me that MySQL's clustered index was the bottleneck, that it would be better to prepare it for many rows at once rather than one at a time.

Since Postgres doesn't do that, happily I might add, is it correct that there would be a near negligible performance boost to bulk inserts?  Please explain why or why not.

There is a big difference. Insert as many rows as possible at once. Best in a single INSERT statement, but at least in a single transaction. Inserting individual rows takes much longer. Quoting the chapter Populating a Database:

  If you allow each insertion to be committed separately, PostgreSQL is
  doing a lot of work for each row that is added. An additional benefit
  of doing all insertions in one transaction is that if the insertion of
  one row were to fail then the insertion of all rows inserted up to
  that point would be rolled back, so you won't be stuck with partially
  loaded data.

If you insert a large number of rows, consider COPY, which is much faster, yet. And read that chapter in the manual. It has all you need.


Postgres PLPGSQL 'un-stored' procedures? I'm getting the following error when I run the query below:

ERROR: query has no destination for result data Hint: If you want to
discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL
function inline_code_block line 12 at SQL statement

I don't want to discard the results though! I want to display them. What's happening here; is Postgres refusing to provide results from a block that isn't a UDF or stored procedure? Or some other syntaxy thing?
DO
$$
declare
    tenant text;
    result1 integer;
    result2 integer;
BEGIN

tenant='mycustomer1';
EXECUTE format('SELECT count(*) from ' || tenant || E'.accounts_user;') INTO result1;

tenant='mycustomer2';
EXECUTE format('SELECT count(*) from ' || tenant || E'.accounts_user;') INTO result2;

select result1, result2;

END;
$$ LANGUAGE plpgsql;

Background: I'm trying to create a set of dynamic queries that can get values from multiple PG schemas to return in a single report.
Context: I'm using Metabase to run the query, with a read only connection talking to RDS / Postgres. My plan is to store the queries as Metabase items. I was originally going to try creating a temp table to put my results in, but I'm not allowed to CREATE anything, including UDFs and stored procedures, hence my thinking of this as an un-stored procedure.

I have found this answer within the great dba.stackexchange.com resource that appears to show that I'm not going to be able to get a return value out of a DO block:
Running a CTE query in a loop using PL/pgSQL
Will leave this open for a bit to see if anyone has a cool workaround.......


Make unique key as a Foreign key? Hello i want to make unique key as a foreign key but i got an error  i already checked this question

mY member Table

 CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `facebook` varchar(100) NOT NULL,
  `cnic` varchar(100) NOT NULL,
  `expertise` varchar(100) NOT NULL,
  `com_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB

and my profile image tabl

create table profileimage(
imageid int primary key not null,
username varchar(100) foreign key references member(username)
ON DELETE CASCADE
 ON UPDATE CASCADE,
imagepath varchar(255) not null
);

and error i faced
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foreign key references member(username) ON DELETE CASCADE ON UPDATE CASCADE,' at line 6

Yes, you can have a FOREIGN KEY constraint that references a column with a UNIQUE constraint.

The syntax error you get is because you didn't provide a datatype for the column. It should be the same type as the referenced column: username varchar(50).

Another issue is the syntax for foreign constraints. If you put them "inline", i.e immediately after the column definition, you skip the FOREIGN KEY and have only REFERENCES:

-- Don't do this in MySQL !!!!
CREATE TABLE profileimage
( imageid int  NOT NULL PRIMARY KEY,
  username varchar(50)
     REFERENCES member (username)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  imagepath varchar(255) NOT NULL
) ENGINE=InnoDB ;

or you put them separately, with the FOREIGN KEY keyword:

-- Do this in MySQL
CREATE TABLE profileimage
( imageid int NOT NULL PRIMARY KEY,
  username varchar(50),
  imagepath varchar(255) NOT NULL,
  FOREIGN KEY (username)
     REFERENCES member (username)
     ON DELETE CASCADE
     ON UPDATE CASCADE
) ENGINE=InnoDB ;

Big surprise: Both syntaxes work in MySQL with a catch: the first is parsed for syntax validity (and it passes) but the foreign key is not created! (you get a warning)

The second syntax works fine.


How can I pass on a value in the hierarchy based on the value of the parent using an hierarchial query? I have a table with categories and another table with a discount for categories per user:

create table category
(
  id NUMBER NOT NULL,
  parent_id NUMBER,
  name nvarchar2(255),
  PRIMARY KEY( id)
);

create table category_discount
(
  user_id NUMBER NOT NULL,
  category_id NUMBER NOT NULL,
  discount NUMBER NOT NULL,
  PRIMARY KEY( user_id, category_id)
);

insert into category( id, parent_id, name ) VALUES( 1, null, 'root');
insert into category( id, parent_id, name ) VALUES( 2, 1, 'C1');
insert into category( id, parent_id, name ) VALUES( 11, 7, 'C11');
insert into category( id, parent_id, name ) VALUES( 12, 7, 'C12');
insert into category( id, parent_id, name ) VALUES( 3, 1, 'C3');
insert into category( id, parent_id, name ) VALUES( 4, 1, 'C4');
insert into category( id, parent_id, name ) VALUES( 5, 4, 'C5');
insert into category( id, parent_id, name ) VALUES( 6, 4, 'C6');
insert into category( id, parent_id, name ) VALUES( 9, 6, 'C9');
insert into category( id, parent_id, name ) VALUES( 10, 6, 'C10');
insert into category( id, parent_id, name ) VALUES( 7, 1, 'C7');

insert into category_discount( user_id, category_id, discount ) VALUES( 1, 1, 30);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 4, 20);
insert into category_discount( user_id, category_id, discount ) VALUES( 1, 7, 25);

Now when I do this query:

SELECT category.*,
       category_discount.user_id,
       category_discount.discount,
       LEVEL
FROM   category
LEFT JOIN category_discount
       ON category.id = category_discount.category_id
       AND category_discount.user_id = 1
START WITH parent_id is null
CONNECT BY PRIOR id = parent_id;

...I get this output which is fine:

| ID | PARENT_ID | NAME | USER_ID | DISCOUNT | LEVEL |
|----|-----------|------|---------|----------|-------|
|  1 |    (null) | root |       1 |       30 |     1 |
|  2 |         1 |   C1 |  (null) |   (null) |     2 |
|  3 |         1 |   C3 |  (null) |   (null) |     2 |
|  4 |         1 |   C4 |       1 |       20 |     2 |
|  5 |         4 |   C5 |  (null) |   (null) |     3 |
|  6 |         4 |   C6 |  (null) |   (null) |     3 |
|  9 |         6 |   C9 |  (null) |   (null) |     4 |
| 10 |         6 |  C10 |  (null) |   (null) |     4 |
|  7 |         1 |   C7 |  (null) |   25     |     2 |
| 11 |         7 |  C11 |  (null) |   (null) |     3 |
| 12 |         7 |  C12 |  (null) |   (null) |     3 |

My requirement is whenever there is a discount on the parent it should be inherited down the hierarchy as long as the child doesn't haven an explicit discount set. So my final output should look like this:

| ID | PARENT_ID | NAME | USER_ID | DISCOUNT | LEVEL |
|----|-----------|------|---------|----------|-------|
|  1 |    (null) | root |       1 |       30 |     1 |
|  2 |         1 |   C1 |  (null) |       30 |     2 |
|  3 |         1 |   C3 |  (null) |       30 |     2 |
|  4 |         1 |   C4 |       1 |       20 |     2 |
|  5 |         4 |   C5 |  (null) |       20 |     3 |
|  6 |         4 |   C6 |  (null) |       20 |     3 |
|  9 |         6 |   C9 |  (null) |       20 |     4 |
| 10 |         6 |  C10 |  (null) |       20 |     4 |
|  7 |         1 |   C7 |  (null) |       25 |     2 |
| 11 |         7 |  C11 |  (null) |       25 |     3 |
| 12 |         7 |  C12 |  (null) |       25 |     3 |

How can I do this?

I am using Oracle 11g and Oracle 12c.

with a (id, parent_id, name, user_id, discount, lvl) as
(
  select
    c.id, c.parent_id, c.name, cd.user_id, cd.discount, 1 as lvl from category c
  LEFT JOIN category_discount cd
       ON c.id = cd.category_id AND cd.user_id = 1
  where parent_id is null
  union all
  select
    c2.id, c2.parent_id, c2.name, cd.user_id,
    nvl(cd.discount, a.discount),
    lvl + 1
  from category c2
  LEFT JOIN category_discount cd
       ON c2.id = cd.category_id AND cd.user_id = 1
  join a on c2.parent_id = a.id
)
search depth first by id set dummy
select id, parent_id, name, user_id, discount, lvl from a;

        ID  PARENT_ID NAME    USER_ID   DISCOUNT        LVL
---------- ---------- ---- ---------- ---------- ----------
         1            root          1         30          1
         2          1 C1                      30          2
         3          1 C3                      30          2
         4          1 C4            1         20          2
         5          4 C5                      20          3
         6          4 C6                      20          3
         9          6 C9                      20          4
        10          6 C10                     20          4
         7          1 C7            1         25          2
        11          7 C11                     25          3
        12          7 C12                     25          3

Answer to original question:

Assuming the following based on your example data (but I am not sure this is what you want):

There is a discount for the whole branch defined by the root - I call this root_discount.

If a child (c1) has discount set, then that is the discount for that child.

If c2 is a child of c1, and c2 does not have discount set, then c2 inherits the discount of c1.

If c3 is a child of c2, and c3 does not have discount set, then c3 inherits the discount from the root (root_discount). This makes the discount 30 for rows with ID 9,10. Without this, it gets a lot simpler.

with a (id, parent_id, name, user_id, discount, prev_discount, root_discount, lvl) as
(
  select
    c.id, c.parent_id, c.name, cd.user_id, cd.discount, 1 as prev_discount,
    discount as root_discount, 1 as lvl
  from category c
  LEFT JOIN category_discount cd
       ON c.id = cd.category_id AND cd.user_id = 1
  where parent_id is null
  union all
  select
    c2.id, c2.parent_id, c2.name, cd.user_id,
    nvl(cd.discount, case when prev_discount = 1 then a.discount else root_discount end),
    case when cd.discount is not null then 1 else a.prev_discount - 1 end as prev_discount,
    root_discount, lvl + 1
  from category c2
  LEFT JOIN category_discount cd
       ON c2.id = cd.category_id AND cd.user_id = 1
  join a on c2.parent_id = a.id
)
search depth first by id set dummy
select id, parent_id, name, user_id, discount, lvl from a;

        ID  PARENT_ID NAME    USER_ID   DISCOUNT        LVL
---------- ---------- ---- ---------- ---------- ----------
         1            root          1         30          1
         2          1 C1                      30          2
         3          1 C3                      30          2
         4          1 C4            1         20          2
         5          4 C5                      20          3
         6          4 C6                      20          3
         9          6 C9                      30          4
        10          6 C10                     30          4
         7          1 C7            1         25          2
        11          7 C11                     25          3
        12          7 C12                     25          3


SQL over N database systems We have a PostgreSQL based system which is installed at 20 customers.

Sometimes I run a SQL query over all systems. Up to now I do this with the command line tool parallel like this:

parallel -q -j0 ssh {} "echo {}; psql -tAc \"select count(*) from auth_user\";echo" ::: $(ssh j list-systems prod)

I am missing the ability to sort the output. I would like to avoid dirty shell scripting, and use SQL wisdom.

How to get the N SQL queries into PostgreSQL, so that I can run ordinary SQL on it?

(Sub question: Is there a matching tag for this kind of question? I mean questions where the SQL spans several databases?)

If you need to do that very often and you can connect to all of those servers directly, I would setup one Postgres instance that acts as a "unified" view on all other servers.

To do that create a new Postgres database (or server) and install the postgres_fdw extension.

Then create one foreign server for each of the database you want to look at.

Then create an (empty) table in your new database, that looks exactly like the corresponding table on the other servers, e.g.

create table auth_user
(
  id integer,
  user_name varchar(100)
);

Then for each server create a foreign table, e.g.:

create foreign table auth_user_server1 ()
inherits auth_user
server customer_1_server options (table_name 'user_auth');

It's probably a good idea to include the option updatable 'false' when defining the foreign table to prevent accidental updates from the integration server to the remote servers.

The above steps could all be scripted if necessary.

Once all tables are defined, you can do a simple:

select count(*)
from auth_users;

on your "integration" server and Postgres will automatically query all remote tables.


How to add spatial indexes through rockmongo In rockmongo we got fields for the field name and whether we should sort by ascending or descending.

How to add spatial indexes then?

From what I can see, RockMongo does not support the creation of geo indexes directly.  Because creating an index is really just an insert into the system.indexes collection for the database (ensureIndex is just a shell helper), it is possible. So, you can still do it via RockMongo, but it is arguably more complicated than using the CLI.

Here is how I successfully added a Geo index via RockMongo:

Go to the system.indexes collection of the parent database (foo in my case)
Insert the following document:

{
   "v": 1,
   "key": {
     "loc": "2d"
  },
   "ns": "foo.geotest",
   "name": "loc_"
}

Substitute the name of the field ("loc" above), the index name ("loc_" above) and the namespace ("foo.geotest" above) as appropriate.

In order to add a field to specify the index type, you are going to have to make a feature request in the project I would think - there is nothing obviously similar currently open:

http://code.google.com/p/rock-php/issues/list


MySQL replication between datacenters I am trying to figure out the best MySQL replication topology, but I am not sure the best way to lay it out.

datacenter A | datacenter B
     ____    |     ____
    |    |   |    |    |
    | AA |   |    | BA |
    |____|   |    |____|
     ____    |     ____
    |    |   |    |    |
    | AB |   |    | BB |
    |____|   |    |____|
             |

These 4 MySQL servers are connected in pairs with AA and AB in Datacenter A, while BA and BB will be located in datacenter B. Datacenter A and Datacenter B are on different subnets to increase redundancy in the event of an ISP failure.

AA and AB will share a VIP using keepalived, heartbeat, or similar.

BA and BB will also share a different VIP using the same method.

I am trying to find the best way to use replication to ensure that all servers are kept in sync. Only one DB server will be written to at any given time (handled through HAProxy load balancer - not pictured)

I think there should be Master-Master replication between AA <-> BB as well as Master-Master between BA <-> BB.

Would it be better to use DRBD between AA <-> AB as well as DRBD between BA <-> BB?

If DRBD is used like this, how will I handle replication between datacenter A and datacenter B? I prefer something similar to master-master where a slave does not need to be manually promoted as this can take time for us to respond.

The HA aspect of this problem may be better suited on serverfault, but I am not sure.

I would definitely use a DRBD pair in one DataCenter and a DRBD pair in the other DataCenter.

I have written posts about using DRBB in this way:

Mar 29, 2011 : MySQL high availability, failover and replication with Latency
Mar 26, 2012 : 3 Databases, Best Replication Setup?
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication

Note that each DRBD pair would have a DBVIP for doing local failovers. Just setup Circular Replication between the DBVIPs only (which would be between the DRBD Primaries of each DataCenter).

Since you need MySQL Replication in addition to all this, I would use Semisync Replication to lower sensitivity to network latency.

Give it a Try !!!


Percona Xtradb Cluster 5.5: node can't join cluster - SST fails with auth problem I have 3-nodes PXC 5.5 cluster. Settings on node03 in my.cnf [mysqld] section:

wsrep_cluster_address=gcomm://<ip_node1>,<ip_node2>
wsrep_cluster_name=cluster
wsrep_provider_options="gcache.size=128M"
wsrep_node_address=<ip_node3>
wsrep_node_name=node03
wsrep_slave_threads=16
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sst:XXXXXXXXXXXXXX

Settings on other 2 nodes are the same. node03 can not join cluster:

160807  5:30:18 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '<ip_node3>' --auth 'XXXXXXXXXXXXXX' --datadir '/var/lib/mysql/data/' --defaults-file '/etc/mysql/my.cnf' --parent '9532': 2 (No such file or directory)

Errors in donor node error.log:

160807  5:13:30 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '<ip_node3>:4444/xt
rabackup_sst' --auth '(null)' --socket '/var/run/mysqld/mysqld.sock' --datadir '/var/lib/mysql/data/' --defaults-file '/et
c/mysql/my.cnf' --gtid 'f964a515-38a3-11e6-8d63-47f60c48dba5:38453358'

Errors in donor node /var/lib/mysql/data/innobackup.backup.log:

160807 05:24:35 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

160807 05:24:35 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 3306, socket: /var/run/mysqld/mysqld.sock
    Failed to connect to MySQL server: Access denied for user 'mysql'@'localhost' (using password: NO).

--auth '(null)' and Access denied for user 'mysql'@'localhost' indicate that wsrep_sst_auth is not set.

But it exists in my.cnf, as you can see. I checked sst:XXXXXXXXXXXXXX on donor node on localhost, it works.

Why joiner node does not use wsrep_sst_auth from config?

I managed to perform SST by creating default user mysql@localhost without password on donor node, with required grants RELOAD, LOCK TABLES, REPLICATION CLIENT. Ugly workaround, but it worked.

On the next day I stopped mysql on node03 and started it after several minutes. SST started normally, with user and password from wsrep_sst_auth variable. Bug disappeared, I don't know what it was.


Compatibility level of databases based on sql server version We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in compatibility level of 130, we need to list them. Similarly, on a SQL Server 2017, , if there are databases that are not in compatibility level of 140, we need to list them.

Take a look into the below script

SELECT name as 'Database name',
       compatibility_level AS 'Compatibility level',
       @@VERSION as 'SQL Version'
FROM   sys.databases S

As mentioned in comment, you need to query sys.databases to get the relevant information.

Below would be straight forward query:

select name from sys.databases
where compatibility_level not in(130) -- you can hardcode this based on version of SQL server
and database_id > 4

If you want to generalize it then, you may use below query:

select name from sys.databases
where compatibility_level not in(select compatibility_level from sys.databases where database_id = 1)
and database_id > 4

Above would work fine except in scenario where DB upgrade(in-place) has taken place or if you are working on Azure. You can read about it more here.

Hope above helps.