How are binlogs updated for MySQL temporary tables? If I use CREATE TEMPORARY TABLE, can I write and update this MySQL table and know that none of it will be stored to the binlogs?
If I use data from the temp table to write to a non-temp table, is the
part-of-the-statement-that-reads-from-temp-table
simply substituted with plain values, or is there something else that happens in the binlog to indicate the data source?

I ask this because normally when reading from another table, the binlog shows the whole statement, meaning it normally does not substitute the plain values.

Statements like CREATE TEMPORARY TABLE will land in the binary logs. This has plaugued me for years when troubleshooting. Here is why:

In MySQL Replication, if you create a temp table, it will remain in existence as long as the SQL thread is in session. I have had dozens of instances where someone would schedule a nightly backup which included STOP SLAVE; before the mysqldump and START SLAVE; afterwards. When you run STOP SLAVE;, it terminates both the IO thread and the SQL thread for Replication. When the SQL thread dies, all tables created with CREATE TEMPORARY TABLE are dropped immediately. When you try to run START SLAVE;, MySQL Replication breaks instantly, stating that the table it was INSERTing into or SELECTing from does not exist. How did I find the table? I would have to run mysqlbinlog against the master's binary logs to locate the position that the CREATE TEMPORARY TABLE was issued.

So, to answer both questions, all you have to do is this:

Any INSERT into a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO tmptable ... ;
SET SQL_LOG_BIN=1;

Any UPDATE of a temp table should like like this:

SET SQL_LOG_BIN=0;
UPDATE tmptable SET ... ;
SET SQL_LOG_BIN=1;

Any INSERT into a real table using data from a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO realtable SELECT ... FROM tmptable ... ;
SET SQL_LOG_BIN=1;

UPDATE 2012-03-19 17:06 EDT

I read your comment

Question 1 from your comments : What happens if two threads both create temp tables of the same name? Do the statements fall into the binlogs and conflict?

Answer : Each temp table of identical names (even identical schemas) are unique to the the DB Connection that opened it. Therefore, no conflict whatsoever. In fact, since replication is single-threaded along the SQL thread, all SQL statements with temp tables of identical names are processed in a serialized manner, i.e., like a FIFO queue.

Question 2 from your comments : Wouldn't this cause the changes to realtable to not be reflected in the binlogs?

Answer : You are correct. Therefore, since you want the changes to the realtable  replicated to the Slave, you will not need SET SQL_LOG_BIN=0;


How to do a FullText Search on Sql Server Table which includes special characters? I am attempting to do a fulltext search on a table where one of the rows is a log and contains characters such as ,.: and so on. Currently when a query is ran using the 'Contains' keyword which includes any of those characters no rows are returned. Is there any advice on the best route that can be take for this search?
Here is what my current query look like at the moment:

ALTER Procedure [dbo].[searchLog]
    @searchValue varchar(150)
    AS
    Declare @myquote varchar(10)
    Declare @wildcard varchar(5)
    set @myquote ='"'
    set @wildcard ='*'
    set @searchValue = @myquote + @searchValue + @wildcard +@myquote
    BEGIN
    SET NOCOUNT ON
    SELECT  o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
    FROM    dbo.o AS o LEFT OUTER JOIN
                dbo.j AS j ON o.Job = j.ID LEFT OUTER JOIN
                dbo.s AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
ORDER BY o.ID DESC

Example of Logs being searched:

"Sunday, July 7, 2019 1:20:00 PM
 Seeking c:\xxxx\xxxx\log1_date.txt
 Seeking c:\xxxx\xxxx\log2_date.txt
 Seeking c:\xxxx\xxxx\log4_date.txt
 Monday, July 8, 2019 1:20:00 PM   FTP AND PGP COMPLETED Processed 0 files ."

You could try the CONTAINS function

SELECT * FROM [TableName] WHERE CONTAINS (*, 'Word')


Multi user, allow user to read data and add their own data I am trying to create a recipe database that can have many users. Each user can enter and save their own recipes, which only they have access to. The recipes are made up with various details including the ingredients that the recipe will use. The ingredients can come from 2 sources, the main 'read only' ingredient list provided by the app or from any 'custom' ingredients that the user has created (likely because the ingredient they wanted was not available in the list provided by the app). In each case the same information is stored about the ingredients (i.e. nutritional information etc.)
So a currently the schema looks something like:
User

id
name

Recipe

id
title
userId

RecipeIngredient

id
ingredientId
quantity
recipeId
userId

Ingredient

id
name
nutritionalInfoId

nutritionalInfo

id
calories
fat
protein
carbs

Category

id
name
userId

Recipe__Category

recipeId
categoryId

What is the best way to handle this? I did start going down the route of adding a UserIngredient table:
UserIngredient

id
id
name
nutritionalInfoId
userId

However, this made things more complicated when it came to assigning an ingredient to RecipeIngredient as it was either a relationship to Ingredient if the User had picked one of the apps 'built in' ingredients from the Ingredient table or one of their own from UserIngredient. It felt like there was probably a better way to handle this?

There are many ways to model this, but I would choose a single ingredient table that contains all ingredients. Add a column user_id that is a foreign key to the table of users. Add a special "public" user (perhaps with id 0) that owns all the public ingredients.
Then you could create a unique constraint on ingredient(id, user_id) and reference that in the recipeingredient table. The only drawback is that you will need a second user_id column in recipeingredient that references recipe(id, user_id). Perhaps call one user ID column recipe_user_id and the other ingredient_user_id.
Then it is easy to add a check constraint on recipeingredient that makes sure that (recipe_user_id = ingredient_user_id OR ingredient_user_id = 0), so that nobody can use somebody else's private ingredients.


Are Clustered Index Usage Stats skewed since they are referenced in Non-Clustered Keys? When using tools like SP_BlitzIndex and looking at usage stats for indexes. Would clustered index usage be skewed in any way since they are secret keys in all non-clustered indexes and would be read more often than not?

Trying to determine if a table has the correct clustered index, it's read usage is high but looking at how data is being accessed, I don't see a lot of direct joins utilizing the clustered index column that would lead me to believe the usage numbers.

The user_lookups column of sys.dm_db_index_usage_stats is a count of clustered index key lookups (a.k.a. bookmark lookups). This indicates a data row retrieved via a non-clustered index using the clustered index key row locator. The separate user_seeks and user_scans columns for a clustered index count the number of times the CI was used directly.

These columns allow you to determine how the clustered index is being used in query plans.


Query syntax is checked at java level or Database Level? If i used normal JDBC Connection for accessing database of Mysql, The syntax of query is checked at java level or Mysql level.

Example

String Query="Select * fromEMP"

above wrong query syntax throw a SQLException.

so this exception is actually thrown by MySQL or java.

Two different things probably:

Syntax is checked in MySQL server. There may be connectors/client libraries which would do the parsing and checking before sending it, but it would only mean doing the same work twice. MySQL server receives query as string and then processes it. Even for prepared statements server receives statement as text and compiles it.
SQLException is probably extended Java Exception, which is purely Java side construct (generally as part of JDBC code). So this exception is thrown by Java MySQL client library when it sends your query and instead of data, server returns some error message. Client and server communicate by socket or tcp, so there is no way how server could throw exception directly to client. It has to signalize error by some other means (probably different response packet type or something similar)


High rise in Aborted_connects on mysql slave servers I have a MYSQL replication set up where I have a single master and three slave DBs. The slave DBs are load balanced behind a Zeus Load Balancer.

I can see on slave DB servers that the Aborted_connects are rising regularly. The same is not happening on Master DB server.

mysql> show status like '%Aborted_connects%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| Aborted_connects | 340973 |
+------------------+--------+

To isolate any possible php code connection related issue and or any suspicious connection, I have disconnected on of the slave DB server and run tcpdump. I don't see any unrecognisable connection coming in to the server except for monitoring tool and master db connection (replication).

However, I have see time to time some of the below messages on mysql error log

140213  9:42:17 [Warning] Aborted connection 2099906 to db: 'unconnected' user: 'gvdb' host: '10.140.10.133' (Got an error reading communication packets)

Not sure how this might help, but someone has advised on this Fixing MySQL errors recorded in error logs to increase max_allowed_packet to 128MB.

Even after turning off replication, the Aborted_connects keep rising.

How to solve Aborted_Connects problem

1) Increase the wait_timeout variable value in [my.cnf]

2) Set the log-warnings variable to greater than 1. This will start logging the aborted connections in the mysql error log file once you identified and fixed the problem you can set it back to 1 again.

Also, You can have a look at this forum discussion which is relevant to Load balancer which checks database health every second
http://forums.mysql.com/read.php?144,194590,194590#msg-194590


Postgres tablespace on mounted usb I've got postgres 9.1 running on raspbian installed with apt-get.
I've got NTFS usb HDD mounted in /mnt/usb.
What I'd like to achieve is to create a tablespace pointing to /mnt/usb/postgres so I invoke:

CREATE TABLESPACE usbwd LOCATION '/mnt/usb/postgres';

and I get:

  could not set permissions on directory "/mnt/usb/postgres": Operation
  not permitted

which is understandable since the manual states that the target directory has to be empty (it is in my case) and owned by postgres linux user. That's what I have problem with and

sudo chown postgres:postgres /mnt/usb/postgres

changes nothing, meaning the ownership does not change. The /mnt/usb/postgres is owned by pi user, and I need to have it owned by postgres user. The /mnt/usb is also owned by pi user.
What am I missing ?

PostgreSQL won't run off NTFS on Linux. Format the drive as ext4.

Frankly, putting a database on a USB key on a tablespace isn't usually a great idea. The tablespace is unreadable without the rest of the database, and the rest of the database is useless without the tablespace. So you're creating a fragile system with two points of failure.

It's a much better idea to instead move the whole PostgreSQL data directory onto the USB drive.


Stored Procedure triggered by Service Broker can't access another database in SQLServer 2017 I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management and machine learning with R and/or Python external scripts).
I wanted to upgrade from SQL Server 2016 to SQL Server 2017, exact same architecture and scripts, but now I get errors I don't understand, apparently caused by the service broker impersonations and privileges.

The activated proc '[StoredProcTriggerdByServiceBroker]' running on queue 'queuename' output the following:  'The server principal "sbuser" is not able to access the database "mainDB" under the current security context.'

To explain a bit a simplification of my process:
There are two DBs, mainDB and testDB. ServiceBroker and stored procedures are running on testDB.
We mainly use Windows logins to use SQL Server and that was less than ideal for some SSIS purposes, so I chose to create a user called sbuser for which connection is disabled but which we can impersonate, with sufficient privileges on both DBs to do everything required. The queues are triggering the stored procedure as sbuser.
The stored procedure fails on statement
SELECT * INTO testDB..targetTable from mainDB..sourceTable

Same select query, impersonating sbuser manually works fine.
Same select query, in a stored procedure, with a execute as sbuser / exec StoredProcedure works fine.
But it fails whenever the stored procedures are triggered by Service Broker.
I added to the stored procedure a logging of CURRENT_USER and results of
SELECT * into tmp_session FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

CURRENT_USER give me sbuser, which is what I expect, but the content of dm_exec_sessions puzzles me as it's different from what I get when I log in as SA, then execute the procedure impersonating sbuser with an execute as.

security_id
login_name
context_info
original_security_id
original_login_name

0x01
sa
0x
0x01
sa

Nothing about sbuser... then again the error message in the log is about sbuser's security context...
So, what has changed between SQL Server 2016 and 2017 in the way the Service Broker works and impersonation of logins and their privileges, and how can I run my select into query now?

From comments:

I don't believe the behavior has changed between SQL 2016 and SQL 2017. It may be that the SQL 2016 database was TRUSTWORTHY whereas the SQL 2017 database is not or has a database owner sid mismatch. The proper solution is module signing as Rusanu describes. Also see the Activation Procedures for Service Broker topic topic in the appendex of Erland's module signing article. - Dan Guzman

I also supposed SQLServer 2016 hadn't CLR security activated as per the Microsoft Docs - Vincent Chalmel


Sql Server Fill Factor - Restart sql server services I changed the Fill Factor option from 1 to 75.

When I run a query that displays the fill Factor I am seeing 75

But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True?

http://sqlmag.com/database-administration/fill-factor-truth

  You must stop and restart the SQL Server service for the change to take effect. The new fill factor will be in effect when you see it in the run-value column

Yes, it is clearly stated here which options require a restart: https://msdn.microsoft.com/en-us/library/ms189631.aspx


Why is it necessary to run both mongod and mongo.exe in windows? What's happening behind the scene? Why is it necessary to run both mongod and mongo.exe in windows? What's happening behind the scene?

What's the difference between these two?

As @Stennie explained it's exactly the other way around .. mongod is the daemon background process running the database server and mongo.exe is the interactive shell.

As such - both are running if you want to use the database from the command prompt / a console [CMD] window.


Index solution for dynamic query I have a Credit/Debit table (5 millions records). The application must provide an UI View to search data providing N criteria:

Type of date (required) e.g.. Creation date or due-date
Date range (required)
Status (paid, non-paid or both)
Document number
Customer ID
Type of payment (credit card, money etc)

The user must provide the first two criteria, but the other may be or not be provided. So I have many combinations.

I think I will have to create many indexes such as:

ix_search_customerID_dueDate (customerID, dueDate)
ix_search_customerID_creationDate (customerID, creationDate)

There's uncountable combinations. I use C# and Entity Framework to generate queries based on which values was provided, that's easy to do, but I don't know how to create indexes to cover all possibilities. Is it possible or should I change the UI logic?

I read this answer so based on this I believe that if I create an index (creationDate, status, document, customerID, typeOfPayment) and the user only provides for example Customer ID the index won't work.

You can't make a single index that supports all of those optional parameters. You can make multiple indexes that do their best to support the most common combinations of parameters, but to support all possible combinations, the overhead is probably not worth it (and you'd have to measure the impact to the write portion of your workload - indexes aren't free).

My solution to the "customer can search in any way they please" problem is to create the kitchen sink procedure, which uses dynamic SQL to construct the where clause depending on the parameters provided. What this does is allow SQL Server to compile a separate plan for each combination of parameters, so that you don't have one massive query that tries to optimize for all scenarios but really can only optimize for very few. The basic approach would be:

CREATE PROCEDURE dbo.KitchenSink
  @param1 datatype = NULL,
  @param2 datatype = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.table
    WHERE 1 = 1'

    + CASE WHEN @param1 IS NULL THEN N'' ELSE
        N' AND column1 = @param1' END

    + CASE WHEN @param2 IS NULL THEN N'' ELSE
        N' AND column2 = @param2' END

    ...
  ;

  EXEC sys.sp_executesql @sql, N'@param1 datatype, @param2 datatype, ...',
         @param1, @param2, ...;
END
GO

This will provide you with the opportunity to make the best use of any indexes that have key columns that align with combinations of supplied parameters. You will have to concede that some queries will just not perform well, but this should help minimize those cases.

Additional caveats:

You can use OPTION (RECOMPILE) inside the dynamic SQL to thwart parameter sniffing (for example, even when only lastname is supplied, the dynamic SQL alone won't help you generate the best plan every time, since a search for LIKE '%x%' will require a different plan than LIKE '[a-m]%').
You should use the optimize for ad hoc workloads setting (see here and here), so that SQL Server doesn't waste valuable plan cache space on potentially complex and large plans that are only used once.


Sparse matrix - key-value db? I have a huge sparse matrix ( 10^9 rows, 10^6 cols, density < 0.03% ), where each row has at least one nonzero column, but some column may contain only zeros. The cells are decimal numbers > 0.

I am looking for some db (preferably key-value) that can retrieve whole row or whole column as fast as possible. Also, I don't need to do any analytics in the DB.

I've come across SciDB which should be fast with multidimensional data, but I am afraid it is too complex for my needs.

Also, another option is to use SQL db ( probably Postgres ) but that is a bit slow and can't be scaled as easily as most NoSQL ( I expect fast row increase in matrix ).

So my biggest hope is some key-value storage, but I am not sure how to represent the matrix.

Using CRS format - but I have no idea how to implement it using key-value store.
Maybe something like { key, key, value } and have indexed both keys, but I am not sure if this is even possible. - So far I've found some clues about secondary index, but I can't imagine how would it look like.

I have some experience with SQL databases but almost none with NoSQL :(

Interesting use case...

What makes your problem complex for a DB is your access pattern. Looks like you want to access both by row as well as column. General purpose DBs are generally either row-oriented storage (mostly) or column-oriented storage which will be their most efficient mode of access. They will support the access other way round also (for e.g column based access in a row-oriented storage) but it will not be most efficient for obvious reasons.

If you have a access pattern (row/column) which is way more frequent than the other, you can pick the appropriate DB. If both the access patterns are equally likely, you may consider storing the information in a redundant way. i.e Both the matrix and the transpose of it. As you said the density of the matrix is 0.03%, the overhead may not be too much. You can make the call here.

Coming to the DBs, most of the noSQL DBs offer a flex schema. i.e you do not need to define the schema(columns) upfront and the columns can be optional. For this reason, I think a noSQL DB will be a better fit for this sparse matrix use case. When you query for the row, you will get only the columns which has values in it. You will get the column name along with the result.

The CRS format, while it is great on its own for space saving, it does not fit so well in a DB schema. You will have to handle the access from you application logic. In other words, you will not be really using the row-based access mechanism of DB.

Another option is to use a modified CRS format. For every row, you can store the matrix column values as a series of (column,column value) pairs. You can store this as a single value in a single column of the database. This will avoid the per-column overhead of the DBs. However, you have to do extra processing to decode the matrix columns in your application.

Which DB ? I dont want to pick a name. I would be starting an opinion war. Please do this research separately.


I didn't get listener file get installed I have downloaded oracle sql developer to access Remote database. After downloading i couldn't connect to the database. I don't have tns.ora file installed in my system neither do i have oracle_home. Do I need to install oracle database too in order to have tns.ora file in my system.

SQL Developer can connect to Oracle databases without Oracle client and tnsnames.ora.

Creating a Database Connection Using SQL Developer

More details you can read at the above url.


MySQL 8.0.15 starts normally but any connection hangs I dropped a large table (80G) Friday morning and got a crash:

InnoDB: ###### Diagnostic info printed to the standard error stream
2019-04-19T18:48:55.445507Z 0 [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 37617336 seconds. We intentionally crash the server because it appears to be hung.[FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2019-04-19T18:48:55.445541Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:625 thread 140252955932416
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
18:48:55 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

...lots more...

The server came back up and the log indicated it was ready for connections:

...

2019-04-22T12:53:51.792574Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 26956
2019-04-22T12:53:54.631860Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-04-22T12:53:54.639196Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-04-22T12:53:54.708512Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-04-22T12:53:54.754962Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.15'  socket: '/data/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

I tried connecting locally and remotely with mysql client, various programs, etc., but cannot complete the connection process - it just hangs. The connection protocol initiates, but then just sits there after negotiation (this is from monitoring with tcpdump):

10:44:02.814237 IP 10.70.250.33.snip-slave > 10.70.250.50.mysql: Flags [S], seq 3534733756, win 14600, options [mss 1460,sackOK,TS val 2565739642 ecr 0,nop,wscale 7], length 0
10:44:02.814273 IP 10.70.250.50.mysql > 10.70.250.33.snip-slave: Flags [S.], seq 4211652446, ack 3534733757, win 28960, options [mss 1460,sackOK,TS val 239668057 ecr 2565739642,nop,wscale 7], length 0

...

10:44:03.042941 IP 10.70.250.50.mysql > 10.70.250.33.snip-slave: Flags [P.], seq 2784:2819, ack 858, win 252, options [nop,nop,TS val 239668286 ecr 2565739871], length 35
10:44:03.082582 IP 10.70.250.33.snip-slave > 10.70.250.50.mysql: Flags [.], ack 2819, win 160, options [nop,nop,TS val 2565739911 ecr 239668286], length 0

The mysqld process has been running at nearly 100% CPU, for over the weekend even, but nothing seems to change. I can't stop the server with systemd, as it'll just sit there too. I can (and have) killed the mysqld process several times today and started it back up, but again nothing seems to change. Most recently I did that to force innodb recovery, which shows in the log, but is no help since I still can't connect with anything.

Looking with iostat, it appears to be beating the daylights out of the general log (/dbdata/var/lib/mysql/data/mysql/general_log.CSV), which is quite large - over half a TB.

I don't really care about the table that was dropped, I just need the rest of the databases to be accessible, and I'm stuck.  Help, please!

Everything but the bit about the general log was a distraction. Killing the mysqld daemon, disabling the general log in my.cnf and then restarting the server worked fine. Although I can't find this documented anywhere, apparently mysqld will be completely unresponsive while it is busy prowling through it's general log, and if that log is extremely large, it can take an arbitrarily long time.


Does `WITH XMLNAMESPACES` have the ability to trigger a DDL database trigger? I'm having an issue where, intermittently, a stored procedure is causing a database trigger to fire. The trigger is for CREATE_FUNCTION, CREATE_PROCEDURE, CREATE_TABLE, CREATE_VIEW, CREATE_TRIGGER
but the procedure itself does not do any of those things. It's a straightforward write procedure that takes an XML parameter and inserts the values into a set of tables. The one thing that is unusual (for us) is that it's using WITH XMLNAMESPACES but it would seem weird for that to be the issue.

Disclaimer: I'm tackling this problem at once removed. I'm having to trust the original developers that they aren't also trying to create an object as part of their code above the database (they say that's the case). I'm also checking all of the tables being written into right now to make sure they don't have weird triggers that try to do something they shouldn't. So far, I've not been able to duplicate the effect in a test environment, even when impersonating a user with minimum access. This is on a SQL Server 2014 server.

This issue came to light because the point of the database trigger is to only let the "build" login create objects on the database.

So to keep my question narrow, is it possible for WITH XMLNAMESPACES to be the issue here?

UPDATE: As a follow up, I was able to get the necessary information. It looks like the error was being caused because the calling code, after calling the stored procedure in question, also called:

exec sp_describe_first_result_set N' EXEC usp_Problem_procedure @P1 ',N'@P1 xml'

And it looks like this is what's actually causing the error. It also looks like the trigger was ultimately not the only problem. Even fixing or disabling the trigger just caused a later error:

Msg 11528, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'REVERT

    --Check if SSB is enabled in this database' in procedure 'sp_send_dbmail' does not support metadata discovery.

Apparently one of the nested procs has the potential to use sp_send_dbmail (we discourage this, fwiw, as it's destroyed the Exchange server more than once).

So that's it. Once I showed the developers what was happening, they recognized that they just needed to call a method slightly differently, and that avoided the problem.

To the question in the title: I'm going to say NO. Something else is going on to force the DDL trigger to fire.

You should (at least temporarily) change the DDL trigger to store the EVENTDATA() output to a table so you can examine what else the app is sending when they call this stored procedure. There must be additional DDL that is being sent, such as an ALTER or DROP/CREATE, because a DDL trigger can't - by definition - be fired simply from executing a stored procedure that doesn't, itself, issue DDL commands.

You may want to set up a server-side trace, too, filtered to this application so you can see what else it might be sending to the server in addition to the call to the stored procedure, since there is a chance it is doing something they don't know. Trust but verify.


Getting the information that exists in "Job properties -> Notifications" page? I use SQL Server 2008 R2 and I need get some properties from the msdb database.

I need a query to get information that exists in the Job properties -> Notifications page.

In which table I can find this information?

Because of where the information is stored in msdb, part of the information is obtained when the Job Properties window is initially opened, then more information is queried when you actually click on the Notifications page. SSMS uses an INSERT/EXEC on the sp_help_ stored procedures to obtain the operator information, which is pretty unwieldy... so unfortunately, using Profiler in this case to trap the queries isn't particularly helpful.

Here's a completed query that returns all the information from the page in one shot:

USE [msdb];
GO

WITH jobStates AS
(
    SELECT 0 AS Level,  'Disabled' AS Description UNION ALL
    SELECT 1,           'On Success' UNION ALL
    SELECT 2,           'On Failure' UNION ALL
    SELECT 3,           'On Completion'
)
SELECT
    j.job_id,
    j.name,
    es.Description AS EmailOnJobState, e.name AS EmailOperatorName, e.email_address AS EmailOperatorEmailAddress,
    ps.Description AS PageOnJobState, p.name AS PageOperatorName, p.pager_address AS PageOperatorPagerAddress,
    nss.Description AS NetSendOnJobState, ns.name AS NetSendOperatorName, ns.netsend_address AS NetSendOperatorNetSendAddress,
    els.Description AS EventLogOnJobState,
    ds.Description AS DeleteJobOnJobState
    FROM [dbo].[sysjobs] j
    INNER JOIN jobStates es ON es.Level = j.notify_level_email
    INNER JOIN jobStates ps ON ps.Level = j.notify_level_page
    INNER JOIN jobStates nss ON nss.Level = j.notify_level_netsend
    INNER JOIN jobStates els ON els.Level = j.notify_level_eventlog
    INNER JOIN jobStates ds ON ds.Level = j.delete_level
    LEFT OUTER JOIN [dbo].[sysoperators] e ON (j.notify_level_email > 0) AND (e.id = j.notify_email_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] p ON (j.notify_level_page > 0) AND (p.id = j.notify_page_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] ns ON (j.notify_level_netsend > 0) AND (ns.id = j.notify_netsend_operator_id);


Intermittent RESOURCE_SEMAPHORE_QUERY_COMPILE Wait Stats I'm trying to troubleshoot some intermittent CPU spikes that we're witnessing on one of our production SQL Servers.  We're running SQL Server 2008 R2 Standard Edition with 28 GB RAM and 4 CPU cores.  When this happens, we are noticing a large number of RESOURCE_SEMAPHORE_QUERY_COMPILER waits, which lasts for about a minute or two and then stops, which then the CPU usage returns to normal.

After researching this, I understand that this is normally caused by compiling lots of non-reusable execution plans, which we are currently working on changes to our application to address.

Can this behavior also be triggered by plan cache evictions due to memory pressure? If so, how would I check for this?  I'm trying to see if there are any short-term remedies we can do, like upgrading the server RAM, until we deploy our application fixes.  The only other short-term option I can think of is to move some of our busiest databases to different server.

I believe you will see this symptom if you have a LOT of large query plans that are fighting for memory in order to compile (this has very little to do with running the query itself). To hit this, I suspect you are using an ORM or some kind of application that generates many unique but relatively complex queries. SQL Server could be under memory pressure because of things like large query operations, but on further thought it is more likely just that your system is configured with far less memory than it needs (either there is never enough memory to satisfy all of the queries you're trying to compile, or there are other processes on the box that are stealing memory from SQL Server).

You can take a look at what SQL Server is configured with using:

EXEC sp_configure 'max server memory';    -- max configured in MB

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'Total Server Memory (KB)',    -- max currently granted
    'Target Server Memory (KB)'    -- how much SQL Server wished it had
  );

You can identify the cached plans that required the most compile memory with the following Jonathan Kehayias query, adapted slightly:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (10) CompileTime_ms, CompileCPU_ms, CompileMemory_KB,
  qs.execution_count,
  qs.total_elapsed_time/1000.0 AS duration_ms,
  qs.total_worker_time/1000.0 as cputime_ms,
  (qs.total_elapsed_time/qs.execution_count)/1000.0 AS avg_duration_ms,
  (qs.total_worker_time/qs.execution_count)/1000.0 AS avg_cputime_ms,
  qs.max_elapsed_time/1000.0 AS max_duration_ms,
  qs.max_worker_time/1000.0 AS max_cputime_ms,
  SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
    (CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
     END - qs.statement_start_offset) / 2 + 1) AS StmtText,
  query_hash, query_plan_hash
FROM
(
  SELECT
    c.value('xs:hexBinary(substring((@QueryHash)[1],3))', 'varbinary(max)') AS QueryHash,
    c.value('xs:hexBinary(substring((@QueryPlanHash)[1],3))', 'varbinary(max)') AS QueryPlanHash,
    c.value('(QueryPlan/@CompileTime)[1]', 'int') AS CompileTime_ms,
    c.value('(QueryPlan/@CompileCPU)[1]', 'int') AS CompileCPU_ms,
    c.value('(QueryPlan/@CompileMemory)[1]', 'int') AS CompileMemory_KB,
    qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS n(c)
) AS tab
JOIN sys.dm_exec_query_stats AS qs ON tab.QueryHash = qs.query_hash
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY CompileMemory_KB DESC
OPTION (RECOMPILE, MAXDOP 1);

You can see how the plan cache is being used with the following:

SELECT objtype, cacheobjtype,
    AVG(size_in_bytes*1.0)/1024.0/1024.0,
    MAX(size_in_bytes)/1024.0/1024.0,
    SUM(size_in_bytes)/1024.0/1024.0,
    COUNT(*)
FROM sys.dm_exec_cached_plans
GROUP BY GROUPING SETS ((),(objtype, cacheobjtype))
ORDER BY objtype, cacheobjtype;

When you are experiencing high semaphore waits, check to see if these query results vary significantly from during "normal" activity:

SELECT resource_semaphore_id, -- 0 = regular, 1 = "small query"
  pool_id,
  available_memory_kb,
  total_memory_kb,
  target_memory_kb
FROM sys.dm_exec_query_resource_semaphores;

SELECT StmtText = SUBSTRING(st.[text], (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1),
  r.start_time, r.[status], DB_NAME(r.database_id), r.wait_type,
  r.last_wait_type, r.total_elapsed_time, r.granted_query_memory,
  m.requested_memory_kb, m.granted_memory_kb, m.required_memory_kb,
  m.used_memory_kb
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_query_stats AS qs
ON r.plan_handle = qs.plan_handle
INNER JOIN sys.dm_exec_query_memory_grants AS m
ON r.request_id = m.request_id
AND r.plan_handle = m.plan_handle
CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) AS st;

And you may also want to look and see how memory is distributed:

DBCC MEMORYSTATUS;

And there is some good information here about why you might be seeing a high number of compiles/recompiles (which will contribute to that wait):

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

http://technet.microsoft.com/en-us/library/cc293620.aspx

You can check for high compile/recompile counts using the following counters:

SELECT counter_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN
  (
    'SQL Compilations/sec',
    'SQL Re-Compilations/sec'
  );

And you can check for internal memory pressure leading to evictions - non-zero counters here would indicate that something not good is going on with the plan cache:

SELECT * FROM sys.dm_os_memory_cache_clock_hands
  WHERE [type] IN (N'CACHESTORE_SQLCP', N'CACHESTORE_OBJCP');

NOTE Most of these metrics don't have a magic "oh my gosh I need to panic or do something!" threshold. What you need to do is to take measurements during normal system activity, and determine where these thresholds are for your hardware, configuration and workload. When you panic do something is when two conditions are true:

the metrics vary significantly from normal values; and,
there is actually a performance problem occurring (like your CPU spikes) - but only if they are actually interfering with anything. Other than seeing the CPUs spike, are you seeing any other symptom? In other words, is the spike the symptom, or is the spike causing other symptoms? Would users of the system ever notice? A lot of people always go after their highest wait consumer, simply because it's the highest. Something is always going to be the highest wait consumer - you have to know that it's varying enough from normal activity that it indicates a problem or some significant change.

Optimize for ad hoc workloads is a great setting for 99% of the workloads out there, but it will not be very helpful in reducing compilation costs - it is aimed at reducing plan cache bloat by preventing a single-use plan from storing the whole plan until it's been executed twice. Even when you only store the stub in the plan cache, you still have to compile the full plan for the execution of the query. Perhaps what @Kahn meant to recommend was setting the database level parameterization to forced, which will potentially provide better plan re-use (but it really depends on how unique all of these high-cost queries are).

Also some good information in this white paper about plan caching and compilation.


What's the best way to maintain a writable DB slave which is always overwritten by the master? I need to replicate our main server's databases (MariaDB) to a local server in our office, which will be used as an offline mirror when we have connectivity issues.

The data on the master should always take priority over the data on the local slave. Our users will be instructed never to write anything to the local "offline" slave, however it will still need to be writeable to enable auth & sessions.

I believe that with a standard Master-Slave setup, this will create problems with data consistency.

Short of writing a shell script to drop & re-migrate all of the databases, & reset the replication logs when the connection is re-established, is there a better way to configure such replication natively with MariaDB or Galera?

You could use symmetricds to perform the bidirectional replication. It allows for conflict management in which you can define which side wins in the conflict in your case the master.


Analyse what caused a pagesplit afterwards? Ok normally we have 0-20 page splits per second, but last night it spiked to ~500.

Now is there any way to figure out what caused it?

I know i can run the following to find mid page splits but it doesn't go that far back.

SELECT COUNT(1) AS NumberOfSplits, AllocUnitName, Context FROM fn_dblog(NULL,NULL) WHERE Operation = 'LOP_DELETE_SPLIT' GROUP BY AllocUnitName, Context ORDER BY NumberOfSplits DESC

I have both full backups and log backups if that helps?

I must say first that using fn_dblog and fn_dump_dblog is unsupported as these commands are not supported by Microsoft. Jonathan Kehayias in his article even pointed out that using fn_dump_dblog multiple times has grave performance implication so use these undocumented commands on your own risk. There is also a way to track page splits, not perfectly though but will help, using DMV Sys.dm_db_index_operational_stats the columns leaf_allocation_count in output points to page split which occurred for index and nonleaf_allocation_count gives you page allocation due to page splits read the documentation please. You can include object(table) for which you want to track page split by editing the query

Below is the code which may tell you page splits for index and heap does not have page splits they have forwarding pointers

select
ios.index_id,
o.name as Object_Name,
i.name as Index_Name,
ios.leaf_allocation_count as Page_split_for_index,
ios.nonleaf_allocation_count Page_allocation_caused_by_pagesplit
from sys.dm_db_index_operational_stats(DB_ID(N'db_name'),Null,Null,Null) ios
join
sys.indexes i
on
ios.index_id=i.index_id
join
sys.objects o
on
ios.object_id=o.object_id
where o.type_desc='user_table'

Other method is by using Extended events, below link by Jonathan has steps( applicable to SQL 2012 or later)

http://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/

Ofcourse you can read below article by Paul Randal where he showed how to track page split using fn_dblog command

http://www.sqlskills.com/blogs/paul/tracking-page-splits-using-the-transaction-log/


Inserting data with SELECT into new column I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.

How things start:

SELECT COUNT(fullpath) FROM data;

12395

I use the column fullpath a lot so I create an index:

CREATE INDEX "index" ON data (fullpath);

I then add the column:

ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);

Insert values into the Sub column based on the fullpath:

insert into data(Sub)
select case
    when fullpath like '%subA%' then 'subA'
    when fullpath like '%subB%' then 'subB'
    else 'neither' end
from data;

Check the number of rows again:

SELECT COUNT(fullpath) FROM data;

24790

I don't understand the syntax and the operations. Do I need to join or something to respect the index?

You already solved your main problem!

But I want to suggest you to create a new table for sub and maybe also for fullpath, because you store the same values again and again in this row.

Something like this:

CREATE TABLE path (id integer, name varchar(100));

Then populate it with a SELECT and add the ID to the main table!


Partial rollback in function in postgres 9.6 Pseudo code

INSERT INTO table_a
INSERT INTO table_b
RAISE EXCEPTION
UPDATE table_b

Is there a way to keep the inserted table_a tuples but not the table_b tuples when this code exists within a function?

Something like this:

DECLARE
  _exception_message text;
  _exception_hint text;
BEGIN
    INSERT INTO table_a
    BEGIN
        INSERT INTO table_b
        RAISE EXCEPTION
        UPDATE table_b
        RETURN;
    EXCEPTION
      WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS _exception_message = MESSAGE_TEXT, _exception_hint = PG_EXCEPTION_HINT;
            RETURN;
    END;
END

Reference: https://stackoverflow.com/questions/42638907/savepoint-in-postgresql-function


Change bucket count for Primary Key Nonclustered Hash on existing In-Memory table I'm on SQL Server 2019 Enterprise edition. I have a table with this definition
CREATE TABLE dbo.MyMemoryTable
(
    FirstId int NOT NULL,
    SecondId int NOT NULL,
    MyCount int NOT NULL,
    CONSTRAINT PK_MyMemoryTable PRIMARY KEY NONCLUSTERED HASH (FirstId) WITH (BUCKET_COUNT = 16000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

It's prefilled with data that are frequently queried and refreshed. There are few schema bound native compiled procedures.
While the bucket count was ok for some time and was 2x the number of unique values, it's not enough now and I'd like to change it to 32 768. I would like this operation to be online.
In my test environment, I used the following ALTER to change the bucket count:
ALTER TABLE dbo.MyMemoryTable
ALTER INDEX PK_MyMemoryTable REBUILD WITH(BUCKET_COUNT = 32768);

Which passes without errors, but scripting out the table still shows the previous 16k buckets
as well as DMV's
SELECT
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],
  i.name                   as [index],
  h.total_bucket_count,
  h.empty_bucket_count,

  FLOOR((
    CAST(h.empty_bucket_count as float) /
      h.total_bucket_count) * 100)
                            as [empty_bucket_percent],
  h.avg_chain_length,
  h.max_chain_length
FROM
        sys.dm_db_xtp_hash_index_stats  as h
  JOIN sys.indexes                     as i
          ON h.object_id = i.object_id
          AND h.index_id  = i.index_id
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

How to achieve this without downtime? I could drop and recreate the table, but I need to drop the schema bound procedures first and that would cause errors.
EDIT: Tested it on a newly created table and it worked.
I've added some data and it worked as well.
I've added more rows than the bucket count, still worked
I've added natively compiled procedure and it worked.
I've tested it again on my real table and it worked now. I have no idea what happened.
The only thing that is suspicious that when I ran the DMV query, it showed me MyMemoryTable but also something called TT___MemoryTable or something like that - maybe that prevented the update?

As Dan Guzman mentioned in the comments, the alter isn't fully online (though should be quick enough). But another alternative that probably has the most minimal downtime is to create a copy of the table with a different name. Then use the sp_rename system stored procedure to rename the old table to a different name, and rename the new table to your old table's original name. This operation is very quick since it's basically just updating a reference in meta data on the server.


Does creating indexes require downtime We have a table with few million rows on production. We ran this query and it froze our database,

USE ExampleDatabase

GO
CREATE NONCLUSTERED INDEX IX_Table1_URL
ON [dbo].[Table1] ([URL])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table1_LastModified
ON [dbo].[Table1] ([LastModified])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table2_URL
ON [dbo].[Table2] ([URL])
GO

GO
CREATE NONCLUSTERED INDEX IX_Table2_LastModified2
ON [dbo].[Table2] ([LastModified2])
GO

I am a developer with very limited knowledge of indexes, I am not sure where to start, just to add both Table1 and Table2 have million of rows.

If you're on SQL Server Enterprise Edition, you can add the ONLINE switch:

CREATE NONCLUSTERED INDEX IX_Table1_URL
ON [dbo].[Table1] ([URL])
WITH (ONLINE=ON);
GO

Most of the index creation will happen without blocking, although not all - read this DBA.se question & answer for details about when even online index creation blocks other queries.

Your server may still be much slower during the index creation, though - creating indexes can be hard work, taxing your SQL Server's storage.


Regularly Archiving Data to Azure I have a database – call it Jacob. Every day a SQL Agent job copies all records older than 15 days to another database called Jacob_Archive

I would like to move Jacob_Archive to Azure, I’ve created a database with the tables, but when I try to move the data I get:

  Msg 40515, Level 15, State 1, Line 16
  Reference to database and/or server name in
  Jacob_Archive.dbo.MasterResults` is not supported in this version of SQL Server.

Please advise

On the Jacob_Archive database you need create an External Data Source that makes reference to the source database.

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='ReferenceData',
    CREDENTIAL= SqlUser
);

Then create External Tables for all tables on the source database that you want to archive data.

CREATE EXTERNAL TABLE [dbo].[zipcode](
    [zc_id] int NOT NULL,
    [zc_cityname] nvarchar(256) NULL,
    [zc_zipcode] nvarchar(20) NOT NULL,
    [zc_country] nvarchar(5) NOT NULL
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
);

Then extract all the data you want from the remote table using an elastic query. You can create a stored procedure to extract the data you want to archive and put it on a table located on the Jacob_Archive database.

Once you have extracted the information you can run a stored procedure on the source table to remove the archived data. You can create an schedule task to perform/run both stored procedures using Azure Automation.


PostgreSQL - maximum of sum over different months with ties over multiple years This question is the PostgreSQL version of a question about MySQL 5.6 here. Originally, it was one question for both RDBMS's but it was suggested to me that, given the different capabilities of the two systems, I should split the question - in particular I think that CTEs (WITH clause) should make the query far more elegant and readable!

Suppose I have a list of tumours (this data is simulated from real data):

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

You want to find out which particular tumour was most common in a given month - so far so good!

Now, you will notice that for month 1 of 2017, there is a tie - so it makes no sense whatsoever to randomly pick one and give that as the answer - so ties have to be included - this makes the problem much more challenging.

I have a solution but it's quite complex - I'd like to know if my solution is optimal or not. The PostgreSQL fiddle is here! The query in the fiddle is very cumbersome - I'll have a look at using CTE's.

My first answer (which works with both PostgreSQL and MySQL) is included in the fiddle but I won't post it here as I believe that it will be superceded by PostgreSQL's superior capabilities and it would be just a copy of my answer to the MySQL question!

For a given month:
SELECT tumour_count, illness
FROM (
   SELECT count(*) AS tumour_count, illness
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- given month: 2007-01
   AND    created_at  <  '2017-02-01'  -- optimized for index lookup
   GROUP  BY illness
   ) sub
WHERE  rnk = 1;

There should be an index on (created_at), or maybe even (created_at, illness) to allow index-only scans.
Subqueries are a bit faster than CTEs in Postgres. So only use CTEs where you need them, or when performance is not important.
Related:

How to optimise window queries in postgres
Count rows with max per group and additional condition

For any given period of time
As requested in the comment:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, illness
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
ORDER  BY mon, illness;

Careful if you have leading or dangling partial months, the counts may be misleading.
This is functionally equivalent to what ypercube already provided. Just some simplifications to be a bit shorter / faster. And the added filter for a given period of time.
Index support becomes less important with the growing share of rows read from the table - and stops being useful at all for more than roughly 5 %. (Exceptions apply, like for index-only scans.)
You can still have 1 row per month, by aggregating tying peers. Like:
SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, string_agg(illness, ' | ')
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
GROUP  BY mon, tumour_count
ORDER  BY mon;

db<>fiddle here


Are there other sites forbidding SQL developer to use c style comments? My personal opinion is that C style comments should not be used by sql developers.

The problem with C Style comments is, that they do not nest.

C Style comments are one of the lifesavers for the support when they have to do some ad hoc corrections in stored procedures, but that only works well, when they don't have to fight with existing C comments.

I never use C-style comments in SQL code.  First off, Management Studio gives a very easy way to comment out blocks of code with SQL-style commenting, and just as easy to uncomment it out.

And not to mention if another person opens a SQL script file not in SSMS, C-style comments have a tendency to be elusive and the commented out code doesn't look so commented.

Just my opinion, but I stay away from C-style commenting.  No gain.


Indexes slowing Insertions(Can't use Explain Insert to Analyse) I have a MySQL database,  I am parsing files from a log and inputting it inside.

So far including a simple 4MB file in the database could take me about 4 minutes to insert ! and i have about 10,000 of such files to process.

At first i thought it's my script but after further digging i learned that this could be related to indexes.

I have a table with 4 columns, 1 primary key and 2 foreign keys which are by default indexes.

From this is it possible to know if the indexes affect insertion performances ? I can't seem to use Explain Insert with MySQL so i don't know of any more information to provide.

CREATE TABLE `as_path` (
  `AS_Path_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Path_Index` int(11) DEFAULT NULL,
  `AS_No` int(11) NOT NULL,
  `Update_ID` int(11) NOT NULL,
  PRIMARY KEY (`AS_Path_ID`,`Update_ID`,`AS_No`),
  KEY `AS_No_fk_idx` (`AS_No`),
  KEY `Update_ID_fk_as_path_idx` (`Update_ID`),
  CONSTRAINT `AS_No_fk` FOREIGN KEY (`AS_No`) REFERENCES `as_detail` (`AS_No`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Update_ID_fk_as_path` FOREIGN KEY (`Update_ID`) REFERENCES `update_detail` (`Update_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1074256 DEFAULT CHARSET=utf8;

A file of 4 MB should not take 4 minutes to insert.I see your table have InnoDB engine.

I hope you first need to optimize basic InnoDB settings for that please have a look at Innodb Performance Optimization Basics

Now How to speed up InnoDB Inserts

There's an entire document  dedicated to bulk loading data into InnoDB. The main points:

disable autocommit to avoid an extra log flush for each insert statement: SET autocommit=0;...sql import;COMMIT;
disable foreign and unique checks (you can't disable all indexes completely):
SET unique_checks=0;
SET foreign_key_checks=0;

Setting innodb_flush_log_at_trx_commit = 0 (or 2) may also speedup inserts. Please note, ACID rules would love that value to be at 1.


Moving a large MariaDB database between MariaDB servers Once my DB size is multiple hundred GB in size, it is not practical to use the documented methods for transferring a database to an alternate db server due to sheer amount of time (days to weeks) for that slow process.  The database is primarily located within a directory tree structure which can easily be copied, however, there seem to be files at the "data" directory (ibdata1, ...) not unique to a specific database, that contain some information that is required to successfully xfer to a new location.  Is there a way to extract the needed information from this file or set of files and provide to the new dB server?  Or is there some way to cause MariaDB to maintain each database within the appropriate directory tree, thereby permitting file transfers to function?
Some background on my objective:
I capture data to my databases on a specific server, but that server is limited to only 1TB disk size.
Once the space on that disk reaches a threshold level (say 10% remaining), I want to move that database to an alternate server with much greater storage available, and free the space on the capturing server.
Stating the objective a little differently: I wish to take a db from one server(1) and add that db to a different server(2), allowing all tables in the "copied" db(1) to be emptied, freeing space for additional collection. (but do so in finite time -- minuites/hours instead of days/weeks)

At my job, we do this several times per week. I'm part of a team who manages about 9000 MySQL database instances, and we're constantly moving them from server to server as they grow.
NOTE: You cannot just copy files from a MySQL / MariaDB datadir while the server is running. You are virtually guaranteed to corrupt your database. Some subset of the data is in log files or in RAM, and it won't be copied with consistency.
We do it this way:

Use Percona XtraBackup to make a physical backup. It doesn't take long, even for a multi-hundred-GB database instance.

Transfer this backup to the other host.

Restore the backup on that new host.

Configure replication on the new host, to subscribe to changes on the original host. This allows it to "catch up" to all the changes that occurred while you were transferring the file and restoring them.

Wait for the new instance to catch up.

Set the original instance to be read-only, so applications can't make any new changes. Then let the replica catch up with respect to the final few seconds of changes (this might take no time at all).

Update applications to use the new server instead of the old one. This is done either by changing configuration in the app, or else just use DNS to give the name of the original server to the new server.

This is all assuming you want the whole MySQL instance to be moved to a new server. If you have individual schemas in the original server that you want to move, that's a bit more complicated.


Do isolation levels only apply to SELECTS and not UPDATES? Scenario that might demonstrate different behavior for SELECTS depending on the isolation level:

1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete
2) 0:02 Thread B runs a query that returns the same 1000 rows
3) 0:05 Thread A updates the last 1 rows in this result set and commits them
4) 0:07 Thread B's query returns*

Depending on the isolation level, the result set in #4 will either contain Thread A's changes or it won't. Is the same true for UPDATES?

The following is an example scenario:

Thread A: UPDATE... set version=6 ... WHERE primary_key = 1234 AND version = 5
Thread B: UPDATE... set version=6 ... WHERE primary_key = 1234 AND version = 5

If both Thread A and Thread B enter their transactions at the same time, and Thread B performs its update after Thread A, will Thread B's update fail to update any rows or will it "see" the record with version 5 and therefore overwrite what Thread A wrote?

Does it depend on the database? e.g. Oracle vs MySql vs PostgreSQL?

In MS SQL Server I can say that it depends on your isolation level.  If you set it more similar to Oracle by making it 'snapshot isolation', the last row that thread A is trying to update will have the 'before' value copied to tempdb, and all reads will read that while it is being updated via a row lock.

If you have the default isolation level of read committed, it will lock that row (or in some cases page) blocking your reader from that particular row or page while it updates it.  This is unless you specify SET ISOLATION LEVEL READ UNCOMMITTED or put a (NoLock) hint on the FROM clause of the query, thus allowing dirty reads.

It all has to do with the Isolation level in ACID, and it's the only ACID property that you have control over in MS SQL Server.


Cons of using a star schema in a columnar database My organisation is implementing a new data warehouse using a columnar data store (Redshift) and the performance is currently abysmal.  There are many causes for this but I think the key reason is because of the way we have our data structured, we're implementing a classic star schema design, which surely is optimal for a traditional RDBMS (OLTP) and not necessarily for OLAP?

I'm looking for someone with experience on this to briefly explain the pros and cons of structuring large quantities of data in a star schema on amazon redshift.

I'm obviously no expert, but I'm looking for some advice and guidance on this.

thanks,
Matt

You are mixing up three orthogonal concepts: data model (star schema), workload characteristics (OLTP vs. OLAP), and physical data organisation (columnar).

Your data model has no bearing on whether column-organised tables are appropriate for you; however, data organisation must reflect the nature of your workload (i.e. queries). For example, SELECT * FROM... cannot possibly perform well in a columnar database.


SQL Always on Availability LSN Query Please can somebody explain how the following works:

Scenario:

Two nodes in always on availability group configuration (all databases).

If I have a backup job on node 1 to backup Full and Log backups to a network share, then failover to node 2 and continue the log backups, will this not break the log chain? So each time a failover occurs does a full backup job need to be executed?

Thank you

As I understand, you are performing a full backup and log backups on server 1, then you perform a fail over to server 2.

When you start performing the log backups on server 2 it follows the log chain.

So to answer your question: No it is not needed to perform a full backup job every time your availability group fails over.

I suggest that you test this in a pre-production environment. Do full backup on server 1, perform log backups on server 1, fail over, do log backup on server 2. Restore on another server the full backup and the log backups from server 1 followed with the log backups from server 2.

Good luck.

Stan


What is the correct way to select data grouped by time interval? I need to display a chart that would illustrate how many ticket sales happen at different time intervals on different dates, e.g. How many tickets for Friday show get sold 3 hours in advance, etc.

The end result will look something like:

I have written a query by calculating values for different offsets at the SELECT statement:

WITH
  -- Create a date range; use LEFT JOIN/ COALESCE to construct date specific report; we don't want gaps in the data
  dates AS (
    SELECT generate_series(
      '2018-04-26',
      '2018-04-28',
      INTERVAL '1 day'
    ) AS date
  ),
  sales AS (
    SELECT
      ts1.date,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at < INTERVAL '0 hour' THEN 1 ELSE 0 END) after_event_start,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_0_to_hour_1,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_1_to_hour_2,
    -- [..]
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '22 hour' AND INTERVAL '23 hour' THEN 1 ELSE 0 END) hour_22_to_hour_23,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '23 hour' AND INTERVAL '24 hour' THEN 1 ELSE 0 END) hour_23_to_hour_24,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at > INTERVAL '24 hour' THEN 1 ELSE 0 END) after_24_hour
    FROM ticket_sale ts1
    WHERE
      ts1.movie_id = 1012718 AND
      ts1.starts_at > '2018-04-26' AND
      ts1.starts_at < '2018-04-28'::date + INTERVAL '1 day'
    GROUP BY ts1.date
  )
SELECT
  to_char(d1.date, 'YYYY-MM-DD') "date",
  COALESCE(s1.hour_0_to_hour_1, 0) hour_0_to_hour_1,
  COALESCE(s1.hour_1_to_hour_2, 0) hour_1_to_hour_2,
  -- [..]
  COALESCE(s1.hour_22_to_hour_23, 0) hour_22_to_hour_23,
  COALESCE(s1.hour_23_to_hour_24, 0) hour_23_to_hour_24,
  COALESCE(s1.day_6_to_day_7, 0) after_24_hour
FROM dates d1
LEFT JOIN sales s1 ON s1.date = to_char(d1.date, 'YYYY-MM-DD')
ORDER BY d1.date

This works. However, as a query it looks odd.

Is there a better way to count sales at different time intervals?

I really overcomplicated this.

As suggested by @Gaius, all I needed to do is to query all the data grouped by the hour of the purchase, i.e.

SELECT
  ts1.date,
  ROUND(EXTRACT(EPOCH FROM (ts1.starts_at - ts1.created_at))/3600) "hour",
  COUNT(*) sales_count
FROM ticket_sale ts1
WHERE
  ts1.movie_id = 1012718 AND
  ts1.date = '2018-04-26'
GROUP BY ts1.date, hour

I can post-process the data in the client-side to group it into the required intervals.


SUM() the result of a function I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this :

I have a function which runs on 4 shards. It looks like this :

CREATE OR REPLACE
FUNCTION count_comm(
    id_toto INTEGER,
    OUT out_nb_toto INTEGER
) RETURNS SETOF INTEGER AS $$
        CLUSTER 'main'; RUN ON ALL;
$$ LANGUAGE plproxy;

It returns me a SETOF INTEGERS like this :

p_commentaire=> SELECT count_toto(42);
 count_commentaire_from_id_toto
---------------------------------
                         2172022

So basically, each of the result is the COUNT() of each shard, it works great as I said.
The thing is, I wanted to know if there's a way to SUM() all the results to get a total instead of having the 4 results individually ...

Edit : Just to add something, PlProxy needs a 'SETOF' return when you run the query on all shards.

So, I found a workaround...

First I made a proxy function on which the first proxy function will run :

CREATE OR REPLACE
FUNCTION p_count_toto(
    in_id_toto INTEGER
) RETURNS INTEGER AS $func$
BEGIN
        RETURN (SELECT sum(tmp_nb_toto) out_nb_toto FROM count_comm(in_id_alias));
END;
$func$ LANGUAGE plpgsql;

And I modified my first PL/Proxy function which looks like this now :

CREATE OR REPLACE FUNCTION count_toto(
        in_id_toto INTEGER,
        OUT tmp_nb_toto INTEGER
) RETURNS SETOF INTEGER AS $$
        CLUSTER 'main'; RUN ON ALL;
$$ LANGUAGE plproxy;

I think that's not the most elegant way to do it, because at the application level, devs will have to change the name of the procedure they'll call, but it does the work.


Why does dm_os_memory_clerks have MEMORYCLERK_SQLCLR values on one NUMA node? We are using a SQLCLR stored procedure for service broker activation, and I want to monitor the memory used by the CLR code. Looking at sys.dm_os_memory_clerks, I see that only NUMA node 1 has any pages associated with the MEMORYCLERK_SQLCLR type. The server has two 8-core CPUs and is running SQL 2014 CU6.

Is this expected? Or should I see memory used on both nodes like I do with MEMORYCLERK_SQLBUFFERPOOL?

Query:

SELECT DOMC.memory_node_id
    , DOMC.pages_kb
    , DOMC.virtual_memory_reserved_kb
    , DOMC.virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks DOMC where type = 'MEMORYCLERK_SQLCLR'

Results:

memory_node_id pages_kb             virtual_memory_reserved_kb virtual_memory_committed_kb
-------------- -------------------- -------------------------- ---------------------------
0              88232                12607744                   1408652
1              0                    0                          0
64             0                    0                          0

Unfortunately, there is not a whole lot of information out there on the specifics of SQLCLR memory usage. I did, however, find the following two resources helpful as a starting point:

Memory Usage in SQL CLR
SQL Server: Clarifying The NUMA Configuration Information

From those and looking through various DMVs and the MSDN pages explaining (well, it's not much explaining, but technically it's more than nothing) how those DMVs kinda sorta work, I put together the queries shown below. From running those on a few different systems, it seems that SQLCLR memory allocation is concentrated on one particular memory_node_id, but there is still some allocation on other nodes, at least in terms of Proc Cache. So for now, unless someone has evidence or information to the contrary, what you are experiencing is "expected".

This query reports the base info:

SELECT domc.*
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

You will notice that by not specifying one particular [type], there are a few more that can take up some memory. Total 4 types that I have seen are:

MEMORYCLERK_SQLCLR,
MEMORYCLERK_SQLCLRASSEMBLY
CACHESTORE_CLRPROC
CACHESTORE_CLRUDTINFO

If you are wondering why I am filtering out [memory_node_id] of 64, run this:

SELECT * FROM sys.dm_os_nodes;

and you will see that node_id of 64 is DAC (Dedicated Administrator Connection).

The following will give you the totals for the two important fields. Please note that the fields changed in SQL Server 2012 so you need to pick the one that is appropriate for the version of SQL Server it is being used on:

-- SQL Server 2012, 2014, and 2016:
SELECT SUM(domc.pages_kb) AS [TotalPagesKb],
       SUM(domc.virtual_memory_committed_kb) AS [TotalVirtualMemoryKb]
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

-- SQL Server 2005, 2008, and 2008 R2:
SELECT SUM(domc.single_pages_kb + domc.multi_pages_kb) AS [TotalPagesKb],
       SUM(domc.virtual_memory_committed_kb) AS [TotalVirtualMemoryKb]
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

The next query shows the relationship between "memory_clerks" and "memory_objects":

SELECT domc.*, N' █ ' AS [ █ ], domo.*
FROM   sys.dm_os_memory_clerks domc
LEFT JOIN sys.dm_os_memory_objects domo
        ON domo.page_allocator_address = domc.page_allocator_address
WHERE  domc.[type] LIKE N'%CLR%'
AND    domc.[memory_node_id] <> 64;

The following queries are a more focused version of the first query (and more like the query in the Question), but the fields changed in SQL Server 2012 so you need to pick the one that is appropriate for the version of SQL Server it is being used on:

-- SQL Server 2012, 2014, and 2016:
SELECT domc.memory_node_id,
       domc.[type],
       domc.pages_kb,
       domc.virtual_memory_committed_kb
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

-- SQL Server 2005, 2008, and 2008 R2:
SELECT domc.memory_node_id,
       domc.[type],
       (domc.single_pages_kb + domc.multi_pages_kb) AS [pages_kb],
       domc.virtual_memory_committed_kb
FROM   sys.dm_os_memory_clerks domc
WHERE  domc.[type] LIKE '%CLR%'
AND    domc.[memory_node_id] <> 64;

UPDATE:

I did some additional testing on a dual-processor system by creating 2 databases and, using a static variable, loaded 80 MB into each of the databases. I created two databases instead of one to see if an AppDomain had to remain on one memory node, if it would be able to at least place another AppDomain on another memory node in order to spread things out. The results:

All 160 MB across both DBs (and hence across both AppDomains) went onto memory node 0
All 160 MB went into virtual memory, not physical memory. It was only the [virtual_memory_committed_kb] field in sys.dm_os_memory_clerks that increased. And the "Commit Size" column in "Task Manager" also reflected this increase.


How do you document your databases? I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.

How do you document your database? (SQL-Server)
What tool do you use?
Documentation Storage Format for database schema/meta-data?

Word documents
Excel spreadsheet
Plain Text

Documentation process or policies?

I am not talking about reverse engineering / document a existing database, but mainly on the documentation best practices while you develop your system/database.

I have been using extended properties since they are very flexible.  Most standard documentation tools can be driven off MS_Description, and then you can use your own with custom-built tools.

See this presentation: #41-Get a Lever and Pick Any Turtle: Lifting with Metadata

And this code: http://code.google.com/p/caderoux/wiki/LeversAndTurtles


Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger TLDR: The CTXCAT index is supposed to be transactional, but Before Insert triggers that modify the indexed column don't seem to induce an update.

I'm running on Oracle 9i.
(Actually, I'm running on 12c, but building for 9i.)

I have a table like so:

------------------------------------------
| Names                                  |
|----------------------------------------|
| first_name | last_name | compound_name |
|----------------------------------------|
| Joe        | Smith     | JOE SMITH     |
| ...        | ...       | ...           |
------------------------------------------

I'm using Oracle Text (available on 9i) with a CTXCAT index on the compound_name field.  CTXCAT is a transactional index, meaning that it updates any time there is a change to whatever it's indexing.  This is in contrast to a CONTEXT index, which requires a manual or periodic sync command issued to it, at which point the index updates.

Index creation code: (forgive the capitalization)

BEGIN
  ctx_ddl.create_preference   ('MY_WL', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute       ('MY_WL', 'substring_index',  'YES');
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_index',     'YES');
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_min_length',  1);
  ctx_ddl.set_attribute       ('MY_WL', 'prefix_max_length',  7);
END;

begin
  CTX_DDL.CREATE_INDEX_SET('MY_IS');
  CTX_DDL.ADD_INDEX('MY_IS', 'is_active');
end;

-- Creating the ctxcat index uses the owner's default tablespace.
ALTER USER test DEFAULT TABLESPACE misc;

CREATE INDEX test.names_idx on test.names (name_compound)
              indextype is ctxsys.ctxcat
              parameters ('WORDLIST MY_WL
                           INDEX SET MY_IS');

I never make changes directly to the compound_name column.  Instead, I have a Before Insert Or Update trigger, that composes that value out of first_name and last_name.  The trigger looks like this:

create or replace trigger test.names_compound_trg
              before
                insert or
                update of first_name, last_name
              ON test.names
                 FOR EACH ROW
   begin
      select (
        UPPER(
              NVL2(:new.first_name,   :new.first_name || ' ',   null) ||
              NVL2(:new.last_name,    :new.last_name,           null)
        )
      ) INTO :new.compound_name FROM sys.dual;
   end;
/

You can see that any change to the first_name or last_name fields updates the compound_name field with a capitalized, concatenated string.  The trigger capitalizes it because Oracle 9i does not support case-insensitive indexes.  To get a case-insensitive search, I uppercase the compound_name and in my query I uppercase the search value.

Now, the problem:

Executing this SQL command does not cause my index to update:

update test.names set first_name = 'george', last_name = null where ...;

But executing this SQL command will:

update test.names set
  first_name = 'george',
  last_name = null,
  compound_name = 'george'
where ...;

The test.names_compound_trg trigger works properly and populates the compound_name field alright.  But it seems as though, even though CTXCAT is supposed to be transactional, it's not smart enough to detect that I've manipulated the :new.compound_name value in my trigger.  In the tests I've done so far, only a direct manipulation of that field with an update or insert query will trigger an update to the index.

Any solutions to this?  I would like to more or less keep the set up I have, with the compound_name field being populated by triggers rather than in my DAO layer, but I need to force the CTXCAT index to update itself whenever that field changes, even if the trigger is changing it.

Alternative methods of handling this are welcome as well, but I'd prefer a simple tweak or annotation or something to just get this working.

Thanks!

Aha!  I've found the answer.  Talk about an edge case.

First, I found this post from 2007, where someone says:

  ...the AFTER trigger for synchronizing the CTXCAT index on [column] is
  not firing (since my update statement does not include the indexed
  column).

  ...

  Oracle, would it not be better to generate the CTXCAT
  trigger to examine the :old and :new values in the indexed column,
  rather than using a check on (if updating([column]))?

Over a year later, someone replied:

  I fixed this issue by adding additional UPDATING (first_name and
  last_name) conditions in the DR$table_name trigger.

  Here is part of the modified trigger.

  if (inserting or updating('LAST_NAME_FIRST') or updating('FIRST_NAME')
  or updating('LAST_NAME')) then reindex := TRUE;

Hm, so the CTXCAT index uses a trigger to know when it needs to update the index for a particular entry.  All I need to do is tweak the trigger and recompile it so it does what I want.

To get the content of the trigger:

SELECT text
  FROM   user_source
  WHERE  name = 'DR$NAMES_IDXTC'
  AND    type = 'TRIGGER'
  ORDER  BY line;

I copied this into Sublime, prettified it, and found this (simplified):

TRIGGER "TEST"."DR$NAMES_IDXTC" AFTER
INSERT
OR
UPDATE ON "TEST"."NAMES"
FOR EACH ROW DECLARE REINDEX boolean := FALSE;

BEGIN

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME") THEN REINDEX := TRUE;
END IF;

...

END;

You can see that the 12c Oracle Text version's AFTER trigger does actually compare the :new and :old values of the indexed column to see if it needs to update--not the case back in 2008.

So...if I'm updating the :new value in my BEFORE trigger, that should be reflected in the AFTER trigger, and the comparison would kick off an update to the index.  What gives?

Well, here are the two SQL statements I was using:

update test.names set
  first_name = 'Skye',
  last_name = 'Fillingim'
  where ... ;

update test.names set
  first_name = null,
  last_name = null
  where ... ;

(Slightly different than what I said in my question, I apologize.)

The effect here is that, each time I used one of these statements, either the :old.compound_name or :new.compound_name would be null.  So when we get to this condition:

OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"

We are doing an equality comparison against a null, which evaluates to UNKNOWN.  Hence, no index update.

This is actually an extreme edge case, because you have to be indirectly modifying :new.column via a trigger, and either :new or :old must be null.  I would never have discovered it it I hadn't used those exact SQL statements.

So, we have a slight update to the trigger:

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"
    OR (:new."COMPOUND_NAME" IS NULL AND :old."COMPOUND_NAME" IS NOT NULL)
    OR (:new."COMPOUND_NAME" IS NOT NULL AND :old."COMPOUND_NAME" IS NULL)
    ) THEN REINDEX := TRUE;

And then just stick CREATE OR REPLACE in front of the old trigger, recompile it, and everything works perfectly.


Which of MultiStatements is "out of sync" How do I find out which statement is out of sync when I get error 2014 with Multi-Statements?

I am calling libmysql.mysql_query with a SQL Connection with SupportsMultiStatements, passing a string of commands separated by ;.
This code runs all day without errors.  But every few days (with no regularity) my application throws:

  2014 - Commands out of sync; you can't run this command now.

Retry always works.

The error message does not specify which of the multiple commands is "out of sync".  Is there any way to know?

This question is not how to fix my specific problem, so I haven't included the list of commands. (I suspect the intermittent problem is caused by a timeout waiting for table lock.)  This question is about how error 2014 applies to multi-statements.

Usually that refers to Stored routines with PREPARE + EXECUTE + DEALLOCATE_PREPARE.  Have you done all three of those, in that order?  Optionally, EXECUTE can be repeated.

If you are referring to stringing together statements with ;, don't do that; it is a security hole waiting for some hacker to jump into.

What client language are you using?

If you need more help, let's see your code.


Difference between transaction log and redo log in MySQL I have read about MySQL. In my opinion, I see two log types as very similar. MySQL saves how and when data is changed in log. Information is used for recovery of MySQL. I am confusing the function of two log types.

REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.

The transaction log is, by default- it can be changed- the two files located in $DATADIR called ib_logfile0 and ib_logfile1. It serves the same functions as the REDO log in other databases- storing writes in a safe way and recovering in the case of a crash, although there are some details in implementation that differ in functionality from other RDMS. It is the main component for InnoDB to be a transactional engine.

Do not confuse the transaction log with the binary logs in MySQL. The binlog, by default, is on the $DATADIR and is *hostname*-bin.index and several *hostname*-bin.00001, etc. It is particular confusing for people coming from other databases, because it is used for other things that other databases use the REDO log for: replication and point in time recovery. The main difference is that the transaction log is InnoDB-only, the binary log is (mostly) transaction-independent, as it is for all storage engines, transactional or not. MyISAM will write (if enabled) to the binary log. InnoDB will write to the transaction log and the binary log.

More info on the manual: REDO log, binary log.


How can I pass column to function in sql? I'm calculating median as:

DECLARE @TEMP TABLE
(
    ID INT
)

Select
(
        (
            Select Top 1 ID
            From
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID
            ) As A
            Order By ID DESC
        ) +
        (
            Select Top 1 ID
            From
            (
                Select  Top 50 Percent ID
                From    @Temp
                Where   ID Is NOT NULL
                Order By ID DESC
            ) As A
            Order By ID Asc
        )
) / 2

Above query I want to use. But, in my case there are so many columns for those I want to calculate MEDIAN. But I think it would be bad to repeat above block of code for each column.
So, I'm trying to define separate function which would accept column values, process and return median.
Should I have to define table-value-funtion for that or there is another optimized way to do so??

This question is related to the following questions:

How to write function in sql which accept table as input and return result back as table?
Getting error "must declare the scalar variable" in SQL function even though it is declared
why unable to execute table-valued function?
https://dba.stackexchange.com/questions/127328/how-to-use-udf-function-in-insert-into-select-from-clause
Calculating multiple medians

There are much more efficient ways to calculate a simple or grouped median than the one shown in your question:

What is the fastest way to calculate the median?
Best approaches for grouped median

The general winner for 2012 is a method by Peter Larsson. The pattern is:

Simple Median

SELECT
    Median = AVG(1.0 * SQ.YourColumn)
FROM
(
    SELECT NumRows = COUNT_BIG(*)
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
) AS C
CROSS APPLY
(
    SELECT YT.ColumnName
    FROM dbo.YourTable AS YT
    WHERE YT.ColumnName IS NOT NULL
    ORDER BY YT.ColumnName ASC
    OFFSET (C.NumRows - 1) / 2 ROWS
    FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
) AS SQ;

Grouped Median

SELECT
    SQ2.GroupingColumn,
    SQ2.Median
FROM
(
    SELECT
        GroupingColumn,
        NumRows = COUNT_BIG(*)
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
    GROUP BY
        GroupingColumn
) AS C
CROSS APPLY
(
    SELECT
        Median = AVG(1.0 * SQ1.YourColumn)
    FROM
    (
        SELECT YT.ColumnName
        FROM dbo.YourTable AS YT
        WHERE
            YT.GroupingColumn = C.GroupingColumn
            AND YT.ColumnName IS NOT NULL
        ORDER BY
            YT.ColumnName ASC
            OFFSET (C.NumRows - 1) / 2 ROWS
            FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
    ) AS SQ1
) AS SQ2;

To maximize the performance of the OFFSET method above, you may need to add a locking hint (advanced topic). Suitable indexing will also be required, of course.

Code reuse

This is hard to achieve directly with a T-SQL function, since these do not allow the execution of dynamic SQL (assuming you were thinking of passing in the column name).

There are several ways around this, including using a function to generate the dynamic SQL text itself, which can then be executed by the caller. There aren't really enough details in the question to say which approach would be most suitable for you.


Memory continually being eaten up since I had SQL Server 2014 SSMS installed I know SQL Server will take up all memory on a server by design, but this is my desktop not a network server.  What should I be looking at in terms of what they installed that needs to be changed so that I can work again?

I am running both SQL Server 2014 and SQl server 2008 SSMS as well as SSIS packages for both. I have no SSMS open and no are there programs besides this and task Manager Lync and my system is up to 94% of my memory being used just after a reboot. I never had this issue before they loaded 2014. But it has been continual since they did. I am sure there is some setting I need to change, but can't find anything on it as all the things I find seem to refer to how memory is handled on a server. It currently is running SQLServer.exe even though I have not opened SSMS since the reboot.

I have 6 GB of memory on an Intel Xeon CPU and Windows 7. It is an HP Z600 workstation.

SQL Server is set to start by default on system start. SSMS is just your management studio and not the SQL Server engine itself.
You can set SQL Server service (services.msc) to manual, and set the maximum memory it can allocate lower if that is a concern to you.

Important:
SSMS <> SQLServer.exe


MongoDB restore with replica set I have MongoDB setup like this:

 Primary   | Server1
-----------+---------
 Secondary | Server2
-----------+---------
 Arbiter   | Server3
-----------+---------
 Backup    | Server4

I have a backup server for daily backup using mongodump. Now I have to test my backup dump using mongorestore.

The main question is the following: What is the best way to restore the backup?

Q1: I have to follow these steps?

Stop arbiter & secondary
Drop database in primary
Restore backup to primary
Drop database on secondary
Restart all the server

Q2: Restore the backup on primary without stopping any of the servers?

Q3. Make secondary as primary and restore the backup to secondary ( previous primary), and make it primary.

Q4: Is there is any way to restore both the server?

Please suggest the best approach to restore MongoDB.

As per MongoDB BOL documented Here MongoDB provides two options for restoring secondary members of a replica set:

1) Manually copy the database files to each data directory.

2)  Allow initial sync to distribute data automatically.

  Note:- If your database is large, initial sync can take a long time to complete. For large databases, it might be preferable to copy
  the database files onto each host.


Pass (comma separated) -yet single parameter to cursor I have to get selection for a given string (with comma separated values) passed to the cursor. There can be multiple values that could be passed via single variable. My code is somewhat like:

CURSOR My_Cursor( vsStr1 )
  IS
  SELECT some_field
    FROM some_table
   WHERE txtfield1 IN ( vsStr1 );    --this field is varchar2 type

vsStr1 varchar2(100) := '01, 25, 80, 100';

How to pass that variable correclty? Any help would be appreciated.

The simplest little dirty trick I know for this is xmltable(). With xmltable()you can convert a comma-seperated list of values into rows, for example:

select * from xmltable('1, 2, 3')

COLUMN_VALUE
------------
1
2
3

Notice how the column is named column_value automatically. This is an XMLType column, that you need to convert to be able to compare it with  your regular types, for example (column_value).getnumberval() or (column_value).getstringval()

Now using this, a cursor could be coded like this:

set serveroutput on
declare
  l_parameters varchar2(50) := '101, 102, 103, 104';

  cursor my_cursor (params varchar2)
  is
  select employee_id, first_name, last_name from hr.employees
  where employee_id in
    (select (column_value).getnumberval() from xmltable(params));
begin
  for c in my_cursor(l_parameters) loop
    dbms_output.put_line(c.employee_id || ': ' || c.first_name || ', ' || c.last_name);
  end loop;
end;
/

101: Neena, Kochhar
102: Lex, De Haan
103: Alexander, Hunold
104: Bruce, Ernst


SQL Server Service pack update I have to update the current SQL server installed in my PROD server from service pack 1 to 2. What all things i should consider before starting the upgrade and how to pre-determine if there are going to be any issues if we upgrade or what all will get impacted or where will it break or what precautions to be taken..does it need a restart ?

Need help in all various aspects - basically a step by step process would be very helpful for me as I will be doing it for first time.

Your question does not indicate you are using any type of HA/DR (Clustering, Availability Groups, Mirroring, etc.), so my answer is based on the assumption of a relatively plain install of Sql Server.

This list may not be all inclusive (and I’m interested in what others might do), but it’s a list I have cobbled together over the years from many different web posts.  Since I don’t even remember where each item came from, my apologies to the original authors for not citing them directly.

Review the Readme for Service Pack/cumulative update/hotfix. Any concerns/recommendations will be found in the readme.
Always apply Service Packs and Updates on non-production Instances first. Once the Instance with the new Service Pack is up and running without any issues for a week or two, plan the same steps on your Production.
Before applying any service pack or cumulative update, run SELECT @@VERSION to retrieve your current install level and save it somewhere.  If you need to fall back from a failed upgrade, you’ll need this information.
Backups, Backups, Backups! - As I understand it, Sql Server service packs and cumulative updates only affect ‘system’ databases, so you should ‘at least’ take full backups of master, model and msdb.  I’m paranoid, so I usually back up my user databases as well.  You should also run RESTORE WITH VERIFYONLY on all backups.
Plan for failure. Depending on the type of failure, you ‘may’ be able to simply restart the upgrade and it will be successful.   A failure during the upgrade may force you to uninstall the upgrade using Add/Remove programs AND force you to restore your system databases.  If you haven’t taken the time to get comfortable with restoring the system databases (master, model, msdb), now is the time to practice that on a test server.  A failed upgrade is not the time to try and learn that process.  Search the internet for “sql server restore system databases”.  Make sure you have the ‘original’ base install media and service pack/cumulative updates for your ‘current’ @@VERSION.  You ‘might’ need to remove the current instance and reinstall up to the level you were at before the upgrade – then, you could restore your system databases and at least be back where you were before the upgrade.
I usually stop the Sql Server Agent before starting the upgrade.
Now it’s time to apply the service pack.  Hopefully all goes well and you’ll get to a successful completion screen.
You should immediately take new full backups (at least master, model, msdb)
Restart the Sql Server Agent, if stopped.
If possible, I usually restart the entire server.


MSSQL Mixed-Mode Users cant connect unless they are in the server sysadmin role SQL Server 10.0.5520 (2008 std sp3)
My goal is to enable data connections in SSMS or Visual Studio for windows users w/out them being sysadmins. Ideally this would be done through Active Directory groups, but for now I'm just targeting one use (my own) for simplicity's sake. I am using 'sa' account to make these changes.

Using either Visual Studio data connection or SSMS, my users are unable to establish a connection to the database unless they are in the server's sysadmin role.

Login failed for user 'DOMAIN\user'.
Reason: Token-based server access validation failed with an infrastructure error.
Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

The previous error states:

Error: 18456, Severity: 14, State: 11.

In SSMS, a user's server roles are limited to public. If I attempt to add the server to Visual Studio. The above error occurs, and the Test connection button fails.

When I add the sysadmin server role the connection succeeds and I am able to browse the databases to select as the initial catalog, and the 'Test Connection' button succeeds.

I have confirmed that public has been granted 'connect' permission for every type of TSQL securable type.

I have given the user total control over the target database on the server.

It seems as though this is totally related to server-roles, and that somewhere something is rejecting non sysadmin accounts. (adding all roles other than sysadmin also causes a rejection).

Permission to connect to database engine is granted to server user.

What are some of the things I should check to help enable the ability to connect?

Follow-Up:

EXECUTE AS LOGIN = 'DOMAIN\user';
SELECT * FROM fn_my_permissions(NULL, 'SERVER')
    ORDER BY subentity_name, permission_name ;
REVERT;
GO

returned 3 records

#, entity_name, subentity_name, permission_name
1, server, , CONNECT SQL
2, server, , VIEW ANY DATABASE
3, server, , VIEW ANY DEFINITION

This may or may not be relevant. I had an AD group created and made the group sysadmin, and all the users in the group can connect... I don't think feel like AD is the culprit. There is something special about sysadmin that i am missing or that was misconfigured early on....

I was able to grant the windows user access w/out the sysadmin role by granting them CONTROL SERVER in the securables list under the server-level user properties.

Thanks for all your comments. I know this thread is pretty old, but I have finally resolved the issue.

The public server role had an endpoint called ConfigMgrEndpoint that had no permission to "Connect". I granted the connect privilege and things started to work for non-sa users.

Under Server Objects>Service Broker I find the ConfigMgrEndpoint.

I have to assume that at one point MS Configuration Manager was being implemented.

I'm not sure how to dis-integrate the service broker end point, and I can't really experiment because this database is in production. Our current plan is to simply deploy an instance of mssql 2012 on a fresh server install and port the database over.

Hopefully someone else gets some benefit from this, as there was never any mention about end points in the articles i read investigating this issue!

Thanks again!