Your query_cache_min_res_unit is 4KB and you have 6MB of query cache free? Look at this:

6MB = 6144 KB
6144 KB divided by 4KB/query = 1536 queries.

You can potentially fit up 1536 small query results in that 6MB of free space in the query cache. That 6MB is most likely being viewed as memory fragmentation. Why?

According to the MySQL Documentation on Query Cache Configuration

  If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

That being the case, you may as well go for the minimum value for query_cache_min_res_unit: 512. This will save you the step of lowering it multiple times until you get better status results. At the very least, it should reduce the fragmentation.


Getting Machine name from where stored procedure was last executed

If the database is in full recovery mode and you are doing periodic log backups then you can find the login that deleted the data, and the exact moment the data was deleted. Read How to read and interpret the SQL Server log. Also, with a proper backup strategy in place, it will be trivial to recover the deleted data as well.

On the other hand, if you do not have a proper backup strategy in place, then you well deserve to loose this data and more.


How can one identify if FK's needed to be indexed apart from the NC indexes already residing

There are benefits to indexing foreign keys as they provide better join performance as described by SQLSkill's team here and here.

  It is generally recommended to create an index which leads on the foreign key column(s), to support not only joins between the primary and foreign keys, but also updates and deletes.

A script can be found here or Indexes Supporting Foreign Keys.

Review any index recommendation that the scripts give and make sure you dont end up created a lot of indexes since there is always a cost of maintaining them.

Also, make sure that your FK's are trusted.


This could be viewed as a very speculative question. However, for the database community it should be a no brainer and a slam dunk.

Edgar Frank Codd : Inventor of the Relational Model we all model databases with.

Christopher J Date : Extensive writer of Database Theory. He keeps evolving database theory and expresses such in his current website.

Donald Chamberlin and Raymond Boyce : Authors of SQL

Some people say that Pioneers explore the land and take all the arrows, while settlers get to dwell in the land left by the trailblazing of Pioneers.

Other names can be added to this list, but database theory and research had its roots from the works of these Pioneers. All users of Databases after their inital works are like Settlers in the land.

Anyone can read books or Google names of such people, but nothing beats using the principles of relational theory to appreciate the depth of their research.


How to determine if an Index is uselss in Percona MySQL?

Percona themselves have written a tool for this very purpose - it's called pt-index-usage and is to be found here.

From the DESCRIPTION:

  This tool connects to a MySQL database server, reads through a query
  log, and uses EXPLAIN to ask MySQL how it will use each query. When it
  is finished, it prints out a report on indexes that the queries didn’t
  use.

Percona's servers, tools, blogs and employees are highly regarded parts of the MySQL ecosystem/community.


Performance tuning across two tables using indexes?

Your first query is also a JOIN, only using the 'old' ANSI-89 syntax, instead of the explicit JOIN clause which was introduced in ANSI-92.

Most DBMS should treat both queries as equivalent, but you should check with your specific engine by executing both and examining the execution plan.

With regard to indexing, I would recommend that you try a composite index on (id, col_x) for both tables.
I would also try and add a predicate to the WHERE clause:

WHERE table_a.id = ? AND table_b.id = ?

Even though this is logically redundant, and will not change the output as the join already filters out rows where the id is not the same, some query optimizers tend to better use indexes when the predicate is explicit and not implied, as they lack the logic to infer that. Check the execution plan, and you will know.

Happy holidays!


MySql - Ranking so it only needs to be done once

You're almost there: Add order by Score1, score2, and enclose it with an update statement:

update players,
    (SELECT UserId, Name, Score1, score2, Rank FROM
        (SELECT userid, Name, Score1, Score2,
        @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank,
        @incRank := @incRank + 1,
        @prevScore1 := Score1,
        @prevScore2 := Score2
        FROM players p,
        (SELECT @curRank :=0, @prevScore1 := NULL, @prevScore2 := NULL, @incRank := 1) r
        ORDER BY Score1, score2     )x) s
    set players.rank=s.rank where players.userid=s.userid;

This is the final result on a sample table:

mysql> select * from players order by score1, score2;;
+--------+--------+--------+--------+------+
| userid | name   | score1 | score2 | rank |
+--------+--------+--------+--------+------+
|      2 | Nathan |     16 |     13 |    1 |
|      3 | Steve  |     22 |     14 |    2 |
|      4 | James  |     22 |     14 |    2 |
|      1 | John   |     95 |     12 |    4 |
|      5 | Alain  |    168 |     16 |    5 |
+--------+--------+--------+--------+------+
5 rows in set (0.00 sec)

I'll upvote the question because it has the big part of the solution.


ERROR unknown variable > 'keyring_okv_conf_dir=/usr/local/mysql/mysql-keyring-okv'

I figured this out. This feature is only available in ENT version of mysql and I am using community ...


In case of multiple updates of the same record in a single transaction, how many versions are being stored?

In case of multiple updates of the same record in a single transaction, how many versions are stored?

Just one.

The first update to the row generates a row version and exclusively locks the row. Later updates to the same row within the same transaction do not generate new row versions.

A linked list of row versions can arise as follows:

Transaction T1 in a SNAPSHOT transaction reads a row and sees the value 'a'.
Transaction T2 (under any isolation level) updates the value from 'a' to 'b' and commits. This generates a row version for the value 'a'.
Transaction T3 (under any isolation level) updates the value from 'b' to 'c' and commits. This generates a row version for the value 'b'. This row version links to the previous row version for 'a'.
Transaction T1 reads the row value by traversing the link from the value stored on the current page ('c'), to the row version 'b', and then on back to row version 'a'.

Row versions are generated by data changes regardless of whether there is an existing reading transaction (at any isolation level). The versions may never be needed, but they are still generated - the same linked list of versions is created even without transaction T1.


There are a few problems with any recommendations here.  The basic issue is that you have a set of tradeoffs in all replication solutions that you won't find in a single source.

The simple approach is to start with the documentation and work on setting up the built-in replication.  The manual is a good place to start there.  If all you want is a copy of everything that's what you want.  Pay close attention to the hardware and software points there and make sure the systems are essentially the same architecture and OS (i.e. both 64-bit, and both Linux).  Ideally they should be the same OS version and PostgreSQL compiled with the same options (binary packages are good here).

However if that doesn't cut it, here are some book recommendations:

"PostgreSQL 9.0 High Performance" by Greg Smith
and
"The PostgreSQL 9 Admin Cookbook" by Simon Riggs and Hannu Krosing.


SQL Server Jobs - syspolicy_purge_history fails

Thanks to the comment of @Tony Hinkle.  I was able to resolve the issue based on the comment at the bottom of this thread.

SQL Server 2012 syspolicy_purge_history fails step 3 "Erase Phantom System Health Records."

Under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps130 I changed the key ExecutionPolicy value to Unrestricted.

I did not make any changes to keys under HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqltoolsps130.


Trigger for removing group with no users left in

If you don't specify FOR EACH ROW in your CREATE TRIGGER statement, it will default to FOR EACH STATEMENT.  In this case, the OLD and NEW records will never be assigned - in the end, which row should they refer to, if you change, for example, a hundred of them?
So, create your trigger as follows:
CREATE TRIGGER TR1
       AFTER DELETE
       ON GROUP_MEMBERSHIP
       FOR EACH ROW
       EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Using the idea from @Darwin von Corax, you could also use a FOR EACH STATEMENT trigger to remove all groups that are now empty:
CREATE OR REPLACE FUNCTION DELETE_EMPTY_GROUPS()
RETURNS TRIGGER AS
$$
BEGIN
    PERFORM DELETE_GROUP(g.ID)
    FROM GROUPS AS g
    WHERE NOT EXISTS (
        SELECT 1
        FROM GROUP_MEMBERSHIP AS gm
        WHERE gm.ID_GROUP = g.ID
    );
    RETURN NULL;
END
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER TR1
   AFTER DELETE
   ON GROUP_MEMBERSHIP
   FOR EACH STATEMENT
   EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Notes:

I believe the trigger should be defined as AFTER DELETE in both cases.
I also believe that your function throws an error (ERROR:  query has no destination for result data) - use PERFORM instead of SELECT.
If you delete many rows (group memberships) at a time, the FOR EACH STATEMENT trigger might be more efficient, as it fires only once.


Backup is most likely Appending to the Last Backup--add WITH INIT to the backup (see below)

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

"A disk device does not have to exist before it is specified in a BACKUP statement. If the physical device exists and the INIT option is not specified in the BACKUP statement, the backup is appended to the device."

declare @DB sysname
declare @DISK sysname
SET @DB = 'DBNAME'
SET @DISK = 'L:\' + @DB + '_'+convert(varchar, getdate(), 112)+'.bak'
BACKUP DATABASE @DB
TO  DISK = @DISK
WITH COMPRESSION, COPY_ONLY, STATS = 5, INIT
GO

p.s. Just saw Aaron's comment--if it's right--please give him a bump as well. Done


The real answer is:

Stop using NOLOCK

OTHER INFORMATION THAT MIGHT BE USEFUL:

Sure, deny direct access to the tables/views, and enforce data access through stored procedures (or views - thanks Martin) - which you can code to use NOLOCK.

If you need it to be per user, and you can enforce data access through procedures, you can use the idea suggested here by SQLMenace:

CREATE PROCEDURE dbo.blah
AS
BEGIN
  SET NOCOUNT ON;

  IF SUSER_SNAME() = N'something'
  BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  END

  SELECT columns FROM dbo.Customer;
END
GO

Or you could also do this:

CREATE PROCEDURE dbo.blah
AS
BEGIN
  SET NOCOUNT ON;

  IF SUSER_SNAME() = N'something'
  BEGIN
    SELECT columns FROM dbo.Customer WITH (NOLOCK);

    RETURN;
  END

  SELECT columns FROM dbo.Customer;
END
GO

If that is not possible, I thought of suggesting a plan guide, but I haven't used them to enforce NOLOCK. Also this document in Books Online suggests that this is reserved for INDEX, FORCESCAN, and FORCESEEK hints. Using a plan guide to try and add NOLOCK if it isn't already present in the query text will lead to an error. Though, the code shown in Example K seems to contradict that, so maybe it is worth a shot after all. Of course, you'll need to set up a plan guide for every query, which may be prohibitive (or impossible), and they're not exactly for the weak of heart.

And I know you don't want to hear the cons of NOLOCK, but it would be much easier (and better all around!) to force everyone to use snapshot.

To address Kenneth's suggestion of using a logon trigger: in my testing this SET option can be specified in a logon trigger, but like setting it in a stored procedure, the setting reverts to the server default when the trigger goes out of scope. So no, there doesn't seem to be a way to set a session's isolation level from a logon trigger.


How to do a FullText Search on Sql Server Table which includes special characters?

You could try the CONTAINS function

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


The best approach is "normalize your data".

If you cannot or don't want by some reason then try to use 4 separate queries and combine their outputs using UNION:
    select id
    from table
    where  p1_user_id='username'
      and turn=0
      and timestamp > 20200101
UNION
    select id
    from table
    where  p2_user_id='username'
      and turn=0
      and timestamp > 20200101
UNION
    select id
    from table
    where  p3_user_id='username'
      and turn=0
      and timestamp > 20200101
UNION
    select id
    from table
    where  p4_user_id='username'
      and turn=0
      and timestamp > 20200101

Each separate query can use index ((pX_user_id, turn, timestamp) or (turn, pX_user_id, timestamp) are the most suitable), i.e. for total improvement you need to create 4 separate indices. Of course final UNION  will cause filesort, but, if the final amount of output rows is not too huge then you may have an improvement totally (but may not - be ready).

When this query runs, it doesn't use the indexes. Using explain, it shows the possible indexes, but then doesn't select one.

Server can use the index only as compact table version, not as an index - but for this the index must be covering, i.e. it must look similar to (turn, timestamp, p1_user_id, p2_user_id, p3_user_id, p4_user_id, id) - but I doubt that this is reasonable.


Why does my SELECT DISTINCT TOP N query scan the entire table?

There look to be three different optimizer rules that can perform the DISTINCT operation in the above query. The following query throws an error which suggests that the list is exhaustive:

SELECT DISTINCT TOP 10 ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, QUERYRULEOFF GbAggToSort, QUERYRULEOFF GbAggToHS, QUERYRULEOFF GbAggToStrm);

  Msg 8622, Level 16, State 1, Line 1

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

GbAggToSort implements the group-by aggregate (distinct) as a distinct sort. This is a blocking operator that will read all of the data from the input before producing any rows. GbAggToStrm implements the group-by aggregate as a stream aggregate (which also requires an input sort in this instance). This is also a blocking operator. GbAggToHS implements as a hash match, which is what we saw in the bad plan from the question, but it can be implemented as hash match (aggregate) or hash match (flow distinct).

The hash match (flow distinct) operator is one way to solve this problem because it is not blocking. SQL Server should be able to stop the scan once it finds enough distinct values.

  The Flow Distinct logical operator scans the input, removing duplicates. Whereas the Distinct operator consumes all input before producing any output, the Flow Distinct operator returns each row as it is obtained from the input (unless that row is a duplicate, in which case it is discarded).

Why does the query in the question use hash match (aggregate) instead of hash match (flow distinct)? As the number of distinct values changes in the table I would expect the cost of the hash match (flow distinct) query to decrease because the estimate of the number of rows it needs to scan to the table should decrease. I would expect the cost of the hash match (aggregate) plan to increase because the hash table that it needs to build will get bigger. One way to investigate this is by creating a plan guide. If I create two copies of the data but apply a plan guide to one of them I should be able to compare hash match (aggregate) to hash match (distinct) side by side against the same data. Note that I can't do this by disabling query optimizer rules because the same rule applies to both plans (GbAggToHS).

Here's one way to get the plan guide that I'm after:

DROP TABLE IF EXISTS X_PLAN_GUIDE_TARGET;

CREATE TABLE X_PLAN_GUIDE_TARGET (VAL VARCHAR(10) NOT NULL);

INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT CAST(N % 10000 AS VARCHAR(10))
FROM dbo.GetNums(10000000);

UPDATE STATISTICS X_PLAN_GUIDE_TARGET WITH FULLSCAN;

-- run this query
SELECT DISTINCT TOP 10 VAL  FROM X_PLAN_GUIDE_TARGET  OPTION (MAXDOP 1)

Get the plan handle and use it to create a plan guide:

-- plan handle is 0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000
SELECT qs.plan_handle, st.text FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%X[_]PLAN[_]GUIDE[_]TARGET%'
ORDER BY last_execution_time DESC;

EXEC sp_create_plan_guide_from_handle
'EVIL_PLAN_GUIDE',
0x060007009014BC025097E88F6C01000001000000000000000000000000000000000000000000000000000000;

Plan guides only work on the exact query text, so let's copy it back from the plan guide:

SELECT query_text
FROM sys.plan_guides
WHERE name = 'EVIL_PLAN_GUIDE';

Reset the data:

TRUNCATE TABLE X_PLAN_GUIDE_TARGET;

INSERT INTO X_PLAN_GUIDE_TARGET WITH (TABLOCK)
SELECT REPLICATE(CHAR(65 + (N / 100000 ) % 10 ), 10)
FROM dbo.GetNums(10000000);

Get a query plan for the query with the plan guide applied:

SELECT DISTINCT TOP 10 VAL  FROM X_PLAN_GUIDE_TARGET  OPTION (MAXDOP 1)

This has the hash match (flow distinct) operator that we wanted with our test data. Note that SQL Server expects to read all of the rows from the table and that the estimated cost is the exact same as for the plan with the hash match (aggregate). The testing that I did suggested that the costs for the two plans are identical when the row goal for the plan is greater than or equal to the number of distinct values SQL Server expects from the table, which in this case can be simply derived from the statistics. Unfortunately (for our query) the optimizer picks the hash match (aggregate) over hash match (flow distinct) when the costs are the same. So we're 0.0000001 magic optimizer units away from the plan that we want.

One way to attack this problem is by decreasing the row goal. If the row goal from the optimizer's point of view is less than the distinct count of rows we'll probably get hash match (flow distinct). This can be accomplished with the OPTIMIZE FOR query hint:

DECLARE @j INT = 10;

SELECT DISTINCT TOP (@j) VAL
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1, OPTIMIZE FOR (@j = 1));

For this query the optimizer creates a plan as if the query just needs the first row but when the query is executed it gets back the first 10 rows. On my machine this query scans 892800 rows from X_10_DISTINCT_HEAP and completes in 299 ms with 250 ms of CPU time and 2537 logical reads.

Note that this technique will not work if the statistics report just one distinct value, which could happen for sampled statistics against skewed data. However, in that case it's unlikely that your data is packed densely enough to justify using techniques like this. You may not lose much by scanning all of the data in the table, especially if that can be done in parallel.

Another way to attack this problem is by inflating the number of estimated distinct values SQL Server expects to get from the base table. This was harder than expected. Applying a deterministic function cannot possibly increase the distinct count of results. If the query optimizer is aware of that mathematical fact (some testing suggests it is at least for our purposes) then applying deterministic functions (which includes all string functions) will not increase the estimated number of distinct rows.

Many of the nondeterministic functions did not work either, including the obvious choices of NEWID() and RAND(). However, LAG() does the trick for this query. The query optimizer expects 10 million distinct values against the LAG expression which will encourage a hash match (flow distinct) plan:

SELECT DISTINCT TOP 10 LAG(VAL, 0) OVER (ORDER BY (SELECT NULL)) AS ID
FROM X_10_DISTINCT_HEAP
OPTION (MAXDOP 1);

On my machine this query scans 892800 rows from X_10_DISTINCT_HEAP and completes in 1165 ms with 1109 ms of CPU time and 2537 logical reads, so the LAG() adds quite a bit of relative overhead. @Paul White suggested to try batch mode processing for this query. On SQL Server 2016 we can get batch mode processing even with MAXDOP 1. One way to get batch mode processing for a rowstore table is to join to an empty CCI as follows:

CREATE TABLE #X_DUMMY_CCI (ID INT NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX X_DUMMY_CCI ON #X_DUMMY_CCI;

SELECT DISTINCT TOP 10 VAL
FROM
(
    SELECT LAG(VAL, 1) OVER (ORDER BY (SELECT NULL)) AS VAL
    FROM X_10_DISTINCT_HEAP
    LEFT OUTER JOIN #X_DUMMY_CCI ON 1 = 0
) t
WHERE t.VAL IS NOT NULL
OPTION (MAXDOP 1);

That code results in this query plan.

Paul pointed out that I had to change the query to use LAG(..., 1) because LAG(..., 0) doesn't appear to be eligible for the Window Aggregate optimization. This change reduced the elapsed time to 520 ms and the CPU time to 454 ms.

Note that the LAG() approach isn't the most stable one. If Microsoft changes the uniqueness assumption against the function then it may no longer work. It has a different estimate with the legacy CE. Also this type of optimization against a heap isn't necessary a good idea. If the table is rebuilt it's possible to end up in a worst case scenario in which almost all of the rows need to be read from the table.

Against a table with a unique column (such as the clustered index example in the question) we have better options. For example we can trick the optimizer by using a SUBSTRING expression that always returns an empty string. SQL Server does not think that the SUBSTRING will change the number of distinct values so if we apply it to a unique column, such as PK, then the estimated number of distinct rows is 10 million. This following query gets the hash match (flow distinct) operator:

SELECT DISTINCT TOP 10 VAL + SUBSTRING(CAST(PK AS VARCHAR(10)), 11, 1)
FROM X_10_DISTINCT_CI
OPTION (MAXDOP 1);

On my machine this query scans 900000 rows from X_10_DISTINCT_CI and completes in 333 ms with 297 ms of CPU time and 3011 logical reads.

In summary, the query optimizer appears to assume that all rows will be read from the table for SELECT DISTINCT TOP N queries when N >= the number of estimated distinct rows from the table. The hash match (aggregate) operator may have the same cost as the hash match (flow distinct) operator but the optimizer always picks the aggregate operator. This can lead to unnecessary logical reads when enough distinct values are located near the start of the table scan. Two ways to trick the optimizer into using the hash match (flow distinct) operator are to lower the row goal using the OPTIMIZE FOR hint or to increase the estimated number of distinct rows using LAG() or SUBSTRING on a unique column.


There's really only a few things that I can think of that might help here, without actually changing the cursor code:

Make sure that your tempdb resides on different physical drives from your database files.  This won't make the temp/static rowset any smaller, but it will make it faster.
Adjust the CURSOR_THRESHOLD configuration setting:  This is a tricky thing and not normally recommended, however, it can have a big impact on this very situation (just not always a positive impact).  My guess would be that you'd want to adjust it from the default of -1 to something like 1000.  But play around with it, and be careful, as it's server instance-wide and can affect other things as well.
Plan Guides:  I think that you need 2008+ for this, and I am not sure if it works on cursor SELECT's.  They are also complicated to use, but if they do work, they can really do amazing things to a repeatable query that is using sub-optimal query plans.

That's all that I can think of ...


MySQL row-based-replication HA_ERR_KEY_NOT_FOUND... which record is it?

I think you should re-initialize your replication.

If the replication worked good, the rows should not be missing. That the rows are missing means there is some inconsistency between your master und your slave. I would not act on the assumption that this is the only error and if I'd fix it by hand everything is back fine again. Instead I would set up the replication from the start again and make sure it stays synced this time, to be sure to get rid of all inconsistencies.

Nevertheless there are some ways to identify which rows are missing. As a starting point you could take a look at this blogpost. This basically includes "decode the relay log" and "search for the current relay log position". One can decode the log for example with this statement:

mysqlbinlog mysql-relay-bin.000001 –base64-output=decode-rows -v > decoded.log


Why not use this query (sqlfiddle):

SELECT [MessageID]
    , [Body]
    , [Date]
    , [MessageParentID]
FROM Messages
WHERE MessageID NOT IN (
        SELECT MessageParentID FROM Messages
    )

Basicaly, if a MessageID is also a MessageParentID it means there is at least 1 reply. Therefore it only looks for message without replies (ie. without MessageParentID).

With this limited sample, it gives the correct output:

MessageID   Body                            Date                        MessageParentID
1           Its getting hot in hure         December, 27 2015 00:00:00  0
2           Where is all the chicken?       December, 28 2015 00:00:00  0
4           Sometimes its a little hotter   January, 05 2016 00:00:00   3

I don't unsderstand why:

are you using a GROUP BY with all the columns in SELECT. Do you need a DISTINCT?
are you using Max(M.Date) and anyway, it is also included in the group by
you are joining to MessageRecipients since anythin from the recipient list or count are not needed


Scriptable command line access to SQL-Server from linux

I found tsql and while its main purpose may lie elsewhere it covers my needs.

It is included in the EPEL package. I installed it with this:
rpm -Uvh http://ftp.df.lth.se/pub/fedora-epel/6/x86_64/epel-release-6-7.noarch.rpm

FreeTDS(May 14, 2011)                                    FreeTDS(May 14, 2011)

NAME
       tsql - utility to test FreeTDS connections and queries

SYNOPSIS
       tsql   { -S servername [-I interface] | -H hostname -p port }
              -U username [-P password] [-o options]

       tsql   -C

DESCRIPTION
       tsql  is  a diagnostic tool provided as part of FreeTDS. It uses the TDS protocol directly to con-
       nect to Sybase or Microsoft SQL Servers, and allows the user to issue queries that test the  capa-
       bilities of FreeTDS.

       tsql  is  *not* a replacement for a complete isql, such as sqsh (www.sqsh.org).  It is designed to
       rely on the lowest level FreeTDS library, tdslib, as a way to isolate potential bugs in the proto-
       col implementation.


In this query:

select *
from item
cross apply (
    select top 1 *
    from item_location_date
    where ild_item = itm_id
    order by ild_date_created desc
) ild

The correlated subquery is first filtered by ild_item and then ordered by ild_date_created to get the TOP 1. With this in mind, the index you need has to have ild_item first and ild_date_created second:

CREATE NONCLUSTERED INDEX NCI_item_location_date_item_date_created
    ON [dbo].[item_location_date] (ild_item, ild_date_created)

Do you really need to select all columns with *? If it's just a few, you could also INCLUDE these on the index so it becomes a covering index and reduces additional pages retrievals.

Your current clustered index is ([ild_date_created], [ild_item], [ild_location]), if you don't mind changing it to ([ild_item], [ild_date_created], [ild_location]) then it will be as fast as you could get, since it's ordered in the manner you need and being it clustered you won't require additional lookups to get the data. Make sure there aren't other queries that depend on the current order if you consider making this change.


When using MariaDB with tokudb plugin, syntax is

COMPRESSION=TOKUDB_LZMA and not ROW_FORMAT=TOKUDB_LZMA

From Mariadb website,

The DDL syntax is different. While binaries from Tokutek have the patched SQL parser, TokuDB in MariaDB uses the special Storage Engine API extension. Thus in Tokutek binaries you write CLUSTERED KEY (columns) and, for example, ROW_FORMAT=TOKUDB_LZMA. And in MariaDB you write KEY (columns) CLUSTERING=YES and COMPRESSION=TOKUDB_LZMA.

That page also provides more info on actually checking the compression formats.

mysql -sNe 'SELECT dictionary_name, internal_file_name FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"' |perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /var/lib/mysql/$F[1]); foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[0]."\t".$1."\n"; last } }'

Different version of the above with tables names to make it easier to get check specific table names.

mysql -sNe 'SELECT dictionary_name, internal_file_name, table_name, " " FROM information_schema.tokudb_file_map WHERE table_dictionary_name = "main" OR table_dictionary_name LIKE "key-%"'  | perl -F'\t' -ane '@out=qx(tokuftdump --nodata --header --rootnode /srv/mysql/data/$F[1]);  foreach $ftdump (@out) { if($ftdump=~/^ compression_method=([0-9]+)$/) { print $F[2]."\t".$F[0]."\t".$1."\n"; last } }' > /var/tmp/tables.compressiontype.lst

Will produce out put like

head /var/tmp/tables.compressiontype.lst
TableName   ./database/TableName-key-entity_index   11
TableName   ./database/TableName-main   11

The meaning of the various integers can be found in the github repo

    // compression methods
printf("typedef enum toku_compression_method {\n");
printf("    TOKU_NO_COMPRESSION = 0,\n");  // "identity" compression
printf("    TOKU_SNAPPY_METHOD  = 7,\n");  // google snappy
printf("    TOKU_ZLIB_METHOD    = 8,\n");  // RFC 1950 says use 8 for zlib.  It reserves 15 to allow more bytes.
printf("    TOKU_QUICKLZ_METHOD = 9,\n");  // We use 9 for QUICKLZ (the QLZ compression level is stored int he high-order nibble).  I couldn't find any standard for any other numbers, so I just use 9. -Bradley
printf("    TOKU_LZMA_METHOD    = 10,\n");  // We use 10 for LZMA.  (Note the compression level is stored in the high-order nibble).
printf("    TOKU_ZLIB_WITHOUT_CHECKSUM_METHOD = 11,\n"); // We wrap a zlib without checksumming compression technique in our own checksummed metadata.
printf("    TOKU_DEFAULT_COMPRESSION_METHOD = 1,\n");  // default is actually quicklz
printf("    TOKU_FAST_COMPRESSION_METHOD = 2,\n");  // friendlier names
printf("    TOKU_SMALL_COMPRESSION_METHOD = 3,\n");
printf("} TOKU_COMPRESSION_METHOD;\n");


DMZ server unable to connect through SQL Browser

Although what you described should work as configured, another option would be just create an alias for your named instance.


Logical Backup strategy in large Oracle Databases

With RMAN you can do point-in-time recovery (if properly) configured, so you could restore the database to the moment before the drop. Also, you can even restore just a single table from a backup. Other than that you have the recyclebin from which you can recover dropped tables and you have also Flashback Database. So to recover from a dropped table there are plenty of options - without an export.
In my career I have only once used daily exports as part of the backup strategy, but we stopped that once you could restore a single table from backup.
Regards,
Daniel


MySQL table with multiple values in one field

You can keep one table for property and one master table for roles and them assign that role to a user in the user table and can keep one to many association between property and users by having separate table property_users


SQL Server uses VLF to fragment the log file. This fragmentation of the log file makes log management easier for the engine, as it adds granularity to the file.

As some of the links that Aaron Bertrand has posted in the comment explain (I would like to add this one), SQL Server writes sequentially to the log file. It starts with the first VLF and, once it is full, continues with the next one and so on. Once all VLFs have been used, it starts over again from the first one. This mechanism requires the log file to free up space that is not being used anymore.

The log file keeps all active transactions in SIMPLE recovery model, and all transactions since the last log backup in FULL (or BULK_LOGGED) recovery model. Once a CHECKPOINT or a log backup happens, the transactions that have been either commited or rolled back are no longer active transactions, and the space in the log file they were using can be reused (log truncation).

SQL server will just truncate VLFs that do not contain any active transaction. It won't free up space in VLFs that contain any active transaction. This way, SQL can just keep track of which VLFs contain active transactions or not, and just make the ones without active transactions available for reuse. I guess -this should be better answered by Microsoft's developers and engineers- that this is far easier than having to check a full logical file, that could be physically fragmented, and having to know where exactly is the lower limit of the log in the file (MinLSN).

The number of VLFs in our log file is important. If we have too few, it is very unlikely that log truncation can happen, because VLFs will be so big that they usually will "host" an active transaction. Too may can lead us to log fragmentation. You can read this excellent blog post by Kimberly Tripp on the topic.


WITH CTE AS (
    SELECT CustomerName
        ,MobileNumber
        ,ROW_NUMBER() OVER (PARTITION BY MobileNumber ORDER BY CustomerName) AS rn
    FROM yourTable
)
UPDATE CTE
SET MobileNumber = NULL
WHERE rn > 1;

This query uses common table expression to group rows by MobileNumber and assign them numbers in chosen order. I chose order by CustomerName to get the same results as in a question description. After that it updates all rows with row number greater than 1 and set MobileNumber to NULL for them.


The /etc/hosts file was incorrectly configured, due to some changes to that file previously. It did not cause trouble immediately, but for some reason changing the file back to defaults (i.e., 127.0.0.1 for the local host and 127.0.1.1 for my machine) solved the problem.

This was discovered by running netstat -tulpn | grep :5432, which gave the following error output: sudo: unable to resolve host <my_machine>. This pointed to the fact that there was a problem with the /etc/hosts file.

For a helpful resource, see this link.


Two possible reasons seem to stick out,  ensure your server_id is different on both the master and the slave.  This is to be defined at startup in the cnf file.

Another option is you specified a very tiny max_binlog_size or max_relay_log_size, ensure the values are what you want and not off by an order of magnitude (eg. 512k instead of 512M)


SQL Availability Group and SSRS, SSIS and SSAS

I am thinking where to install my SSIS / SSRS / SSAS.

If it were me, and I was the one having to administrate the servers, I'd load these each on their own individual server with their own individual resources. Virtual servers are great for this, and if you aren't sure about the load then it is very easy to adjust resources.

When I see servers that have everything loaded on them and all running - I know there's going to be a problem, it's a matter of when and not if.

  If they don't have high availability requirement, then does it make sense to install them on node 2?

If you're into that sort of thing, then sure by all means. If you're limited by some other reason, then I would fight for more of whatever resource you are low on... disk, servers, memory, licensing, etc., as 15k for a new server is generally less money than 1,000 work hours wasted by everyone troubleshooting issues.

  The only draw back I see is the potential network congestion.

Here are a few more for the list:

None of the services know about each other
This can severely impact your FCI RTO
"Phantom" performance issues
Non-Scalable setup
Overly complicated troubleshooting (as a result of #3)

Don't get me wrong, in a pinch this can absolutely be done and sometimes when the servers are beefy enough and the load is small enough and not so important that a few hiccups here and there will be noticed then it can work.

For example, a runaway/poorly written/large data SSIS package is running but it's slow - what do you troubleshoot? Is there also a large parallel query taking up CPU in SQL Server, maybe the SSRS report is running a large report and rendering, at the same time SSAS is rebuilding a cube. How do you show that one is impacting the other?

With all of the services running on the same server, it's extremely hard to pinpoint individual items... it can be done, with the proper amount of monitoring and troubleshooting. However, as stated before, I'd rather have each isolated so that when this inevitably occurs, I can get to the root of the issue much faster and with greater accuracy.

  My understanding the active node will be the one taking most of the load so that's why I think to install the other stuff on the passive

I wouldn't continue thinking this way as you're setting yourself up for more issues. Instead, think of AGs and FCIs as "running anywhere, I don't really care" and your designs will be much more robust with less overall issues. Assuming that a single server will always hold a service on it in a WSFC is a bad assumption. Assume it will be on any of the possible owners at any given point in time.


How to plan bring down the space used by databases to as low as possible

I'll echo SqlWorldWide on the point about data types. The article they linked has a few good examples, but there are a lot more less-obvious areas to check too.
I've written a script sp_sizeoptimiser to help automatically identify a lot of them. Even if you don't use it, the areas it covers are worth looking into:
Data Types

Time based data types
Unspecified VARCHAR length
Mad VARCHAR Max
NVARCHAR data type
FLOAT and REAL data types
BIGINT as IDENTITY
NUMERIC or DECIMAL with 0 scale
Enum columns not implemented as foreign key

Architecture

Default fill factor
Number of indexes
Inefficient indexes
Sparse columns
Heap tables

Details for each of these is in the documentation linked above.
Preferably, I'd run this on a non-production copy of the database if possible, however it doesn't do any heavy lifting under the hood, so should be OK to run on production as well.


Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count?

Sure.  A query like this should do the trick:

with q as
(
  select top 100 *
  from …
  where …
)
select count(*)
from q


When to use multiple tables in DynamoDB?

Yes, it is legit to do what you are saying. Both are actually. There are some variables that you do not have in here and can help guide how the data model should be done.

What kind of scale are you looking to get to with this application and data model?
Of the application's access patterns, what is the ratio of reads between those patterns. Meaning which one is hit most over the others.
Of the access patterns you list, how many times per second are they performed?

For example, if 80% of all reads are to find the users on a project and that needs to happen 30,000/sec, but in your application not as many people will go that step further and find out the documents for the projects, then it is 20% of the overall reads and may only be 2000 reads/sec. That first one is the "hot path" of your application and should be optimized for.

Also think of it this way, with a non-relational database like DynamoDB, you can optimize for how your application uses and accesses the data and not like relational database where you have to worry a lot about how it is stored in the database.


How to store the result of multiple select queries into one csv file?

far as i know not possible if you want append one table after another to same file. But you can get them to same file using union

SQL:

select f.id as fid , null sid   from first f union select null fid , s.id sid from second s

that would create file where all data from first table are
1;null
2;null
....
and second table
null;1
null;2

or you can use psql command line tool in linux and call :

psql mydb -c "select id,name from first; " >> myOutFile.txt
psql mydb -c "select id,salary,father_name from second; " >> myOutFile.txt
psql mydb -c "select id,score,bonus,extra from third; " >> myOutFile.txt

In *nix, the > (greater than) character operator redirects output to a file, overwriting the file.

When doubled, the >> operator redirects output to a file appending the redirected output at the end.


Allow update access to only 2 tables in a database schema

Here is an example script

CREATE USER [UserName] FROM LOGIN [LoginName]

-- Grant SELECT on the schema
GRANT SELECT ON SCHEMA::SchemaName TO [UserName]
-- Grant UPDATE on the individual tables
GRANT UPDATE ON SchemaName.TableName1 TO [UserName]
GRANT UPDATE ON SchemaName.TableName2 TO [UserName]

And please remember that unless the user is some level of administrator, db_owner for example, any DENYs that you do will override any form of grant.  If you do this:

DENY UPDATE ON SchemaName.TableName3 TO [UserName]

Then nothing you can do will grant them update access until you REVOKE the DENY.
You could add the user to db_datareader to give them the SELECT permissions but that will give them SELECT permissions to all schemas not just one.  It just depends on what you are looking for.  That command is like this:

EXEC sp_addrolemember 'db_datareader','UserName'


Postgres PITR: Recovery does not seem to fetch from Archive Dir

The problem is that WAL files only get archived when they are full (or at the end of a PostgreSQL backup).

So it is likely that there was no WAL switch since you finished the backup, and the WAL archiver probably died before it could archive the final WAL file with the additional actions in it (check the original server's log file).

You can use the archive_timeout parameter to mitigate the problem, or you can use pg_receivewal to archive WAL in real time, so you don't lose transactions.

The error from your docker container indicates that the restore_command didn't work (check the log file).


How can I create such a query in MS access so that only customers with more than 3 bookings show up?

SELECT Customers.Customer_ID
FROM Customers
INNER JOIN Bookings
    ON Customers.Customer_ID = Bookings.Customer_ID
GROUP BY Customers.Customer_ID
HAVING COUNT(Customers.Customer_ID) > 3

Visually (some terms are approximate because I have no MS Access with English interface):

Add both tables to Query pane
Join them by proper fields
Select fields from Customers table which you want to output, do NOT add fields from Booking table
Press Totals knob on the ribbon
Add one more Customers.Customer_ID to the fields list
Change its Grouping function from Grouping by to Count
Set the criteria for this field to >3
Uncheck Show if you do not need in its value


unable to add data to table because related record is required

The answer is that I was adding the parameters in the wrong order. All of your parameters have to line up in order.


Use pretty much the same script in a CTE but add the row number (partition by month).
This will return all rows with a row number in front, reseting at 1 for every month. Then you can just issue your query with a "where rn <=5" and that will make it.

Here's an example just to give you a starting point:

create table #Demo (Rental_id int identity, rental_month int, inventoryId int, customer_id int)

insert into #Demo values (2,556,495),(2,236,495),(2,116,495),(2,556,495),(2,556,495),(2,116,495),(2,323,495),(2,56,495),(2,49,495),(2,49,495),
(2,116,495),(2,556,495),(2,453,495),(2,236,495),(2,34,495),(2,116,495),(2,556,495),(2,556,495),(2,24,495),(2,24,495)

insert into #Demo values (3,117,495),(3,236,495),(3,117,495),(3,236,495),(3,117,495),(3,116,495),(3,117,495),(3,236,495),(3,117,495),(3,236,495),
(3,116,495),(3,556,495),(3,453,495),(3,236,495),(3,34,495),(3,117,495),(3,556,495),(3,556,495),(3,117,495),(3,24,495),(3,34,495)

go

with cte as (
select rental_month, inventoryId, COUNT(inventoryId) "no_of_rental", ROW_NUMBER() over (PARTITION by rental_month order by count(inventoryId)) "RN"
from #Demo
group by rental_month, inventoryId
)
select * from cte where RN <6;

drop table #Demo


SQL Server Agent fails to start in Failover cluter

There are logs in a few places. For the Agent logs please see the screenshot:

In Object Explorer expand SQL Server Agent \ Error Logs.


Assigning blank values on joins_query design

If the aim of the query is to join rows from the tables that match the "land_use" as well, then you need to include those columns in the join condition:

SELECT ...
FROM dbo_AIBLVIC_LUM_PORTION_500 AS alp500
    LEFT JOIN dbo_AIBLVIC_LUM_PORTION_1600 AS alp1600
        ON  alp500.AIBL = alp1600.AIBL
        AND alp500.LAND_USE_500 = alp1600.LAND_USE_1600 ;     -- added condition


SQL Server - granting permissions to an entire schema vs. object?

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

Grant Schema Permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: <schema> TO <user>;

Further to that, if you want to then deny permissions on a certain object within that schema, you can do.

Denying Object Permissions

DENY INSERT ON OBJECT::<schema>.<object> TO <user>;


oracle active sessions waiting for scheduler

They are Oracle Resource Manager waits. Resource Manager is a set of Oracle functionality that lets you prioritise work.

See the list of possible waits in the documentation.


fill table A with random ID's from another table B

You can assign two ROW_NUMBERs based on a random sort and join on them:

select
   t.id,
   g.id as gebruikerid
from
 (
   select id,
      (row_number() over (order by newid())
    % (select count(*) from gebruiker))+1 as rn -- number from 1 to n
   from topic
 ) as t
join
 ( select id,
     row_number() over (order by newid()) as rn -- sequential number from 1 to n
   from gebruiker
 ) as g
  on t.rn = g.rn
order by t.id

See fiddle

EDIT:

You can use the result of this SELECT to update the Topic table:

update t
set gebruikerid = dt.gebruikerid
from topic as t
join
 (
   select
      t.id,
      g.id as gebruikerid
   from
    (
      select id,
         (row_number() over (order by newid()) % (select count(*) from gebruiker))+1 as rn
      from topic
    ) as t
   join
    ( select id,
         row_number() over (order by newid()) as rn
      from gebruiker
    ) as g
   on t.rn = g.rn
 ) as dt
on t.id = dt.id;

fiddle


Advice on table processing for invoice-allocation-payment structure for invoices of zero amount

In the end I went with option 1. That way it copes with people paying unnecessarily as there is an invoice against which to place the payment. It also allows for refunds to be made against zero amount invoices if someone pays unnecessarily but then wants a refund. The payment / refund can be put in the cash_payment table and allocated to the invoice through the payment_allocation table just like any other invoice.


How to get the function name of a regprocedure in PostgreSQL?

You have

ERROR:  syntax error at or near "variadic"

From the docs,

  VARIADIC parameters are input parameters, but are treated specially as described next.

That means you don't do anything special for VARIADIC parameters beyond what you would otherwise specify for input params. In your case, just drop that token,

SELECT get_funcname('my_func(text, text[])');

  Can anyone help explain how to fix the function or syntax?

Sure.

WHERE p.oid = regexp_replace(_fn, ' VARIADIC ', ' ', 'i');


Federated table of two LEFT JOIN tables not working

To define a FEDERATED table the field definition on local table much match field types on remote table. You are creating a brand new table by joining existing tables. I don't think this is legal.

  A local server with a database table, where the table definition
  matches that of the corresponding table on the remote server. The
  table definition is stored within the .frm file. However, there is no
  data file on the local server. Instead, the table definition includes
  a connection string that points to the remote table.

http://dev.mysql.com/doc/refman/5.5/en/federated-description.html


PSQL trying to connect to wrong database

This is expected behaviour.

The man page for psql says (emphasis mine):

  -l

  --list

  List all available databases, then exit. Other non-connection options are ignored. This is similar to the meta-command \list.

  When this option is used, psql will connect to the database postgres,
  unless a different database is named on the command line (option -d or
  non-option argument, possibly via a service entry, but not via an
  environment variable).


Greatest-n-per-group in a one-to-many relationship

I managed to come up with a query that suits my need. Probably not the most efficient one, but the table is not too big...

select c.CurrencyID, c.MarketName, t.TickTime, t.Last, t.Volume
from (select t.*,
         (@rn := if(@v = CurrencyID, @rn + 1,
                    if(@v := CurrencyID, 1, 1)
                   )
         ) as rn
  from Ticker t cross join
       (select @v := -1, @rn := 0) params
  order by CurrencyID, TickTime desc
 ) as t
INNER JOIN Currency c
    on c.CurrencyID=t.CurrencyID
where
    rn <= 3;


Web Edition is not available to download at will.

See here.  Scroll half way down to 'SQL Server Price' section.


The column max_length is the maximum column length in bytes.  For the National-Character set string types (NCHAR and NVARCHAR), each character requires two-bytes, so NCHAR(10) would have a max_length value of 20.


Prevent reverse engineering of encrypted views

Bad news: check out dbForge SQL Decryptor. Anyone can decrypt your encrypted objects.


How to move from 32bit SQL Server install to 64bit?

As stated in BOL here:

  Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit using SQL Server Setup. However, you can back up or detach databases from a 32-bit instance of SQL Server, and then restore or attach them to a new instance of SQL Server (64-bit) if the databases are not published in replication. You must re-create any logins and other user objects in master, msdb, and model system databases.

If you have installed a default instance of SQL Server and wish to keep it that way your only option is to remove the current install and install using the 64-bit media. Otherwise you can install a 64-bit instance as a named instance on the same server.

Cindy Gross has some additional useful information here.


The widely used tool is the SQL command EXPLAIN ANALYZE, possibly with more options for more details in the answer. That outputs the query plan with the planner estimates plus actual execution times.

Why would you want to clear the cache? The generally more likely use case is that the cache is populated. If you still want to go that route, here is a related answer on SO.

Not resetting the cache, here are two simple ways to test with many iterations:

Simple UDF

EXPLAIN ANALYZE
SELECT f_myfunc(g) FROM generate_series (1,1000) AS t(g);

Or with random input - random numbers between 0 and 5000 in the example:

EXPLAIN ANALYZE
SELECT f_myfunc((random()*5000)::int) FROM generate_series (1,1000) AS t(g);

Or with a real life table:

EXPLAIN ANALYZE
SELECT f_myfunc(my_column) FROM my_tbl;   -- LIMIT n

More complex functions / queries

CREATE FUNCTION f_test(ct int, sql text) RETURNS void AS
$func$
DECLARE
   i int;
BEGIN

FOR i IN 1 .. $1 LOOP
    EXECUTE sql;  -- not safe against SQLi!
END LOOP;

END
$func$ LANGUAGE plpgsql

Call:

EXPLAIN ANALYZE
SELECT f_test(100, $x$SELECT * from MADLIB.gp('mock3', '{x1, x2, x3}', '{y1}', 100,20, 3)$x$

Careful: The query is actually executed!
Careful: Not fit for public use. Possible SQL injection.

Again, you can use random parameters if needed. Possibly with the USING clause of EXECUTE.


Will a row update check all child tables which have foreign key relationships in InnoDB?

ON DELETE CASCADE: When you delete rows in the master table referenced by other tables, all those child tables will also delete their rows that reference the deleted master data.
ON UPDATE NO ACTION: If you try to update a field in the master table referenced by others, MySQL will prevent you from doing that. NO ACTION and RESTRICT are synonyms in MySQL.

So to answer your updated question, MySQL will prevent you from updating a field in the master table that is referenced by any child tables because you specified NO ACTION.  If you are updating any other field in the master table it will take the same amount of time as an update on a table without foreign keys.


The URL to manage the reports is different than the URL to execute the reports (the RDLs files)

You can check it in the Report Configuration.

Regards,
Martín G


Your code is very, very close to working.

It's not explicitly clear from the docs but the examples do show this.

When using EXEC (or EXECUTE) with a stored procedure, you write the object name straight after the statement (example 1 below), however when calling a T-SQL string you must include brackets (example 2 below)

Example 1

EXEC dbo.Procedure

Example 2

EXEC ('SELECT * FROM dbo.SomeTable')

As such, your solution should look like this (Note the replacement of the static date with GETDATE() to satisfy your original condition of dropping based on the age of the database):

--For internal objects, always use NVARCHAR - this is what the engine uses internally in most places
DECLARE @DBName NVARCHAR (64)
DECLARE @SQL NVARCHAR (255)
DECLARE DROPDB CURSOR FOR
SELECT name FROM sys.databases
--Use GETDATE() with DATEADD for -60 days to get the threshold for deletion
WHERE create_date  <= DATEADD(DD,-60,GETDATE())
AND name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')

OPEN DROPDB
FETCH next FROM DROPDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP DATABASE ' + QUOTENAME(@DBName)
            PRINT @SQL
    EXEC (@SQL)
    FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB

As an aside, given that this is such a potentially damaging script, I'd drop the EXEC and simply copy the printed statements into a new query to run them. This would avoid any unforeseen accidents as it gives you an opportunity to audit the script before you run it.


Is the official term "template database" or "maintenance database"?

You must be an old-timer. The postgres database has been around since version 8.1.
There are three notions:

A template database is a database whose purpose is to be used as a template in CREATE DATABASE: the template is essentially copied.
While in principle any database can be used as template, template databases typically have datistemplate set to TRUE.
Initially PostgreSQL comes with two templates: template1 (which you can modify if you want) and template0 (which remains unmodified).

A default database called postgres. The purpose of this database is for administrative connections, typically to execute the CREATE DATABASE SQL statement.
Before 8.1, when this database was introduced, template1 did double duty for that purpose. The 8.1 release notes describe that well:

In prior releases, template1 was used both as a default connection for utilities like createuser, and as a template for new databases. This caused CREATE DATABASE to sometimes fail, because a new database cannot be created if anyone else is in the template database. With this change, the default connection database is now postgres, meaning it is much less likely someone will be using template1 during CREATE DATABASE.

The maintenance database is a term unknown in PostgreSQL, it exists exclusively in pgAdmin and is a frequent source of confusion.
In that text field you enter the name of the database to which you want to connect. Just forget the “maintenance” part.
The designers of pgAdmin probably intended people to enter postgres there, assuming that the tool is used by a DBA who has access to that database. Then you can click on other databases of the same cluster, and pgAdmin will open an additional database connection to those databases.


Migrate MySQL to another partition in same server

For linux (as root user)

Stop mysql service through systemctl
rsync or cp -a the whole of /var/lib/mysql to your preferred destination.
mv /var/lib/mysql /var/lib/mysql-old
edit mysql configuration file and change datadir= under mysqld
Start mysql service through systemctl.


Performance difference between single large Application instance vs multiple small Application Instance in Netweaver

Let's consider a situation when you need to build a server with SQL Server used by multiple client applications. Here are two options to consider.

Single instance that host ALL the databases
Multiple instances with each having subset of the databases.

We`ve created a table with comparison of these two options.

Comparison of single instance to multiple instances SQL Server installation

Single instance is Better for

Memory usage
TempDB disk usage
CPU usage
IO
Ease of administration
Overall performance
Cost of licensing
Monitoring

Multiple instances is Better for

Separate Security between
databases and applications
SLA differences
Multiple test and dev environments

Conclusion
From the table above we can see that having multiple instances of SQL Server on a single server could be beneficial only in a specific cases when you need a great degree of isolation for the environment, otherwise consider using single instance installation.


Quoting from docs, these are the same:

  If you specify this clause [i.e. LOCAL (...)], then the number of
  PARTITION clauses must be equal to the number of table partitions, and
  in the same order. If you omit partition, then Oracle Database
  generates a name that is consistent with the corresponding table
  partition.


REORGANIZE seems to work as well as REBUILD all the time

Let me quote another part of that same page, which basically says IT DEPENDS - emphasis mine:

  These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment.

There's not really a good answer for this except "it depends"... those are just general guidelines and may or may not be appropriate depending on the number of rows, the columns in your index, their data types, how wide the index is, what else is getting written to disk in between writes to those tables, etc.

Like how big should I size tempdb, what is a good threshold for PLE, and what type of car should I buy, the answer is the same: IT DEPENDS.


Will adding more indexes speed up using RedGate SQL DATA compare?

The question suggests that the tool is being used to migrate data and not schema, which implies that you are referring to SQL Data Compare and not SQL Compare.

In which case, yes, indexes on the comparison key columns will speed on SQL Data Compare.

I've consulted the SQL Data Compare project manager, who provides the following insight: "Usually performance problems with SDC have more to do with the network connection speed to the server and/or the disk speed on the location of the temp files, neither of which are affected by having an index on the comparison key"

SQL Data Compare performance tips are part of the SQL Data Compare documentation.


Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX)

You need to specify a SqlFacet for the return value. This is the same as specifying a SqlFacet for an input parameter, except you prefix it with return:

For (max) length, use MaxSize=-1 in the SqlFacet attribute, example follows:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        SystemDataAccess=SystemDataAccessKind.None,
        IsDeterministic=true,
        IsPrecise=true
        )
    ]
    [return: SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=-1)]
    public static SqlString ScalarFunction1
        (
            [SqlFacet(IsFixedLength=false, IsNullable=true, MaxSize=-1)]
            SqlString input
        )
    {
        return input;
    }
}

The T-SQL script generated for this SQLCLR function is:

CREATE FUNCTION [dbo].[ScalarFunction1]
(@input NVARCHAR (MAX))
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [Library].[UserDefinedFunctions].[ScalarFunction1]


mysql - How to remove duplicate rows in parent table and leave one row with the lowest ID, and replace foreign key in child's table with this ID?

DROP TEMPORARY TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable
(id int
,minId int
PRIMARY KEY (id)
);

-- filling temporary table with list of duplicate ids
-- and corresponding minimal id within each group of duplicates
WITH CTE AS (
SELECT id
    ,MIN(id) OVER (PARTITION BY modern_source_id, location_within_source ) AS minId
    ,ROW_NUMBER OVER (PARTITION BY modern_source_id, location_within_source ORDER BY id) AS rn
FROM junc_modern_source_has_reference
)
INSERT INTO tempTable (id, minId)
SELECT id, minId FROM CTE WHERE rn > 1;

UPDATE hgn_picture_has_reference AS t1
JOIN tempTable AS t2 ON t1.id = t2.id
SET t1.modern_reference_id  = t2.minId;

DELETE t1
FROM junc_modern_source_has_reference AS t1
JOIN tempTable AS t2 ON t1.id = t2.id;

DROP TEMPORARY TABLE tempTable;

If you use MySQL version older than 8.0 you can't use window functions and you need to modify part with CTE this way:

INSERT INTO tempTable (id, minId)
SELECT id, minId
FROM
    (SELECT t1.id
            ,t2.minId
    FROM junc_modern_source_has_reference AS t1
    JOIN (
        SELECT MIN(id) AS minId
            ,modern_source_id
            ,location_within_source
        FROM junc_modern_source_has_reference
        GROUP BY modern_source_id, location_within_source
        HAVING COUNT(1) > 1
        ) AS t2 ON t1.modern_source_id = t2.modern_source_id
                   AND t1.location_within_source = t2.location_within_source
                   AND t1.id <> t2.minId
    ) AS CTE


If you have multiple data files for a database and you are trying to move one from one disk to another, I believe you can add new data file on the new drive to your filegroup, and then use ShrinkFile with the EmptyFile option on the file to be moved to migrate data from that file into the other files in the filegroup (SQL Server will distribute across the files automatically).  The only problem with this is that you cannot do this for the first file of the database's primary filegroup, which I'm betting will be an issue for you.


Postgres not being able to create a tablespace

I just found a solution. I changed the permission of /mnt/DB/postgres to 700:

postgres@vostro /mnt/DB $ ls -l
total 4
drwx------ 3 postgres postgres 4096 May 25 17:46 postgres

Now it worked the creation of the ts

postgres@vostro /mnt/DB/postgres $ ls -l
total 4
drwx------ 2 postgres postgres 4096 May 25 17:46 PG_9.5_201510051

Community Wiki answer created from a comment left by the question author.
 Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


@Wozniak form what i can see you need the sql syntax t build this structure ! to do so use the syntax belew :

create table Activity_Types(
activity_type_id in,
activity_name varchar(20),
PRIMARY KEY (activity_type_id)
);

create table Activity_Log(
activity_id int,
user_id id,
activity_type_fk id,
activity_date date,
PRIMARY KEY (activity_id),
FOREIGN KEY (activity_type_fk) REFERENCES Activity_Types(activity_type_id)
);

Where foreign key that makes the relation is hold on the Activiy_log table under the activity_type_fk column.
 to join this tables just use this columns !


Getting to know SQL via the "short questions/simple answers style of learning"

Have a look at:

http://sqlzoo.net/

This seems to be what you are looking for.


How to take backup of a database in mysql?

Can you try grant permission on whole database like,

GRANT ALL PRIVILEGES ON db.* TO 'username'@'%' IDENTIFIED BY 'yourpass' WITH GRANT OPTION;


2008R2 Cannot Disable Non-Clustered Index

You are relying on the graphical tree icon in SSMS to indicate that an index is disabled. In my experience, this has never been true for indexes (though it is true for certain other things, like jobs and triggers).

To address Paul's comment:

  No, I don't know why they changed this behaviour.

I don't know that they have changed it in the way the asker suggests:

  I swear it used to show a little down arrow on the index icon in the tree.

Unless we're going back to 2005 days (I have 2005 instances, but no 2005 Management Studio handy to test). In one way, they've actually improved the behavior; in another, they've made it worse. Let me explain.

In the following screen shot, 2012/2014 is on the left, 2008/2008 R2 is on the right. There is even less visual cue in the older UI about the index being disabled. Notice that the index name in the tree has the same visual and iconic representation in both versions, even though the index is disabled (click to enlarge), and only 2012/2014 has the warning that Paul mentioned:

So, as I said, I think the visualization has actually improved, even if it is less than perfect - I do tend to agree that there should be a red down arrow or something similar to be consistent with other elements in the UI (have you filed a Connect item yet?).

Now. how has it gotten worse in the newer versions of SSMS? Well, I can't find a way to re-enable the index in the UI. In 2008/2008 R2, there was a "Use index" checkbox on the Options tab, which works - though I'm not sure how intuitive that is. There's also a checkbox for "Rebuild index" which seems to be more appropriate for a button, since it's not an option or a property, but I suppose that is a different discussion (click to enlarge):

To verify the index is really disabled, I would not use the UI at all, but rather the catalog views:

SELECT i.name, i.is_disabled
  FROM sys.indexes AS i
  INNER JOIN sys.objects AS o
  ON i.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo' -- or another schema
    AND o.name = N'the table the index belongs to'
    AND i.name = N'the index you disabled';

You can leave out the last AND if you want to see all the indexes for a table. If you want to see all disabled indexes in your database, small change:

SELECT s.name + '.' + o.name, i.name
  FROM sys.indexes AS i
  INNER JOIN sys.objects AS o
  ON i.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE i.is_disabled = 1;

You can also just right-click the index in the tree, Script Index As > Create To > New Query Editor Window, and you should see that - for an index that you have disabled - the index includes a DISABLE script at the end, e.g.:

USE [yourdb]
GO

CREATE INDEX ...
GO

ALTER INDEX [x] ON [dbo].[y] DISABLE
GO


MYSQL refuses to set password for new user

I am going to take a very wild guess on this one
My guess is that you upgraded to MySQL 5.7 but did not upgrade the the mysql schema.
I wrote about the mysql.user table having a different number of columns per version:

Oct 10, 2014 : MySQL service stops after trying to grant privileges to a user
Aug 07, 2013 : MySQL - ERROR 1045 (28000): Access denied for user : permission issue
May 24, 2013 : How often should MySQL (stock, Percona, etc.) be upgraded?
May 01, 2013 : How often should MySQL (stock, Percona, etc.) be upgraded?
Apr 12, 2012 : Cannot GRANT privileges as root (I actually show the differences in the column layouts in MySQL 5.6, 5.5, 5.1, 5.0, 4.x)

Now for the shocker
Did you know the following about mysql.user in MySQL 5.7 ?

There are 45 columns
There is no longer any column named password
The column authentication_string stores the password now
I mentioned this Oct 28, 2015 : Install MySQL for Windows from .zip and reset root password

At this point, you are probably asking : Why in the world did create user 'testing'@'localhost' identified by 'password'; work ?
Look in my Apr 12, 2012 post. Please note the the column layout for mysql.user. The column authentication_string column appears in MySQL 5.6 and MySQL 5.5.
Please run this query
select Host, User, password_expired,Password,authentication_string from mysql.user;

You should see the encrypted password in the authentication_string column
What makes me believe this to be the case was the clue you provided
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

The command SHOW GRANTS; no longer shows the encrypted password in MySQL 5.7. I ran in to this issue even with pt-show-grants.
What should you do ?
Perhaps you should run mysqld --upgrade to fix the column layout. It looks like this in 5.7:
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

mysql>


Max from 2 columns with priority date col then the seq col Oracle

You don't mention DBMS, so I'll assume it is one that supports window functions (DB2, Oracle, SQL-server, Postgres, etc)

select personid, xdate, seqnumber
from (
     select personid, xdate, seqnumber
          , row_number() over (partition by personid
                               order by xdate desc, seqnumber desc) as rn
     from T
) as X
where rn = 1;

Edit: added sample data and result (http://sqlfiddle.com/#!4/73ad2/4)

create table T
(   personid   int
,  xdate      date
,  seqnumber  int );

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

insert into t values (123, '2012-01-01', 0);
insert into t values (123, '2011-01-01', 0);
insert into t values (136, '2012-01-02', 1);
insert into t values (136, '2012-01-02', 0);
insert into t values (136, '2012-01-01', 0);
insert into t values (168, '2012-01-04', 2);
insert into t values (168, '2012-01-04', 1);
insert into t values (168, '2012-01-04', 0);

select personid, xdate, seqnumber
from (
    select personid, xdate, seqnumber
         , row_number() over (partition by personid
                              order by xdate desc, seqnumber desc) as rn
    from T
) x
where rn = 1;

PERSONID    XDATE   SEQNUMBER
123     January, 01 2012 00:00:00+0000  0
136     January, 02 2012 00:00:00+0000  1
168     January, 04 2012 00:00:00+0000  2


Couldn't restore database and couldn't change Single user mode to Multi user mode

Here is what I do to ensure that I get the ONE (1) connection for SINGLE_USER.

USE MyDB;  -- Use the database you want to make SINGLE_USER
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE ----; /* Or whatever you need to do. */
GO
ALTER DATABASE MyDB SET MULTI_USER;
GO

The trick is to be in the database already when you switch to SINGLE_USER mode.  This way, you are the SINGLE_USER and nobody else can get in until you return the database to MULTI_USER.

NOTE: If you exit the database while in SINGLE_USER mode someone else can take over the database.  This can also cause you problems.


I think the problem is the WHERE. If no user was joined, no e-mail address can match and you get the empty set. Try to CROSS JOIN the user in question and then LEFT JOIN the addresses and transactions.

SELECT c.id,
       coalesce(sum(t.amount), 0)
       FROM coins c
            CROSS JOIN (SELECT u.id
                               FROM users u
                               WHERE u.email = 'matt@test.net') u
            LEFT JOIN addresses a
                      ON a.coin_id = c.id
            LEFT JOIN transactions t
                      ON t.address = a.address
                         AND t.user_id = u.id
            GROUP BY c.id;


Setting Select Permission on 100+ tables in a database

Use GRANT statement with tables delimited by comma like below.

Grant select on tbl_1, tbl2, tbl3, tbl4, ...., tb1100 to [MyRole];


Unique filtered index and surprising estimation

the Estimated Number of Rows property for [table1] shows 10. Why is
  this? In the schema (copied below) I have a unique index
  AKF_tabela1_Kod. Shouldn't this unique index force the row estimation
  to be 1?

The statistics are created when creating the unique index, but as a result of the unique index being filtered, the statistics are also filtered.
(where kod is not null).

As a result, the query as it is cannot use these statistics.

Why?

The Index can be used, but the statistics are not a match for the query.

Even if you do a select on kod:

 select kod from dbo.tabela1 t
     where kod is not null  option(recompile)

The statistics are still no match:

If you do a select of the id however:

 select id from dbo.tabela1 t
 where kod is not null  option(recompile)

There is a match:

This seems to be buggy with auto create stats:

ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

This does not create stats for some reason, but is still able to do the correct estimation.

No new stats:

Re running this:

 ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

Gives the same warning:

This seems to be an issue with auto create statistics being off, even though no stats are created.

I am not sure, but an estimate of 10 might be a 'default' value to use, like when using a table variable.

Workarounds:

1

If you create the next index, the result will be 1 estimated rows

CREATE UNIQUE NONCLUSTERED INDEX [AKF_tabela1_Kod2]
    ON dbo.[tabela1]([Kod] ASC)
    INCLUDE ([id])
    WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, MAXDOP = 0);
GO

2

Another route you could take is creating your own statistics:

 create statistics ST_Tablea1_Kod on dbo.tabela1(kod)

Retry the query (with a new execution plan):

SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on  t.Kod = x.Kod option(recompile)

3

Or ofcourse, enable auto create statistics and rerun the query:

ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

Update:

The below queries give a stat missing on the first execution, but not on the other two. A statistic was created by auto create stats.

     SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod option(recompile)
      ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;
         SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod option(recompile)
           ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

             SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod option(recompile)

Dropping the stats and retrying with a select:

     drop statistics  dbo.tabela1.[_WA_Sys_00000003_0EA330E9]

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

Does not generate stats, and gives the same missing index hints on the first and third execution:

I thought that this maybe was because of the optimization level being trivial. But this was not the case.

StatementOptmLevel="TRIVIAL"

Non trivial optimization level:

 select kod from dbo.tabela1 t
 where kod is not null
 group by kod  option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

 select kod from dbo.tabela1 t
 where kod is not null     group by kod   option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

 select kod from dbo.tabela1 t
 where kod is not null    group by kod   option(recompile)

Result:

Query #2 with automatic stat creation:

(I added 100 rows for a total of 200)

Query #3 , after putting automatic stat creation off again:

(180 estimated rows)

As a conclusion, this should have something to do with automatic stat creation being disabled.
My guess is on some sort of temporary stats or the way the plan is created.
I checked if i could find any 'temp'stats that are created at runtime,
but i did not find anything.
    (select * from sys.stats where is_temporary = 1)


I will call creationtimestamp simply ts...

SELECT  FLOOR(UNIX_TIMESTAMP(ts)/3600) AS start_of_hour,
        COUNT(*)
    FROM session ...
    GROUP BY FLOOR(UNIX_TIMESTAMP(ts)/3600);

If you need to "pivot" the output into 24 columns, then generate the code from http://mysql.rjweb.org/doc.php/pivot .


Is reading a single record always an atomic operation?

In an ACID compliant database it is atomic


Using MySQL EXPLAIN to analyze the performance of a function

EXPLAIN was meant to do queries, not functions or procedures.

When it comes to procedural code, you must think like a developer all over again. For what purposes would you optimize code, especially for a database ?

Streamline Code Execution

Use SQL for all rows
Forcing SQL behavior over the Query Optimizer

Reduce Client/Server Traffic

Minimize Responses
Triggers

Divide/Conquer Complex Operations

Optimize Correlated Queries
Avoid Self Joins
Remove Unnecessary Statements from Loops
Use Controlled Break Statements (LEAVE or CONTINUE)
Good IF/CASE statements
Recursion
Cursors

It is no longer a case of EXPLAIN a query. If you can EXPLAIN the procedure, you can code. Obviously, if you can EXPLAIN a procedure faster, you can produce faster code.


One vs. Many Tables for Frequently but Asynchronously Updated Columns?

You should use one table and views for Current and History data.

Reasons:

This is easier to update (just add a new data row with a newer timestamp)
easier to maintain on tables changes
does not need move logic which can fail
the views can easily be changed to view full history (with current data) without table joins


How to set user-level security in 2007+ MS Access?

As I was stuck in not seeing "User-Level Security Wizard", I made my way to access it by including this item to the custom ribbon tab:

After that I was able to continue and create users for the DB.


Retrieving data from inner join using LIMIT and OFFSET

Use a subquery (as displayed) or CTE for that purpose:

SELECT *
FROM  (
   SELECT qid, gid
   FROM   table1
   ORDER  BY date DESC
   LIMIT  10
   OFFSET ?
   ) q
JOIN   table2 a USING (qid, gid)

USING (qid, gid) is just a shortcut for ON q.qid = a.qid AND q.gid = a.gid with the side effect that the two columns are only included once in the result.


MySQL unique index only if column has specific value

I had a similar question, and have a way to make it work, so if you don't mind the kludge, here it is:

Since MySQL allows multiple NULL values on a unique index, you can use NULL to indicate "deleted" and non-NULL value (42?) to indicated "not deleted".

You would then create a composite unique index on [slug, deleted].

The table would look like this:

slug   deleted
abcd   42    # This record is marked as not-deleted
abcd   NULL  # This record is marked as deleted
abcd   NULL  # This record is marked as deleted
xyz    42
xyz    NULL

If using non-NULL to signify "not deleted" seems a bit iffy, so if you can, you could reverse the values and change the column name to "available" or something? :

slug   available
abcd   42    # This record is marked as available
abcd   NULL  # This record is marked as not-available
abcd   NULL  # This record is marked as not-available
xyz    42
xyz    NULL

Hope that helps


execution sequence of parallel execution plan

According to the quoted text Oracle will only have two parallel active per data flow operation. Take a look at the TQ column of the EXPLAIN PLAN. There is only one data flow operation for this query (Q1). There are five different parallel slave sets for Q1:

Q1,00
Q1,01
Q1,02
Q1,03
Q1,04

Q1,00 and Q1,01 will be processed first. The operation on line 7 (FTS on T3) has a slave set of Q1,02 so it can only start after Q1,00 or Q1,01 finish and free up a slave set.


Installing and configuring postgresql 9.3 on ubuntu 13.10 using puppet

Dunno if it was true when you wrote your question, but the puppetlabs/postgresql module does some pretty clever stuff with versions that are not standard for your OS distro, including installing the required repository.

Snippets from my config:

In one of my puppet classes I call:

    class { 'postgresql::globals':
      encoding => hiera('postgresql::globals::encoding', 'UTF8'),
      locale => hiera('postgresql::globals::locale', 'C'),
      manage_package_repo => hiera('postgresql::globals::manage_package_repo', false),
      version => hiera('postgresql::globals::version', undef),
      notify => Class['postgresql::server', 'postgresql::client'],
    }

and in my hiera config data I have:

postgresql::globals::version: "9.3"
postgresql::globals::manage_package_repo: true

Using hiera like this is optional of course, but it gives you a nice way to use different versions on different hosts.  On later versions of puppet (at least 3.x) you can override postgresql::globals variables without needing that to be part of how you call the package.

You won't need to do stuff from source here, but if you did, I'd recommend that you should build your own package rather than try to automate the build process through puppet.  Avoid it as far as possible though, since it sets you up to do ongoing maintenance of that package.


Resolving MySQL Process ID to page that ran it?

There are three(3) ways to find out info on queries and its process ID

Way #1 : Activate the General Log as a Text File

If you add the following lines to my.cnf and restart mysql

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log

You can parse /var/log/mysql_general.log text file. The header of each query should have the process ID (Thread ID) that the query ran under

Way #2 : Activate the General Log as a MyISAM table

There is a table called mysql.general_log. It is a CSV file by default. Run the following commands:

ALTER TABLE mysql.general_log ENGINE=MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

then add the following to my.cnf

[mysqld]
general-log
log-output=FILE

Once you have the general log entries stored as a table rows, you can run SELECTS against the MyISAM at will, with queries like:

SELECT * FROM mysql.general_log
WHERE event_time >= (DATE(NOW())+INTERVAL 0 SECOND)
AND event_time <= NOW();
AND argument like '...';

Way #3 : Check the Process List

If you want to catch the queries in the act of running long, do the following (if you have MySQL 5.1) :

SELECT id,info FROM information_schema.processlist
WHERE command = 'Query' AND user <> 'system user';

This shows you every query currently running. You can parse the info column for whatever query you are tracking down


MySQL 5.6 Slave Stops Processing Binlog on Master Restart

Guess what ? You have a nasty little bug  (Replication stall with multi-threaded replication)

This bug was first reported in MySQL 5.6.17 on 20 Jun 2014 15:45

SUGGESTION #1

You need to look over all the release notes from MySQL 5.6.18 up to MySQL 5.6.22 to see if it was resolved. If any of those release notes claim the problem was fixed, then someone missed a patch. I do advise that you do not regress to anything before MySQL 5.6.21 (Any known issues upgrading from MySQL 5.1.73 to 5.6.21? under ISSUE #3 : Security Issues)

SUGGESTION #2

Don't use multithreaded replication until this bug is fixed.

SUGGESTION #3

In the aforementioned bug report, it says this somewhere in the middle

  When MySQL comes back up again, the log is rotated, and IO thread starts writing from the event group which was partially written.

  .
  .
  .

  You can see that the "end_log_pos" values are the same for the partial event I showed earlier and this one. It is the same UPDATE transaction that was partially written.

  Now comes the interesting part. When the coordinator reads the relay log, it sends the partial event to the worker, since the event is partial hence the worker never commits the transaction and the transaction is kept open. The coordinator reads the next event (which is the full version of the partial event) but the coordinator cannot assign the next event to another worker because of one of the workers having an open transaction.

  And apparently, MTS waits for workers to commit transactions when it sees log rotated.

I would suggest not using --flush-logs with mysqldump, just in case there are some issues with group commits that are all-of-a-sudden split by the mysqldump and this bug isn't viewing incoming binlog events correctly.


How to Get Median Value From Row in SQL Server 2008 R2 Without Using Pivot and Unpivot Table

PIVOT and UNPIVOT are indeed not supported under compatibility level 80.

However, you can unpivot rows using a nested VALUES constructor. The resulting query in my case looks slightly unwieldy because of the double nesting, but it works in SQL Server 2008 with any supported compatibility level:

SELECT
  id,
  median =
  (
    SELECT
      AVG(val)
    FROM
      (
        SELECT
          c   = COUNT(*) OVER (),
          rn  = ROW_NUMBER() OVER (ORDER BY v.val ASC),
          val = v.val
        FROM
          (
            VALUES
            (t.a), (t.b), (t.c), (t.d), (t.e), (t.f), (t.g),
            (t.h), (t.i), (t.j), (t.k), (t.l), (t.m), (t.n),
            (t.o), (t.p), (t.q), (t.r), (t.s), (t.t), (t.u)
          ) AS v (val)
        WHERE
          v.val IS NOT NULL
      ) AS derived
    WHERE
      rn IN ((c + 1) / 2, (c + 2) / 2)
  )
FROM
  temp AS t
;

The v.val IS NOT NULL filtering is there to imitate the UNPIVOT behaviour more closely, because UNPIVOT automatically filters out NULL values.

An extra nesting was necessary because there was no other way to produce the count and row numbers and use them at the same nesting level.

So the innermost SELECT (SELECT ... FROM (VALUES ...)) unpivots the row and provides the row count and row numbers, while the middle-tier level calculates the median.

It is possible to reduce nesting with the help of CROSS APPLY and grouping in the main query, like this:

SELECT
  id,
  median = AVG(x.val)
FROM
  temp AS t
  CROSS APPLY
  (
    SELECT
      c   = COUNT(*) OVER (),
      rn  = ROW_NUMBER() OVER (ORDER BY v.val ASC),
      val = v.val
    FROM
      (
        VALUES
        (t.a), (t.b), (t.c), (t.d), (t.e), (t.f), (t.g),
        (t.h), (t.i), (t.j), (t.k), (t.l), (t.m), (t.n),
        (t.o), (t.p), (t.q), (t.r), (t.s), (t.t), (t.u)
      ) AS v (val)
    WHERE
      v.val IS NOT NULL
  ) AS x
WHERE
  x.rn IN ((x.c + 1) / 2, (x.c + 2) / 2)
GROUP BY
  t.id
;

For a live demonstration of both methods, please follow this dbfiddle.uk link.


How to avoid certain characters in MySQL full text search?

This situation seems to be the result of a bad design decision.

As this column is not storing human readable content, but is to be used as an identifier, the content of this column should have a uniform structure, either with or without the dashes, but not mixing them! You'd also have a hard time having a UNIQUE constraint on this column...

I'd recommend stripping the dashes from the table:

UPDATE table SET parcel_number = replace(parcel_number,'-', '');

MySQL replace reference

Then you only have to strip the dashes from the the search string too. I assume ab-cd-ef is the same as abcdef. Do note however that doing so means a-bcd-ef and ab-cd-ef will be treated as being the same.


Based on your description you want to have a GroupMembers table that does all the many to many joins for you.

Your current schema only allows a Group to contain one User and one Student as you have made the GroupName the Primary Key.  You really want to break the Group out to its own table in case the group name changes.

This can allow you to have:

A Group with many Users and many Students
Users can be members of many Groups
Students can be members of many Group
A User should not be duplicated in a Group
A Student should not be duplicated in a Group

Thats achieved with a schema like this:

Because you only ever want one combination of a given group, user and student you can make the foreign key columns the primary key too.

This all follows Third Normal Form (at least).


Log shipping with a monthly archival process

You can script this whole process out with TSQL and SQL Agent jobs depending on the your cleanup process -- I assume you can or already have it scripted but here are the basics how I've dealt with similar issues in environments I maintain and support.

Switch recovery model to simple on primary DB (TSQL with SQL Agent job step) -- this will break the log shipping chain so time out your LSCopy, LSBackup, and LSRestore jobs around this time when this occurs -- then run your cleanup process on primary DB
Switch your primary DB back to full recovery model (TSQL with SQL Agent job step), grow your (or perhaps shrink) your primary log file back to the "usual" size (SQL Agent TSQL again)
Run (or TSQL script with SQL Agent job) a FULL backup of the primary DB to the "usual" full backup location
Restore the secondary DB with the FULL backup file as in #3 above (TSQL with SQL Agent job step)

Afterwards, as timed out with some testing, what you already know about the timing, etc. the LSBackup, LSCopy, and LSRestore jobs should start working again.

I don't remember exactly, but you may need to purge the LSBackup and LSCopy TRN files on both primary and secondary after step #1 above to ensure SQL doesn't try to apply the broken chain TRN files to the secondary DB.

This can be done and I've done it before.  It may not be "best practice" but if there's a business need, that should be enough justification if it works, gets the job done, and allows you to put some automation around it.


Update Statement on one table takes locks on other tables

The tables are being locked due to the trigger: [iduSalesOrderDetail] on [Sales].[SalesOrderDetail]

The trigger launches if any of these actions occur:

IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])

And UPDATE([OrderQty]) is one of these actions.

This trigger will then update Person.Person two times.

One of these Update statements:

  UPDATE [Person].[Person]
            SET [Demographics].modify('declare default element namespace
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1]
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')
            FROM inserted
                INNER JOIN [Sales].[SalesOrderHeader] AS SOH
                ON inserted.[SalesOrderID] = SOH.[SalesOrderID]
                INNER JOIN [Sales].[Customer] AS C
                ON SOH.[CustomerID] = C.[CustomerID]
            WHERE C.[PersonID] = [Person].[Person].[BusinessEntityID];

And also Sales.SalesOrderHeader

 UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] =
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

Explaining the Exclusive locks on these objects.


mariadb won't let me access from "remote"

Four things to check:

Check that the DB user allows for connecting from the remote client. A user has a both a 'user' and a 'host' component. SELECT user, host FROM mysql.user; will show your users including their hosts, i.e. the clients they allow. You can use the wildcard '%' to allow connecting from any client, or a specific IP address or hostname if you prefer that.
Make sure that the skip-networking variable is not enabled in the configuration files.
Make sure bind-address is either not enabled or pointing to a specific IP address you want to allow in the configuration files.

Once you have updated the configuration files, restart MariaDB.

Check the MariaDB documentation on configuring for remote client access for details.

Make sure the firewall on CentOS isn't blocking access:

sudo firewall-cmd --zone=public --permanent --add-service=mysql

sudo systemctl restart firewalld