MySQL on Unix, should administrator accounts join the operating system "mysql" group?

When exporting information from MySQL for jobs using mysqlbinlog, using sudo is convenient for me too because it is usually to fix or troubleshoot something broken and time is a concern.

If you intend to re-import data extracted from MySQL then you need to work around the problem of permissions issues at some point. When creating dump files using the root account, the files created assume the permissions of the root account which MySQL can have difficulty accessing those files. (I also avoid sticky permissions as a workaround as it just gets messy when you don't deal with it on a regular basis)

What I might suggest:

add your OS account to the mysql group as it makes it convenient to view the files in the MySQL data folder if your duties include administrating/troubleshooting MySQL on that server.
limit the OS accounts that have access to the MySQL data/log files from the OS to reduce the level of exposure to your data.
developers do not need access to the OS (assuming DB servers are kept separate on the intranet and do not house the web server)
do not create files/scripts that requires your account to persist if you change duties. (i.e. If I am managing one of your DB servers, I should not find automated scripts under your account.)

For regular administrative tasks, instead of using my local account, I tend to create separate OS 'service' accounts to interface with MySQL that are in the mysql group.

If you look at the running processes (ps -ef | grep mysql) you should see that MySQL is initialized by the root account (mysqld_safe) which (as you mentioned) runs the MySQL daemon (mysqld) under the mysql account.

Using this same approach, I tend to create scripts I can run with root privileges which calls and runs another file in a linux shell as a specific user with less permissions. This also makes it easier to add the same script to the root crontab.

I start by creating a service account called 'mysqlbackup' and in the home directory add a file .my.cnf

/------ file content

[client]

user = osbackup

password = 12345

------/

Create the administrative script to be run by 'root' which executes another file from a shell

file: /var/lib/scripts/run-db-backup.sh (chmod 700 & chown root:root)
file contains: su -l mysqlbackup -c /var/lib/scripts/db-backup.sh

Create your 'real' script to be run as the OS user 'mysqlbackup'

file: /var/lib/scripts/db-backup.sql (chmod 700 & chown mysqlbackup:mysql)


Monetary value in integer over decimal datatype

The reasoning that it does not store actual value and will cause some rounding difference/errors

This is only an issue if you are storing decimals as floating point decimals. A float should always be considered to be an estimate which is subject to rounding errors and variability.

In most RDBMSs, using a decimal () data type is not a floating point decimal, and is not subject to the same variability.

When performing math & other computation, you will need to be conscious of rounding or truncation to ensure stored values meet accepted practices-- but you'll need to do that regardless of whether storing as an int or a decimal.

For example, take the following example on SQL Server (You didn't specify an RDBMS, but I'm most familiar with SQL Server). This is a simple example that mimics a calculation like "increase by 6.5%":

DECLARE @intmoney int;
DECLARE @decmoney decimal(19, 4);

SET @intmoney = 1234500  * 1.065;
SET @decmoney = 123.4500 * 1.065;

SELECT IntegerMoney = @intmoney,
       DecimalMoney = @decmoney;

The integer money value will be rounded to 1314742.
The decimal money value will be rounded to 131.4743.

Doing the math "by hand" will give a value of 131.47425.
* The integer value has been truncated.
* The decimal value has been rounded .

This is expected behavior based on the different data types. Depending on your application's rounding rules, one of these behaviors may be more or less desired. If you are storing important financial information, you may want to explicitly specify rounding using the ROUND, CEILING, and FLOOR functions (or equivalent, depending on platform & language). This can ensure you always have the expected behavior and do not suffer from unexpected rounding/truncating issues.


how to estimate rebuild index on 140 million records?

Partition on day or week.  That will give you the best possible way to delete old data (via DROP PARTITION).  There is not (unless you have 5.6) a way to move a partition from one table to another; that would be even nicer for "archiving".

The size of a table does not necessarily impact its performance.  It depends on the queries.  Queries that look just at "recent" data (at one end of the table) will probably perform nicely.  Queries that need to scan the entire table, or need to randomly fetch rows (either from the data or from an index) will get slower and slower as the table grows.  This is because of the table being so much bigger than can be cached in RAM.  "Count the disk hits."

Partition discussion:  http://mysql.rjweb.org/doc.php/partitionmaint

Deleting lots of rows without bogging down:  http://mysql.rjweb.org/doc.php/deletebig

Make sure your cache is the right size:  http://mysql.rjweb.org/doc.php/memory

ALTERing a huge table:  http://mysql.rjweb.org/doc.php/alterhuge

(Hehe... I guess your question hits more of my canned answers than any other question.)

InnoDB has a hard limit at 64TB (data+index) for table size.


How do I get % progress for "type db.sql | mysql"

Have I got a crazy query for you !!!

This will report on the size of every InnoDB table.

SELECT CONCAT(db,'.',tb) table_name,CONCAT(ROUND(
tbsz/POWER(1024,IF(pw<0,0,IF(pw>4,4,pw))),3),' ',
SUBSTR(' KMGT',IF(pw<0,0,IF(pw>4,4,pw))+1,1)) table_size
FROM (SELECT data_length+index_length tbsz,table_schema db,table_name tb
FROM information_schema.tables WHERE engine='InnoDB') A,
(SELECT 3 pw) B;

(SELECT 0 pw) give Bytes
(SELECT 1 pw) give KB
(SELECT 2 pw) give MB
(SELECT 3 pw) give GB
(SELECT 4 pw) give TB

Just run this periodically during the loading of the SQL script. If the SQL script table locks the table during the load, then you will not see numbers until the table is fully loaded.

Give it a Try !!!


How identify tables with millions of entries

Since the tables you want to check are MyISAM, you can use just one query. For any given table or tables using MyISAM, here is that query:

SET @SecondsAgo = 60;
SELECT
    table_schema,table_name,
    data_length MYDSize,index_length MYISize,
    table_rows,update_time
FROM information_schema.tables
WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','performance_schema','mysql')
    AND update_time > (NOW() - INTERVAL @SecondsAgo SECOND)
ORDER BY update_time DESC;

For each MyISAM table, you will get the following:

table name and database
.MYD size
.MYI size
number of rows in the table
last time the table was updated

You can set @SecondsAgo to choose how granular to scan for changes.

If many tables or the same set of tables appear from this query, you will see which tables are experiencing the most frequent writes and the most growth.

I will leave it to your to determine the weirdness from the results.


Window functions and a single subquery:

SELECT user_id, date, is_cleared
     , count(*) OVER (PARTITION BY user_id, grp ORDER BY date) AS custom_rank
FROM  (
   SELECT user_id, date, is_cleared
        , count(is_cleared = 1 OR NULL) OVER (PARTITION BY user_id ORDER BY date DESC) AS grp
   FROM   tbl
   ) t
ORDER  BY user_id, date;

The special difficulty here: each partition ends with 1. In most similar cases, each partition would start with 1. The simple solution is to count occurrences of 1 in descending order. This way each partition includes the next 1 if it exists. Voilá.

Assuming all involved columns to be NOT NULL and (user_id, date) unique.

Basics:

Select longest continuous sequence

Asides:

The first row of B gets custom_rank = 1. Not 2.
The column is_cleared should really be boolean.
A timestamp column shouldn't be called "date".


Shall I use enum when are too many "categories" with PostgreSQL?

With an average of 2.4 characters (more relevant: avg bytes - but that's the same for all ASCII characters) I would not bother to use enums. Those occupy 4 bytes on disk plus, possibly, alignment padding. (text does not require alignment padding.) You are not even saving storage and get more overhead for it.

With most values below 7 characters (= 8 bytes on disk), an index on a text category column will also be only slightly bigger than one on an enum. (Space for data is (typically) allocated in multiples of 8 bytes.)

For a fixed number of 208 categories, a "char" encoding (not to be confused with char!) might be an option to save storage. See:

How to store one-byte integer in PostgreSQL?

But, again, not worth the trouble for such small strings. Just use text. Maybe enforce correctness with a FK constraint to a category table like:

CREATE TABLE category (category text PRIMARY KEY);

Also a good place to store additional information per category. And you can easily modify the set of categories. Make the FK constraint ON UPDATE CASCADE and you can change category names in one central place. Make it ON DELETE SET NULL , and you can easily remove a category. Etc.

Related:

What is the overhead for varchar(n)?
Calculating and saving space in PostgreSQL


MySQL Like for Like - 100% accurate Backup/ Restore

It's not safe to copy data files unless mysqld is stopped.
I would suggest Percona XtraBackup for a physical backup of the database. This should be close to preserving the exact pages, including any fragmentation.
Another alternative is filesystem snapshots. You could revert to the snapshot after each of your tests.


How to select TOP 1 value from each column where value is not null

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

Here's a SQLFiddle (against SQL Server - the basic command should work in just about any SQL language) showing it in action. You can see that it picks values from different rows if needed. Even if only one out of five rows has a non-NULL value, both MAX and MIN will return that one non-NULL value. You only get NULL if there are no non-NULL values at all for a given UnitID.


Looks like there's an error in your XML file (the character U+0003 is "ETX" - end of text, which is nonprinting and invalid in XML).  Run the XML file through a validator.

Also (and you probably know this), your atbash "scramble" is not particular secure.


How do I store World/event data in a treelike structure SQL/SQLite3

I have seen a reporting database that had join tables that assigned a city to an administrative district (county), the administrative districts are assigned to another, bigger district (state), which is assigned to a country. The nesting action is done through the queries, instead of trying to have Russian Nesting Dolls of tables inside of tables.
Example:
CityID | CityName | DistrictID
1        New York   5
DistrictID | DistrictName  |  Province\State
5            New York County  7
ProvinceID   |ProvinceName|  CountryID
7             New York        127
CountryID | CountryName
127            USA


To answer in reverse order

What version am I on?

There are two different queries that I use to identify my SQL Server level.

The first is @@version. The nice thing about this is that it brings back OS level information as well. The challenge with it is that it's a big dump of text and you have to parse that to find the relevant bits.

e.g.

SELECT @@version;

/*
Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
    Jun 11 2015 19:18:41
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
*/

My preference is to use ServerProperties as I can individually identify the elements.

SELECT
    SERVERPROPERTY('productversion') AS ProductVersion
,   SERVERPROPERTY ('productlevel') AS ProductLevel
,   SERVERPROPERTY ('edition') AS Edition;

/*
ProductVersion  ProductLevel    Edition
12.0.4416.0     SP1             Developer Edition (64-bit)
*/

Is there native functionality to check for patches?

Nothing that I'm aware of. If the host OS has Windows Update service running and someone has specified "Include patches for other MS products" then you might get patches installed for SQL Server.

But, if you're a professional, you're probably not interested in waiting for a patch to be listed in WSUS or you'd like to validate that the patch works for your environment. That's going to involve reading knowledge base (KB) articles and testing.

How can I tell if my SQL Server instances are patched?

Lacking anything built into the product, I have been referencing sqlserverbuilds.blogspot.com. But there are plenty of other sites out there attempting to offer the same consolidated list of patches, such as:

http://sqlserverupdates.com/
http://blogs.sqlsentry.com/category/sql-server-builds/

I have taken the data from that blog and converted it to a View, dbo.PatchLevel. That view exposes all the patches. This view is truncated to only cover 2016 and 2014, otherwise I blow the character limit for answers.

CREATE VIEW dbo.PatchLevel
AS
-- data from http://sqlserverbuilds.blogspot.com
WITH SRC(Build,[File version],[KB / Description],[Release Date], SimpleVersion) AS
(
    SELECT
        CASE LEN(D.Build) - LEN(REPLACE(D.Build, '.', ''))
            WHEN 3 THEN REPLACE(D.Build, '.00.', '.0.')
            WHEN 2 THEN REPLACE(D.Build, '.00.', '.0.') + '.0'
        END AS Build
    ,   D.FileVersion
    ,   D.KB
    ,   CAST(REPLACE(D.ReleaseDate, ' *new', '') AS date) AS ReleaseDate
    ,   CAST(LEFT(D.Build, 4) AS decimal(4,2))
    FROM
    (
        VALUES
            ('13.00.500.53','2016.130.500.53','Microsoft SQL Server 2016 Community Technology Preview 2.3 (CTP2.3)','August 28, 2015 *new')
        ,   ('13.00.407.1','2016.130.407.1','Microsoft SQL Server 2016 Community Technology Preview 2.2 (CTP2.2)','July 23, 2015')
        ,   ('13.00.400.91','2016.130.400.91','Microsoft SQL Server 2016 Community Technology Preview 2.2 (CTP2.2) [withdrawn]','July 22, 2015')
        ,   ('13.00.300.44','2016.130.300.444','Microsoft SQL Server 2016 Community Technology Preview 2.1 (CTP2.1)','June 24, 2015')
        ,   ('13.00.200.172','2016.130.200.172','Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2)','May 27, 2015')
        ,   ('12.00.4427','2014.120.4427.24','3094221 Cumulative update package 3 (CU3) for SQL Server 2014 Service Pack 1','October 21, 2015 *new')
        ,   ('12.00.4422','2014.120.4422.0','3075950 Cumulative update package 2 (CU2) for SQL Server 2014 Service Pack 1','August 17, 2015')
        ,   ('12.00.4416','2014.120.4416.0','3067839 Cumulative update package 1 (CU1) for SQL Server 2014 Service Pack 1','June 22, 2015')
        ,   ('12.00.4213','2014.120.4213.0','MS15-058: Description of the nonsecurity update for SQL Server 2014 Service Pack 1 GDR: July 14, 2015','July 14, 2015')
        ,   ('12.00.4100','2014.120.4100.1','SQL Server 2014 Service Pack 1 (SP1)','May 14, 2015')
        ,   ('12.00.4050','2014.120.4050.0','SQL Server 2014 Service Pack 1 (SP1) [withdrawn]','April 15, 2015')
        ,   ('12.00.2556','2014.120.2556.4','3094220 Cumulative update package 10 (CU10) for SQL Server 2014','October 20, 2015 *new')
        ,   ('12.00.2553','2014.120.2553.0','3075949 Cumulative update package 9 (CU9) for SQL Server 2014','August 17, 2015')
        ,   ('12.00.2548','2014.120.2548.0','MS15-058: Description of the security update for SQL Server 2014 QFE: July 14, 2015','July 14, 2015')
        ,   ('12.00.2546','2014.120.2546.0','3067836 Cumulative update package 8 (CU8) for SQL Server 2014','June 22, 2015')
        ,   ('12.00.2506','2014.120.2506.0','3063054 Update enables Premium Storage support for Data files on Azure Storage and resolves backup failures','May 19, 2015')
        ,   ('12.00.2505','2014.120.2505.0','3052167 FIX: Error 1205 when you execute parallel query that contains outer join operators in SQL Server 2014','May 19, 2015')
        ,   ('12.00.2504','2014.120.2504.0','2999809 FIX: Poor performance when a query contains table joins in SQL Server 2014','May 5, 2015')
        ,   ('12.00.2504','2014.120.2504.0','3058512 FIX: Unpivot Transformation task changes null to zero or empty strings in SSIS 2014','May 5, 2015')
        ,   ('12.00.2495','2014.120.2495.0','3046038 Cumulative update package 7 (CU7) for SQL Server 2014','April 23, 2015')
        ,   ('12.00.2488','2014.120.2488.0','3048751 FIX: Deadlock cannot be resolved automatically when you run a SELECT query that can result in a parallel batch-mode scan','April 1, 2015')
        ,   ('12.00.2485','2014.120.2485.0','3043788 An on-demand hotfix update package is available for SQL Server 2014','March 16, 2015')
        ,   ('12.00.2480','2014.120.2480.0','3031047 Cumulative update package 6 (CU6) for SQL Server 2014','February 16, 2015')
        ,   ('12.00.2474','2014.120.2474.0','3034679 FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING','May 15, 2015')
        ,   ('12.00.2472','2014.120.2472.0','3032087 FIX: Cannot show requested dialog after you connect to the latest SQL Database Update V12 (preview) with SQL Server 2014','January 28, 2015')
        ,   ('12.00.2464','2014.120.2464.0','3024815 Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014','January 5, 2015')
        ,   ('12.00.2456','2014.120.2456.0','3011055 Cumulative update package 5 (CU5) for SQL Server 2014','December 18, 2014')
        ,   ('12.00.2436','2014.120.2436.0','3014867 FIX: "Remote hardening failure" exception cannot be caught and a potential data loss when you use SQL Server 2014','November 27, 2014')
        ,   ('12.00.2430','2014.120.2430.0','2999197 Cumulative update package 4 (CU4) for SQL Server 2014','October 21, 2014')
        ,   ('12.00.2423','2014.120.2423.0','3007050 FIX: RTDATA_LIST waits when you run natively stored procedures that encounter expected failures in SQL Server 2014','October 22, 2014')
        ,   ('12.00.2405','2014.120.2405.0','2999809 FIX: Poor performance when a query contains table joins in SQL Server 2014','September 25, 2014')
        ,   ('12.00.2402','2014.120.2402.0','2984923 Cumulative update package 3 (CU3) for SQL Server 2014','August 18, 2014')
        ,   ('12.00.2381','2014.120.2381.0','2977316 MS14-044: Description of the security update for SQL Server 2014 (QFE)','August 12, 2014')
        ,   ('12.00.2370','2014.120.2370.0','2967546 Cumulative update package 2 (CU2) for SQL Server 2014','June 27, 2014')
        ,   ('12.00.2342','2014.120.2342.0','2931693 Cumulative update package 1 (CU1) for SQL Server 2014','April 21, 2014')
        ,   ('12.00.2269','2014.120.2269.0','3045324 MS15-058: Description of the security update for SQL Server 2014 GDR: July 14, 2015','July 14, 2015')
        ,   ('12.00.2254','2014.120.2254.0','2977315 MS14-044: Description of the security update for SQL Server 2014 (GDR)','August 12, 2014')
        ,   ('12.00.2000','2014.120.2000.8','SQL Server 2014 RTM','April 1, 2014')
        ,   ('12.00.1524','2014.120.1524.0','Microsoft SQL Server 2014 Community Technology Preview 2 (CTP2)','October 15, 2013')
    ) D(Build,FileVersion,KB,ReleaseDate)
)
SELECT
    SRC.Build
,   SRC.[File version]
,   SRC.[KB / Description]
,   SRC.[Release Date]
,   SRC.SimpleVersion
FROM
    SRC;
GO

I have a second view, yes nested view - bad me, dbo.MyPatchLevel which uses the version data from above to compare to my current instance and generate output to let me know just how bad it is.

CREATE VIEW dbo.MyPatchLevel
AS
WITH MostRecentBuild AS
(
    SELECT
        SRC.Build
    ,   SRC.[Release Date]
    ,   SRC.SimpleVersion
    ,   SRC.[KB / Description]
    FROM
        dbo.PatchLevel AS SRC
    WHERE
        SRC.[Release Date] =
    (
        SELECT
            MAX(SRCI.[Release Date])
        FROM
            dbo.PatchLevel AS SRCI
        WHERE
            SRCI.SimpleVersion = SRC.SimpleVersion
    )
    AND
        SRC.Build =
    (
        SELECT
            MAX(SRCI.Build)
        FROM
            dbo.PatchLevel AS SRCI
        WHERE
            SRCI.SimpleVersion = SRC.SimpleVersion
            AND SRCI.[Release Date] = SRC.[Release Date]
    )

)
, MyVersion AS
(
    SELECT
        SRC.Build
    ,   SRC.[File version]
    ,   MRB.[KB / Description]
    ,   SRC.[Release Date]
    ,   D.ProductVersion
    ,   D.ProductLevel
    ,   D.Edition
    ,   D.Version
    ,   SRC.SimpleVersion
    ,   MRB.Build AS MostRecentBuild
    ,   MRB.[Release Date] AS MostRecentReleaseDate
    FROM
        dbo.PatchLevel AS SRC
        INNER JOIN
        (
            SELECT
                SERVERPROPERTY('productversion') AS ProductVersion
            ,   SERVERPROPERTY ('productlevel') AS ProductLevel
            ,   SERVERPROPERTY ('edition') AS Edition
            ,   @@VERSION AS Version
        )D
        ON D.ProductVersion  = SRC.Build
        INNER JOIN
            MostRecentBuild AS MRB
            ON MRB.SimpleVersion = SRC.SimpleVersion
)
SELECT
    MV.ProductVersion AS CurrentVersion
,   MV.MostRecentBuild
,   MV.[Release Date]
,   MV.MostRecentReleaseDate
,   DATEDIFF(DAY, MV.[Release Date], MV.MostRecentReleaseDate) AS DaysSincePatchAvailable
,   MV.[File version]
,   MV.ProductLevel
,   MV.[KB / Description] AS [Most Recent KB / Description]
,   MV.Edition
,   MV.Version
,   MV.SimpleVersion
,   CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS sysname) AS Server
,   CAST(SERVERPROPERTY('InstanceName') AS sysname) AS Instance
,   CAST(SERVERPROPERTY('ServerName') AS sysname)  AS ServerName
FROM
    MyVersion AS MV;
GO

I'd use a query like

SELECT
    MPL.CurrentVersion
,   MPL.MostRecentBuild
,   MPL.[Release Date]
,   MPL.MostRecentReleaseDate
,   MPL.DaysSincePatchAvailable
,   MPL.[Most Recent KB / Description]
FROM
    dbo.MyPatchLevel AS MPL;

Results

CurrentVersion  MostRecentBuild  Release Date  MostRecentReleaseDate  DaysSincePatchAvailable  Most Recent KB / Description
12.0.4416.0     12.0.4422.0      2015-06-22    2015-08-17             56                       3075950 Cumulative update package 2 (CU2) for SQL Server 2014 Service Pack 1

Armed with these query results, I know whether I'm patched up to date, how long it's been since I've patched and what the KB is to go read up on the current patch state.

Scripts are also hosted on github


Using your CASE expression example, here's how it could be written:

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    )
insert into @Paymenthistory values ('James','2016-01-01',100.00)
insert into @Paymenthistory values ('James','2016-02-01',100.00)
insert into @Paymenthistory values ('James','2016-03-01',100.00)
insert into @Paymenthistory values ('James','2016-04-01',100.00)
insert into @Paymenthistory values ('James','2016-05-01',100.00)
insert into @Paymenthistory values ('James','2016-06-01',100.00)
insert into @Paymenthistory values ('James','2016-07-01',100.00)

insert into @Paymenthistory values ('Roger','2016-01-01',100.00)
insert into @Paymenthistory values ('Roger','2016-02-01',100.00)
insert into @Paymenthistory values ('Roger','2016-03-01',100.00)
insert into @Paymenthistory values ('Roger','2016-04-01',100.00)
insert into @Paymenthistory values ('Roger','2016-05-01',100.00)
insert into @Paymenthistory values ('Roger','2016-06-01',100.00)
insert into @Paymenthistory values ('Roger','2016-07-01',100.00)
SELECT Employeename
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 1
                    THEN TotalCheckamt
                END), 0) AS Q12017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 2
                    THEN TotalCheckamt
                END), 0) AS Q22017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 3
                    THEN TotalCheckamt
                END), 0) AS Q32017
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2016'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42016
    ,ISNULL(SUM(CASE
                WHEN Datepart(Year, [DatePaid]) = '2017'
                    AND DATEPART(Quarter, [DatePaid]) = 4
                    THEN TotalCheckamt
                END), 0) AS Q42017
FROM @PaymentHistory
GROUP BY Employeename
ORDER BY Employeename ASC

After you accepted my original answer, I noticed where other posters were advocating the use of PIVOT to achieve the same result as multiple CASE expressions.  For completeness of my answer, I decided to try my hand at using PIVOT to provide additional options for you (and learn something myself in the process).  I've found the information from Questions About Pivoting Data in SQL Server You Were Too Shy to Ask to be quite useful when trying to understand how to use PIVOT.  You'll learn a lot about PIVOT by working through the examples (static and dynamic PIVOT) in the link - I know I did.

To summarize some of the key points of PIVOT (from the link):

  You use the PIVOT operator within your query’s FROM clause to rotate
  and aggregate the values in a dataset. The data is pivoted based on
  one of the columns in the dataset. Each unique value in that column
  becomes its own column, which contains aggregated pivoted data.

  To better understand how this all works, let’s start with the basic
  syntax for a query that uses the PIVOT operator:

SELECT column_list
FROM table_expression
  PIVOT
  (
    aggregate_function(aggregate_column)
    FOR pivot_column
    IN( pivot_column_values )
  ) [AS] pivot_table_alias
[ORDER BY column_list];

  For the SELECT clause, you can specify an asterisk (*) or the
  individual columns, and for the FROM clause, you can specify a table
  or table expression. If you use a table expression, then you must also
  define a table alias. You can also include an ORDER BY clause, but
  that’s optional. You’ll get to see these clauses in action as we
  progress through the questions. For now, let’s focus on the PIVOT
  clause. You need to understand how this clause works to make sure your
  pivots work the way you want.

  After you specify the PIVOT keyword, you pass in what are essentially
  three arguments, enclosed in parentheses. The first is the aggregate
  function and the name of the column to be aggregated. You can use any
  aggregate function except the COUNT function, when used with an
  asterisk, as in COUNT(*).

  Next, you define the FOR subclause, which specifies the column on
  which the pivot will be based. It is this column whose distinct values
  are turned into their own columns. The FOR subclause also includes the
  IN operator, which is where you specify the pivot column values that
  will be turned into columns. The values you specify here must exist in
  the pivot column or they will be ignored.

I'm by no means a PIVOT expert and other posters may have better solutions (hey, I'm willing to learn a better way), but here's my take on it.

I'm including two examples of PIVOT - one static and one dynamic.
(Hopefully, by using my examples and the information in the link provided, you'll understand what I'm doing)

The first example is a static PIVOT and is similar to the CASE expression solution I originally provided (except it uses PIVOT).

Notice that my sample data now includes years 'outside' of the CASE expression.  That information won't show up in the static version, but you'll see how we can solve that in the dynamic version later.

Static PIVOT

DECLARE @PaymentHistory TABLE (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into @Paymenthistory values ('James','2016-01-01',100.00);
insert into @Paymenthistory values ('James','2016-02-01',100.00);
insert into @Paymenthistory values ('James','2016-03-01',100.00);
insert into @Paymenthistory values ('James','2016-04-01',100.00);
insert into @Paymenthistory values ('James','2016-05-01',100.00);
insert into @Paymenthistory values ('James','2016-06-01',100.00);
insert into @Paymenthistory values ('James','2016-07-01',100.00);
insert into @Paymenthistory values ('James','2017-01-01',100.00);
insert into @Paymenthistory values ('James','2018-10-01',900.00);

insert into @Paymenthistory values ('Roger','2016-01-01',100.00);
insert into @Paymenthistory values ('Roger','2016-02-01',100.00);
insert into @Paymenthistory values ('Roger','2016-03-01',100.00);
insert into @Paymenthistory values ('Roger','2016-04-01',100.00);
insert into @Paymenthistory values ('Roger','2016-05-01',100.00);
insert into @Paymenthistory values ('Roger','2016-06-01',100.00);
insert into @Paymenthistory values ('Roger','2016-07-01',100.00);
insert into @Paymenthistory values ('Roger','2020-10-01',900.00);
;
WITH cte_Paymenthistory
AS (
    SELECT CASE
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 1
                THEN 'Q12016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q22016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32016'
            WHEN Datepart(Year, [DatePaid]) = '2016'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42016'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 2
                THEN 'Q12017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 3
                THEN 'Q32017'
            WHEN Datepart(Year, [DatePaid]) = '2017'
                AND DATEPART(Quarter, [DatePaid]) = 4
                THEN 'Q42017'
            END AS ColumnLabel
        ,EmployeeName
        ,TotalCheckAmt
    FROM @PaymentHistory
    )
SELECT EmployeeName
    ,coalesce([Q12016], 0) AS [Q12016]
    ,coalesce([Q12017], 0) AS [Q12076]
    ,coalesce([Q22016], 0) AS [Q22016]
    ,coalesce([Q22017], 0) AS [Q12017]
    ,coalesce([Q32016], 0) AS [Q32016]
    ,coalesce([Q32017], 0) AS [Q32017]
    ,coalesce([Q42016], 0) AS [Q42016]
    ,coalesce([Q42017], 0) AS [Q42017]
FROM cte_Paymenthistory
PIVOT(SUM(TotalCheckAmt) FOR ColumnLabel IN (
            [Q12016]
            ,[Q12017]
            ,[Q22016]
            ,[Q22017]
            ,[Q32016]
            ,[Q32017]
            ,[Q42016]
            ,[Q42017]
            )) AS pvt
ORDER BY employeename;

Did you notice the data outside the CASE expressions didn't show up?

Let's try to solve that by using a dynamic PIVOT.

Dynamic PIVOT

  How do I pivot data if I don’t know the values in the pivoted column?

(Due to using dynamic SQL, we have to switch to using a temporary table instead of a table variable)

set nocount on
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

IF OBJECT_ID('tempdb..#PaymentHistory') IS NOT NULL drop Table #PaymentHistory
CREATE TABLE #PaymentHistory (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into #Paymenthistory values ('James','2016-01-01',100.00);
insert into #Paymenthistory values ('James','2016-02-01',100.00);
insert into #Paymenthistory values ('James','2016-03-01',100.00);
insert into #Paymenthistory values ('James','2016-04-01',100.00);
insert into #Paymenthistory values ('James','2016-05-01',100.00);
insert into #Paymenthistory values ('James','2016-06-01',100.00);
insert into #Paymenthistory values ('James','2016-07-01',100.00);
insert into #Paymenthistory values ('James','2017-01-01',100.00);
insert into #Paymenthistory values ('James','2018-10-01',900.00);

insert into #Paymenthistory values ('Roger','2016-01-01',100.00);
insert into #Paymenthistory values ('Roger','2016-02-01',100.00);
insert into #Paymenthistory values ('Roger','2016-03-01',100.00);
insert into #Paymenthistory values ('Roger','2016-04-01',100.00);
insert into #Paymenthistory values ('Roger','2016-05-01',100.00);
insert into #Paymenthistory values ('Roger','2016-06-01',100.00);
insert into #Paymenthistory values ('Roger','2016-07-01',100.00);
insert into #Paymenthistory values ('Roger','2020-10-01',900.00);
;
;
SELECT @col =
        Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull =
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT
    'Q' +
    CONVERT(VARCHAR(1), DATEPART(Quarter, [DatePaid])) +
    CONVERT(VARCHAR(4), year([DatePaid]))
        AS PvtColumnName
    FROM #PaymentHistory
    ) AS PaymentHistory;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with cte_PaymentHistory as
(
select
''Q'' + CONVERT(varchar(1),DATEPART(Quarter, [DatePaid])) + CONVERT(varchar(4),year([DatePaid])) as PvtColumnName
,EmployeeName
,TotalCheckAmt
from #PaymentHistory
)
SELECT EmployeeName, ' + @colCoalesceNull + 'FROM cte_PaymentHistory
    PIVOT(SUM(TotalCheckAmt)
    FOR PvtColumnName IN (' + @col + ')) AS PivotPaymentHistory';

EXEC sp_executesql @sql;

Your question prompted me to research and learn more about PIVOT - thanks.


MySQL crashes daily on VPS with Ubuntu Server 12.04 - How can I find the error or conflict?

Check the value of the variable "log_error":

select @@log_error

or

show variables like 'log_error'

It defaults to /var/log/mysqld.log on a RHEL6 server though can be different.

Then check this log file - it will generally give you a reason why it's failing.

Alternatively, if the service is being killed because the server is running out of memory, check the system log:

/var/log/messages

For the "Out Of Memory killer" doing it's thing or any other messages that might be relevant around the time that the server died.


Oracle error 12154 while importing DMP file

Use this on Windows (three nested double quotes):

exp system/password_for_system@dev file=c:\fooadmin.dmp full=yes

imp """sys/password12345@dev as sysdba""" FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp

In general imp would work as intended with sys account, it is just not recommended. But exp with sys account could possibly produce inconsistent dump file silently. So it's better to avoid it, for example use system.


Answers originally left as comments

I think you identified it in your question, regression testing. You maybe able to thin down the testing to just corner cases of the functionality. However if you are looking at more than “does it run” and wanting to test “how well does this run” a full test maybe your best option. – Aaron

To me, this is less of a question about the database, and more of an issue with your regression testing. You may want to explore ways to make your regression testing less labor intensive. – AMtwo

Also there aren’t that many things that work in 2008 and break in newer versions; the exceptions are limited to lists documented on Microsoft’s site, like this one for SQL Server 2016. So outside of those items, you should generally expect that moving to a new version should just work. Performance testing is something completely different, of course, and there aren’t any magic shortcuts aside from testing. Baselines are a good start. – Aaron Bertrand


You need the btree_gist extension so that you can create a GiST index on an uuid:

CREATE EXTENSION btree_gist;

Now you can create an exclusion constraint:

ALTER TABLE reservations ADD EXCLUDE USING gist (
   reservable_id WITH =,
   reserved_between WITH &&
);

&& is the “overlaps” operator for ranges.


How can I design a relational database with words that are all related to each other

The associative entity idea is a good way to handle this, but it has a drawback that you might want to consider.

Something that hasn't been addressed in some other answers is how you're going to maintain all of this data. It's going to be a lot of work, particularly if you want to be able to get from one word to any of its synonyms (or antonyms) without knowing which of your words you're starting from.  Consider your example of {angry, enraged, furious, irritated, offended}.  That's a set of five synonyms.  If you used associative entities you'd have these pairs: angry=enraged, angry=furious,... etc. You'd also have to do them the other way around: enraged=angry, furious=angry, and so forth.

Five synonyms isn't a lot.  However, if you make an associative table for 5 synonyms you're going to have 10 entries. For 6 synonyms you'll have 15 and for 7 synonyms you'll have 21. You can see how this might get out of hand.

Alternative: Association via a root
The good news is that there's another way to do this.  If you pick one of your synonyms and make it the base or root word (say in your case "angry") then you can have one record for each of these words in one table (five synonyms = five records, include "angry" in your list of words) and then a second table containing just the roots (for example "angry")

The key (pun intended) is to have the list of words point at their base or root word.  This would be via a foreign key.

So in your word table, you might have something like this:

angry = angry
enraged = angry
furious = angry
irritated = angry
offended = angry

Now, you might want to use a meaningless numerical key instead of "angry" in the second column in each of the above.  That might make things easier for a variety of practical reasons.  In that case you'd have a second table with just the base words with their meaningless numerical keys, for example:

123, angry

So your word table looks more like this:

angry, 123
enraged, 123
furious, 123
irritated, 123
offended, 123

So how do you find synonyms for a word?

That's a simple process, but it's a two step* process:

Look up the root key for the given word.  Say you want to find synonyms for "furious". You look it up and find the root is number 123.
Look for all the words that have the same root key as your word, these are your synonyms.

The advantage of doing it this way is that it saves you a ton of data maintenance. You only have to maintain one record for each word, plus one for each group of synonyms.  That's a lot fewer than combinations of all synonyms, which is what you're looking at if you do this with an associative entity.

*Note: you don't actually have to do this in two steps. You can write a SQL which combines all of this into a single statement using a self join.


Stored procedure not displaying data when null is passed into input parameter

Well, I figured out the problem by myself. I removed _subject_name IS NULL in both the first and the second IF condition and it worked as expected.


Which characters are considered "word" characters

The characters considered for forming a word, is explained in the Documentation:

  The MySQL FULLTEXT implementation regards any sequence of true word
  characters (letters, digits, and underscores) as a word. That sequence
  may also contain apostrophes ('), but not more than one in a row. This means that
  aaa'bbb is regarded as one word, but aaa''bbb is regarded as two
  words. Apostrophes at the beginning or the end of a word are stripped
  by the FULLTEXT parser; 'aaa'bbb' would be parsed as aaa'bbb.

Regarding Delimiters:

  The built-in FULLTEXT parser determines where words start and end by
  looking for certain delimiter characters; for example,   (space), ,
  (comma), and . (period).

Besides the above, there are Storage engine specific limits as well, on the minimum word size:

  Any word that is too short is ignored. The default minimum length of
  words that are found by full-text searches is three characters for
  InnoDB search indexes, or four characters for MyISAM.

Now, regarding the list of defined "True Word Characters", MySQL has given flexibilities, to be able to add/remove additional characters, for word criteria. As stated in the Documentation:

  Suppose that you want to treat the hyphen character ('-') as a word
  character. Use one of these methods:

  Modify the MySQL source: In storage/innobase/handler/ha_innodb.cc (for InnoDB), or in storage/myisam/ftdefs.h (for MyISAM), see the
  true_word_char() and misc_word_char() macros. Add '-' to one of those
  macros and recompile MySQL.
  Modify a character set file: This requires no recompilation. The true_word_char() macro uses a “character type” table to distinguish
  letters and numbers from other characters. . You can edit the contents
  of the  array in one of the character set XML files to
  specify that '-' is a “letter.” Then use the given character set for
  your FULLTEXT indexes.

Now looking at the source code ha_innodb.cc here:

#define true_word_char(c, ch) ((c) & (_MY_U | _MY_L | _MY_NMR) || (ch) == '_')

For some simple character-sets like latin1, one can edit the <ctype><map> array in the respective .xml file: https://github.com/mysql/mysql-server/blob/5.7/sql/share/charsets/latin1.xml#L25

However, utf8 being a complex character-set, it is implemented in the ctype-utf8.c file . It contains the complete character set definitions. Now,  array elements are bit values. Each element describes the attributes of a single character in the character set. Each attribute is associated with a bitmask, as defined in include/m_ctype.h file:

#define _MY_U   01  /* Upper case */
#define _MY_L   02  /* Lower case */
#define _MY_NMR 04  /* Numeral (digit) */

So, the characters having attributes associated with above bitmasks will be considered as true word character. Check out some explanation here --> https://dev.mysql.com/doc/refman/5.7/en/character-arrays.html

Finally, there is a list of stopwords (InnoDB has limited list, but MyISAM has quite a big list), which are ignored. Eg: about, are, com etc.

  Words in the stopword list are ignored. A stopword is a word such as
  “the” or “some” that is so common that it is considered to have zero
  semantic value. There is a built-in stopword list, but it can be
  overridden by a user-defined list.


RETURN statement before DEALLOCATE CURSOR

Copying part of my answer from here...

How long the resources hang around depends on whether you declared the cursor locally or globally (and what the default is in your environment - default is global but you can change it).

If the cursor is global, then it can stay "alive" in SQL Server until the last piece of code is touched in the scope in which it was created - and can often persist even beyond that. For example if you call a stored procedure that creates a global cursor, then call 20 other stored procedures, the cursor will live on while those other 20 stored procedures are running, until the caller goes out of scope. I believe it will stay alive at the session level, not the connection level, but haven't tested this thoroughly. If the cursor is declared as local, then it should only stay in scope for the current object (but again, this is theoretical, and I haven't done extensive, low-level memory tests to confirm). I don't know of any DMVs that show this usage individually, or even if on a global level you could detect memory changes in DMVs that probably don't get updated often enough to reflect the truth at any microsecond.

The general concept, though, should be: when you're done with something, say so. I believe there are several ways to write your code such that the RETURN doesn't have to happen within the cursor, and probably ways to re-write the code to eliminate the cursor.

In order to make my cursors as efficient as possible, I always use the following declarations:

DECLARE c CURSOR
  LOCAL STATIC FORWARD_ONLY READ_ONLY
  FOR SELECT ...

I've also heard that there can be memory issues if you only CLOSE or only DEALLOCATE so - even though I haven't proven or disproven it - I always do both when I'm done:

CLOSE c;
DEALLOCATE c;


how to upgrade and migrate postgres dbs to new server machine?

The simplest approach is to simply use pg_dumpall -f cluster.dump, then restore with psql -f cluster.dump on the new host.

Slightly more sophisticated is to dump globals, then dump each DB separately:

pg_dumpall --globals-only > globals.sql
psql -qAt -c 'SELECT datname FROM pg_database WHERE NOT datistemplate;' -0 |\
  xargs -0 -i pg_dump -Fc -f "{}".pgbackup "{}"

Restore with something like:

psql -f globals.sql
for f in *.pgbackup; do
  pg_restore -f "$f" -C -d postgres
done

In all these cases you may need to run as the postgres user.

A 10GB dump is quite reasonable.

If you need to do a low downtime upgrade, there are a few more sophisticated options:

Set up slony-I or Londiste to replicate changes from the old server to the new one; or
Install the same version of PostgreSQL on the new server. Set up streaming replication. Fail over to the new server, then pg_upgrade to a current PostgreSQL version.


Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

Nope.  Here's a simple test:

SELECT COALESCE(1, (SELECT 1/0)) -- runs fine
SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error

If the second condition is evaluated, an exception is thrown for divide-by-zero.

Per the MSDN Documentation this is related to how COALESCE is viewed by the interpreter - it's just an easy way to write a CASE statement.

CASE is well known to be one of the only functions in SQL Server that (mostly) reliably short circuits.

There are some exceptions when comparing to scalar variables and aggregations as shown by Aaron Bertrand in another answer here (and this would apply both to CASE and COALESCE):

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

will generate a division by zero error.

This should be considered a bug, and as a rule COALESCE will parse from left to right.


Connecting client application to MariaDB Galera cluster

You'll have to look up on the documentation of your database connector. Normally (e.g. in Java world) you can specify comma-separated list of nodes instead of a single host.

For instance, check the documentation on Failover at:
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

Alternatively you can run a separate load-balancer server "in front of" the cluster nodes and just point the client to the load-balancer.


Parameterized Queries in PostgreSQL using PHP fail to handle null values

if ($p_int_4 = '' || is_null($p_int_4)) $p_int_4 = null;

The equality operator in PHP is ==. Your code uses =, and will always set $p_int_4 to an empty string as a result of this mistake.


ListServ Database: Statistics Table Design Question

So, in the chat room for the site, I've been told to leave my table structure alone, and to use views or specific queries to get out the data I need from just the timestamp.

Given this, I've decided to leave the table alone, and started looking into the date interaction functions with PostgreSQL (and MSSQL where another group I trust will like to use the functions) and have begun incorporating such specific checks into the predefined routines which I've designed for this system.

The only thing I left out from my question was that my code base in Python only interacts with the database via the predefined routines and functions I've written, so views are less necessary if I can get the database functions configured right to correctly process the specific date ranges on my radar.

Thanks to all who commented in chat about this to me directly, it's helped me work to restructure my initial design for the functions, and do a lot of stuff database side instead of Python-side.


SQL Server 2005 upgrade service pack via command line

I have used below in past to upgrade all instances running sql 2005 to SP4. Just put this in a .bat file

@echo off
set hour=%time:~0,2%
if "%hour:~0,1%"==" " set hour=0%time:~1,1%
echo      Started at %date% %time%
SQLServer2005SP4-KB2463332-x64-ENU.exe /allinstances /quiet

echo      Completed at %date% %time%


SQL Server 2005 Database move and rationalisation - Recommended maintenance processes

Shrinking and Storage

Enable "Instant File Initialization"

Disable autoshrink

Size your storage properly (15% free per drive)

RAID what? RAID level matters and SQL Server accesses the files differently. Log file access is generally sequential and Data is generally random.

tempdb

Separate data and log files (see RAID, below) - get a good tempdb strategy. We have tempdb logs on a log drive and our tempdb files (1/4-1/2 the number of cores) on their own drive. While you're at it, make sure that tempdb has an appropriate number of data files, that their initial sizing is exactly the same, and that they have exactly the same growth factor. While you're at it, read up on Trace Flag 1118

Schema Changes

If you're going to be making these changes, make sure you have a good, durable backup. What happens in 9 months when someone actually needed those tables?

Can your application actually withstand the changes from NVARCHAR, NCHAR, and NTEXT? While you're at it, NTEXT is deprecated so moving to TEXT would not be good. Check out VARBINARY or VARCHAR(MAX) instead.

You can change the collation all you want, but it won't affect any of the existing data.

Compatibility

Change the db compatibility levels to 100 if you can.

Tuning Option

Check out some of these trace flags none of them may be appropriate. Some of them might be, "it depends." Also, read about trace flag 4199.

Tooling

I'd also recommend coming in to this install with a good tool set. Create a DBA database for this toolkit. I'd start with sp_WhoIsActive by Adam Machanic, sp_Blitz by Brent Ozar. I'd also look for Kendra Little's work with scheduling and storing the results from sp_WhoIsActive. Finally, I'd start up a way of capturing Waits and Queues so that you have a good history of your server's profile.

Look into getting a monitoring solution.

Finally (and maybe firstly), check out Glenn Berry's excellent series on provisioning a new SQL Server Instance.

Part 1, Part 2, and Part 3


Is there a SSH server on your machine?? If not you may install copssh - OpenSSH for Windows or similar and then try to connect.


Why is MySQL database data-files deployed under /var/lib/mysql?

In the Filesystem Hierarchy Standard, /var/lib is listed as containing:

  State information. Persistent data modified by programs as they run, e.g., databases, packaging system metadata, etc.

So it is very much because it is the traditional/documented place to put such things. If a database system or similar is installed under /opt it is common for the data files to also be under /opt, though many would argue that /var/lib is still the right default location in that case (with state pertaining to the running of the app in /opt and state/data produced or consumed by the app (such as user databases) under /var/lib).

Of course on a multi-user system, keeping user databases under /home/<user> often makes more sense, though not all database engines support this (or don't support it easily, without some manual jiggery-pokery on the part of your SysAdmin/DBA).


Is it a best practice to contrast table and dependent view metadata to target which views to refresh?

It would be much, much, much, much, much simpler to just run a loop applying sp_refreshsqlmodule for all views and procedures after you apply a vendor update. Don't make this harder than it needs to be.


Query hanging in ClientRead and blocking all others

You have a problem with transaction management.

A session that waits for ClientRead is done processing the last query and waits for the client to send the next request. The only way that such a session can block anything is if its state is idle in transaction. All locks are held until the transaction ends, and no locks are held once the transaction finishes.

If sessions remain in state idle in transaction for a longer time, that is always an application bug where the application forgot to end the transaction. This is bad, not only for the reason you observe, but also because it keeps autovacuum from doing its essential job.

There are two things you can do:

Fix the application so that it properly closes transactions. This is the only good solution.
Prevent the worst from happening by setting idle_in_transaction_session_timeout so that these transactions are automatically rolled back by PostgreSQL after a while. This will keep locks from being held indefinitly, but the buggy application will receive an error.


What is the difference between a "record" and a "row" in SQL Server?

To quote Joe Celko (not only can you find this reference all over the web and in his Wikipedia entry, but you will even see it on T-shirts at some conferences):

  Rows are not records.

A lot of people point him out as a pedantic jerk who just likes to humble and verbally abuse newbies, and I will admit that is how he comes across. But I have also met him in person - even shared a meal with him - and I can't tell you how different his real-life persona is from his online front. I even once caught him calling rows records, and he was very embarrassed (full backstory here).

In any case, say what you will about the guy's online character, but he wrote the standard, and the fact that such an authority dictates that there is a distinction should tell you something. And as much as he cringes when someone calls a row a record, so do many of my colleagues - who are also experts in the SQL Server world. And those of us in that camp believe he is right.

For example, Itzik Ben-Gan, an obvious SQL Server guru. Here is a quote from the very first lesson in his Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012:

  As an example of incorrect terms in T-SQL, people often use the terms “field” and “record” to refer to what T-SQL calls “column” and “row,” respectively. Fields and records are physical. Fields are what you have in user interfaces in client applications, and records are what you have in files and cursors. Tables are logical, and they have logical rows and columns.

And, knowing Itzik, if you send him an e-mail or corner him at a conference he will happily tell you the same. If you call a row a record, in his opinion, you're not using the terminology correctly.

Now, being an industry full of folks of all kinds, you are likely to find material (such as the tech target articles posted in another answer) that seem to make very subtle distinctions between the two, and you will find many people in the industry consider them the same (I know several folks at Microsoft, and other folks like Brent Ozar, who will just always call it a record). That doesn't make them right, that's just their way of looking at it - they view logical and physical as the same (at least in this context) and many of them probably think the rest of us are just anal retentives spending too much time on semantics.

Since no vendor gets to say "thou shalt call them {records|rows}", we will forever be dealing with this argument, because there will always be someone who doesn't get the logical vs. physical, or was taught differently, or came from Access or programming backgrounds, etc. Just like some people say tomay-to and other people say tomah-to, there will always be a variety of people who range from "they're the same" to "they're completely different" - and many shades in between. Again, that doesn't make any of them right, because nobody can be the ultimate authority on this. But in the SQL Server space, there is definitely a majority.

That said, IMHO, when you are talking about data that is in a table, you call it a row. When you are performing an insert, you are inserting a row into a table. When you run an update, you are updating a row that is in a table. And when you perform a SELECT, you are retrieving rows from a table.

Feel free to call it a record once your application has a hold of it. But don't get angry if you say, "I inserted a record," and someone corrects you.


PhpMyAdmin Import Error-MySQL server has gone away/Unrecognized Keyword

Changing the MySQL settings worked. This is what I changed, exactly:

In php.ini

Find:

post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
max_input_time = 60
memory_limit = 8M

Change to:

post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 5000
max_input_time = 5000
memory_limit = 1000M

In my.ini (MySQL File)

Under mysqld add: max_allowed_packet = 500M


Manipulating duplicates by merging similar (Is it possiple)

Use GROUP_CONCAT() for this.

Your table:

create table foo
(
id int,
name varchar(10),
status int,
c_lan varchar(10),
Java_lan varchar(10),
Dot_lan varchar(10)
);

insert into foo values (  1,'A',111,'yes','Q','W'   );

insert into foo values (  1,'A',222,'yes','Q','W'   );

Testcase:

mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan from foo group by id;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

View:

create view concatview as (
select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
from foo
group by id
);

Test:

mysql> select * from concatview;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

Obviously you may want to GROUP on more than just id (depending on what you consider a "duplicate", and may want to order the GROUP_CONCAT() in some way, but this points you in the right direction.

Edit to address your comment:

mysql> insert into foo values ( 2,'b',555,'yes','Q','W' );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
    -> from foo
    -> group by id
    -> ;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
|    2 | 555     | yes   | Q        | W       |
+------+---------+-------+----------+---------+
2 rows in set (0.01 sec)

mysql>


Why does PostgreSQL create an additional NULL bitmap if all fields after the 8th are NOT NULL?

The answer to your question is also in the linked answer:

  NOT NULL constraints do not directly affect that.

The size of the NULL bitmap is not influenced by NOT NULL constraints at all.

And there is only ever one (or no) NULL bitmap per row. Never an "additional" NULL bitmap. For rows up to 8 user columns the spare byte between row header and column data (or an optional OID) is used. With more than 8 user columns, the NULL bitmap is extended by MAXALIGN (typically 8 bytes). Column data moves back by the same number of bytes. Resulting in a single NULL bitmap.

Even if it might seem reasonable not to extend the bitmap for trailing columns with NOT NULL constraints, complications might ensue. Like: removing a constraint would have to trigger a whole-table rewrite.

And even if that might be handled, I expect no core developer would be willing to complicate the code to save a few bytes per row for this rare special case.


Store the result from a cursor with exec into a temp table without explicitly named columns

I assume that the query is a constant query. In that case, use this slightly modified code, using the global temp table theme;

DECLARE
    @db VARCHAR(50),
    @query VARCHAR(MAX),
    @query2 varchar(max),
    @sql VARCHAR(MAX),
    -- make a unique global temp table name for now. Assumes that you drop
    -- the table explicitly at the end of each run.
    @tmpTbl varchar(50) = '##tmp_' + CAST(@@SPID as varchar)

SET @query = 'SELECT Col1=1, Col2=2 INTO ' + @tmpTbl;
SET @query2 = 'INSERT INTO ' + @tmpTbl + ';'

DECLARE db_cursor CURSOR FOR
SELECT db=name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql =' USE '+@db+'; ' +@query+''
    FETCH NEXT FROM db_cursor INTO @db
    EXEC (@sql)
    SET @query = @Query2; -- This will happen each time, but only
        -- adds a tiny bit of overhead to the process.
END

CLOSE db_cursor
DEALLOCATE db_cursor

SET @SQL = 'SELECT * FROM ' + @tmpSQL
EXEC (@sql)
SET @SQL = 'DROP TABLE ' + @tmpSQL
EXEC (@SQL)


An exception occurred while opening a connection to the server

I guess it is a locking problem. You should open profiler and watch which queries are locking each other.
Otherwise may be you can try it after researches.
Document.
db.adminCommand( { setParameter: 1, maxTransactionLockRequestTimeoutMillis: 200 } )


Connect a linked server to MS Reporting Service?

I don't see any problems generating reports the usual way, as long as you use the 4 part name:
'select top 10 col1, col2 from server.database.schema.table' in the code used to get the data for your reports.


"The SELECT permission was denied" only for some of my tables

The difference between the two tables and the other tables was not in the table it self, but in how the stored procedures where doing the select. In this case the two tables where the only 2 which had some dynamic SQL in them which causes the error. Replacing the dynamic sql fixes the need for Grant select on the tables.


Why does JSON_QUERY within CASE statement fail to remove escape characters?

From JSON_QUERY (Transact-SQL)

  Returns a JSON fragment of type nvarchar(max).

and

  JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON
  doesn't escape special characters in the JSON_QUERY return value.

So for json path escapes nvarchar(max) data unless the nvarchar(max) data comes from json_query().

In your query the data comes from the case statement not directly from json_query.

The workaround you can do is to put json_query outside of the case.

json_query(case when @debug = 1 then @data else null end)

Incorporating this into your full script, so you can compare the different approaches, gives:

declare @data nvarchar(max);
declare @debug int = 0;

set @data = '{"id":10}';

set @debug = 0;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData',
    json_query(case when @debug = 1 then @data else null end) as 'caseResultProcessed'
    for json path, without_array_wrapper ;

set @debug = 1;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData', -- This was the issue
    json_query(case when @debug = 1 then @data else null end) as 'caseResultProcessed' -- This is the solution
    for json path, without_array_wrapper ;


Issues with Log Shipping in SQL Server 2019

I had the exact same issue. I installed Cumulative Update 3 for SQL Server 2019
and it went away


Here's a broad answer to your broad question.

All DMLs and SELECTs always goes through the buffer_pool in InnoDB.  It is a cache of the blocks that compose the BTrees that compose the data (organized by the necessary PRIMARY KEY) and each secondary INDEX.

For DML (broadly):

Fetch a block, if not already in the buffer pool.
Modify whatever.
Mark the block "dirty".
Rinse and repeat.
Eventually (as a background task), the block will be written back to disk.  Meanwhile, it is in the cache so that other operations can avoid having to fetch it from disk.

Modifying a non-unique index involves the "Change buffer".  That is sort of a "delayed write" for such changes.

SELECTs:

Fetch a block (if not already in buffer pool).
Fetch row(s) out of that block.
Rinse and repeat.

Note: in both DML and SELECT, the discovery of where a row usually involves drilling down a BTree, which involves fetching nodes (blocks) of the BTree.  They, too, are cached (and tend to live a long time in the buffer pool).

If you want more details, start another Question with more specifics.


Deleting aged postgresql rows with foreignkey constraints

First start a transaction:

begin;

Start deleting all from links_groupseen first:

delete
from links_groupseen
where which_reply_id in (select myproperid from links_reply where "submitted_on" < now() - interval '30 days')

and then delete from links_reply:

DELETE
FROM links_reply
WHERE "submitted_on" < now()- interval '30 days';

and if you're happy with what you see, you can COMMIT. The reason I'm using a transaction is that within a transaction, now() will give you the same values at each call. Otherwise, you would have to replace now with a constant MYDATE value of your choice on both delete statements.


column identifier versus separate table with related data with different purposes

In my opinion, It is very much depends on the details.

Does cleaner robot has different attributes compare to killer robot?
Do you have different type of killer/cleaner robots?

if your answer to any of these questions is yes, Then probably you need a separate table for each type (Normalization). Using different table for different type simplifies your job when you want to extend your application. Attributes with functional dependency (2nd Normal Form) and transitive dependency (3th Normal Form) need to be placed in the same table. You may read more about pros and cons of normalization.


You will need to check out the sum total of your indexes for MyISAM. Please run this query:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

Whatever number comes out, use either that number or 8G, whichever is smaller.

MyISAM only caches index pages.

If you plan to migrate your data to InnoDB, use these settings:

[mysqld]
innodb_file_per_table
innodb_log_file_size=2047M
innodb_log_buffer_size=64M
innodb_buffer_pool_size=18G
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=10000

You wiil need to optimize the InnoDB infrastructure


Generating a numeric pattern in query result set

Based on your comments you will be ordering the results by date. In that case you could use

ROW_Number () over( order by date)

Which would output the row numbers.  You could then use the SQL Modulo function, % and perform a %3 which would give you 1,2,0 repeating as the row output which gives you the grouping you want.


Reindex or Analyze Tables on PostgreSQL after import

If you use pg_dump/pg_restore, then all indexes are created during pg_restore (after the data was loaded), so there is no reason to run reindex.
pg_restore however, does not import any statistics, so running analyze directly after running pg_restore is a very good idea.


Returning case-sensitive input of case-insensitive array match

First of all, you should mention that you are using the additional module citext that provides the case insensitive text data type citext, which is not strictly needed here. The solution below only requires standard Postgres 9.4.

Key elements are jsonb_each_text() to unnest jsonb values into key/value pairs and json_object_agg() to re-assemble the JSON value with changed parts:

SELECT j.id, json_object_agg(d.key, CASE WHEN d.key = 'email'
                                         THEN t.email ELSE d.value END)::jsonb AS d
FROM   jsonthings j
JOIN   unnest('{TEST1@TEST.COM,test3@test.com}'::text[]) t(email)
                                                ON lower(t.email) = lower(j.d->>'email')
     , jsonb_each_text(j.d) d
GROUP  BY 1;

Result:

id | d
---+---------------------------------------
1  | {"id": "1", "email": "TEST1@TEST.COM"}
3  | {"id": "3", "email": "test3@test.com"}

How?

(0.) Of yourse your table has a primary key you did not mention. Let's call it id.

Unnest the text array into individual rows and JOIN to the 'email' value of the jsonb column using lower() on both sides to make it case insensitive.
Decompose the jsonb column (only identified rows!) into key/value pairs with jsonb_each_text().
Replace original 'email' values with the search string with a CASE statement.
Finally aggregate everything back to a json object with json_object_agg() and cast to jsonb.

Aside: For a simple structured form like you present in the example, a normalized schema with scalar Postgres data types would be much more efficient:

How to perform update operations on columns of type JSONB in Postgres 9.4


Pitfalls when using Java stored procedure

A well-written Java stored procedure is unlikely to "put the server down", however, Java routines might have some performance implications, because invoking them means that Oracle needs to start a Java virtual machine (JVM) process, transfer control to it, and wait for the Java code to terminate. Since this is still happening within the scope of a SQL transaction, concurrency might suffer. This is especially true when Java is used to synchronously perform some potentially long running activities that depend on remote resources, such as sending and receiving email.

You probably want to avoid using Java stored procedure if possible; consider using the UTL_MAIL Oracle built-in package to handle email messages.


Subquery check for every occurence - Gantt style

I might derive the progress table into two pieces... one with the zeros (complete) and one with the ones (not yet done), and join them together by the case number.

Then I'd join those with the predecessor table to find rows where the task is in the ones table and it's predecessor is in the zeros table.

Once I've identified those tasks, I'd join with the main table to get the task's proper name.

That might look something like this:

SELECT
    t.name,
    p1.id_progress
FROM
    tasks_predecessor tp
INNER JOIN
    (SELECT task_id,case,id FROM progress where current = 0) p0
    ON p0.task_id = tp.predecessor
INNER JOIN
    (SELECT task_id,case,id FROM progress where current = 1) p1
    ON p1.task_id = tp.task
    AND p0.case = p1.case
INNER JOIN
    tasks t
    ON t.id = tp.task

Then, if you wanted a particular case, you could isolate it with:

WHERE p0.case = 199

Give this a try, but don't hold me to it... I'm not in front of a console where I can test this, and I may have missed something obvious or simple.


Try to enable "Instant File Initialization" feature if it is not enabled already. It speeds up the restore process and makes it significantly faster.

What is Instant File Initialization(IFI)?

SQL Server needs to initialize the pages before using them. Thus, it tries to fill the pages with zeros(zeroing the files). If IFI is enabled, it skips this part and data files can be initialized instantaneously to avoid zeroing operations.

How to enable IFI?

Unfortunately, there is no in-built checkbox or trace flag to enable this feature. Instead, SQL Server service account needs to have "Perform Volume Maintenance Tasks" permission. Check out below page for detailed steps on how to do that.

MS Documentation: Instant File Initialization

When it will be enabled?

Be aware of that you need to restart SQL Server service so that SQL service will pick this policy up and enable this cool feature.

Bonus: You can check SQL Error Log to see whether IFI is enabled or not. Use "
"Filter" to search for keywords such as "Instant", "Initialization" or You may use below query taken from this blog post.

SELECT  @@SERVERNAME AS [Server Name] ,
    RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
    LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
    + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
    service_account ,
    instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%'


You have to import MongoDB tools via .
Download, unzip, and use the export path and run the command below on the terminal and try the export command again.
export PATH=$PATH:inputmongodbtoolpath

If you already use brew, you can try with it.
brew install mongodb-community
brew services start mongodb-community


Modeling a db that has products: What are the good practices/solutions? How to treat grammar variations in the words?

How would I guarantee to the user that no matter how he types "chair"
  (singular, plural, with or without adjectives), he will get all the
  results that the database can provide?

Wildcard queries could solve your immediate problem. For example, your products table may have the following entries:

productID    companyID    productType
--------------------------------------
1            1            chair
2            2            wooden chair
3            2            desk chairs

If you wanted to get all of the products with the word chair in it, you could run the following query (mysql):

select productID, productType
from products
where productType like '%chair%'

However, that design isn't optimal, because you will encounter a problem when a user types in recliner, or some other type of chair that doesn't explicitly have the word "chair" in it.

A better design would be to have a separate table that specifies the categories of products. For example, you could have a categories table that contained a fixed set of categories:

categories table

categoryID    categoryName
---------------------------
1             chair
2             office furniture
3             wooden furniture

Then, you would have a table to map categories to products. Products can have many categories, which will give your users more flexibility when searching. Here is the productCategories table.

productCategories table

categoryID    productID
-----------------------
1             1
1             2
1             3
1             4
2             3
3             2

Finally, you would have your normal products table, which would contain additional information about your products.

Products table

productID    companyID    productName              price
---------------------------------------------------------------
1            2            Plain chair              40.00
2            2            Modern wood chair        120.00
3            2            Black office chair       160.00
4            3            Leather recliner         800.00

With this model, finding all of the chairs is simple. You just need find all products assigned to the chair category.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName = 'chair'

This would return the recliner as well. Also, this model gives the user some flexibility for creating more specific searches. For example we can change only one line (see the last line of the query below) to fetch all of the wooden chairs.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName in ('chair', 'wooden furniture')


Move/Copy unique rows from TableA to TableB, if they are not in TableB

If I can understand correctly what you are looking to do, you want to SELECT all records from TableA that only appear once, but don't already appear in TableB.

You can do this with a GROUP BY and HAVING to identify those that only appear once in TableA, then do an EXCEPT to filter out those that already appear in TableB.

SELECT
  int,
  com
FROM TableA
GROUP BY int, com
HAVING COUNT(*) = 1

EXCEPT

SELECT
  int,
  com
FROM TableB

If you actually wanted to SELECT ALL distinct records from TableA (regardless of how many times they occur in TableA) that do not already appear in TableB, then you could use the DISTINCT keyword.

Edit: As @ypercube correctly pointed out, EXCEPT already filters for DISTINCT rows

SELECT
  int,
  com
FROM TableA

EXCEPT

SELECT
  int,
  com
FROM TableB

If the two tables have more columns, it's handier to use NOT EXISTS instead of EXCEPT. You could also use ROW_NUMBER() to select an arbitrary row in 2005+ versions. So, the copying from table A to B would be:

INSERT INTO TableB
  (int, com, --- rest of the columns)
SELECT
  int, com, --- rest of the columns,
FROM
  ( SELECT
      int, com, --- rest of the columns,
      rn = ROW_NUMBER() OVER (PARTITION BY int, com ORDER BY someColumn)
    FROM
      TableA AS a
    WHERE
      NOT EXISTS
      ( SELECT *
        FROM TableB AS b
        WHERE b.int = a.int
          AND b.com = a.com
      )
  ) AS copy
WHERE
    rn = 1 ;


Converting MySQL AES_DECRYPT() to Postgres decrypt()

Actually, I suspect all three of your assumptions are correct and all play a part in why you cannot decrypt the data. I will attempt to explain a little.

One of the most important factors in encryption & decryption is binary compatibility - to be more precise, how does each database physically store the data? Is the data stored big-endian or little-endian, how are the internal data structures implemented (what is the binary difference between VARBINARY and BYTEA), what is the character set & encoding in use on each system? Encryption is bit-sensitive and so any variations in the bit/byte order will affect the outcome of any encryption or decryption.

Additionally, even though both systems can use AES their default settings vary slightly. On MySQL the default for AES is a 128bit key using ECB mode ; whereas on PostgreSQL the default is a 128bit key using CBC mode to encrypt the data. You will need to ensure that all of the options used to encrypt/decrypt are the same (even if you were trying to decrypt it within the same system).

Logically, if everything was identical between the two systems you should be able to decrypt the data. In practice, it's going to be a lot harder than that and if you have access to the source system, decrypt in on there, export the data, then import & encrypt it in PostgreSQL.


what’s the criteria SQL server (2014) will automatically recompile a stored procedure which has table variables?

Tables variables doesn't trigger recompile.
Well, they do, but not until SQL Server 2019 where they are treated as temp tables in this aspect.


Grid Control Standby database status: Creation status unknown

There's a MOSC note, and while it's specific to versions 10.2.0.1 to 10.2.0.5, maybe it worths testing.

The note states that when creating Standby using grid, there were some issues and the remaining part of its creation was done manually.

The recommendation is to remove the Standby DB from the Grid in order to clear the errors, and then rediscover the Standby DB.

For complete reference: Doc ID 1455165.1


Multi-homed SQL Server with High Availability Groups

Is there any way to set up the Failover Cluster and High Availability
  Group to tell it: Send synchronous replica traffic for SQL-ATL01 <-->
  SQL-ATL02 over 192.168.99.x Send asynchronous replica traffic for
  (SQL-ATL01, SQL-ATL02) <--> SQL-NYC01 over 10.0.0.x

Yes.

All AG replication traffic goes over TCP/IP connections made to the database mirroring endpoint on the instance hosting the primary replica.

The mirroring endpoint is, by default, listening on all IP addresses.  If you configure the mirroring endpoint to listen on only one of a server's IPs, then when you create the AG you must register the ENDPOINT_URL using that IP address or a hostname that resolves to only that IP address.

But, in your desired scenario you must have the ATL servers' database mirroring endpoints listening on both IP interfaces (or else NY wouldn't be able to connect).  So if you currently have the ATL nodes joined to the AG with their ENDPOINT_URL set to the 192.x IP addresses, you'll have to change that to add the NY node.

But you can ensure that the ATL-ATL traffic goes over the 192 network by using host file entries on the ATL servers, as @Tony Hinkle suggests.


Slide Partition from Rowstore to Columnstore

No, this is not possible. ALTER TABLE... SWITCH is a metadata operation and both tables need to have identical schemas. There's no such thing as a partitioned table that has some columnstore partitions and some rowstore partitions.

The best you can do is switch the rowstore partition out, build a clustered columnstore index on the switched out table, and then switch that table into the columnstore table.


select items from multiple rows and add to one

I'm making some assumptions about how your data is ordered (do all BB's come after AA's ordered by Id; do all CC's come after BB's ordered by Id), etc.
You also don't say what you want for the ID column in the final result, so I didn't include that column.

By using a Common Table Expression and the Row_Number Window Function, I'm added a sequential row number to each of your original rows.

Here's a demo set up script:

DECLARE @Data TABLE (
    ID INT
    ,col_name1 VARCHAR(10)
    ,col_name2 VARCHAR(10)
    ,col_name3 VARCHAR(10)
    ,col_name4 VARCHAR(10)
    )
INSERT INTO @Data (ID, col_name1, col_name2, col_name3, col_name4) VALUES
(1,'AA','a1','a2','a3'),
(2,'BB','b1','b2','b3'),
(3,'CC','c1','c2','c31'),
(4,'CC','c1','c2','c32'),
(5,'CC','c1','c2','c33'),
(6,'CC','c1','c2','c34')
;
with DataAndRowNumber as
(
select *,ROW_NUMBER () over(order by ID) as rn
from @Data
)
Select * from DataAndRowNumber

produces

| ID | col_name1 | col_name2 | col_name3 | col_name4 | rn |
|----|-----------|-----------|-----------|-----------|----|
| 1  | AA        | a1        | a2        | a3        | 1  |
| 2  | BB        | b1        | b2        | b3        | 2  |
| 3  | CC        | c1        | c2        | c31       | 3  |
| 4  | CC        | c1        | c2        | c32       | 4  |
| 5  | CC        | c1        | c2        | c33       | 5  |
| 6  | CC        | c1        | c2        | c34       | 6  |

From your question, it appears that you want col_name7 to be the first CC value, col_name8 to be the next CC value, col_name9 to be the next CC value and col_name10 to be the last CC value.  I'm assuming there will always be an 'AA' and a 'BB', so the first CC row is row number 3, the next CC row will be 4 and so on.

Putting it all together

DECLARE @Data TABLE (
    ID INT
    ,col_name1 VARCHAR(10)
    ,col_name2 VARCHAR(10)
    ,col_name3 VARCHAR(10)
    ,col_name4 VARCHAR(10)
    )
INSERT INTO @Data (ID, col_name1, col_name2, col_name3, col_name4) VALUES
(1,'AA','a1','a2','a3'),
(2,'BB','b1','b2','b3'),
(3,'CC','c1','c2','c31'),
(4,'CC','c1','c2','c32'),
(5,'CC','c1','c2','c33'),
(6,'CC','c1','c2','c34')
;
with DataAndRowNumber as
(
select *,ROW_NUMBER () over(order by ID) as rn
from @Data
)
select distinct
    (select col_name2 from DataAndRowNumber where rn=1) as col_name5,
    (select col_name2 from DataAndRowNumber where rn=2) as col_name6,
    (select col_name4 from DataAndRowNumber where rn=3) as col_name7,
    (select col_name4 from DataAndRowNumber where rn=4) as col_name8,
    (select col_name4 from DataAndRowNumber where rn=5) as col_name9,
    (select col_name4 from DataAndRowNumber where rn=6) as col_name10
from DataAndRowNumber

| col_name5 | col_name6 | col_name7 | col_name8 | col_name9 | col_name10 |
|-----------|-----------|-----------|-----------|-----------|------------|
| a1        | b1        | c31       | c32       | c33       | c34        |


This is just me, but no, I wouldn't ignore an event that said:

SQL 2008 service transitioned from a Normal state to a Failed state at 2018-05-11 09:16:21

That would be the kind of event I'd pay a little deeper attention to. If you Google for text involved in that error, you get KB articles like 2615182, suggesting that it's a Windows backup failing, not a SQL Server one.


You need to visit the MySQL download page.
Select your preferred platform (I prefer Debian Linux). And then select your OS-type (32-bit or 64-bit).
Click on the Download button. It will ask if you want to Sign Up or Login, just choose the No thanks, just start my download. If you are downloading the .deb file on the terminal right click the link and choose Copy Link Address. Open the terminal, type wget (paste the link).
After downloading, you can double click on the .deb file or type in your terminal sudo dpkg -i (filename)

You are done. I usually have trouble installing the 5.6 version, if so visit this site.


You don't need the values keyword when you use IN
delete from some_table
where (col1, col2) in ( (1,2), (3,4) );


Two thoughts come to mind:

You may have missed a step in the configuration setup - Check Eliminating the "Access Is Denied" Error
Is this part of a SQL Server upgrade?  If so, you may need to install SSMS for that release.  The SSMS 17+ do not support connections to Integration Services on earlier versions of SQL Server.  Previous SSMS releases


Indexing for group by + SUM query on mysql

I noticed something interesting about your query: you are getting these totals for this year. The trendCut date is not a static value. It is a very bad candidate for an index column since the column value changes and it is neither first nor last in the column order of the index.

The index you need is this

monitorID,trendCut,nGram,nGramWord,total

However, this would still be deficient.

Suggestion: If you do queries that require trendCut by year, you should have a trendCutYear column. Try the following:

Create New wordtrend Table
Create TrendCutYear Column in the New wordtrend Table
Create New Index in the New wordtrend Table
Load the New wordtrend Table from the Old wordtrend Table
Keep the Old wordtrend table under another name

Here are those steps:

CREATE TABLE wordtrendnew LIKE wordtrend;
ALTER TABLE wordtrendnew ADD COLUMN trendCutYear SMALLINT NOT NULL;
ALTER TABLE wordtrendnew ADD INDEX (monitorID,trendCutYear,nGram,nGramWord,total);
ALTER TABLE wordtrendnew DISABLE KEYS;
INSERT INTO wordtrendnew
SELECT oid,monitorId,nGram,nGramWord,negatives,
neutrals,positives,total,trendCut,YEAR(trendCut)
FROM wordtrend;
ALTER TABLE wordtrendnew ENABLE KEYS;
ALTER TABLE wordtrend RENAME wordtrendold;
ALTER TABLE wordtrendnew RENAME wordtrend;

Now, you have a static trendCutYear. You can now query using a static value in the middle of the index. The query can now be rewritten using trendCutYear instead of trendCut:

SELECT nGram, nGramWord, SUM(total) AS sTotal,
SUM(positives), SUM(negatives), SUM(neutrals) FROM WordTrend
WHERE monitorId = 21751021 AND trendCutYear = 2011
GROUP BY nGram, nGramWord ORDER BY sTotal DESC;

This should perform a whole lot better for you.

Give it a Try !!!


The UPDATE queries in your previous two questions (Question1,Question2) are hitting the table 'people' by PRIMARY KEY with row level locking. This is what I stated back in Question1 on June 6, 2011 10:03 AM

All the transactions are traversing the PRIMARY key. Since the PRIMARY is a clustered index in InnoDB, the PRIMARY key and the row itself are together. Thus, traversing a row and and the PRIMARY KEY are one and the same. Therefore, any index lock on the PRIMARY KEY is a row level lock as well.

Something else has not been considered yet that can attribute slowness to indexes: The use of NON-UNIQUE indexes in InnoDB. Every indexed lookup in InnoDB using non-unique indexes also have the rowID of each row attached to the non-unique key. The rowID basically eminates from the Clustered Index. Updating non-unique indexes MUST ALWAYS interact with the clustered index EVEN IF THE TABLE DOES NOT HAVE A PRIMARY KEY.

Another thing to think about is the process of managing BTREE nodes in an index. Sometimes, it requires the page splitting of nodes. All entries in the BTREE node of non-unique indexes contain non-unique fields PLUS the rowID within the clustered index. To properly mitigate the splitting of such BTREE pages without disturbing data integrity, the row associated with the rowID must experience a row level lock internally.

If the 'people' table has a lot of non-unique indexes, prepare to have a large number of index pages in the tablespace as well as having tiny little rows locks sneak up on you from time to time.

There ia another factor which is not as obvious: Key Population

Sometimes when an index get populated, the key values making up the indexes could become lopsided over time and cause the MySQL Query Optimizer to switch from keyed lookups, to index scans, and finally to full table scans. That you cannot control unless you redesign the table with new indexes to compensate for the lopsidedness ot keys. Please provide the table structure for the 'people' table, the count of the 'people' table, and the show indexes output for the 'people' table.

Even if queries use only the PRIMARY KEY, lopsidedness of keys in non-unique indexes still needs BTREE balancing and page splitting to occur. Such BTREE management will produce a notable slowdown due to intermittent row level locks you did not intend to happen.

UPDATE 2011-06-14 22:19

Queries From Question 1

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>',
iphone_device_time = '2011-06-06 05:35:09', last_checkin = '2011-06-06 05:24:42',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com',
phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>-<id>-<id>',
iphone_device_time = '2011-06-06 05:24:42', last_checkin = '2011-06-06 05:35:07',
location_lat = <lat>, location_long = -<lng>, gps_strength = 3296,
picture_blob_id = 1190,
authority = 1, active = 1, date_created = '2011-04-13 20:21:20',
last_login = '2011-06-06 05:35:09', panic_mode = 0,
battery_level = NULL, battery_state = NULL WHERE people_id = 3125

Picture the sequence in events

Find the row by PRIMARY KEY
Lock the row and clustered index
Create MVCC Data for all columns being update
Four columns are indexed (email,company_id,iphone_device_id,picture_blob_id)
Each index requires BTREE management
Within the same transaction space, steps 1-5 is trying to be repeated on the same row, updating the same columns (email the same in both queries, company_id the same in both queries, picture_blob_id the same in both queries, iphone_device_id different)

Queries From Question 2

UPDATE people SET iphone_device_id=NULL
WHERE iphone_device_id='iphone:<device_id_blah>' AND people_id<>666;

UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>',
temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@gmail.com',
phone = NULL, mobile = NULL, iphone_device_id = 'iphone:<device_id_blah>',
iphone_device_time = '2011-06-06 19:12:29', last_checkin = '2011-06-07 02:49:47',
location_lat = <lat>, location_long = <lng>, gps_strength = 66,
picture_blob_id = 1661,
authority = 1, active = 1, date_created = '2011-03-20 19:18:34',
last_login = '2011-06-07 11:15:01', panic_mode = 0, battery_level = 0.55,
battery_state = 'unplugged' WHERE people_id = 666;

These two queries are even more confusing because the first query is updating everything except people_id 666. Hundreds of rows are being painfully locked with just the first query. The second query is updating people_id 666 running the 5 sequence of events. The first query is running those same 5 sequence of events on every row involved except people_id 666 but the index for iphone_device_id is on an interecept course with two different queries. Somebody's gotta lock in the BTREE pages on a first-come-first-serve basis.

In the face of these two pairs of queries on a collision course to possibly lock the same BTREE pages within one index can be a gut-wrenching experience for InnoDB or any ACID-compliant RDBMS. Thus, an index slowdown is the destiny of these pairs of queries unless you can guarantee that the queries run with AUTOCOMMIT = 1 or by allowing dirty reads (although collisions like these make READ-COMMITTED and READ-UNCOMMITED a nightmare for MVCC).

UPDATE 2011-06-15 10:29

@RedBlueThing : In the queries from question 2, the first query is a range query, so a lot of row locks are being attained. Also notice both queries are trying to lock the same space id 0 page no 4611 n bits 152 is being locked in the PRIMARY KEY, aka clustered index.

In order to make sure you app is, at the very least, running based on the series of events you expect, there are two different options you could try:

Option 1) Convert this table to MyISAM (at least on a development server). Each UPDATE, INSERT, and DELETE will impose a full table lock on a first-come, first-serve basis.

Option 2) Try using the SERIALIZABLE isolation level. That will lock all intended rows in SHARED mode.

The sequence of events you expect will either break or be successful using these two alternative options. If both of these options fail, then you will need to look over your app and prioritize the order of execution of your queries. Once you establish that priority, you can simply undo these options (For option 1, go back to InnoDB, For option 2, go back to the default isolation level [stop using SERIALIZABLE]).


They are both "Integrated Authentication", as opposed to "SQL Authentication" which uses a user name and password stored in SQL Server.

The first is "Integrated Authentication With a Shared Servcie Account".

The second one "Integrated Authentication With a End-User Impersonation".


DBeaver: connecting to a PostgreSQL database server through SSH: can't initialize tunnel, auth fail

I tried DBweaver on Kubuntu and had the same issue. It looks like the issue is around the fact that the authenticity of the SSH server hadn't been verified beforehand, and DBweaver and unable to cascade the correct error message (e.g., Host key verification failed.) and instead misleadingly blames some authentication error.

I fixed the issue on Kubuntu as follows:

Disable the SSH Tunnel:

Run in the shell sudo apt-get install -y autossh
Add the shell command in your connection in DBeaver:

autossh -f -N -L 5432:localhost:5432 ssh_server_username@ssh_server_ip

At that point if you try to connect you'll have the same com.jcraft.jsch.JSchException: auth fail error message. Go to the shell and run the command:

ssh -f -N -L 5432:localhost:5432 ssh_server_username@ssh_server_ip

it will output:

The authenticity of host 'ssh_server_hostname (ssh_server_ip)' can't be established.
ECDSA key fingerprint is 29:13:b5:cb:99:21:b2:18:f1:11:aa:12:54:c2:88:9a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ssh_server_hostname,ssh_server_ip' (ECDSA) to the list of known hosts.
Password:

Go back to DBeaver, try to connect, that should work. I still don't know why I should use autossh instead of the SSH tunnel tab in DBeaver though.


error 1366 incorrect integer value: '""' for column 'word_id' at row 3

This error is logical, basically, an empty string is not a number. That's the first problem.

Also I tried using null, quotes, no quotes, all different types of formatting the data that you could imagine

The problem with this is that if your word_id column is not nullable, you can't insert a null value into it. Also, formatting invalid data doesn't make it valid.

error 1452 cannot add or update a child row: a foreign key constraint fails ('words', CONSTRAINT 'fk_words" FOREIGN KEY ('person_id') REFERENCES 'person' ('person_id'))

This error is normal. This is what Foreign Key constraints are for. Basically, what they do is make sure that you can't insert invalid data when referencing some other data
In this case, you reference a word (As far as I can tell), but you don't have words that have the ID value '' (empty string), so it doesn't allow you to insert the row with an incorrect reference.
Now that the errors are explained, there are two solutions:
1: Allow your word_id column to be null in your table schema. (This is possible even with foreign key constraints, however it might violate your database design). This way, you can insert a NULL value into the word_id column instead of an actual word_id.
2: Fix your data. Basically, you've built your database in such a way that you don't allow an empty or null value for the word_id column, which coincidentally means that your CSV data is invalid in the way that it is an empty string.
You need to update the CSV data to have a correct reference to the word_id column for your data to be 'correct' again.
The so-called 'correct' approach according to how you've set up your database is the second one, where you fix the data. However, both solutions will work. Just make sure to document the changes to your database if you chose solution number 1 because the application which uses the data from your database might not be expecting NULL values and might break because of this.


Database design: 4 types of users but have different functionality , separate or one table?

A simple design you can go for is to choose three tables

Table MasterUser

UserId - PK - Identity(auto increment)
phone
mail

Table AdminNormalUser

UserId -- Pk of this table and UserId of `MasterUser`
FirstName
LastName

Table CompanyPorviderUser

UserId -- Pk of this table and UserId of `MasterUser`
CompanyName
Fax

This design will give you a unique Id to each user, no matter a Admin, Normal, Company or provider user, which is a requirement for application that will use these tables, And it will get rid of columns with NULL values.