Listing the existing SQL Server Logins and Users

Not sure if there's a built-in way, but give this query a try for server principals:

;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

Basically what it does it gets the permissions granted and denied, and unions it with the role membership.  It should give you a brief look on the security for server logins.  Let me know if that's what you're looking for.


How to find if my innodb row uses overflow/off pages?

SHOW TABLE STATUS LIKE 'tablename'; -- There are 3 length columns (Data, Index, Free).  These are in bytes; divide by 16KB to get pages.

It is more complex to discover on-record vs off-record storage.

If all you want is whether it uses off-record pages, then that can probably be determined by the definition of the ROW_FORMAT.  For DYNAMIC, I think these are the rules:

Any column up to 40 bytes is stored on-record.
If all the rest of the columns will fit (in about 8KB), then put them on-record.
Spin off the longest rows first until the rest fit.

The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page.  ("all or none")

When a column is stored off-record, a 20-byte "pointer" is left on-record.  (There may be a 2-byte length in addition; I am not sure.)

Depending on what version of MySQL/MariaDB/Percona you are using, there are detailed statistics on the data and index BTrees.


SELECT  PID, count(distinct SurveyID) as totalsurvey
    FROM  locationsummary
    where  st_contains(ST_Envelope(ST_GeomFromText(
         'LineString(137.109375 21.47351753335, 87.890625 -22.411028521559)')),
              geopoint )
    group by  PID

This is slow only because ST_Contains in MySQL does not now, nor has it ever used an index. You have to use MbrContains for that. And you could never put anything on a spatial index, except spatial data.

I see you state in your question MBRContains do not use that Index. I don't believe you. ;)

Note, in PostGIS, ST_Contains will use an index.  And, moreover, you can create a spatial index and add to that index non-spatial data (thanks to btree_gist, and btree_gin).


Unable to start SQL Server Integration Services 12.0 service (SQL Server 2014) after upgrade to SQL Server 2019 and patched to CU4

I was able to successfully start the Integration Services 12.0 service after running a Repair using the SQL Server 2014 Installation Media. I then installed 2014 SP3 and 2014 SP3 CU4 to get it back to where it was before the upgrade, and all is well now.


SQL Table Design Schema - To Normalize or Not?

This looks like a school project correct?  I think the confusion is coming in because your example is very simple (perfectly appropriate for a class/training environment).  The design you have is considered best practice.  The reason it might seem simpler to go with the denormalized design is because you have so little data/so few columns.

If you add a dozen columns each to the Class and Subclass tables it starts to look worse.  Then if you add a million rows it gets even worse. When you design tables you can't always know in advance how big they will get and generally they will be wider than in your example.

Another reason why it's best practice is because you are logically separating your data.  What if you need to tie/use subclass without the class table?  Or class has an additional purpose.  If they are all in a single table you can't break them up like this.

Think of the same structure using School & ClassName.  The School table could be used for any number of other purposes (to tie employees to for example).  Same with the ClassName (list of required classes for a degree) table and even the join table (students taking the class at a given school).  But if you have a single wide table you wouldn't be able to use them that way.


All depends on their relationship. However, the way you describe it implies that this is a 1-to-1 relationship, since each schema refers to the other. Therefore, why not just combine the schemas?


How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)

Hints for join operations are used the same way as other hints, for instance

SELECT /*+ parallel(4) USE_MERGE(T1 T2)*/ T1.A
FROM ....

Optimizer chooses join algorithm based on different criteria. There are many things to be considered, but in general merge join is used when optimizer mode is ALL_ROWS (it prefers nested loops for FIRST_ROWS) , both tables involved in the operation have index on join column[s] ( it may still use merge join even if you don't have indexes, but hash_join_enabled set to false or using hash join estimated as more expensive).

The query with hint parallel(4) may potentially allocate 8 parallel servers (4 consumers, 4 producers).


Difference between an endpoint and a port in SQL Server

I like to think of it as the endpoint being the "SQL Server side" part of what a client connects to. There are endpoints for TCP (probably the one you refer to), Named Pipes and Shared Memory. Those are the regular endpoints that a client application connects to. You can, for instance, set privileges on an endpoint, allowing login A to connect to TCP and B connect to Shared Memory (not that I've ever had the need for this). The endpoint is implemented by a DLL (I usually refer to all this as "NetLib", and for TCP and Named Pipes exposed through a port.

Then there are endpoints for mirroring and the old SOAP web-methods as well.


How should a numeric value that is an external key be stored?

Before start, please note that external keys are usually bad as primary keys, because they are larger than needed, they can change and some people may not have one, or have duplicated ones (even if legally that shouldn't happen).

Speaking correctly, the EIN is a code of 9 digits. As such, the technically correct value should be a string. In particular, for MySQL (yes, the database and storage engine matters a lot, although not so much between MySQL or MariaDB), that should be (I'm assuming it is a not null field) a:

CHAR(9) charset ascii NOT NULL

That should only take 9 bytes of fixed space in most engines. Please remember to use ascii, and not other charset, as you may waste 3 or 4 bytes per character.

The reason to check if logically a number should be an integer or a string is checking if it has to be used in arithmetic operations and the ordering. If someone asks you to order the EINs, '000-00-0009' should be before '100-00-0000', so zeros are important.

Having said that, using something like

int(9) UNSIGNED ZEROFILL NOT NULL

Would take you less size (4 bytes) and on the command line client it will show you the preceding zeros:

mysql> SELECT * FROM ein;
+-----------+
| ein       |
+-----------+
| 123456789 |
| 000000001 |
+-----------+

One common misunderstanding of int(NUMBER), is that we are telling the integer that it can only be up to 9 digits. That is incorrect. An int will always take 4 bytes and, in the case of an unsigned int, it will take values from 0 to 4,294,967,295. Most applications will ignore the size anyway.

Less space means less amount of bytes read and written to disk, and more free space on memory. And even if 4 to 9 bytes may not be a lot, if you have 200 million records, that is 1GB less of information (more if you have into account indexes). If you do not have a large amount of records, that may not affect you so much, although depending on the operations performed (it may involve transforming it into a character), both approaches may have its downsides.

There is a last option, probably way worse in performance, but that could help with consistency. MySQL has the "NUMERIC/DECIMAL" datatype, so you could also define it as:

ein decimal(9,0) zerofill unsigned not null

That should only take 4 bytes in MySQL, but it disallows both non-number characters (something that char allows) and numbers > 999,999,999 and <0 (unlike int), so it may be a bit less error-prone, but usually fixed-point arithmetic takes more cpu cycles than simple integers.

In general, performance issues can only be checked once you have the whole system running– sometimes trying to over-optimize is a mistake if there are not so many records and it makes working with them more complicated. Stick with the easiest one at first and optimize later.


Mongo DB Memory Utilisation freezes app needs restart

Mongo will always use all free memory, eventually! That's why you don't put application(s) and mongodb to same server. However, you can use cgroups to restrict resource usage (memory, cpu,...) of mongo.


SQL log shipping from different hosts on different versions

As per the link in the comments, it looks like you can do a log shipping restore from a lower to higher version, but cannot make it standby (read only):
log ship from older version to newer one
I'll probably write up a separate question to see what alternatives there are (other than transactional replication... ideally looking for something uncomplicated).


SQL Server: What securables to enable a server role for read-write access to all DBs

To give access to a database you need to use database roles and database users but not server roles.  Please check difference between a user and a login

You can create a server role, add logins to that. Then you can add that role as a user in each database and add it as a member of a database role that has select, insert and update rights.

If you do this in the model database and then that would be copied to all user databases created after that.


What Permissions Are Needed for Selecting Data and Viewing All Schema?

Assign the VIEW ANY DEFINITION securable to the user.

That will allow them to see SP and view definitions but not allow them to change it.

https://technet.microsoft.com/en-us/library/ms175808(v=sql.105).aspx


New user creation option not available in SSMS sql server 2106 , how to enable?

My guess is that your installation of SSMS is messed up. Try using SSMS on a different machine and see if you get the same behaviour there.
If it is SSMS that is messed up, then I suggest you just install the most recent version of SSMS and work with that. Hopefully that will sort things out for you.
(I initially thought that you might not have permissions to create a user, but I tried that and SSMS still gave me a "new user" option, but at a later stage I would get an error that I don't have permissions to create a user.)
You can of course always use the CREATE USER command from a query window.


How do indices impact query performance?

It will take marginally longer to generate the plan when there are more indexes to consider, but I doubt the difference would be measurably significant.  The reasons for dropping an index does not list query performance.  On the other hand, in general you shouldn't create indexes unless you know they will be used to make a query more efficient.

From the Oracle Concepts Guide, here are the criteria for creating an index.

  In general, consider creating an index
  on a column in any of the following
  situations:

  The indexed columns are queried frequently and return a small
  percentage of the total number of rows
  in the table.
  A referential integrity constraint exists on the indexed column or
  columns. The index is a means to avoid
  a full table lock that would otherwise
  be required if you update the parent
  table primary key, merge into the
  parent table, or delete from the
  parent table.
  A unique key constraint will be placed on the table and you want to
  manually specify the index and all
  index options.

All indexes will be considered in the sense that all indexes on the tables in the query are examined to determine whether they could be used.  Those that could be are further examined to determine usefulness.


Diagnosing Blocking issues using Blocked Process report

waitresource="KEY: 10:72057594039238656 (594b17b7493c)" waittime="20631"
             ownerId="9710782702" transactionname="UPDATE"

The blocked process is waiting on a KEY (therefore on a row), for 20631 ms (~20s).

The blocking transaction is still executing a statement, likely the UPDATE that causes the blocking. It is not blocked, if it would be blocked then it wouldn't be the tip of the wait chain. Is executing, right now is suspended (not blocked!) meaning it just yielded CPU.

Looks like you have a long running UPDATE statement, most likely it has a missing index and triggers a table scan which blocks all readers. Use sys.dm_exec_sql_text on the handle 0x03000a00210f020741a1be0058a000000100000000000000 to retrieve the exact statement being executed.

BTW your blocked process is exactly the same UPDATE (same handle, same proc, same offsets) which is not surprising at all. Looks like your stored proc with object id 117575457 contains an UPDATE that is not optimized.


MySQL : if server_id is not explicitly set, then get error `ERROR 1200 (HY000) at line 1: The server is not configured as slave;`

Two servers are not allowed to have the same server_id. Also, If you omit --server-id from a master, the default ID is 0, in which case a master refuses connections from all slaves, and a slave refuses to connect to a master.

For more information, see Section 15.1.1.2, “Setting the Replication Slave Configuration”.

Even if both servers have different server_id values, please make sure the new master has binary logging enabled.


Yes
Yes, if you copy all dbpath files from primary (what is stopped; or you use file system snapshot), then local database is copied too
It depends.. If nothing is inserted to primary during rsync, about one second... If there is lot's of inserts, updates, deletes at primary, then more than few seconds.


which option is better for not slow down the performance of queries?

I would use a system where each payment is recorded separately

Typically, you'd want to keep the "balance" consistent over time and this requires them to be recorded individually.

the ability to link payments
record cancellations separately

I would never concatenate previous payments into one opaque string.

I also wouldn't worry about performance yet: this is premature optimisation. If you do expect 100s of millions of rows, there are other techniques to improved performance. Otherwise, concentrate on data integrity and correctness


How to close all query windows in DBeaver?

You can right-click on any tab then choose "Close All":

In order to automatically close database object editors when exiting the application, you can uncheck this option: "Reopen database editors after restart":

Note that this will only close database editors (tables, schemas...) and it won't affect text editors. There's currently (DBeaver 5.1.2) no option to automatically close text editors when exiting the application. The feature request is tracked on this issue.


Best practice to recover an CRUD statement to a linked server if connection is lost

Instead of having the trigger rely on the linked server being available, use a queue table. Have a background job that can use sp_testlinkedserver and process all of the items in the queue only when the server is available. It can use TRY/CATCH as well, and only mark those as processed when they are successfully updated, otherwise it will continue trying them every time the background job runs.

This allows you to continue updating the local table and not worry about the connection to the other server (and not making your local transactions wait on updating a remote server).


How to export image filenames from Wordpress database?

Pure MySQL, be sure to correct it for your desired filepath /tmp/filenames.log: (Thanks Phil for the select query)

"SELECT meta_value AS filename
FROM wp_posts p, wp_postmeta pm
WHERE p.id = pm.post_id
AND p.post_type = 'attachment'
AND meta_key = '_wp_attached_file'
INTO OUTFILE '/tmp/filenames.log'
LINES TERMINATED BY '\n';"


Both AG and DBM will replicate primarily the log being generated on the principal. The buffer flush rate has some relation to the the log generation rate, but is not identical (think how 100 updates of the same row will generate 100 log records but will only flush the page once, and also think how a single insert could be described in maybe 256 bytes of log, but it dirties an entire page that, when flushed, writes 8kb) and the counters are not per database to start with.

There are some additional control messages and when AG is present there is extra logging, but not significant. I would say that the log itself is about 95% of the traffic. On one hand keep in mind that both DBM and AG can and will compress the log in traffic. But on the other hand DBM and AG will require fully logged operation (minimal logging cannot occur on a AG/DBM source). With AG the number of replicas also plays a role (at worse the traffic gets multiplied x times number of replicas).

You need to consider not only throughput, but also latency. In synchronous mode commits have to be hardened by the replica and this impact the operation latency. Check your current log flush rate in SQL Server, Databases Object.

Once established both DBM and AG have their own counters: SQL Server, Database Mirroring Object and SQL Server, Availability Replica. The raw traffic (including protocol overhead) can be measured also in SQL Server, Broker / DBM Transport Object. Of course, these require to first establish an AG and hit it with load :)

But for an estimate of number of bytes that will be sent (before compression), the log file virtual io stats, as suggested by mrdenny, is a very good aggregate indicator, but unfortunately it gives no information on the actual shape of the throughput (ie. spikes). Unfortunately there is no direct perfmon counter for that.


Dynamic Table partitioning on a daily basis

From SQL Server 2008 SP2 and SQL Server 2008 R2 SP1 there is support for 15,000 partitions so to be honest, you don't really need to do that much dynamically.  Instead of having a complex daily process (Dynamically add filegroup and boundaries) with an opportunity to fail, simply create the partitions up-front from now to the year 2020, and you're well within your limits and pretty future-proof.

You could assign all partitions to one filegroup (ok not necessarily a great pattern), or round-robin between a limited number.  To put it another way, there is no technical need to have a filegroup per day, eg

-- Assign all to one filegroup; ok not necessarily great
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO ( [FG1] )

-- Or round-robin
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG1] ... etc )

Obviously use Excel or some tool to generate the scripts for you - no need to type them out : )

Use DMV sys.partition_range_values and metadata function $PARTITION to work out information about what data is where.  Create a daily job to switch out and truncate your oldest partition.  I would regard this as lower risk than the daily add.

Warning!! Carefully read the whitepaper as this needs to be enabled and there are some issues with this approach ( eg Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is not supported ).  If you're feeling risk averse the standard limit of 1,000 partitions would still allow you to pre-allocate just under 3 years.

As you really want to partition by DATE not DATETIME2, consider a computed column.  I would probably want to performance test this first though.

There is also a tool on codeplex (SQL Server Partition Management) which might be worth a look although I haven't used it.

To answer your other questions, there should only be one log file for the database.  Add other files as .ndf not .mdf.  To use the same partition scheme (not function) on the same tables, simply create them with the same scheme, they will divide the data up into the files beneath the filegroups eg

CREATE TABLE dbo.yourTable (    ...

    CONSTRAINT PK_yourTable PRIMARY KEY ( rowId, someDate )

    ) ON ps_test(someDate)

OK, this will make for a long answer but I've knocked up a demo of how something like this might work.  It does remind me that the great thing about partition switching is that as a metadata-only operation it's instant.  Just to be clear, this is a demo to show off principles and some example "how-to" code, it is not production quality.  Work through it and make sure you understand it before running in a dev or test environment.  You'll need about 200MB space.

------------------------------------------------------------------------------------------------
-- Setup START
-- Demo runs on my laptop in < 1 minute (ok on SSD)
-- You'll need 200MB space
------------------------------------------------------------------------------------------------

USE master
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    ALTER DATABASE tooManyPartitionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    DROP DATABASE tooManyPartitionsTest
GO
CREATE DATABASE tooManyPartitionsTest
GO

ALTER DATABASE tooManyPartitionsTest SET RECOVERY SIMPLE
GO

-- Add 7 filegroups with 4 files each
-- Add 365 files and filegroup
DECLARE @fg INT = 0, @f INT = 0, @sql NVARCHAR(MAX)

WHILE @fg < 7
BEGIN

    SET @fg += 1
    SET @sql = 'ALTER DATABASE tooManyPartitionsTest ADD FILEGROUP tooManyPartitionsTestFg' + CAST( @fg AS VARCHAR(5) )

    -- Add the filegroup
    PRINT @sql
    EXEC(@sql)

    -- Initialise
    SET @f = 0

    WHILE @f < 4
    BEGIN

        SET @f += 1
        --!!WARNING!! DON'T USE THESE SETTINGS IN PRODUCTION.  3MB starting size and 1MB filegrowth are just for demo - would be extremely painful for live data
        SET @sql = 'ALTER DATABASE tooManyPartitionsTest ADD FILE ( NAME = N''tooManyPartitionsTestFile@f_@fg'', FILENAME = N''s:\temp\tooManyPartitionsTestFile@f_@fg.ndf'', SIZE = 3MB, FILEGROWTH = 1MB ) TO FILEGROUP [tooManyPartitionsTestFg@fg]'
        SET @sql = REPLACE ( @sql, '@fg', @fg )
        SET @sql = REPLACE ( @sql, '@f', @f )

        -- Add the file
        PRINT @sql
        EXEC(@sql)

    END

END
GO

USE tooManyPartitionsTest
GO

SELECT * FROM sys.filegroups
SELECT * FROM sys.database_files
GO

-- Generate partition function with ~3 years worth of daily partitions from 1 Jan 2014.
DECLARE @bigString NVARCHAR(MAX) = ''

;WITH cte AS (
SELECT CAST( '30 Apr 2014' AS DATE ) testDate
UNION ALL
SELECT DATEADD( day, 1, testDate )
FROM cte
WHERE testDate < '31 Dec 2016'
)
SELECT @bigString += ',' + QUOTENAME( CONVERT ( VARCHAR, testDate, 106 ), '''' )
FROM cte
OPTION ( MAXRECURSION 1100 )

SELECT @bigString = 'CREATE PARTITION FUNCTION pf_test (DATE) AS RANGE RIGHT FOR VALUES ( ' + STUFF( @bigString, 1, 1, '' ) + ' )'
SELECT @bigString bs

-- Create the partition function
PRINT @bigString
EXEC ( @bigString )
GO

/*
-- Look at the boundaries
SELECT *
FROM sys.partition_range_values
WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' )
GO
*/

DECLARE @bigString NVARCHAR(MAX) = ''

;WITH cte AS (
SELECT ROW_NUMBER() OVER( ORDER BY boundary_id ) rn
FROM sys.partition_range_values
WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' )
UNION ALL
SELECT 1    -- additional row required for fg
)
SELECT @bigString += ',' + '[tooManyPartitionsTestFg' + CAST( ( rn % 7 ) + 1 AS VARCHAR(5) ) + ']'
FROM cte
OPTION ( MAXRECURSION 1100 )

SELECT @bigString = 'CREATE PARTITION SCHEME ps_test AS PARTITION pf_test TO ( ' + STUFF( @bigString, 1, 1, '' ) + ' )'
PRINT @bigString
EXEC ( @bigString )
GO

IF OBJECT_ID('dbo.yourLog') IS NULL
CREATE TABLE dbo.yourLog (
    logId       INT IDENTITY,
    someDate    DATETIME2 NOT NULL,
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded   DATETIME DEFAULT GETDATE(),
    addedBy     VARCHAR(30) DEFAULT SUSER_NAME(),

    -- Computed column for partitioning?
    partitionDate AS CAST( someDate AS DATE ) PERSISTED,

    CONSTRAINT pk_yourLog PRIMARY KEY ( logId, partitionDate )  -- << !!TODO try other way round

    ) ON [ps_test]( partitionDate )
GO

IF OBJECT_ID('dbo.yourAcks') IS NULL
CREATE TABLE dbo.yourAcks (
    ackId           INT IDENTITY(100000,1),
    logId           INT NOT NULL,
    partitionDate   DATE NOT NULL

    CONSTRAINT pk_yourAcks PRIMARY KEY ( ackId, logId, partitionDate )

    ) ON [ps_test]( partitionDate )
GO

IF OBJECT_ID('dbo.yourLogSwitch') IS NULL
CREATE TABLE dbo.yourLogSwitch (
    logId       INT IDENTITY,
    someDate    DATETIME2 NOT NULL,
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
    dateAdded   DATETIME DEFAULT GETDATE(),
    addedBy     VARCHAR(30) DEFAULT SUSER_NAME(),

    -- Computed column for partitioning?
    partitionDate AS CAST( someDate AS DATE ) PERSISTED,

    CONSTRAINT pk_yourLogSwitch PRIMARY KEY ( logId, partitionDate )

    ) ON [ps_test]( partitionDate )
GO
-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Data START
------------------------------------------------------------------------------------------------

-- OK load up data for Jan 2014 to today.
DECLARE @startDate DATETIME = '1 Jan 2014', @rand INT

WHILE @startDate < GETDATE()
BEGIN

    -- Add between 1 and 10,000 rows to dbo.yourLog for today
    SET @rand = RAND() * 10000

    ;WITH cte AS (
    SELECT TOP 10000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
    FROM master.sys.columns c1
        CROSS JOIN master.sys.columns c2
        CROSS JOIN master.sys.columns c3
    )
    INSERT INTO dbo.yourLog (someDate)
    SELECT TOP(@rand) DATEADD( second, rn % 30000, @startDate )
    FROM cte

    -- Add most of the Acks
    INSERT INTO dbo.yourAcks ( logId, partitionDate )
    SELECT TOP 70 PERCENT logId, partitionDate
    FROM dbo.yourLog
    WHERE partitionDate = @startDate

    SET @startDate = DATEADD( day, 1, @startDate )

    CHECKPOINT

END
GO

-- Have a look at the data we've loaded
SELECT 'before yourLog' s, COUNT(*) records, MIN(someDate) minDate, MAX(someDate) maxDate FROM dbo.yourLog
SELECT 'before yourAcks' s, COUNT(*) records, MIN(partitionDate) minDate, MAX(partitionDate) maxDate FROM dbo.yourAcks

-- You'll see how pre-May data is initially clumped together
SELECT 'before $partition' s, $PARTITION.pf_test( partitionDate ) p, MIN(partitionDate) xMinDate, MAX(partitionDate) xMaxDate, COUNT(*) AS records
FROM dbo.yourLog WITH(NOLOCK)
GROUP BY $PARTITION.pf_test( partitionDate )
ORDER BY xMinDate
GO

-- Data END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Maintenance START
------------------------------------------------------------------------------------------------

-- Oh man, we're behind with our switching and truncation.
-- Create a job that sweeps up.  Do we get blocking?

-- ALTER TABLE dbo.yourLog SWITCH PARTITION 1 TO dbo.yourLogSwitch PARTITION 1
-- TRUNCATE TABLE dbo.yourLogSwitch

-- Let's pretend we only want to maintain up to 30 days ago
DECLARE @testDate DATE
SET @testDate = DATEADD( day, -30, GETDATE() )

-- Create local fast_forward ( forward-only, read-only ) cursor
DECLARE partitions_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT boundary_id, CAST( value AS DATE )
FROM sys.partition_range_values
WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' )
  AND value < @testDate

-- Cursor variables
DECLARE @boundary_id INT, @value DATE, @sql NVARCHAR(MAX)

OPEN partitions_cursor

FETCH NEXT FROM partitions_cursor INTO @boundary_id, @value
WHILE @@fetch_status = 0
BEGIN

    -- Switch out and truncate old partition
    SET @sql = 'ALTER TABLE dbo.yourLog SWITCH PARTITION ' + CAST( @boundary_id AS VARCHAR(5) ) + ' TO dbo.yourLogSwitch PARTITION ' + CAST( @boundary_id AS VARCHAR(5) )

    PRINT @sql
    EXEC(@sql)

    -- You could move the data elsewhere from here or just empty it out
    TRUNCATE TABLE dbo.yourLogSwitch

    --!!TODO yourAcks table

    FETCH NEXT FROM partitions_cursor INTO @boundary_id, @value
END

CLOSE partitions_cursor
DEALLOCATE partitions_cursor
GO

-- Maintenance END
------------------------------------------------------------------------------------------------

-- Have a look at the data we've maintained
SELECT 'after yourLog' s, COUNT(*) records, MIN(someDate) minDate, MAX(someDate) maxDate FROM dbo.yourLog
SELECT 'after yourAcks' s, COUNT(*) records, MIN(partitionDate) minDate, MAX(partitionDate) maxDate FROM dbo.yourAcks

-- You'll see how pre-May data is initially clumped together
SELECT 'after $partition' s, $PARTITION.pf_test( partitionDate ) p, MIN(partitionDate) xMinDate, MAX(partitionDate) xMaxDate, COUNT(*) AS records
FROM dbo.yourLog WITH(NOLOCK)
GROUP BY $PARTITION.pf_test( partitionDate )
ORDER BY xMinDate

-- Remember, date must always be part of query now to get partition elimination
SELECT *
FROM dbo.yourLog
WHERE partitionDate = '1 August 2014'
GO

-- Cleanup
USE master
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    ALTER DATABASE tooManyPartitionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
    DROP DATABASE tooManyPartitionsTest
GO


How to test encryption on Amazon RDS MySQL?

Whether encrypted RDs is disk/file level encrypted or db level encrypted? I have done some searching, but could bot able to get a clear answer.

The encryption is done at the file/disk level. From this page, emphasis mine:

  Amazon RDS encrypted instances provide an additional layer of data protection by securing your data from unauthorized access to the underlying storage.

Also to highlight exactly what is encrypted, we have this quote:

  All logs, backups, and snapshots are encrypted for an Amazon RDS encrypted instance. A Read Replica of an Amazon RDS encrypted instance is also encrypted using the same key as the master instance.

To answer your question about confirming that the RDS is encrypted, because you do not have access to the OS that RDS runs on the only method you have is to verify the backups/snapshots are encrypted.

To download a snapshot, you can use the console (or the rds-copy-db-snapshot tool).

The keys used to encrypt/decrypt them should be found in the KMS section of the console.


Error code 1290 and inability to import data on table

As per MySQL documentation

This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.

So one solution is to place your files under the value displayed under this variable

if the directory doesn't exist create it as being displayed and make sure mysql user have access on it.

 show variables like '%secure_file_priv%';

or change the value in my.ini/cnf file as this variable is read-only and would require a restart

if you want to disable this restriction and can use any directory set it to empty value or change it according to your need

[mysqld]
secure_file_priv=new_path

if you want to disable the feature of loading-unloading set it's values to NULL

Hope it helps


Running queries with SQLCMD vs. Running queries with SSMS

The differences are probably due to SET settings, such as SET ARITHABORT. What happens is that these settings are taken into account when deciding on the plan to use, and sometimes you can get stuck with a really bad plan with one setting (and this probably happened because that application used non-optimal parameters), and the opposite setting has a good plan.

You can discover what settings are in play by checking sys.dm_exec_requests and sys.dm_exec_sessions when the query is running. The latter has columns for various settings (but you'll have to figure out the session_id from the former). Once you've discovered the differences, you can manually set the setting to the "better" setting from sqlcmd, and hope that you now get the better plan. Or, you can simply issue WITH RECOMPILE to avoid the parameter sniffing issue in the first place.

A really useful resource to read thoroughly is:

Slow in the Application, Fast in SSMS?


Insert a large TEXT. Is INSERT the only way?

I recommend avoiding putting more than 16MB (perhaps less) in a column in MySQL.  There are many limitations in MySQL and possible in the companion tools you are using.

For storing Text (as opposed to blobs) in a column, I would strongly consider compressing it.  This is likely to shrink the data by 3x.  Do the compression in the Client, not the Server.

What is the Text for?  How will be it be used eventually?  An analogy: with images (which must be stored in BLOB/MEDIUMBLOB/LONGBLOB), it can be better to store them as files when the use is HTML's <img src=file...>.  This makes the fetching trivial.

I once had the task of storing large data, even multi-GB movies.  Obviously, I could not even use LONGBLOB (4GB limit).  I chose to break the stream into 50KB (pick a number) chunks and store them in a BLOB in rows of a table.  Together with it was a sequence number to allow reconstructing the result.  This allowed me to "stream" things rather than "store (or fetch) it all at once".  Hence I was not straining the limits of the client software (which was PHP and Apache).

As for inserting blob (or compressed text), it may be optimal to convert it to hex, then use UNHEX("...") in the INSERT statement.  This completely avoids any issue of escaping strings, etc.  (But does not avoid SQL Injection problems.)  It does, however, cut in half some of the limits you may encounter.


What's the maximum number of listeners an Oracle database can have?

To the best of my knowledge, there is no limit.  Of course, it is exceptionally rare that you would have more than one listener for a database.  I've seem people run two listeners just in case one listener fails but I've never heard of anyone wanting to run more than two.  If you really wanted to, though, nothing stops you from running a dozen listeners on a dozen different ports or on a dozen different machines.  But since the listener is virtually never the bottleneck and the listener virtually never fails, that sort of redundancy is rarely helpful.


MS SQL Server autogrow configuration with heavy loaded database

1 MB is not the right choice for autogrowth !

You should always set auto growth to a sensible value that suits your environment. I tend to capture the autgrowth value for a month and then average it out to give me a ball park figure of autogrowth for my database .

I have provided a sample script here as well. Also refer to Mike Walsh's answer.

The default trace keeps track of AUTOGROWTH events out-of-the box. Below script will give you a good starting point.

Also, Instant file initialization is a boon when auto growth kicks in (ONLY applies to data files).

IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
    DROP TABLE #autogrowthTotal;
IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
    DROP TABLE #autogrowthTotal_Final;

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

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

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

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

-- process all trace files
SELECT
  ftg.StartTime
  ,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.[FileName] as LogicalFileName
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
,mf.physical_name AS PhysicalFileName
into #autogrowthTotal
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
join sys.master_files mf on (mf.database_id = ftg.databaseid) and (mf.name = ftg.[FileName])
WHERE (ftg.EventClass = 92  -- Data File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime

select count(1) as NoOfTimesEventFired
        , CONVERT(VARCHAR(10), StartTime, 120) as StartTime
        , EventName
        , DatabaseName
        , [LogicalFileName]
        , PhysicalFileName
        , SUM(GrowthMB) as TotalGrowthMB
        , SUM(DurMS) as TotalDurationMS
into #autogrowthTotal_Final
from #autogrowthTotal
group by CONVERT(VARCHAR(10), StartTime, 120),EventName,DatabaseName, [LogicalFileName], PhysicalFileName
--having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
order by CONVERT(VARCHAR(10), StartTime, 120)

select * from #autogrowthTotal_Final

Original Script from SimpleTalk.com (modified a bit to help me for reporting).


Updating table based on conditions (overlapping dates)

Condition 1:

WITH ord as (
    SELECT ID, CustomerID, CheckInDate, CheckOutDate
        , n = ROW_NUMBER() over(partition by [CustomerID] order by [CheckInDate], [CheckOutDate])
    FROM @data d1
), first as (
    SELECT o1.ID, o1.CustomerID, o1.CheckInDate, o1.CheckOutDate, o1.n
        , m = ROW_NUMBER() over(partition by o1.[CustomerID] order by o1.[CheckInDate], o1.[CheckOutDate])
    FROM ord o1
    INNER JOIN ord o2 ON o1.CustomerID = o2.CustomerID AND o2.n+1 = o1.n AND o1.CheckInDate > o2.CheckOutDate
), groups as (
    SELECT o.ID, o.CustomerID, nx = MIN(coalesce(f.n, 1)), n = MAX(o.n)
        , p = ROW_NUMBER() over(partition by o.CustomerID, MIN(coalesce(f.n, 1)) ORDER BY o.ID)
    FROM ord o
    LEFT JOIN first f ON o.CustomerID = f.CustomerID AND o.n < f.n
    GROUP BY o.ID, o.CustomerID
), dates as (
    SELECT g.CustomerID, g.nx, CheckInDate = MIN(o.CheckInDate)
        , CheckOutDate = CASE WHEN SUM(CASE WHEN o.CheckOutDate IS NULL THEN 1 END) IS NULL THEN MAX(o.CheckOutDate) END
    FROM groups g
    INNER JOIN ord o ON g.ID = o.ID
    GROUP BY g.nx, g.CustomerID
    HAVING COUNT(g.nx) > 1
)
SELECT o.ID, o.CustomerID
    , CheckInDate = CASE WHEN g.p = 1 THEN d.CheckInDate END
    , CheckOutDate = CASE WHEN g.p = 1 THEN d.CheckOutDate END
FROM groups g
INNER JOIN ord o ON g.ID = o.ID
INNER JOIN dates d on g.CustomerID = d.CustomerID AND g.nx = d.nx
ORDER BY ID

This query output the rows that must be updated:

ord = I first partition by CustomerID and order by CheckInDate
first = I join consecutive rows from previous CTE where the next CheckInDate does not overlap previous CheckInDate and CheckDate and I partition and order them
groups = I group by the previous partitioned number in order to know to which group a row belongs
dates = I join with the original data in order to get the first and last date for each groups. Groups with only 1 row are removed
main select output the dates for p=1 or NULL otherwise

Output:

ID  CustomerID  CheckInDate CheckOutDate
1   1           2015-03-04  NULL
3   1           NULL        NULL
4   1           NULL        NULL

Condition 2:

WITH ord as (
    SELECT ID, CustomerID, CheckInDate, CheckOutDate
        , n = ROW_NUMBER() over(partition by [CustomerID] order by [CheckInDate], [CheckOutDate])
    FROM @data d1
), first as (
    SELECT o1.ID, o1.CustomerID, o1.CheckInDate, o1.CheckOutDate, o1.n
        , m = ROW_NUMBER() over(partition by o1.[CustomerID] order by o1.[CheckInDate], o1.[CheckOutDate])
    FROM ord o1
    INNER JOIN ord o2 ON o1.CustomerID = o2.CustomerID AND o2.n+1 = o1.n AND o1.CheckInDate > o2.CheckOutDate
), groups as (
    SELECT o.ID, o.CustomerID, nx = MIN(coalesce(f.n, 1)), n = MAX(o.n)
        , p = ROW_NUMBER() over(partition by o.CustomerID, MIN(coalesce(f.n, 1)) ORDER BY o.ID)
        , last = ROW_NUMBER() over(partition by o.CustomerID, MIN(coalesce(f.n, 1)) ORDER BY o.ID DESC)
    FROM ord o
    LEFT JOIN first f ON o.CustomerID = f.CustomerID AND o.n < f.n
    GROUP BY o.ID, o.CustomerID
), dates as (
    SELECT g.CustomerID, g.nx, CheckInDate = MIN(o.CheckInDate)
        , CheckOutDate = MAX(o2.CheckOutDate)
    FROM groups g
    INNER JOIN ord o ON g.ID = o.ID
    INNER JOIN (SELECT ID, CustomerID, nx FROM groups WHERE last = 1) l ON g.CustomerID = l.CustomerID AND g.nx = l.nx
    INNER JOIN ord o2 ON l.ID = o2.ID
    GROUP BY g.nx, g.CustomerID
    HAVING COUNT(g.nx) > 1
)
SELECT o.ID, o.CustomerID
    , CheckInDate = CASE WHEN g.p = 1 THEN d.CheckInDate END
    , CheckOutDate = CASE WHEN g.p = 1 THEN d.CheckOutDate END
FROM groups g
INNER JOIN ord o ON g.ID = o.ID
INNER JOIN dates d on g.CustomerID = d.CustomerID AND g.nx = d.nx
ORDER BY ID

Output:

ID  CustomerID  CheckInDate CheckOutDate
1   1           2015-03-04  2015-05-03
3   1           NULL        NULL
4   1           NULL        NULL

For Updates, replace SELECT by UPDATE:

UPDATE g SET
    CheckInDate = CASE WHEN g.p = 1 THEN d.CheckInDate END
    , CheckOutDate = CASE WHEN g.p = 1 THEN d.CheckOutDate END
FROM ...

Your data:

declare @data table([ID] int, [CustomerID] int, [CheckInDate] date, [CheckOutDate] date);
Insert into @data([ID], [CustomerID], [CheckInDate], [CheckOutDate])
VALUES
    (1, 1, '2015-04-02', '2015-04-05'),
    (2, 2, '2015-03-04', '2015-05-02'),
    (3, 1, '2015-04-01', NULL),
    (4, 1, '2015-03-04', '2015-05-03'),
    (5, 1, '2015-01-03', '2015-02-03')
;

It works as well with this sample:

(1, 1, '2015-04-02', '2015-04-05'),
(2, 2, '2015-03-04', '2015-05-02'),
(3, 1, '2015-04-01', NULL),
(4, 1, '2015-03-04', '2015-05-03'),
(5, 1, '2015-01-03', '2015-02-03'),
(6, 1, '2015-01-02', '2015-02-03'),
(7, 1, '2015-03-04', '2015-03-06'),
(8, 1, '2015-03-04', '2015-05-06'),
(9, 1, '2014-04-02', '2014-04-05'),
(10, 1, '2014-03-04', '2014-05-02')

If it does not work with some of your data, update Input and Output table with more relevant values.


How to best use connection pooling in SQLAlchemy for PgBouncer transaction-level pooling?

with PGBouncer, you'd probably want to just stick with NullPool.  In that case you may be able to share a single Engine across subprocesses since no socket connections will be carried over the subprocess boundary.  But you can't share anything referring to a Connection object, like a Session with an active transaction, over this boundary.    You definitely wouldn't want to do "engine-per-request" though, an Engine is an expensive object that accumulates a lot of information about a particular database URL the first time it sees it.


Hardware Cluster to VMware, is vMotion an alternative for clustering?

vMotion and clustering aren't alternatives for one another. vMotion moves the same "machine" (albeit virtual) from one physical host to another.

This provides some sort of resilience to hardware failure (unless it's the shared SAN bombing out) but you can't fail over to another host if you would like to create a maintenance window with minimal downtime for example. vMotion won't help you if you need to patch the (virtual) servers without downtime.

If you need the clustering for other things than hardware failure resilience I wouldn't rely on the VMware features but I would create 2 VM's on separate physical boxes and create a regular cluster between the 2 VM's.

vMotion adds a few features like being able to move VM's between hosts to redistribute load and hopefully recover from a hardware failure but is not an alternative for clustering. Clustering between 2 VM's is not different from clustering between 2 physical machines.


slow log contains query, but query time is less than long_query_time

If you add to my.cnf ( or my.ini )

min_examined_row_limit=1

the line should be omitted in the slow query log - in this case, no rows were examined.


I assume you realize that the two servers both need to have access to the WAL archive - so if you are using commands such as 'cp' then you would have to be using NFS or some other similar technology to achieve it.

The contents of recovery.conf on the slave refer to processes on the slave, so the path you should use is the path to the WAL archive dir as seen on the slave.

The path specified in archive_command in postgresql.conf on the master refers to the WAL archive dir as seen on the master.

I recommend if at all possible you align the machines so that the path is the same (which you can do via symlinks or by changing mount points). When you use pg_basebackup to create your slave, it copies across complete contents of the master including config files, so it makes life easier if the same paths are valid in both machines.


Is it bad to shrink + reindex after removing a significant part of data?

Well, ask yourself: What is the point of shrinking, if you know the reindex (and any other data changes, really) will expand the file again after the shrink?

Even if the reindex doesn't bring it back up to 9GB, if the database is going to grow in the future, I see absolutely zero value in reclaiming some of that disk space temporarily. What will you do with that space until the database has to grown again? Lease it out and then evict them? This is like a restaurant disassembling a table every time a customer gets up and leaves, only to reassemble it when a new customer arrives.

In your situation (where you need to keep the data file under 10GB because of the license you chose and no other reason), there is no point going down to 5GB just to grow back to 9GB again. I would rather have some active monitor that warns you when the data file is getting back up into the 9GB space again (or just keep it at 9GB!) and the space used is actually approaching 9GB.

Instead of thinking about it as a "keep the last three months" requirement, think of it as "keep 9GB of data" requirement. Which you'll have to revisit as your data volumes per time period (day/week/whatever) go up anyway. What happens when you amass more than 10GB of data inside of three months?


In a ready-heavy environment, a MyISAM table behaves a like a prioritized queue.

SELECTs will make all DDL, INSERTs, UPDATEs, & DELETEs, wait until all SELECTs are done
A single write operation makes all SELECT wait. The exception to this rule is Concurrent INSERT. The environment for Concurrent INSERTs:

Only INSERTs and SELECTs
MyISAM tables must not contain any gaps

In other words, SELECTs are not blocked on a MyISAM table as long as newly INSERTs rows are entering a table with no gaps. IF any row being INSERTed has to fill any gaps, then the conditions for Concurrent INSERTs are no longer applicable. SELECTs go back to being handled the way they are normally handled.

If you are performing bulk loading of a MyISAM table, you will need to rev up certain things. For instance, you will need to add this option and restart mysql:

[mysqld]
bulk-insert-buffer-size=256M

Next, change the way you delete the data. Instead of running the DELETE query, try copying the data to be retained into a temp table and then rename. For example, if you have to delete rows from table mydb.mytb whose id <= 500000, run these steps (it should be faster):

use mydb
create table mybt like mytb;
alter table mybt disable keys;
insert into mybt select * from mytb where id > 500000;
alter table mybt enable keys;
drop table mytb;
alter table mybt rename mytb;

Instead of lots of INSERTs into your table, try putting all your new data into a CSV file and use LOAD DATA INFILE to mass populate your production table. You should disable keys before loading and enable keys after the new data is loaded.

There is no need to optimize table doing these things. You may want to run analyze table instead. That will update the index statistics on the MyISAM table.

I hope these suggestions help !!!


Importing MySQL database using Linux Terminal

You have to add the target database name.

$mysql -u root -p -h localhost

mysql> create database foo;
mysql> exit;

$mysql -u username -p -h localhost DATA-BASE-NAME < data.sql


Performance implications of MySQL VARCHAR sizes

You must realize the tradeoffs of using CHAR vs VARCHAR

With CHAR fields, what you allocate is exactly what you get. For example, CHAR(15) allocates and stores 15 bytes, no matter how characters you place in the field. String manipulation is simple and straightforward since the size of the data field is totally predictable.

With VARCHAR fields, you get a completely different story. For example VARCHAR(15) actually allocates dynamically up to 16 bytes, up to 15 for data and, at least, 1 additional byte to store the the length of the data. If you have the string 'hello' to store that will take 6 bytes, not 5. String manipulation must always perform some form of length checking in all cases.

The tradeoff is more evident when you do two things:
1. Storing millions or billions of rows
2. Indexing columns that are either CHAR or VARCHAR

TRADEOFF #1

Obviously, VARCHAR holds the advantage since variable-length data would produce smaller rows and, thus, smaller physical files.

TRADEOFF #2

Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields. This is not any conjecture on my part. The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:

ALTER TABLE tblname ROW_FORMAT=FIXED;

This directive forces are VARCHARs to behave as CHARs. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%, without changing anything else. It worked as published. However, it did produce a table almost double in size, but that simply goes back to tradeoff #1.

You could analyze the data being stored to see what MySQL recommends for column definition. Just run the following against any table:

SELECT * FROM tblname PROCEDURE ANALYSE();

This will traverse the entire table and recommend column definitions for every column based on the data it contains, the minimum field values, maximum field values, and so forth. Sometimes, you just have to use common sense with planning CHAR vs VARCHAR. Here is a good example:

If you are storing IP addresses, the mask for such a column is at most 15 characters (xxx.xxx.xxx.xxx). I would jump right at CHAR(15) in a heartbeat because the lengths of IP addresses will not vary all that much and the added complexity of string manipulation controlled by an additional byte. You could still do a PROCEDURE ANALYSE() against such a column. It may even recommend VARCHAR. My money would still be on CHAR over VARCHAR in this instance.

CHAR vs VARCHAR issues can be resolved only through proper planning. With great power comes great responsibility (cliche but true)


What corner cases exist when relying on undocumented behaviour to determine values selected by MySQL for hidden columns in GROUP BY operations?

I was thinking the NATURAL JOIN example you just used

SELECT * FROM my_table NATURAL JOIN (
  SELECT   group_col, MAX(sort_col) sort_col
  FROM     my_table
  GROUP BY group_col
) t

If you shift to another type of JOIN and impose WHERE, ordering can come and go without warning in spite of the ill-advised reliance on undocumented behavior of the GROUP BY.

For this example, I will

use Windows 7
use MySQL 5.5.12-log for Windows
create some sample data
impose a LEFT JOIN without a WHERE clause
impose a LEFT JOIN with a WHERE clause

For the DB Environment

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.12-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like '%version_co%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
3 rows in set (0.00 sec)

mysql>

Using this script to generate sample data

DROP DATABASE IF EXISTS eggyal;
CREATE DATABASE eggyal;
USE eggyal
CREATE TABLE groupby
(
    id int not null auto_increment,
    num int,
    primary key (id)
);
INSERT INTO groupby (num) VALUES
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
(floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp()));
INSERT INTO groupby (num) SELECT num FROM groupby;
SELECT * FROM groupby;

and these two queries for testing the GROUP BY subsequent use;

SELECT * FROM groupby A LEFT JOIN
(
    SELECT   num, MAX(id) id
    FROM     groupby
    GROUP BY num
) B USING (id);
SELECT * FROM groupby A LEFT JOIN
(
    SELECT   num, MAX(id) id
    FROM     groupby
    GROUP BY num
) B USING (id) WHERE B.num IS NOT NULL;

Let's test the durability of the GROUP BY's results;

STEP 01 : Create the Sample Data

mysql> DROP DATABASE IF EXISTS eggyal;
Query OK, 1 row affected (0.09 sec)

mysql> CREATE DATABASE eggyal;
Query OK, 1 row affected (0.00 sec)

mysql> USE eggyal
Database changed
mysql> CREATE TABLE groupby
    -> (
    ->     id int not null auto_increment,
    ->     num int,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO groupby (num) VALUES
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp())),
    -> (floor(rand() * unix_timestamp())),(floor(rand() * unix_timestamp()));
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO groupby (num) SELECT num FROM groupby;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM groupby;
+----+------------+
| id | num        |
+----+------------+
|  1 |  269529129 |
|  2 |  387090406 |
|  3 | 1126864683 |
|  4 |  411160755 |
|  5 |   29173595 |
|  6 |  266349579 |
|  7 | 1244227156 |
|  8 |    6231766 |
|  9 |  269529129 |
| 10 |  387090406 |
| 11 | 1126864683 |
| 12 |  411160755 |
| 13 |   29173595 |
| 14 |  266349579 |
| 15 | 1244227156 |
| 16 |    6231766 |
+----+------------+
16 rows in set (0.00 sec)

STEP 02 : Use LEFT JOIN without a WHERE clause

mysql> SELECT * FROM groupby A LEFT JOIN
    -> (
    ->     SELECT   num, MAX(id) id
    ->     FROM     groupby
    ->     GROUP BY num
    -> ) B USING (id);
+----+------------+------------+
| id | num        | num        |
+----+------------+------------+
|  1 |  269529129 |       NULL |
|  2 |  387090406 |       NULL |
|  3 | 1126864683 |       NULL |
|  4 |  411160755 |       NULL |
|  5 |   29173595 |       NULL |
|  6 |  266349579 |       NULL |
|  7 | 1244227156 |       NULL |
|  8 |    6231766 |       NULL |
|  9 |  269529129 |  269529129 |
| 10 |  387090406 |  387090406 |
| 11 | 1126864683 | 1126864683 |
| 12 |  411160755 |  411160755 |
| 13 |   29173595 |   29173595 |
| 14 |  266349579 |  266349579 |
| 15 | 1244227156 | 1244227156 |
| 16 |    6231766 |    6231766 |
+----+------------+------------+
16 rows in set (0.00 sec)

mysql>

STEP 03 : Use LEFT JOIN with a WHERE clause

mysql> SELECT * FROM groupby A LEFT JOIN
    -> (
    ->     SELECT   num, MAX(id) id
    ->     FROM     groupby
    ->     GROUP BY num
    -> ) B USING (id) WHERE B.num IS NOT NULL;
+----+------------+------------+
| id | num        | num        |
+----+------------+------------+
| 16 |    6231766 |    6231766 |
| 13 |   29173595 |   29173595 |
| 14 |  266349579 |  266349579 |
|  9 |  269529129 |  269529129 |
| 10 |  387090406 |  387090406 |
| 12 |  411160755 |  411160755 |
| 11 | 1126864683 | 1126864683 |
| 15 | 1244227156 | 1244227156 |
+----+------------+------------+
8 rows in set (0.00 sec)

mysql>

ANALYSIS

Looking at the aforementioned results, here are two questions:

Why does a LEFT JOIN keep an ordering by id ?
Why in the world did using a WHERE impose a reordering ?

Was it during the JOIN phase ?
Did the Query Optimizer look ahead at the ordering of the subquery or ignore it ?

No one foresaw any of these effects because the behavior of explicit clauses was relied upon by the implicit behavior of the Query Optimizer.

CONCLUSION

From my perspective, corner cases can only be of an external nature. In light of this, developers must be willing to fully evaluate the results of a GROUP BY in conjunction with the following twelve(12) aspects:

aggregate functions
subquery usage
JOINs clauses
WHERE clauses
sort order of results with no explicit ORDER BY clause
query results using older GA releases of MySQL
query results using newer beta releases of MySQL
the current SQL_MODE setting in my.cnf
the operating system the code was compiled for
possibly the size of join_buffer_size with respect to its effect on the Query Optimizer
possibly the size of sort_buffer_size with respect to its effect on the Query Optimizer
possibly the storage engine being used (MyISAM vs InnoDB)

Here is the key thing to remember : Any instance of MySQL that works for your query in a specific environment is itself a corner case. Once you change one or more of the twelve(12) evaluation aspects, the corner case is due to break, especially given the first nine(9) aspects.


How can I see what query is emitted by the query parser?

This leads me to suspect that the query parser is emitting a query
  that doesn't contain the clause y.

The query parser emits query trees and the query planner emits plans. Both can be shown in more detail with the debug_print_parse and debug_print_plan settings.
From the documentation:

  Now what is a query tree? It is an internal representation of an SQL
  statement where the single parts that it is built from are stored
  separately. These query trees can be shown in the server log if you
  set the configuration parameters debug_print_parse,
  debug_print_rewritten, or debug_print_plan

In the case of the query in the question, y is apparently eliminated. It's the work of the planner, not of the parser.
The log output with debug_print_parse would show it, because that's before it gets eliminated, contrary to the output with debug_print_plan, in which it should not be present.

However, the result of EXPLAIN with no mention of the evaluation of y should be a sufficient proof that the planner has eliminated it.


Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?

Direct answers to your three questions would be;

Yes it does. When you replace your view, the lock of view object will be AccessExclusiveLock.
Yes, even the 'select' queries that uses that specific view will wait until the 'create or replace' statement is committed.
Yes they do. After the "view changer" query is committed the queries that are waiting will start


How to Get the Second Sequence of Characters using Regular Expressions

postgres=# select substring('68 TIDAL BREEZE DR' from '\s+(.*)\s');
  substring
--------------
 TIDAL BREEZE
(1 row)

Lazy match the first whitespace character to chop the first bit off.


MySQL - Deduct from two columns with a remainder

You can use the variables to do the trick:

UPDATE table AS w
  JOIN amounts AS z ON z.w_Id = w.ID
   SET w.balance = IF( (@remainder := w.balance - z.amount) >= 0, @remainder, 0 )
     , w.otherBalance = IF( @remainder >= 0, w.otherBalance, w.otherBalance + @remainder )
;

Here I suppose that amount is fetched from the another table amounts and perform the mass update for all the rows at once.

If the amount is bigger than balance, then @remainder becomes negative therefore I've added it to the otherBalance instead of subtracting it.


SQL Join to a Same Combination of Tables

As suggested by @Akina, it might be better to just write a query that joins the desired table to a view that joins the common tables. In this manner, we might be able to get good optimization and inlining of the view.

We need to note that for all 3 joins, we use the same value from the table joining to each table. Therefore we should be able to create a view that has the entire set of value {ProductID, ProductType} available to join.

I will assume that not all valid values necessarily exist in each table, so we can't just left join the other tables to the X table; it might not even have that value that we ultimately are looking for. Thus, we need a set of all possible values in all tables first.

CREATE VIEW AllThings AS
WITH AllPossibleValues AS (
  SELECT ProductID, ProductType
  FROM DimShelf
  UNION
  SELECT ProductID, ProductType
  FROM DimDesk
  UNION
  SELECT ProductID, ProductType
  FROM DimCouch
)
SELECT
  b.ProductID,
  b.ProductType,
  x.... -- All columns you need from x
  y.... -- All columns you need from y
  x.... -- All columns you need from z
FROM AllPossibleValues AS b
LEFT JOIN DimShelf x
  ON b.ProductId = x.ProductId
  AND b.ProductTypeId = x.ProductTypeId -- 1
LEFT JOIN DimDesk y
  ON b.ProductId = y.ProductId
  AND b.ProductTypeId = y.ProductTypeId -- 2
LEFT JOIN DimCouch
  on b.ProductId = z.ProductId
  and b.ProductTypeId = z.ProductTypeId -- 3
;

Note that it is not recommended to use SELECT * with any of the table in a view definition - it implies that it'll select all columns but in fact it only selects all columns that existed at the time of view's creation/modification. For that reason, it's best to enumerate the columns explicitly. This does make for lengthy query definition but as @Akina correctly points out, that will not necessarily hurt the optimization.

Furthermore, if you find the performance to be problematic, you might need to push down the predicate to the CTE, which necessitates the use of table-valued function in order to pass a parameter. Something similar to this:

CREATE FUNCTION AllThingsParameterized (
  @ProductID int,
  @ProductType varchar(50)
) AS RETURNS TABLE
RETURN
WITH AllPossibleValues AS (
  SELECT ProductId, ProductType
  FROM DimCouch AS x
  WHERE ProductID = @ProductID
    AND ProductType = @ProductType
  UNION
  ....
) ...

This variant, however, is good only for one value of the ProductID & ProductType; if you need a set of ProductID & ProductType, then you may need to consider using a table-valued parameter, which may further complicate the setup.

Do note that if the view is used in another view, you may negate the benefit of inlining as the final statement may become too complex for the optimizer to effectively optimize. If that is your typical scenario, you might be actually better off generating a temporary table result from a stored procedure and joining the table to that instead.


What do you call something that's not the primary key?

User terms surrogate key and natural key for primary key as a variant.

  Surrogate Key:

  Surrogate keys are keys that have no
  “business” meaning and are solely used
  to identify a record in the table.
  Such keys are either database
  generated (example: Identity in SQL
  Server, Sequence in Oracle,
  Sequence/Identity in DB2 UDB etc.) or
  system generated values (like
  generated via a table in the schema).

  Natural Key:

  Keys are natural if the attribute it
  represents is used for identification
  independently of the database schema.
  What this basically means is that the
  keys are natural if people use them
  example: Invoice-Numbers, Tax-Ids, SSN
  etc.

Surrogate Keys vs Natural Keys for Primary Key


How I can copy from local file to remote DB in PostgreSQL?

There are two possibilities here.

If you are using psql, then \copy is the correct way to do things as you mention.
If you are using a client program you have authored, then you need to

COPY FROM STDIN.....

And then feed the file in (see the documentation on the COPY command.

The COPY command is a slightly strange beast.  It is the one command PostgreSQL currently supports from Quel and so it is both non-standard, and based on something of a different paradigm than the rest of the commands.  For this reason it's important to read the docs relatively carefully on this command.


Using Oracle hierarchical queries for transposing field value into rows

Your CONNECT BY joins every row to every other (including itself) N times. This is a multi-auto-cross join. I can see how it could behave poorly performance-wise when the number of records increases.

I suggest a query like this instead:

SQL> SELECT test_id, regexp_substr(str_data, '[^,]+', 1, lvl)
  2    FROM (SELECT 1 AS test_id, '1,2,3' AS str_data
  3            FROM dual
  4          UNION ALL
  5          SELECT 2 AS test_id, '4,5,6' AS str_data FROM dual) test_data
  6    JOIN (SELECT LEVEL lvl FROM dual CONNECT BY LEVEL <= 1000)
  7      ON lvl <= length(str_data) - length(REPLACE(str_data, ',')) + 1;

TEST_ID REGEXP_SUBSTR(STR_DATA,'[^,]+',1,LVL)
------- -------------------------------------
      1                                     1
      2                                     4
      1                                     2
      2                                     5
      1                                     3
      2                                     6

Replace 1000 by the maximum number of elements (or calculate it with an inner COUNT subquery). Each row will only be queried n times (where n is the number of commas).

Also if performance is critical, use INSTR and SUBSTR instead of REGEX.


Give me suggestion about backup of schema and full backup in oracle

650*1024/15/3600 = 12.3 MB/s. Sounds like you have a 100 Mbit network connection for backup. In that case, using a compressed backupset instead of image copy will significantly reduce the time it takes to backup the database. – Balazs Papp


Is there any performance gain due to not setting a default for a new column in Mysql?

When adding a TINYINT column:

If specified as NULL:  1-bit NULL set; 1 byte is allocated or not allocated (depending on Engine and ROW_FORMAT)
NOT NULL: no bit for nullness; 1 byte allocated and filled in with the specified DEFAULT (or the default DEFAULT, which is 0 for TINYINT).

If the TINYINT is to be only a true/false flag, then it does not help to make it NULL.

ALTER TABLE takes time and may impact other processes.  Is the ALTER TABLE what you are worried about?

Bottom line:  The ALTER needs to fill in the byte, so you may as well let it do all the work for you.


SQL Server: INSERT in transaction locks the table until transaction finished

By issuing a SELECT COUNT(*) FROM ... you are requesting to read every row in the table. This will include the newly inserted, uncommitted, rows. As the original answer says, at no time is the table locked. By designing a test that explicitly looks at the locked rows you are, as expected, blocking yourself.

Well tuned workloads never scan the entire table. Scans create performance problems because they need to read the entire table and, as you discovered, are guaranteed to block behind any lock.


Create Log Shipping Step Failed After ServerName Changed

So in SQL Server 2012 SP1 I did this:

EXEC msdb..sp_helptext 'sp_verify_jobstep';

I scanned for 14234 (the error number you received), and right above the relevant RAISERROR call, I saw this:

IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(@server))))

So, run this code with a PRINT:

IF NOT EXISTS (SELECT *
  FROM master.dbo.sysservers
  WHERE (UPPER(srvname) = UPPER(N'ServerB')))
BEGIN
  PRINT 'Not found.';
END

Then verify exactly what is in master.dbo.sysservers and also check master.sys.servers. I suspect either those two views don't match or the name in both is not exactly ServerB. Show the actual syntax you used to call the procedure and verify that the server wasn't renamed with leading/trailing spaces, whitespace or other non-printing characters.

Another idea is to not use the system procedure to set up log shipping in the first place. Log shipping isn't all that cumbersome to set up and in the past I've always just set up the backup / restore jobs manually. Also the stored procedure you called first only takes monitor_server as a potential server name where it could get into trouble connecting somehow, and it is nullable. Are you sure you need to pass it? Why would you want the monitor server to be the same as the source server? If one goes down they're both down by definition. What happens when you don't explicitly name a server as the monitor_server and leave that and other related parameters null?

Also these various hooks and potential conflicts are why I don't ever bother to rename servers that are already running. I'd rather pay the extra cost of wiping, reinstalling and restoring than dealing with all of this after the fact - especially if the quick rename was sold as something "hassle-free."


Setting the default character set and collation is completely safe. This will ensure that future DDL changes will use utf8, but will not affect existing columns that use latin1. Those will have to be converted to utf8. Make a backup of the data, because there are risks of data corruption (one example).

And for completeness, I will point out that adding the changes in the my.cnf will require a server restart.


mysqldump with automatically generated export name

Here a script i have running my database backup :
-it create daily zipped bkps  in their onw daily data dirs.
-it may look strange that no passw in the conf ! is because i have configured my user root to run mysqldump without asking for password(this is for security reasons- you don't what your OS admin to sniff the process and see the password :))

To configure
password less access just create a .my.cnf file in your root home dir containing :

[mysqldump]
user = root
password = password

Script

 #!/bin/bash
        #Script backup mysql individual
        USER="root"
        DIR="`date | awk {'print $2"_"$3"_"$6'}`"
        PWDDIR="/mysql/backup_mysql/_mysqldump/"
        FINALDIR="$PWDDIR$DIR"
        GZIP_ENABLED=1
        GZIP="/bin/gzip"
        MYSQLDUMP="/usr/bin/mysqldump"
        MYSQL="/usr/bin/mysql"
        mkdir $FINALDIR
        databases=`$MYSQL --user=$USER -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

        for db in $databases; do
            echo $db
                if [ $GZIP_ENABLED == 1 ]; then
                        mysqldump --force --opt --user=$USER  --databases $db | gzip > "$FINALDIR/$db.gz"
                else
                    mysqldump --force --opt --user=$USER  --databases $db > "$FINALDIR/$db.sql"
                fi
        done

the script will create data dir for every day:

drwxr-xr-x 2 root root 4096 Mar 13 02:00 Mar_11_2014

Windows version for a nice mysqldump follow this link


Will composite indexes or unique indexes help with query performance?

(t2.`owner` = 'ABC123' or t2.`user` = 'ABC123')

This search term will force a table-scan. Composite indexes won't help.

Suppose you had a composite index on (owner, user). It could do a quick lookup for owner = 'ABC123' but then the desired values for user could be anywhere in the table, not just in the subset matching the desired owner value. So it has to scan the whole table anyway.

Likewise if you had a composite index on (user, owner). It could do a quick lookup for user = 'ABC123' but the same problem exists, finding the rows for owner will force a table-scan.

The one remaining possibility is to use index merge union optimization, but in my experience this doesn't work as well as you'd think.


pg_dump 9.2.x command does not work with pg_dump 9.2.3

I'd venture that it has nothing to with pg_dump version but that it may be an invisible spurious character on the command line that is causing this mess. As a demo, if I type this in cmd.exe, it works as expected at first:

  pg_dump -U postgres postgres >out.db

But now if I come back on this command with Arrow-Up, position the caret just before the -U option, add a non-breaking space by hitting Alt+255 on the numeric keypad, and validate this command, it yields the error you mention:

  pg_dump: too many command-line arguments (first is "-U") Try "pg_dump
  --help" for more information.


How long can the log reader agent run for before a restart will do it some good?

I think it depends on your environment and how busy it is but I support quite a few environments that are very stingy with their patches because they have incredibly low surface areas and end up with reboots/SQL restarts pretty infrequently (a few times a year) and the log reader agent isn't restarted outside of this schedule and there are no issues.

If your environment is high transaction and you are experiencing issues with the cleanup jobs on distribution and are running a local distributor you may see a benefit from restarting but, in general, I have found no need to worry about restarting the log reader agent.

I don't believe Microsoft has any particular guidance so this is a YMMV type of question but I think you'll be fine to not restart it outside of your normal patching and reboot/restart policy. As long as you monitor performance and look for issues and symptoms with your replication environment you'll be fine IMHO.


MongoDB - Replica sets - Syncing and DC issue

Is this normal behavior during the transition where they have to take themselves offline?

It is expected that your replica set instances will drop network connections when there is a change in primary.

  My only concern in this situation is what happens if VM1a goes down right after LocalMongo comes up? Wouldn't that mean that the only place where data is actually being stored would be on the primary and that it would create a broken link where VM1b (which never went down) wouldn't sync to anything?

Replica set secondaries can sync off of other secondaries, which can help reduce load on your primary. If your secondaries sync source goes down, it will reevaluate and change sync source to an available member.


You can replicate data from one server to others using Replication feature in MongoDB.

The replica set ensures high availability and redundancy of you mongodb data in case if node failure. In your case, you can create a replica set of 3 nodes (PRIMARY, SECONDARY, ARBITER). When one of the server goes down, Secondary would be promoted as PRIMARY which ensures high availability of MongoDB data.

It is always recommended to have at least 3 members in replica set.

You can find detailed document here


Performance issue using view vs. direct statement

You might want to try using a window function for that:

create view vw_current_value as
select id1, id2, value from (
  select id1, id2, value,
      row_number() over (partition by id1, id2 order by value_at desc) rn
  from your_values_table
) t where t.rn = 1;

sqlfiddle

EDIT:

The above has an advantage of being standard SQL. However (thanks to @a_horse_with_no_name) Postgres also offers a proprietary alternative: DISTINCT ON(something), which reportedly performs better:

create view vw_current_value as
select distinct on (id1, id2) id1, id2, value
from your_values_table
order by id1, id2, value_at desc;

sqlfiddle 2

Comparison of the execution plans of the alternatives is left as an excercise for the reader.


Is it bad practice NOT to delete unused database entities?

In case of tables, if it's not being updated you are safe to remove then and keep a backup (just in case).

Other objects like SP, Views and Functions can be easily removed.

Of course you can end up messing with relational integrity if you are not cautions in removing data. I recommend full regression tests over the system.

The advantages of removing unused objects is keeping the schema (and the system) clean and simple, this helps maintenance.

The disvantage is the risk of lost of essential data, damaging relational integrity and jeopardizing the system.

Also be aware if you remove a substancial mass of data it smells like trouble.

Edit

This is long comment full of "IMHO".
I'm a developer happened to get some dba tasks by accident. When they say "it's better let things as they are" it means "ok, cleaning the schema is fine but developers and testers will get they hands full of (unnecessary?) work and they are already full"

And that's true, in special if there acumulating garbage from years of "better let things as they are". Developers can need to remove references to that superflous objects. Testers must testify nothing ill blow up, etc.

At the end of the day someone needs to make the hard decision: To clean or not to clean, that's the question. If you can (do you have good documentation? have the time? Can alloc coworkers?) make a "possible impacts" report to your manager where you can pontuate the advantages, disvantages, risks and answer one little question: Are us just postponing a job needs to be done some point in the future?
Btw the short answer for your quastion is Yes!


How to maintain fixed number of rows per foreign key?

It will be much more efficient to limit the SELECT and purge periodically.

If you had to maintain a hard limit of 10 for business requirements, what you'd do is define a trigger on history before insert or update or delete for each row that:

does a SELECT ... FOR UPDATE on the parent object_id to lock it, so it serves as a lock to prevent concurrent insert/delete of other history entries for the same object;
If this is an insert, counts rows for this object and gets the id of the oldest
If the number of entries will be increased to 11 by this insert, deletes the oldest row

Deadlocks can occur, so you need to be able to retry the whole transaction if there's a deadlock.


How to effectively limit a selection size in a generic manner in Oracle?

SELECT *
FROM (
  SELECT *
  FROM FOO f
  WHERE f.bar > baz
  ORDER BY f.qux
)
WHERE ROWNUM <= 1;

Means: order the resultset by f.qux then return the 1st row based on that order.

SELECT *
FROM FOO f
WHERE f.bar > baz
    AND ROWNUM <= 1
ORDER BY f.qux;

Means: get the 1st row by unspecified order, then order the result. Order by has no effect at all in this specific example.

They are different.

Use the first one, or starting with 12c, you can use the below:

SELECT *
FROM FOO f
WHERE f.bar > baz
ORDER BY f.qux
FETCH FIRST 1 ROW ONLY; -- FETCH FIRST 1 ROWS ONLY, FETCH FIRST n ROWS ONLY also works


Oracle extents - How can I have so many with this small size?

The data you're getting from Toad appears to be incorrect or, at least, misleading.  If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically.  In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB).  The next 63 extents are going to be 1 MB in size.  So if you have 26 extents, that implies that the table occupies 11 MB of space on disk.  An initial extent of 1.44 GB makes no sense and a size of 4.18 MB seems rather low if you're saying it hasn't shrunk.

What does

SELECT tablespace_name,
       extent_id,
       bytes/1024 kb
  FROM user_extents
 WHERE segment_name = <<name of table>>

show you?  If you have 26 extents, you should see that extents 0-15 are 64 kb in size and extents 16-25 are 1 MB in size.

SELECT SUM(bytes)/1024/1024 size_in_mb
  FROM user_extents
 WHERE segment_name = <<name of table>>

will show you the total size of the table's extents on disk.


Select a count of record sharing the same value than record X

SELECT COUNT(*)
FROM table t1
JOIN table t2 USING (catId)
WHERE t1.id = 3

If id column is not unique then
SELECT COUNT(DISTINCT t2.id)
FROM table t1
JOIN table t2 USING (catId)
WHERE t1.id = 3


Representing a many-to-one relationship in a database diagram

transactionID in loadingDetail can be duplicated when the loadingID
  different

This is your requirement.Table composite key already handling this.

  transactionID in stowageDetail is UNIQUE

This is your requirement

  •many transactionID in loadingDetail just can have 1 transactionID in
  stowageDetail

This is your requirement.This can Unique index. Or it can be handle via code/trigger.
This can never be FK constraint.You cannot define partial FK constraint.

  •transactionID in loadingDetail can be deleted when there is no
  transactionID in stowageDetail (No Action)

Because you cannot define partial FK constraint. You have to handle this via code.**Instead of Trigger** is ideal for this scenario.

I assume you cannot define transactionID and loadingID as FK in stowageDetail table because of your requirement.

Read this

As per your requirement, your design is already ok.Adding any other candidate key is useless. you cannot do it via PK-FK constraint.


This will work I think:
with d as
  ( select ctid, row_number() over (partition by t.*) as rn
    from tablename as t
  )
delete from tablename as t
using d
where d.rn > 1
  and d.ctid = t.ctid ;

And another variation. Not sure which will be more efficient:
delete from tablename as t
where exists
      ( select *
        from tablename as d
        where d.ctid > t.ctid
          and d.* is not distinct from t.*
      ) ;

But note what the docs say abut ctid:

ctid
The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

So, if the table was created WITH OIDS, use that instead.


How to make multiple counts in one query?

To get a count for each of those you can try

SELECT
    COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1,
    COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2,
    COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3
FROM `table1`;


How can client retrieve SQL Server public SSL certificate?

Technically it is possible, both TDS and TLS/SSL are documented. But you are asking the wrong question.

There is no point in willy-nilly manually installing a self-signed certificate everywhere, it buys you nothing. You will only install the MitM's cert everywhere...

Focus on deploying a properly configured PKI infrastructure. Either use a trusted certificate (ie. Verisign et all), or install a PKI trust in your network and use a infrastructure signed certificate.

See this series: Designing and Implementing a PKI


How to design tables for players in a tournament?

UPDATED version based on feedback:

CREATE TABLE group (
    group_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(team_id)
);

CREATE TABLE player (
    player_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    group_id INT,
    player_rank INT,
    PRIMARY KEY(player_id)
);

CREATE TABLE game (
    game_id INT NOT NULL AUTO_INCREMENT,
    match_date DATE,
    overtime TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (game_id)
);

CREATE TABLE player_game (
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_score INT,
    PRIMARY KEY (game_id, player_id)
);

...and UPDATED explanation:
I think group and player are self-explanatory.  The table game will have one record per match.  The player_game table records the players in a match and the results; each match will have two records, one for each player.

Since you appear to be using InnoDB as the storage engine you may want to use foreign keys to enforce referential integrity, and obviously you can add other constraints and indexes as needed.  If you need some functionality that this doesn't seem to cover, ask and I will either explain or add it.


pg_archivecleanup won't work automatically with 2 replicas, as it doesn't coordinate between them to reach an agreement.  You would have to manually decide up to what point you can remove them by determining which replica has the older restartpoint.  You could then feed that oldest restartpoint to pg_archivecleanup, but I don't see the point, if you have to decide manually what to use, why not just remove them manually as well?
You could use replication slots, and do away with the archive.  Then it will automatically keep files in pg_wal and remove them once they are no longer needed by any replica.  This does mean pg_wal might need to have somewhat larger capacity than it currently does, if the replicas ever get way behind.  (But then you don't have to reserve capacity for the archive anymore)


I don't think you can avoid a local install. I'm not aware of any online sites (e.g. SQLFiddle does not offer DB2).

In addition to the book you linked to, I can highly recommend the "DB2 SQL Cookbook" as well: http://mysite.verizon.net/Graeme_Birchall/id1.html

And don't forget the official manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp


Insert into table with column same values as auto increment column ( computed column )

You can create a table with a Calculated column that it's value is equal to column1.
Each time you insert a row into your table column1 will be inserted as identity and column2 will be euqal to that.

MySql :

CREATE TABLE myTable
  (column1 int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  column2 int);
INSERT INTO myTable (name) VALUES('John Doe')
UPDATE mytable SET column2 = column1;

Best ways for MySql : Calculated column or trigger that I refer you to these answers : Calculated Column MySql , Insert, then update, Create before insert trigger

You can add other columns like name.

Sql Server :

CREATE TABLE myTable
  (column1 int identity (1,1) NOT NULL,
  name nvarchar(100),
  column2 as column1);

INSERT INTO myTable (name) VALUES('John Doe')


Need to include FKs in SQL Indexed View clustered index?

SQL Server will always be able to automatically update the view's index(es) as the source table(s) change; there's nothing extra you need to do.

You can inspect a query plan that modifies one of the source tables, you'll see there are extra operators to update the view's index(es), the same way it has to update all the applicable nonclustered indexes on the source tables when data changes.


Failover clustering SQL server 2016 standard

Please have a look at Features Supported by Various Editions of SQL Server.

  Or put differently (perhaps?) is failover clustering always on the entire instance and all databases it contains?

No, AlwaysON Availability(AOAG) groups can be configured for specific set of databases. It is not necessary to include all databases which reside on instance. So suppose you have 7 databases on instance you can configure AOAG for 4 databases and leave aside 3 databases.

The unit of failover would be the AG group, this means when failover will happen all databases which are part of that AG will failover
One or multiple databases can be grouped within an Availability Group
One SQL Server instance can support multiple AGs. The Availability Group is the unit of failover
A specific database can be part of one Availability Group only and cannot be a part in multiple AGs

Please read more about AlwaysON Availability groups from this blog it has many links start with link "What is AlwaysON"

Basic AG would only support one database.


DBF file compatibility between Oracle 12.2.0.1 and 11.2.0.4?

I assume that you will eventually upgrade production to 12.2. If it were me, I would have this scripted. Drop the development database, change /etc/oratab file on the development database to be 11.2.0.4 if you are using Linux/Unix, otherwise drop recreate the windows service using oradim. Clone from prod to development as 11.2.0.4. Do a silent upgrade of the development database using dbca, and be ready to repeat as you get closer to the migration date. When you practice the upgrade procedure enough to work out the bugs, and have tested the performance enough to know that you won't have performance issues in production, you are ready to do the upgrade in prod.

There are some automatic tuning parameters in 12.2 that you may need to disable, depending on how your application performs.


How does ON DELETE CASCADE relate to JSON?

You're confused here,

JSON is a data-interchange format, in PostgreSQL we provide json and jsonb as formats with advantages like validation and indexing that help you work with this format.
ON DELETE CASCADE is a clause that tells a referring table what to do when the table that it refers to lose the row it's referencing.

You can mix the two concepts, but you should never do this

CREATE TABLE tfoo ( qux jsonb PRIMARY KEY );
CREATE TABLE tbar ( qux jsonb REFERENCES tfoo );
CREATE TABLE tbaz ( qux jsonb REFERENCES tfoo ON DELETE CASCADE );

Now we can insert some test data in tfoo,

INSERT INTO tfoo (qux) VALUES ( '{"id":42}' );
INSERT INTO tfoo (qux) VALUES ( '{"id":7}' );

And you'll get a reject if you try to insert into tbar or tbaz anything but the above,

-- Rejection
INSERT INTO tbar (qux) VALUES ( '{"id":2}' );
ERROR:  insert or update on table "tbar" violates foreign key constraint "tbar_qux_fkey"
DETAIL:  Key (qux)=({"id": 2}) is not present in table "tfoo".

This is a really bad idea though for many reasons. We can see here that these work though,

INSERT INTO tbar (qux) VALUES ( '{"id":42}' );   -- ON DELETE REJECT
INSERT INTO tbaz (qux) VALUES ( '{"id":7}' );    -- ON DELETE CASCADE

And moreover, we can see the effects -- {"id":7} is in tbaz which has the ON DELETE CASCADE

DELETE FROM tfoo WHERE qux = '{"id":7}';

And, this fails as {"id":42} is in tbar and it doesn't know what to do if you remove that entry from tfoo so it rejects the operation.

DELETE FROM tfoo WHERE qux = '{"id":42}';
ERROR:  update or delete on table "tfoo" violates foreign key constraint "tbar_qux_fkey" on table "tbar"
DETAIL:  Key (qux)=({"id": 42}) is still referenced from table "tbar".

Clarification

So you can see you can have ON DELETE CASCADE on any type that can be put on a UNIQUE index (PRIMARY KEY is a special UNIQUE NOT NULL index). This means, you can mix these two concepts, as we did above. But you should never do that, and I've never seen it done in practice. Generally, you put FOREIGN KEYS on int or text,

CREATE TABLE tfoo (
  id_foo int     PRIMARY KEY
    GENERATED BY DEFAULT AS IDENTITY,

  qux    jsonb
);
CREATE TABLE tbar ( id_foo REFERENCES tfoo, qux jsonb );
CREATE TABLE tbaz ( id_foo REFERENCES tfoo ON DELETE CASCADE, qux jsonb );


why is granting ALL permissions and privileges to a new role/user for a new database difficult with postgresql?

There are certainly some differences between PostgreSQL and other databases, such as MySQL. When I first started working with PostgreSQL, I found it to have a lot more friction than the servers I was more familiar with. However, a bit of practice — and a lot of snippets in Evernote — reveals the method to the madness.
That said, to answer your question, there are 4.5 steps to create a database, a user account, and grant all permissions.
From the command line:

Create the user:
$ sudo -u postgres createuser <username>

Create the database:
$ sudo -u postgres createdb <dbname>

The next commands are done in PostgreSQL, so step 2.5 is logging in to the server:
$ sudo -u postgres psql

Assign the user a password:
ALTER USER <username> WITH ENCRYPTED PASSWORD '<password>';

Grant privileges on database:
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username> ;

Done and done.
You probably do this already, but a note-taking system like Evernote (or just a well-structured directory with text files) is an indispensable tool for processes like this until muscle memory kicks in


How can wait times be higher than clock time?

No - wait stats can (and more often than not, will) total more than the physical time spent for the server itself.

Imagine a situation where 2 separate threads spend the same second waiting for some resource - you would have 2 seconds of wait time for 1 second of clock time.

Each thread has it's own waits - the message is telling you that 20 seconds of wait time for that particular resource occurred over the last 5 seconds of clock time.

Or to put it another way, each core can be running multiple queries at the same time, and multiple cores can add up to even more waits. Which is to say, the unit is really thread·seconds, not seconds.


MySQL - identifying bottlenecks in complex queries

The MySQL documentation includes a section with general query tuning advice. However, if you read that, you'll see that it offers little in terms of specific advice.

That's because there aren't a lot of things that you can do that make every query you write better.

There are a few things you can do, though:

Develop an understanding of how the optimizer works: Look for articles that talk about query tuning in your DBMS of choice, and learn the things that most often result in sub-optimal query plans. Similarly, develop an understanding of your DBMS' query plans: how to see them, how to follow them, and what they mean.
Keep searches SARGable: That means making sure that your query will use indexes wherever possible. The biggest rule to follow for this is to avoid comparisons that require applying functions to your columns in comparisons. If the engine has to apply a function to the data from your table, a standard index can't be used (because the values in it aren't the values being compared).

If you can, write comparisons so the side with the most rows, with an index on the column involved, doesn't have a function applied on it. Apply a more complicated function to a column with fewer rows to worry about, if a function must be involved. Whenever possible, make sure that values to be compared to each other are of the same data type, and are using the same units, so they can be compared directly without needing functions.
Follow maintenance standards: While following the first step in my advice, you may notice some trends in what causes poor query plans. in SQL Server, I've noticed that one of the sings that a plan may be off is when the optimizer's estimate of the number of rows a particular step will return is far off from the actual number of rows returned. One of the most common reasons for these estimates to be off is if SQL Server's statistics (which help it determine the "cardinality" of a piece of a query - how many rows it's likely to bring back. If you've only got 100 rows from one table, the most effective way to join it to another table may be completely different than if you've got 1 million rows.

In SQL Server, these estimates are usually much better if you regularly run a job to update the statistics on your tables. Watch for things like this in MySQL, and make sure you're running the appropriate maintenance jobs as often as you need to.
Avoid pre-emptive "optimization": Some of the tricks used to force queries to perform better involve forcing the query to operate in a specific way; for instance, using hints to force a particular method to be used when joining table, or to use a specific index. In some circumstances, this sort of thing can help; however, whenever you use one of these, you limit the optimizer's options when figuring out the best way to run the query.

If you have a column in your table where there are 100 distinct values, but 90% or the rows all have 1 value, and the other 99 values are split amongst the other 105 of the rows. If you search for a value from that table (using a variable to pass in the value), then a run where the variable is set to the most common value, the optimizer isn't likely to use an index on that column; the value doesn't do a good job of narrowing down the number of rows the query has to deal with. If any of the other values is used, then the index might be useful, because it would tend to limit the number of rows fairly effectively.

If you force the query to always use an index on this column, then it will likely work well any time the value is one of the rare ones, but relatively poorly if the value is the most common one.
Be flexible: Keep learning, and try not to assume you know everything. Most DBMSes receive fairly regular updates, and these can change the rules. Know what works now, but keep an eye out for new articles, and new questions, to see if some new strategy has been added to the toolkit, or replaced an old one.

An example, again from the SQL Server world: for years, it was standard advice that you should regularly defragment indexes to keep performance good. While this isn't bad advice, in the past couple of years more and more people are saying that defragmenting your indexes isn't as necessary as they used to think. You see, when an index is defragmented, its statistics are normally updated as well; and, it seems that the updated statistics (helping provide better query results) are probably more important that the actual defragmenting; and, are less intrusive. A case where the old advice has been superseded by new advice (even for older version of the software).

Generally, assume the optimizer will do its job well 99%+ of the time; don't limit its options unless you see a real reason to do so; and then only after you've tried other ways to optimize the query. You don't know exactly how any query will perform until it's performing in a production environment. Write queries as wisely as you can, but try to start with a query you (and others who may not even be on your team now) can maintain in general. Only twist a query around in weird ways to improve performance when it's been proven to be necessary.


I found solution:

  change engine from aria to myisam
  stop mysql, add aria_block_size = 1024 to my.cnf
  delete aria_log_control file in mysql directory
  start mysql
  convert myisam tables back to aria

This process is safe for data, can be done without outage (only one fast mysql restart) and much faster then re-create tables and import data.