Does the version of a SQL Server Witness matter?

From BOL :

  •For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.)

Note: Express edition of the same sql server version is supported.

Edit: based on the comment from @Raidri

Technically it is possible to have a witness with a higher version of sql server than the 2 partners, but it is highly recommend and supported that all servers have the same VERSION of sql server running - edition can be different.

  Except during an upgrade from an earlier version of SQL Server, the server instances in a mirroring session must all be running the same version of SQL Server. For example, a SQL Server 2008 witness is supported when you are upgrading from a SQL Server 2008 mirroring configuration but cannot be added to an existing or new SQL Server 2008 R2 or later mirroring configuration.


Why is this frequent SQL not in the SQL Server procedure cache?

There's nothing in the Core .Net SqlClient Data Provider (AKA System.Data.SqlClient) that would explicitly prevent plans from being cached.

It would be helpful to know exactly what queries you're running to check the plan cache.

For instance, depending on whether the developers are parameterizing their queries, you could see entries in sys.dm_exec_cached_plans with different objtype values:

"Prepared" - the query had parameters added using the SqlParameter classes
"Adhoc" - the query did not have any parameters, or the parameters were embedded directly into the query text

But I expect that would be true of other data providers as well.

Since you're on SQL Server 2017, another option available to you is to enable Query Store for this database, and then see if the expected SQL text is being captured there.  This will include even queries that use OPTION (RECOMPILE) (see Erin Stellato's blog post Queries with OPTION (RECOMPILE) and Query Store for details).  This could help confirm whether or not developers are using that hint.

Finally, you could use Extended Events to temporarily capture executing queries while the application is running.  A very simple way to do this is with Erik Darling's sp_HumanEvents.  Here's an example usage from the site, which runs for 20 seconds, capture all queries against the "YourDatabaseName" database that ran for at least 1 second:

EXEC dbo.sp_HumanEvents
    @event_type = 'query',
    @query_duration_ms = 1000,
    @seconds_sample = 20,
    @database_name = 'YourDatabaseName';


Compare the contents of a single column and sync rows in that colum

Is this what you're looking for?

Set up demo table with data

declare @T table (ItemNumber int, location int, price decimal(11,2))

insert into @T (ItemNumber,location, price) values
(1,1,10.00),(1,2,20.00),(1,3,30.00)

select * from @T

| ItemNumber | location | price |
|------------|----------|-------|
| 1          | 1        | 10.00 |
| 1          | 2        | 20.00 |
| 1          | 3        | 30.00 |

The update statement

UPDATE @T
SET price = (
        SELECT price
        FROM @T
        WHERE location = 1
        )
WHERE location <> 1

select * from @T

| ItemNumber | location | price |
|------------|----------|-------|
| 1          | 1        | 10.00 |
| 1          | 2        | 10.00 |
| 1          | 3        | 10.00 |


Is there an efficient way to use 3rd party packages in PL/Python?

I've found examples of people importing 3rd part packages into PL/Python scripts, but it seems to me that re-importing libraries upon every run of a stored procedure is terribly inefficient.

Using strace, I can see that the files are only actually read the first time an import is done in any given session (there may be exceptions, like if the effective user id changes within a session).  There may be some initialization which is re-done every time the import is run, I don't know how to investigate that.

I don't think you can boost the loading to above the session level.


what are all possible filters to add to a server-side trace?

from this question: Find out what columns are in a trace file

there is this script

--  Gets all Events for a given trace id
SELECT a.[EventID], b.[name] AS [Even Name], a.[ColumnID], c.[name] AS [Column Name], d.[name] AS [Category]
FROM fn_trace_geteventinfo(<trace ID>) AS a --<<Put the trace ID you are interested in
    INNER JOIN sys.trace_events AS b ON a.EventID = b.Trace_Event_ID
    INNER JOIN sys.trace_columns AS c ON a.ColumnID = c.Trace_Column_ID
    INNER JOIN sys.trace_categories AS d ON b.Category_ID = d.Category_ID
ORDER BY a.[EventID], a.[ColumnID]

that shows all the columns of a trace, due to the DMV sys.trace_columns

I have put in place a server side trace with the following filters:
(this is part of the script)

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 53e0541a-7b74-4fce-bcac-9255e79a6372'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'udpProductTaxRateGet'
exec sp_trace_setfilter @TraceID, 34, 1, 6, N'udpProductBulletPointSelectByTier1NoteTypeCode'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'US16AUTPProduct'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID

When I run this query now, I can see what columns are there, and exactly how the filters would work.

------------------------------------------------------------
-- what filters are set on @traceid
-- and how they are working
-- marcelo miorelli sql server 2005 - 8-8-2016
------------------------------------------------------------
declare @traceid int
select @traceid = 2
select a.[columnid]

     ,c.[type_name]
     ,c.[name] as [Column Name]

     ,[ @comparison_operator ] = CASE A.comparison_operator
                                     WHEN 0 THEN '= (Equal)'
                                     WHEN 1 THEN '<> (Not Equal)'
                                     WHEN 2 THEN '> (Greater Than)'
                                     WHEN 3 THEN '< (Less Than)'
                                     WHEN 4 THEN '>= (Greater Than Or Equal)'
                                     WHEN 5 THEN '<= (Less Than Or Equal)'
                                     WHEN 6 THEN 'LIKE'
                                     WHEN 7 THEN 'NOT LIKE'
                                     ELSE 'NOT SURE'
                               END
     ,a.value
     ,[ @logical_operator ]    = CASE A.LOGICAL_OPERATOR
                                     WHEN  1 THEN 'OR'
                                     ELSE         'AND'
                                 END
 from fn_trace_getfilterinfo( @traceid ) a
    INNER JOIN sys.trace_columns AS c ON a.ColumnID = c.Trace_Column_ID

  --[ @logical_operator = ] logical_operator

  --Specifies whether the AND (0) or OR (1) operator is applied.

  --logical_operator is int, with no default.


Update table where table name in select query itself

You could use a stored procedure or anonymous PL block to build dynamic SQL. See below for an example.

  DISCLAIMER: I must point out that dynamic SQL is almost never the way to go - the database cannot cache the query plans efficiently and it is much slower than conventional queries. Were we on Stack Overflow here I'd be more inclined to suggest you use a supplementary language with a switch statement or something to accomplish this.

do language plpgsql $$
declare
    _sql text;
    _r record;
begin
    for _r in (
        select   article.articleId ,'MUSIC' as type ,createddate
        FROM     MTDDiscussion article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.articleId, 'SPORTS' as type ,createddate
        FROM     MTDOpinion article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.articleId ,'DANCE' as type  ,createddate
        FROM     MTDSurvey article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1)
        UNION
        (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate
        FROM     WikiTopic article
        where    article.status=2
        and      article.createdBy=101
        order by createdDate ASC limit 1
    ) loop
        _sql = E'update ' || _r.type::text || E' set COLUMNNAME = \'' || _r.createddate::text || E'\'::date where id = ' || _r."articleId"::text;
        execute _sql;
    end loop;
end
$$;


could not receive data from WAL stream: ERROR: requested WAL segment XXXXXX has already been removed

Add a. restore_command that can restore the required WAL file from the archive to recovery.conf (or postgresql.conf from v12 on) and restart the standby.


Some basic questions about SQL and TLS 1.0 and 1.2

This might be different for each client, so you need to test them separately.

First of all for most clients (since they use SChannel) your Windows machines need to be recent enough to support TLSv1.2 (I.e. Server 2008 R2 and later). For those you also need to make sure TLSv1.2 is actually turned on in the Schannel registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client
    Dword: DisabledByDefault = 0

Some clients might need client software updates, this includes a certain minimum .Net Framework (before 4.6 all need patches) as well as a specific SQL Server Native Client version.

And also, if you have a Java application the JVM must actually be recent in order to be TLSv1.2 able (and have it enabled by default). Some Java applications might turn the TLSv1.2 protocol off despite it beeing enabled in Java 8, then you need to check the application.

But in regards to your assumption it requires other things to be negotiated, I don’t think so: in all cases TLSv1.2 will be negotiated if it is supported on both sides. If you are sure your SQL Server is capable then it’s the client configuration.

The TLSv1.2 KB article lists quite a few known issues and required patches (including client requirements):

https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

In some cases you might also need to exchange provider=SQLOLEDB with SQLNCLI11.


how to write code for logon/logoff triggers in oracle?

Basically, all of your sys_context() calls in the subqueries were wrong. It always needs to be sys_context('userenv','session') because you need the sid to join with v$session. Note that you could condense it down to a single update statement.

I've tested this on 12c and it works:

create or replace trigger
   logoff_trigger
before logoff on database
begin
update logon_off_audit set last_action = (select action from v$session where sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_program = (select program from v$session where
sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_module = (select module from v$session where
sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
  logon_off_audit
set
   logoff_date = sysdate
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
elapsed_minutes =
round((logoff_date - logon_date)*1440)
where
sys_context('userenv','sessionid') = session_id;
end;
/


Is there a well-known name for this "poor man's ref. integrity" schema design pattern?

Your design looks a bit like the "supertype/subtype" pattern. Search for that and for "table inheritance". It needs quite a lot of work to be able to enforce integrity constraints though.

You are missing a generic Fruit table (that's the "supertype") and a FruitType table to store the alllowed fruit types:

FruitType
    fruit_type PK

Fruit
    fruit_type PK, FK -> FruitType (fruit_type)
    fruit_id   PK

Then the 3 (or 4 or more) tables would be (the "subtype" tables):

Apple
    fruit_type
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Apple')

Banana
    fruit_type PK
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Banana')

Orange
    fruit_type PK
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Orange')

And any other table can reference the Fruit table:

FruitPack
    fruitpack_id PK
    destination

FruitPackFruits
    fruitpack_id FK -> FruitPack (fruitpack_id)
    fruit_id
    fruit_type
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)

It doesn't look very nice and one column in every "fruit" table seems redundant as it has one and only one allowed value. And every time you need to add a new fruit (say Cherry), you have to add a row in the table FruitType and a new table (Cherry), similar to the other ones. So, it works better if your design is more or less stable. If you find that you may need to add a new "fruit" every few days or if you have a thousand (or more!) different fruits, it's not the best way.

On the other hand, it enforces integrity and you can't insert cherries into the Bananas or oranges into the Apples.


Find procedures that haven't been called in  days

If sys.dm_exec_procedure_stats is not reliable for you (probably more because the information doesn't survive restarts than anything to do with the plan cache), SQL Server doesn't keep track of this in any other way.

The only way to do this would be to add logging to your stored procedures (or to the app that calls them, if that is feasible and inclusive enough), or to run a very targeted server-side trace perpetually and review the trace.

Also note that just because a procedure hasn't been called in a week doesn't mean it won't be called tomorrow. You could have reporting procedures that are only called monthly or annually or some obscure operation that doesn't happen very often. Deleting that stored procedure could be disastrous days or weeks from now, potentially beyond any backup you have at the time (and assuming you aren't following best practices and storing your stored procedures in source control).

The safest way, IMHO, is to rename stored procedures (maybe with a zzz_ prefix so they sort to the bottom of any lists) that you've already identified through other means as potential candidates of being "too old" - then at least when you do that to one inadvertently and something breaks, it is easy enough to rename it again, restoring functionality without having to scrounge for old code in backups. Only delete the procedures when a full business cycle has passed and nobody has complained.


How to systematically export all tables from a DB2 database schema to csv

You can easily create a small script that does this for you, or even do a loop on the command line. Assuming you are on a unix/linux platform something like below should do.

#> db2 connect to mydb

#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema = 'DB2INST1'"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

You can replace DB2INST1 with whatever schema you are interested in. If you want tables from several schematas you can use in:

#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema in ('DB2INST1', ...)"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

There are a lot of options that you can use to format the output from export, here I use modified by coldel but there is much more that you can configure. See:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html

for more info.


How to know a backup command failed or worked in a script

the problem is the error severity of the backup exception.
I simulated your situation with this script:
BACKUP DATABASE [test] TO  DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE [test] TO  DISK = N'C:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The first command generate an error of severity 16 (path not found)
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'. Operating system error 3(path not found.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
and then sql server continues to execute the next command:
11 percent processed.
21 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
61 percent processed.
71 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 504 pages for database 'test', file 'test' on file 2.
Processed 2 pages for database 'test', file 'test_log' on file 2.
BACKUP DATABASE successfully processed 506 pages in 0.029 seconds (136.179 MB/sec).
Completion time: 2021-01-07T14:06:19.6844320+01:00
As you can see here:
https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver15#levels-of-severity
The error severity 16 do not breaks the batch.
But if you use XACT_ABORT ON...
    SET XACT_ABORT ON

    BACKUP DATABASE [test] TO  DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

    BACKUP DATABASE [test] TO  DISK = N'C:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

The second backup isn't ever reached.
Msg 3201, Level 16, State 1, Line 3
Cannot open backup device 'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'. Operating system error 3(Impossibile trovare il percorso specificato.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
Completion time: 2021-01-07T14:34:39.0700755+01:00


I can't say that I understand what it is that You are trying to do...
The thing is, just like You have already been told, that You can't use Dynamic SQL in a Function.

Using a Stored Procedure will solve the problem You encountered:
(I did not try to find another logic to retrieving the same data, which is possible, I just converted Your Function to a Stored Procedure)

CREATE PROC [dbo].[usp_getContentURL]
(@VID INT, @accountID INT)
AS
DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) =  ''
SET @sql = '
DECLARE @CompanyID int
DECLARE @ProjectNumber int
DECLARE @ContentID int
DECLARE @VersionNumber int
DECLARE @FileName varchar(400)
select @VersionNumber=V.Number, @ContentID=V.ContentID, @ProjectNumber=P.Number, @FileName=C.Name, @CompanyID=P.CompanyID
from ' + @vTable + ' V
inner join' + @cTable + ' C on V.ContentID=C.ID
inner join Project P on C.ProjectID=P.ID
where V.VID='+convert(varchar,@VID)+'
select ''/''+Convert(varchar,@CompanyID)
+''/''+Convert(varchar,@ProjectNumber)
+''/''+Convert(varchar,@ContentID)
+''/''+Convert(varchar,@VersionNumber)
+''/''+@FileName'

EXEC (@sql)
GO

If this does not meet Your needs please post what exactly You are trying to do.
Good Luck,
Roi


If you're using big fields like VARCHAR(MAX), you'll discover that off-page data isn't compressed with page compression. (Hey, go figure.)

To see how much data of your table is being stored off-page, use the free open source script sp_BlitzIndex (disclaimer: I'm a coauthor on that). You can run it at the table level like this:

EXEC sp_BlitzIndex @DatabaseName = 'mydb', @SchemaName = 'dbo', @TableName = 'whywontthiscompress'

Then, in the top result set where it lists the indexes, look at the amount of LOB data stored on the table. If it's a lot, that's probably why compression isn't working. (And you can post a screenshot of the top result set in your question if you'd like more help/clarifications on this.)


Unable to use a temporary table more than once

Community wiki answer generated from comments to the question by various people.

Despite the name, you are declaring #results as a Common Table Expression (CTE), not a temporary table. CTEs only apply to the same statement they are defined in.

You can solve your problem using a temporary table, using syntax like:

CREATE TABLE #mytable (<columns>);
INSERT INTO #mytable...

or

SELECT *
INTO #results
FROM ...

Additional comments by the author:

As Pointed out by Shanky, I am using a CTE instead of a temp table, which as Queue Man said, loses scope when I assign it to a variable.

So I ended up creating a temp table and inserting the contents of my CTE #results as suggested by JNK.


Simply "disable" the login:

ALTER LOGIN [xyz] DISABLE;

Then, if the login is a SQL Server login, you can change the password with:

ALTER LOGIN [xyz] WITH PASSWORD = '$ome$ecurePa$$w0rd';

Then, re-enable the login:

ALTER LOGIN [xyz] ENABLE;

If the login is from Active Directory, you can still disable the account (and re-enable later), but you'll need to have the password changed in Active Directory.

Note that disabling the login does not disconnect anyone currently connected as that login. In order to do that, you'd need to kill the spids associated with the login.


When it comes to securing mysql, as sson as you install mysql, you must run the script mysql_secure_installation. If mysql is already in production for a while, you can perform the steps of the script manually with a lot of caveats.

For example, you have to erase all entries in mysql.db whose db column's first four(40 characters are 'test'. : Please see MySQL : Why are there "test" entries in mysql.db? (February 17, 2012)

You must drop anonymous users

MySQL error: Access denied for user 'a'@'localhost' (using password: YES) (January 19, 2012)
Weird MySQL Users Been Created (eg. bug115166_10073) and not by me  (September 28, 2011)

Here are my other posts on securing mysql

Limiting database security (June 6, 2012)
Is this a normal set of MySQL privileges? (September 11, 2011)


How do I automatically deliver the SQL Server built in reports?

No, there is no scheduling capability in SSMS for the SSMS reports. This is by design, if the limitations stated here apply to standard reports too (SQL Server Manageability Team Blog)

However, (I haven't tried this) the reports are available to use in a full SSRS install. See another page "on the same blog as above", attached zip file


Why is SQL Server on the same box?

"Perform Volume Maintenance Tasks" should certainly be granted to the service account used to run SQL Server since that will allow SQL Server to not spend time unnecessarily zero-ing files during expansion of existing files and creation of new files.

"Lock Pages in Memory" would be a good consideration if you want to guarantee SQL Server has enough memory to perform the workload assigned.  If you don't care about the SQL Server workload, why is it on the same box as SSAS?


Does cancelling an (AUTO)VACUUM process in PostgreSQL make all the work done useless?

Work done by an interrupted VACUUM FULL will be entirely lost, as it will simply revert to using the previous version of the table and throw away the in-progress version of the table.

Work done by a regular (not-FULL) VACUUM might not be entirely lost.  It cleans indexes in batches, and any batches which were fully cleaned will not need to be cleaned again.  They will still need to be inspected again, but will be found already clean the next time.  So you might save some write IO which won't need to be repeated.


What are the dangers of not gracefully shutting down mysql server

It's possible that a transaction may be partially committed leading to unresolvable corruption on reboot. This is especially the case if you use MyISAM tables - InnoDB tables have a double write buffer which is a form of Write Ahead Log (WAL).

A good discussion of the concepts is available here and here. Note that from the Percona article we have "the changes made by transactions will occur in volatile memory and later be flushed to disk". Switch off the machine and your RAM evaporates.

Note that despite these elaborate precautions and systems, many questions here concern MySQL corruption (even with InnoDB engine tables) which shows that no system is perfect. The "horse's mouth" guide to InnoDB (i.e. Oracle/MySQL's own technical article on the issue) is available here.

This corruption can lead to the need for a restore from backup which means that data changed/inserted between then and the power interruption is lost. It's not a good idea on production servers and is the reason that we have UPSs (Uninterruptable Power Supply) on them.


seeing as no one has helped me with this i'll answer myself to help anyone who is looking at the same thing.  It looks as if to enable CDC on an existing warehouse you need to do this on a table by table basis, recommending capturing all the ETL in package per table.  Also, it is recommended to enable CDC state per table to avoid conflicts.  Recommended approach would be --> enable CDC on source DB.  Transfer incremental loads to staging DB or schema inside DWH.  Incrementally load records to Dimensions and Facts. Every example i can find on net is for one table, which is fairly useless... be much appreciated if someone can find a CDC example for multiple tables...


Two things:

1 - You still need to have a UNIQUE constraint to use a field as a foreign key.

2 - In your example you WILL have a performance decrease, because by default a PK is also a clustered index.  A non-clustered index on your FK field will speed the JOIN but you will still need to pay the cost for a bookmark lookup since the other fields aren't at the leaf level of the index.  If you INCLUDE(otherdata) in your NC index then it should work fine (assuming that ID is your clustered index.


PgBouncer logging details for `query_wait_timeout` error

The log message you shared is not due to some error with a query.  Rather, it is due to a pgbouncer client session that is waiting for a server connection, but that wait took more than 2 minutes.  In other words, all real-connections in a pool have been used, and some session was waiting around for 2 minutes to get an opportunity to run a query on the database.  There could be a number of factors causing this situation:

Session-level pooling -- you may have several pgbouncer sessions that get real connections to the database, but are idle and never releasing a connection back to the pool.  Then, the newest session just waits around and never gets a chance to run its query (and query_wait_timeout happens, and the new pgbouncer session is booted)
Transaction-level pooling, pool size too small -- you may just have a lot of connections from applications and they spend a lot of time running a transaction, such that any new sessions are stuck waiting around for the transactions to finish, but 2 minutes was not enough time.
query_wait_timeout too small -- you might just have lots of long-running queries (check pg_stat_activity for any old queries that are still running, or for idle transactions)

There may be a few other possible reasons for what you're seeing, but these are just a few that came to mind.


MongoDB - overloading PRIMARY with multiple replica set

MongoDB replica sets read data from Primary oplogs.

Secondaries in replica sets sync from another member of the replica set with a newer oplog, but that does not necessarily have to be the current primary.

MongoDB supports chained replication, where a secondary member can replicate from another secondary member instead of the primary. This feature is enabled by default, and is particularly useful to minimise network traffic for deployments spanning multiple data centres. Unless chained replication is specifically disallowed, secondaries will periodically evaluate the member they are syncing from and may choose a lower latency secondary (with a more current oplog) to sync from.

You can check which member a secondary is syncing from via rs.status(). There should be a syncSourceHost field (MongoDB 4.0+) or a syncingTo field (deprecated name for the same field in older versions of MongoDB).

  We have 7 nodes cluster though the load is not heavy and hence checking if we need to downsize it to 5 or so ..

If you aren't seeing any performance issues with your current deployment and workload, there should be no need to proactively downgrade unless you feel the extra replica set members are no longer needed for your use case.


Stare at this:

/
(   SUM(ua_expenses_by_school.Amount_USD) * 100 ,
1 + SUM(ua_money_allocation.Value_USD) )


Trying to restore users/passwords for a MySQL server

You don't need to export or import anything.  As long as the versions of MySQL on both machines are identical you can simply

Shutdown MySQL process service mysql stop
Overwrite the installed /var/lib/mysql with the old /var/lib/mysql.

A. cp -Rf /var/lib/old_mysql /var/lib/mysql

B. As a precaution you could move the /var/lib/mysql on the new server
mv /var/lib/mysql /var/lib/mysql_default
chown mysql:mysql -R /var/lib/mysql
Overwrite the installed my.cnf with the old /etc/mysql/my.cnf file

A. As a precaution you can mv the my.cnf on the new installation
mv /etc/mysql/my.cnf /etc/mysql/my.cnf_default

B. Or even better, you could put a custom .cnf file in /etc/mysql/conf.d
Start MySQL process service mysql start

Your users and permissions will be restored automatically

You can verify version info with mysql -V

Good luck


Calculate row size and max row size for a table

After much of thinking and researching found one answer which really helped in achieving what  was required. It is a perl script and reference link is

http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

#!/usr/bin/perl
use strict;
$| = 1;

my %DataType = (
"TINYINT"=>1, "SMALLINT"=>2, "MEDIUMINT"=>3, "INT"=>4, "INTEGER"=>4, "BIGINT"=>8,
"FLOAT"=>'$M<=24?4:8', "DOUBLE"=>8,
"DECIMAL"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"NUMERIC"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',
"BIT"=>'($M+7)>>3',
"DATE"=>3, "TIME"=>3, "DATETIME"=>8, "TIMESTAMP"=>4, "YEAR"=>1,
"BINARY"=>'$M',"CHAR"=>'$M*$CL',
"VARBINARY"=>'$M+($M>255?2:1)', "VARCHAR"=>'$M*$CL+($M>255?2:1)',
"ENUM"=>'$M>255?2:1', "SET"=>'($M+7)>>3',
"TINYBLOB"=>9, "TINYTEXT"=>9,
"BLOB"=>10, "TEXT"=>10,
"MEDIUMBLOB"=>11, "MEDIUMTEXT"=>11,
"LONGBLOB"=>12, "LONGTEXT"=>12
);

my %DataTypeMin = (
"VARBINARY"=>'($M>255?2:1)', "VARCHAR"=>'($M>255?2:1)'
);

my ($D, $M, $S, $C, $L, $dt, $dp ,$bc, $CL);
my $fieldCount = 0;
my $byteCount = 0;
my $byteCountMin = 0;
my @fields = ();
my $fieldName;
my $tableName;
my $defaultDbCL = 1;
my $defaultTableCL = 1;
my %charsetMaxLen;
my %collationMaxLen;

open (CHARSETS, "mysql -B --skip-column-names information_schema -e 'select CHARACTER_SET_NAME,MAXLEN from CHARACTER_SETS;' |");
%charsetMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <CHARSETS>);
close CHARSETS;

open (COLLATIONS, "mysql -B --skip-column-names information_schema -e 'select COLLATION_NAME,MAXLEN from CHARACTER_SETS INNER JOIN COLLATIONS USING(CHARACTER_SET_NAME);' |");
%collationMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), <COLLATIONS>);
close COLLATIONS;

open (TABLEINFO, "mysqldump -d --compact ".join(" ",@ARGV)." |");

while (<TABLEINFO>) {
chomp;
if ( ($S,$C) = /create database.*?`([^`]+)`.*default\scharacter\sset\s+(\w+)/i ) {
$defaultDbCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : 1;
print "Database: $S".($C?" DEFAULT":"").($C?" CHARSET $C":"")." (bytes per char: $defaultDbCL)\n\n";
next;
}
if ( /^create table\s+`([^`]+)`.*/i ) {
$tableName = $1;
@fields = ();
next;
}
if ( $tableName && (($C,$L) = /^\)(?:.*?default\scharset=(\w+))?(?:.*?collate=(\w+))?/i) ) {
$defaultTableCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultDbCL);
print "Table: $tableName".($C||$L?" DEFAULT":"").($C?" CHARSET $C":"").($L?" COLLATION $L":"")." (bytes per char: $defaultTableCL)\n";
$tableName = "";
$fieldCount = 0;
$byteCount = 0;
$byteCountMin = 0;
while ($_ = shift @fields) {
if ( ($fieldName,$dt,$dp,$M,$D,$S,$C,$L) = /\s\s`([^`]+)`\s+([a-z]+)(\((\d+)(?:,(\d+))?\)|\((.*)\))?(?:.*?character\sset\s+(\w+))?(?:.*?collate\s+(\w+))?/i ) {
$dt = uc $dt;
if (exists $DataType{$dt}) {
if (length $S) {
$M = ($S =~ s/(\'.*?\'(?!\')(?=,|$))/$1/g);
$dp = "($M : $S)"
}
$D = 0 if !$D;
$CL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultTableCL);
$bc = eval($DataType{$dt});
$byteCount += $bc;
$byteCountMin += exists $DataTypeMin{$dt} ? $DataTypeMin{$dt} : $bc;
} else {
$bc = "??";
}
$fieldName.="\t" if length($fieldName) < 8;
print "bytes:\t".$bc."\t$fieldName\t$dt$dp".($C?" $C":"").($L?" COLL $L":"")."\n";
++$fieldCount;
}
}
print "total:\t$byteCount".($byteCountMin!=$byteCount?"\tleast: $byteCountMin":"\t\t")."\tcolumns: $fieldCount\n\n";
next;
}
push @fields, $_;
}
close TABLEINFO;

Thanks all for great help.


ERD directly producing 5NF relational schema - is this desirable?

If your tables are already in 5NF then you don't need to think much more about further normalization.

Note however that the principle of full normalization (POFN) is no panacea. There may remain non-obvious update anomalies.

Database constraints will need to be applied. In my experience, designers who start with an ER diagram are great at implementing primary keys and foreign keys but other constraints can be easily overlooked. Other design approaches may result in better constraints e.g. I have a little experience of object role modelling which seemed to work well.

Further normalization should only be considered in conjunction with constraints because 6NF often makes constraints more difficult to implement using today's industrial-strength technologies e.g. none support Standard SQL's CREATE ASSERTION nor CHECK constraints that may include a subquery.

Also consider the principle of orthogonal design (POOD).


Table name both exists and does not exist in the same query

I know Oracle has a reputation for being difficult to deal with,

No comment

but this is just straight-up insane! Has anyone seen this before, who
might know how to get it working?

It's working just fine.  As it parses that single sql statement, it is finding multiple errors . . .  multiple times it refers to a non-existent table.  The error that it reports is simply the last one ...
SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  SELECT 1 FROM DUAL
  4  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  SELECT 1 FROM DUAL
  5  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 3:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  SELECT 1 FROM DUAL
  6  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 4:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  6  SELECT 1 FROM DUAL
  7  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 5:
ORA-00942: table or view does not exist

SQL> --
SQL> INSERT ALL
  2  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  3  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  4  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  5  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  6  INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
  7  SELECT 1 FROM DUAL
  8  ;
INTO mySchema.myTable (c1, c2, c3) VALUES ('A', 'A', 'A')
              *
ERROR at line 6:
ORA-00942: table or view does not exist

Your reported error points, and the fact that you mention parameterized values, indicate that your actual use case is more complex than my demo, raising the possibility of there being other issues at play.  But since we can only go with what we see, we can't address those potential issues.


How to transfer data from mysql 5.5.24 to 5.1.36

A backup is just a boat anchor until you verify that you can reload from it.  So, give it a try.  Set up a 5.5 version (if you can find such an old version) and try the dump.

It may work as is.

How are you doing the backup?  mysqldump?  If so...

At worst, you might need to edit the dump file to fix some old syntax.  (I don't remember any from 5.1 to 5.5.  Before that, there was TYPE=InnoDB changing to ENGINE=InnoDB.)

If you are copying a directory tree, then "files are files"; there is the backup is just a filesystem; it does not care what the bits mean.

So, I am puzzled as to why you talk about "5.1", yet you talk about "/data".


Best practices for schema changes and data migrations to a live database without downtime?

You almost have your answer already:

Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.


Is there any string compaction option for PostgreSQL?

if you want an effect like strings stored as integers use an enum type, the SQL syntax for interacting with the table will be the same, except you'll need to cast if you want to do string operations on the new column;

CREATE TYPE example_category AS ENUM (
 'category1', 'category2', 'category3', 'category4', 'category5',
 'category6', 'category7', 'category8', 'category9');

ALTER TABLE tmp_strings ALTER COLUMN category
  TYPE example_category USING category::example_category;

I think enums use 4 bytes (possibly 5?)


Return Entire Row which has the first Max Value in One Field

This is a simple task for a ranking function:

WITH cte AS
 (
   SELECT  prodMPEntity,
       prodMPLiquid,
       prodMPNormMonth,
       prodMPProdDate,
       ROW_NUMBER() -- assign a rank based on your description
       OVER (PARTITION BY prodMPEntity
             ORDER BY prodMPLiquid DESC, prodMPProdDate) AS rn
       FROM    dbo.prodMPRunningTotals
       WHERE        (prodMPNormMonth <= 6)
 )
SELECT   prodMPEntity,
    prodMPLiquid,
    prodMPNormMonth,
    prodMPProdDate
FROM cte
WHERE rn = 1 -- return the row which is ranked 1st


profile name not valid when creating SQL server 2014 availability group

As @Nic rightly suggested, the secondary replica had a trigger to send emails on CREATE DATABASE statements which was set to use an incorrect profile name. I corrected that and validation checks completed successfully.


Which DBMS is good for super-fast reads and a simple data structure?

If you are not that concerned with relational logic, want really fast read speed, and you are willing to work with an RDBMS, I would prejudicially venture to say MySQL. Why ???

The MyISAM storage engine has an option that can allow the physical structure of the table to be augmented for better performance. What is that option ? The ALTER TABLE option ROW_FORMAT.

For example, the book MySQL Database Design and Tuning recommends using ROW_FORMAT=FIXED on pages 72,73. This will internally convert all VARCHAR fields to CHAR. It will make the MyISAM table larger, but executed SELECTs against it will be much faster. I can personally attest to this. I once had a table that was 1.9GB. I changed the format with ALTER TABLE tblname ROW_FORMAT=FIXED. The table ended up 3.7GB. The speed of the SELECTs against it was 20-25% faster without improving or changing anything else.

What if you already have a MyISAM table that is populated with data ? You could get metrics for recommended column definitions based on the data present in the MyISAM table. What query presents those metrics ?

SELECT * FROM tblname PROCEDURE ANALYSE();

PROCEDURE ANALYSE() This will not display data. It will read the value of every column and recommend column definitions. Example, if you have a type column whose values are 1-4, it would sugggest using an ENUM of those 4 values. You could then choose to use TINYINT or CHAR(1) since they take the same amount of space (1 byte).

Here is something else to consider: Since you were thinking about using a NoSQL DB, have you ever thought of using MyISAM in a NoSQL manner ? This is quite possible. Page 175 of the same book I mentioned suggests using HANDLER structures to read a table without the relational baggage. In fact, page 175 gives this example:

CREATE TABLE customer_mileage_details
(
    customer_id INT NOT NULL,
    ff_number CHAR(10) NOT NULL,
    transaction_date DATE NOT NULL,
    mileage SMALLINT NOT NULL,
    INSERT(customer_id),
    INSERT (ff_number,transaction_date)
) ENGINE = MYISAM;

This table contains millions of rows. Suppose that you need to create a data analysis apllication that has the following requirements:

It needs to retrieve blocks of information as quickly as possible.
Based on user input or other factors, it will likely "jump around" in the table.
It is not concerned with concurrency or other data integrity issues.
Cross-application table locking is not required.

These commands allow quick-and-dirty reads from the table:

HANDLER customer_mileage_details OPEN;
HANDLER customer_mileage_details READ ff_number FIRST WHERE ff_number=('aaetm-4441');
HANDLER customer_mileage_details READ NEXT LIMT 10;
HANDLER customer_mileage_details CLOSE;

I hope this give food for thought. Please look into it.

CAVEAT

What is very ironic about about me writing this particular post is that I wrote an earlier post about HANDLER being used in Percona Server binaries and thinking that using it was out-of-date. Since that older post, I have never thought I would ever write something in support of HANDLER structures. I now stand corrected.


Duration of reclaiming disk space on very large tables (2.5+ billion rows)

I chose a different approach to reclaiming the diskspace. A lot of data was used for the pkey and prop_id_3df0d554fdfe92ee_idx indexes. It was possible to halt all writes to the table, during which time I issued a REINDEX command for the individual indexes.

After rebuilding the indexes enough diskspace was reclaimed to allow a secondary table to be created based on the contents of the original table. Afterwards the original table was deleted and the second table renamed.


Does a huge key length value for a mulibyte column affect the index performance?

The number is a worst-case estimate. Remember that EXPLAIN doesn't typically read the data, so it has no idea if your data contains only a short string of 1-byte characters on average, or if it contains a full 64 characters each 3-bytes per character.
The performance of the query is much more influenced during execution by the number of pages read, and that varies more directly with the actual data. Short string values will allow more index entries to fit per page, therefore fewer pages need to be read. This is something the EXPLAIN cannot predict.
I would recommend do NOT change your encoding to latin1 solely for the sake of making EXPLAIN output "less confusing." This is not a good reason, and it's bound to limit you later if you want to support UTF-8 characters in that column.
In today's era, there's virtually no reason to use the latin1 character set. I recommend setting all strings to utf8mb4 as a default.
If the column has something like strings of hex digits for a uuid or a hash value, that's different. You could set the character set to ascii. But that's for a reason of the data, not for the convenience of someone reading the EXPLAIN.


Postgresql: percentile_cont interpolation of values

Because that is the syntax which is available for "ordered set" aggregate functions.  If the ORDER BY were removed, then it would not know what column or expression to calculate the percentile over.  I know what "the median height of a dog" means, and even "the median dog by height" (although that is of questionable utility, especially the continuous version) but what would the "median dog by no specified metric" mean?

We could invent some way of conveying that column-to-be-used information other than ORDER BY, but why do that? There is a machinery for "ordered set" aggregate functions in place, it should be used for the things that fit into it.


How to debug a db memory-leak causing mysql to go before it's own limits?

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite."  It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high.  Here's what makes me think this might be contributing to your problem:

  When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

  http://dev.mysql.com/doc/refman/5.6/en/memory-use.html

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time.  Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not.  I tested this on one of my systems, which had 9 threads in the cache.  Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem.  If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed.  Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test.  You should not see a substantial performance penalty by setting thread_cache_size to 0.  Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.


What is SQL Server "Denali"? What's new?

The new features include :

Multi-Subnet Failover Clustering
Programming Enhancements including sequences, ad-hoc query paging and full-text search tweaks
BI and Web Development Environment Improvements
Web-based Visualization
Data Quality Services enhanced

You can view the detailed review here :  New Features Of Denali

"Denali" is a code name. Here is the list of the code name of other versions of SQL Server:

1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 – SQL Server 2000 64-bit, codenamed Liberty
2005 – SQL Server 2005, codenamed Yukon (version 9.0)
2008 – SQL Server 2008, codenamed Katmai (version 10.0)
2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
2011 – SQL Server 2012, Codenamed Denali


Inconsistency between object explorer and available database dropdown

(follow-up from my comment to the OP)

This is what you should see:

Filter icon is grayed out because "Databases" is not selected.


I notice that when I accidentally put ' in it ... the console seems to stuck there

It's not "stuck" at all.

It's patiently waiting for you to supply the closing quote for the string (or date) literal that you started when you typed in the opening quote!  It even goes as far as to give you a "hint" that it's doing so - notice the different prompt it offers you, complete with a quote character in it!

Of course, when you do provide that closing quote, it will complain bitterly because what you've entered doesn't make sense.


Database appearing in one tool but not another

You will need to grant access on a user@hostname basis, not just the user alone. So you will have to grant access to 'sequel_pro'@'your_domain_name' to database_name.*. Click here for a sample script.


Someone suggested that as my data is structured, I do not need NoSQL. I moved to RDBMS (Postgres). It performed better than the earlier solutions but it is getting to its knees after a few million rows.

How is getting to it's knees? You can easily index a few billion rows, with a BRIN index. Merge your start and end times into a tsrange, and cluster on the pkid and range.

CREATE TABLE foo (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  startts tsrange
);
CREATE INDEX ON foo USING brin (id,startts);

I would consider frequently clustering on id until your workload won't permit that, but at a few million rows you've got a LONG way before you have a real scale problem.


Number of sessions connected to database


In a multi-database AG, what decides which databases will have more than 1 redo thread?

Parallel redo threads are assigned in database recovery order, which does follow the sys.databases creation date.
That said, the separate parallel database recovery feature means each database recovery task can be assigned to a different SOS scheduler (when there are a large enough number of schedulers available).
Say you had 8 databases and 32 processors. The 8 separate recovery tasks would likely be assigned to 8 different schedulers (the SOS abstraction of a CPU). Creation of the 8 tasks (in create date order) and scheduler assignments can happen very quickly.
How soon (and in which order) each scheduler starts executing its assigned recovery task depends on what other work each scheduler has at the time (its runnable queue), and how far through the current quantum any other currently-active task is.
Parallel redo threads are assigned (up to the global limit) soon after each independent recovery task starts executing on its assigned scheduler. This is non-deterministic due to the issue described just above.
Microsoft support have some undocumented trace flags that can help promote good distribution of parallel redo threads in complex scenarios. You should contact them about your situation.


Honestly with a 32 Gig database physical fragmentation shouldn't be much of an issue unless you don't have a couple of gigs of RAM.


querying multiple values from one column

SELECT
DESCRIPTION,
item,
cust_code,
pack_description,
uos,
pack,
total_value,
total_value_vat,
total_value_goods,
price_indicator
from TRANSACTION_DETAILS
where cust_code=1273
and item in
(
    371355,
    371476,
    371629,
    370556,
    370677,
    370820,
    370952,
    371070,
    371223,
    372154,
    372253,
    371751,
    371872
)

As for duplicates, you're going to have to decide how you want duplicates handled.  Using a GROUP BY, if there is a duplicate item you will have to figure out how to aggregate the rest of the data for the result set.  Feel free to give me that info and I can write up your aggregate query.


Database is not reflecting in sh.status()

db.getSiblingDB.databases.find()

Those databases what have "partitioned":false are not sharded and you can see from "primary":xxx key which shard those are located.


Does the SQL Spec require a GROUP BY in EXISTS ()

I found it in the SQL 2011 spec...

  If the <select list> “*” is simply contained in a <table subquery> that is immediately contained in an <exists predicate>, then the <select list> is equivalent to a <value expression> that is an arbitrary <literal>.

This confirms that by * not being equivalent to an arbitrary literal in this context that it is in fact PostgreSQL breaking the spec.

Keep in mind this is a distinct problem from

SELECT *
FROM ( VALUES (1),(2),(3) ) AS t(x)
HAVING count(*) > 1

Which both databases reject.

PostgreSQL,

  ERROR:  column "t.x" must appear in the GROUP BY clause or be used in an aggregate function

SQL Server,

  Column 't.x' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why this bug persists in PostgreSQL

Thanks goes to RhodiumToad on irc.freenode.net/#PostgreSQL for his help trouble shooting this. He also points out the difficulty in resolving this situation

  20:33 < RhodiumToad> the one problem is that in pg you can do  exists(select func() from ...   where the func() is an SRF that might return 0 rows

An SRF is a set returning function.

In PostgreSQL, we can do for instance use an SRF to generate a series from 1-10 (generate_series is in core)

SELECT * FROM generate_series(1,10);

And, we likewise can put it right here.

SELECT generate_series(1,10);

Two of them together give us a cross-join (cartesian product)

SELECT generate_series(1,10), generate_series(1,2);

But, if either of those return 0-rows you get nothing.. Effectually the same as this

SELECT * FROM ( VALUES (1) ) AS t(x)
CROSS JOIN ( SELECT 1 LIMIT 0 ) AS g;

And, that's the problem with optimizing this out entirely. You can have an SRF in a select-list inside of an EXIST statement that returns 0-rows, and forces the EXISTS to evaluate to false.


How do I configure AWS Aurora to separate write/read operations

AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html

In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to the proxy, the proxy would then have to do packet inspection to examine each incoming query to determine if it's read-write,  which then gets forwarded on to the master, or read-only, which can then get forwarded to any of N replicas.

Be aware that this has some notable implications:
1. This means that the proxy needs to understand the MySQL protocol
2. It needs to inspect each packet (query) and determine if it's RW or RO
3. It then needs to forward the query to the appropriate backend MySQL instance
4. It likely needs to keep track of each connection, maintaining a map of front-end connections between your app and the proxy, and backend connections to the mysqld instances. The front-end connection would remain stable, but the backend connection could change for each query.
5. You can potentially have some state issues as a result. For example, when you start an explicit transaction, create temporary tables, or set session variables in your connection... those could get lost when (transparently) switching backends.
6. This will have an impact on SSL and other security measures, as you are explicitly using a MITM
7. All of this typically adds quite a bit of overhead, and you will typically see noticeable query latency because of it.

This is a feature that we hope to have in the MySQL Router (the replacement for the old MySQL Proxy: http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/), but we do not yet. It takes a lot of time and effort to do it properly, so as to minimize the effects noted above. One such proxy that does support that today is ProxySQL:
http://www.proxysql.com   (See the "Read write split" section)

You can grab the source and start playing with it here: https://github.com/renecannao/proxysql

Good luck!


Is there any rule of thumb to optimize sql queries like this

Some rules of thumb that might be relevant to you . . .

Never try to tune a query without a representative EXPLAIN plan. Representative means to get an EXPLAIN plan either from the production database or from a dev/test database that have nearly the same number of rows and nearly the same distribution of values, ideally running on similar hardware. Different numbers of rows and different distribution of values often means different execution plans.
Make sure statistics are up to date.
Index (or verify indexes) on every column used in a JOIN clause and every column used in a WHERE clause. I think every dbms implements primary key constraints and foreign key constraints with indexes. There used to be some that wouldn't let you drop such indexes, and one or two that would let you. Since I deal with a lot of different dbms, I don't bother trying to remember which is which. I just look it up when I need to know. So just look it up.
One multi-column index might perform better than three single-column indexes. Think about consolidating indexes.
Write sargable WHERE clauses. Your expression COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN one_column AND another_column has to evaluate the COALESCE() function for every row--it can't use an index. If it's possible, replace the NULLs with the literal integer 1, and make that column non-nullable. (This isn't always practical, but non-sargable WHERE clauses are often a performance killer, especially if other considerations provoke a full table scan.)


Why is this second query more expensive than the first?

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID.  It also seems to assume that Person indexes PersonID and Person independently.

If this is the case, the first query comes back as

{INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID}
{INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice
and Zack get back FriendID}
{LOOP {INDEX UNIQUE SCAN Person on PersonID} for Alice and Zack}

Then the second comes back as

{INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID}
{TABLE ACCESS FULL PersonFriend FOR FriendID of Bob get back
PersonID}
{LOOP {INDEX UNIQUE SCAN Person on PersonID} for friend(s)}

This would be an elementary relational database design error.  With a relationship table like this you would always index all keys, and the performance profile for both queries would be indistinguishable (ignoring variations in value distributions, etc).

At this point it sounds like the author is trying to show that relational databases are intrinsically deficient.


How to Recover mysql Database From mysql data directory in Xampp

[mysqld]
innodb_file_per_table=1
innodb_force_recovery=5
--This will make MySQL skip a lot of checks on your data
--create a dummy database, with dummy tables that have the same names as the --ones you wish to restore.
-- stop mysql instance
--copy over your .frm files now
--start mysql
mysql> use recover;
mysql> flush tables;
mysql> exit;


How we take dump with batch of records option?

Here is something you may find intriguing
SUGGESTION #1 : Let mysqldump create the batches
mysqldump already uses an option called --extended-insert. This will group a series of records that can be loaded in a single INSERT command. While you cannot use mysqldump to control the number of rows to be batched together, you can parse a mysqldump. Of course, these means you will have to script it in the language of your choice (Python, Perl, C++, etc).
For any given table in a mysqldump of a single database, you will see the following paradigm:
CREATE TABLE
LOCK TABLE
INSERT INTO ...
INSERT INTO ...
INSERT INTO ...
...
...
INSERT INTO ...
INSERT INTO ...
UNLOCK TABLES;

You could parse the mysqldump as follows:

Step01 : Get the next table name in the mysqldump
Step02 : Locate next INSERT INTO or UNLOCK TABLES;
Step03 : If you encounter UNLOCK TABLES;, goto Step01
Step04 : If you have no more tables, you are done
Step05 : Goto Step02

Each INSERT command you read has a certain number of rows. This number you cannot control unless you script to put them together in the number of rows you choose. I would advise against that be mysqld decided the best number of rows for that particular batch given the current configuration settings (perhaps the max_allowed_packet)
SUGGESTION #2 : Use --skip-extended-insert and batch them your way
When you deactivate --extended-insert, each INSERT command will have one row. You can collect the rows using a script and collect as many rows as you like.
CAVEATS

Please be a little conservative on the number of rows you choose to batches together.
You will take much longer to parse the mysqldump because of the mysqldump will be much longer.


How to get a count of how often a name/string appears

Not sure what the rules are re 1 user owning the same device multiple times, if this should increase the count remove the distinct from within the count aggregate function
create table #x (Username  varchar(20),DeviceName int)
insert #x (username, devicename) values
('user1',1),
('user1',2),
('user2',3),
('user3',4),
('user4',5),
('user4',6)

select username, count(distinct devicename) as devcount
from #x
group by username


Tools to migrate from SQLite to PostgreSQL

Have you looked into using ETL tools? They are external and I believe pentaho runs on both windows and linux and its free.
pentaho's site


Phantom SQL Server database backups. How can I find the cause?

This looks like Veeam backups or some other third-party backup tool. Ask your systems guys if they take VM snapshots and backup the SQL server. Make sure you are VERY careful as Veeam can take FULL backups and break your differential base and possibly hose up your restore chain. I believe in Veeam they can do COPY_ONLY backups or set it to not truncate the transaction log, you'll want to make sure they aren't causing your well-planned restore strategy to go down the toilet in the event of disaster.


'skip rows' in a flat file source does not work as advertised

You're reading it backwards. You have instructed it to skip the first row of data and it does that. It then looks in the next row for column headers, which it won't find because it's blank. The actual data should be found on the third row. It tries to clue you in when you look at your preview. I create two files, one with your data and the other with the first line blank and the headers in the second row.

Notice we are previewing rows 2-4

Contrast that with your blank in between header and data

Still previewing rows 2-4 but the default header names are the first rows of data. Not quite what we're expecting.

When I created a simple Data flow using the two connection managers, it fails (as expected) on the BlankSecondRow as the above definition performs some validation that the header names are actually there. If I uncheck "Column names in the first data row" and up the "Header Rows to Skip to" 2, then it will work.


MS SQL Server 2017 for Linux - Installing to non default paths

You can set environment variables to control this: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables.  When you run the mssql-conf utility (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf) it will pick up these values and configure them for you.

This will allow you to change your directories:

Default data directory
Default log directory
Default dump directory (memory dumps etc)
Default backup directory

and more.


MySQL equivalent of PostgreSQL's psql \c command

In the context of MySQL, you cannot switch users. You can switch database and host.

According to the MySQL Documentation on the MySQL Client

  connect [db_name host_name]], \r [db_name host_name]]

  Reconnect to the server. The optional database name and host name arguments may be given to specify the default database or the host where the server is running. If omitted, the current values are used.

This looks similar to how PostgreSQL does it.


What does "sample_ms" and "num_of_reads" means in "sys.dm_io_virtual_file_stats". what is the interval time of stats

First, these start values are not measuring the same thing.

sys.dm_os_sys_info:

  sqlserver_start_time  - datetime -    Specifies the date and time SQL Server
  last started. Not nullable.

sys.dm_io_virtual_file_stats:

  sample_ms - bigint- Number of milliseconds since the computer was
  started. This column can be used to compare different outputs from
  this function.

  Does it mean the statistic values (num_of_reads, num_of_writes etc..)
  in DMF (sys.dm_io_virtual_file_stats) returns cumulative reads since
  the computer restart or since the SQL Instance restart or there is
  different interval time that we need to consider?

It should be cumulative reads/writes on each file ("Total number of bytes read on this file") for the current instantiation of the instance, i.e. since it was last started. The original function that this dmf replaces, sys.fn_virtualfilestats returned a timestamp column to help compare results received over time ("Database timestamp at which the data was taken").

Now that this is a dmf, it appears as though sample_ms fills that void based on its description:

  This column can be used to compare different outputs from
  this function.

In most realistic cases the only thing reading/writing to SQL Server's data/log files is itself, so these would not be incrementing unless the instance was started anyways. Regardless, it does not appear the intent is to use this counter for any purpose other than comparing multiple outputs over time.

I was unable to find a canonical reference saying that all dmvs reset on instance restarting, but some of the index based dmvs' documentation do specify this:

  The counters are initialized to empty whenever the SQL Server
  (MSSQLSERVER) service is started.

Many of the dmvs do not return information that is cumulative, so it doesn't make sense to describe them as being reset on an instance restart. However, this one does appear to fall into that category and should be noted as such.

I've made a pull request to updated the documentation to reflect the fact that it resets on instance startup.


I'd suggest, you store the 'chosen cards' in an separate assignment-table and group this assignment per user.

For example your table 'cards':

 table: cards
--------------
 card_id | PK
 ...

The following table stores the blurb for the given user:

 table: blurbs
--------------------------------------------------------------
 blurb_id   | PK
 user_id    | (given user, that wrote the blurb)
 blurb_text | (whatever you want to store here, e.g. a blob?)

Last, but not least, you need the assignment-table, to define, which user/blurb is assigned to which cards:

 table: card_assignment
------------------------------------------------------------------------------------------
 blurb_id | PK
 card_id  | PK (the cards to be chosen - card can only be assigned once per 'user-blurb')

Now, you can select any information, you need across that three tables:

E.g.: Any cards chosen by a particular user:

SELECT *
FROM card_assignment a
LEFT JOIN cards c ON (a.card_id = c.card_id)
WHERE EXISTS (
    SELECT 1
    FROM blurbs b
    WHERE b.user_id = :user_id
    AND b.blurb_id = a.blurb_id)

Indeed, the select statements can get very complex, if you have to use multiple tables that way, but in my opinion, this solution is as clean as possible.

Don't forget to put referential constraints on the fields, if you use mySQL with Inno-DB!


Does a SQL server unique constraint partially or fully fail a multi-row write?

Every individual transaction (statement) in SQL Server is atomic, meaning it passes or fails as a unit.

If the 999,999th record fails on a constraint violation in a 1,000,000 row insert, all of the other rows get rolled back and the table is exactly as it was before the failed statement was attempted.

The same applies for updates and deletes, as well as multiple statements in an explicit transaction.  For instance, if I have an explicit transaction that does a DELETE followed by an INSERT and an UPDATE, if I encounter an issue in the UPDATE and issue a manual ROLLBACK all of those actions get undone.


Is there a standard for naming foreign keys when two columns reference the same table?

No, there is no standard naming convention for this or just about any other scenario. Naming conventions are purely subjective: that your convention makes sense, and that you use your standard consistently, are both much more important than which standard you choose.

That said, in this case, I would opt for this option:

FK_CurrentTable_Created_Users
FK_CurrentTable_Modified_Users

This provides all the information you would need if you wanted to deduce something about this foreign key without all the difficult labor of retrieving the actual definition. If you just use this:

FK_CurrentTable_Created

Then you don't get any information about the other table. In this specific case it should be easy enough to infer, but that won't always be true. If you use this:

FK_CurrentTable_Users_Created

That just feels to me like it's in the wrong order. "This table references that table on column x." Much more intuitive, IMHO, to say "This table's column x references that table." It might even call for full references on both sides (of course, the users table may have more than one candidate key, though it is fairly uncommon for an FK to point to a candidate/natural rather than the surrogate):

FK_CurrentTable_Created_Users_UserID

And therein lies the problem. You're going to continue finding contradictory opinions precisely because this decision has to, by definition, be driven by opinion. And not everyone is going to agree.


How to get Identity Column to jump ahead (by say 1000) on SQL Server 2012

You could do this by running something like

declare @newid int;

select @newid = IDENT_CURRENT('YourTable') + 2000;

dbcc checkident('YourTable', reseed, @newid);

This would reseed your table identity to be 2000 higher than it currently is and seems like it should resolve what you are looking to do.


How to convert an age interval into a decimal year number in PostgreSQL

SELECT EXTRACT(EPOCH FROM INTERVAL '29 years 6 mons 22 days 21:17:52.860842')/31557600;

where 31557600 is roughly the number of seconds in a year.


Given the unique indexes, your query will select at most one row.

The optimizer knows it will need to descend the index b-tree just once, and will not need to scan forward or backward from that point to find more matches. This is known as a singleton seek (equality test on a unique index).

The current index matching implementation happens to always choose the clustered index when it can use a singleton seek.

The choice between clustered and nonclustered index here is generally not very important.
There might be a tiny extra cost as the upper levels of the b-tree are navigated (using binary search or linear interpolation) but this would be challenging to even measure. Remember only the ID and UserSystemID key components are present on non-leaf index pages.

One could argue that wider clustered index leaf pages are less likely to be in memory on average. There are a few other edge case consequences, but I don't see this behaviour being changed any time soon.

  But my leading column of clustered index is much less selective than the other unique nonclustered index. So I expect that the performance must be worse with clustered index but in practice it is not.

The selectivity does not matter for equality seek on a compound b-tree index.

Your unique clustered compound index has keys (UserSystemID, id).

To find a row with (UserSystemID = 1 and id = 1502945), SQL Server does not find all rows where UserSystemID = 1, then find rows where id = 1502945. That would be very inefficient.

You can tell how many pages your test query touches using SET STATISTICS IO ON. Your example builds a clustered index with two non-leaf levels. In total, finding the row you want means touching three pages - one at each level of the index.

Rows are ordered in the index by UserSystemID and id. My copy of your demo table has the following layout on the root (top level) page of the clustered index:

Performing a binary search on this page is easy:

Start at the middle row.
Compare the UserSystemID with the one you are looking for.

If it is not equal, continue the binary search in the usual way (choose a new midpoint in the earlier or later rows as appropriate).
If equal on UserSystemID, Compare the id with the one you are looking for, and continue the binary search

Following that logic, we will quickly find the child (next lower level) index page the searched keys will be found on if they are present. Repeat the binary search on that page, and so on until we reach the single leaf-level page that must contain the row we are looking for, if it exists.


in your case you will have to repopulate the order column completely
because if you delete row id 118 or 119, you need to change 118,119,120 and so on till the end of table

so in this case i suggest the following using the ROW_NUMBER() function

    update YourTable
    Set [order] = NewOrder
    from
    (
    SELECT t.id,t.[order],@rownum := @rownum + 1 AS neworder
  FROM YourTable t, (SELECT @rownum := 0) r
    ) X
    where YourTable.id = X.id

so in this case if you delete 118

Delete from YourTable where id = 118

run the above update script again and then you will see that your order field is updated

Select id,[order] from YourTable


Where do I find the meaning of TdsInputBufferError numbers?

I'm not sure that this is an exhaustive list but both Error 109 and Error 10054 (from this similar question) are detailed in the SQL communication errors KB article.


PostgreSQL: Insert into JSONB key failing with syntax error at or near "->>"

The reason is that the attributes of a JSON column are not database columns, so you cannot use them in the SET clause of an UPDATE statement.
You can only replace the complete JSON with an UPDATE statement.
It might be a good idea to use regular "scalar" database columns instead of lumping several attributes together into a JSON if you want to modify them individually.


(mysql innodb) Is single update statement with "where" transaction safe?

For sanity, we just assume inno

  If update 1 and update 2 is executed at the same time, is there a chance that both get successfully executed and one of them overwrite the value of another?

No, that's not how MVCC works. And wrapping single statements in transactions doesn't do anything. They're effectively already wrapped in transactions.

The first transaction will read from ABC, lock it with a row-level lock pending update, write a new row with the updated value, and set it as live on the table.

The second transaction will read from ABC, if the row is locked, it'll block. If not, it will lock the row pending update, write a new row with the updated value, and set it to live on the new table. If it is locked, it'll wait until the lock releases and recheck the row.

Interestingly enough, the default isolation level of read committed does not protect from phantom writes. So if both transactions start at the same time, they'll both read from the same snapshot. But, if one txn1 writes the update it'll actually modify the snapshot txn2 is reading from. So while you can't read the modified data (phantom reads), when you try to write the data you're interacting with the same master copy.

Suggestion: Upgrade to a real database, if possible

For more information, read the docs on MariaDB on transaction isolation.


The "right answer" is highly application dependant, but here are a few strategies I've seen used before.

Daily Backup / Restore-on-demand

This works well if you have a small data set, and the need to be able to have various developers use the backups ad-hoc. The idea is basically a mysqldump on a replication target system and some scripts to do a MySQL import.

Binary Log Replication

This method probably won't work for you, as you specifically want to read and write to the development database. Sometimes people will use replication to run read-only regression testing against, but modifying the database will result in failed replication and/or inconsistent data.

[https://serverfault.com/questions/380701/replicate-main-mysql-db-to-a-development-server-to-play-with-real-data]


MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

Please note that you ran ALTER TABLE ... ENGINE=InnoDB; That is a full table rebuild whether you go from MyISAM to InnoDB, or InnoDB to InnoDB (known as the NULL ALTER).

Please note the paragraph from the MySQL 5.7 Docs on innodb_file_format:

  The innodb_file_format default value was changed to Barracuda in MySQL 5.7.

  The innodb_file_format setting is ignored when creating tables that use the DYNAMIC row format. A table created using the DYNAMIC row format always uses the Barracuda file format, regardless of the innodb_file_format setting. To use the COMPRESSED row format, innodb_file_format must be set to Barracuda.


Where's the primary key?

DELETE records FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY HAMBASA, storeid ORDER BY HAMBASA) AS HambasaCount
    FROM @TMI) records
WHERE records.HambasaCount > 1


Multiple SELECT vs single SELECT over TCIP

With SQL server (and, I suspect, pretty much everything else) querying for a range will be more efficient than querying for many individual rows, even when working locally. The range query will likely result in an index seek followed by a short scan through the few subsequent the index pages (followed by any other page lookups needed), then many single selects will result in many individual index seeks (followed by any other page lookups needed).

The "where in (<static list>)" option mentioned by user503207 may perform similarly to the date based range query in SQL Server, try it and see what query plan results, though in some DBMSs (mySQL for instance, unless that has improved in that area since I last used it) such queries can be quite inefficient (sometimes resulting in a full index scan).

As your IDs are in sequential order by date and you know which ones you are needing (or you wouldn't be able to put together the list of individual selects) then you could instead sort them when producing your query to find the range and use where id between <first> and <last> which would also avoid needing to add the date column.

When considering doing the same remotely the query performance difference will be the same if all the selects are sent as a single batch, though you will see a little more bandwidth used because transferring many small resultselts is less efficient than a single larger one containing the same rows. If you are submitting the selects individually then you will see a significant slowdown as you are adding network latency to each individual query instead of to the batch as a whole.


Well, if you want/need to break it out that way, I would go:

Country(id,name,code)
Region(id,name)
Sub_Region(id,name)
City(id,name)
Address(id,country_id,region_id,sub_region_id,city_id,street_address,postal_box,appt_suite_number,floor_number,postal_code)

That should cover most of your scenarios US and world-wide. (ie, region could cover states, provinces, etc. Sub region can cover counties, sub-provinces, etc. Postal code instead of zip code so it can work world wide.) And I suppose if you really wanted to get fancy you could break out the street "type" (ie, street, avenue, boulevard, circle, etc.). But I don't know if you need that level of granularity or not?


If you look throught the slides from beginning to end you will find that the explanation is pretty accurate.

The recovery of the database starts.

The depicted slide is showing you the situation as it is at the time when the vertical axis is at the checkpoint.

At that time T2 and T3 are put into the UNDO queue.
Moving forward (in the reference) the examined time (vertical axis) moves forward and reaches the BEGIN of T4. The transaction T4 is put into the UNDO queue.
Moving forward (in the reference) the examined time (vertical axis) moves forward and reaches the BEGIN of T5. The transaction T5 is put into the UNDO queue.

  You now have all four transactions T1, T2, T3, T4 in the UNDO queue.

Moving further forward (in the reference) the examined time (vertical axis) moves forward and reaches the END of T2. The transaction T2 is put into the REDO queue.
Moving forward (in the reference) the examined time (vertical axis) moves forward and reaches the END of T4. The transaction T4 is put into the REDO queue.

  The transactions T3 and T5 are in the UNDO queue.
  The transactions T2 and T4 are in the REDO queue.

Moving even further forward (in the reference) the examined time (vertical axis) moves forward and reaches the Failure.

  T3 and T5 are UNDO-ne and T2 and T4 are REDO-ne (or reapplied).

The database is now in a consistent state.


Why is MongoDB user with the only privilege as "find" able to insert and update collection?

according to https://docs.mongodb.com/manual/reference/built-in-roles/
the "readwrite" is to blame ...

readWrite
Provides all the privileges of the read role plus ability to modify data on all non-system collections and the system.js collection.
The role provides the following actions on those collections:

collStats
convertToCapped
createCollection
dbHash
dbStats
dropCollection
createIndex
dropIndex
find
insert
killCursors
listIndexes
listCollections
remove
renameCollectionSameDB
update

Although the list also contains the remove - right .


Is there a way to de-orphan users without setting context to a restored database?

When creating the login principals, set the SID of the login to the original login.

Original server select name, sid from templatedb.sys.database_principals to get the SID

New server create login MyAdmin with password = 'DummyPassword951', sid = 0xBCB4D3AC87D6F449979D6810B8E9E250

Edit: FYI MyAdmin is the owner of the template database and is mapped to the dbo user. In the installed version of the database, I have mapped the MyAdmin login to the dbo user, using the dbo SID from the template DB.


Postgres use multiple tablespaces in a single database

Yes it's possible.

As documented in the manual you can specify a tablespace when creating a table.

However, tablespaces are rarely used in Postgres as they don't really provide any advantage.

One of the few reasons to use them is, when you can't easily expand the size of your filesystem where the data directory is located.


If you are running MySQL on Debian 6 squeeze stable with vMySQL-5.5 then there is a known issue mentioned about dotdeb packaging:

http://bugs.mysql.com/bug.php?id=64988


That's not a "nested table" (at least not in the sense as e.g. Oracle uses that term). The column still only contains a "single value" (which is a record though), not multiple rows.

The second table contains a single column with the type table1 which is a composite type (a.k.a. "record").

To access the fields of the composite type, you need to enclose the column name in parentheses:

select *
from table2
where (col2).id = 42;

Assuming that the type type1 has a field named id


Do regular ALTERs weaken the performance?

It's an intuitively-sensible concern that you've raised because it would seem to make sense that frequent DDL operations might leave little bits of messiness behind, but my experience has been that this concern is unfounded with InnoDB.

Prior to MySQL 5.6, the vast majority of ALTER TABLE operations actually rebuild the entire table with the new structure, copy all the data, then swap in the new table and delete the old one... so, I see very little potential for frequent changes to have a negative impact down the road.

You might, though, be well-advised to OPTIMIZE TABLE on tables where you've repeatedly inserted and deleted test data to clean up wasted disk space and rebuild the indexes, but unless your test data has been comparable in size to your production data, even this will disappear in the mix as the tables take on live data.


Export as csv or xsl file from SQL Server 2008 express R2

You'll want to look at using SQL Server Integration Services.

If you want to export a small amount of data, you can also SELECT the data in SQL Server Management Studio, and highlight / copy / paste it into Excel, or rt click save as.


Query to update column in one table based on conditions met in another table

I think this solution will work.

--demo setup
DROP TABLE IF EXISTS tblOrders;
DROP TABLE IF EXISTS tblUnits;
CREATE TABLE tblOrders (
  Order_ID INTEGER,
  Status INTEGER
);

INSERT INTO tblOrders
  (Order_ID, Status)
VALUES
  ('100', '0'),
  ('101', '0'),
  ('102', '0');

  CREATE TABLE tblUnits (
  Unit_ID INTEGER,
  Status INTEGER,
  Order_Id INTEGER
);

INSERT INTO tblUnits
  (Unit_ID, Status, Order_Id)
VALUES
  ('1', '1', '100'),
  ('2', '1', '100'),
  ('3', '1', '100'),
  ('4', '1', '101'),
  ('5', '1', '101'),
  ('6', '0', '101'),
  ('7', '0', '101');

--solution
UPDATE o
SET o.Status = 1
FROM tblOrders o
--check to see if there are units for the order
WHERE EXISTS (
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
        )
--make sure there are NO units that are not status = 1
    AND NOT EXISTS (
        SELECT *
        FROM tblUnits u
        WHERE u.Order_Id = o.Order_ID
            AND u.STATUS <> 1
        )
--verify update
select * from tblOrders

| Order_ID | Status |
|----------|--------|
| 100      | 1      |
| 101      | 0      |
| 102      | 0      |


How SELECT loaded into Memory - If the table is huge

Not exactly, but in effect pretty close.
Pages are read as they are needed (imagine the query thread is reading page after page). If a page isn't in cache, then it is read into cache. The cost in cache for that page would then be 1, since it has been accessed 1 time. When there is memory pressure (imagine your thread continues and reaches a point where all memory is now used), then memory pressure will trigger removal of "cheap pages" from cache. A page which has been read only only once (like the pages read in the beginning of your query processing) will have a cost of 1, and are likely to be targets from removal compared to other pages, which might have been read multiple times (having a cost higher than 1).
It is basically like any cache handling mechanism, in principal.


A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSDTC service

Excluding the MSDTC log file with the full path in AV ( Trend Micro ) fixed the issue.


Performance penalty for using thousands of tables in MySQL

Some day WP will realize that their design of 1 database per user has a scaling problem.  1 user versus 100 users is probably not much difference.  1K-10K begins to show pain, depending on how many are 'active'.

1000 databases means 1000 subdirectories in the filesystem.  That is some burden in the OS.  (10K would be worse.)

15K tables may or may not be an issue.  If, say, 100 blogs are active, then, 1500 tables are active.  That means that you should probably have table_open_cache set to about 1500.  (Plus other settings may need raising.)

But... If you don't have symptoms of performance problems, don't worry.  Perhaps your users are so light-weight that it is not a problem.


The maps table looks pretty ok. The name should probably be unique so you have the possability to skip mapid. Also, I would use the identifier mapname instead of just name which is to vauge, and also highly likely to be a reserved word (I have not checked though). Alternative design:
CREATE TABLE maps
( mapname varchar(64) not null primary key
, max_no_players int not null
, min_no_players int not null
);

You may or may not introduce a surrogate key, but then the name should still be unique (my guess):
CREATE TABLE maps
( mapid int not null primary key
, mapname varchar(64) not null unique
, max_no_players int not null
, min_no_players int not null
);

Next, I would look at CHESTS. A chest seem to have no attributes, so I will just add a surrogate key:
CREATE TABLE chests
( chestid int not null primary key
);

A map can contain one or more chests:
CREATE TABLE chests_in_maps
( chestid int not null
, mapid int not null
,     constraint pk_chests_in_maps
          primary key (chestid, mapid)
,     constraint ...
          foreign key (chestid)
          references chests (chestid)
, <f.k. for maps>
);

I would then continue with ITEMS and ITEMS_IN_CHESTS in a similar way. Properties for ITEMS looks like it is attributes of an ITEM.
This is by no means a complete answer, but it hopefully gives you some ideas.


Why BLOB ?
Why not string or varbinary(max) or nvarchar
Dot Net Nuke in their database inside table UserProfile have generc column

CREATE TABLE [dbo].[UserProfile](
    [ProfileID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [PropertyDefinitionID] [int] NOT NULL,
    [PropertyValue] [nvarchar](3750) NULL,
    [PropertyText] [nvarchar](max) NULL,
    [Visibility] [int] NOT NULL,
    [LastUpdatedDate] [datetime] NOT NULL,
    [ExtendedVisibility] [varchar](400) NULL ....


Unfortunately, no. The basic Find (Ctrl+F) and Replace (Ctrl+H) functions in the Access UI search the resultset of the form row-by-row.

For an ODBC linked table (any ODBC linked table, not just PostgreSQL) once Access has gone past the end of the rows that it pre-fetched when it displayed the form it retrieves one row at a time until it finds a match or reaches the end.

(Find and Replace is often considerably faster for Access linked tables - as opposed to ODBC linked tables - because the Access Database Engine works directly with the database file, retrieving 4KB pages of data at a time. If consecutive rows happen to be on the same page, or if the 4KB page for a given row has recently been cached, then Access can "get" that row without another round-trip to the database file.)

However, there is one workaround that may be helpful: If a Filter has been applied to a Form or Datasheet view then Find and Replace operations are restricted to the filtered rows. Access can take advantage of indexes and (at least some) server-side processing when applying a Filter to a form bound to an ODBC linked table, so you might be able to get the job done faster by Filtering the records first (even a non-sargable filter such as LIKE "*foo*"), and then performing the Find and Replace on the filtered set.


Use Database to Store Data Extracted From Internet

Sounds on the surface like a graph database problem. If you're going to be walking the edges between users, neo4j or such like may be the one for you.

You might be able to do more generic processing using a document db where every user has an _id of user_id and an array of followers _ids.

Perhaps you could output to MongoDb, then use Neo4j for creating the graph(s) for specialised work, and mongodb for more general work. MapReduce and the aggregation framework in MongoDb are pretty good (speaking from experience, although MapReduce is much more powerful than aggregrtion framework (currently)).

Since the schema is likely to morph, and you do not know what the additional data will be, you might prefer a doc or graph db over a RDB. If you prefer to work in a relational manner at a later point, you can generate csv extracts to upload to your RDBMS of choice after you have defined a schema.


Should database directory and file access be restricted to only the db user?

I'd say this is a good security practice to restrict OS access to your DB files.  First you should definitely restrict write privileges to DB files at the OS level, if not already configured that way.  Don't allow anyone other than the OS user that runs the DB to write to the DB files.  Practically any DB software I can think of restricts this by default.

If you want to additionally restrict read access, this would be a good idea, especially if your database contains sensitive information.  Any unprivileged OS user that shares your OS, if he/she has read-access to your DB files, they basically can get read access to your DB.  They could simply steal copies of your DB files, send them to their own server, and setup the DB system and grant for themselves unlimited access to that clone.  I'd say the majority of DB software does NOT proactively restrict read access at the OS level to the datafiles.  It's up to you, the DBA to restrict this.  If your data is super sensitive, you might want to consider encryption, which is available in most major DBMS.  If the datafiles are encrypted, a would-be information thief would most likely be foiled even if he/she made off with your datafiles.

Don't forget to encrypt your backups, too, since they are just as sensitive as the DB itself.

Generally, you don't need to worry about the impact of cutting off access to other OS tools like backup software.  Most DBMS can't and don't use any OS-based backup tools since DB files are changing.  Rather you use specific tools like Oracle RMAN, SQL Server native backup, Litespeed, mysqldump, etc.  These tools know how to handle a database that is changing and to get a proper backup.  And some other OS tools like antivirus scanners run with root/system authority, so you can't keep them from reading your datafiles by permissions alone (encryption can).

If you're doing this on Unix, it can sometimes be a matter of simply chmod 700 (or perhaps 750) on the directory that contains your DB files.  On Windows you could likely do this via NTFS permissions.