Quote from comments on Blog Introducing MySQL InnoDB Cluster – MySQL HA: Out-of-Box, Easy to use High Availability

  Is this MySQL InnoDB Cluster – MySQL HA for Community Edition i.e
  under GPL /OpenSource?

answer from Mike Zinner:

  (...)
  yes, MySQL InnoDB Cluster is available in the Community
  Edition and it is licensed under GPL. You can download the individual
  components from https://dev.mysql.com/downloads/ You will need MySQL
  Server, MySQL Shell and MySQL Router to build your MySQL InnoDB
  Cluster (...)


how to insert a list of varchar values, into a single column table?

Since you are using SQL Server 2005, you need to perform a single insert for each value.  Upgrade to SQL Server 2012 or higher, and you get the multiple VALUES clause, making this much easier.
Something like:
INSERT INTO __RADHE (ItemNo)
VALUES ('34933945');

I would recommend using find-and-replace to replace the commas with the required syntax.
Replacing the commas in your value-list above with:
); INSERT INTO __RADHE (itemNo) VALUES (

Seems to get you almost all the way there.
As an aside; you should always specify the schema when referencing objects.  When specifying PRIMARY KEY CLUSTERED, you are implicitly creating a constraint, which will be named by SQL Server if you don't specify a name.  Specify a name if you don't like names similar to PK__T1__3214EC27023D5A04.
For instance, your CREATE TABLE statement should be something like:
CREATE TABLE dbo.[__RADHE]
(
    itemNo VARCHAR(10) NOT NULL
        CONSTRAINT PK___RADHE
        PRIMARY KEY CLUSTERED
);

If there is a chance the source data contains duplicate entries, you may find it easier to delay creating the primary key constraint until the data has been inserted, and duplicates removed.  To do that, create the table like this:
CREATE TABLE dbo.[__RADHE]
(
    itemNo VARCHAR(10) NOT NULL
);

Then insert all the rows using the INSERT INTO ... statements as above.
Run this to remove duplicates:
;WITH src AS (
    SELECT r.*
        , rn = ROW_NUMBER() OVER (PARTITION BY r.itemNo ORDER BY r.itemNo)
    FROM dbo.__RADHE r
)
DELETE
FROM src
WHERE rn > 1;

Then create the primary key constraint:
ALTER TABLE dbo.__RADHE
ADD CONSTRAINT pk__RADHE
PRIMARY KEY CLUSTERED
(itemNo);

As a side note, unless you use a TABLOCKX lock hint on the table, all rows are individually logged using either the UNION ALL approach or the individual INSERT INTO approach I detail above.  If you can afford to lock the entire table for the duration of the inserts via TABLOCKX, then only the page allocations are logged, vastly reducing the amount of logging that takes place.  Having said all that, there are of course a lot of "gotchas" that might prevent bulk logging of pages - as detailed by Paul White here.


Is it possible through only T-SQL to physically move the data and log files of a database from one drive to another drive (on the same server)?

Below is a solution that uses robocopy with xp_cmdshell to move the files. Afterwards the DEL command is used to delete the old files.
It has its flaws (only data files + log files to be moved), only one data location, no checks for missing or needed \'s, error handling should be better,...

But you might find use in simply printing the commands by setting the @debug parameter to 1.

Test database used

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Test', FILENAME = N'G:\SQL\MSSQLSERVER\Test.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'test2', FILENAME = N'D:\DATA\test2.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Test_log', FILENAME = N'E:\LOG\Test_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO

All files are moved to F:\Data and F:\Log in the example

Full script:

DECLARE @DBNAME varchar(255) = 'Test' -- No [ ] 's
DECLARE @NewDataFileLocation varchar(4000) = 'F:\Data\' -- end with \
DECLARE @NewLogFileLocation varchar(4000) = 'F:\Log\' -- end with \
DECLARE @debug bit = 1; -- 1 to print, 0 to execute

-- main file table, can be kept after commands are exeucted if created as a physical table and the drop command and the end is removed

CREATE TABLE #files (logicalname varchar(255),
                     physical_name varchar(4000),
                     movelogicalfilecommand nvarchar(max),
                     movephyisicalfilecommand nvarchar(max),
                     error bit)

-- insert the commands for the data files
INSERT INTO #files(logicalname,physical_name,movelogicalfilecommand,movephyisicalfilecommand)
SELECT  [name],
        [physical_name],
        'ALTER DATABASE ' +QUOTENAME(DB_NAME(database_id)) +'
 MODIFY FILE (name='''+[name]+'''
             ,filename='''+@NewDataFileLocation+RIGHT(physical_name,(CHARINDEX('\',REVERSE(physical_name))-1))+''');',
           'ROBOCOPY "'+
           REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))+1,LEN(physical_name)))
           + '" "'
           + LEFT(@NewDataFileLocation,(LEN(@NewDataFileLocation)-1)) +  '" "'+
           RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) +'" '

FROM sys.master_files
WHERE QUOTENAME(DB_NAME(database_id)) = QUOTENAME(@DBNAME)
AND type_desc = 'ROWS';

-- insert the commands for the log files

INSERT INTO #files(logicalname,physical_name,movelogicalfilecommand,movephyisicalfilecommand)
SELECT  [name],
        [physical_name],
        'ALTER DATABASE ' +QUOTENAME(DB_NAME(database_id)) +'
 MODIFY FILE (name='''+[name]+'''
             ,filename='''+@NewLogFileLocation+RIGHT(physical_name,(CHARINDEX('\',REVERSE(physical_name))-1))+''');',
           'ROBOCOPY "'+
           REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))+1,LEN(physical_name)))
           + '" "'
           + LEFT(@NewLogFileLocation,(LEN(@NewLogFileLocation)-1)) +  '" "'+
           RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) +'" '

FROM sys.master_files
WHERE QUOTENAME(DB_NAME(database_id)) = QUOTENAME(@DBNAME)
AND type_desc = 'LOG';

-- Logically move the files in the system catalogs

DECLARE @cmd nvarchar(max), @movelogicalfilecommand nvarchar(max), @movephyisicalfilecommand nvarchar(max);
DECLARE C CURSOR FOR
SELECT movelogicalfilecommand
FROM #files;
OPEN C;
FETCH NEXT FROM C INTO @movelogicalfilecommand;
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRY

    SET @cmd = @movelogicalfilecommand;
    IF @debug = 1
    PRINT @CMD;
    ELSE
    EXEC SP_EXECUTESQL @cmd;

END TRY

BEGIN CATCH

    UPDATE #files
    set error = 1
    WHERE movelogicalfilecommand = @movelogicalfilecommand;

END CATCH

FETCH NEXT FROM C INTO @movelogicalfilecommand;
END
CLOSE C;
DEALLOCATE C;

-- Set the database offline
-- subsequently move the physical files with robocopy

DECLARE @cmd2 NVARCHAR(4000)
IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN

    SET @cmd = 'ALTER DATABASE '+QUOTENAME(@DBNAME)+' SET OFFLINE WITH ROLLBACK IMMEDIATE'
    IF @debug = 1
    PRINT @CMD;
    ELSE
    EXEC SP_EXECUTESQL @cmd;

    DECLARE C CURSOR FOR
    SELECT movephyisicalfilecommand
    FROM #files;
    OPEN C;
    FETCH NEXT FROM C INTO @movephyisicalfilecommand;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        SET @cmd2 = @movephyisicalfilecommand;
        IF @debug = 1
        PRINT 'EXEC XP_CMDSHELL '''+@cmd2 +'''';
        ELSE
        EXEC XP_CMDSHELL @cmd2;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE movephyisicalfilecommand = @movephyisicalfilecommand;

    END CATCH

    FETCH NEXT FROM C INTO @movephyisicalfilecommand;
    END
    CLOSE C;
    DEALLOCATE C;
END
ELSE
SELECT 'ERROR moving logical files'

-- Set the database online

IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN

    BEGIN TRY

        SET @cmd = 'ALTER DATABASE '+QUOTENAME(@DBNAME)+' SET ONLINE'
        IF @debug = 1
        PRINT @CMD;
        ELSE
        EXEC SP_EXECUTESQL @cmd;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE movephyisicalfilecommand = @movephyisicalfilecommand;

    END CATCH
END
ELSE
SELECT 'ERROR moving physical files';

DECLARE @physical_name varchar(4000)

IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN
    DECLARE C CURSOR FOR
    SELECT physical_name
    FROM #files;
    OPEN C;
    FETCH NEXT FROM C INTO @physical_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        SET @cmd2 = 'DEL "'+@physical_name +'"';
        IF @debug = 1
        PRINT 'EXEC XP_CMDSHELL '''+@cmd2+'''';
        ELSE
        EXEC XP_CMDSHELL @cmd2;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE physical_name = @physical_name;

    END CATCH

    FETCH NEXT FROM C INTO @physical_name;
    END
    CLOSE C;
    DEALLOCATE C;
END
ELSE
SELECT 'ERROR setting database online';

DROP TABLE #files;

Output example

ALTER DATABASE [Test]
 MODIFY FILE (name='Test'
             ,filename='F:\Data\Test.mdf');
ALTER DATABASE [Test]
 MODIFY FILE (name='test2'
             ,filename='F:\Data\test2.ndf');
ALTER DATABASE [Test]
 MODIFY FILE (name='Test_log'
             ,filename='F:\Log\Test_log.ldf');
ALTER DATABASE [Test] SET OFFLINE WITH ROLLBACK IMMEDIATE
EXEC XP_CMDSHELL 'ROBOCOPY "G:\SQL\MSSQLSERVER" "F:\Data" "Test.mdf" '
EXEC XP_CMDSHELL 'ROBOCOPY "D:\DATA" "F:\Data" "test2.ndf" '
EXEC XP_CMDSHELL 'ROBOCOPY "E:\LOG" "F:\Log" "Test_log.ldf" '
ALTER DATABASE [Test] SET ONLINE
EXEC XP_CMDSHELL 'DEL "G:\SQL\MSSQLSERVER\Test.mdf"'
EXEC XP_CMDSHELL 'DEL "D:\DATA\test2.ndf"'
EXEC XP_CMDSHELL 'DEL "E:\LOG\Test_log.ldf"'


MongoDB - temporarily turn shard to standalone for local user creation

If you comment out keyFile (assuming you have not specified auth explicitly) you will turn off authentication.  Basically keyFile implies auth but not the other way around.

When you comment out shardsvr, the port will change from 27018 to the default port number - 27017.  That is actually a good thing.  When you take a node out of a replica set or a sharded cluster you want to change the port so that the rest of the set/cluster does not attempt to continue to communicate with the node you are working on.  Then, once you restore the original configuration, it will rejoin the set and cluster as normal.

Although the port will change when you do this, I tend to prefer explicitly specifying the port number to use for maintenance (30000 is my preference) rather than rely on default settings.  Also, I tend to have plenty of instances running on my hosts, so 27017 may well already be in use.

Once you have made your changes, all you have to do to restore the node in question is restart and switch back to your original settings.  This should be a relatively quick maintenance, so you should not have to worry about a node going stale (can be a concern with prolonged work).


What is the advantage of setting a character column as VARCHAR(32) over VARCHAR(255)

You are probably defaulted to CHARACTER SET utf8 (or utf8mb4), in which case (255) would need 2 bytes for the length.

But the real problem is with temp tables (pre-8.0):  If the SELECT is so complex that it needs to generate a temp table (eg, for sorting), it will try first to use a MEMORY table.  In doing so, it will turn VARCHARs into CHARs, thereby taking 765 bytes (if utf8).  If, in doing so, this (and other actions) take too much space, it will switch to the slower MyISAM for the temp table.  See the status value Created_tmp_disk_tables.

For SSN, country_code, zip, etc, explicitly say CHARACTER SET ascii (or latin1).  This will avoid extra bloat for the charset.

For fixed-length columns, use CHAR, not VARCHAR.  So, if this is a US SSN, shouldn't it be

SSN CHAR(11) CHARACTER SET ascii  COMMENT 'example: "123-45-6789"'

In spite of all that, the performance difference will be minor.

More important in the serious legal implications (in the US) if your computer is hacked into, and you lose sensitive info such as SSNs.  The cost of such a breach could bankrupt your fledgling business.  And no, simply encryption won't suffice -- think about how easy the hacker can find the encryption key.


How can I take backup of particular tables in SQL Server 2008 using T-SQL Script

Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets you back up whole or partial SQL Server database or individual files or filegroups of the database. Table-level backup cannot be created, there is no such option. But there is a workaround  for this

Taking backup of SQL Server table possible in SQL Server. There are various alternative ways to backup a table in sql SQL Server

BCP (BULK COPY PROGRAM)
Generate Table Script with data
Make a copy of table using SELECT INTO
SAVE Table Data Directly in a Flat file
Export Data using SSIS to any destination

Here I am explaining only the first one rest you might be knowing

Method 1 – Backup sql table using BCP (BULK COPY PROGRAM)

To backup a SQL table named "Person.Contact", which resides in SQL Server AdventureWorks, we need to execute following script, which

-- SQL Table Backup
-- Developed by DBATAG, www.DBATAG.com
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want    to backup
SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
+ '.dat'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

OUTPUT

Note -

You must have bulk import / export privileges
In above Script -n denotes native SQL data types, which is key during restore
-T denotes that you are connecting to SQL Server using Windows Authentication, in case you want to connect using SQL Server Authentication use -U -P
This will also tell, you speed to data transfer, in my case this was 212468.08 rows per sec.
Once this commands completes, this will create a file named "AdventureWorks.Person.Contact_20120222" is a specified destination folder

Alternatively, you can run the BCP via command prompt and type the following command in command prompt, both operation performs the same activity, but I like the above mentioned method as that’s save type in opening a command prompt and type.

bcp AdventureWorks.Person.Contact out C:\MSSQL\Backup\AdventureWorks.Person.Contact_20120222.dat -n -T


Have: Postgres database files. Need: Functioning database

@a_horse_with_no_name is quite right. Because you compiled PostgreSQL from source, the usual tools provided by pg_wrapper like pg_ctlcluster and pg_lsclusters are not available. As a result the PostgreSQL init scripts on Ubuntu won't start your PostgreSQL 9.1 DB.

You could hook your custom binaries into pg_wrapper, but it's not worth the hassle for a one-off.

Assuming your 9.1 data directory is still owned by user postgres and in the default location:

sudo -u postgres -i
PGPORT=5440 PATH=/path/to/your/9.1_install/bin pg_ctl -D /path/to/your/9.1/db
PGPORT=5440 /path/to/9.3/bin/pg_dump -Fc -f mydb.pgdump mydb
PGPORT=5440 /path/to/9.3/bin/pg_dumpall --globals-only > my_91_globals.sql

then run my_91_globals.sql against your 9.3 server, or examine it and make any required changes.

Then pg_restore the mydb.pgdump using the pg_restore from 9.3.

You might need to adjust this if you're working from a restored backup copy of your 9.1 datadir, because its permissions may not be those of the postgres user. It does not matter what user owns it, but you might need to tweak pg_hba.conf if you're using peer auth and connecting as a different user.


DB Design - 1 column for each dataset OR dataset_name AND dataset_value?

Upon realization that I had unwittingly reinvented a modified EAV model with Option #1 and would likely be headed into a world of hurt with this option, I decided to go with Option #2.

Option #2 should preserve many of the benefits of RDBMS and I think MySQL/InnoDB can support the row sizes I currently expect.  While I will have a quantity of NULL entries that some might find disagreeable, I expect the DB will perform queries quickly, which is my main concern.

At some future point, should I hit constraints/performance issues, I may consider a NoSQL option as it appears the data I am working with doesn't necessarily correspond so well to RDBMS.


Install mysql through deb file on ubuntu 20.04

You can use
 mysql-server_8.0.21-1ubuntu20.04_amd64.deb

But yoz should follow the howto
Install short Howto

Download latest mysql-apt-config deb from
https://repo.mysql.com/

Install mysql-apt-config deb:
# sudo dpkg -i mysql-apt-config_*.deb

Update the package index:
# sudo apt-get update

Install mysql-server deb package:
# sudo apt-get install mysql-server

ANd if you have problems see https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/linux-installation-debian.html


Looks like your socket file is missing

When that happens, you must connect to MySQL using TCP/IP as follows

mysql -uroot -p -h127.0.0.1 -P3306 --protocol=tcp

Specifying the port number and protocol, this will connect to MySQL with or without a socket file.

I have suggested this method many times when mysqld is running without a socket file

NOTE: Restarting MySQL will bring back the socket file. Usually, starting MySQL when mysqld is already running is what usually cause the socket file to disappear. (See Is there anyway that I can manually create a PID for the MySQL instance?)


Are relations slower than a big, inefficient table?

I don't see how anyone could make such a statement without having some actual facts to back it up. If your queries are CPU bound, then you should look to find ways to reduce that bottleneck.

It sounds as if your boss feels that a denormalized database will perform best, but I don't know enough about your application to say if that is right or not. What will be the expected number of deletes, updates, and inserts for this table?

I would expect that such a denormalized design may result in a reduced amount of CPU time but would expect that your disk I/O would increase. And physical reads from disk will be much more expensive than a CPU cycle, so perhaps your boss has a very specific metric to meet (CPU) and as a result wants a very specific design? If so, I would simply build what is asked for and keep metrics on CPU cost for the queries being run. If you see an increase in time then you may want to suggest some design changes.

In fact, it is probably a good idea to get a list of all the metrics your boss wants to see, and track those over time.


How can I (re-)create the Entity Relationship Diagram (ERD) of an existing hidden Oracle database?

Many database modelling tools can extract the required data and create an initial ERD diagram. You will likely need to rearrange the diagram, but the initial diagram will provide a good starting point.

Given the limitations, you may need to use queries that generate DDL and create a database based on this DDL.  I would avoid selecting from the all_* views as many of those objects will be system related, rather than application related.


Is "deadlock detected" really an error? Should I be suppressing them after handling them?

A deadlock is a kind of serialization error: you didn't do anything forbidden, it just so happened that there was an interaction with other active transactions that prevented your transaction from being completed. Your reaction is the correct one: retry the transaction. There is absolutely no need to wait before retrying.
I concur that with a sufficiently complicated workload of bigger transactions it can be nigh impossible to rule out deadlocks completely. As long as they happen only rarely, they are not a real problem if as you handle them correctly.
Deadlocks start becoming a problem if they happen too frequently: it means that you have to redo much work, which is bad for performance and puts extra load on your database. Also the one second wait before the deadlock is resolved means that locks are held for a long time (a second is long), which is not great for concurrency.
Even if you cannot completely get rid of deadlocks, you can take measures to reduce them:

see that your transactions are short

try to reduce the number of data modifications per transaction

Both will reduce the likelihood of running into a deadlock.
It is safe to ignore deadlock errors in your log file, but then you should monitor pg_stat_database.deadlocks and take action of the deadlock count per hour increases beyond an acceptable number.
You seen to take objection to calling a deadlock an error. By definition, an error is a condition that aborts the execution of an SQL statement. So a deadlock is clearly an error.


Temporarily removing replication from Publisher

This seems to be the best method: Script the Publication -> Drop Subscriptions -> Drop Publication -> Re-create them

I was hoping there would be a 'trick' with replication where I could leave subscribers in place and just script out the publisher and recreate it. I don't see a clear way to restart replication without doing a full rebuild/reinitialise.

What I have noted is that this method does work pretty well once you have established replication for a database, worked out all the issues, scripted it all out; running those scripts does simplify the process.

Hope this helps someone


How to speed up queries in MySQL on a table with ~50 million rows

Provide SHOW CREATE TABLE; what you provided is incomplete.  4 indexes on 50M rows is likely to slow down INSERTs; we need to discuss that.

For that one query, you need the one composite index:

(DataType, SystemNumber, DateTimeStamp) -- or
(SystemNumber, DataType, DateTimeStamp) -- (but not both)

The order is important.  A "composite" index is not the same as indexes on each column.  One of those could (should) probably be the PRIMARY KEY instead of the one you mentioned.

More discussion on creating the best index:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql

If you have other queries; let's see them.  Else get rid of the rest of the indexes.

Perhaps you meant "<" instead of "<="?  (BETWEEN is inclusive.)

Is there some reason for using DECIMAL(15,5) instead of, say, FLOAT (which takes 4 bytes (float) versus 8 and still gives 6-7 significant digits of precision.)

Is the table InnoDB?  This is important because of the way the PK works.

You have PRIMARY KEY(DateTimeStamp, DataNumber, SystemNumber, value); does that mean you expect to have multiple values for each date+num+sys combination?  (It is unusual to have something called "value" included in a PK.)


SSIS - Excel destination does not allow characters more than 255

SSIS Excel Data Source:

  Error with output column “Comments” (5691) on output “Excel Source
  Output” (5596).

The column status returned was:

  “Text was truncated or one or more characters had no match in the
  target code page.”

Reason for this error:

The error is due to the lengthy data in the excel.
SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

Considering am having a column named "Name"
First 8 rows is of length <255
9th row is of length > 255.
While executing the package you will get the above error, its because your input value will be truncated and SSIS wont allow for that. In this case,
Two types of logics can be followed,

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

Please read the blog


How to select the mail profile in a sql agent job

Database Mail profiles work a bit differently when it comes to SQL Agent jobs. In configuring SQL Agent itself through the properties window you will configure the Alert System.

The two arrows point to where a database mail profile can be selected. In these two instances the profile you select is basically telling SQL Agent this is your SMTP configuration and who you are going to send the emails "as", or the "FROM" field. Configuring the Alert System does require restarting the SQL Agent service.

Once that is done in order to receive notifications for SQL Agent jobs you will need to configure Operators.

You would setup the operator to note who you want the notification to be sent "TO". You can put more than one email address in this field but it does have a character limit. You can create multiple operators but within the notifications page of the job properties you will notice it only allows you to select one. This is just the limitation of SQL Agent.

The alternative to this would be using the sp_send_db_mail within the job step itself. In this you can use any mail profile you want, in the event you wanted it to go to someone other than any operator you may have configured.


SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site Distribution

What you are describing would be either peer to peer replication, or merge replication.  Peer to peer is masterless, while merge replication has a master, however you can use mirroring on the publisher and mirror the publisher to another data center so that if the publisher goes offline the workload can fail over to another site.


Postresql, max returns all rows with same ids

If using a non-standard PostgreSQL construct is no problem, the easiest way would be

SELECT DISTINCT ON (product_id)
       product_id, store_id, available_count
FROM j_product_store_availability
ORDER BY product_id, available_count DESC, store_id;


How to calculate total memory used by one table?

Both are correct; both say the same values.  (Don't divide by 1024*1024; it will be a little more obvious.)

In InnoDB, the PRIMARY KEY is "clustered" with the data.  That is, they are both in the same BTree structure.  The PK provides the order; the data provides the content.

In your table, the data is ~4GB, including the PK and BTree overhead; the two indexes are in two separate BTrees, adding up to about 5GB.


What to do with a table with too many indexes?

Probably some of those indexes are used, so deleting them all is not a good idea.

You can review index usage stats and find bad NC indexes with this query (taken from Glenn Berry's Diagnostic Queries):

-- Possible Bad NC Indexes (writes > reads)  (Query 47) (Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
    i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
    user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
    user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND user_updates > (user_seeks + user_scans + user_lookups)
    AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

You can also find recommended missing indexes using this query (again from Glenn Berry's diagnostic queries):

-- Missing Indexes for current database by Index Advantage  (Query 48) (Missing Indexes)
SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],
    migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
    OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK)
    ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC OPTION (RECOMPILE);

Instead of creating the recommended missing indexes as suggested, try to combine them with existing indexes and find a balance to keep the number of indexes low.


Multiple Entries Return Lowest Data With Lowest Date

SELECT pn, MIN(d1) AS [oldestDate]
FROM @Test
GROUP BY pn

pn          oldestDate
Number One  2014-01-01
Number Two  2014-02-22


"ORA-01033: Oracle initialization or shutdown in progress" error after PC restart

The tutorial you linked uses the multitenant architecture with pluggable databases. I quickly read through the tutorial, and it does not cover this topic, but pluggable databases do not open automatically with the root container, unless specified otherwise. My guess is that you tried to connect the pdborcl pluggable database. Run the below:

select name, open_mode from v$pdbs;

If you see pdborcl, with open_mode = MOUNTED, issue the below:

alter pluggable database pdborcl open;

Now try connecting again. If your database version is 12.1.0.2, you can use the below after opening the pluggable database to make it start automatically the next time the whole database is restarted:

alter pluggable database pdborcl save state;

If your database version is 12.1.0.1, the above feature is not available, and you need to open the pluggable database each time manually, or create an after startup trigger in the database, for example:

CREATE OR REPLACE TRIGGER open_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/


Postgres client unable to detect UTF-8 database

psql detects the client_encoding from the LC_CTYPE variable in the environment; this falls back to LC_ALL and then LANG if unset.

In the terminal you're launching psql from, run locale. e.g.

$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

Paste the result as an edit to your question. I strongly suspect your terminal locale will be en_CA, not en_CA.UTF-8.


Why is IDENTITY_INSERT ON only allowed on one table at a time?

I think it's to make it difficult.  If you could just leave it on all the time, why even have an identity field?

There are actually a couple of restrictions, though:

It persists only on that connection
It's only settable on one table per connection

Based on the connection-related restrictions, I think it's mainly so it's never accidentally left ON.

Imagine if someone turned on ID insert on one of your tables, then you didn't realize and a (normally) invalid insert was performed that broke the integrity of your ID field?

Bear in mind ID fields can have duplicate values if there is no constraint or unique index in place...


Dimensional model or Relational model for data warehouse?

The point of a data mart / warehouse is to facilitate analysis for non-DBAs. The relational model is wonderful for transactional databases to speed writes integrally, but is harder to understand for non-DBAs.

A data warehouse is a collection of data marts, or can act as a central data mart. If you are going to use dimensional modelling in your data marts (as you should), then you should use it in your warehouse as well.

You will find that most companies use regular RDBMSs (say, Oracle, SQL Server, Postgres) for their data warehouse, though you probably want one that supports materialized views and easy table partitioning. Some companies use columnar databases (say, MonetDB) or NoSQL databases (say, HBase) as well.


Change the same field from all the tables from a particular database

Modify column by name(s):
you can also use any SQL Where, such as column_name in (...) or using other columns in
 information_schema.columns table.

select  concat('alter table ',table_schema,'.',table_name,' change column ... ;') from information_schema.columns  where column_name='r_id'

use group_concat or similar and just copy/paste.


1)  When to be sure that your database design is perfect?
Your design is never perfect because the business logic and amount of data is always changing. Perfect is difficult to define
I've seen systems that were great on deployment but had poor performance after a few years of data were added.  The regrettable trend to treat a database like a black box by some application developers means some databases are deployed with critical tables lacking primary keys or indexes.  Perfect to the CIO because they got the application delivered on time and on budget could be a pain in the butt to the developer/ DBA who has to deal with the problems.

Here are some of indicators I would look for which indicate your design is ready to go
a) Extensive use of primary keys, unique keys, foreign keys, indexes, more so on the larger tables ( I only mention this because I've seen commercial products which lack this)
b) Application logic is duplicated as far as is practical in the database with the use of constraints and default values.  You cannot capture everything but just knowing that there will always be a value for an entry provides peace of mind.
c) Test, test, test:  test from the user perspective on data entry, test from the manager perspective who wants an overview, test from the analyst perspective who wants to see trends
If you find yourself joining 9 large tables which require full table scans in order to find out what work is assigned to a user then maybe you need to reexamine things.
Not everything can be simple but excessive complexity to answer user needs is a hint of trouble to come.
d) do some daydreaming on how the database could and could not be extended.  If I need to add a new property to a unit of work how hard is it?  Can it be done without table changes?  If you are asked to add a new type of work how hard is it.  (Work could be a product, a transaction, a case)
e) people and organizations provide hours of work for me.  How easy is it to create, edit, de-duplicate and report on them?
f) how many user languages are you supporting? What character sets will be required.  If, for example, you intend to support English and Spanish, what happens if the design must be extended to cover French and Italian?

2) Is returning to the data base design to change some issues (like adding new column, delete a column or change data type or add new table or ....) considered as a bad practice or is it normal?

I would say it is normal for an application where the business logic changes frequently or the end user requirements are being added to.


Unattended install to listen on specified interface

With SQL Server on Windows, the registry entries can be modified using PowerShell. Below is an example with SQL Server 2017 paths for the default instance. Of course, the instance will need to be restarted for changes to become effective.

Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpdynamicports -Value ''
Set-ItemProperty -Path 'HKLM:\software\microsoft\microsoft sql server\mssql14.MSSQLSERVER\mssqlserver\supersocketnetlib\tcp\ipall' -Name tcpport -Value 1433

EDIT:

One can also target specific network interfaces by identifying the desired key and changing as desired. Below is an example PS function that finds the entry by IP address and modifies the values. This can be used to enable/disable TCP for each instance and interface as needed.

Function Set-SqlInterfaceProtocol($SQLInstanceTcpPath, $IPV4Address, $Enabled, $Active, $Port) {

    $interface = Get-ChildItem $SQLInstanceTcpPath `
        | ForEach-Object {Get-ItemProperty $_.pspath} `
        | Where-Object -Property IPAddress -EQ $IPV4Address

    if($interface -eq $null) {
        throw "No entry found for IP address $IPV4Address"
    }

    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Enabled" -Value $Enabled
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "Active" -Value $Active
    Set-ItemProperty -Path "$SQLInstanceTcpPath\$($interface.PSChildName)" -Name "TcpPort" -Value $Port
}

$SQLInstanceTcpPath = "HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp"
Set-SqlInterfaceProtocol -SQLInstanceTcpPath $SQLInstanceTcpPath -IPV4Address "10.1.1.123" -Enabled "1" -Active "1" -Port "1433"

Note this script is not well-tested so you may need to tweak it for your scenario.


MySQL intersection on one table with time type

It seems you need a self join with a non-equality condition that checks for overlaping ranges. The output can be in different formats. Depending on your needs, the easiest would be a 2-column output:

SELECT a.id AS a_id, b.id AS b_id
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil
ORDER BY a.id, b.id ;

Output would be (2 columns):

#1    #2   (the whole day shares the times with morning)
#1    #3   (the whole day shares the times with afternoon)
#2    #1   (the morning shares the times with the whole day)
#3    #1   (the afternoon shares the times with the whole day)

To get the rows combined, you can use GROUP_CONCAT():

SELECT a.id AS a_id,
       GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR ' ') AS b_ids
FROM calendar AS a
  JOIN calendar AS b
    ON  a.id <> b.id
    AND a.timefrom < b.timeuntil
    AND b.timefrom < a.timeuntil
GROUP BY a.id
ORDER BY a.id ;

Output (2-columns):

#1    #2 #3   (the whole day shares the times with morning, afternoon)
#2    #1      (the morning shares the times with the whole day)
#3    #1      (the afternoon shares the times with the whole day)

If you want one row per a.id and multiple columns, you'll need dynamic SQL.


The fact that he database drop-down is blank suggests to me that the backup file is invalid/corrupt in some way.

First, try and restore the backup file header to confirm that the file is a valid SQL backup. In Management Studio, connect to your SQL 2016 instance and open a new query window. Run the following:

RESTORE HEADERONLY FROM DISK = 'C:\Users\nasscribe\Documents\sibk\sibk'

If you receive an error from this query, either it is not a valid SQL backup file (possible that there was a write error during the backup process on your 2008 R2 instance, or you just grabbed the wrong file), or it is a permissions issue where the SQL Service account cannot access the file, and you'll see something similar to below:

  Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file
  "C:\Users\nasscribe\Documents\sibk\sibk". Operating system error
  5: "5(Access is denied.)".

If you receive the above message, copy the backup file to somewhere like C:\Temp\ and retry.

If instead you see an error like this:

  Msg 3241, Level 16, State 0, Line 3 The media family on device
  'C:\Users\nasscribe\Documents\sibk\sibk' is incorrectly formed. SQL Server cannot process
  this media family.

  Msg 3013, Level 16, State 1, Line 3 RESTORE
  HEADERONLY is terminating abnormally.

Then the backup file is not a valid SQL backup. Provided you still have access to your old server, connect to it and re-run the backup:

BACKUP DATABASE TO DISK = '[Backup path here]\sibk.bak' WITH STATS=5

Once done copy it over to your 2016 server and run the restore again.


MySQL proxy and SHOW PROCESSLIST query time

I am against writing to both Masters in a dual-master setup.  There are too many things that can go wrong, and they can be messy to fix -- AUTO_INCREMENTs, other duplicate keys, etc.

Hundreds of "Sleep" connections is virtually no impact on a server, so limiting to 40 is not useful.  10 or more active connections (non-Sleep) can be an issue.  In that case I would look at the queries.  Usually optimizing the queries is the best answer.

Note also, that every write (INSERT, UPDATE, etc) that is done on one Master must be done on all the other Masters and Slaves.  So, you can't really "spread" writes around.

If you have processes that do only reads (SELECTs), then they should go to Slaves and/or the backup Master, not the live, writable, Master.  This will help.

Be aware of the "critical write" problem.  Example:  A user posts a blog comment, then looks at his comments, but it is missing.  This can happen if the write went to one machine, but the read hit another, and replication is "behind".

(My comments apply to all versions, and all APIs, not just 5.1 and PHP's mysqli.)

I stay away from mysql_pconnect (and other connection pooling mechanisms).  Connection startup/teardown is very fast in MySQL.  Pooled connections may have issues with @variables, transaction modes, sql_modes, etc.


Can I create a database on a local folder mapped as a drive

subst is scoped per session. Your command and the instance run on different sessions.

Creating a database on a subst virtual folder does not sound like a good idea. What are you trying to achieve?


IP Addresses tab is not showing a specific IP Address

I don't know the official way that Microsoft recommends adding a new ip address to the network configuration panels, and I don't know how you would do it with SQL Server 2008, I've only worked with SQL Server 2012+, so take these with a grain of salt.
For reference, here's Microsoft's page on editing the TCP/IP properties panels - https://docs.microsoft.com/en-us/sql/tools/configuration-manager/tcp-ip-properties-ip-addresses-tab?view=sql-server-ver15
IF all of your ip addresses on the host are going to be for listening to SQL Server, you could tell it to 'Listen All' = 'Yes'. Instead of then having to specify the ip addresses and ports you would then just specify the port under the 'IPAll' region. Now, this is not for everybody, but it is an option.
If using the 'IPAll' doesn't work for you, and you are lucky enough to have an 'IPx' that isn't in use you can edit the 'IP Address' box and 'TCP Port' fields to make it work for the new ip address. Just make sure to check 'Enabled' = 'Yes' otherwise SQL Server will ignore it.
Now, if you can't just blanket listen to all ip addresses on the same port or just edit an existing IPx field you are going to have to add another 'IPx' field. Here's how I add an 'IPx' field to the panel, but I don't think it's how Microsoft officially recommends people to do it, and unfortunately it's a registry edit. With that being said, if you are not comfortable working with the registry DO NOT DO THIS. As always, back up your registry, take preventative measures, and test it out in development before applying this in your production environment. In order to get a new 'IPx' field to show up you have to create a new registry key at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.(INSTANCENAME)\MSSQLServer\SuperSocketNetLib\Tcp\IPy (where IPy is the number that comes after the largest existing one) and copy over the key entries found in the other IPx registry entries. You should see registry entries that say 'Active' and 'IpAddress' under the registry entry that you copy over.
I am going to be very interested to see how others solve this to hopefully make it so that I don't have to edit the registry in the future.


Is there a more efficient way to resolve this "one to many" circular reference?

If this is your current table schema:

CREATE TABLE PRODUCTS
(
   PRODUCT_ID INT PRIMARY KEY,
   PRODUCT_SKU INT,
   KIT_ID INT
);

CREATE TABLE PRODUCT_KIT_ITEMS
(
    KIT_ID INT,
    PRODUCT_ID INT REFERENCES PRODUCTS(PRODUCT_ID),

    PRIMARY KEY (KIT_ID, PRODUCT_ID)
);

INSERT INTO PRODUCTS VALUES
(1, 10, 1), (2, 20, 0), (3, 30, 0), (4, 40, 2),
(5, 50, 0), (6, 60, 0), (7, 70, 0), (8, 80, 0);

INSERT INTO PRODUCT_KIT_ITEMS VALUES
(1, 2), (1, 3), (1, 6), -- KIT_ID(1) HOLDS PRODUCT_ID(2, 3, 6)
(2, 5), (2, 7);         -- KIT_ID(2) HOLDS PRODUCT_ID(5, 7)
GO

13 rows affected

You can get it by selecting all products with KIT_ID <> 0 (Parent rows) and then use CROSS APPLY to select all child rows that match parent KIT_ID.

SELECT      P.PRODUCT_SKU AS PARENT_SKU,
            CHILDS.PRODUCT_SKU AS CHILD_SKU
FROM        PRODUCTS P
CROSS APPLY (SELECT P2.PRODUCT_SKU
             FROM   PRODUCT_KIT_ITEMS PKI
             JOIN   PRODUCTS P2
             ON     P2.PRODUCT_ID = PKI.PRODUCT_ID
             WHERE  PKI.KIT_ID = P.KIT_ID) CHILDS
WHERE        COALESCE(KIT_ID, 0) <> 0
ORDER BY     P.PRODUCT_SKU;
GO

PARENT_SKU | CHILD_SKU
---------: | --------:
        10 |        20
        10 |        30
        10 |        60
        40 |        50
        40 |        70

Your current query:

SELECT   Parent.[PRODUCT_SKU] AS KitParent,
         Child.[PRODUCT_SKU] AS ChildSKU
FROM     [PRODUCT_KIT_ITEMS]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU]
          FROM   [PRODUCTS]
         ) AS Child
ON       Child.[PRODUCT_ID] = [PRODUCT_KIT_ITEMS].[PRODUCT_ID]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU], [KIT_ID]
          FROM   [PRODUCTS]
          WHERE  [KIT_ID] IS NOT NULL
          AND    [KIT_ID] != 0
         ) AS Parent
ON       Parent.[KIT_ID] = [PRODUCT_KIT_ITEMS].[KIT_ID]
ORDER BY Parent.[PRODUCT_SKU];
GO

KitParent | ChildSKU
--------: | -------:
       10 |       20
       10 |       30
       10 |       60
       40 |       50
       40 |       70

dbfiddle here


My gut feeling is that you did not update user "john's" profile to invoke the DB2 profile setup. If you do not do this, then you won't have "access" to DB2 per say, even though the binaries are there.

So what you want to do is write a script that checks for your DB2 instance "home" profile script
in your case it would be /home/db2inst1/sqllib/db2profile and if it is there, to invoke it.

if [ -f /home/db2inst1/sqllib/db2profile ] ; then
     . /home/db2inst1/sqllib/db2profile
     if [ ! $? = 0 ] ; then
          echo "Error setting up DB2 instance access"
     else
          echo "DB2 instance access set up"
     fi
fi


Of course you can put it up on Dropbox - or anywhere else.

Depends on how secure you want your data to be? Do you want the NSA looking at it? Dropbox employees?

I would put up an encrypted version of your mysqldump and securely transmit the key to your colleague - either face to face or over encrypted email, or have the password/key sent by courrier.

It all depends on how much the data is worth to you and/or your company. Is it worth a courrier? Do I encrypt my email? Those are questions only you can answer. If your internal system is good, then go with that.

The key (no pun intended) question is "how much is your data worth (now and in the future)"? You should afford your data as much protection as is reasonable given its value to you/your organisation.

If you're interested in these matters, go to the source of all wisdom - Bruce Schneier. "Security is a process, not a state". Once you start thinking in these terms, then solutions will become apparent. What is your data worth now? In a day's time? A year's? What would be the result if all of this data went live on internet now? If you wish to learn more, then I suggest you go to the crypto group - you ain't seen paranoid until you've lurked there for a while! :-)


Replace value based on previous row / next row depending on conditions

Add this composite index if you don't already have it -- it should probably be the PRIMARY KEY.

INDEX(company_id, date_time)

Run this a few times (until it stops modifying any rows)

UPDATE    tbl a
    JOIN  tbl b
       ON  b.company_id = a.company_id
      AND  b.date_time  = a.date_time + INTERVAL 1 MINUTE
    SET a.price_open = b.price_open
    WHERE  a.price_open  = 0.00
      AND  b.price_open != 0.00;

In case I am wrong, suggest you copy the table over for testing:

CREATE TABLE tbl LIKE real_tbl;
ALTER TABLE tbl DROP PRIMARY KEY,
                ADD  PRIMARY KEY(company_id, date_time);  -- if needed
INSERT INTO tbl SELECT * FROM real_tbl;
run the update a few times
check the values
if satisfied, ...
RENAME TABLE real_tbl TO old, tbl TO real_tbl;
DROP TABLE old;

On the presumption that your SELECTs will be company-centric, it is better to have PRIMARY KEY(co, dt) than (dt, co).


How to recover a crashed innodb table...?

I created a table called test2

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

I then inserted one record

insert into test2 values(1);

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

I then started MySQL and saw the following

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

I then created another table with the same structure

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

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

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

I restarted MySQL and had access to test2

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

Here is the record that I inserted

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

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


For your SUM issue, please see this article for a detailed description of Oracle solutions that may be helpful --> https://community.oracle.com/thread/2137764?tstart=0

For the LISTAGG issue with duplicate values and values from other columns, check out this article for some solutions that may work for your need --> https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values

With some quick reading and not being an Oracle SQL guy, this seems as if there are a couple things in these that may help but I cannot easily provide an example query for you to use at the current moment unfortunately with this table data examples you provided.

Hopefully this will be helpful or point you in the right direction for the two issues I see with what results you get and what results you need.


sp_send_dbmail: Failing When Adding Attachment

After fighting with this for half a day I took SQL out of the equation and tried to perform the same task using Power Shell. This also failed, my network guy ran a PCAP between SQL and relay server and we have identified the issue.
File was getting to the relay server but for whatever reason on that particular SQL server (AWS) by the time the file made it to relay the file was fragmented, wasn't sending the full file.
This SQL box was built before my time by someone who 'DID' SQL, it has several setup and config issues so I am pushing for a rebuild.


Mongodb - Will Bulk.find.remove() lock the collection

The short answer is: No, you will not be blocked.

When performing multi-document write operations, whether through a single write operation or multiple write operations, other operations may interleave.

The long explains are:

Collection is granular level (or the resource).

Whenever there is a write operation, Exclusive (X) lock mode must be applied on the resource.

As Collection is the resource in this scenario, Exclusive (X) lock mode must be applied on the Collection.

For all outer levels : Database, Global; Intent Exclusive (IX) must be applied.

When a write operation is triggered for a Collection, the control must do the following series of locks :

The MongoD instance must be locked in Intent Exclusive (IX) lock mode, with an intent to apply write operation in a granular level (Database / Collection / Document).
The Database must be locked in Intent Exclusive (IX) lock mode, with an intent to apply write operation in a granular level (Collection / Document).
The Collection must be locked in Exclusive (X) lock mode, to apply write operation on the Collection.

You can find more info in the following links:

https://docs.mongodb.com/manual/core/write-operations-atomicity/
https://docs.mongodb.com/manual/faq/concurrency/
https://docs.mongodb.com/manual/reference/method/Bulk.find.remove/
https://www.tutorialkart.com/mongodb/mongodb-locks/


Find which databases have a particular service in SQL Server Service Broker

you can have the same service in different databases on the same instance and in that case, you should use the service_broker_guid to distinguish them when you BEGIN DIALOG CONVERSATION

No.  Typically you should let Service Broker Routing to find the correct target service for the new conversation.  If you have multiple services with the same name on a SQL Instance, the default behavior is to round-robin new conversations across the matching broker instances.  If you want all the conversations initiated by from a database targeting a service b to route to a specific database, then add a route in the initiating database specifying the broker_id of the target database.  EG

CREATE ROUTE b_route
    WITH
    SERVICE_NAME = 'b',
    BROKER_INSTANCE = 'EF782150-7F7B-4544-937E-B03CF2A3248C',
    ADDRESS = 'LOCAL' ;

This allows you to specify the routing at deployment time, and your service broker code does not need to specify the target broker instance.


Index rebuild not working with SQL Server 2016

As mentioned in the comments there are various factors that could cause fragmentation, and following are the areas that i can think of in your case.

Fill Factor
Seems it's already lower than normal on particular index. However, consider fine tuning with it (in development server) if following options are not applicable in your scenario.

Auto Shrink
Verify by any chance the AUTO SHRINK option set to ON at database level as this could cause heavy fragmentation

GUID Columns
There is also possibility when the entries are inserted without an order, switching to SEQUENTIAL-GUID would be better option (if possible)


The only answer here, unfortunately is that mirroring is only supported for the pulbisher and only if the distributor is on a different server from with the principal or mirror. I would recommend a dedicated server for the distributor, but even using the subscriber server would work.

Database mirroring is not supported for distributors or for subscribers, and the work-around for mirroring a subscriber as detailed in the whitepaper by SQLCat only works for transactional replication. You could still use the manual sync option if you want to try to do something similar, but you'd have to get the data in perfect sync yourself which is very difficult to do unless you stop traffic to the publisher.


You cannot use declare and any code manipulation in view (select statement ONLY at SQL Server of course).

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

See for more details

Use stored procedure instead of view


What is an "Obfuscation key" in SQL Server?

I would guess they mean rows created for symmetric keys.

create symmetric key KeyTest with
  algorithm = aes_128,
  identity_value = '123456'
  encryption by password = '123456!Q';

go

-- Needs a DAC connection
select *
from sys.sysclsobjs
where Name = 'KeyTest'

Result:

class id    name      status  type intprop  created                 modified
----- ----- --------- ------- ---- -------- ----------------------- -----------------------
56    259   KeyTest   0       A1   128      2017-12-22 08:43:33.170 2017-12-22 08:43:33.170


How can I create readonly user for backups in PostgreSQL?

No, it's easy (now anyway).

Grant the connect permission on a new user

GRANT CONNECT ON DATABASE mydb TO myReadolyUser;

Grant the permissions on all the current database objects. This is schema-specific, and you'll have to run one copy for every schema you wish for your user to use,

GRANT SELECT ON ALL TABLES IN SCHEMA mySchema TO myReadonlyUser;

From the docs, ALL TABLES includes everything you'd want.

  There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views and foreign tables.

Then ALTER DEFAULT PRIVLEGES to grant future SELECT privileges for objects not yet created.

ALTER DEFAULT PRIVILEGES IN SCHEMA mySchema
GRANT SELECT ON TABLES TO myReadonlyUser;


I resolved this issue by creating a named query in the DSV which contains the product key and productorder keys, linked these back to the two fact tables, created a new dim for production orders (so i had one for each fact table),  and created a new measure group off of the named query which acts as a bridge.  In dim usage I then setup regular relationships to the 2 dims for each fact table.  This seems to have fixed my issue!


MySQL check duplicate with group by using wildcard?

Your query can return the expected results by adding the reverse condition:

SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM persons A
LEFT JOIN persons B
ON a.ID <> b.ID
AND (a.Name LIKE CONCAT ("%", b.Name, "%") OR b.Name LIKE CONCAT ("%", a.Name, "%"))
ORDER BY ID;

I don't know if it will be faster, but another way to do it would be to use INSTR:

SELECT A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM persons A
LEFT JOIN persons B
ON a.ID <> b.ID
AND (Instr(a.Name, b.Name) > 0 OR Instr(b.Name, a.Name) > 0)
ORDER BY ID;

SQL Fiddle


How does SQL Server determine the expression type when mixing Unicode and non-Unicode strings?

There are several situations in which two (or more) distinct things are being combined and yet there can only be a single result. These questions are resolved by applying a set of precedence rules. In this case, it would use both Datatype Precedence and Collation Precedence. But there is also logical precedence (when combining AND and OR without parenthesis) and how to deal with decimal computations of varying precision, etc).

Data Type Precedence
Collation Precedence
Precision, Scale, and Length

Related:

Operator Precedence


Oracle Database Vault: Changing DV Owner and DV Account-Manager passwords hangs PX Deq: Execute Reply/row cache lock

We opened a SR at Oracle support. It was identified as a bug and forwarded to development.
Workarounds:

Give DV_PATCH_ADMIN role to user changing pwd.
Close pdb and execute in cdb.


How to configure TCPS in JDBC thin client for oracle

To setup TCPS with JDBC, we have a few tasks.
First, create the wallet in both client and server:
orapki wallet create -wallet "wallet" -pwd XXXXXXXX -auto_login

Then, create self-signed certificates (or signed by a trusted CA)
orapki wallet add -wallet "wallet" -pwd XXXXXXXX -dn "CN=%COMPUTERNAME%" -keysize 1024 -self_signed -sign_alg sha256 -validity 3650

Exchange via SFTP/SCP and import each other certificate in the wallet
orapki wallet export -wallet "wallet" -pwd XXXXXXXX -dn "CN=%COMPUTERNAME%" -cert ./%COMPUTERNAME%.crt

orapki wallet add -wallet "wallet" -pwd XXXXXXXX -trusted_cert -cert %REMOTECOMPUTER%.crt

Now indicate in SQLNET.ORA the location of the wallet (in TNS_ADMIN folder, in both client and server).
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = D:\oracle\wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Now edit listener.ora in the server to add the secure endpoint (PROTOCOL = TCPS):
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mydbservername)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mydbservername)(PORT = 2484))
    )
  )

Restart the listener with lsnrctl stop, start and status to check that your line is showing up.
Update the TNSNAMES.ORA accordingly in both client and server.
MYDB_SSL=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)
      (HOST=MYDBSERVERNAME)
      (PORT=2484)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=REP3)
    )
  )

The TNS_ADMIN folder in the client has to be the same used for the wallet, so place your sqlnet.ora and tnsnames.ora in that folder only.
If everything is good so far, tnsping MYDB_SSL will return OK.
D:\>tnsping MYDB_SSL

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 05-FEB-2021 13:10:59

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS) (HOST=MYDBSERVERNAME) (PORT=2484)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))
OK (1100 msec)

Otherwise, check lsnrctl on the server side, find the log and troubleshoot.
On the java side, set the JDBC URL as per the example below (for Spring/JPA):
spring:
  jpa:
    database: oracle
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect
  datasource:
    url: jdbc:oracle:thin:@MYDB_SSL?TNS_ADMIN=\\oracledb\\wallet

Load the certificates in the cacerts with the keytool command:
keytool -importcert -file %ORACLE_BASE%\%CN%.crt -keystore %CACERTS% -storepass changeit -alias %CN%

Run the app, you may specify the TNS_ADMIN in the command line too:
%JAVA% -Doracle.net.tns_admin=%ORACLE_BASE%\wallet -jar myapp.jar...

Hope this helps...


Mysql Server always throwing unknown error

I found the answer through this post MySQL 5.7 No Longer Returns Meaningful Error Messages.
On analyzing the mysql log in the path /var/log/mysqld.log. the error was decribed as [ERROR] Can't find error-message file '/usr/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.


How should I represent a midnight end time?

Introduction

You might want to consider that 23:59:59.999 belongs to the current day. 00:00:00.000 is considered to be the transition from one day to the next. Let me explain...

Reference: Midnight (Wikepedia).

24 Hour Notation

Depending on whether you are using the 24 hour notation or the 12 hour am/pm notation the following bits of information might be equally interesting.

Midday / Lunch

Midday is not an issue with the 24 hour notation because the step from 11:59:59.999to 12:00:00.000 and onward to 12:00:00.001 have no impact on the time format.

Midnight

Looking at the times around midnight you will find that 23:59:59.999 is still the same day, whereas 00:00:00.001 belongs to the next day. Following this logic the static value 00:00:00.000 should not belong to either day. It is the point of transition.

However, some RDBMS systems will switch the date when adding 0.001 s to 2018-03-07 23:59:59.999.

12 hour notation

Midday / Lunch

Midday in 12 hour notation is neither am nor pm, even though clocks will tend to add a PM as soon as the clock jumps from 11:59:59.999 to 12:00:00.000. Midday should actually have a single m added when static, seeing that a.m. is defined as ante-meridiem which translates to before midday, and p.m. which is post-merdiem translates to after midday.

Midnight

This is equally valid for the 12 hour notation around midnight. 11:59:59.999 p.m is near midnight. 12:00:00.001 a.m. is just after midnight. 12:00:00 a.m./p.m. is midnight and is exactly 12 hours after midday or before the new midday.

However, most systems decide to add an a.m. as soon as the clock reaches midnight at 12 o'clock.

Examples

PostgreSQL

Adding a millisecond to a timestamp value.

Input:

postgres=# select TIMESTAMP '2018-03-07 23:59:59.999' + interval '1 ms';

Output:

 2018-03-08 00:00:00

SQL Server

Adding a millisecond to a datetime value.

Input:

select dateadd(ms,001,cast('2018-03-07 23:59:59.999' as datetime))

Output:

2018-03-08 00:00:00.000

Recommendations

As you can see with the examples, both tested RDBMS systems determine 00:00:00.000 to be the time (after or roundabout) midnight but for the next day.

If you aren't using dates together with the time, then avoid using 00:00:00.000 as the notation for midnight following 23:59:59.999 (or the 11:59:59.999 p.m.)the same day. This corresponds with your solution number 1.
If you are using dates together with the times, then find out which notation your RDBMS supports when adding a microsecond to either 2018-03-07 11:59:59.999 or 2018-03-07 11:59:59.999 p.m.

Full Reference List

How should “midnight on…” be interpreted? (English Language & Usage)
When is it AM and when is it PM? (Arstechnica)
12 am - 00.00 (WordReference.com)
Is midnight 12 a.m. or 12 p.m.? (FAQ - Time) (National Physical Laboratory [U.K.])
A.M. and P.M. (Greenwichmeantime.com)
Midnight (Wikepedia)
9.9. Date/Time Functions and Operators (PostgreSQL)
8.5. Date/Time Types (PostgreSQL)
DATEADD (Microsoft Docs)


Get matching part from conditional pattern

WITH
cte AS ( SELECT value AS pattern            -- or TRIM(value)
         FROM STRING_SPLIT('%m%,%p%', ',')  -- comma is NOT allowed in a pattern
                                            -- or alter your delimiter
       )
SELECT Search.[id],
       Search.[key],
       Search.[value],
       STRING_AGG(cte.pattern, ',') [match]
FROM Search,
JOIN cte ON Search.[value] LIKE cte.pattern -- or LEFT JOIN
GROUP BY Search.[id],
         Search.[key],
         Search.[value]

fiddle

Possibe CTE replace usable on previous server versions (CHAR(9) is used as a delimiter, @pattern declared before cte must be query parameter transferred from client as literal):

DECLARE @pattern VARCHAR(64) = '%m%'+CHAR(9)+'%p%';

WITH cte AS ( SELECT LEFT(@pattern,CHARINDEX(CHAR(9),@pattern)-1) pattern,
                     SUBSTRING(@pattern, CHARINDEX(CHAR(9),@pattern)+1, LEN(@pattern)) slack
              UNION ALL
              SELECT LEFT(slack,CHARINDEX(CHAR(9),slack)-1),
                     SUBSTRING(slack, CHARINDEX(CHAR(9),slack)+1, LEN(slack))
              FROM cte
              WHERE CHARINDEX(CHAR(9),slack) > 0
              UNION ALL
              SELECT slack, ''
              FROM cte
              WHERE CHARINDEX(CHAR(9),slack) = 0
                AND slack != ''
            )

fiddle

STRING_AGG() must be replaced too...


Value of an UNSET NEW.Column in Before Update Trigger

Maybe mysql documentation  is not so clear, but NEW is in fact refers to pseudorecord which contains new values (let's ignore access right for simplicity).  It's probably more accurate to say that it gives you access to the row as it will be stored in the table , whereas OLD gives access to the row as it was right before UPDATE/DELETE executed.
Keeping that in mind, comparision in trigger body OLD.PASSWORD <> NEW.PASSWORD  will be true (assuming "password" field is not null, otherwise you need to use IFNULL or something similar) only if PASSWORD field was actually changed by UPDATE statement.


Inserting empty string on Windows MySQL doesn't use default value

PROBLEM

You need to insert nothing ?

SOLUTION

Then, insert nothing at all ... literally !!!

One of the following will work

INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);

SAMPLE DATA

use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE test2
    -> (id int not null auto_increment primary key,
    -> test int default 0);
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql>

QUERIES EXECUTED

If id and test are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2 besides id and test, name the test column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0 will become 0.

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
+----+------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT #1 : I did this in Windows 8.1 from the command line

mysql> select * from information_schema.global_variables
    -> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
| VERSION                 | 5.6.15                       |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)

mysql>

CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.


For MS SQL Server.
False: You can use the Import Data Wizard to map your columns in the CSV file to columns in the Table that the Wizard will create when you run it.


Product attribute schema design, mixing solutions

Put common things (price, SKU) in MySQL columns; index them if needed.

Put the rest in a JSON column.  Do not try to use LIKE to search that.

Instead, first filter on what you can from the other columns, then use your application language to finish the filtering.

To save on space and bandwidth, compress (in the client) the JSON string and put it into a BLOB column.

Write your user-interface to encourage them to filter on the real columns, only reluctantly let them specify the other things.

See also MariaDB's "Dynamic Columns; it's been available for several releases.  In the future, 5.7 promises to have a bunch of JSON tools.


Your second clause is triggering as true before your third clause and as such your third clause does not get evaluated - CASE WHEN returns the first TRUE value as opposed to the last TRUE value

@Val1 = 5 TRUE

@Val2  IN(1,2) TRUE

@Val3 = 200000 TRUE

@Val4 IS NOT NULL TRUE


Can't restore sql bak file to a new database

Close all tabs on the right side in the management studio and try again.  It is possible that you clicked on the database and some tab on the right side is accessing it.

Alternatively, you may use this sql (replace file name and path)

USE master;
GO
ALTER DATABASE DELVIPROD_JUNE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
restore database DELVIPROD_JUNE FROM DISK = 'C:\temp\db.bak'   WITH replace
ALTER DATABASE DELVIPROD_JUNE SET MULTI_USER;

If you want to make a copy of a database, this might help https://serverfault.com/questions/62590/how-to-duplicate-mssql-database-on-the-same-or-another-server


Detail of all transactions on a specific row

Edit: Forgot that Change data capture is already in 2008, you could use it to capture the data, or create your own log/history table using triggers or something similar.

If you were in SQL Server 2016, then you could use temporal tables


I killed a session and it is still running?

From a thread on AskTom, when a session is killed:

  The session is killed but it is being cleaned up.  When you kill a session --
  outstanding work must still be cleaned up.

When you do DML on a table, Oracle may make the changes to the base table almost immediately, even if you don't commit. The other sessions don't see these changes yet thanks to multiversion read consistency but the actual physical block may already be overwritten with your uncommited work.

This is done because Oracle is optimized for commit. Most sessions should rarely rollback so Oracle anticipates by writing preemptively the changes. This is also one of the reasons that allows Oracle to have arbitrarily large transactions (limited only by the size of the undo tablespace). The changes are not delayed (much). This is also why you almost never wait for a commit in Oracle, even for multimillion-row DML.

One of the consequences is that rollback needs to undo the changes. The uncommited transaction is read back from the undo tablespace and each and every change is undone in reverse order. This can take longer than the initial DML for several reasons:

The rollback is not as well optimized, in particular multi-row operations can take a lot longer than regular DML.
Some changes are more expensive in reverse. For instance a delete which is a relatively cheap DML becomes an insert in reverse (which is more expensive).

This explains why rollback can take a lot of time. You have few means to accelerate the process. In any case you should not:

kill the rollback process
shutdown the database in the hope that this will terminate the session

If you shutdown the database normally (or transactional or immediate), Oracle will wait for the process to complete so that the database is left in a consistent state. If you shutdown ABORT, the database will be left in an inconsistent state (uncommited data written to disk) and Oracle will resume the rollback as soon as the database is restarted.

The rollback is handled by smon, I wouldn't try to kill it.

Some session, after being killed, will remain in the v$session view, even after all their changes have been entirely rolled back. This is often because Oracle is waiting for the client to return a relevant error message, such as:

ora-00028 your session has been killed

If the client itself has already left or the network communication has been severed, the killed session may stay indefinitely until you restart the instance. I don't think these sessions take resources.


You have transposed your desired result so it is rather straightforward to achieve what you want:

select storenum
     , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1)
from monster.dbo.storesales
group by storenum
    , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end

Now you can map each storenum / notes combination to a text, something like:

select case when storenum = '1463' and x = 0 then 'Passed w/ notes'
            when storenum = '1463' and x = 1 then ...
            when ...
     , cnt
from (
    select storenum
         , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end as x
     , count(1) as cnt
    from monster.dbo.storesales
    group by storenum
           , case when CAST(notes As VARCHAR(MAX)) IS NOT NULL then 1 else 0 end
) as T


Create a foreign key to a table that doesn't exist in postgres

Not possible. The source and target of a foreign key constraint must exist when you create the foreign key.

The usual solution to that problem is, to have all foreign keys in a separate script that is run after all tables have been created. Not together with each table.


I contacted uk@neo4j.com to ask for clarification and the answer they gave is that:

  There is a limit on node count and relationship count of 34Bn each

and

  The 34bn limit doesn't apply to Neo4j Enterprise

The release announcement for 3.0 mentions a 34bn limit that no longer applies:

  Dynamic pointer compression expands Neo4j’s available address space as needed, making it possible to store graphs of any size. That’s right: no more 34 billion node limits!

So my guess is that 'Dynamic pointer compression' is an Enterprise Edition feature.


ROLLUP was implemented in Postgres 9.5.

Original answer for Postgres 9.4:
The rollup() in group by rollup(i_category,i_class) causes aggregation in hierarchical steps. It's an extension of the ISO SQL standard in SQL Server that's not implemented in Postgres. For two items, you need three steps in standard SQL - as well as in Postgres:

aggregate total
group by i_category
group by i_category, i_class

The grouping() function comes with this extension and indicates whether a given column is aggregated in each row. It's used here to sort the total and group sums first in the result.
rank() is a standard window function that works the same in Postgres, but we have to rewrite it due to the above adjustments.
top 100 translates to FETCH FIRST 100 ROWS ONLY in standard SQL, which is implemented in Postgres as well as the shorter LIMIT 100 (FETCH FIRST syntax works in SQL Server for 2012+ versions, too.) It's a bit odd to cut off after 100 rows in this aggregate query.
I am using a CTE to reuse the intermediary results in multiple aggregations below. Except for the LIMIT everything should basically work in SQL Server just as well:
WITH cte AS (
   SELECT sum(ss_net_profit) AS sum_profit
        , sum(ss_ext_sales_price) AS sum_price
        , i_category, i_class
   FROM   ssv_itemv_storev_IJGBAgg
   WHERE  d_year = 2001
   AND    s_state = 'TN'
   GROUP  BY i_category, i_class
   )
SELECT sum(sum_profit)/sum(sum_price) AS gross_margin
     , NULL AS i_category, NULL AS i_class
     , 2 AS lochierarchy
     , 1 AS rank_within_parent
FROM   cte

UNION ALL
(  -- parentheses required!
SELECT sum(sum_profit)/sum(sum_price) AS gross_margin
     , i_category, NULL AS i_class
     , 1 AS lochierarchy
     , rank() OVER (ORDER BY sum(sum_profit)/sum(sum_price)) AS rank_within_parent
FROM   cte
GROUP  BY i_category
ORDER  BY rank_within_parent, i_category  -- last item = my addition to break ties
)

UNION ALL
(
SELECT sum_profit/sum_price AS gross_margin
     , i_category, i_class
     , 0 AS lochierarchy
     , rank() OVER (PARTITION BY i_category
                    ORDER BY sum_profit/sum_price) AS rank_within_parent
FROM   cte
ORDER  BY i_category, rank_within_parent, i_class  -- last item = my addition to break ties
)
LIMIT 100;


query_cache_type on mysql galera cluster

No Query Cache on Galera Cluster.  It is much to complex to keep it in sync among the nodes.

Instead, depend on the buffer_pool, etc, to make the queries "fast enough".

In 95+% of Production systems, the Query Cache (or any other caching) is a waste.  Why?  Because data is flowing in so fast that the cache must be all-too-frequently purged.

Perhaps your application is mostly read-only?  That might be a justification for a read cache.

Perhaps your read queries are so slow that you have turned to caching as a 'fix'.  Instead, let's look at the queries.  Often, there is a simple solution (such as a composite index) to speed up reads to the point where caching is unnecessary.


Are we required to handle Transaction in C# Code as well as in stored procedure

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I am recommending is:

Always have the TRY / CATCH structure so that errors can be properly bubbled up
Optionally include the 3 Transaction handling pieces in the code below if you have multiple DML statements (since a single statement is a transaction in itself). HOWEVER, outside of adding some additional code where it isn't specifically needed, if one prefers to have a consistent template, then it doesn't hurt to keep in the 3 Transaction-related IF blocks. But in that case I would still recommend not keeping the 3 Transaction-related IF blocks for SELECT-only (i.e. read-only) procs.

When doing 2 or more DML statements, you need to use something along the lines of the following (which can also be done for single DML operations if one prefers to be consistent):

CREATE PROCEDURE [SchemaName].[ProcedureName]
(
    @Param  DataType
    ...
)
AS
SET NOCOUNT ON;
DECLARE @InNestedTransaction BIT;

BEGIN TRY

    IF (@@TRANCOUNT = 0)
    BEGIN
        SET @InNestedTransaction = 0;
        BEGIN TRAN; -- only start a transaction if not already in one
    END;
    ELSE
    BEGIN
        SET @InNestedTransaction = 1;
    END;

    -- { 2 or more DML statements (i.e. INSERT / UPDATE / DELETE) }

    IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
    BEGIN
        COMMIT;
    END;

END TRY
BEGIN CATCH

    IF (@@TRANCOUNT > 0 AND @InNestedTransaction = 0)
    BEGIN
        ROLLBACK;
    END;

    DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
            @ErrorState     INT = ERROR_STATE(),
            @ErrorSeverity  INT = ERROR_SEVERITY();

    -- optionally concatenate ERROR_NUMBER() and/or ERROR_LINE() into @ErrorMessage

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN;

END CATCH;

When doing just 1 DML statement or just a SELECT, you can get away with just the following:

CREATE PROCEDURE [SchemaName].[ProcedureName]
(
    @Param  DataType
    ...
)
AS
SET NOCOUNT ON;

BEGIN TRY

    -- { 0 or 1 DML statements (i.e. INSERT / UPDATE / DELETE) }

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE(),
            @ErrorState     INT = ERROR_STATE(),
            @ErrorSeverity  INT = ERROR_SEVERITY();

    -- optionally concatenate ERROR_NUMBER() and/or ERROR_LINE() into @ErrorMessage

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    RETURN;

END CATCH;

Second, you should handle the transaction at the app layer only if you need to execute more than 1 query/stored procedure and they all need to be grouped into an atomic operation. Doing a single SqlCommand.Execute___ only needs to be in a try / catch, but not in a Transaction.

But, does it hurt to do a Transaction at the app layer when only making a single call? If it requires MSDTC (Microsoft Distributed Transaction Coordinator) then it is a bit heavier on the system to do this at the app layer when not expressly needed. Personally, I prefer to avoid app layer-based transactions unless absolutely necessary as it cuts down on the potential for orphaned transactions (if something went wrong with the app code prior to doing the commit or rollback). I also have found that it sometimes make debugging certain situations a little more difficult. But that being said, I don't see anything technically wrong with also handling the transaction at the app layer when making a single proc call; again, a single DML statement is its own transaction and doesn't need any explicit transaction handling at either layer.


Is any possible way to get warning message through by oracle

Compiler messages are not returned to the client through the getWarnings() on the statement or the connection.

Instead once you have identified the warning through Statement.getWarnings() you need to parse retrieve the compiler errors from the view ALL_ERRORS

Something like:

SELECT line,
       position,
       text,
       name,
       type
FROM all_errors
WHERE owner = user
AND type = 'FUNCTION'
AND name = 'FN_OEDS_CONVERTDATE2'
ORDER BY line, position


Why turning off condition_fanout_filter speed up query

you have one of the cases where the optimizer searching for a good solution costs much more time than the query it self, but that can change with increasing numbers.
You should read about what this optimization switch does.
if you need more speed you can try if one of https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html work better. additinal to different indexes


GZip existing varbinary(max) column in Sql Server 2008+

You can use .NETs GZipStream Class with SQLCLR to do the compression since you are on SQL Server 2008.

Solomon Rutzky has a free SQLCLR version (Util_GZip and Util_GUnzip functions) that you can test and use.

See : Alternative way to compress NVARCHAR(MAX)?


How do I run a batch file from a scheduled Job via SQL Server Agent

If memory serves me correctly:

cmd.exe /c "c:\test.bat"

Quotes are needed if there are any spaces in the path or file name


How do I determine what MDF files are in use

The  Best way  to delete  databases in sql server is     using    DROP DATABASE  command .
If you right click and delete in SSMS  you will end up with files left in folder .

Then You have to manually remove the files from server folder .

To remove these files from folder , first bring sql server to online  then go the folder and start deleting  the files ,  when deleting mdf or ldf files  you will get error like files in use  means that database you are using and you can remove remaining .


SHOW VARIABLES LIKE 'open_files_limit';

That will reflect the OS's ulimit -n.  In some OSs, that limit is set too low.  Change it.

Then set table_open_cache to be about one-third of it.


Query does not use index (according to EXPLAIN SELECT)

Antonis answer was correct. I tested it by hardcoding some JOINs (for 2 rows) and it disappeared from the slow-query log.


Actually - reindexing WILL lead you to to performance issues.
It very depends what you want to reindex and which server edition you have.

If you have an Enterprise+ edition - you can reindex all the non-clustered indexes as ONLINE operation

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = @sql + N'ALTER INDEX ['+i.name+N'] ON ['+s.name+N'].['+t.name+N'] REBUILD WITH (ONLINE = ON);'+NCHAR(13)+NCHAR(10)
FROM sys.indexes i
JOIN sys.tables t
    ON i.object_id = t.object_id
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
WHERE i.type = 2
PRINT @sql

same you can do with the clustered indexes when them does not contain BLOB columns - just replace i.type = 2 to i.type = 1

for reindexing specially clustered index of Very Big Tables with BLOB fields -
you may choose three options -

or rebuild clustered index offline
or by moving all the table's data into the new table (painful)
using 'large values out of row' option ON, make sure that all the LOB IS off-row (update table set LOB=LOB --do not use this - it is just an examlpe), rebuild the clust. index offline relatively fast and after it - reorganize it with lob compaction - which is an online operation by default


'ORA-00059: maximum number of files exceeded' on Oracle 12c

The answer was posted as an update to the question:

It looks like the following resolves the issue: Bug 18522516 12.1.0.1.4 (Jul 2014) Database Patch Set Update (PSU)
One note though, I had to bounce the database before I could create a new pluggable database. As, I preformed all the steps that I saw in the doc:
Apply the patch using opatch apply Startup/Open all pdbs Run datapatch -verbose
Then I had to bounce the database to resolve my issue.


How to archive a transactional table on another server?

Caution:

Since you will be using SQL Server 2012 express edition (and sql 2008R2), it has a limit of 10GB per database size (for previous versions, it is still 4GB). So if your table is not going to grow that big with all the archive data for many years, you are good uptill it hits 10GB limit.

My first suggestion would be to use Transactional replication. It wont have that much of overhead as you are just replicating 1 table. If you set the agent to run continuously or every 5 mins, then you will have up-to-date data on the destination (subscriber). You just have to remember to not replicate delete statements.

Secondly, you can use SSIS and schedule it using SQL Agent Job to incrementally load the data into the destination server.


How do I diagnose missing rows on a MySQL slave?

The traditional way would be to use pt-table-checksum and pt-table-sync

I like doing things a little different. I immediately run pt-table-sync with the --sync-to-master --print options.

Here is the --sync-to-master option

  --sync-to-master

  Treat the DSN as a slave and sync it to its master.

  Treat the server you specified as a slave. Inspect SHOW SLAVE STATUS,
  connect to the server’s master, and treat the master as the source and
  the slave as the destination. Causes changes to be made on the master.
  Sets --wait to 60 by default, sets --lock to 1 by default, and
  disables --[no]transaction by default. See also --replicate, which
  changes this option’s behavior.

Here is the --print option

  --print

  Print queries that will resolve differences.

  If you don’t trust pt-table-sync, or just want to see what it will do, this is a good way to be safe. These queries are valid SQL and you can run them yourself if you want to sync the tables manually.

This, if you do something like this:

echo "SET SQL_LOG_BIN=0;" > /root/SQLChanges.sql
pt-table-sync --print --sync-to-master h=10.1.2.30,u=username,p=password >> /root/SQLChanges.sql

The file /root/SQLChanges.sql will contain every change you need to execute on the Slave. Once you are satisfied with its contents, just execute the script on the Slave.

With regard to using LOAD DATA INFILE in Replication, @DTest answered this question about that. I further explained how mysql replicates LOAD DATA INFILE.


MariaDB: How can I grant read-only privileges for Stored Procedures?

Per the knowledge base, SELECT privileges on mysql.proc is sufficient.


Parameter Sniffing and Multiple Plans in Cache

There are a few different questions in here.
Q: If two identical queries use two different parameter names, what does SQL Server do?
SQL Server will build a separate execution plan for each of the queries. SQL Server considers even the slightest change of the text - even as simple as a space - to be a different query.
Each variation will get compiled and cached completely independently of the other.
Q: Will these two queries get different plans or the same plan?
SQL Server will examine the contents of the parameter and build an execution plan based on that. @Firm611's plan will be compiled based on the selectivity of @Firm611, and @Firm625's plan will be compiled based on the selectivity of @Firm625.
If the two parameters happen to have the same selectivity, both compiled plans may end up with the exact same shape, same index usage, same memory grants, etc. However, they will still be compiled independently and cached separately.
Q: What does the warning mean in sp_BlitzCache?
The warning indicates that you have two "different" queries that aren't really different - they're getting plans that are shaped the same. That means you're wasting time building multiple versions of them because:

You're wasting CPU resources repeatedly compiling the same plan
You're wasting memory caching different plans (that are really the same plan)

Q: Would OPTION (RECOMPILE) fix this?
No, because then all of them would get recompiled every time they run, leading to higher CPU usage. I only recommend recompile hints when a query runs once per minute or less - if it runs more often than that, I start worrying about the CPU overhead due to the frequent compilations of all the queries where you're using that trick.
I use a baseline of once per minute because that's only this query - you have to keep in mind the total workload on the server. If you slap recompile hints on every query, you can end up with 100% CPU utilization - even if each individual query is only run 1x per second, that's a lot of compiles when you consider every query in total.


Are the results of the STRING_SPLIT() function returned in a deterministic order?

No, they are not returned in a deterministic order.
While you are unlikely to see them returned in a different order, that doesn't make the current behavior deterministic or reliable. Tricks like applying ROW_NUMBER() OVER (ORDER BY (SELECT 1)) to the output in an intermediate place like a CTE are similarly not guaranteed to work.
It's one of the features notably missing from STRING_SPLIT(), which I've blogged about:

A way to improve STRING_SPLIT in SQL Server - and you can help
Please help with STRING_SPLIT improvements

And so has Andy Mallon:

When STRING_SPLIT() falls short

Vote and comment here:

Add row position column to STRING_SPLIT

(This item specifically asks for an additional column to be returned to indicate position within the original string but, due to backward compatibility issues, would likely need to be delivered via a new function, similar to how CONCAT_WS came to be.)

As an aside, the documentation originally stated:

The sort order of the output rows matches the order of the substrings in the input string.

That was deliberately changed in this commit to remove any notion that the return order is promised. Now the documentation states:

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string.

Why would they need to remove that promise?
I am not familiar with the internal implementation, but I believe the original version of the documentation was written by observers, not the function authors. That statement was likely written that way because that's what behavior they observed when they used the function. Many of us do the same thing when we tell people we don't need ORDER BY when selecting rows from a clustered table: "they will always come out in this order." Pretty reliable, until the optimizer chooses a different index.
My guess is they are covering their butts for future behavior. Think about all the changes they've been making to how functions work, and also ongoing changes to the optimizer.
Currently, with the simple case:
SELECT * FROM STRING_SPLIT('cow,dog,dinosaur','');

There isn't much opportunity or reason for that to be anything but sequential. But what about when you OUTER APPLY against an nvarchar(max) column between two huge, partitioned tables, and the data starts getting processed in batch mode and/or in parallel? Would they have to write additional logic in the code to ensure all that output came back in the right order? Would it be worth it? Would they have to revisit it for every future function processing / optimizer change to maintain that promise?


You need to know who the user is and that information needs to be accessible through the view.

CREATE VIEW dbo.tPatientForUser
AS
SELECT patient_id, name, address, gender, loginame
FROM tPatient
WHERE loginame = SUSER_SNAME();

Or, depending on your application you might need:

WHERE loginame = ORIGINAL_LOGIN();

This can help you filter the rows to only those with the same user.

EDIT: Per your latest comment:

To create a login for someone, and to make them a user of your database, you can read the KB article at: https://msdn.microsoft.com/en-us/library/aa337562.aspx   (Look at the examples toward the bottom of the page.)

Examples:
-- Create a login for SQL Server by specifying a server name and a Windows domain account name.

CREATE LOGIN [<domainName>\<loginName>] FROM WINDOWS;
GO

-- Creates the user "shcooper" for SQL Server using the security credential "RestrictedFaculty"
-- The user login starts with the password "Baz1nga," but that password must be changed after the first login.

CREATE LOGIN shcooper
   WITH PASSWORD = 'Baz1nga' MUST_CHANGE,
   CREDENTIAL = RestrictedFaculty;
GO

Remember though that the View or the Stored Procedure must implement your security plans, which you have not really explained.  This tip is just showing you how to make use of a users identity, not how to create your system.


The only way to be 100% certain is to restore the backups. If you are restoring to the same SQL Server instance then you will want to restore with a new database name and make use of the MOVE restore syntax to restore the .mdf and .ldf files to a different directory.

Alternatively, you can restore to a separate SQL server instance with the same database name. Again you will need to consider if the MOVE syntax is relevant.

See Retore SQL Server database to different filenames and locations for help.

For added piece of mind, you should create all your backups with checksum and after validate the metadata consistency with a RESTORE VERIFYONLY check. The restore verify can be automated by using using Ola Hallengrens excellent backup solution SQL Server Maintenance Solution


Altering the location of Oracle-Suggested Backup

I cannot say for sure why RMAN behaves in this way, but as a workaround you can edit the resulting script within the same workflow in EM before the job is created:

 ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/disk1/%d_backups/%U';

This will ensure the backups are placed in the destination you specified regardless of RMAN settings in control file saved with CONFIGURE command.