ERROR: invalid input syntax for type money While importing I get this error:

pg_restore: [archiver (db)] COPY failed for table "transaction_details":
ERROR:  invalid input syntax for type money: "$0.00"

restore completed, but the transaction_details table is empty.
This is Heroku's PostgreSQL Dump database.

PostgreSQL version on Heroku is 9.3.15 and the same on my system

The command I used:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U root -d database 577b86e3-8d96-4410-9b36-dd0f78cfe32f

Could anyone suggest a solution?

The manual about the money type has advice for your case exactly:

  Since the output of this data type is locale-sensitive, it might not
  work to load money data into a database that has a different setting
  of lc_monetary. To avoid problems, before restoring a dump into a new
  database make sure lc_monetary has the same or equivalent value as in
  the database that was dumped.

To check:

SHOW lc_monetary;

The manual on lc_monetary.

The example value in the error message ('$0.00') would work with the default setting (among others):

lc_monetary = 'C';

You can set it in postgresql.conf, reload and try again ...

This peculiar behavior is one of the reasons why the money type is unpopular.


Removing/ Undoing Database addition to Alwayson Groups I am about to create a new database for an AAG group, and I want to know in case of a fall back plan where the data synchronization fails, would removing the database from the AAG and then dropping it from the primary would suffice? Please advice

Thanks!

Yes it is possible to do so. However, there are some possible issues you have to consider or expect.

You will have at some down time in you application or web (whatever accessing the database).
You need to change the connection string. AOA group connection string uses the listener or AoAgroup. When you switch back, you need to use regular connection string FQDN of your server or IP of your address.
You might also need to remove the server from the cluster.
When you failover databases under your secondary server remain under no recovery state. Databases in your primary server will be online but sometimes (especially if you have really big database) if there is some transaction log left and during the failover, your database will hang up under suspect mode.


Migration of records to the table with different column names I have two databases, and I need to move data from one to another. The catch is they have a table with different set of columns. Here is how I need to rename columns in migration:

NULL -> ID (should be generated as I add records normally)
NULL -> category_id (should be fixed, e.g. 3)
date_art -> created_at
title_art -> title
short_art -> promo
full_art -> content

Is it possible to accomplish by a single query?

The SQL would be like:

insert into tab1 (category_id, created_at, title, promo, content)
as
select 3, date_art, title_art, short_art, full_art
from   tab2

The ID column will get it's value during the insert.


How to achieve this sql server replication scenario? I want to achieve below illustrated scenario via sql server replication.

Client A and Client B are client databases containing same article Table A. This article needs to be published by Client A and Client B whose changes need to be merged at the server (direction of arrows represent data flow).

Server is the destination database where article Table A should contain the changes made by both Client A and Client B. The trio consisting of Server, Client A and Client B are running Sql Server 2008 R2.

This is what I've tried in vain. Made Client A and Client B publishers and initially subscribed Server to Client A (went well). Next I tried subscribing Server to Client B (went haywire, found this msdn post where I figured out the reason behind the failure )

I still need to achieve the above diagrammed scenario. Is this even possible? If yes, any ideas how?

Thanks for your time.

Yes, this type of topology is known as the Central Subscriber Model.  To make this work you should horizontally partition your published data, utilize static row filters, and set the Action if name is in use article property to Delete data. If article has a row filter, delete only data that matches the filter.

The MSDN article does not detail the implementation very well but I have covered it in Central Subscriber Model Explained.


ERROR 1067 (42000): Invalid default value for 'uploadedDate' Hi everyone i am getting an error trying to run the below query ERROR 1067 (42000): Invalid default value for 'uploadedDate'

CREATE TABLE `filenew` (
  `id` int(11) NOT NULL AUTO_INCREMENT  /* MYSQL_TYPE_LONG */,
  `originalFilename` varchar(255) CHARACTER SET utf8 NOT NULL  /* MYSQL_TYPE_VARCHAR */,
  `shortUrl` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileType` varchar(150) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `extension` varchar(10) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileSize` bigint(15) DEFAULT NULL  /* MYSQL_TYPE_LONGLONG */,
  `localFilePath` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `userId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `uploadedUserId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `totalDownload` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `uploadedIP` varchar(45) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `uploadedDate` timestamp DEFAULT NULL  /* MYSQL_TYPE_TIMESTAMP2 */,
  `statusId` int(2) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `status` enum('active','trash','deleted') DEFAULT 'active'  /* MYSQL_TYPE_ENUM */,
  `visits` int(11) DEFAULT '0'  /* MYSQL_TYPE_LONG */,
  `lastAccessed` timestamp DEFAULT NULL  /* MYSQL_TYPE_TIMESTAMP2 */,
  `deleteHash` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `folderId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `serverId` int(11) DEFAULT '1'  /* MYSQL_TYPE_LONG */,
  `adminNotes` text CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_BLOB */,
  `accessPassword` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `fileHash` varchar(32) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `minUserLevel` int(3) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `linkedFileId` int(11) DEFAULT NULL  /* MYSQL_TYPE_LONG */,
  `keywords` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `description` varchar(255) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `isPublic` int(1) NOT NULL DEFAULT '1'  /* MYSQL_TYPE_LONG */,
  `total_likes` int(11) NOT NULL DEFAULT '0'  /* MYSQL_TYPE_LONG */,
  `uploadSource` enum('direct','remote','ftp','torrent','leech','webdav','api','fileimport','other') CHARACTER SET utf8 NOT NULL DEFAULT 'direct'  /* MYSQL_TYPE_ENUM */,
  `unique_hash` varchar(64) CHARACTER SET utf8 DEFAULT NULL  /* MYSQL_TYPE_VARCHAR */,
  `date_updated` datetime DEFAULT NULL  /* MYSQL_TYPE_DATETIME2 */,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_hash` (`unique_hash`),
  KEY `shortUrl` (`shortUrl`),
  KEY `originalFilename` (`originalFilename`),
  KEY `fileSize` (`fileSize`),
  KEY `visits` (`visits`),
  KEY `lastAccessed` (`lastAccessed`),
  KEY `extension` (`extension`),
  KEY `userId` (`userId`),
  KEY `statusId` (`statusId`),
  KEY `userId_2` (`userId`),
  KEY `uploadedDate` (`uploadedDate`),
  KEY `folderId` (`folderId`),
  KEY `serverId` (`serverId`),
  KEY `fileHash` (`fileHash`),
  KEY `linkedFileId` (`linkedFileId`),
  KEY `statusId_2` (`statusId`),
  KEY `uploadedUserId` (`uploadedUserId`),
  KEY `keywords` (`keywords`),
  KEY `status` (`status`),
  KEY `uploadedIP` (`uploadedIP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I have tried many many fixes on Google and other forums but none of them worked, few things that i have tried are

Setting in my.cnf

sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Trying this

SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

...and many more. If someone could please help me out in this matter, because I am not experienced in databases, then I would really appreciate the help.

I am on centos 7 and MariaDB10.3.23-MariaD

MariaDB (unlike MySQL) has special behavior if NULL is assigned to column that uses the TIMESTAMP data type.

If the column is assigned the NULL value in an INSERT or UPDATE query (including implicit assigning via DEFAULT), then MariaDB will automatically initialize the column's value with the current date and time. So DEFAULT NULL for TIMESTAMP column is incorrect.

Each time when you try to assign NULL to TIMESTAMP column (explicitly or implicitly) it will be assigned to CURRENT_TIMESTAMP. Disabling NO_ZERO_DATE and NO_ZERO_IN_DATE has no effect in this case.

So specify columns as:

`uploadedDate` timestamp DEFAULT CURRENT_TIMESTAMP

and

`lastAccessed` DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP


MySQL Tuner OPTIMIZE and Defragmentation I have just started using mysqltuner to help keep my mysql server fast and healthy. However, I am having a little trouble optimizing my tables. When I execute mysqltuner, it all works fine and returns the results. It states that there are 317 fragmented tables and that I should run 'OPTIMIZE' table to repair them.

So, I wrote a script that executes each week to 'OPTIMIZE' every table in the databases on the local server. The script seems to work fine because of the results that are returned. Despite this, when I run mysqltuner again, it still says there are 317 (actually, to be fair, it has reduced by 4 to 313) fragmented tables and that I should OPTIMIZE them still.

Is there something else I should do? Is my script right?

Here is my OPTIMIZE script:

mysqlcheck -u'admin' -p`cat /etc/psa/.psa.shadow` --auto-repair --optimize --all-databases;

Once you delete some rows from a table its data file could become fragmented and that table becomes unoptimized in terms of MySQL. If you really do frequently delete rows from those tables, you should not worry because this is the expected behavior. You don't need to do anything else.

OPTIMIZE TABLE is usually used to reclaim the unused space and to defragment the data file. Also you should know that MySQL locks the table during the time OPTIMIZE TABLE is running, which means that no one can run queries against that table and has to wait.


Execution plan contains a 'sort' even though the data is sorted in index I have a table which has a clustered index as below:

CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded]
ON Machinery (MachineryId, DateRecorded)

I'm selecting based on the fields in the clustered index, plus one more:

SELECT DateRecorded, Latitude, Longitude
FROM MachineryReading
WHERE MachineryId = 2127        -- First key in the index
AND DateRecorded > '2017-01-10' -- Second key in the index
AND DateRecorded < '2017-10-16' -- Second key in the index
AND FixStatus >= 2              -- Not a key, resulting in a scan
ORDER BY DateRecorded

I would have expected this to result in a simple clustered index scan. However, looking at the live query statistics, and the actual execution plan, the majority of the query's execution time comes from sorting the results after the index scan. Why is the ordered data being sorted again?

https://www.brentozar.com/pastetheplan/?id=S10DvjZpb

Your query accesses 10 partitions and you are searching a 10 month range so my guess would be that it is partitioned on month of DateRecorded.

I can reproduce your plan with the sort with the below.

CREATE PARTITION FUNCTION pf1 (DATE) AS RANGE RIGHT FOR VALUES (
'2017-01-01',
'2017-02-01',
'2017-03-01',
'2017-04-01',
'2017-05-01',
'2017-06-01',
'2017-07-01',
'2017-08-01',
'2017-09-01',
'2017-10-01',
'2017-11-01' );

CREATE PARTITION SCHEME ps1 AS PARTITION pf1 ALL TO ([Primary]);

CREATE TABLE MachineryReading
  (
     MachineryId  INT,
     DateRecorded DATE,
     Latitude     FLOAT,
     Longitude    FLOAT,
     FixStatus    INT
  )
ON ps1(DateRecorded)

CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded]
  ON MachineryReading (MachineryId, DateRecorded)

but technically a sort could be avoided if you could get a plan that processed the partitions in order and just concatenated one ordered result to the next.

If you are happy to assume that the partition numbers will be in order of value (I don't know if this is actually guaranteed but it seems to be the case even after partition splits) then adding a leading column to the sort of the partition number achieves this

SELECT DateRecorded,
       Latitude,
       Longitude
FROM   MachineryReading
WHERE  MachineryId = 2127
       AND DateRecorded > '2017-01-10'
       AND DateRecorded < '2017-10-16'
       AND FixStatus >= 2
ORDER  BY $PARTITION.pf1(DateRecorded),
          MachineryId, --Not really needed as this is a constant 2127
          DateRecorded


Update trigger issue throwing errors on IF statements I have created a trigger, basically what I want it to do is update or insert a record to another table.
Two table I have got:

MVF_Transport_Register: Main table
MVF_Transport_Acc: table trigger need to update.

When a record is updated on the Main table, I want to the trigger to check if that records exists on the MVF_Transport_Acc.

If it does only update the Record_status column.
If the record is not in MVF_Transport_Acc then insert the new record.

This is what I have written but I am getting the following error when trying to deploy it:

Msg 156, Level 15, State 1, Procedure UpdateTransport_TEST1, Line 36
Incorrect syntax near the keyword 'END'.

Here's the trigger:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UpdateTransport_TEST1]
   ON  [dbo].[MVF_Transport_Register]
   AFTER UPDATE
AS
IF UPDATE (Record_Status)

BEGIN
IF (i.Order_No=c.Order_No )
UPDATE c
set Record_Status=i.Record_Status
FROM dbo.MVF_Transport_Acc AS c
JOIN inserted as i
ON i.Order_No = c.Order_No
JOIN deleted as d
ON i.Order_No=d.Order_No
OR (i.Record_Status <> d.Record_Status)
WHERE  (i.Record_Status = 'CONFIRMED' OR
       i.Record_Status = 'AMENDED') AND (i.Price > 0)
ELSE

INSERT into dbo.MVF_Transport_Acc
([Order_No], [Record_Status], [Notes], [Transport_Supplier], [Surcharge], [Vendor_No], [Pallets], [Amend_Pallets],[Price], [Input_Date], [Amend_Price], [Transport_Invoice_No],[Transport_Job_No], [Amend_Date], [Report_Price],
                         [Return_Price], [Report_Date])

SELECT  i.[Order_No], i.[Record_Status], i.[Notes], i.[Transport_Supplier], i.[Surcharge], i.[Vendor_No], i.[Pallets], i.[Amend_Pallets],i.[Price], i.[Input_Date], i.[Amend_Price], i.[Transport_Invoice_No],i.[Transport_Job_No], i.[Amend_Date], i.[Report_Price],
                         i.[Return_Price], i.[Report_Date]

        FROM   [dbo].[MVF_Transport_Register] c
        INNER JOIN inserted i on
       c.Order_No = i.Order_No
WHERE  (i.Record_Status = 'CONFIRMED' OR
       i.Record_Status = 'AMENDED') AND (i.Price > 0)

END IF

END

This logic seems wrong to me, but I think you have it way more complicated than it needs to be:

ALTER TRIGGER [dbo].[UpdateTransport_TEST1]
   ON  [dbo].[MVF_Transport_Register]
   AFTER UPDATE
AS
BEGIN
  UPDATE c
    SET c.Record_Status = i.Record_status
    FROM dbo.MVF_Transport_Acc AS c
    INNER JOIN inserted AS i
    ON c.Order_No = i.Order_No
    AND c.Record_Status <> i.Record_Status
    WHERE i.Record_Status IN ('CONFIRMED', 'AMENDED')
      AND i.Price > 0;

  IF @@ROWCOUNT = 0
  BEGIN
    INSERT dbo.MVF_Transport_Acc
    ([Order_No], [Record_Status], [Notes], [Transport_Supplier],
     [Surcharge], [Vendor_No], [Pallets], [Amend_Pallets],[Price],
     [Input_Date], [Amend_Price], [Transport_Invoice_No],[Transport_Job_No],
     [Amend_Date], [Report_Price], [Return_Price], [Report_Date])
    SELECT i.[Order_No], i.[Record_Status], i.[Notes], i.[Transport_Supplier],
           i.[Surcharge], i.[Vendor_No], i.[Pallets], i.[Amend_Pallets],
           i.[Price], i.[Input_Date], i.[Amend_Price], i.[Transport_Invoice_No],
           i.[Transport_Job_No], i.[Amend_Date], i.[Report_Price],
           i.[Return_Price], i.[Report_Date]
    FROM inserted AS i
    WHERE i.Record_Status IN ('CONFIRMED', 'AMENDED')
      AND i.Price > 0;
  END
END
GO

I still feel like that logic is incomplete for some reason.


MYSQL optimization questions about values on server I have an etc/my.cnf file with these values:
innodb_force_recovery=6
sql-mode="NO_ENGINE_SUBSTITUTION"
innodb_strict_mode=0
bind-address=0.0.0.0
performance-schema=0
innodb_file_per_table=1
thread_concurrency=4
max_connections=250
max_user_connections=30
key_buffer_size=256M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=1M
interactive_timeout=45
wait_timeout=360
connect_timeout=120
max_allowed_packet=1053741824
max_connect_errors=10
query_cache_limit=20M
query_cache_type=1
flush
flush_time=60
open_files_limit=50000
default-storage-engine=MyISAM

What would you recommend when having this kind of warnings in Mysql please:
created tmp disk tables    345.7K
handler read rnd 165M
handler read rnd next 45G
innodb buffer pool reads 140k
opened tables 2k
Qcache lowmenprunes 50k
sort merge passes : 55
select full join 450k

All the above showing as "High" in PHPMYADMIN
The site is relatively slow and I wonder if increasing the memory in some cases might help a bit but I would like to have an opinion on this please first.
Thank you.

On settings:
innodb_force_recovery - don't set this. Its dangerous to have this as default.
query_cache_limit=20M
query_cache_type=1
Disable these. They are a concurrency problem.
flush flush_time=60 - I've never seen these set. Sounds like a workaround for MyISAM being default.
default-storage-engine=MyISAM don't set MyISAM being a default. It has horrible concurrency problems. Change existing tables to innodb.
On status variables warnings:

created tmp disk tables    345.7K

handler read rnd 165M

handler read rnd next 45G

select full join 450k
All these indicate poorly indexed queries. Enable your slow query log and start asking questions on how to index your tables better.

innodb buffer pool reads 140k

Your innodb_buffer_pool_size isn't big enough. Especially after you change all your tables to InnoDB. Start at several G and increase until the innodb_buffer_pool_reads is significantly (<<1%) less than innodb_buffer_pool_read_requests.

opened tables 2k

Might not be that bad. Check the number of active tables and if > 2k increase table_open_cache.

Qcache lowmenprunes   50k

Won't matter when query cache is disabled.

sort merge passes : 55

When indexing is improved maybe this won't be needed.  If you do a lot of large table sorts maybe sort_buffer_size should be increased, but with correct indexing there is often no need to sort results.


SQLite3: Remove duplicates Given the following table

CREATE TABLE `sms` (
    `_id`   INTEGER,
    `thread_id` INTEGER,
    `address`   TEXT,
    `person`    INTEGER,
    `date`  INTEGER,
    `protocol`  INTEGER,
    `read`  INTEGER DEFAULT 0,
    `status`    INTEGER DEFAULT -1,
    `type`  INTEGER,
    `reply_path_present`    INTEGER,
    `subject`   TEXT,
    `body`  TEXT,
    `service_center`    TEXT,
    PRIMARY KEY(_id)
);

I'd like to delete all rows where address and body are identical. In other words, eliminate dups based on address and body, leaving only the first occurrence.

If you just want to disambiguate two rows with similar content, you can use the ROWID functionality in SQLite3, which helps uniquely identify each row in the table.

Something like this:

DELETE FROM sms WHERE rowid NOT IN (SELECT min(rowid) FROM sms GROUP BY address, body);

should work to get you the rows with the minimum rowid, which will be the first occurrence of that row in the table.

Be sure to run this in a transaction however, to make sure that it does exactly what you want, and of course, take a backup beforehand. =)


How to efficiently eliminate data from select when rows are different by one column value I have such TABLE with data:

A  | B  | C  |  D | E
-----------------------
a1 | b1 | c1 | d1 | PA
a1 | b1 | c1 | d1 | PR
a2 | b2 | c2 | d2 | PA
a3 | b3 | c3 | d3 | PA
a3 | b3 | c3 | d3 | PR

row1 has its counterpart row2 (they are only different by col E)
row3 has no counterpart - unique
row4 has its counterpart row5  ( same as first statement - col E )

the query should gave me a result like this:

A  | B  | C  |  D | E
-----------------------
a2 | b2 | c2 | d2 | PA

only unique row which has no counterpart -
no row with same set of data only which different only by value in column E

we can apply here a few approaches which one will be most effective if we have more rows with its counterparts as unique rows (without counterpart)
which join use ?
which one created select statement subtract or add ?

ps. an extended case

A  | B    | C  |  D                  | E
----------------------------------------
a1 | b1   | c1 | 2017-05-16 11:46:36 | PA
a1 | b1   | c1 | 2017-05-16 11:46:37 | PR
a2 | b2   | c2 | 2017-05-17 01:34:28 | PA
a3 | null | c3 | 2017-05-12 19:14:15 | PA
a3 | b3   | c3 | 2017-05-12 19:14:15 | PR
a4 | b4   | c4 | 2017-05-12 19:16:15 | PA
a4 | b4   | c4 | 2017-05-12 23:16:15 | PR

column A -D may contain null values  (row4)
column D is a TIMESTAMP  and it could vary from its counterpart by 1-2minutes   (row1, row2)
we add the max difference in timestamp  = example 2 minutes (row6 has no counterpart as row7 vs row6 timestamp difference is greeter than 2 minuts (4 hours)
we don't want any row with PR in our result

expected result:

A  | B    | C  |  D                  | E
----------------------------------------
a2 | b2   | c2 | 2017-05-17 01:34:28 | PA
a4 | b4   | c4 | 2017-05-12 19:16:15 | PA

I wouldn't use a JOIN. If you would like to look for rows that have a set of columns in common (in your case, all but one), I would just GROUP the rows by the common columns. Then, restrict which groups you want to return (using a HAVING clause):

SELECT
    A, B, C, D, min(E) AS E
FROM
    t
GROUP BY
    A, B, C, D
HAVING
    count(*) = 1 ;

The min(E) part is just a trick to be "SQL compliant". max(E), or first(E) (for the databases that have it) would do the job as well. As you're choosing just groups with a single row, the min value of a column within the group is the single value there is. In mySQL 5.6 and earlier, leaving E as a column would work as well; although this is not SQL standard and doesn't work in 5.7 (thanks to
ypercubeᵀᴹ for pointing the fact that this doesn't work on 5.7).

Results are:

|  A |  B |  C |  D |  E |
|----|----|----|----|----|
| a2 | b2 | c2 | d2 | PA |

You can get everything at this SQL Fiddle

You can also take a different approach. Look for rows that literally comply with what you're looking for: that is, it does NOT exist another row with the same A, B, C and D and different E. In this case, use an EXISTS condition in your WHERE clause:

SELECT
    A, B, C, D, E
FROM
    t AS t0
WHERE
    NOT EXISTS
    (SELECT
         *
    FROM
        t AS t1
    WHERE
             t1.A  = t0.A
         AND t1.B  = t0.B
         AND t1.C  = t0.C
         AND t1.D  = t0.D
         AND t1.E <> t0.E
    ) ;

Older versions of MySQL would probably not handle this query too well, because EXISTS conditions are not well optimized. mySQL 5.7 does a decent job; and so do most other databases (Oracle, MS SQL Server, PostgreSQL, ...) . You do need an index on (A, B, C, D, E) (or, at least, (A, B, C, D) to be really performant.

SQL Fiddle

The two queries will not work the same in the case where you have two exact duplicate rows (which I interpret is a case not allowed by your spec). The first one will return no rows, whereas the second one will return both rows.

MySQL 5.6 Schema Setup:

CREATE TABLE t
(
    `A` varchar(2),
    `B` varchar(2),
    `C` varchar(2),
    `D` varchar(2),
    `E` varchar(2)
) ;

INSERT INTO t
    (`A`, `B`, `C`, `D`, `E`)
VALUES
    ('a1', 'b1', 'c1', 'd1', 'PA'),
    ('a1', 'b1', 'c1', 'd1', 'PR'),
    ('a2', 'b2', 'c2', 'd2', 'PA'),
    ('a3', 'b3', 'c3', 'd3', 'PA'),
    ('a3', 'b3', 'c3', 'd3', 'PR');

For your extended case, the second query is your starting point.

Change your equalities with whichever conditions meet your needs.

For instance, if your D columns is a timestamp, and you want to consider that "two times are equivalent if the difference is less than 2 minutes"... then change

t1.D = t0.D

by

abs( unix_timestamp(t1.D) - unix_timestamp(t0.D) ) < 120 -- seconds

The possibility of nulls (that work as "jokers") should be handled with:

(t1.A = t0.A or t0.A is null or t1.A is null)

If E is nullable, then the comparison is done with

NOT (t1.E <=> t0.E)

which is mySQL equivalent of (t1.E is distinct from t0.E)

This will give you:

 SELECT
     A, B, C, D, E
 FROM
     t AS t0
 WHERE
     NOT EXISTS
     (SELECT
          *
     FROM
         t AS t1
     WHERE
              (t1.A = t0.A or t1.A is NULL or t0.A is NULL)
          AND (t1.B = t0.B or t1.B is NULL or t0.B is NULL)
          AND (t1.C = t0.C or t1.C is NULL or t0.C is NULL)
          AND (abs( unix_timestamp(t1.D) - unix_timestamp(t0.D) ) < 120)
          AND NOT (t1.E <=> t0.E)
     )
     AND t0.E <> 'PR' ;

 A  | B  | C  | D                   | E
 :- | :- | :- | :------------------ | :-
 a2 | b2 | c2 | 2017-05-17 01:34:28 | PA
 a4 | b4 | c4 | 2017-05-12 19:16:15 | PA

dbfiddle here

Don't expect this kind of queries to be efficient in realistic scenarios.


How mysql database performance can impact by different types of disk[hard disks] I have a ques regarding mysql server performance which how related to server's different types of disk. I mean how disk impacting Mysql Server performance and which type of disk is more convenient for good performance. Please suggest the best way to decide which type is disk is best like ssd,nvme etc

Short answer: It's 2020, the case for spinning rust is super-weak.

Long answer: It depends on how much memory you have and what your read:write ratio is.

If the workload is mostly reads, and you have enough buffer pool to cache all of the even remotely hot data, disk speed won't matter as much because once the buffer pool is warmed up, not much data will be coming off the disk.

For writes, every transaction is going to require at a minimum 2-4 disk IOPS with InnoDB.

1) Once into the transaction log

2) Once into the target tablespace

3) Once again into the transaction log (unless innodb_doublewrite=0)

4) Once into the binlog (if log_bin and sync_logbin=1)

A typical 7200rpm disk can sustain about 120 IOPS (that's how many times per second the required sector comes around to meet the heads).

So if you don't need to sustain more than about 30 transactions per second (note: transactions per second is not the same as queries per second), you may be able to scrape by with a spinning disk.

There are various things you can do to speed this up, if you can afford to lose transactions in a system crash scenario, e.g.:

innodb_doublewrite=0
innodb_flush_logs_at_trx_commit=0

or even better, if you are on ZFS, zfs set sync=disabled on the relevant volume, and enjoy the fact that on ZFS innodb_doublewrite=0 is safe and there is no need to set innodb_flush_logs_at_trx_commit=0 if you set sync=disabled.

That will make the workload a lot more sequential and thus the IOPS limitation will be less severe. But really, if you have to resort to this sort of thing to squeeze more performance out of the storage, you really should be buying an SSD instead.

NVMe is usually 4x faster on throughput and much faster on latency than SATA SSD.

So you will have to decide in the context of your workload what your performance requirements are and what you can afford, and plot a compromise through those parameters.


How to match a WiredTiger index file to its corresponding collection I have a mongodb database with about 2 billion records, shared between 20 or so collections. Each of these collections has an index on it, based on a single key (other than _id).

I have an index file (named index-1-5374774422504609475.wt) that I'd like to match to a collection. Is there a quick way to match directly which index file corresponds to a collection?

I do mean the actual physical index file in the mongo db directory.

Thanks in advance!

Ref:
https://docs.mongodb.com/manual/reference/method/db.collection.stats/

https://docs.mongodb.com/manual/reference/command/collStats/#collStats.indexDetails

As you are using WiredTiger storage engine you can use db.collection.stats() with indexDetails option set to true.

Easy copy paste:

.stats({"indexDetails":true})

  A document that reports data from the WiredTiger storage engine for
  each index in the collection. Other storage engines will return an
  empty document.

  The fields in this document are the names of the indexes, while the
  values themselves are documents that contain statistics for the index
  provided by the storage engine. These statistics are for internal
  diagnostic use.


Need advises for a new MySQL architecture I need to deploy a MySQL-based application with the following requirements and would love to receive some guidance (any) as to how I could set things up:

Scale of deployment

5 servers with a latency of 200-400ms between them.
Database content/structure

Running InnoDB. 1GB of data split across 50 tables which are categorized as follows:

-user tables: about 200MB across 10 tables which store information on users (e.g. user, user_favorites, user_votes...).
-content tables: about 800MB across 40 tables which store information about everything else (these tables don't have any relationships with the user tables).
Content creation/access

-user tables: each server should have READ-WRITE access to these tables. The frequency of writes depends on users' activity on the website (e.g. people creating account, updating it, voting and/or putting items in favorites...).

-content tables: each server should have READ ONLY access to these tables which would be updated once a week when the webmaster updates the website's content (and on some occasions maybe on a daily basis to perform urgent content updates).

And here is the setup I've come up with so far:

each server would have its own local copy of the database for READ-ONLY access.
WRITE queries for user tables would be forwarded to 1 MASTER server using MySQL splitting techniques.
WRITE queries for content tables would be performed directly on the MASTER server.
The MASTER server would be replicated to the other servers operating as SLAVES.

And here are my questions:

Q1: Do you see anything fundamentally wrong with my setup / can you recommend something better?

Q2: What tool would you use to split READ and WRITE queries?

Q3: WRITE queries for user tables are going to be delayed because of the latency of writing to the MASTER: is there anything that can be done to mitigate that?

This sounds like a good use case for Galera.
http://codership.com/content/using-galera-cluster

You could also use something like this:
Each server is a master for it's copy of the user table (I assume the user table is sharded?). Then there could be a central server with MariaDB or MySQL + Tungsten Replicator which is a slave of all the other servers.

And then you could create another master (extra instance on one of the servers?) which hosts the master copy of the content tables.

CM = Content Master
U  = User Master
UC = User Combined

Server 1: CM + UC (2 Instances)
Server 2-5: U

For the content tables (1 master, 4 slaves):
CM -> U1
   -> U2
   -> U3
   -> U4

For the user tables (4 masters, 1 fan-in slave):
U1 -> UC
U2 ->
U3 ->
U4 ->

Galera and MySQL Cluster provide synchronous cluster where you can write to any server. MySQL Cluster needs 2 data nodes and 2 SQL nodes as a minimal setup (the mgmt node can be combined with a SQL node). MySQL Cluster does not use InnoDB or MyISAM. Galera needs 3 nodes and does support InnoDB.

Investigate Galera, Tungsten (and MySQL Cluster aka NDB?).

Read MySQL High Availability (O'Reilly).


Results for every hour of the day even data if not present I am trying to write a query to show the count of users for each hour of the day. If there is no data for that hour, I want to record the hour with a count of zero.

Source data

╔════════╦═════════════════════════╗
║ userId ║      creationDate       ║
╠════════╬═════════════════════════╣
║      1 ║ 2014-10-08 14:33:20.763 ║
║      2 ║ 2014-10-09 04:24:14.283 ║
║      3 ║ 2014-10-10 18:34:26.260 ║
╚════════╩═════════════════════════╝

Desired output

╔═════════════════════════╦═══════════╗
║          Date           ║ UserCount ║
╠═════════════════════════╬═══════════╣
║ 2014-10-08 01:00:00.000 ║         1 ║
║ 2014-10-08 02:00:00.000 ║         1 ║
║ 2014-10-08 03:00:00.000 ║         0 ║
║ 2014-10-08 04:00:00.000 ║         1 ║
║ ....                    ║           ║
║ .....                   ║           ║
║ 2014-10-10 23:00:00.000 ║         1 ║
║ 2014-10-10 00:00:00.000 ║         0 ║
╚═════════════════════════╩═══════════╝

Attempt 1

SELECT
    CAST(creationDate as date) AS ForDate,
    DATEPART(hour,date) AS OnHour,
    COUNT(distinct userId) AS Totals
FROM Table
where primaryKey= 123
GROUP BY
    CAST(creationDate as date),
    DATEPART(hour, createDate);

This only gives me counts per hour for records that are present, nothing for the missing hours. I think there is a way by using a cross join to get a zero count for the missing hours.

Something like this, I came across, but not able to construct a proper query with it:

cross join (select ROW_NUMBER() over (order by (select NULL)) as seqnum
from INFORMATION_SCHEMA.COLUMNS) hours where hours.seqnum <= 24

I am not a SQL expert, but I am trying hard to construct this result set.

Attempt 2:

with dh as (
     select DATEADD(hour, seqnum - 1, thedatehour ) as DateHour
     from (select distinct cast(cast(createDate as DATE) as datetime) as thedatehour
           from Table a
          ) a
          cross join
          (select ROW_NUMBER() over (order by (select NULL)) as seqnum
           from INFORMATION_SCHEMA.COLUMNS
          ) hours
          where hours.seqnum <= 24
    )
select dh.DateHour, COUNT(distinct c.userId)
from dh cross join Table c
--on dh.DateHour = c.createDate
group by dh.DateHour
order by 1

Take the query you already have and capture the output in a temp table #T.

select cast(T.creationDate as date) as ForDate,
       datepart(hour, T.creationDate) as OnHour,
       count(distinct T.userId) as Totals
into #T
from dbo.T
group by cast(T.creationDate as date),
         datepart(hour, T.creationDate)

Get the distinct ForDate values from #T and cross apply against a list of hours for one day and left outer join #T to get the Totals for each hour.

select dateadd(hour, H.OnHour, cast(D.ForDate as datetime)) as ForDate,
       isnull(T.Totals, 0) as Totals
from (select distinct T.ForDate from #T as T) as D
cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
                   (13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) as H(OnHour)
  left outer join #T as T
    on H.OnHour = T.OnHour and
       D.ForDate = T.ForDate

drop table #T


Difference between is_published and is_merge_published in Sql Server? What is the difference between the columns is_published and is_merge_published in Sql Server  ?

I can't find docs describing it !

SELECT [name],[is_published],[is_merge_published] FROM sys.databases ;

As per the comments, you are referring to the columns in sys.databases

As per SQL Server Docs

  is_published  bit

  1 = Database is a publication database in a
  transactional or snapshot replication topology

  0 = Is not a publication database

  is_merge_published    bit

  1 = Database is a publication database in a
  merge replication topology

  0 = Is not a publication database in a
  merge replication topology


Update a valid column when another column has changed We have a valid_phone column that just stores 1 or 0 for valid or not valid.

When the user changes their phone number we want that field to go back to 0 so they will have to validate their number again. If it's not changing we want to respect whatever value was there, so not always just assuming that if it didn't change set it to 1 because they may have not validated their phone yet so it should in that case stay at 0.

I saw a query that used a CASE and thought it may work for us but so far I have not gotten it to work.

UPDATE text2bid_users SET valid_phone = CASE
    WHEN phone NOT '0000' THEN '0'
END WHERE user_id = '1234'

I'm sure my syntax may be wrong or I may even be looking in the wrong place for a solution as I'm a front end guy just starting to dip into backend and database.

My thought on this is to see if phone is already set to the phone I'm about to update it with (so that wouldn't be hard-coded) and if it is not the same then set valid_phone to 0 and then outside of this name, email, address etc. will also update as well.

How would I get a query to do what we need it to do here?

I might be updating other values too, so I don't want it to not update the other fields if the phone comes through as the same. I suppose I could run two queries but I feel like I should be able to do it in one query. I may be wrong.

UPDATE text2bid_users
SET valid_phone = CASE WHEN phone != '0000' THEN 0 else valid_phone END,
phone = '0000'
WHERE user_id = 1;

http://sqlfiddle.com/#!9/24784/1


Unable to check whether the location specified is on CFS I have googled this error message and it says:

  INS-30014: Unable to check whether the location specified is on CFS
  Cause: The location specified might not have the required permissions.
  Action: Provide a location which has the appropriate required permissions.

But I am already the administrator and run the installation file as administrator. Any idea? Any help will be appreciated. Thanks.

I was getting this on a VM with NAT NIC. Installer was pulling in DNS suffix from NIC and messing up DNS name of my server on install. Disabling NAT NIC allowed to me to install and then I re-enabled after install and it was fine. Not related to permissions at all.


Trying to understand TID definition from PostgreSQL documentation I am trying to understand what is TID and how it works.
I found 2 definitions of TID in the documentation:

1) from https://www.postgresql.org/docs/11/datatype-oid.html

  A final identifier type used by the system is tid, or tuple identifier
  (row identifier). This is the data type of the system column ctid. A
  tuple ID is a pair (block number, tuple index within block) that
  identifies the physical location of the row within its table.

2) from https://www.postgresql.org/docs/11/storage-page-layout.html

  In fact, every pointer to an item (ItemPointer, also known as CTID)
  created by PostgreSQL consists of a page number and the index of an
  item identifier.

I understand the second definition and it's clear for me, but I struggle with the first definition and the terms it uses.

What are block and tuple index within block? How do they match with pages and the second definition?

Please help and clarify, which definition is correct and how should I understand the terms from the first definition.

“Page” and “block” are synonyms in PostgreSQL and refer to the 8KB block of data that is the unit for all object storage. One tends to use the term “block” when speaking of disk storage and “page” when the data reside in memory, but it is the same.

So a tid of (42,9) means that the item is the ninth element in the 43rd 8KB block of the table or index (blocks are counted from 0).


How to make sqlplus output appear in one line? I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read.

What I'd rather like is either a horizontal scroll bar to appear or somehow send the output to less

I run following statements in SQLPlus -

SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
SPOOL output.txt
SELECT * FROM big_table;

Then in bash I run -

  less output.txt

The output still appears wrapped and unreadable.

It is not enough to force sqlplus not to wrap lines. It is also necessary to tell the viewer that you use to view the spool file not to wrap lines.
If your viewer is less then -S the option you have to use according to
https://superuser.com/questions/272818/how-to-turn-off-word-wrap-in-less .
On Unix/Linux you can use head -1 output.txt to get the first line of a file and so check if this is as expected or you can use od -c output.txt|head
to see where the line breaks are actually placed in your output file.

If you display LONG columns and their values contain line breaks then multiple lines will be printed for these column values and you cannot overrule this with sqlplus settings.

The following sqlplus commands maybe usefull:

SET LINESIZE linesize the length of the line. In most cases the maximum value for linesize is 32767.  You can find out your maximum value if you set LINESIZE to an invalid value and check the error message
so SET LINESIZE 0 may give SP2-0267: linesize option 0 out of range (1 through 32767)  (1)
SET TRIMSPOOL ON otherwise every line in the spoolfile is filled up with blanks until the linesize is reached.
SET TRIMOUT ON otherwise every line in the output is filled up with blanks until the linesize is reached.
SET WRAP OFF Truncates the line if its is longer then LINESIZE. This should not happen if linesize is large enough.
SET TERMOUT OFF suppresses the printing of the results to the output.
The lines are still written to the spool file. This may accelerate the exectution time of a statement a lot.
SET PAGESIZE 0 to set an infinite pagesize and avoid headings , titles and so on.
There are some other SET parameters concerning output (NUMWIDTH, NUMFORMAT, LONG, COLSEP)  and performance (ARRAYSIZE, LONGCHUNKSIZE).

You have to use the COLUMNcommand to format individual columns.

E.g. column name format a30 will format the column name in the output to a maximum length of 30 characters.

If you want the display size not be fix but it should be equal to the size of the actual value of a columns in a row then the only way I know is that you change the select clause of your statement to get the desired result and use the
string concatenation operator || , e.g.

select emp_id||' '||first_name||' '||last_name
from emp;

A full description of all variable can be found in SQL*Plus User's Guide and Reference.

If you want to reuse some settings (or COLUMN definitions)  you can store them in a file and run this file when you need them again. You even can run this file automatically if you start sqlplus.

(1)  "How to Find the LINESIZE Maximum Value (It is System Dependent) (Doc ID 1547262.1)"


Does the transaction isolation level affect indices as well? Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages?

E.g. using ISOLATION LEVEL READ UNCOMMITTED, what effect (if any) do the ALLOW_PAGE_LOCKS or ALLOW_ROW_LOCKS on an index have??

ALTER INDEX IX_FirstName ON Employee
SET (ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)

I can't seem to find a definitive answer anywhere - the MSDN documentation on the transaction isolation levels really only talks about the data pages....

No. It doesn't affect anything regarding the internal management within SQL Server. You're setting that connection for you, for your queries. SQL Server manages it's own locking it's own way.

Why would you turn off page and row locking on an index? You're more likely to see more severe locking than if you let SQL Server manage that index as it sees fit. By setting both those values to OFF, you just told SQL Server to take a table lock out. Considering you're also messing with read_uncommitted, I'll bet you don't want that.

If you're asking does read_uncommitted allow you to get dirty reads on indexes, yes. It does.


How to Search values Integer column alias in a WHERE Clause How to Filter values in where clause  alias variable,

CREATE TABLE BASE_Chq
(
ChequeInvoicePaymentId BIGINT NULL,
ChequeNumber VARCHAR(45) NOT NULL,
Amount MONEY NOT NULL
)

Once i select the whole table value i'm getting the result like below,

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
BASE_Chq

i want to filter Cheque Type wise but i'm getting error,

ERROR CODE:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
FROM BASE_Chq
WHERE (ChequeType = 'PARTY')

How to search using alias  column in where clause?

You cannot use an alias in your where clause

Without questioning any parts of the query, you could do this:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
FROM BASE_Chq
WHERE (IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') = 'PARTY');

A better approach would be directly filtering on the IS NOT NULL:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
FROM BASE_Chq
WHERE ChequeInvoicePaymentId IS NOT NULL;

A small DB<>Fiddle example

Why

The filter in the where clause can be passed to the table as a (seek) predicate before computing the IIF function in the SELECT.

More info on this processing order in an answer by Erland Sommarskog:

  You can't do that because a SELECT statement is computed in this
  order:

  FROM-JOIN
  WHERE
  GROUP BY
  HAVING
  SELECT
  ORDER BY

  So when the WHERE clause is evaluated, the aliases defined in the
  SELECT list are not defined yet. ...


Postgresql view how to do the query I have 4 tables:

Project: id name

Keyword: id, name

ProjectKeyword: project_id, keyword_id (project has many keywords, keywords has many projects)

ProjectReport: project_id, keyword_id, position(keyword, project has many project_reports)

ProjectCompetitorReport: project_report, keyword_id, position, competitor_idposition`(keyword, project, competitor has many project_reports)

With the help of those tables I need to produce something like this output:

Keyword.name, project_report.position, project_competitor_report.position

The problem that I have is repetition, I don't now how to group the result.

SELECT
    k.name,
    pr1.position,
    pr2.position
from
    project_keywords p1
    JOIN
    keywords k on p1.keyword_id = k.id
    JOIN
    project_reports pr1 on
        p1.project_id = pr1.project_id AND p1.keyword_id = pr1.keyword_id
    JOIN
    project_competitor_reports pr2 on
        p1.project_id = pr2.project_id AND p1.keyword_id = pr2.keyword_id;

I need to group the result produced by that query so that for each keyword i get all the positions from project_reports and all positions from project_position_reports.

As an exemple:
keyword1 has position with value: 1 and 2 on project_report
keyword1 has position whth value: 2 on project_competitor_reports

For this the output should be:

keyword1 1,2 2

At the moment I get them like this:

keyword1 1, 2

keyword1 2, 2

Use array_agg or string_agg

select
    k.name,
    array_agg(pr.position order by pr.position) as pr_position,
    array_agg(pcr.position order by pcr.position) as pcr_position
from
    project_keywords pk
    inner join
    keywords k on pk.keyword_id = k.id
    inner join
    project_reports pr using (project_id,  keyword_id)
    inner join
    project_competitor_reports pcr using (project_id, keyword_id)
group by k.name
;


Postgresql 13 Change WAL segment size On my Ubuntu 20 server, I installed PostgreSQL 13 using the apt manager. I read some articles on performance tuning of PostgreSQL [1] and thought of increasing the WAL segment size (from default of 16MB). I see the following instruction, however, I don't know where to run this.
initdb -D ./data --wal-segment=1024
I guess the documentation refers to installing from PostgreSQL source code, which I don't intend to do. How do I go about change the WAL segment size?
[1] https://software.intel.com/content/dam/develop/external/us/en/documents/Open-Source-Database-Tuning-Guide-on-3rd-Generation-Intel-Xeon-Scalable-Processors.pdf

Following Daniel's answer, I did the following steps
$ pg_lsclusters
$ sudo pg_dropcluster --stop 13 main
$ sudo pg_createcluster 13 main -- --wal-segsize=256
$ sudo pg_ctlcluster 13 main start

You can verify the size of the WAL segments as
# du -hcs /var/lib/postgresql/13/main/pg_wal/*

Ubuntu/Debian packages for Postgres have their own layer on top of initdb and pg_ctl to control multiple instances and the integration with systemd.
The command that may be used to create an instance with specific options in Debian/Ubuntu is:
pg_createcluster [options] version name [-- initdb options]

use pg_lsclusters to see the list of already existing clusters. Possibly you want to drop the existing default cluster named main using the default segment size that you don't want, in order to have one single Postgres instance with the desired wal segment size.


Conceptual Data Model match MySQL script? Could someone tell me if my Conceptual Data Model (MCD) matches my MySQL script?

Can cardinality (1,4) exist?

MCD:

tbl1 ---(1,4)---(relation)---(1,1)---tbl2

MySql Script:

CREATE TABLE tbl1(
    idTbl1    Int  Auto_increment  NOT NULL
    [...]
    ,CONSTRAINT tbl1_PK PRIMARY KEY (idTbl1)
)ENGINE=InnoDB;

CREATE TABLE tbl2(
    idTbl2    Int  Auto_increment  NOT NULL
    [...]
    ,CONSTRAINT tbl2_PK PRIMARY KEY (idTbl2)
    ,CONSTRAINT tbl2_tbl1_FK FOREIGN KEY (idTbl1) REFERENCES tbl1(idTbl1)
)ENGINE=InnoDB;

You could enfoce your (1,4) cardinality, eg.

create table tbl2
    (idTbl1  int references tbl1,
     Tbl2Id int check (Tbl2Id in (1,2,3,4)) enforced,
     constraint tbl2_PK primary key (idTbl1,Tbl2Id))
;


CLR SQL vs T-SQL for queries I am aware that CLR stored-procedures is more efficient in procedural code and T-SQL is better for queries - however I am having a hard time deciding what to use for my specific needs, and I would like to know if there are other things I should consider when making my decision.

The code I am planning on creating involves querying a set of data, implementing some algorithms (that will become more complex over time) and insertion of the data to the database. This stored procedure will be deployed by a scheduler - most probably azure webJobs since as far as I know Azure SQL does not support db jobs.

why I think of using C#

Assuming I can load DAL libraries from my server, and leveraging OOP - I guess I can implement a more coder friendly solution to this problem - and as for performance I assume it would be better or at least not noticeable in comparison.

Why I think of using T-SQL

I don't actually believe it is a better solution, but there might be something I am overlooking that might be a good case for T-SQL, because it makes no sense to me that it would have none.

Most importantly

Is there a way I can create a T-SQL stored procedure, call a C# stored procedure with complex objects as parameters - do my algorithms and return the data to be inserted, and insert it from T-SQL? seems like the most elegant, and best performing(?) solution (assuming it's possible and there are no drawbacks to calling CLR from T-SQL).

First off, just to be clear: SQLCLR / .NET / C# / VB.NET cannot query the database. Only T-SQL can query SQL Server. So in order for SQLCLR code to get data or interact with SQL Server in any way, it must establish a SqlConnection, like any other .NET app, and submit T-SQL, or execute a Stored Procedure.

Yes, you can call a SQLCLR (whether it is C# or VB.NET does not matter) Stored Procedure, or Scalar Function, or Table-Valued Function (TVF), etc from T-SQL. In fact, that is the only way you can call SQLCLR objects. Classes representing the SQLCLR objects, defined in Assemblies, need to have T-SQL wrapper objects defined that point to those classes so that they can be invoked.

Sending complex objects into SQLCLR objects can be accomplished, but unfortunately not via Table Valued Parameters (TVPs). However, you can either create a local temporary table in T-SQL, populate it, and select from it in the SQLCLR object, OR you can package up the complex data in XML and pass that in as a parameter to the SQLCLR object that will traverse it via an XmlReader.

For getting the data back efficiently, you can either:

Issue INSERT statements from the SQLCLR Stored Procedure
Create a T-SQL Stored Procedure that accepts a TVP and call that from the SQLCLR object
return a Result Set from the SQLCLR object that is used in an INSERT statement, as as INSERT INTO ... EXEC for a SQLCLR Stored Procedure, or INSERT INTO ... SELECT for a SQLCLR TVF.

As far as loading you DAL libraries goes, that could work, but there could also be complications. It all depends on what you libraries are doing and what Framework libraries are being referenced. Only a small subset of the .NET Framework libraries are supported in SQL Server's CLR host, so if your libraries reference other libraries that are not "approved", then you will need to load those libraries manually, along with any of their dependent libraries (if there are any). The complicating factors here are that loading unsupported .NET Framework libraries requires that they be set to UNSAFE, which in turn requires that the Database be set to TRUSTWORTHY ON. The other complication is that loading the .NET Framework library might not even be possible in the first place since the CLR host inside of SQL Server only allows pure-MSIL Assemblies, and so you cannot load mixed-mode Assemblies. Some .NET Framework libraries are currently mixed-mode and won't load. Others are currently "pure" and will load, but there is no guarantee that those will not ever be changed into a mixed-mode Assembly in a future .NET Framework update. If/when that happens (and it has happened, such as with System.ServiceModel), then you will need to re-code you Assembly. So it's best to stick with the supported Framework libraries as those are guaranteed to continue working through future upgrades.

Keep in mind that App Domains are per Database, per Owner (of the Assembly). This means that, in terms of any particular SQLCLR object, all sessions / SPIDs will be calling the same code and sharing the same memory. This is why the methods need to be declared as static. And this is why attempting to use non-readonly static class variables requires the Assembly to be marked as UNSAFE: because the value of such a variable can be overwritten by another session / SPID (case in point: SQLCLR assembly throws error when multiple queries run simultaneously).

With respect to this statement in the Question:

  as for performance I assume it would be better or at least not noticeable in comparison.

I wouldn't assume anything here. There are a lot of factors that influence performance in either direction:

some datatypes transfer back-and-forth between SQL Server memory and .NET AppDomain memory faster than others
how you code your algorithms in C# (I recently wrote a simple function for someone who had already gotten another suggestion that used LINQ -- the LINQ version took 375 - 400 ms to do the same thing that my version, with more lines, took only 2 - 9 ms to complete, a 41x - 200x difference!)
how much data you have (sometimes T-SQL is faster with smaller values -- usually strings -- where SQLCLR might be faster with longer values)
are you following best practices? If you are coding a deterministic SQLCLR scalar function and don't mark it as IsDeterministic=true, then you can't get the benefit of it being able to participate in a parallel plan (something T-SQL UDFs can't do).
etc

Hence: don't assume; test!

If you are looking to learn more about working with SQLCLR, you might want to check out a series that I am writing on this topic on SQL Server Central: Stairway to SQLCLR (free registration is required to access their content). There are several articles and more coming, and plenty of examples to illustrate various features, security, etc.

Regarding Azure SQL Database: please note that while support for SQLCLR (SAFE Assemblies only) was added in late 2014, it was recently (and supposedly temporarily) removed. Customers were emailed on April 8th, 2016 of the feature being removed on April 15th, 2016. The most official notice I could find was this thread on reddit:

Customer:

  Is support for CLR Assemblies in Azure SQL going to end any time soon?
  I ask this because I received an email this morning (supposedly) from Microsoft stating that this feature will no longer be supported after April 15th (!!!).

Jan, a PM in the Azure SQL DB team working on performance and Elastic Pools:

  The email you received is indeed real. As stated in the mail, we determined that there is a security risk with user-defined SQL CLR in SQL Database. There have been no known exploits to date. In an effort to help protect the security of our customers, we have proactively suspended the use of user-defined SQL CLR in Azure SQL Database. This means that user-defined SQL CLR will no longer work. We will update you within six month with a timeline for bringing back SQL CLR.

and Tommy, a PM in the Azure SQL DB Security team:

  This issue only applies to the Azure SQL Database service, not SQL Server on-prem or IaaS.


How to set up foreign key constraints referencing more than one table I have a simple structure where I am not sure how to handle some of the issues that pop up.

There are 4 tables: Reports, ReportParameters, ScheduledReports, ScheduledReportParameters.

ReportParameters lists the needed parameters for the reports.

ScheduledReports is a scheduled instance of a report. (Report X to run on Thursday as an example).

ScheduledReportParameters is the list of parameters and their values to use when this instance runs.

Basic layout:

Reports:
ReportID (PK)

ReportParameters:
ReportParameterID (PK)
ReportID (FK to Reports)
Name

ScheduledReports:
ScheduledReportID (PK)
ReportID (FK to reports)

ScheduledReportParameters:
ScheduledReportID (FK to ScheduledReports)
ReportParameterID (FK to ReportParameters)
Value
(PK is formed by the ScheduledReportID and ReportParameterID)

This has the issue where the parameter associated with a scheduled report doesn't have to agree with what the base report actually expects, it could be to a different base report entirely and not the report the scheduled report is based off of.

So, I thought I could add the ReportID to the ScheduledReportParameters, but that doesn't help as there wouldn't be anything enforcing that the ReportID there agrees with what the ScheduledReport is based off of.

Am I missing something simple? Or, is this trickier than I had thought?

Having a scheduled report with parameters that don't actually go with the report isn't a big deal to me as when I pull them, the joins will automatically filter out any potential bad ones but I would like to prevent bad data going in the first place. I know I could use triggers to do this, but that can get ugly quickly.

It looks like you are not aware that foreign key can be based on several columns.

So, yes, add a column ReportID to ScheduledReportParameters and include it in two foreign keys to enforce the constraints.

One foreign key between ScheduledReportParameters and ScheduledReports using two fields (ScheduledReportID, ReportID).

Second foreign key between ScheduledReportParameters and ReportParameters using two fields (ReportParameterID, ReportID).

Full script

Reports

CREATE TABLE [dbo].[Reports](
    [ReportID] [int] NOT NULL,
    [ReportName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Reports] PRIMARY KEY CLUSTERED
(
    [ReportID] ASC
))

ReportParameters

CREATE TABLE [dbo].[ReportParameters](
    [ReportParameterID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ParameterName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ReportParameters] PRIMARY KEY CLUSTERED
(
    [ReportParameterID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_ReportParameterID_ReportID]
ON [dbo].[ReportParameters]
(
    [ReportParameterID] ASC,
    [ReportID] ASC
)
GO

ALTER TABLE [dbo].[ReportParameters]  WITH CHECK ADD
CONSTRAINT [FK_ReportParameters_Reports] FOREIGN KEY([ReportID])
REFERENCES [dbo].[Reports] ([ReportID])
GO

ALTER TABLE [dbo].[ReportParameters] CHECK CONSTRAINT [FK_ReportParameters_Reports]
GO

ScheduledReports

CREATE TABLE [dbo].[ScheduledReports](
    [ScheduledReportID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ScheduleName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ScheduledReports] PRIMARY KEY CLUSTERED
(
    [ScheduledReportID] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_ScheduledReportID_ReportID]
ON [dbo].[ScheduledReports]
(
    [ScheduledReportID] ASC,
    [ReportID] ASC
)
GO

ALTER TABLE [dbo].[ScheduledReports]  WITH CHECK ADD
CONSTRAINT [FK_ScheduledReports_Reports] FOREIGN KEY([ReportID])
REFERENCES [dbo].[Reports] ([ReportID])
GO

ALTER TABLE [dbo].[ScheduledReports] CHECK CONSTRAINT [FK_ScheduledReports_Reports]
GO

ScheduledReportParameters

CREATE TABLE [dbo].[ScheduledReportParameters](
    [ScheduledReportParametersID] [int] NOT NULL,
    [ScheduledReportID] [int] NOT NULL,
    [ReportID] [int] NOT NULL,
    [ReportParameterID] [int] NOT NULL,
    [ParameterValue] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ScheduledReportParameters] PRIMARY KEY CLUSTERED
(
    [ScheduledReportParametersID] ASC
))
GO

ALTER TABLE [dbo].[ScheduledReportParameters]  WITH CHECK ADD
CONSTRAINT [FK_ScheduledReportParameters_ReportParameters]
FOREIGN KEY([ReportParameterID], [ReportID])
REFERENCES [dbo].[ReportParameters] ([ReportParameterID], [ReportID])
GO

ALTER TABLE [dbo].[ScheduledReportParameters]
CHECK CONSTRAINT [FK_ScheduledReportParameters_ReportParameters]
GO

ALTER TABLE [dbo].[ScheduledReportParameters]  WITH CHECK ADD
CONSTRAINT [FK_ScheduledReportParameters_ScheduledReports]
FOREIGN KEY([ScheduledReportID], [ReportID])
REFERENCES [dbo].[ScheduledReports] ([ScheduledReportID], [ReportID])
GO

ALTER TABLE [dbo].[ScheduledReportParameters]
CHECK CONSTRAINT [FK_ScheduledReportParameters_ScheduledReports]
GO

It is necessary to have unique index on two columns that are used by the foreign key. I added explicit indexes in this example (IX_ReportParameterID_ReportID and IX_ScheduledReportID_ReportID). You can also add the second column into the primary key of the corresponding table.

Sample data

INSERT INTO [dbo].[Reports]
    ([ReportID]
    ,[ReportName])
VALUES
    (1,'R1'),
    (2,'R2');

INSERT INTO [dbo].[ReportParameters]
    ([ReportParameterID]
    ,[ReportID]
    ,[ParameterName])
VALUES
    (1,1,'R1P1'),
    (2,1,'R1P2'),
    (3,2,'R2P3'),
    (4,2,'R2P4'),
    (5,2,'R2P5');

INSERT INTO [dbo].[ScheduledReports]
    ([ScheduledReportID]
    ,[ReportID]
    ,[ScheduleName])
VALUES
    (1,1,'S1');

Testing constraints

Add parameter values for report 1:

INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (1,1,1,1,'R1 P1 V1'),
    (2,1,1,2,'R1 P2 V2');

(2 row(s) affected)

Try to add value for the parameter that doesn't belong to the report (parameter 3 belongs to report 2, not 1:

INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (3,1,1,3,'V');

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ScheduledReportParameters_ReportParameters". The conflict occurred in database "AdventureWorks2014", table "dbo.ReportParameters".
The statement has been terminated.

Try to add parameter value for the wrong scheduled report (while parameter 3 belongs to report 2, schedule 1 is not for report 2):

INSERT INTO [dbo].[ScheduledReportParameters]
    ([ScheduledReportParametersID]
    ,[ScheduledReportID]
    ,[ReportID]
    ,[ReportParameterID]
    ,[ParameterValue])
VALUES
    (3,1,2,3,'V');

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ScheduledReportParameters_ScheduledReports". The conflict occurred in database "AdventureWorks2014", table "dbo.ScheduledReports".
The statement has been terminated.

Missing parameter values

Please note, that this schema doesn't prevent missing values for parameters. For example, you can delete value V2:

DELETE FROM [dbo].[ScheduledReportParameters]
WHERE [ScheduledReportParametersID] = 2;

(1 row(s) affected)

Thus now the schedule S1 has value only for parameter P1 and doesn't have value for parameter P2.


Broken tempdb location and can't recover Made a mistake and mistyped an alter database command for tempdb.

Now the instance won't start. I can't start in single user mode using -m as it states the tempdb could not be found. I tried using:

net start msqsqlserver /f /t3608

But then I can't actually connect to the instance at all using either sqlcmd or ssms.

Start SQL Server as an application, not a service, in minimal config mode, and only recovering master, from a command prompt:

[path to this instance]\BINN\sqlservr.exe -c -f -T3608

(Add -s InstanceName if this is a named instance.)

Now, in a different command prompt, connect using SQLCMD:

sqlcmd -S InstanceName -E

And issue a correction (triple-check this!!!), then shut the application down:

> ALTER DATABASE tempdb MODIFY FILE ...
> SHUTDOWN WITH NOWAIT;
> GO


Contact system database design I'm designing a database for contacts and I'm looking for some feedback on the current design.

What I want:

A contact can be a person or an organization.
A contact can have many groups.
A contact can have many emails and addresses
A contact can have one organization (employed)
An organization can have many contacts (employees)
A group can have many contacts.

What I have:

Contact(Id, PersonId (FK), OrganizationId (FK), EmployerId (FK, self reference))
Person(Id, FirstName, LastName, Birthday)
Organization(Id, Name, Founded)
Group(Id, Name)
ContactGroup(Id, ContactId (FK), GroupId (FK))
Email(Id, ContactId (FK), Name)
Address(Id, ContactId (FK), Street, City, ZipCode, Country)

What bothers me the most is that the contact entity has one organization or one person, and also a self reference (EmployerId) if an organization has employees (which are also contact entities) and vice versa.

Is this a problem or see you a better way?

Check the "Data Model Resource Book, Vol. 1". It covers standard schemata for - among other things - contact management. Pretty much the whole CRM and accounting etc. side.

And yes, there are a lot of problems with your approach - but I wont replicate 30 pages or so from that book describing all the issues.


The domain for "Date" (Date is a reserved word, let's call it event_date) is a union between created_at and updated_at.

select created_at as event_time, 'C' as event_type
from T
union all -- the legs are disjoint
select updated_at as event_time, 'U' as event_type
from T

From there we can extract the date and count each type:

select date(event_time) as event_date
     , count(case when event_type = 'C' then 1 end) as created_at
     , count(case when event_type = 'U' then 1 end) as updated_at
from (
    select created_at as event_time, 'C' as event_type
    from T
    union all -- the legs are disjoint
    select updated_at as event_time, 'U' as event_type
    from T
) as TT
group by date(event_time)
order by date(event_time);

Untested, but the principle should be valid.


BI development in Microsoft BI I need to learn how to create a dimensional modelling for an organisation that do not has a BI tool and BI model.

Where can I learn more about BI development that should be used in a company?

IT's development and NOT maintainence I need to learn. The tool I'm using is Microsoft BI.

BI Development is a big subject to learn and will take a lot of time understand all the concepts, I would suggest this book as place to start your journey of discovery: The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy
Permalink: http://www.amazon.co.uk/dp/0470640383


Delete trigger mysql on another table I am new on mysql and I am try to create a before delete trigger on mysql.

I have a table call "persons" and I need delete a record on another table called "ldap_entries"

the table "persons" have a ID, unique and this id it is on "ldap_entries" but the columns it is called "keyval".

so I am try to do like this: but not working

CREATE TRIGGER `before_delete`
BEFORE DELETE ON `persons`
FOR EACH ROW
BEGIN
  DELETE FROM ldap_entries WHERE ldap_entries.keyval = OLD.id
END $$

Here is my describe output:

mysql> describe persons;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255) | NO   | UNI | NULL              |                             |
| surname    | varchar(255) | NO   |     | NULL              |                             |
| package    | varchar(64)  | NO   |     | NULL              |                             |
| macaddress | varchar(21)  | NO   |     | NULL              |                             |
| customer   | varchar(255) | NO   |     | NULL              |                             |
| address    | varchar(140) | NO   |     | NULL              |                             |
| phone      | varchar(9)   | NO   |     | NULL              |                             |
| email      | varchar(50)  | NO   |     | NULL              |                             |
| created    | date         | NO   |     | NULL              |                             |
| updated    | timestamp    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.00 sec)

mysql> describe ldap_entries
    -> ;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| dn        | varchar(255)     | NO   | UNI | NULL    |                |
| oc_map_id | int(10) unsigned | NO   | MUL | NULL    |                |
| parent    | int(11)          | NO   |     | NULL    |                |
| keyval    | int(11)          | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Also in response to the comments:

I don't know if InnoDB is being used.
There isn't a FK relationship between the tables.

You need to change the delimter to $$ and then back to ;:

DELIMITER $$
CREATE TRIGGER `before_delete`
BEFORE DELETE ON `persons`
FOR EACH ROW
BEGIN
  DELETE FROM ldap_entries WHERE ldap_entries.keyval = OLD.id
END $$
DELIMITER ;


Table update is causing front end SQL statement to fail We were in a situation where we needed to add many rows of data to our database and decided the most efficient method was to use an SQL INSERT INTO statement. The table was very simplistic. A primary key column plus an additional seven columns, three being date columns. The table doesn't have any constraints or columns with the IDENTITY property. We used the MAX function to determine the highest primary key id assigned and then added 1 to that value for the new row(s) to be added.

Everything appeared to go well on the back end and the front end. However, now when end users enter a new a record via the front end they are getting the error message "SQL Statement failed, this may be caused by network or database issues". This is relevant because it is only happening on a field that is directly associated with the table we updated.

Can anyone offer any insight to what may be happening?

Turned on sql profiler and learned there was a sequence table that is used to determine the next primary key id. Updated this table and deleted the cache. Performed a restart on the server and the problem was solved. In short it was primary key violation.

Thanks for the sql trace idea that led to sql profiler.


Monitor Sql Server 2000 I was wondering if there is a free tool to monitor state of SQL Server 2000. I would prefer a web tool.

I would like to see if some database is accessible, or are there any problems on server, etc. Also I would like not to put to much extra load on SQL Server.

If anybody knows tool like this, please help

Thanks

The Zabbix tool, posted in the comment, is not really working, because the server doesn't run on Windows, only the agent. See info in their requirements page. This means you'd need to install the tool on a Linux machine and only an agent on the Windows machine. Great if you have a heterogeneous environment, not really helpful in a full Win environment.

The same situation is for Nagios, another interesting open source monitoring tool. They say there is a package only for Windows, but the doc page it redirects to doesn't work now.

Of complete free monitoring tools useful for SQL 2000 I'm not really aware, but you can use successfully a pair of:

Perfmon (find more info here)
Server trace (trace specific queries, not just everything) -> actually files that can be open in SQL Profiler
SQL Alerts (info in MSDN article or here, in this SimpleTalk article)

PS: found that a great monitoring tool is also working on SQL 2000. So you'd better try to test Confio Ignite free. It's fantastic for the price :-).

PS2: there is also a small free monitoring tool from Idera -> SQL Check, but I don't know if it works on SQL 2000.


How to get balance of of remaining medicine batch wise I have this query:
select itemcode, batchno, eXpirydate,
    CASE
         WHEN type= 1 THEN sum(qty)
         WHEN type= 2 THEN sum(qty*-1)
    END AS balance
FROM strans
group by itemcode, batchno, EXpirydate,type

It gives me these results:
   itemcode  batchno    EXpirydate    balance
1  0000002  123         2023-01-01     500
2  0000002  123         2023-01-01    -100
3  0000002  321         2024-01-01     500
4  0000002  321         2024-01-01    -150

I want to get the following results:
   itemcode  batchno    EXpirydate    balance
1  0000002  123         2023-01-01     400
2  0000002  321         2024-01-01     350

Basically you cannot GROUP BY the type, because you want the  SUM of both types
SELECT itemcode, batchno, eXpirydate,
    SUM(CASE
         WHEN type= 1 THEN qty
         WHEN type= 2 THEN qty*-1
    END) AS balance
FROM strans
GROUP BY itemcode, batchno, EXpirydate


how does CXPACKETS relates to CXCONSUMER wait types? when running a delete query to a table  that has many foreign keys I get the following waits.

I noticed that I have both CXPACKETS and CXCONSUMER wait types at the same time during of the execution of my query.

the question is:

what is the relation between these 2 wait types (if any)?

CXCONSUMER occurs when a consumer thread waits for a producer thread to send rows.
CXPACKET occurs when trying to synchronize the query processor exchange iterator or getting a required buffer.

CXCONSUMER waits are generally benign, encountered as a normal part of parallel(CXPACKET) execution.


My SQL syntax is error, how to solve it? This is the SQL syntax:

WITH max_budget(value) AS (SELECTMAX(budget) FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = max_budget.value;

I get this error:

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
'max_budget(value) AS (SELECTMAX(budget) FROM department)SELECT budget FROM ' at line 1

How can i solve this problem?, help me guys, thanks.

MySQL doesn't support Common Table Expressions (CTE) so either your mysql tag is incorrect or you've pulled this syntax from nowhere.

To make this work with MySQL, you'll need to rewrite it as derived tables. Note, these sql fiddles use the question that was a duplicate of this as it was at least more interesting to answer.https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql

This is the SQL Server version but I guarantee whatever logic you're attempting could be simplified if you explained what you were attempting to do versus "code doesn't work"

This specific question suffers from two issues: The first and already addressed is that CTEs WITH don't exist in mysql.

The second is simply syntax. You have SELECTMAX instead of SELECT MAX

WITH max_budget(value) AS (SELECT MAX(budget) FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = max_budget.value;

The above is valid syntax for SQL Server and all DBMS that have implemented CTEs. The MySQL equivalent follows

SELECT
    *
FROM
    department
WHERE
    department.budget =
    (
        SELECT MAX(budget) FROM department
    );

This uses a correlated subquery which has the distinct advantage of working in SQL Server, MySQL, PostGres, etc.

You could also write it as a derived table:

SELECT
    department.*
FROM
    department
  JOIN
    (
        SELECT MAX(budget) AS value FROM department
    )  max_budget
  ON
    department.budget = max_budget.value ;


PostgreSQL everyday trigger? I want to add a function like trigger to my Postgres database. The problem is that this function should executed one time each day automatically and change values in the specified table.

Is it possible to carry out this by SQL syntax or is it better to write a bash script with some queries I need?

PostgreSQL does not have time-based triggers.

If you want to store as much of your logic as possible in the database, you could create an ordinary function (not a trigger function) and then invoke it from your favorite job scheduler (cron for me).  If you want to use the database for storing data, not logic, then you could put the queries you want to run in a .sql file and use your favorite job schedule to invoke that file using psql -f for example.  Or, if you want all of your application logic in, for example, a Perl module, you could write a function in your Perl module which connects to the database (reusing whatever connection logic your module already has) and does what you want, then use your favorite job scheduler to invoke that function.

There are many ways to do it, the key is to pick a method which fits in with the your overall design.

It would probably be possible to put the logic into a trigger on a dummy table, then have your favorite job scheduler do a dummy operation on the dummy table to get the trigger to fire.  Pointless, but possible.


mysqldump not exporting all tables I have a Drupal site with a fair amount of content, about 12G in size, with 500+ distinct fields making for almost 1400 tables. We're migrating to a new server environment so I need to be able to export the DB and then import it on the new servers. I've successfully used mysqldump on this system in the past, but the last couple days each export is incomplete, with the data for some tables (in particular, the critical node and users tables) not getting dumped.

The process I follow is to put the site into maintenance mode and then halt any external process that hits the database. I then export the structure first

$ mysqldump -u XXXXXX -p DBNAME --no-data | gzip > structure.sql.gz

and then the table data

$ mysqldump -u XXXXXX -p DBNAME --no-create-info | gzip > data.sql.gz

(The second query is a little more complicated as I tell mysqldump to skip the cache files, the search index etc. but you get the point.)

Both times mysqldump runs without complaint, and the resulting SQL files load without error messages, but looking through the DB afterwards it's clear the data dump process got partway through the list of tables and then stopped after completing a table. Note that it does not stop at the same place each time.

Has anyone encountered this problem? Are there any tricks to get mysqldump to better handle large DBs?

When you have a database with that many tables, what must you remember ???

Use --single-transaction with --no-create-info : This will produce a dump of all table data with the same point-in-time
Setup Replication : You can run STOP SLAVE; and then run your mysqldump, assured that no other activity in going on in the Slave. When dump is finished, that run START SLAVE;.
Don't do any DDL during the dump : Running DDL while dumping from a MySQL Instance can produce some spurious actions (dump reload not completing, wrong structure of table prior to data reload, and things like these)

If your database is all-MyISAM (you start hearing Twilight Zone music) : Don't mysqldump. Shutdown mysql and maker a copy of /var/lib/mysql (LVM Snapshot, scp, rsync, etc). That way, there is no SQL controversy.

You should probably dump data and table structure together, just to be sure.


mysql innodb index: better one big or many compact? I've a question for an expert in how innodb indexes are structured (mysql 8.0.18)

Say I have 4 varchar columns on a table with one billion rows:

country, state, city, attraction

I have queries that look for all or certain attractions, either by country, state, city or attraction name.

query1: "select * from table where attraction like 'asd%' and country = 'X'"
query2: "select * from table where attraction like 'asd%' and country = 'X' and state = 'Y' and city = 'Z'"
query3: "select distinct attraction from table where country='X'"
query4: "select distinct attraction from table where attraction like 'Ux%'

Combined index: (attraction, country, state, city)

This index would cover all 4 queries.

Can I expect similar performance on query1,3,4 in comparison to a specialized index ?

Specialized index1:  (attraction, country)
Specialized index2:  (attraction)

I don't have the time to dive into the details of innodb storage, I hope someone did that already ;)

My main thoughts on this:

More indexes will need more memory and storage (given a billion rows quite a bit), so that's a concern.
If an index that's made for 4 columns is called on a query that only needs one (the first) column or on two (the first two) columns is the data access sequential and as effective as when having small dedicated indexes (that basically contain duplicate data)?

So should I have one index, covering the WHERE requirements of all 4 queries or 3 indexes, each dedicated to the query it serves ?

danblack's post answers the main question about the best index strategy for you queries.

However, I would add a sometimes forgotten optimization of the index strategy, which is implemented in most recent versions of RDBMs (MySQL, MariaDB, PostgreSQL...): covering indexes

Definition of covering index: (from MySQL documentation)

  An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O

That means that your third query:

select distinct attraction from table where country='X'

would benefit more from a (country, attraction) index than a simple (country) index.


SQL Server 2016 ISNULL Returning 1900-01-01 I have a datetime2 column called DocDate that I am trying to evaluate an ISNULL check against to return a blank '' value for instead of NULL.

A snippet of the statement:

SELECT TOP 100
    DocumentId
    ,DocDate
    ,ISNULL(DocDate,'') AS 'DocDateEmpty'
FROM
    DocDetails
WHERE
    DocDate IS NULL

This returns: 1900-01-01 00:00:00.0000000

My WHERE clause clearly brings back only the rows that are explicitly NULL, so it is not an issue of DocDate being an empty string and evaluating to 0.

How can DocDate IS NULL evaluate correctly but ISNULL fails?

Per MSDN, ISNULL should evaluate the literal NULL value correctly and return an empty value.

  Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

This will happen with DATETIME as well as DATETIME2:

DECLARE @dummy DATETIME2 = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

DECLARE @dummy DATETIME = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

Along with DATE and TIME

DECLARE @dummy DATE = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

DECLARE @dummy TIME = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO

I think the easiest way around it is to add a second CONVERT to a string:

DECLARE @dummy DATETIME2 = NULL
SELECT @dummy, ISNULL(CONVERT(NVARCHAR(30), @dummy), N'')
GO

Which returns an empty string as desired.

To answer your question about why, well, let's look at another scenario:

DECLARE @dummy INT = NULL
SELECT @dummy, ISNULL(@dummy, '')

The ISNULL here returns 0. If you're not up on how SQL Server stores and deals with dates, see my Q&A here for more information.

In short, if you run SELECT CONVERT(DATETIME, 0), you get a familiar result. There's no such thing as an empty number, and there's no conversion of an empty string to a numeric expression.

Hope this helps!


Mail alert from MySQL when databases are edited How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits.

I have tried Triggers but its not working.

Server specifications:

Ubuntu 10.04 64 Bit
MySQL Server 5.1.41

script which i have tried

  #!/bin/sh

  tail -f /var/log/mysql/mysql.log | egrep 'INSERT|UPDATE|DELETE|root'
      | mail -s 'backend update' sushanth@example.com

but its not working

MySQL has no particular SMTP setup, mechanisms, or drivers whatsoever built in.

However, there are two basic things you can do the kind of monitoring you want.

Option 1 : You Could Monitor the Binary Logs

If binary logs are enabled, you could write a shell script to call mysql and do SHOW MASTER STATUS; If either the filename or filesize changes, something changed. Once detected, you could send out an email expressing that something changed !!!

Try something like this:

FIRST_READ=1
while [ 1 -eq 1 ]
do
    mysql -h... -u... -p... --skip-column-names -A -e"SHOW MASTER STATUS" > /tmp/ms.txt
    currfile=`cat /tmp/ms.txt | awk '{print $1}'`
    currsize=`cat /tmp/ms.txt | awk '{print $2}'`
    if [ ${FIRST_READ} -eq 0 ]
    then
        SOMETHING_CHANGED=2
        if [ "${prevfile}" == "${currfile}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
        if [ "${prevsize}" == "${currsize}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
        if [ ${SOMETHING_CHANGED} -gt 0 ]
        then
            echo "Something Changed" | mail -s "Something Changed Subject" abc@xyz.com
        fi
    fi
    FIRST_READ=0
    prevfile=${currfile}
    prevsize=${currsize}
    sleep 10
done

Option 2 : You Could Monitor information_schema.tables

You could loop through every table and check its UPDATE_TIME column in information_schema.tables

First collect all table names prepended with database. Then, loop through all the table names and check that entry in information_schema.tables.

Try the following (Any table that changed in the last 10 minutes):

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine IS NOT NULL" > /tmp/TableNamesToPoll.txt

while [ 1 -eq 1 ]
do
    for DBTB in `cat /tmp/TableNamesToPoll.txt`
    do
        DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
        TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
        NEWUPDATE=`mysql -h... -u... -p... --skip-column-names -A -e"SELECT IFNULL(update_time,NOW() - INTERVAL 100 YEAR) > (NOW() - INTERVAL 10 MINUTE) UpdatedRecently FROM information_schema.tables WHERE table_schema='${DB}' AND table_name='${TB}'"`
        if [ ${NEWUPDATE} -eq 1 ]
        then
            echo "Something Changed in ${DBTB}" | mail -s "Something Changed Subject" abc@xyz.com
        fi
    done
    sleep 5
done

These are just skeleton scripts to detect changes. For option 1, you can do mysqlbinlog against the current binary log and see the SQL that was executed in whatever timeframe you need. For option 2, you can change the SQL to retrieve the datetime stamp of the last update for a given table.

UPDATE 2011-06-29 06:30 EDT

Option 3 : You Could Monitor the general log

Interestingly, you could activate the general log. What's even more intriguing is that you can activate it a MySQL Table. The template to the general log as a table already exists in /var/lib/mysql/mysql as general_log.CSV. Here are the steps:

Step 01) Add these to /etc/my.cnf

[mysqld]
log-output=TABLE
log

Step 02) service mysql restart (general_log is a CSV table after restart)

Step 03) Run these commands to convert the general_Log from CSV to MyISAM

SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

Step 04) Move the general_log file to a huge disk volume that can accommodate a fast growing log table

Example: If you have the following disk layout

[root@iml-db10 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg1-root  117G  3.2G  108G   3% /
/dev/mapper/vg2-data01
                      1.7T  688G  877G  44% /data
/dev/sdc1             3.6T   36G  3.4T   2% /backup
/dev/sda1              99M   18M   77M  19% /boot
tmpfs                  95G     0   95G   0% /dev/shm
none                   16G   51M   16G   1% /var/tmpfs

Perform these steps to move the general log table:

mkdir /backup/general_log
mv /var/lib/mysql/mysql/general_log.MY* /backup/general_log/.
chown -R mysql:mysql /backup/general_log
ln -s /backup/general_log/general_log.MYD /var/lib/mysql/mysql/general_log.MYD
ln -s /backup/general_log/general_log.MYI /var/lib/mysql/mysql/general_log.MYI

When do make sure the symlinks exist

[root@db1]# ls -l /var/lib/mysql/mysql/general*
-rw-rw---- 1 mysql mysql 8776 Jun 25 15:53 /var/lib/mysql/mysql/general_log.frm
lrwxrwxrwx 1 root  root    35 Jun 25 18:33 /var/lib/mysql/mysql/general_log.MYD -> /backup/general_log/general_log.MYD
lrwxrwxrwx 1 root  root    35 Jun 25 18:32 /var/lib/mysql/mysql/general_log.MYI -> /backup/general_log/general_log.MYI

Step 05) Run this SQL command

SET GLOBAL general_log = 'ON';

That's it. You should have the general_log as a MyISAM table

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' DATA DIRECTORY='/backup/general_log/' INDEX DIRECTORY='/backup/general_log/'
1 row in set (0.00 sec)

All you need to do is poll the general_log table every 15 minutes via crontab running this query

SELECT COUNT(1) UpdateCount FROM mysql.general_log
WHERE LOCATE('UPDATE',argument) > 0
AND event_time >= (NOW() - INTERVAL 15 MINUTE);

SELECT COUNT(1) DeleteCount FROM mysql.general_log
WHERE LOCATE('DELETE',argument) > 0
AND event_time >= (NOW() - INTERVAL 15 MINUTE);

WARNING : Entries will pile up quickly. Delete all events keeping the last 3 days. Run this in a crontab every night a midnight

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
DELETE FROM mysql.general_log WHERE event_time < NOW() - INTERVAL 3 DAY;
SET GLOBAL slow_query_log = @old_log_state;

Give this a Try !!!


The problem with your provided query is that you're doing an INNER JOIN. Because there are no rows with a parentid of 5, the subquery would return NULL on those rows and be excluded.

What you want is a LEFT JOIN, something like this (sqlfiddle):

SELECT t.*, parentchildrelation.parentid, childs
FROM tbl_info t
LEFT JOIN
  (SELECT
    parentid,
    COUNT(DISTINCT id) AS childs
  FROM
    tbl_info
  GROUP BY parentid) AS parentchildrelation
  ON t.id = parentchildrelation.parentid
WHERE t.no_daughter != parentchildrelation.childs
   OR (t.no_daughter > 0 AND parentchildrelation.parentid IS NULL)
GROUP BY t.id, t.parentid;

I included more columns, because where id=5 and parentid=2 has two different values for no_daughter. You should be able to adjust those as needed.


Mongo DB Memory Utilisation freezes app needs restart I am running a project using Mongo DB and Django, I have 2 separate instance for my Django project and database each 16GB ram t2xlarge ubuntu ec2 instances.
I am facing issue that within a day or two usage memory on DB server goes above 6GB and on app server it remains below 1GB but freezes application and need to restart machines to recover.
Any suggestions and help would be really appreciated.

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


Walking a BTREE index as far as possible in MySQL Suppose one has a column of words on which one builds a BTREE index:

CREATE TABLE myTable (
  words VARCHAR(25),
  INDEX USING BTREE (words)
);

LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO TABLE myTable (words);

And now one wants to find the records which share the longest common prefix with some search query, e.g. 'foobar'.  I thought to do the following:

SELECT DISTINCT words
FROM   myTable
WHERE  words LIKE CASE
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'f%') THEN '%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'fo%') THEN 'f%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'foo%') THEN 'fo%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'foob%') THEN 'foo%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'fooba%') THEN 'foob%'
  WHEN NOT EXISTS (SELECT * FROM myTable WHERE words LIKE 'foobar%') THEN 'fooba%'
  ELSE 'foobar%'
END

Which is fine: it's very readable and performant; and it can easily be generated in application code.

However, this search should be even simpler to resolve: just walk through the index tree according to the search term until a branch does not exist, then return all the results that branch from the current node.

Granted that walking a path through the index only once instead of multiple times is probably a needless micro-optimisation, but it feels as though it should be possible:  is it?

If you really want to walk just the b-tree index, using the innodb_ruby project can help
http://blog.jcole.us/2013/01/14/efficiently-traversing-innodb-btrees-with-the-page-directory/

I think your logic is reversed in your query. It'll start with the shortest word.

How I would handle it is this

DROP PROCEDURE IF EXISTS `find_longest_prefix`;

DELIMITER $$

CREATE PROCEDURE `find_longest_prefix`(IN `word` varchar(255), OUT `word_prefix` varchar(255))
BEGIN
    SET max_sp_recursion_depth = 255;
    SET @nextWord = LEFT(`word`, LENGTH(`word`)-1);

    SELECT COUNT(DISTINCT `words`) FROM `myTABLE` WHERE `words` LIKE CONCAT(`word`, '%') INTO @word_count;

    IF (@word_count > 0)
    THEN
        SET `word_prefix` = `word`;
    ELSE
        IF (LENGTH(@nextWord) > 0)
        THEN
            Call `find_longest_prefix`(@nextWord, `word_prefix`);
        ELSE
            SET `word_prefix` = '';
        END IF;
    END IF;
END$$

DELIMITER ;

This uses the fact that finding a miss on a btree is fast so we just loop around calling recursively until we have a hit.

Examples

No results

mysql> CALL find_longest_prefix(';autobon', @prefix);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @prefix;
+---------+
| @prefix |
+---------+
|         |
+---------+
1 row in set (0.00 sec)

A few results

mysql> CALL find_longest_prefix('autobon', @prefix);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @prefix;
+---------+
| @prefix |
+---------+
| auto    |
+---------+
1 row in set (0.00 sec)

And as you can see, the data is correct:

mysql> SELECT * FROM myTable WHERE words LIKE 'auto%' OR words LIKE ';auto%';
+----+-----------------+
| id | words           |
+----+-----------------+
| 19 | AUTOCOMMIT      |
| 20 | AUTOEXTEND_SIZE |
+----+-----------------+
2 rows in set (0.00 sec)

This method should be much faster then actually checking every step before the longest and getting a lot more returned data.

It should be easy for you to have the stored proc SELECT the rows once it finds the correct prefix and return them if you'd prefer.


Schema for scarce goods I have following objects:
(Rooms) which are (available in a certain period of time [in days]).
During the time of availability they can be (booked) by (users). There are basically these four tables. I know that there is a foreign key in (booked) for (users), I also have a foreign key for (rooms) in (booking) and I do have a foreign key of the (room) in (availability). But how can I connect my (availability)-table to avoid that the good is booked on a period of time it isn't available?

The refered question didn't lead to an answer in my matter.

To summarize:
(Rooms) - (availability) - (booking) - (User)

I have a hard time figuring out how I set the foreign keys for this transaction.

The most relational way is adding a day table. You will be able to join room, availability and day to find out which rooms are available for a certain period.


MySQL Master-Slave setup: master is not indexed and slave is indexed Is it possible to have a MySQL master-slave setup where the master has no indexes (for faster insertion) and the slave will be indexed?

Thanks in advance!

yes.

But 1) you have more trouble later on when doing maintenance on the tables (replicating alter statements might caue issues) and ypu might have issues when havin primary/unique/foreign keys on slaves only as it could reject data the master accepts

And 2) Slaves still have to insert the data and build indexes, yes the maser has a tiny bit less work, the overall system not, though.


Different execution times running a query I have a view on a table that has 35 million rows. When I'm selecting from the view for the first time, the execution time is about 35 seconds. When I run the query again repeatedly, it is much faster, down to about 3 seconds each time.

Is there a generic explanation of why I get different execution times in scenarios like this?

All operating systems and all applications use a concept called "caching".

It means - when the data is first read from a slow memory device (like, a hard disk), it is saved in a fast memory device (like, RAM) for some time, to facilitate faster lookups.

The same applies to RDBMS.

First time the data blocks that build up your query results are read from disk, second time they are read from memory.

Details can be explored using OS and database tools. If you specify what RDBMS and what OS you are on, we can help you get the details. For PostgreSQL it's about EXPLAIN command.