Does partitioning help if you don't query on the partition value? Say I have an orders.Order table that is partitioned on CreatedWhen (a datetime column set to GetDate() when the row is created).

The table has over 10 million rows and the partition is quarterly (or some such regular interval).

If I run a query like:

SELECT ord.OrderId, ord.ClientId, ord.ReceptId, ord.Cost, agt.State
FROM   orders.Order ord
       join personnel.Agent agt
          on ord.AgentId = agt.AgentId
WHERE  agt.FirstName = 'Bob'
       and LastName = 'Whiely'
       and ord.Cost > 135

Am I going to see any benefit from the partition for this query?

I don't see how there could be a benefit because I am not using the partition value in my query and all of the table still needs to be searched.

Note: The results of the query will all be in the most recent partition.  But I don't see how that would help.

Second Note: The partitions are all on the same SAN drive (Sys Admins won't let it be any other way), so the disk IO will not be parallel.

Is there a better way to partition so that random queries, like the one above, will perform better?  (Maybe on the primary key?)

Third Note: The query above is made up.  I am using it to show that I have many queries that hit many random columns (which are properly indexed).

Partitioning is not typically a performance enhancer.  Normally, partitioning is employed to enable efficient data management.  For instance, you can employ partitioning to easily swap old rows out of a table into an archive table, one partition at a time.
Querying against a partitioned table where your query does not include the partitioning key forces SQL Server to look at the entire table.  This is less efficient than simply scanning or seeking into a non-partitioned table because each partition is essentially a table, necessitating a UNION ALL style query against all the partitions.  This is not particularly obvious when looking at a query plan for a partitioned table, although you can see it if you look carefully at the pop-up properties for an index or table seek or scan:

To make this more obvious, lets set up a simple test rig in tempdb, with a date-based partitioning key:
USE tempdb;
GO
IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Tab;
    DROP PARTITION SCHEME PartScheme;
    DROP PARTITION FUNCTION PartFun;
END
GO

CREATE PARTITION FUNCTION PartFun (datetime)
AS RANGE LEFT
FOR VALUES (
      N'2012-01-01T00:00:00'
    , N'2012-04-01T00:00:00'
    , N'2012-07-01T00:00:00'
    , N'2012-10-01T00:00:00'
    , N'2013-01-01T00:00:00'
    , N'2013-04-01T00:00:00'
    , N'2013-07-01T00:00:00'
    , N'2013-10-01T00:00:00'
    , N'2014-01-01T00:00:00'
    , N'2014-04-01T00:00:00'
    , N'2014-07-01T00:00:00'
    , N'2014-10-01T00:00:00'
);

CREATE PARTITION SCHEME PartScheme
AS PARTITION PartFun
ALL TO ([PRIMARY]);

IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
DROP TABLE dbo.Tab;
CREATE TABLE dbo.Tab
(
    TabID int NOT NULL
    , CreateDate datetime NOT NULL
    , Data1 varchar(100) NOT NULL
    , Data2 varchar(10) NOT NULL
    , Data3 varchar(1000) NOT NULL
    , CONSTRAINT PK_Tab
        PRIMARY KEY CLUSTERED
        (CreateDate, TabID)
) ON [PartScheme](CreateDate);

This will populate the table with 1,000,000 rows with randomly generated data evenly spread over all partitions:
;WITH Ten AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, Million AS
(
    SELECT Num = (t6.Num * POWER(10, 5))
        + (t5.Num * POWER(10, 4))
        + (t4.Num * POWER(10, 3))
        + (t3.Num * POWER(10, 2))
        + (t2.Num * POWER(10, 1))
        + (t1.Num)
    FROM Ten t1
        CROSS JOIN Ten t2
        CROSS JOIN Ten t3
        CROSS JOIN Ten t4
        CROSS JOIN Ten t5
        CROSS JOIN Ten t6
)
INSERT INTO dbo.Tab (TabID, CreateDate, Data1, Data2, Data3)
SELECT m.Num, DATEADD(DAY, m.Num % 1000, N'2012-01-01T00:00:00')
    , CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
    , CONVERT(varchar(10), CRYPT_GEN_RANDOM(10))
    , CONVERT(varchar(1000), CRYPT_GEN_RANDOM(1000))
FROM Million m;

Let's go ahead and create a non-clustered index using our partitioning scheme:
CREATE NONCLUSTERED INDEX IX_Tab
ON dbo.Tab(Data1)
ON [PartScheme](CreateDate);

Now, let's look at three "typical" queries you might execute (the first query just gets an actual value out of the table, which is then used in the WHERE clause for the three queries):
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.Tab t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
FROM dbo.Tab
WHERE Tab.Data1 = @d;

SELECT Data1
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'
    AND tab.CreateDate <= N'2013-08-29T00:00:00';

Here is a quick screenshot of the execution plans so you can see the difference adding the partitioning key to the WHERE clause can make:

The "query costs relative to the batch" numbers are instructive here.  The first query consumes 50% of the batch cost since it performed a scan of all 13 partitions.  It does that since it has no idea where in the table the particular value of Data1 exists.  The second query consumes only 23% by virtue of the "Start Date" parameter, which can eliminate half the partitions.  Finally, the third query only consumes 4% since it only needs to look at a single partition, because we included a "start date" and an "end date".
The "statistics" for the 3 queries are:

Table 'Tab'. Scan count 13, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 6, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What if we add one of the other columns to these queries:
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.Tab t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
    , Data2
FROM dbo.Tab
WHERE Tab.Data1 = @d;

SELECT Data1
    , Data2
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
    , Data2
FROM dbo.Tab
WHERE Tab.Data1 = @d
    AND tab.CreateDate >= N'2013-08-23T00:00:00'
    AND tab.CreateDate <= N'2013-08-29T00:00:00';

The execution plans:

The statistics for those:

Table 'Tab'. Scan count 13, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 6, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tab'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, lets just do away with the partitioning, and re-run all those tests:
IF OBJECT_ID(N'dbo.TabU', N'U') IS NOT NULL
DROP TABLE dbo.TabU;
CREATE TABLE dbo.TabU
(
    TabID int NOT NULL
    , CreateDate datetime NOT NULL
    , Data1 varchar(100) NOT NULL
    , Data2 varchar(10) NOT NULL
    , Data3 varchar(1000) NOT NULL
    , CONSTRAINT PK_TabU
        PRIMARY KEY CLUSTERED
        (CreateDate, TabID)
) ON [PRIMARY];

;WITH Ten AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, Million AS
(
    SELECT Num = (t6.Num * POWER(10, 5))
        + (t5.Num * POWER(10, 4))
        + (t4.Num * POWER(10, 3))
        + (t3.Num * POWER(10, 2))
        + (t2.Num * POWER(10, 1))
        + (t1.Num)
    FROM Ten t1
        CROSS JOIN Ten t2
        CROSS JOIN Ten t3
        CROSS JOIN Ten t4
        CROSS JOIN Ten t5
        CROSS JOIN Ten t6
)
INSERT INTO dbo.TabU (TabID, CreateDate, Data1, Data2, Data3)
SELECT m.Num, DATEADD(DAY, m.Num % 1000, N'2012-01-01T00:00:00')
    , CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
    , CONVERT(varchar(10), CRYPT_GEN_RANDOM(10))
    , CONVERT(varchar(1000), CRYPT_GEN_RANDOM(1000))
FROM Million m;

CREATE NONCLUSTERED INDEX IX_TabU
ON dbo.TabU(Data1)
ON [PRIMARY];

Now, the first set of queries:
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.TabU t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
FROM dbo.TabU
WHERE TabU.Data1 = @d;

SELECT Data1
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'
    AND TabU.CreateDate <= N'2013-08-29T00:00:00';

And the plans:

And the stats:

Table 'TabU'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

All three queries now have the same "cost", which is substantially lower than the partitioned version.
The queries with Data1 and Data2 columns:
DECLARE @d varchar(100);
SELECT TOP(1) @d = t.Data1
FROM dbo.TabU t
WHERE t.CreateDate > N'2013-08-24T00:00:00';

SELECT Data1
    , Data2
FROM dbo.TabU
WHERE TabU.Data1 = @d;

SELECT Data1
    , Data2
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'

SELECT Data1
    , Data2
FROM dbo.TabU
WHERE TabU.Data1 = @d
    AND TabU.CreateDate >= N'2013-08-23T00:00:00'
    AND TabU.CreateDate <= N'2013-08-29T00:00:00';

and the stats:

Table 'TabU'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabU'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Clearly, if you can't do the queries the correct way, by including the partitioning key, then you are going to see worse performance with partitioning than without.


A trigger that show the user that has made an insertion I have the following code

create table o_c(
op_id NUMBER(10,0) PRIMARY KEY NOT NULL,
n_c VARCHAR2(50) NOT NULL,
op VARCHAR2(500) NOT NULL,
amount NUMBER(9,2)
);

create table a_o(
a_id NUMBER(10,0) PRIMARY KEY NOT NULL,
details VARCHAR2(500) NOT NULL
);

create sequence a_o_seq
START WITH     1
INCREMENT BY   1
NOCACHE
NOCYCLE;

And I need a trigger that writes in "details" of each record this message:

User username performed an insert operation. The data added were saved with op_id = value, the operation was performed on the date DATE

Google is there for helping you out in coding. Take this as a challenge start coding , it will help you out more than someone writing it for you. Have a nice day.


Standard Meaning of "Bounce the Database" I have been hearing the term "bounce the database" lately, and researching it, it seems that people take it to mean different things.

Most recently is was an Oracle DBA saying:

  "not even the DBAs could access the instance, so we had to bounce the database"

Is there a standard definition?

In the context of Oracle databases, I have seen people use it to refer to a shutdown normal, shutdown immediate, or shutdown abort followed by a restart.

Would it mean the same thing in the context of SQL Server? e.g. stop the service, restart, or kill the service abruptly, restart, etc.

Perhaps in all cases it just means to shutdown and restart a database (or instance?, or server?) and depending on the context, can be achieved in a variety of ways.

It's been consistent for me: bounce generally means restart
- Bounce SQL Server = restart the service, normally
- Bounce the server = reboot
- Bounce the PC = reboot
- Bounce IIS = run iisreset

I'd interpret "bounce Oracle" as restart the DB engine processes


How to access external DLL's static methods that incorporate output parameters in SQL Server I know how to load a CLR DLL in SQL Server and access to its static methods which does not incorporate output parameters in their method signatures but what I cannot realize is how I can use methods which have output parameters.

For example it is strightforward to access Math.dll 's factorial method in SQL server like this:

CREATE ASSEMBLY MathAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\Math.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

CREATE FUNCTION Factorial(@x FLOAT)
    RETURNS FLOAT
    EXTERNAL NAME MathAsm.Math.Factorial;
Go

But what if method's signature would be like:

public static int GetInfo(int[] inputParams, out int[] outputParams)

What have you tried and what error are you getting?

The following works for me on SQL Server 2008 R2.

C#:

namespace CLRTest
{
    public static class ParamsTest
    {
        [SqlProcedure]
        public static void Test(out int p)
        {
            p = 5;
        }
    }
}

SQL:

CREATE PROCEDURE dbo.Test(@p int OUTPUT)
    AS EXTERNAL NAME [CLRTest].[CLRTest.ParamsTest].[Test];
GO

DECLARE @x int;

EXEC dbo.Test @p = @x OUTPUT;

SELECT @x;

Returns 5 as expected.

Now, if you're trying to return an array in an output parameter, I don't think that's supported, save for the exceptions noted here. If that's what you're looking to do, use a table-valued function or stored procedure to return a result set. Note also that table-valued (input) parameters are not supported as mentioned here, assuming you're using SQL Server 2008 or higher.


PostgreSQL results of conditional insert I've got a simple query which inserts data to table when value (unique) does not exist:

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM mytable WHERE hash = 'somelonghash'
    )
    THEN
        INSERT INTO mytable (id, hash, status, datecreated)
        VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z');
    END IF;
END $$;

But if I want to receive results like "RETURNING *" if there was an insert, I receive error: "query has no destination for result data".
I've tried something like:

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM mytable WHERE hash = 'somelonghash'
    )
    THEN
        INSERT INTO mytable (id, hash, status, datecreated)
        VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
        RETURNING *;
    END IF;
END $$;

or even like

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM mytable WHERE hash = 'somelonghash'
    )
    THEN
        WITH ins_res AS (
            INSERT INTO mytable (id, hash, status, datecreated)
            VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
            RETURNING *;
        ) SELECT * FROM ins_res;
    END IF;
END $$;

I assume I'm missing SELECT statement somewhere at the very beginning, like SELECT * FROM (IF-THEN-ELSE results), but can't figure out where. Also this query without IF-THEN works:

WITH ins_res AS (
    INSERT INTO mytable (id, hash, status, datecreated)
    VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
    RETURNING *
) SELECT * FROM ins_res;

You can try insert ... select:

    INSERT INTO mytable (id, hash, status, datecreated)
    SELECT 123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z'
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE hash = 'somelonghash')
    RETURNING *;

But: is there a reason preventing you from using on conflict instead?


SQL Server 2008 R2 index rebuild fails with severity 17 Occasionally during our index maintenance, the job will fail with a SEV 17 error where enough space can not be allocated for the object it is rebuilding. The database is laid out as such:

Data_file1    PRIMARY    0 growth         0% free                Max Size UNLIMITED
Data_file2    PRIMARY    0 growth         0% free                Max Size UNLIMITED
Data_file3    PRIMARY    0 growth         Less than 1% free      Max Size UNLIMITED
Data_file4    PRIMARY    250 MB growth    Less than 1% free      Max Size UNLIMITED

Essentially, 3 of the 4 data files are full and not allowed to grow, the fourth is full and allowed to grow. The files are spread out across different LUNs (and the reason for why is messy). So when the online index rebuild starts, it is my understanding that if any additional space is needed, it will grow into Data_file4 and be fine, but it is apparently trying to grow into a different file where growth is not allowed and failing. I am unable to reproduce this error, but I was wondering if anyone had insight into why this happens.

Full SQL Server version is 2008 R2 Enterprise, SP2 CU 4 (10.50.4270). We use Ola Hallengren's rebuild scripts, where we rebuild online but no sort in tempdb.

My experience is that it is always going to do an online rebuild in the filegroup on which the index lives.  It has to map the existing index and hold enough space for, essentially, one copy.

You should only be getting the error when an index which is too large to hold mappings (the copy) is rebuilt - for instance, one time it may be fragmented enough to qualify in Ola's script and the next time it may not be.

There is a great article http://technet.microsoft.com/en-us/library/ms179542(v=sql.105).aspx  which I had to read several times when running into disk space issues with indexes.


How to determine MySQL queries per day? I'm investigating the big switch from MySQL to a NoSQL DBaaS and I've run into an issue trying to forecast expenses. Essentially, I can't figure out how many queries my current MySQL server handles per day to try and estimate the number of requests I'll be using with Cloudant, which charges $0.015 per 100 PUTs, POSTs, and DELETEs and $0.015 per 500 GETs and HEADs.

I've found a lot of information about using SHOW STATUS and SHOW GLOBAL STATUS to get the stats that MySQL collects on itself, but there's no timeframe reference.

For instance, SHOW GLOBAL STATUS returns the following:

Queries                           | 13576675

Which is great, except I have no idea the timeframe that wraps that number. 13 million queries when? Per month? Year? Since the beginning of time?

The MySQL docs don't really elaborate too much:

  Queries

  The number of statements executed by the server. This variable includes statements executed within stored programs, unlike
  the Questions variable. It does not count COM_PING or COM_STATISTICS
  commands. This variable was added in MySQL 5.0.76.

Thanks in advance for any help.

For SELECTs:

show global status like "Com_select";

UPDATEs:

show global status like "Com_update";

INSERTs:

show global status like "Com_insert";

DELETEs:

show global status like "Com_delete";

ALl values are "cumulativ" since MySQL last restart.

So to get your SELECTs in one hour:

At 9pm:

[21:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 671664 |
+---------------+--------+
1 row in set (0.00 sec)

At 10pm:

[22:00:00] [DEV\(none)] mysql> show global status like "Com_select";
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 672363 |
+---------------+--------+
1 row in set (0.00 sec)

The number of SELECT in the past hour : 672363 - 671664 = 699

Best Regards


Postgresql 9.6 Error trying to have a function return a string without first casting it as a string. I am in the progress of learning PostgreSQL (9.6) and am a bit confused by an error I am receiving. If I attempt to create the following function I I receive the following error.

create or replace FUNCTION HelloWorld ()
RETURNS TABLE(HelloCol text, WorldCol text) AS $$

SELECT 'Hello' , 'World' ;
$$
 LANGUAGE sql;

Error:

    ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns unknown instead of text at column 1.
CONTEXT:  SQL function "helloworld"
SQL state: 42P13

If I cast the text as text it works properly.

create or replace FUNCTION HelloWorld ()
RETURNS TABLE(HelloCol text, WorldCol text) AS $$

SELECT cast('Hello' as text) , cast('World' as Text);
$$
 LANGUAGE sql;

I've also tried char(10) and varchar(10) in the first scenario with the same error. I assume I'm just not getting the right variable type in the "TABLE(HelloCol text, WorldCol text)" portion but if 'Hello' isn't text, char, or varchar, what is it? Just some undefined type?

I can change the returned columns to int and have it Select 1,2 and that works just fine without casting.

This has been fixed in version 10, as seen in the docs, Type Conversions:

SELECT Output Columns
The rules given in the preceding sections will result in assignment of non-unknown data types to all expressions in a SQL query, except for unspecified-type literals that appear as simple output columns of a SELECT command. For example, in
 SELECT 'Hello World';

there is nothing to identify what type the string literal should be taken as. In this situation PostgreSQL will fall back to resolving the literal's type as text.
...
Note
Prior to PostgreSQL 10, this rule did not exist, and unspecified-type literals in a SELECT output list were left as type unknown. That had assorted bad consequences, so it's been changed.

The solutions for you are pretty straight forward:
Either cast the values to text or upgrade to version 10.


Why am I connecting securely to MySQL when I don't have client certificates? I don't understand the MySQL documentation for encrypted connections. The server is configured with certificates, and unless I am misreading it, the documentation says that the client must have certificates as well.

But, if I start the client with 'mysql --ssl' then \s reports that the connection is SSL encrypted using DHE-RSA-AES256-GCM-SHA384 even though I haven't used any certificates with the client. /etc/my.cnf.d/client.cnf does not have any certificates in the [client] section, and I don't have a .my.cnf

How is that possible that I am connected with SSL? Is it not really encrypted?

Yes the connection is encrypted. I think you are getting a few things mixed up here. Let me try to simplify it for you:

SSL is a protocol for exchanging data across a secure network, and in order to establish a connection, the server must have a certificate to use. In order to obtain a certificate you must be issued one. This is generally done by a Certificate Authority (CA) or by using a self-signed one, but we will keep it simple here and stick with CA's.

If the certificate you used when setting up SSL is valid, and is trusted by the client attempting to establish a connection, then the connection attempt should succeed. The client does not need to present a certificate here, but it does need to trust the issuer of the certificate. This is generally done by having a copy of the CA certificate on your machine and verifying that the certificate presented by the server is signed/issued by the CA.This corresponds roughly to the --ssl-mode=VERIFY_CA and --ssl-mode=VERIFY_IDENTITY (which checks that the host name in the certificate matches the server you are trying to connect to).

MySQL allows the client to select the certificate to use when verifying the identity of the server using the --ssl-ca, --ssl-cert and --ssl-key options and in order for the transport connection to be successful, the certificate presented by the client must be issued by the same CA that issued the server certificate. This is optional, and if you don't present one then the client will look for a corresponding certificate in its store.

Additionally, once you have established an SSL connection you can authenticate an account using certificates (generally using some kind of PKI technology) which would essentially replace using passwords to log in - but the key thing to understand here is that by the time the process gets to the point where you are attempting to log in, the SSL connection has already been established. When you use this method, you generally find that the client certificates have been issued by the same CA as the one that issued the server certificate.

I use the following analogy to explain it to people I teach:

  When my daughter was born the only people she knew she could trust was her mother and me. Then as she got older and more people came into her life we told her who she could and couldn't trust. In this scenario, my wife and I act as the Certificate Authority verifying the trustworthiness of others. If we have not told our daughter she can trust someone then she will not speak to them, much like a how a client will act if it does not trust a server.

The topic of certificates is quite extensive but the key for you is to understand the difference between the client trusting the server enough to establish a secure connection and you then authenticating across that connection using a client certificate.


Advantages of using PgBouncer vs HAProxy? Are there any advantages for using one over the other? I'm only looking at options to hide the actual IP address of the master and slave servers from my processes, and not having to restart them if the master fails.

If Nagios detects a failing master, I still want to get involved to do the failover, including telling HAproxy / PgBouncer about the new master.

HAProxy is a low lewel high availability and load balancing solution. PgBouncer combines a very good connection pooler and a very limited load balancing for PostgreSQL.

So both has different goals and can not be compared like "vs" in my opinion. Though they can be used in a combination with each other, eg. if one needs to load balance read only queries between several replicas with HAProxy and these replicas are served by their own PgBouncer instances to get pooling advantages.

More about what PgBouncer is and what it is not in is perfectly described in this blog post.


Automatic Recovery of MyISAM table I have an application in C language which inserts the report data in MyISAM table by using mysql_stmt_execute() function.
Due to load on the table, it gets crashed.
For auto repair of crashed table, I have added following option in /etc/my.cnf file
myisam-recover-options=FORCE,BACKUP

When the table gets into a crashed state, then mysql_stmt_execute() function fails and I can't insert the reports. As I have specified recovery options, expected result should be, table gets repaired and application should work fine.
QUESTIONS

How to repair the table automatically when it gets crashed ?
Why myisam-recover-option can't recover the table when query is executed ?

myisam_recover_options is not a dynamic variable. It is among the startup options for MyISAM.

If you have set this option in my.cnf, mysqld must be restarted

Please restart mysqld with

# service mysql restart

or if you have Windows

C:\> net stop mysql
C:\> net start mysql

You should try switching your table to InnoDB and make your table crash-safe.


Microsoft SQL Server - Permission to create a Snapshot I've developed a set of SSIS packages to load a data warehouse.
In order to manage any issue that may arise during package execution, each package starts by creating a snapshot of the data warehouse.

The package authenticates on my server using a login that does not belongs to the dbcreator server role.
Obviuosly with this configuration the package fails due to the lack of permission to create/drop a database.

The dbcreator server role allows a user to create/drop a database on the whole instance.
My question is: Is there a way to grant a database user the permission to create a snapshot of the given database (and only that) and eventually drop the snapshot (and only the snapshot associated to the given database)?

What are the best practices to handle this requirement?

Its not possible to do straight away as mentioned on MS documentation:

  Permissions Any user who can create a database can create a database
  snapshot; however, to create a snapshot of a mirror database, you must
  be a member of the sysadmin fixed server role.

What I understand is - if you could have alternative in place for DB creator role, this purpose can be achieved and this has been explained very well by one of MSSQL SME Mr. Solomon Rutzky

You can achieve this by using module signing which has been explained by Mr. Tibor Nagy on this link --> https://www.mssqltips.com/sqlservertip/3336/how-to-use-module-signing-for-sql-server-security/

You can also refer the question and answer thread for better understanding of module signing at the same forum and how can this be used:

Granting a user a permission to restore a database

Execute Permissions for a Store Procedure that creates databases

https://sqlquantumleap.com/2018/02/15/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-server-level/

More about Module signing at Microsoft:

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms345102(v=sql.100)

Hope this leads you into right direction.


How Count Row Value in SQL Server database? I have a table called TimeSheet :

Card_No    Name    1    2    3    4
123        GGG    A    P    P    P
123        GGG    A    P    A    P
123        GGG    A    P    P    P
123        GGG    A    A    A    P

I want to count total A in one row. Result should be like this

Card_No    Name    1    2    3    4    TotalA
123         GGG    A    P    P    P    1
123         GGG    A    P    A    P    2
123         GGG    A    P    P    P    1
123         GGG    A    A    A    P    3

How can I do it ? Please help me.

One way

SELECT
      *,
      CASE WHEN [1] = 'A' THEN 1 ELSE 0 END +
      CASE WHEN [2] = 'A' THEN 1 ELSE 0 END +
      CASE WHEN [3] = 'A' THEN 1 ELSE 0 END +
      CASE WHEN [4] = 'A' THEN 1 ELSE 0 END AS TotalA
FROM TimeSheet

or

SELECT *,
       (SELECT count(*)
        FROM   (VALUES([1]),
                      ([2]),
                      ([3]),
                      ([4])) V(c)
        WHERE  c = 'A') AS TotalA
FROM   TimeSheet


Oracle: Resetting an identity column vs resetting a sequence An Oracle Identity column can be reset by simply changing its start value with an alter table... modify statement. Given that identity column internally uses a sequence, why is it not possible to do the same with a sequence i.e. change its start value to a specific number with a single alter statement? So far the only way I've seen to do this is through a procedure involving multiple steps.

Or, to put it in another way, how does Oracle internally handle the altering of start value of an identity column? Does it drop and recreate the backing implicit sequence? Or is there a way to reset a sequence which is just not accessible to users?

Given that identity column internally uses a sequence, why is it not possible to do the same with a sequence i.e. change its start value to a specific number with a single alter statement?

Oracle Official way of resetting a sequence is dropping and recreating the sequence, of course, you need to take care about the privileges granted.

  You can also use this statement to restart a sequence by dropping and then re-creating it. For example, if you have a sequence with a current value of 150 and you would like to restart the sequence with a value of 27, then you can drop the sequence and then re-create it with the same name and a START WITH value of 27.

Oracle 12c Docs

However, there is an undocumented way to reset the sequence which works for Oracle 12c.

SQL> select serial.nextval from dual;

   NEXTVAL
----------
         4

SQL> alter sequence serial restart start with 1;

Sequence altered.

SQL>  select serial.nextval from dual;

   NEXTVAL
----------
         1


DB2 (LUW) equivalent for Oracle "drop user cascade" This question is related to creating and initialising test and development databases for an application that has the database running on LUW (linux-unix-windows)

On oracle, we have

an os user (on linux) called oracle,
an admin oracle user called    sys or system
a fixed predefined oracle user that is the    application object
owner, let's call it appowner
an oracle user that is the application user, ie has limited
previleges on the    objects owned by appowner, let's call it
appuser

whenever there is a need to initialise the database, ie to start from scratch, we first connect to oracle using sys or system oracle user and then issue this command:

DROP USER appowner CASCADE;

and then we go about recreating the user and the objects it owns from scratch. We also grant some previliges to appuser on the objects created by appowner

The application always logs in as appuser rather than appowner unless there are any administrative tasks to be performed on the database.

Now we are porting this application to db2, and this is where we are flummoxed.

For starters, db2 creates these os users, which are also db2 users:

dasusr1
db2inst1
db2fenc1

How do these three users map to sys/system, appowner and appuser?

I believe dasusr1 is the rough equivalent of sys/system,
db2inst1 is the rough equivalent of appowner, and
db2fenc1 is the rough equivalent of appuser

(please correct me if I'm wrong, and I fully appreciate that the mapping will not be exact)

That being the case, if I have to remove all objects owned by db2inst1, do I login as dasusr1 and drop the user db2inst1?

There isn't really a one to one mapping between the oracle users and db2 users, because

db2inst1 can create multiple databases whereas appuser is mapped to one database in oracle
within a db2 database, there can be multiple schemas, whereas in oracle, one users maps to one schema

So it's a little confusing. It would help if someone with experience in porting applications from oracle to db2 could throw some light on the equivalence between the db2 and oracle users, which would finally lead to finding out the db2 equivalent for oracle drop user cascade.

It might be helpful to review DB2 in general first.

When you install DB2 it creates an instance. Think of this as a "server". An instance can have zero to many databases within it (can't say I know the limit). When you created DB2 locally it created an instance for you named DB2. Usually when you install DB2 on say a Unix server, you name your instances. You can have more than one instance on the same physical server. You dsinst1 id is the "owner" of the instance (usually the instance is named after the ID that owns it). You never want to delete this ID without scrapping the instance or you can end up with some very strange behaviors. This ID (since it is instance ower) has the power to create databases, but does not have to. This blog entry by fellow DB2 user Ember Crooks helps explain the concept of an instance to those more familiar with Oracle.

Instances can have databases created within them. We'll come back to this later.

The DAS (or DB2 Administration Server) is really more like a service. It is what allows some of the GUI tools like DB2 Control Center to interface with DB2. From what I understand, this service isn't necessary for DB2 to run, but is often necessary for end-users to interface with DB2 from a non-command line perspective. (Mustaccio am I correct here?). No matter how many instances you have on a physical box, there is only one installation of the DAS. This one installation can service all the instances. There is usually a separate ID that is needed to execute the DAS service. This is your dasusr1. Again, you don't want to delete this ID. This ID usually has no authority to create any objects within your database.

Now for the fence ID (ie, db2fenc1). Having one of these is a good idea. When you create an instance you can specify a fence ID. If you do, it becomes that ID. If you don't, then the instance owner ID becomes the fence ID. The fence ID is used for running certain stored procedures within DB2. In DB2 there are two ways to run stored procedures: fenced and unfenced. Unfenced means the stored procedure runs within the DB2 engine's memory. It will run faster, but if the stored procedure is more than SQL (ie, contains C++ or Java code for example) and it crashes, it can threaten the engine and actually crash it and cause harm. Fenced stored procedures run outside of the DB2 engine memory. They are thus slower, but if they crash they do not threaten the DB2 engine because they ran in their own protected memory area. An example of how this is used by DB2: if you run the db2 export utility from the ADMIN_CMD stored procedure, DB2 will switch the user on you of who actually executes the export. This is because you are doing file I/O and DB2 wishes to run this fenced to protect the engine. So even if you are running this stored procedure call as the instance owner, DB2 will switch the ID to the fenced ID (with no chance of you altering this behavior). If you look at the permissions of who owns the export file, you will notice it is the fenced ID, rather than the instance ID (or other ID you used to fire off the stored procedure). Of course if your instance ID is your fenced ID, then it will look one and the same to you.

(Big breath).

OK. So I said all that to introduce you to DB2 in a nutshell. Now we get to what you are probably worrying about -- who owns what? Which ever ID creates the database owns the database. There are "groups" that DB2 accepts that generally have the DBADM (ie, database administrator) authority to create databases. So if you created a database using your instance owner ID, then it would be the owner of the database and any objects it creates within the database. If a different ID connects to the database (as long as it has the correct permissions) it can create objects and own them.

Here is the interesting thing about groups and IDs within DB2. They aren't really objects from what I can see. So you can't drop them. You can't create them either (at least from what I can see). When you grant or revoke a permission to an ID, DB2 then stores mention of that group or ID. But DB2 does not really "create" them. Most often I have seen DB2 pointed to the OS or to an LDAP or both to authenticate the IDs. If the IDs are removed, you have to manually remove the permissions from the IDs and/or transfer ownership of the objects. There is nothing magical about this here. I asked a similar question having to do with doing a backup/restore to a different system and how that affects permissions.

And lastly for your comment about schemas, generally each ID maps to its own schema. Whether or not the schema exists (created by the DBADM ID explicitly or by the end user through the IMPLICIT_SCHEMA permission) is another question. Generally whatever ID connects to a database, DB2 assumes the schema is the same as the user id and you have to switch schemas (SET SCHEMA MYSCHEMA) or explicitly mention your objects (SELECT * FROM MY_OTHER_SCHEMA.MY_TABLE) to work with them.

Hope this helps.


unknown variable after upgrading from mySQL 5.7 to 8 After upgrading from mySQL 5.7 to 8.0 on freebsd, I keep getting the follow error, which prevents mysql to start:
[ERROR] [MY-000067] [Server] unknown variable 'innodb_large_prefix=true'.

The thing is that setting is not on my my.cnf file. I understand it is deprecated on mySQL 8, but it is not on my configuration.
Is there a way to fix it?
Best,
Francis

Found it. Apparently there was a naughty my.cnf file under /usr/local, where the variable was. Problem fixed by commenting it.


NEWSEQUENTIALID() resets sequence on server restart MSDN learns me that NEWSEQUENTIALID():

  Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.

So a newly generated GUID can be lower than a previously generated GUID. Isn't this highly undesirable in terms of index fragmentation and such?
And if so: is there some way to provide NEWSEQUENTIALID with a seed of some sort, to prevent this?

Or isn't this as bad as it seems as seeing the servers won't restart that often en we can deploy maintenance scripts for rebuilding indexes?

No way to seed it. That whole "globally unique" part gets in the way.

the bigger question here is "do you truly require this?"

GUIDs are big, GUIDs -even with newsequentialid, can lead to index fragmentation. If you need GUIDa where do you need them? They typically do not make great clustered index candidates. For both of these reasons and more.


Why did a Redshift UNLOAD increase precision of a float4 field in the output? I created a table in AWS Redshift such as

CREATE TABLE exampleTableName (
  id       int identity(1,1),
  accId    varchar(16) encode zstd,
  amount   float4,

  primary key(id)
)
distkey(accId)
interleaved sortkey(accId);

An example record in the table has the amount field's value as 120.12.

However, when I try to export the data by performing an UNLOAD, the resulting file (essentially a CSV) has additional precision for the field value.

The unload command:

UNLOAD ('SELECT * from exampleTableName')
TO 's3://bucket/prefixFile_'
IAM_ROLE 'XXX'
HEADER
ADDQUOTES
PARALLEL OFF
MAXFILESIZE AS 5gb
DELIMITER AS ','
GZIP;

The field value in the resulting output: 120.120003 (i.e. it has added 4 more decimal places, that aren't in the original dataset).

Why is this happening, and how can I prevent the additional precision (i.e. decimal places) from being output as part of the UNLOAD command?

Answer from AWS forum:

this happens when you use FLOAT for the decimal data because it cannot store values
of arbitrary precision. https://en.wikipedia.org/wiki/IEEE_754

I would generally always recommend using the DECIMAL datatype unless you have an
existing application that has an unchangeable requirement for FLOAT, e.g.,
calculation expects FLOAT and output cannot change.

Additionally, by using DECIMAL you are able to use our new AZ64 compression encoding
which will reduce the amount of storage needed and improve your query performance.
https://aws.amazon.com/about-aws/whats-new/2019/10/amazon-redshift-introduces-az64-a-new-compression-encoding-for-optimized-storage-and-high-query-performance/


How to anonymize pg_dump output before it leaves server? For development purposes, we dump the production database to local. It's fine because the DB is small enough. The company's growing and we want to reduce risks. To that end, we'd like to anonymize the data before it leaves the database server.

One solution we thought of would be to run statements prior to pg_dump, but within the same transaction, something like this:

BEGIN;
UPDATE users
SET email = 'dev+' || id || '@example.com'
  , password_hash = '/* hash of "password" */'
  , ...;
-- launch pg_dump as usual, ensuring a ROLLBACK at the end
-- pg_dump must run with the *same* connection, obviously

-- if not already done by pg_dump
ROLLBACK;

Is there a ready-made solution for this? Our DB is hosted on Heroku, and we don't have 100% flexibility in how we dump.

I searched for postgresql anonymize data dump before download and variations, but I didn't see anything highly relevant.

There are four tough problems to solve when trying to anonymize data. I'm going to summarize an old blog post of mine about it:

1: Anonymized data may grow in size. If you have peoples' names, for example, you don't really want all of the anonymous ones to have exactly the same length as the original data. If you change the data as it goes out, that means the solution needs to understand the original field name's length to avoid growing data out to something too large to fit.

2: Anonymized data has different statistics distribution. Say you've got a date-of-birth field - you could completely randomize the dates, but then it'll be much less meaningful on the other side, especially if you're trying to do performance testing. Plus...

3: Anonymized data breaks referential integrity. I know it's a bad idea, but sometimes applications join on fields that need to be anonymized. I hate that I've seen this, but I've seen folks using email address in multiple tables, and then expecting to join on those, or find related orders.

4: Anonymizing slows down the export. You'll need to run some kind of business logic to determine which columns need to be anonymized, and then generate the new data in its place.

I used to say that if you could solve this problem, you'd be rich - but then you've got some competition. Delphix does data masking for DB2, Oracle, SQL Server, etc, but I don't think they've got Postgres or Heroku covered - yet.


Force PostgreSQL clients to use SSL? I have configured ssl = on in postgresql.conf (and installed a certificate etcetera). Does this ensure that all clients will always connect over SSL?

(I.e. does ssl = on it make it impossible to connect without SSL encryption?)

Are there other ways to ensure that all clients always connect over SSL/TLS?

ssl = on only enables the possibility of using SSL.

To ensure that all clients are using SSL, add hostssl lines in pg_hba.conf, e.g.,

hostssl  all  all  0.0.0.0/0  md5

and remove all host lines.  (Well, maybe keep the ones for localhost.)

If the desire is to force the client to send a certificate, then md5 has to be changed to cert. e.g.,

hostssl  all  all  0.0.0.0/0  cert


Storing overridable hierarchical settings I've searched the Internet and found the question Pattern(s) about hierarchical settings overwriting on Software Engineering SE. It exactly describes my need, the only difference is that I need to store the settings in a SQL Server database.

How can I implement it in SQL Server?

Whenever I select the setting for level1-child1, I want all overridden settings from child1, plus inherited ones from level1. For example:

level1: max-value:100;  min-value:10;  enable-alarm:true;
level-child1: min-value:30;

after select:

level1-child1: max-value:100; min-value:30; enable-alarm:true;

Currently I use hierarchy path to implement hierarchies.

You don't give a lot of detail on your current structure, so I'm assuming each node in the hierarchy is in a simple table with a single parent relationship like so and the path being a string of ID like /<rootNodeID>/<GPNodeID>/<PNodeID>/<LeadNodeID> and that you intend to store the settings in a property bag arrangement rather than needing to update the DB structure when a new setting is added to the applcation logic, resulting insomething like this:

Nodes                 NodeSettings
=============         ============      Settings
NodeID   (PK) <--.--  NodeID  (FK)      =========
ParentID (FK) ---'    Name    (FK) ---> Name (PK)
FullPath              Value
OtherProp1
OtherProp2

In this arrangement you can pull all settings that have a value anywhere up the tree from a given location (with values closer like so:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 ns.value
                FROM   NodeSetting ns
                JOIN   Nodes n ON n.NodeID = ns.NodeID AND '<PathToTargetNode>' LIKE n.FullPath+'%'
                WHERE  ns.Name = s.Name
                ORDER BY LEN(n.FullPath) DESC
               )
FROM   Settings s

The use of a sub-query like this my not be efficient enough if you have many settings but whould be fine otherwise. Of more concern is using LIKE that way: it will not be able to properly use any index on Nodes.FullPath so an index scan (or worse, a table scan) could be performed for each call of the sub-query. Off the top of my head I can't think of a way to significantly optimise this as a single query without adding new structure to your storage of the relationships between nodes such as keeping map between ascendants and descendants like:

RelationshipDistance
=======================
Ascendant  (FK->NodeID)
Descendant (FK->NodeID)
Distance

(making sure to store the node's relationship with itself, as AscNode='NodeID', DescNode='NodeID', Distance=0). Then you can do:

SELECT Name  = s.Name
       Value = (SELECT TOP 1 s2.value
                FROM   RelationshipDistance r
                JOIN   Settings s2 ON n.NodeID = s.NodeID AND s2.Name = s.Name
                WHERE  r.Descendant = '<IDOfNodeYouAreLookingFor>'
                ORDER BY r.Distance DESC
               )
FROM   Setting s

The disadvantage here is needing to maintain the extra structure (through triggers or work in another layer of your code).

Of course this is assuming that you want to return the settings from a single statement. You could always define a procedure that walks up the path collecting the settings in a temporary table or table variable, adding new settings found at each step towards the root - this is likely to be more efficient than a single statement version that causes index/table scans.


Subtract time difference between two columns in a table Problem: How can I subtract the TIMEDIFF between two sensors (sensor 1 and sensor 2 in the example below) and return the the value in mili seconds?

How do I get with the resolution of milliseconds?

I am using the command line below to try to create the table with milliseconds resolution but returns error.

CREATE TABLE via1
(
    id int auto_increment primary key,
    sensor VARCHAR (10) NOT NULL,
    date timestamp(6) default current_timestamp(6)
);

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) default current_timestamp(6))' at line 1

Server version: 5.5.44-0 + deb7u1 (Debian)

If you are only looking for the difference between two rows, you can reference them by their ids.

SELECT
  TIMESTAMPDIFF(MICROSECOND, s1.date, s2.date) DIV 1000
FROM
  via1 s1
  , via1 s2
WHERE
  s1.id = 1
  AND
  s2.id = 2
;

There are a few errors in your CREATE TABLE statement:

CREATE TABLE via1 (
  id int auto_increment primary key,
  sensor varchar(10) NOT NULL,
  date timestamp(6) default current_timestamp(6)
);

You're using an old version of MySQL (5.5.44).  From the docs:

11.3.6 Fractional Seconds in Time Values - MySQL 5.6 Reference Manual

  Before MySQL 5.6.4, the instances are limited in which a fractional seconds part is permitted in temporal values. ...when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.


I really cannot see the point of using self-join in this query. It is 100% equivalent to this:

select m.*
from message m
where m.thumbs_up = 98 and m.id < 13
   or m.thumbs_up < 98
order by m.thumbs_up desc, m.id desc
-- limit N
  ;

I would expect an index on (thumbs_up, id) to be used by this.


Populate ID Field by Converting Group Name Values This is a bit tricky to explain, but here goes.  We have a System Rights database that loads system users and system rights or access levels into two different tables.  The database is already built and live with some system data populating it daily.  I have been asked to build the SSIS package to populate a few new system users/rights into this database, but a couple of these systems do not have numeric fields identifying the position rights field as an id that I can populate the systemAccessLevelID field in the database with.  I will try to outline what I have and what I need to do.

SystemUsers Table:

SystemID - Int Defined in a system table
SystemAccessLevelID - Int populated from SSIS pull from source
Name - nvarchar(50) populated from SSIS pull from source

SystemRights Table:

SystemID - Int Defined in a system table
SystemAccessLevelID - Int populated from SSIS pull from source
SystemAccessLevelDesc - nvarchar populated from SSIS pull from
source

The idea here is one source database that stores all of the different system users with their rights in those respective systems to be able to run audit reports against the database.

The Problem
As an example in Source1 there is no numeric field that can be converted to Int to identify the Access levels for the different rights in the systems.

This has to get reloaded daily for audit purposes and the SystemAccessLevelID field needs to stay the same regardless if new system access levels are created in the source system.

I debated trying to create a function to loop through the SystemAccessLevelDesc field and converting each letter to an integer through hard coding a numeric value for every letter and special character then adding it all together, but I don't really know where to start with that.  I also feel that I must be missing an existing function or process somewhere that I could use and was hoping someone could point me in the right direction.

Example Data:

SystemID, SystemAccessLevelID, SystemAccessLevelDesc
1       , %DontHave%         , SYSADMIN
1       , %DontHave%         , SYSACCESS
1       , %DontHave%         , SYSREAD

How can I convert he different SystemAccessLevelDesc to get unique numeric values for SystemAccessLevelID?  I was hoping to be able to do this with a single Select statement still which is why I was thinking the user defined function was my best route.

I would use the CHECKSUM function to determine a numeric value for each SystemAccessLevelDesc.  You could make this a persisted, computed column, such that the value is automatically calculated when data is inserted into the column.
From the CHECKSUM page on Microsoft's documentation site:

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns.
CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For this definition, null values of a specified type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead.

There is a possibility, however extremely small, that CHECKSUM can return the same value for two different inputs.  I've created a UNIQUE constraint on the table such that an insert where the checksum results in two identical values will fail.  If you have a relatively small number of values for the SystemAccessLevelDesc column, I would not expect you to experience any checksum collisions.
Sometimes an example is helpful:
USE tempdb;

CREATE TABLE dbo.SystemRights
(
    SystemID int NOT NULL
    , SystemAccessLevelID AS CHECKSUM(SystemAccessLevelDesc) PERSISTED
        CONSTRAINT UQ_SystemAccessLevelID
        UNIQUE
    , SystemAccessLevelDesc nvarchar(100) NOT NULL
    , CONSTRAINT PK_SystemRights
        PRIMARY KEY CLUSTERED
        (SystemId, SystemAccessLevelID)
);

INSERT INTO dbo.SystemRights (SystemID, SystemAccessLevelDesc)
VALUES (1, 'SYSADMIN')
    , (1, 'SYSACCESS')
    , (1, 'SYSREAD');

SELECT *
FROM dbo.SystemRights;

The results from the SELECT above:
╔══════════╦═════════════════════╦═══════════════════════╗
║ SystemID ║ SystemAccessLevelID ║ SystemAccessLevelDesc ║
╠══════════╬═════════════════════╬═══════════════════════╣
║ 1        ║ -32772874           ║ SYSACCESS             ║
║ 1        ║ 1727577266          ║ SYSADMIN              ║
║ 1        ║ 2008724622          ║ SYSREAD               ║
╚══════════╩═════════════════════╩═══════════════════════╝

If you are concerned about checksum collisions, and are not worried about space implications, you could use the HASHBYTES function to generate a 32-byte binary value instead of the int value returned by CHECKSUM:
IF OBJECT_ID(N'dbo.SystemRights', N'U') IS NOT NULL
DROP TABLE dbo.SystemRights;

CREATE TABLE dbo.SystemRights
(
    SystemID int NOT NULL
    , SystemAccessLevelID AS CONVERT(binary(32), HASHBYTES('SHA2_256', SystemAccessLevelDesc)) PERSISTED
        CONSTRAINT UQ_SystemAccessLevelID
        UNIQUE
    , SystemAccessLevelDesc nvarchar(100) NOT NULL
    , CONSTRAINT PK_SystemRights
        PRIMARY KEY CLUSTERED
        (SystemId, SystemAccessLevelID)
);

INSERT INTO dbo.SystemRights (SystemID, SystemAccessLevelDesc)
VALUES (1, 'SYSADMIN')
    , (1, 'SYSACCESS')
    , (1, 'SYSREAD');

SELECT *
FROM dbo.SystemRights;

The output:
╔══════════╦════════════════════════════════════════════════════════════════════╦═══════════════════════╗
║ SystemID ║ SystemAccessLevelID                                                ║ SystemAccessLevelDesc ║
╠══════════╬════════════════════════════════════════════════════════════════════╬═══════════════════════╣
║ 1        ║ 0x09B006A24922040C18013ED244C62245B8DEA81A0AB3829FEF2471299138BEC5 ║ SYSACCESS             ║
║ 1        ║ 0x98AC053FBB8875188E9DCB3F77FDB6245CA9A42B8BECFAA204F2FA3054DACB7A ║ SYSREAD               ║
║ 1        ║ 0xDC61780D08D34D244226A6B414FE382E138819B40362B624888D775D31505C1A ║ SYSADMIN              ║
╚══════════╩════════════════════════════════════════════════════════════════════╩═══════════════════════╝


Database design for bigger tracking table I am creating a database for a prayer tracker. There are 5 types of prayer for each day. I have a table called "Types"
id, name
1, Type 1
2, Type 2
3, Type 3
4, Type 4
5, Type 5

I want to save records for each user against each prayer type for each day. Below is my structure for tracking table.
id, type_id (FK of Types), user_id (FK of user table), date (Y-m-d)

I have 500K+ users and for each user there can be max of 5 records against each day. This will be in millions. Basically I want to create an optimized db structure, So it should be faster while doing queries.

User is login and he should see all records of him/her against each
day.
After 1 year, if there are 5 records against each day then for
500K users, it will be 912,500,000 records. Table will be much
bigger.
No. of users are also increasing day by day.

So In order to optimize the database, what will be best practice for above structure of tracking
table?

Honestly, 912 million records may seem daunting but it's nothing that regular indexing won't be able to handle, especially since your main query is to return only the current day's prayer types for the current user which is a very small amount of data. I've managed Tables with 10s of billions of rows in them and proper indexing allowed us to return small sets of data in milliseconds runtime, on very modest hardware. And I'm sure Tables in the 100s of billions would've been fine as well. Note this was using SQL Server instead of MySQL but the principals are the same on how indexing works in regards to storing and fetching data.
At your current rate (no user growth) for example, you wouldn't hit 100 billion rows for about 100 years. If you're able to implement Akina's suggestion then you'll be able to cut the amount of data down by 80% too. So that 100 billion rows now becomes only 20 billion in 100 years. (In other words, it withstands the test of time. :) If you're able to archive data after X number of years you can also alleviate the data load of your Table.
Finally in regards to indexing I think to support a query that will select all prayer type records for a given user only on a given day, you should create the index on user_id, date (and depending on which MySQL engine you're using include type_id if it's not by default) for best indexing.


Find missing entries across several tables by SQL call I was already browsing around here and on other platforms but could not find a match to my request. My knowledge of SQL is quite limited and my IT department is not willing to help unfortunately.
I have 7 different tables (‚Sys1, Sys2…Sys7‘), with columns 'name' and 'date'.
An 8. table (‚summary‘) also has columns ‚name‘, ‚date‘ and additionally ‚sys‘. ‚sys‘ can have values ‚Sys1‘ to ‚Sys7‘. I want to compare if entries in tables ‚Sys1-7‘ also appear in table ‚summary‘ and vice versa. The reference for it is values in the column ‚sys‘. 'Name' and 'date' are the same in both tables. However, it is possible that name-date-combinations appear twice in one table, it is also possible that the same name-date-combination appears in two or more ‚Sys‘ tables.
I am finally only interested to know, if there are entries that have NOT been made with name and date in both tables (Sys1-7 AND summary).
An example:
Table ‚Sys1‘:
| Name          | date     |
|---------------|----------|
|Max Mustermann |01.12.2020|

Table ‘Sys2’:
|Name           | date       |
|---------------|------------|
|Hans Wurst     | 01.12.2020 |
|Max Mustermann | 01.12.2020 |
|Donald Trump   | 01.12.2020 |

Table ‘summary’:
| Name          | date       | sys  |
|---------------|------------|------|
|Max Mustermann | 01.12.2020 | Sys1 |
|Hans Wurst     | 01.12.2020 | Sys2 |
|Max Mustermann | 01.12.2020 | Sys2 |
|Joe Biden      | 01.12.2020 | Sys1 |

So here I want to see that Donald Trump did not sign into table 'summary' and Joe Biden did not sign in table 'Sys1'.
Thanks for any help or link to answered questions I might have missed

SELECT *
FROM SysN sy
WHERE NOT EXISTS ( SELECT NULL
                   FROM summary su
                   WHERE (su.name, su.date, su.sys) = (sy.name, sy.date, 'SysN') )

Execute 7 separate queries or combine them via UNION ALL into one query.


Fill Factor for OLTP database I am setting up a new OLTP database and expect to write to the database about 70% of the time and read from it around 30% of the time.

Given this criteria, I'm wondering what would be good practice in setting the default fill factor for this database as well as other best practices I may want to look into applying given this criteria.

Any input would be greatly appreciated.

Thanks

The answer is, unfortunately, it depends. Your database might be 70/30 write/read, but every table in the database won't be 70/30. Some tables, such as reference tables and lookups, may only be written to once during initial deployment then very rarely after that. Those tables should have a high fill factor.

You transactional tables that have the 70/30 write/read split may be candidates for lower fill factors, but again, it depends on the index. For example, filtered indexes that may actually only have a 50/50 read/write ratio may be okay with a higher fill factor, but your clustered indexes may want a lower fill factor to prevent excessive page splits.

You need to analyse your tables and indexes individually and make the call based on the expected read/write split for that specific index rather than trying to set it for your database as a whole.

Another consideration is your number of transactions per minute/hour/day. If your OLTP system has to handle millions of transactions a day, then fill factor is an important design consideration, if your system doesn't need to scale beyond a few thousand transactions per day, then fill factor is less important due to the low volumes of data ingress.

If you're architecting this database from scratch, then take the time to correctly identify the required fill factor per index, and ensure any new indexes\tables have this analysis performed as well, rather than creating them with default fill factors.

EDIT: As per Erik Darling's comment, another thing to be aware of is that your fill factor only applies when you create, rebuild or reorganise an index. There is a great article on Brent Ozar's site about fill factor which should help.


Select column from ALL tables I have a sqlite3 database with many tables which have same columns. It's a dictionary database, and each table name is starting word letter.

I would like to list single column from all tables in database, but couldn't find a way to do so.

From sqlite3 CLI client, something like:

sqlite3 -line my_db.db 'select my_column from *'

Of course the above does not work.

You're looking for UNION and UNION ALL.

select my_column from table1
union all
select my_column from table2
union all
select my_column from ...

But needing to select one column from every table is likely the sign of a significant design error.


How to return a single composite type from a plpgsql function without repeating the composite type's parts explicitly as OUT parameters? I've hunted online and through documentation, but the much more common use case appears to be returning a SETOF some particular row type, not just a single row.

The use case below is trivial for illustration, as I am just trying to determine if this is even possible in plpgsql, and understand the syntax to use for it if it is.  (Just like the Postgresql documentation contains examples of functions to return a static set of fixed values, the point here is not the use case but the syntax.)

With this table:

create table my_table (f1 int primary key, f2 text, f3 real, f4 text, f5 bool);

The following function can be created:

create function my_func1(f1_to_look_up int) returns my_table
language sql
as $$ select * from my_table where f1 = f1_to_look_up $$;

Also, this can be done in plpgsql like so, with explicit out parameters (and with f1 specified explicitly in the where clause to avoid ambiguity):

create function my_func2(f1_to_look_up int, out f1 int, out f2 text, out f3 real, out f4 text, out f5 bool)
language plpgsql
as $$
begin
  select * into strict f1, f2, f3, f4, f5
    from my_table where my_table.f1 = f1_to_look_up;
  return;
end
$$;

Is there a way to accomplish the same thing in plpgsql, without needing to repeat the definition of my_table in order to list all the OUT parameters?

You can return a row type by using an expression with that type.
here it's effectively select my_table from my_table

create function my_func4(f1_to_look_up int) returns my_table language plpgsql
as $$
    begin
        return my_table from my_table where f1 = f1_to_look_up;
    end;
$$;

or via a variable

create or replace function my_func4(f1_to_look_up int) returns my_table language plpgsql
as $$
    declare
        retval my_table;
    begin
        select into retval * from my_table where f1 = f1_to_look_up;
        return retval;
    end;
$$;

You can also use a row constructor, which would be especially useful if the query is more complicated and you need to return values taken from more than one table joined together:

create function my_func5(f1_to_look_up integer)
 returns my_table
 language plpgsql
as $function$
begin
  return row(t.*) from my_table as t where f1 = f1_to_look_up;
end
$function$
;

The key is that where the plpgsql syntax documentation indicates that you can write return expression, "expression" can be everything that would ordinarily follow "select" in a normal SQL query.


Partial mysqlbinlog on latest binlog file I'm trying to do a partial mysqlbinlog on the latest binary log file at a certain position therein (trying to extract just one query).

mysqlbinlog --start-position=1234 --stop-position=1234 /var/lib/mysql/mysql-bin.000021

The output contains:

  Warning: this binlog is either in use or was not closed properly.

How do I force MySQL to start logging in the next binlog file (i.e. mysql-bin.000022) and release the original file so I can use mysqlbinlog it?

Is there another solution to this problem?

You can use FLUSH LOGS to rotate the binlog before it reaches max size.


raise an error in sql server severity higher than 18 I can easily raise user defined errors where severity is not higher than 18. When is higher, I get this error

  Error severity levels greater than 18 can only be specified by members of the
  sysadmin role, using the WITH LOG option.

I have access to sysadmin role, so can someone specify the syntax for raising errors WITH LOG option.
Thanks.

WITH is an optional clause that goes at the end of the RAISERROR. A few examples:

RAISERROR('Oops', 10, 1)
RAISERROR('Oops', 10, 1) WITH NOWAIT
RAISERROR('Oops', 10, 1) WITH SETERROR

RAISERROR('Oops', 19, 1) -- Fails with "Error severity levels greater than 18..."
RAISERROR('Oops', 19, 1) WITH LOG -- OK

Remember that severities of 20 and above will terminate the connection (won't even jump to a CATCH).


SQL Server database has corruption, but displays no errors We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and execute it there. Then when I tried to select from the table, it would just keep executing. It is an extremely simple table: two columns, both int, no rows at the moment. We were also unable to delete the table.

We suspect there is some type of corruption, but when we run error checking on tables or database it says there are no problems. I've included our version below, just a SQL Server 2008 R2 Express version. The only errors we get are when we run dbcc checkdb. But it only shows an error on the last line. It shows no errors on any individual tables.

We restarted the SQL Server and then we were able to delete the table. We can select from every table, and everything is now working normal.  But we still have that error at the end of checkdb.

Not sure what to do, it's a little weird. Everything is working, but we are a little afraid something might happen.  We have backups and everything, just trying to fix this if at all possible.

Last lines of checkdb:

  CHECKDB found 0 allocation errors and 0 consistency errors in database '....'.

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

  Msg 0, Level 20, State 0, Line 0
  A severe error occurred on the current command.  The results, if any, should be discarded.

When we run checkdb on another db:

  CHECKDB found 0 allocation errors and 0 consistency errors in database '....' .

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Our SQL Server database version:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
Jun 17 2011 00:57:23
Copyright (c) Microsoft Corporation
Express Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor)

This might help someone with a similar problem.  There was nothing wrong with the database, we needed to install an update.  A service pack I believe.  Then everything was fine.


"Wrong affix file formate for flag" while loading dictionaries from LibreOffice in PostgreSQL 9.3 Trying to get good TEXT SEARCH results out of PostgreSQL in English, German and Dutch made that I downloaded proofing tools of LibreOffice (tried of version 4.3 and 3.3) and iSpell. I placed the files in the "tsearch_data" dir and renamed them so PostgreSQL would find them. I now have:

en_us.dict, en_us.affix, nl_nl.dict, nl_nl.affix, de_de_frami.dict, de_de_frami.affix

All of them give the same sort of errors while creating a dictionary in PostgreSQL 9.3 like so:

CREATE TEXT SEARCH DICTIONARY test_ispell (template = ispell, Dictfile = en_us, affFile = en_us, stopwords = english);

ERROR:  wrong affix file format for flag
CONTEXT:  line 2428 of configuration file "/usr/share/postgresql/9.3/tsearch_data/en_us.affix": "COMPOUNDMIN 1"

ERROR:  wrong affix file format for flag
CONTEXT:  line 2533 of configuration file "/usr/share/postgresql/9.3/tsearch_data/en_us.affix": "SFX 123 N 1"

ERROR:  invalid byte sequence for encoding "UTF8": 0xc4 0x62
CONTEXT:  line 18 of configuration file "/usr/share/postgresql/9.3/tsearch_data/de_de_frami.dict"

ERROR:  wrong affix file format for flag
CONTEXT:  line 604 of configuration file "/usr/share/postgresql/9.3/tsearch_data/dutch.affix": "SFX Na N 1"

It got pointed out to me that the first error appears to be a wrongful one as can be found in its man page: http://linux.die.net/man/4/hunspell

COMPOUNDMIN num
   Minimum length of words used for compounding.  Default value is 3 letters.

Update: COMPOUND Hunspell functions can can't work in PostgreSQL see: http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY

The SFX syntax, is beyond me. The invalid byte sequence must be fixable as well I presume if I somehow manage to tell PostgreSQL the file type, yet I have no clue how to do that.

Anyone got any idea how to load these files?

Update:

The Dutch dictionaries provided with Debian still produce errors this is part of the Myspell package, and I can't get it to work. Anyone any success with that?

For now I will try and file a bug report to Debian since it is a function that should be working.

So I found the answer for Debian at least.
Hunspell is the successor of Myspell, and they ship with your distribution (in my case Debian).  These are the dictionaries you can use.  You can install these by executing

apt-get install hunspell-en-us

These dictionaries are installed in /usr/share/hunspell or /usr/share/myspell. The PostgreSQL dictionaries that are automatically generate out of these file when installing through apt-get are located in /var/cache/postgresql/dicts. Furthermore apt-get automatically creates links to the files generated in you /usr/share/postgresql/VERSION/tsearch_data dir. In short you are ready to use them.

If you want to manually install hunspell or myspell dictionaries you can place them in the /usr/share/hunspell or /usr/share/myspell dirs. And execute

pg_updatedicts

This will generate PostgreSQL compatible dictionaries in

/var/cache/postgresql/dicts

and you will have to link them yourself /usr/share/postgresql/VERSION/tsearch_data

ln –s /var/cache/postgresql/dicts/de_de.affix           \
  /usr/share/postgesql/VERSION/tsearch_data/            \

ln –s /var/cache/postgresql/dicts/de_de.dict            \
  /usr/share/postgesql/VERSION/tsearch_data/            \


MariaDB Galera cluster node won't start I have configured a MariaDB Galera cluster with three nodes. The master node and one slave-master node is up and running, but the third node just won't run. The system specs and cluster config are the same on all three nodes (except changing the wsrep_node_address and the wsrep_node_name on each node).

Here is the config for the nodes (wsrep_node_address and the wsrep_node_name are unique on each node):

[galera]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0

datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2

wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='192.168.9.113'
wsrep_node_name='george-db4'
wsrep_sst_method=xtrabackup
wsrep_sst_auth=username:password

It seems like MariaDb does not like the config

wsrep_cluster_address="gcomm://192.168.1.111,192.168.1.112,192.168.1.113"

When I comment it out MariaDB restarts just fine. When I uncomment it, it just displays this message:

Starting MySQL................................... ERROR!
ERROR! Failed to restart server.

When I change the wsrep_cluster_address to just

wsrep_cluster_address="gcomm://

MariaDb runs with no problem. So I am not sure why it is having problems with assigning the IP address (even if it works fine on the other two nodes). I have tried emptying the galera.cache file, I have tried re-installing the cluster, I have tried rebooting the machine etc. and nothing works so far. I even truncated the log file /var/lib/mysql/db1.err with the command truncate logfile --size 0 (now I cannot figure out how to get my log file back with space as I realized I would need the log file).

Please post the tail end of your MySQL error log.  Usually, it will be in /var/log/syslog or /var/log/mysql/mysql.log (depending on your setup).

Without having some details, it is hard to say, but it almost sounds like you have network connectivity problems.  Please ensure that the necessary ports are open on the third node to properly allow for all Galera traffic.


What kind of a lock does IN keyword takes in SQL If I have a query in which I have used IN keyword than what kind of lock will be taken by IN keyword.

Query is :

UPDATE table1 SET b_id where id in (SELECT id from table1 where id IS NULL )

I wanted to know the type of lock taken by the query when IN keyword is used.

See File Locking And Concurrency In SQLite Version 3.

There is a single lock for the entire database file.

When the transaction reads the database for the first time, it takes a read lock (a SHARED lock). This happens not when the IN is executed, and not before that when the subquery is executed, but even before that when the database reads the system table to get information about table1.

When the transaction writes to the database for the first time (i.e., when at least one row is actually UPDATEd), it takes a write lock (a RESERVED lock, and later PENDING/EXCLUSIVE when the cached changes are actually written at the end of the transaction).


MS SQL full datetime to only month and date i am using ms sql server, i have date filed with value '01-Jan-2016 12:12:03PM'
from this format i need something like 'January-2016'
how can i do this?
with this i need to count something by month.

MY code following

select
DATENAME(MONTH,p._DATE) AS AC_MONTH_,
COUNT(CASE WHEN p.PC=266 THEN 1 END) AS PRE_DATE,
COUNT(CASE WHEN p.PC=1325 THEN 1 END) PP_DATE
from true_all p
where  p._DATE>'01-Jun-2016'
group by DATENAME(MONTH,p._DATE)
order by DATENAME(MONTH,p._DATE) desc

For best performance, use date/time arithmetic to truncate a datetime column to year and month before using it for grouping. There are at least two distinct approaches to that.

Approach 1

The first is a well-known date/time truncation method applicable to various units, not just months. It consists in finding the difference between your datetime value and another, fixed, datetime value in the same same units that you want to perform the truncation on and adding that difference to the fixed datetime value. Very often a 0 is used as an easily memorable fixed value (it represents the date of 1900-01-01 00:00):

DATEADD(unit, DATEDIFF(unit, 0, yourDateTimeValue), 0)

You want to do the truncation on months – so, in your case it would be

DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)

The result will be the datetime value representing the midnight of the first of the same month as p._DATE. For 2016-11-16 14:32:58.547, for instance, you will get 2016-11-01 00:00:00.000.

Approach 2

Another method, primarily applicable to dates but easily adaptable for datetimes, is to take the date's day value (for instance, 16 for today), decrease it by one (15) and subtract that number of days from the date. As you can understand, that will also give you the first of the same month. In your case the expression would be:

 DATEADD(DAY, 1 - DAY(p._DATE), p._DATE)

That assumes p._DATE is the date type. If it is datetime, just cast the second occurrence to date:

 DATEADD(DAY, 1 - DAY(P._DATE), CAST(P._DATE AS date))

Usage

With both approaches the expression is supposed to be used as the grouping criterion. Using the first one as an example, your query could be rewritten like this:

SELECT
  AC_MONTH_ = DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)
FROM
  dbo.true_all AS p
WHERE
  p._DATE > '01-Jun-2016'
GROUP BY
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
ORDER BY
  AC_MONTH_ DESC
;

Notes on formatting

Of course, if you want the date values in the output to look like Month-YYYY, you will need to format them accordingly. Conventionally, formatting should be done at the presentation level, not in the database, so that the application could take advantage of the client-side locale settings.

If you insist on doing it in Transact-SQL, though, you can certainly have some options. Just make sure you apply the formatting to the already transformed value (the expression) rather than the source column. Use of the DATENAME function is one of the options:

  AC_MONTH_ = DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0))
            + '-'
            + DATENAME(YEAR,  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)),

In order to suppress repetition of the same long expression, you can use CROSS APPLY to calculate the month date once and reference it both in GROUP BY and in SELECT, like this:

SELECT
  AC_MONTH_ = DATENAME(MONTH, x.MONTH_DATE) + '-' + DATENAME(MONTH, x.MONTH_DATE),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)
FROM
  dbo.true_all AS p
  CROSS APPLY
  (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
  ) AS x (MONTH_DATE)
WHERE
  p._DATE > '01-Jun-2016'
GROUP BY
  x.MONTH_DATE
ORDER BY
  x.MONTH_DATE DESC
;

Note the ORDER BY x.MONTH_DATE DESC in this case. Now that the AC_MONTH_ column holds formatted values, it can no longer be used for consistent sorting. But the grouping criterion can, so the above query is using it in ORDER BY.


What Oracle SQL syntax do I need to find transactions that occurred during time intervals of a day for a period of a year? I am looking at a transactional-level table for which I need to extract all transactions which occurred between certain hours (between 15:00 and 23:59) of a day for the past year.  How can I do this using Oracle SQL syntax?

Simply add a WHERE-condition like this:

WHERE datecol BETWEEN DATE '2014-01-01' AND DATE '2014-12-31'
  AND EXTRACT(HOUR FROM CAST(datecol AS TIMESTAMP(0))) BETWEEN 15 AND 23

To dynamically define the previous year:

WHERE datecol >= ADD_MONTHS(TRUNC(CURRENT_DATE, 'year'), -12) -- jan 1st previous year
  AND datecol < TRUNC(CURRENT_DATE, 'year')                   -- jan 1st current year
  AND EXTRACT(HOUR FROM CAST(datecol AS TIMESTAMP(0))) BETWEEN 15 AND 23


How to get server event notification I'm just getting into sql-server, so forgive me if this is a basic question. I'm building an application using .NET 4.0 and VS 2010 and ms-server2008.

For my application I need to detect the server response, suppose: in db-server I need to detect which table insert/update/delete records, and which is the affected record is?

Is there any mechanism or technique to detect the server response.

If have any questions please ask.

Your options are

to use stored procedures for data access and write log of changes,
or to use triggers on specified tables to write log of changes

and after it you have periodically poll the log tables to reflect to the tables changes

See related answers:

https://stackoverflow.com/questions/8998545/notifications-from-sqlserver-to-java/8998579#8998579

https://stackoverflow.com/questions/8895595/sql-server-track-table-inserts/8895683#8895683


drop database command is cancelled ^C I want to know what happens when a drop database command is canceled using ^C.

Does it partially removes the data or completely?

Let's try again, but with the correct statement ;)

When you cancel a query in psql, what it does internally is to send a pg_cancel_backend() to the server. This returns directly, but the statement might still be running on the server.

When a query is run, there are a few times it checks if it safely can cancel the statement, and in that case do so. This applies to a drop database statement as well.

There is a short time between the database files are removed from disk and the statement is committed where things can get interesting. It is not possible to cancel the query during that phase, but if there is a server crash then there is a risk that the database still exists in the meta tables, but the files are removed from disk.

Update, clarification of when it can be cancelled

When the server receives a cancel request, it marks a flag that the statement should be cancelled, and the statement checks that flag once in a while during execution. To explain what happens when we cancel the drop database we need to understand what it does:

Statement is received from the server.
Implicit transaction is started.
Lock is taken on the database.
Checks are made to make sure that the database is not being used.
The database is removed from the meta tables.
A checkpoint is written in the WAL.
The database files are removed.
The transaction is committed.

At any point up until #6 the statement can be cancelled (the final check of the flag is done at #6)


Are there any other ways to get results from Two tables without using Sub Query I have a Customer table

+--------+---------+
| Id     |  Name   |
+--------+---------+
| 1      |   A     |
| 2      |   b     |
| 3      |   c     |
| 4      |   d     |
| 5      |   3     |
| 6      |   f     |
| 7      |   g     |
+--------+---------+

and an order table

+-----+------+--------------------------+
| ID  | C_Id | OrderDate                |
+-----+------+--------------------------+
| 1   | 1    |  2017-05-12 00:00:00.000 |
| 2   | 2    |  2017-12-12 00:00:00.000 |
| 3   | 3    |  2017-11-12 00:00:00.000 |
| 4   | 4    |  2017-12-12 00:00:00.000 |
| 5   | 1    |  2017-12-12 00:00:00.000 |
| 6   | 2    |  2017-12-12 00:00:00.000 |
| 7   | 3    |  2017-12-12 00:00:00.000 |
| 8   | 4    |  2017-11-12 00:00:00.000 |
| 9   | 2    |  2017-06-12 00:00:00.000 |
| 10  | 3    |  2017-07-12 00:00:00.000 |
+-----+------+--------------------------+

I need the result of orders with customers who did not buy in last month. That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I have this query which returns the result perfectly.

SELECT C_ID , MONTH(OrderDate) from [Order] WHERE MONTH(OrderDate) <> MONTH(GETDATE()) - 1 AND C_ID NOT IN
(SELECT C_ID FROM [Order] WHERE MONTH(OrderDate) = MONTH(GETDATE()) - 1)

Can anyone help me to write this query without using subquery

For better clarity, assume the Orders table contains current year orders only and I need to exclude the customers from the result(get all orders for current year) if they had any purchase in November. Which means if the customer John has an order in April, June and November the result(orders with customer name) should not include the customer John and all his orders as he made a purchase in November..

For learning purpose I need to know how can we achieve the above in other ways without using sub query.

I need the result of the customers who did not buy in last month.

  That is from the order table Customer 3 and 4 have purchased in last
  month(November). The result should not include customer 3 and 4 even
  they had purchase in earlier months.

I am a bit confused with your question.

If you want to see customers who did not purchase last month (in this case, November) Then below query should be able to handle it.

SELECT DISTINCT
    c.id,
    c.name
FROM #Customer c
LEFT JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1
WHERE o.c_id IS NULL

If you want to see customers who only purchased last month (in this case, November) Then below query should be able to handle it.

 SELECT DISTINCT
        c.id,
        c.name
    FROM #Customer c
    INNER JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1


tuning feed item's table query with multiple condition I have a query like this:

SELECT *
FROM `rss_item`
WHERE `feed_id` = 1 and `timestamp` < 1350698635
ORDER BY `rss_item`.`timestamp` ASC

MySQL: 5.1; table engine: innodb

Explain result:

id  |  select_type  |  table     |  type   |  possible_keys  |  key        |  key_len |  ref     |  rows    |  Extra
1   |  SIMPLE       |  rss_item  |  range  |  timestamp      |  timestamp  |  4       |  NULL    |  133534  |  Using where

Table schema:

CREATE TABLE `rss_item2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(1000) NOT NULL,
    `description` longtext NOT NULL,
    `url` varchar(200) NOT NULL,
    `url_crc` bigint(20) NOT NULL,
    `date` datetime NOT NULL,
    `timestamp` int(11) NOT NULL,
    `feed_id` int(11) NOT NULL,
    `image` varchar(250) NOT NULL,
    `goto` int(11) NOT NULL,
    `likes` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `timestamp` (`timestamp`,`feed_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2425930 DEFAULT CHARSET=utf8

This query with 400,000 rows takes 4 seconds. I added a composite index on feed_id and timestamp fields. How can I tune this query?

I dont know about feed_id but your timestamp column by definition have probably many differents values. I will advise you to try to create a simple index on timestamp column.

Do you have "disparate" values too on feed_id column?

For me you should take your attention on the timestamp column (WHERE + ORDER BY). Maybe it'll not a index problem, maybe your MySQL instance have some difficulties to sort the result (bad temp table management) so you can try without ORDER BY clause to see its impact.

Let me know.

Max.


SQLite - Remove duplicates across multiple columns I have the following table:

C1 | C2 | C3
-------------
A | X | 1
A | Y | 2
B | X | 3
B | Y | 4

I want to deduplicate across columns 1 and 2, and select the max from column 3. In case, since rows 1 and 2 both have "A" in column 1, those are a duplicate. Since rows 3 and 4 both have "B" in column 1, those are a duplicate. Since rows 1 and 3 both have "X" in column 2, those are a duplicate. Finally, since rows 2 and 4 both have "Y" in column 2, those are a duplicate. Hence the first four rows would be considered a duplicate, and the result should return row 4, since that contains the maximum in column 3. I cannot figure out how to deduplicate across multiple columns. Any advice would be appreciated.

WITH RECURSIVE cte AS
( SELECT c1, c2, c3, 1 level
  FROM test
UNION ALL
  SELECT cte.c1, t2.c2, GREATEST(cte.c3, t2.c3), level+1
  FROM cte
  JOIN test t1 ON cte.c2 = t1.c2
  JOIN test t2 ON t1.c1 = t2.c1
  WHERE t2.c3 != cte.c3
    AND level < ( SELECT MAX(c3)
                  FROM test )
UNION ALL
  SELECT t2.c1, cte.c2, GREATEST(cte.c3, t2.c3), level+1
  FROM cte
  JOIN test t1 ON cte.c1 = t1.c1
  JOIN test t2 ON t1.c2 = t2.c2
  WHERE t2.c3 != cte.c3
    AND level < ( SELECT MAX(c3)
                  FROM test )
)
SELECT cte.c1, cte.c2, MAX(cte.c3) c3
FROM cte
JOIN test ON (cte.c1, cte.c2) = (test.c1, test.c2)
GROUP BY c1, c2
HAVING c3 = MAX(test.c3);

fiddle


MySQL Schema Design: One Possible Values Between Multiple Columns I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables:

CREATE TABLE `mammals` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `species_name` VARCHAR(255) NOT NULL,
  `fur_color` VARCHAR(255) NOT NULL
);

CREATE TABLE `birds` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `species_name` VARCHAR(255) NOT NULL,
  `wingspan` DECIMAL(11,8) NOT NULL DEFAULT 0
);

The tables are separate because a large percentage of the combined columns between the two tables are unique to one specific derivative (e.g. fur_color and wingspan in the example above). The different derivatives also have very different relationships to other tables.

However in some other tables we might want to reference a specific instance of one of those entities:

CREATE TABLE `pets` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `owner_name` VARCHAR(255) NOT NULL,
  `mammal_id` INT(11) UNSIGNED ZEROFILL NOT NULL,
  `bird_id` INT(11) UNSIGNED ZEROFILL NOT NULL
);

The above example would allow us to specify what species of animal each pet is, but there's a problem: the pets schema allows for values to be entered in both the mammal_id and bird_id column while in reality I only want to allow input in one or the other.

What is the best solution to this issue? I was considering a check constraint but according to the MySQL Reference Manual:

  The CHECK clause is parsed but ignored by all storage engines.

Should I simply replicate the constraint as a trigger that throws an error, or is there a better solution that I'm not thinking of?

id, species_name, dominant_color, owner_id,
type ENUM('mammal', 'bird', 'reptile', 'arachnid', ...),
other_characteristics TEXT

Where other_characteristics has JSON in it.  You can add whatever you want there.

Other tips:  Do not blindly use (255); use some reasonable limit.  Once wingspan is in the JSON, you can get rid of the ridiculously precise DECIMAL(11,8).


mysqld node not connecting to cluster I'm trying to set up a basic NDB cluster, the data nodes register correctly but the mysqld API node doesn't accept

Connected to Management Server at: 174.138.80.168:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @104.131.16.252  (mysql-5.6.37 ndb-7.4.16, Nodegroup: 0, *)
id=3    @174.138.80.126  (mysql-5.6.37 ndb-7.4.16, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @174.138.80.168  (mysql-5.6.37 ndb-7.4.16)

[mysqld(API)]   1 node(s)
id=4 (not connected, accepting connect from manager.mysql.cluster)

Manager Node's my.cnf

[ndb_mgmd]
config-dir=/usr/local/mysql/mysql-cluster
config-file=/usr/local/mysql/mysql-cluster/config.ini

# MySQL Config
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql

# Run ndb storage engine
ndbcluster
# IP address management node
ndb-connectstring=174.138.80.168

# MySQL Pid and Log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Management Node's /var/lib/mysql-cluster/config.ini file

[ndb_mgmd]
# Management process options:
hostname=manager.mysql.cluster  # Hostname of the manager
[ndbd]
hostname=node1.mysql.cluster    # Hostname of the first data node
datadir=/var/lib/mysql-cluster   # Remote directory for the data files

[ndbd]
hostname=node2.mysql.cluster    # Hostname of the second data node
datadir=/var/lib/mysql-cluster   # Remote directory for the data files

[mysqld]
# SQL node options:
hostname=manager.mysql.cluster

Each of the data node's /etc/my.cnf files:

# MySQL Config
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql

# Run ndb storage engine
ndbcluster
# IP address management node
ndb-connectstring=174.138.80.168

[mysql_cluster]
# IP address management node
ndb-connectstring=174.138.80.168

# MySQL Pid and Log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Thanks

I guess I had that issue before
double check whether mysql has been started or not
run following command and see

[root@aba ~]# systemctl status mysqld.service

active status should be

 Active: active (running) since Wed 2018-02-7 12:50:46

before set up cluster


What is the optimal set of database maintenance tasks for high volume of transactions I have a database that has a high volume of data being inserted and deleted each week.

Every week there are approximately one million rows of data being deleted and inserted. Each month the base table on which the data gets inserted grows by around a million rows.

I think this large number of transactions are causing the log file to grow larger and get fragmented.

I am afraid the performance may get worse each month.

What is the best way to maintain and manage such a database?

If anyone can explain or provide a link that deals with such a scenario that would be great.

As far as the transaction log goes, I highly recommend you read this article: SQL Server Central: Managing Transaction Logs.  The biggest takeaway you should get from this is that you need to plan your transaction log backups according to:

The maximum amount of data that can be lost in the case of a disaster
The size to which the log can grow.

The above was quoted directly from the above article on SSC.

What #1 is indicating is that if the drive that your log file exists on is corrupted and you can't salvage your log file (which means you can't back up the tail of the log for the minimization of data loss), then you will have data loss since your last transaction log backup.

And #2 being obvious, the transaction log in full recovery mode will grow until backed up.


How to tell whether mysql was built with libedit or readline? How to tell whether mysql was built with libedit or readline?

I'm trying to configure the mysql client tool to use Ctrl-R for searching command history but not sure which input library my installation is using.

If on Linux you can just use ldd to check which libraries it's dynamically linked against:

phil@ironforge:~$  ldd `which mysql`
        linux-vdso.so.1 =>  (0x00007ffd5add5000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007eff0a44e000)
        libreadline.so.5 => /lib/x86_64-linux-gnu/libreadline.so.5 (0x00007eff0a210000)
        libncurses.so.5 => /lib/x86_64-linux-gnu/libncurses.so.5 (0x00007eff09fee000)
        libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007eff09dc4000)
        libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007eff09baa000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007eff099a6000)
        libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007eff09623000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007eff0931a000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007eff08f50000)
        /lib64/ld-linux-x86-64.so.2 (0x000055960d9f2000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007eff08d39000)
phil@ironforge:~$

In this example on my host, it's linked against libreadline.

It's also in the --help output:

phil@ironforge:~$ mysql --help | grep readline
mysql  Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
phil@ironforge:~$