Adding field constraints on child tables based on parent table I need to add constraints to a particular field in all the entries of a child table with a foreign key to a single row in parent table. What is the best way to do it?

For example, my parent table has

    Id Money Name
    -- ----- ----
     1  30    xyz
     2  40    abc

And the table referencing this

    Id Cost Person
    -- ---- ------
     1  10    1
     2  10    1
     3  20    2

Here Person is the foreign key and I need to put constraint in Cost based on Money, for instance, the sum of all cost should be always lesser or exactly equal to the total Money.

A CHECK constraint can only reference the table or column it's attached to. You'll have to validate the values as part of the query that's inserting into the referencing table. Something like this:

DECLARE @Cost INT
DECLARE @PersonID INT

INSERT INTO ChildTable
        ( cost,person )
SELECT @Cost
       ,@PersonID
FROM ParentTable PT
WHERE pt.id = @PersonID
      AND ((SELECT SUM(cost) FROM ChildTable CT WHERE CT.id = PT.id) + @Cost) < PT.money

You could also create a DELETE trigger that queries the other table and immediately removes the row if it fails the criteria. However that is a very sub-optimal choice.


Operand data type nvarchar is invalid for multiply operator error I am getting this error when I run (this part) of the following query in SQL Management Studio. Can someone help? Do I need to change the query to see this as a different value?

sum(a.UNITCST * a.PRICEQTY) AWP,
sum(a.CLTINGRCST) INGRD_CST,
sum(a.CLTDISPFEE) DF

Some of your fields are text (char) fields, you should use CAST/CONVERT or TRY_CONVERT before to use it on numeric operations.

CREATE TABLE TEST (A VARCHAR(10), B VARCHAR(10), C DECIMAL(18,2));
INSERT INTO TEST VALUES
('10.2', '20.2', 5.5),
('5.2', '7.4', 10.0);
GO

2 rows affected

SELECT SUM(A * C) F1,
       SUM(B),
       SUM(C)
FROM   TEST;
GO

Msg 8117 Level 16 State 1 Line 2
Operand data type varchar is invalid for sum operator.

SELECT SUM(TRY_CONVERT(DECIMAL(18,2), A) * C) F1,
       SUM(TRY_CONVERT(DECIMAL(18,2), B)) F2,
       SUM(C) F3
FROM   TEST;
GO

F1       | F2    | F3
:------- | :---- | :----
108.1000 | 27.60 | 15.50

dbfiddle here


Altering a column: null to not null I'm having to change a column in MYSQL to NULL to NOT NULL, but I get the error excutar the syntax. I want to change only the last column, already performed some research but have not found the solution, which can be simple but I'm new to database and had not realized this until the moment. can anybody help me?

Example held: Quickly change NULL column to NOT NULL

ALTER TABLE glpi_ticketsatisfactions ALTER COLUMN comment BIGINT NOT NULL;

My table:

Error:

  [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BIGINT NOT NULL' at line 1

CREATE TABLE:

CREATE TABLE glpi_ticketsatisfactions
( id int(11) NOT NULL AUTO_INCREMENT,
  tickets_id int(11) NOT NULL DEFAULT '0',
  type int(11) NOT NULL DEFAULT '1',
  ate_begin datetime DEFAULT NULL,
  date_answered datetime DEFAULT NULL,
  satisfaction int(11) DEFAULT NULL,
  comment text COLLATE utf8_unicode_ci,
  PRIMARY KEY (id),
  UNIQUE KEY tickets_id (tickets_id)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The syntax you used is from a SQL Server example, not from MySQL. It would be good to check the MySQL documentation about ALTER TABLE syntax.

In MySQL, the ALTER COLUMN subclause can only be used for setting or dropping the default value of the column (SET DEFAULT literal or DROP DEFAULT).

You need to use either CHANGE COLUMN (note that column name is doubled as you could use this to change its name):

ALTER TABLE MyTable
    CHANGE COLUMN comment comment BIGINT NOT NULL;

or MODIFY COLUMN:

ALTER TABLE MyTable
    MODIFY COLUMN comment BIGINT NOT NULL;

Now, there are 2 more problems, beside the syntax:

Seeing the CREATE TABLE, converting a "comment" column from TEXT to BIGINT does not make much sense. I'll assume that you want to keep it TEXT and only make it NOT NULL.
There might be NULL values already in the table. If that's the case, the statement will fail with errors. So, you have to first UPDATE those values to some non-null value (say the empty string) and then modify the column:

So our statements become:

UPDATE glpi_ticketsatisfactions
SET comment = ''
WHERE Comment IS NULL ;

ALTER TABLE glpi_ticketsatisfactions
    MODIFY COLUMN comment TEXT COLLATE utf8_unicode_ci NOT NULL ;


Is it a good or bad idea to use "ON UPDATE CASCADE ON DELETE CASCADE" for foreign keys? Why does this mechanism exist at all? I understand what foreign keys are, and have made a point of including them wherever they make sense for all my database tables that I design.

However, something which has always confused me is whether or not I should be explicitly setting the "ON UPDATE" and "ON DELETE" features (in lack of a better term). Example:

CREATE TABLE "test1"
(
    id              serial,
    referenceid     integer,
    FOREIGN KEY     (referenceid) REFERENCES "othertable" (id) ON UPDATE CASCADE ON DELETE CASCADE
)

This code goes out of its way to explicitly add the technically "unnecessary" part: "ON UPDATE CASCADE ON DELETE CASCADE".

Since this is not done by default, there must be a reason for this! After all, the default behaviour is always (or at least should always be) the most commonly needed behaviour:

CREATE TABLE "test2"
(
    id              serial,
    referenceid     integer,
    FOREIGN KEY     (referenceid) REFERENCES "othertable" (id)
)

In the test1 table, as I understand it, if the "othertable" either changes the "id" column values, or deletes any record(s), that means that the referenced records in the test1 table will either be updated or deleted. This seems, on the surface, like what should be the default behaviour.

In the test2 table, again as I understand it, if the "othertable" either changes the "id" column values, or deletes any record(s), that means that PostgreSQL will refuse to perform the query if there are records in test2 which reference the ones being modified.

I'm basically confused about the entire concept of "ON UPDATE" and "ON DELETE". Why would one ever want a query to be refused like that? And "CASCADE" isn't even the only option (besides none); there are multiple other values you can use which cause various behaviour (which I don't understand).

Since there is a stated relationship between the tables (through the foreign keys), isn't the whole point that you want them to remain consistent? So why you not want it to "CASCADE" if there are changes made to the "master" table?

This might be similar to how I could never understand why object-oriented programming had "security measures" in the code, disabling you from directly changing or retrieving an object's properties and being forced to go through "getters" and "setters". I mean, if something can execute queries in your database, isn't "all lost" anyway? They can just do:

DELETE FROM table1/table2 CASCADE

... or something like that.

The ON UPDATE/ON DELETE mechanism seems almost like the database engineers could not decide on the best behaviour and instead put this on the user of the product instead. For me, it adds a lot of confusion and anxiety.

It should be noted that I have used the "test2" style code many times in the past, only to realize that I cannot update or delete records where it made sense. That's why I started using "ON UPDATE CASCADE ON DELETE CASCADE" in the first place, after asking and learning about it.

So why isn't this the default and even the only behaviour for a database? Why would you ever want a query to update/delete your "master records" to fail?

I'm not sure about ON UPDATE CASCADE. If you find yourself needing this sort of cascaded update then that is perhaps a "code smell" in your database design. In theory your primary key should be static so changes that need cascading shouldn't need to happen. Perhaps it was added as a logical step along from ON DELETE CASCADE. It is at least safer than cascading deletes.
The existence of ON DELETE CASCADE makes more sense: while PKs shouldn't really change things do often get deleted. The cascading is simply a convenience, it saves you from having to write code to drop child entities manually when getting rid of a parent. Further more it might be considered safer than implementing this in other logic because the database is taking care of transactional consistency, deadlocks, and so forth, so the operation should (bugs permitting) be guaranteed atomic. If you implement your own "find children, delete, then delete parent" which may have to be nested, you have to do some legwork[!] to ensure that if there is an error part way through there is no way that you delete the great-great-great-grand-children of a row but leave the rest standing (leaving a partly deleted entity which could cause difficult to diagnose issues later).
[!] Taking appropriate locks, preferably not by locking whole tables, ensuring transaction isolation settings are right, ... - it isn't as simple as it might first look.
Why Not Cascade?
As I said above, I consider a need to cascade updates routinely to be a bit of a design smell. You shouldn't need to change a primary key value during normal operations.
I'm very wary of cascaded deletes, despite the danger of bugs in more manually deleting complex structured entities. Too often you see inexperienced people perform UPSERT operations[*] using a DELETE-then-re-INSERT method, even when the DB supports single-statement upsert operations[^], which damages your data if cascaded deletes are enabled: the delete removes the children too, and they don't get put back by the subsequent insert.
Also, in a lot of cases with real data you don't actually want a cascaded delete. For example: if a manager leaves a company you don't want to delete their sub-ordinates because assigning a new manager first was forgotten, or prevented by a bug.
[*] in postgres via INSERT ... ON CONFLICT ... but this is not standard and quite different syntax[†] is used elsewhere
[^] either because they aren't aware of the available syntax, or are avoiding it in order to be cross-DB compatible
[†] MERGE can be used in Microsoft's TSQL for the same effect, mySQL[‡] supports INSERT ... ON DUPLICATE KEY ...
[‡] mySQL also supports REPLACE INTO, but IIRC that is just syntactic sugar for delete+insert so has the same dangers


How to test mysql for changes I'm OCD person, I think I accidentally dropped something in my mysql.

Problem is I've around 50 databases, and I cannot manually check everything :(

Is there a way to check for these kind of changes? I tried to look at timestamps in /var/lib/mysql but it didn't help, I was looking at directory timestamps.

I also ran:

SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 1 day)

it only returned databases changed in last few secs.

After much floundering around with the information_schema.tables table, I finally discovered the answer to your problem - update time doesn't work for InnoDB. See the points here by a.out and ajacian81.

The only way you can do this is to implement logging tables (as suggested by Bill Karwin, a bit of a MySQL/database guru).

The only other thing that I can think of is to use a backup and use a diff programme to compare the text output of queries from your current and backup database. Horrible I know, but there are tools which can do this if it's critical. There's MySQL's own (MySQLdiff - in utilities), you could try Liquibase or Flyaway (which I believe have this functionality) or Google "database diff programmes mysql".

I also found this post helpful in realising that the information_schema tables are dynamic.

Maybe I'm a bit OCD too (can't let this go! :-) ) but I also found this thread - check out the answer by Jacket - Percona may be the way to go if you are in real need of this functionality. However, it's obviously too late for your particular situation. I think it's backups for you :-(.


How to determine Windows Server start time using T-SQL I am looking for a way to find out when the Windows Server was last started using only t-sql commands.

I am trying to stay within the default security configurations (i.e. I don't want to enable xp_cmdshell)

You can use the ms_ticks column from sys.dm_os_sys_info. This is the number of milliseconds since the computer was started.

SELECT DATEADD(SECOND, (ms_ticks/1000)*(-1), GETDATE())
    FROM sys.dm_os_sys_info

Will get you this information.


(Partially) clone a huge DB for DEV purposes I want to setup a dev and test environment for website that has a huge MySQL database. Since this DB is so big, it is quite inconvenient to clone it 1-on-1, because it simply takes too long and uses too much storage.

My ideas was to only dump / copy certain records, matching some SQL. For example, only the last 1000 users and last 1000 products in the DB, assuming we only need this much data for testing and development.

My questions are:

How to best go about this, keeping in mind the DB is live at the time of the dumping (referential integrity), and keep it from going offline
The data is complex and has many joins, making insert into ... select ... statements a bit complex, but still possible.

My ideas was to use mysqldump to set up only the structure (no data), then use a long list of insert into ... select ... statements to fill the tables 1 by 1.

Only issues I am uncertain of is the referential integrity and processing the tables in the right order, and maybe there is a better way?

The DEV environment is my personal laptop, so not a server with huge disks.

Answers originally left in comments

A test environment should probably have a full copy and could be populated form the backups (and it double checks they are working right). – danblack

  My ideas was to use mysqldump to set up only the structure (no data), then use a long list of insert into … select ... statements to fill the tables 1 by 1.

Use SELECT .. INTO OUTFILE and LOAD DATA INFILE instead of building SELECTs. Plus processing the tables in the right order of course. – Akina


Recreating the control file I have experienced a problem with flashback and the solution is to recreate the control file (the error was ORA-01207).

So to do this I tried

 alter database backup controlfile to trace as '/tmp/trace.sql';

This creates a file that instructs one to either use set one or set two. i.e. with or without resetlogs.

How does one determine what set is the most applicable? Is there any tests that one needs to perform to find out?

From /tmp/trace.sql:

  The first set opens the database with the NORESETLOGS option and should be used only if the current versions of all online logs are available. The second set opens the database with the RESETLOGS option and should be used if online logs are unavailable.

If you're only recreating the controlfile you should be able to perform a complete recovery. i.e. without resetlogs. If you are missing some online redo logs or if they are not complete, then it will be a partial recovery, and RESETLOGS will be required.


Can a function be immutable while calling a volatile function? I have a function that does some mathematical calculations. It calls another function, which is volatile. I would like to know if the calling function can be declared as either immutable or stable, or it must necessarily be volatile as well.

The documentation describes IMMUTABLE as

  IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. [...]

This means you can do the following:

CREATE OR REPLACE FUNCTION immutablerandom()
    RETURNS integer
    LANGUAGE plpgsql
    IMMUTABLE
AS $$
DECLARE a numeric;
BEGIN
    a := random();
    RETURN 1;
END;
$$;

random() is a volatile function, and immutablerandom() fulfills the criteria of the definition of IMMUTABLE.  If this actually makes sense is a different thing - you have to discard the results of the volatile function altogether, and in practice I cannot see a case where I could use it.


How to Log All mysql queries into log file? I am using Mysql 5.6.12 under Wamp server environment. Now I want to log All queries into ".log" file, the queries which are running by PHP or from PHPMyAdmin, I want to log them...

[mysqld]
# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /usr/log/slowquery.log
log_queries_not_using_indexes = 1

#Set General Log
general_log = on
general_log_file=/usr/log/general.log

Note that enabling general_log on a production server has overhead you should avoid it. You can check problematic queries from slow log.


how to JOIN on not null Im trying to create referals system. I am doing that by passing into during database every registaration all referals (first, second and third "stage").

to clarify, if user ID 3 registers from ref. of user ID 2, and user ID 2 was registered from ref of ID 1, the values for user ID 3 would look like:

`r1` = 2, `r2` = 1, `r3` = NULL

To read from database who is registered using some id's ref link with whole path, I figured out this query:

SELECT `sub`.`id` AS first,`sub2`.`id` AS `second`,`sub3`.`id` AS `third`

FROM `users` AS `usr`

JOIN `users` AS `sub` ON `sub`.`r1` = `usr`.`id`
JOIN `users` AS `sub2` ON `sub2`.`r2` = `usr`.`id`
JOIN `users` AS `sub3` ON `sub3`.`r3` = `usr`.`id`

WHERE `usr`.`id` = :CurrentUserId ;

(:CurrentUserId is PDO placeholder for PHP for int value)

Although, its problematic, as it works only for accounts with all 3 refers into past, so people with only first line or first and second line, but without third will not be listed. In other words, that would mean, if there is user, that has following values

r1 = 2, r2 = 1, r3 = NULL

is not listed. It works if r3 is equal to something.

I could work it around by sending 3 queries to database, but I am sure, that there is better way.

(Im not very expirienced with databases)

Use left joins instead of inner joins so that records from users will still display even if r1, r2, and r3 are null:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.r1 = usr.id
LEFT JOIN users AS sub2 ON sub2.r2 = usr.id
LEFT JOIN users AS sub3 ON sub3.r3 = usr.id

WHERE usr.id = :CurrentUserId ;

However, based on your example I think you may need the following query instead, changing the join conditions so that sub, sub2, and sub3 represent referred users:

SELECT sub.id AS first,sub2.id AS second,sub3.id AS third

FROM users AS usr

LEFT JOIN users AS sub ON sub.id = usr.r1
LEFT JOIN users AS sub2 ON sub2.id = usr.r2
LEFT JOIN users AS sub3 ON sub3.id = usr.r3

WHERE usr.id = :CurrentUserId ;


Restoring all rights for root I have root access to the database on VPS, however it looks like root has no rights.

When I tried to create a database, it said :'user 'root' access denied to database..'

When I wanted to select users from mysql.users it said

'SELECT command denied to user 'root'

I wrote 'SHOW GRANTS', it gave
'GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD <secret>' in 'GRANTS FOR 'root@%'' column

Ideas?

How are you connecting?  Is the client on the same machine?  Same client?  You seem to be coming in from a different machine.  % is the world; you do not want root to get in that way.

If this is a Cloud setup, you were probably told what IP address to come in from, and that may be the only way to come in.

We need to know who set up your access and the details (except password).


what is the best configuration for innodb_flush_log_at_trx_commit? There are 3 different configuration is available for innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit = 0

innodb_flush_log_at_trx_commit = 1

innodb_flush_log_at_trx_commit = 2

Which is the best configuration for innodb_flush_log_at_trx_commit?

what is the difference between this three configuration?

innodb_flush_log_at_trx_commit  has default value of 1 - To disk on each transaction commit. is extremely expensive. Your applications can get incredible performance boost if you change the value to 2 - To OS cache on each transaction commit. To disk every second.

With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.


RapidMiner Not Connecting To SQL Server Database Connection I recently installed RapidMiner and tried connecting the application to my MS SQL database connection via the Manage Database Connections feature. I have followed video tutorials on integrating SQL Server but i keep getting errors on connecting to localhost, and port 1433.

I have checked to see if SQL Server browser was running. It is. My TCP/IP connection is enabled, and also my default port is 1433. So what can be the problem?

I use mixed authentication with SQL Management Studio, i added the same in the Manage DB Connection form. But i keep getting an error:

  "The TCP/IP connection to the localhost, port 1433 has failed. Error:
  Conection refused. connect"

I tried using Microsoft SQL Server (JTDS) as DB system (I downloaded JTDS from Sourceforge),  but i keep getting a

  "Network error IOException: Connection refused:connect"

Please, I'd really appreciate help because it's left me frustrated as i need this application to work for some project i am undertaking.

Thanks

Check if TCP-IP is enabled with SQL Server Configuration Manager. Run SQL Server Configuration Manager for different versions of SQL.

Then Edit TCP-IP and go to IPAll and set TCP-IP port to 1433 and Dynamic port to 0, then restart SQL Server service.


Ubuntu / MySQL - How to choose from the various downloads? I have Ubuntu 12.04.2 LTS.  I would like install MySQL 5.5.29.

So I go to the MySQL 5.5 archive page
http://downloads.mysql.com/archives.php?p=mysql-5.5&o=other
and scroll down to section 5.5.29

Here I see the following options:

There appear to be duplicates here, so I hover the mouse over the links.

mysql-5.5.29-linux2.6-x86_64.tar.gz
mysql-5.5.29-linux2.6-i686.tar.gz
mysql-5.5.29-debian6.0-x86_64.deb
mysql-5.5.29-debian6.0-i686.deb
mysql-5.5.29.tar.gz
mysql-5.5.29.zip

My operating system, Ubuntu 12.04.2, is 64 bit, so that rules out #2 and #4.  I assume the .zip is for Windows, so that rules out #6.

That leaves #1, #3 & #5.

Please explain how I choose from these remaining options?
Perhaps Ubuntu is considered similar to Debian so thats what I should choose?
Or should I select the generic download?

Well, I have some advice for you.

Given https://help.ubuntu.com/12.04/serverguide/mysql.html,

  As of Ubuntu 12.04, MySQL 5.5 is installed by default

Go with the 5.5.x version they supply, and update accordingly.

If you don't want to do that and really want 5.5.29 for a specific reason, go for mysql-5.5.29-debian6.0-x86_64.deb as Ubuntu was derived from Debian.

You may have to remove the version of MySQL that came with your ubuntu installation.


In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped? In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped? Also, what happens when a new primary key is added?

If there is a not null unique key, that will be used as an internal primary key. If there is no candidate for a primary key, a 6-byte internal identified will be used as a hidden primary key that, unlike Oracle's ROWID, it cannot be accessed by the user.

As that implies that potentially an arbitrary value will be used to cluster the data, it is always recommended to have an explicit primary key.

When a new primary key is added, essentially the table (B+tree) is fully rebuilt around the new primary key value, including the secondary keys. That is why any ALTER TABLE operation involving the primary key will be very costly (and in MySQL 5.6 cannot be done online).


Run a Stored Procedure in DB2 (which has dynamic DDL) under the Definer rights For our Datawarehouse applications, we want to perform ALTER TABLE ALTER CONSTRAINT <constname> NOT ENFORCED, before the load. Perform the load and then ALTER TABLE ALTER CONSTRAINT <constname> ENFORCED.

Tables are owned by DBA (SYSADM) and the application id doesn't have rights to perform ALTER.

So, the DBA asked me to create a Stored Procedure which will be owned by DBA and will be invoked by the application id.

For this, we explored DYNAMICRULES option of DB2.

So before the procedure is created by the DBA, the below call is made by the DBA

CALL SET_ROUTINE_OPTS('DYNAMICRULES RUN)
Procedure is created under DBA owner
Execute grant privilege to the application id

When I called the procedure using the application id, the error that I get is

  SQL0551N  "App id" does not have the required authorization or privilege to
  perform operation "ALTER TABLE" on object "DBA.Object_name".

The documentation that I referred is

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0007210.htm

Any help on this would be very much appreciated, as I feel that Dynamic DDLs can't be allowed to execute using Definer rights by a invoking auth id.

In general, dynamic SQL statements can be executed with definer (binder) rights. However, certain DDL statements, including ALTER TABLE can only be executed dynamically under DYNAMICRULES RUN.

I'm not sure why you chose to run the statement dynamically. DB2 allows static execution of DDL statements, in which case they execute with the definer authority.

A simple example:

db2inst1@blusrv:~> db2 "create procedure test begin create table blah (f1 int); end"
DB20000I  The SQL command completed successfully.
db2inst1@blusrv:~> db2 "call test"

  Return Status = 0
db2inst1@blusrv:~> db2 describe table blah

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
F1                              SYSIBM    INTEGER                      4     0 Yes

  1 record(s) selected.


What is {Oracle}... {Oracle Off}? I inherited an old script, that should, as part of a pl/sql routine, create a temporary index for me. I tried it on Oracle 11.2.0.4.0, but it does not accept the options.

CREATE INDEX x ON y(gid) {IN IndexTableSpace} {ORACLE}COMPUTE STATISTICS{ORACLE OFF};

However I am not familiar with the options listed in {}. Can somebody enlighten me as to what these are? And would the following be a updated equivalent?

create index x on y(gid) tablespace IDX_TS;

Any help is appreciated!

I guess these {...} get replaced somewhere in the code with something that makes sense.

This is indeed an old script because compute statistics is not quite state of the art anymore.

Go ahead and create the index with the sql you just posted:

create index x on y(gid) tablespace IDX_TS;

And then analyze the table with dbms_stats:

begin
  DBMS_STATS.GATHER_TABLE_STATS(
       ownname          => '<owner of the table>'
      ,tabname          => 'Y'
      ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
      ,method_opt       => 'FOR ALL COLUMN SIZE AUTO'
      ,granularity      => 'AUTO'
      ,cascade          => TRUE
    );
end;
/

EDIT: "cascade => TRUE" will analyze indexes of the table as well.


If view A is built atop other views and tables, does a user need permission to only view A, or everything down? I'm talking about SQL Server in particular. I suppose a user tried it and got an error (you don't have select permissions on hidden underlying view B not explicitly mentioned in query). The main view A joins a table and another view (view B) together, and they don't have permission for view B.

I'm not in charge of permissions for the database, but I'm curious how it works.

Do you need to grant permissions to every object built underneath the views? Or just the view itself? Or either one?

I thought one of the purposes of views was a snapshot of underlying objects that you DON'T want to give full permissions for. That's what is unusual to me.

Do you need to grant permissions to every object built underneath the
  views? Or just the view itself? Or either one?

Permissions need to be granted to the person executing the query for every object referenced by the view. Except if they are owned by the view owner. In that eventuality Ownership Chains come into play.

  When an object is accessed through a chain, SQL Server first compares
  the owner of the object to the owner of the calling object. This is
  the previous link in the chain. If both objects have the same owner,
  permissions on the referenced object are not evaluated.

The below creates two tables. T1 (owned by TestUser1) and T2 (owned by TestUser2) and a view that references both tables and is owned by TestUser1.

A third user is granted SELECT permissions on the view but none of the underlying objects. Selecting from the view initially fails for them. However it succeeds after they are granted select permissions on T2. There is no need to grant them permissions on T1 as this is owned by the same user as the view.

Setup

CREATE USER TestUser1 WITHOUT LOGIN;
CREATE USER TestUser2 WITHOUT LOGIN;
CREATE USER TestUser3 WITHOUT LOGIN;

CREATE TABLE dbo.T1(C INT);

ALTER AUTHORIZATION ON dbo.T1 TO TestUser1;

CREATE TABLE dbo.T2(C INT);

ALTER AUTHORIZATION ON dbo.T2 TO TestUser2;

GO

CREATE VIEW dbo.V
AS
SELECT T1.C
FROM dbo.T1 CROSS JOIN dbo.T2

GO

ALTER AUTHORIZATION ON dbo.V TO TestUser1;

GRANT SELECT ON dbo.V TO TestUser3;

Test 1 (Fails)

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

  The SELECT permission was denied on the object 'T2', database 'Foo',
  schema 'dbo'.

Test 2 (Succeeds)

GRANT SELECT ON dbo.T2 TO TestUser3

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

Cleanup

DROP TABLE dbo.T1, dbo.T2
DROP VIEW dbo.V
DROP USER TestUser1
DROP USER TestUser2
DROP USER TestUser3


MongoDB replication to local hard drive MonogDB's dynamic schema has made me thinking about replacing MySQL. I eventually want to use MongoDB as the default for all new app developments.

But this project runs on only 1 dedicated server (with 2 hard drives).

Docs about "MongoDB for production" recommends multiple servers. This makes me wonder if MongoDB is only suited for large commercial projects?

Anyways... I am wondering if the live database data can be replicated to the second hard drive as a replica of the original data (to recover from any data corruption on the primary).

I did take a look at mongodump, but I am wondering if there is anything more realtime like the replica set.

Currently my server backs up everything to the second hard drive & rsync is scheduled to sync everything in this drive to a remote machine.

Any thoughts against the use of MongoDB in a single server environment is also appreciated. The database size is around 7GB.

Thanks

Replication in MongoDb is a simple process to setup and I configured it locally during an online course I completed via the 10gen website, obviously this will fail over without an issue to your other instance but depending upon what fails on your machine will it be of any use!

I'd advise working your way through the free course they have it as it will solve 90% of the queries you will have with evaluating the product (replica sets are part of this)

We had it my old company and did run it on several machines for full redundancy and automatic failover to another node, you'll need a witness too.


MySQL performance, UNION then JOIN, or JOIN on each part of the UNION? Which does the optimiser handle better?

SELECT TBL.*,J.* FROM (
    (SELECT ...,T.ThingId FROM ...)
    UNION
    (SELECT ...,T.ThingId FROM ...)
) TBL
LEFT JOIN ThingDetails TD USING(ThingId)
....

Or

(SELECT ...,T.ThingId,TD.* FROM ... LEFT JOIN ThingDetails TD USING(ThingId))
UNION
(SELECT ...,T.ThingId,TD.* FROM ... LEFT JOIN ThingDetails TD USING(ThingId))

I would imagine in simple cases that it doesn't matter much, but I wondered what recommendations you guys may have. I don't want to experiment much here because of the time taken to actually write test queries and even then they'll be very specific cases. I'd like to know what the optimiser can apply here.

As an example of an optimisation I know through experience, replacing an OR with a XOR if you know both operands cannot both be true can help!

I'd also be curious to know about WHERE statements, On each part of the union, or outside? Some testing shows that it actually may matter (an <autokey0> pops up if I use a where outside of union) - I shall keep investigating but a point in the right direction would be great!

Any information on MariaDB or MySQL is welcome. I'm not looking for specific behaviour in each version and going back as far as 5.1 at least MySQL is able to factor in and out various constraints, I'm looking for detailed optimiser behaviour.

My own limited testing showed that MySQL both factored in some joins and factored out others. In fact one of my tests involved MySQL returning the first union in the order of an index of the table it was joining to's primary key. The as-if rule states that any optimisation is okay as long to the observer it is as if what they wrote actually happened. I've had problems with this before. It was actually a bug in MySQL 5.1 -  I now know it's because of how InnoDB works.

This question is really about what 'confuses' the optimiser enough to not be able to make these observations.

It depends.  Let's dissect each scenario.

( SELECT UNION ALL SELECT ) JOIN

breaks down into

create tmp table
do first select into that tmp (n1 rows)
do second select into that tmp (n2 rows)
dedup because of ALL -- change to DISTINCT to skip this step (n3=n1+n2, or fewer rows)
join n3 times

And...

( SELECT JOIN ) UNION ALL ( SELECT JOIN )

breaks down into

create tmp table
do first select and join into that tmp (n1 rows & n1 joins)
do second select and join into that tmp (n2 rows & n2 joins)
dedup because of ALL -- change to DISTINCT to skip this step (n3=n1+n2, or fewer rows)

So it depends on which case you have.

If the JOIN filters out some rows, then whatever follows it will have less work.
If you switch to UNION DISTINCT, that will shrink the subsequent work.
If the JOIN adds lots of bulky columns, the join-first case is building a bigger tmp table.

Watch out for <autokey0> -- it means you have a tmp table without an index, but 5.6 was smart enough to create one for you.  Your SELECT, as written, should not need that unless ThingsDetails does not have an index on ThingsId.  If that is the case, then add an index rather than taking advantage of autokey.


How oracle syncs data across nodes in a cluster If I have a oracle db server clustered across 2 nodes, it means both nodes have a copy of the whole data, the data is not lying half and half on each node.

Now when I make an update using my application connected to the cluster, (application deployed on WAS), i make an update on one of the nodes and then Oracle will sync it on the other node. My question is, will this sync happen in real time? Is it something configurable? It seems if it is in real time, then the performance will be affected?

What is the standard solution for that?

Well actually….no. The second Development Milestone Release of MySQL Cluster 7.2 introduces support for what we call “Multi-Site Clustering”. In this post, I’ll provide an overview of this new capability, and considerations you need to make when considering it as a deployment option to scale geographically dispersed database services.

You can read more about MySQL Cluster 7.2.1 in the article posted on the MySQL Developer Zone.

MySQL Cluster has long offered Geographic Replication, distributing clusters to remote data centers to reduce the affects of geographic latency by pushing data closer to the user, as well as providing a capability for disaster recovery.

Multi-Site Clustering provides a new option for cross data center scalability. For the first time splitting data nodes across data centers is a supported deployment option. With this deployment model, users can synchronously replicate updates between data centers without needing to modify their application or schema for conflict handling, and automatically failover between those sites in the event of a node failure.

MySQL Cluster offers high availability by maintaining a configurable number of data replicas.  All replicas are synchronously maintained by a built-in 2 phase commit protocol.  Data node and communication failures are detected and handled automatically.  On recovery, data nodes automatically rejoin the cluster, synchronize with running nodes, and resume service.

All replicas of a given row are stored in a set of data nodes known as a nodegroup.  To provide service, a cluster must have at least one data node from each nodegroup available at all times.  When the cluster detects that the last node in a nodegroup has failed, the remaining cluster nodes will be gracefully shutdown, to ensure the consistency of the stored databases on recovery.

Improvements to the heartbeating mechanism used by MySQL Cluster enables greater resilience to temporary latency spikes on a WAN, thereby maintaining operation of the cluster. A new “ConnectivityCheck” mechanism is introduced, which must be explicitly configured. This extra mechanism adds messaging overheads and failure handling latency, and so is not switched on by default.

When configuring Multi-Site clustering, the following factors must be considered:

Bandwidth
Low bandwidth between data nodes can slow data node recovery.  In normal operation, the available bandwidth can limit the maximum system throughput.  If link saturation causes latency on individual links to increase, then node failures, and potentially cluster failure could occur.

Latency and performance
Synchronously committing transactions over a wide area increases the latency of operation execution and commit, therefore individual operations are slowed. To maintain the same overall throughput, higher client concurrency is required.  With the same client concurrency level, throughput will decrease relative to a lower latency configuration.

Latency and stability
Synchronous operation implies that clients wait to hear of the success or failure of each operation before continuing. Loss of communication to a node, and high latency communication to a node are indistinguishable in some cases.  To ensure availability, the Cluster monitors inter-node communication.  If a node experiences high communication latency, then it may be killed by another node, to prevent its high latency causing service loss.

Where inter-node latencies fluctuate, and are in the same range as the node-latency-monitoring trigger levels, node failures can result.  Node failures are expensive to recover from, and endanger Cluster availability.

To avoid node failures, either the latency should be reduced, or the trigger levels should be raised.  Raising trigger levels can result in a longer time-to-detection of communication problems.

WAN latencies
Latency on an IP WAN may be a function of physical distance, routing hops, protocol layering, link failover times and rerouting times. The maximum expected latency on a link should be characterized as input to the cluster configuration.

Survivability of node failures
MySQL Cluster uses a fail fast mechanism to minimize time-to-recovery. Nodes that are suspected of being unreachable or dead are quickly excluded from the Cluster.  This mechanism is simple and fast, but sometimes takes steps that result in unnecessary cluster failure.  For this reason, latency trigger levels should be configured a safe margin
above the maximum latency variation on inter-data node links.

Users can configure various MySQL Cluster parameters including heartbeats, Connectivity_Check, GCP timeouts and transaction deadlock timeouts. You can read more about these parameters in the documentation.

Recommendations for Multi-Site Clustering
- Ensure minimal, stable latency;
- Provision the network with sufficient bandwidth for the expected peak load - test with node recovery and system recovery;
- Configure the heartbeat period to ensure a safe margin above latency fluctuations;

Configure the ConnectivtyCheckPeriod to avoid unnecessary node failures;
Configure other timeouts accordingly including the GCP timeout, transaction deadlock timeout, and transaction inactivity timeout.

Example
The following is a recommendation of latency and bandwidth requirements for applications with high throughput and fast failure detection requirements:
- latency between remote data nodes must not exceed 20 milliseconds;
- bandwidth of the network link must be more than 1 Gigabit per Second.

For applications that do not require this type of stringent operating environment, latency and bandwidth can be relaxed, subject to the testing recommended above.

As the recommendations demonstrate, there are a number of factors that need to be considered before deploying multi-site clustering. For geo-redundancy, Oracle recommends Geographic Replication, but multi-site clustering does present an alternative deployment, subject to the considerations and constraints discussed above.


Database Model for Various Type of Sensors I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this?

-

Option 1 : should I integrate all sensors into a single database like this

Table Sensor
---
SensorID
SensorType
SensorDesc

Table SensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue

-

Option 2 : Or should I put each sensor type into separate table?

Table TemperatureSensor
---
SensorID
SensorDesc

Table TemperatureSensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue (double)

Table MotionSensor
---
SensorID
SensorDesc

Table MotionSensorReadings
---
SensorReadingID
SensorID
ReadingDateTime
ReadingValue (integer)

Considering that various types of sensor tables will have similar fields but potentially different reading data types (some of them are integer and some of them are doubles, etc). Is it logical to separate the tables for each sensors? Or keep using a single table for every type of sensors?

Which one is the best practice to implement such database if there might be various types of sensors to be added in the future?
Which one can be better optimized for performance?

Thank you

Depending on how you plan on using the reading values, I would lean towards option 2 for this. Since you have different categories of sensors, with different types of values being used for each, then it would make sense to to have a master table that stores all the sensors and their type and status (online/offline) and location. There would then be a table for each of the types. This would make it easier to process the data over time, such as average temperature over time.

If you just want to store the data without much processing, then going the one-table route would be best, and store the values as VARCHARs. This would make adding a type later on easier, but would make manipulating the data in the future more difficult.


Optimizing calculation query In a legacy project I've the following query:

SELECT
    concat(SR_PO, '-', SR_RlsSequence) as Order,
    count(*) as Todo,
    sum(isnull(SR_Enabled,0)) as ToLoad,
    sum(isnull(SR_Done,0)) as Loaded,
    sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
    count(EndTime3) as Quot,
    sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104')
GROUP BY by concat(SR_PO, '-', SR_RlsSequence)

But its too expensive, especially when the IN parameters are lots. Any ideas concerning an optimization? please consider that I cannot use a stored procedure.

Estimated Query plan is uploaded here.

You could try applying the filter

(WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104'))

Directly on the index instead of it being implemented as a filter further in the execution plan.

You can do this by not applying functions on columns in your where clause and adding an index. The group by is also changed to work without the function.

Query adaptation:

SELECT
    concat(SR_PO, '-', SR_RlsSequence) as [Order],
    count(*) as Todo,
    sum(isnull(SR_Enabled,0)) as ToLoad,
    sum(isnull(SR_Done,0)) as Loaded,
    sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
    count(EndTime3) as Quot,
    sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE SR_PO = '100063' AND SR_RlsSequence IN ('100','101','103','104')
GROUP BY SR_PO, SR_RlsSequence;

And index:

CREATE INDEX IX_SR_PO_SR_RlsSequence
ON [dbo].[Edgebanding](SR_PO,SR_RlsSequence)
INCLUDE(SR_Enabled,SR_Done,Rejected,EndTime3);


Tag Schema select intersection I have a, I think pretty common, schema:

samples <-> sample_labels <-> labels

Here's an SQL Fiddle. I would like to achieve a selection of all samples that have all of the tags specified.

Given parameters of:

label.key=narf, label.value=zord
label.key=foo, label.value=bar

I only would like to retrieve samples that are having both of these labels. A where of

-- will find nothing cause conditions can't be satisfied
WHERE
  label.key = 'narf' AND label.value = 'zord'
  AND label.key = 'foo' AND label.value = 'bar'

will yield 0 rows (seems clear enough because no label tuple can actually fullfill these conditions. Given this:

-- will find either of "narf=zord", "foo=bar"
WHERE
  label.key = 'narf' AND label.value = 'zord'
  OR label.key = 'foo' AND label.value = 'bar'

Will yield all rows in the SQL Fiddle example because (again, seems clear enough to me) every label in that case will fulfill the conditions.

Even worse is this:

-- will also find "foo=zord" or "bar=narf" -> unwanted
WHERE
  (l.key IN ('foo', 'narf')) AND
  (l.value IN ('bar', 'zord'))

My brain is in meltdown mode and I'm pretty close to filtering thru the results in my code. It seems obvious that I'm simpy not smart enough to get the query correct.  :(

What is the query to get only samples that are correlated to ALL the given labels?

You must ensure that both combinations exists.

-- I'd like all samples that have labels of "narf=zord" AND "foo=bar"
-- AND l.key = 'foo' AND l.value = 'bar'
WITH pairs AS
(
    SELECT s.id AS sample_id, s.prefix AS sample, l.id AS label_id, l.key, l.value
    FROM   samples s
    JOIN   sample_labels sl ON (s.id = sl.sample_id)
    JOIN labels l ON (sl.label_id = l.id)
)
SELECT p.sample_id, p.sample, p.label_id, p.key, value
FROM   pairs p
WHERE  EXISTS (SELECT 1
               FROM   pairs
               WHERE sample_id = p.sample_id and key = 'narf' AND value = 'zord')
AND    EXISTS (SELECT 1
               FROM   pairs
               WHERE sample_id = p.sample_id and key = 'foo' AND value = 'bar');

sample_id | sample              | label_id | key  | value
--------: | :------------------ | -------: | :--- | :----
        1 | narf=zord & foo=bar |        1 | narf | zord
        1 | narf=zord & foo=bar |        2 | foo  | bar

dbfiddle here


MySQL Memory & max_connections Performance Tuning So, our site runs WordPress and in the past few days we have been getting tons of spam comments etc.  I believe that we are becoming the victims of some kind of bot army that has been crashing our server a lot in the past few days and I really need help.

I have called our tech support and they have recommended decreasing some of the buffer sizes in my.cnf and lowering max_connections.   I ran the mysqltuner script and here are the results.

The immediate problem is that we are reaching our max_connections value.  I don't want to increase it beyond 40 because when I do MySQL uses the full capacity of the server's memory and starts to have memory allocation errors when I'm running terminal commands.  The real issue is what do I need to tweak so that I can increase the max_connections without allowing MySQL to use more memory?  I think the buffer pool size is a big factor but I don't know how to tweak it properly.

Also, mysqltuner says I have a bunch of fragmented tables but I just went through by hand yesterday and ran OPTIMIZE TABLE on all of the tables?? Could that be a result of running mysqlcheck --repair --all-databases ??

After thorough research, we determined that the problem was that WordPress was being attacked and it was causing MySQL to use the maximum amount of memory allotted to it in the configuration files.  Even caching of WordPress pages could not stop this illegitimate traffic from using all of the memory because of the sheer volume of IP addresses the attackers had at their disposal.  We ended up getting CloudFlare and that seems to have resolved the problems by blocking the DDoS at the DNS level. We will also be looking at getting more memory for the server, to prevent this from happening with heavy legitimate traffic in the future.


Pushing or appending to JSON array in PostgreSQL 9.4 I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values.

Something like that:

CREATE OR REPLACE FUNCTION add_array(
    array_received json[])
  RETURNS void AS
$BODY$

    update table set _array_field = _array_field | array_received ...;

$BODY$
  LANGUAGE plpgsql VOLATILE;

In pre-9.5 you can use json_array_elements, and array_to_json(array_agg()), like this.

SELECT array_to_json(array_agg(x))
FROM (
  SELECT jsonb_array_elements('[1,2,3]'::jsonb)
  UNION ALL SELECT '4'::jsonb
) AS t(x);

You can also write this using the ARRAY constructor like this..

SELECT array_to_json(ARRAY(
  SELECT jsonb_array_elements('[1,2,3]'::jsonb)
  UNION ALL SELECT '4'::jsonb
));


MySQL replication: how to safely propagate ignored table structure changes from master to slave? We have a MySQL database with a replication set up (master to single slave).
Some tables are skipped from replication (they are listed in Replicate_Wild_Ignore_Table when calling SHOW SLAVE STATUS). In the slave database the ignored table is present with no data.

The problem is the structure of one of the ignored tables was ALTER'ed on master and this change needs to be propagated to slave.

What is simplest non-breaking way of achieving that?
Can I just run ALTER TABLE on slave, adding the necessary columns?

Never mind. Tried it and it worked without breaking anything.


Optimizing queries vs. adding indexes I have this very old and SLOW query that I am trying to optimize, but I am not sure I can do anything to it, but add more indexes on columns involved in WHERE, JOIN and ORDER BY.

Query:

SELECT TOP 400 jobticket.jobnumber, jobticket.typeform, jobticket.filename, jobticket.req_number, jobticket.reqd_del_date, jobticket.point_of_contact, jobticket.status, jobticket.DapsDate, jobticket.elpod, job_info.IDOrderMaskedStatus, job_info.job_status, job_info.SalesID, job_info.location, job_info.TOMetadataID
FROM jobticket WITH (NOLOCK)
INNER JOIN job_info WITH (NOLOCK) ON job_info.jobnumber = jobticket.jobnumber
WHERE
(
    NOT(
        (jobticket.status = 'Complete' OR jobticket.status = 'Completed')
         and (job_info.job_status = 'Actualized' OR job_info.job_status = ''
              OR job_info.job_status = 'Actualized Credit Billed'
              OR job_info.job_status = 'DWAS Actualized' OR job_info.job_status = 'DWAS Actualized Credit Billed'
             )
        )
    or
    ((SELECT COUNT(job_status) AS Expr1 FROM tblConsolidatedBilling AS tblConsolidatedBilling_1 WITH (NOLOCK)
      WHERE  (job_status <> 'Actualized'
      AND  job_status <> 'Actualized Credit Billed')
      AND (master_jobnumber = jobticket.jobnumber)) > 0)
)
and (jobticket.status != 'Waiting Approval' or (jobticket.status = 'Waiting Approval' and jobticket.DPGType is null))
and jobticket.typeform <> 'todpg'
and ((job_info.isHidden <> 1 or job_info.isHidden is null) and job_info.isInConcurrentRelease is null)
and job_info.deleted != '1'
and jobticket.status != 'New Job'
and jobticket.status != 'PRFYCLSFD'

ORDER BY
job_info.expediencyLevel DESC,
jobticket.jobnumber DESC

Execution Plan:

In all honesty I don't know what to do with this query.

Should I add individual nonclustered indexes on all columns involved in WHERE JOIN and ORDER BY?

There are many indexes on these tables, but I am not sure whether they are helpful in this query:

You have a whole lot of looping going on
Give this a try
The exists will be faster then count
And bring stuff up into the join can help the optimizer

SELECT TOP 400 jobticket.jobnumber, jobticket.typeform, jobticket.filename, jobticket.req_number, jobticket.reqd_del_date, jobticket.point_of_contact, jobticket.status, jobticket.DapsDate, jobticket.elpod
     , job_info.IDOrderMaskedStatus, job_info.job_status, job_info.SalesID, job_info.location, job_info.TOMetadataID
 FROM jobticket WITH (NOLOCK)
 JOIN job_info  WITH (NOLOCK)
       ON job_info.jobnumber = jobticket.jobnumber
      and jobticket.typeform <> 'todpg'
      and (    (job_info.isHidden <> 1 or job_info.isHidden is null)
            and job_info.isInConcurrentRelease is null
          )
      and job_info.deleted <> '1'
      and jobticket.status not in ('New Job', 'PRFYCLSFD')
      and (     jobticket.status != 'Waiting Approval'
            or (jobticket.status  = 'Waiting Approval' and jobticket.DPGType is null)
          )
WHERE jobticket.status    not in ('Complete','Completed')
   or job_info.job_status not in ('Actualized','','Actualized Credit Billed','DWAS Actualized','DWAS Actualized Credit Billed')
   or ( exists ( SELECT job_status AS Expr1
                   FROM tblConsolidatedBilling AS tblConsolidatedBilling_1  WITH (NOLOCK)
                  WHERE job_status not in ('Actualized', 'Actualized Credit', 'Billed')
                    AND master_jobnumber = jobticket.jobnumber )
      )
ORDER BY job_info.expediencyLevel DESC, jobticket.jobnumber DESC


Strange behaviour with Computed Columns in SQL-Server Whilst reading through my 70-433 exam book, I thought of something that I can see not working, yet I believe it does. The passage read something like:

  The column must also be marked as PERSISTED, which means that SQL Server physically stores the result of the computed column's expression in the data row instead of calculating it each time it is referenced in a query.

From this I understand two things:

A non-persisted computed column is calculated every time that it is referenced in a query
Because nothing is stored for the computed column, I assume no index can be created for the column.

After reading it, I thought that this was a bit strange as I have managed to create an index on a non-persisted column in a previous project.

How can an index be created for something that is not persisted and is this detrimental in the long run?

To prove this I have run the following SQL statement:

CREATE TABLE testTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    telephone VARCHAR(14),
    c_areaCode AS (SUBSTRING(telephone,0,5)),
    cp_areaCode AS (SUBSTRING(telephone,0,5)) PERSISTED
)

INSERT INTO testTable VALUES('09823 000000');
INSERT INTO testTable VALUES('09824 000000');
INSERT INTO testTable VALUES('09825 000000');

CREATE NONCLUSTERED INDEX IX_NotPersisted ON testTable(c_areaCode);
CREATE NONCLUSTERED INDEX IX_Persisted ON testTable(cp_areaCode);

And then run the following queries:

DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL');
DBCC DROPCLEANBUFFERS
GO
SELECT cp_areaCode FROM testTable;
GO
SELECT c_areaCode FROM testTable;

Having looked at the query plan for the above code, I can see that both select queries are using the non-persisted index. Again, how?

2.Because nothing is stored for the computed column, I assume no
  index can be created for the column.

This assumption is not true - either kind can be indexed. The computed column must be deterministic in either case but when the computed column is persisted, the requirement that the computation is also precise is relaxed (ie it can involve floating point operations).

  How can an index be created for something that is not persisted and is
  this detrimental in the long run?

The result of the function is 'persisted' in the index in either case - the only difference is whether it is persisted in the table.


Online Re-Indexing In SQL Server 2005/2008 Standard The Story: Once a week, we run a special Maintenance Plan that involves re-indexing 2 critical tables that have ~6 Million records between the two of them. We use SQL Server 2005 Standard Edition.

The Issue: During this re-indexing (~15 min operation), we fail to insert data into the tables, and our clients are dataless.

I was told that SQL Server 2008 Enterprise has a "Online Re-Indexing" that is not present in lesser versions of SQL Server that will resolve our problems.

However, I just found the following option in our Maintenance Plan options (SQL 2005 Standard):

My Question: Does this perform that same "Online Re-Indexing" that 2008 Enterprise does? What are the repercussions (performance wise) to enabling this option? Was I almost wooed into purchasing a 2008 Enterprise license needlessly?

The option is in SSMS but it will not work. SSMS is not Edition smart so to speak, they did not make it smart enough to hide options specific to the Edition you are running.

If you try to use it, I believe it just gives you a message relating to "this is an Enterprise only feature"; or something close to it.


Any Idea how to migrate from PostgresSQL 8.4 to PostgresSQL 9.1.1 in opensuse12.1 I had a crashed opensuse 11.3 that's why I installed opensuse 12.1..
after I installed (via yast) and edited /etc/passwd

I do this:

 rcpostgresql start

and this shows on the /var/log/messeges

~ # less  /var/log/messages  | grep postgres
Dec 27 10:43:08 t1libora1 shadow[2393]: new group added - group=postgres, gid=26, by=0
Dec 27 10:43:08 t1libora1 shadow[2393]: running GROUPADD_CMD command - script=/usr/sbin/groupadd.local, account=postgres, uid=26, gid=0, home=, by=0
Dec 27 10:43:08 t1libora1 useradd[2396]: new account added - account=postgres, uid=26, gid=26, home=/var/lib/pgsql, shell=/bin/bash, by=0
Dec 27 10:43:08 t1libora1 useradd[2396]: running USERADD_CMD command - script=/usr/sbin/useradd.local, account=postgres, uid=26, gid=26, home=/var/lib/pgsql, by=0
Dec 27 11:08:45 t1libora1 su: (to postgres) root on none
Dec 27 11:08:45 t1libora1 postgresql[2998]: Starting PostgreSQL2011-12-27 13:08:45 GMT   FATAL:  los archivos de base de datos son incompatibles con el servidor
**Dec 27 11:08:45 t1libora1 postgresql[2998]: 2011-12-27 13:08:45 GMT   DETALLE:  El directorio de datos fue inicializado por PostgreSQL versión 8.4, que no es compatible con esta versión 9.1.1.**
Dec 27 11:08:50 t1libora1 postgresql[2998]: pg_ctl: no se pudo iniciar el servidor.
Dec 27 11:08:50 t1libora1 postgresql[2998]: Examine el registro del servidor.
Dec 27 11:08:50 t1libora1 postgresql[2998]: ..failed
Dec 27 11:08:50 t1libora1 systemd[1]: postgresql.service: control process exited, code=exited status=1
Dec 27 11:08:50 t1libora1 systemd[1]: Unit postgresql.service entered failed state.

how can I migrate?

In a perfect world, you'd have a backup of your databases created with pg_dump. If you did, you could restore those database backups to your 9.1 version.  Otherwise, you could reinstall 8.4 somewhere else, copy in your original database files and make a backup of all your databases and users.  Then restore that backup to your 9.1

Install your old version of PostgreSQL

1.Install an instance of PostgreSQL 8.4, same version as you had on OpenSUSE 11.3.  This doesn't have to be on the same machine.  If it does, you could compile
a version from a .tar.gz. example: http://ftp.postgresql.org/pub/source/v8.4.8/postgresql-8.4.8.tar.gz

Decompress, configure, make and install to some other directory

2.Make sure your 9.1 isn't listening otherwise 8.4 will not be able to use the 5432 port.

3.start up postgres. somedirectory/data is where you have your 8.4 data files.

su - postgres

/postgresql84_directory/bin/pg_ctl start -D /somedirectory/data -l serverlog

4.Backup your databases to a text file

/postgresql84_directory/bin/pg_dumpall > all.dbs_8.4.sql

5.Stop postgresql 8.4

/postgresql84_directory/bin/pg_ctl -D /somedirectory/data -l logfile stop

Restore your 8.4 dump file to 9.1

1.If your 9.1 version isn't working anymore, you will probably want to reinitalize your data directory.

/postgresql91_directory/bin/initdb -D /postgresql91_directory/data/

2.Startup Postgresql9.1 again

3.Now restore the 8.4 dump file to your 9.1 instance

/postgresql91_directory/bin/psql -f all.dbs_8.4.sql


Cannot select all data from a table php/sql $sql = $conn->prepare('SELECT * FROM users WHERE user_id != null');
if($sql->rowCount() > 0){
}
else{
echo "there are no users"
}

I want to select all data from a table however the code above does not work. It echoes/prints out "there are no users." No errors are shown too.

user_id != NULL

always fails.  Instead, say

user_id IS NOT NULL

And you need to ->execute it.


EXPLAIN ANALYZE shows no details for queries inside a plpgsql function I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:

create function f1()
  returns integer as
$$
declare

event tablename%ROWTYPE;
....
....

begin

FOR event IN
   SELECT * FROM tablename WHERE condition
LOOP
   EXECUTE 'SELECT f2(event.columnname)' INTO dummy_return;
END LOOP;

...

INSERT INTO ... FROM a LEFT JOIN b ... LEFT JOIN c WHERE ...

UPDATE T SET cl1 = M.cl1 FROM M WHERE M.pkcols = T.pkcols;

...

end
$$ language plpgsql;

If I ran EXPLAIN ANALYZE f1(), I only get the total time, but no details. Is there a way I can get detailed results for all queries in the function?

If queries in the function should not be optimized by Postgres, I would also ask for an explanation.

The correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL:
EXPLAIN ANALYZE SELECT f1();
Optimization
PL/pgSQL functions are black boxes to the query planner. Queries inside are optimized just like other queries, but separately and one by one like prepared statements, and the execution plan may be cached for the duration of the session. Details:

Function Performance

EXPLAIN function bodies
Like @Daniel already commented, you can use the additional module auto_explain to get more details (lots of details). Statements inside plpgsql functions are considered "nested statements". Be sure to set:
SET auto_explain.log_nested_statements = ON;

Detailed instructions:

Postgres query plan of a function invocation written in plpgsql

As an exception to the rule, simple SQL functions (not plpgsql) may be "inlined", i.e. the function code is inserted into the outer query and everything is executed like there wasn't a function to begin with. The query plan includes detailed information in such cases.


Is high Extent Fragmentation a problem? DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (2048062382); index ID: 1, database ID: 28
TABLE level scan performed.
- Pages Scanned................................: 1019182
- Extents Scanned..............................: 127400
- Extent Switches..............................: 127399
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [127398:127400]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 77.25%
- Avg. Bytes Free per Page.....................: 135.7
- Avg. Page Density (full).....................: 98.32%

I have read that Scan Density = 100% is very good, and Logical Scan Fragementation <1% is also great.  77% Extent Scan Fragmentation troubles me, but the internet says to ignore it.

I'm analyzing a single-table slow performing query.  It runs ~30 seconds on first execution, then 200 ms on second and subsequent executions.  I can reset this behavior with DBCC DROPCLEANBUFFERS.

Is the high Extent Scan Fragmentation an important clue?

(If not, I'll likely add another question about my single-table query).

In my experience, even if you're doing full table scans, it is unlikely that extent fragmentation will affect performance much, and for more typical query patterns it should be negligible at best. That is for queries that use cached data that fits into memory - obviously fragmentation of any kind becomes rather moot if the data is in memory and isn't being read directly off disk.

Now, you've got a table that is > 8 GB, so it is possible that extent fragmentation might be harmful to your queries. If this query is using a table scan across 34 million rows, and the worst you're getting (on first execution only!) is 30 seconds, then it is extremely unlikely that lowering that extent fragmentation number is going to help much. That 30 seconds is spent loading the data into memory, and I can't fathom that improving the extent fragmentation will buy you much there. If you have the memory to spare to keep this table in memory, perhaps you should consider a startup job or some background process that periodically runs the query without forcing a user to wait for it, ensuring that it stays fresh in the cache.

Hekaton might be for you.


How is Index rebuild affected by IN_ROW_DATA vs LOB DATA I am using Ola Hallengren's scripts to maintain my databases.  On a few of them there are indices that don't seem to be benefiting from the maintenance.  I know that his scripts have a lower threshold (I believe 1000 pages), below which it will not attempt to rebuild or reorganize.

For one index, in dm_db_index_physical_stats the clustered index shows up on 2 lines.  One is PK_DATA, allocation unit type of IN_ROW_DATA, 16 pages, and 93.75% fragmented.  The other PK Data, allocation unit type of LOB_DATA, 57200 pages, and 0% fragmented.

Should I expect this index to be reorganized or is it being skipped due to the differing page counts for the different allocation unit types?  Or might it be getting reorganized unsuccessfully?

Thanks in advance for any advice you might have.

In Ola Hallengren's scripts he uses the MAX fragmentation and only considers the IN_ROW_DATA allocation unit type. See this excerpt from his scripts:

ALTER PROCEDURE [dbo].[IndexOptimize]
-- ...
-- Is the index fragmented?
-- ...
-- Note his use of the following below:
---- @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent)
---- WHERE alloc_unit_type_desc = ''IN_ROW_DATA''
  SET @CurrentCommand05 = @CurrentCommand05 + 'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(@ParamDatabaseID, @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'

Without going further into the exact logic of the scripts it would appear that the index would not be reorganized because the page count is below the minimum of 1000.

Github scripts
link to script line above


Authorization error running db2 command line with db2inst1 I need to enable the COLUMNAR setting in DB2 Express Edition on Docker. For that to work I need to set INTRA_PARALLEL ON at the instance or database level.
I connect to the db2 command with db2inst1 that is the instance owner, but I'm getting an error saying that the user is root. How to fix this problem?
db2 => connect to bank0002 user db2inst1 using xxxxx

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.4.0
 SQL authorization ID   = DB2INST1
 Local database alias   = BANK0002

db2 => UPDATE DBM CFG  USING INTRA_PARALLEL ON
SQL5001N  "ROOT" does not have the authority to change the database manager
configuration file.

update dbm cfg is run by your user in the sh, not the user you connected as. You can attach to the instance as a particular user:
db2 attach to instance db2inst1 user db2inst1

or:
db2 attach to instance db2inst1 user db2inst1 using <password>

and then:
db2 update dbm cfg using INTRA_PARALLEL YES

Documentation can be founds at ATTACH
In addition to that, I find it much easier to run db2 directly from the sh (as I did above) instead of starting db2 clp separately. You can use the sh as a host language and for example do loops over a resultset:
#> db2 connect to mydb
#> for t in $(db2 -x "select ..."); do
       echo "The table is ${t}"
   done

Of course just a silly example, but it is a powerful mechanism for running db2 from a sh script


Slot Array and Total Page Size I continue to read in many forums and on many blogs that a page is comprised as shown below:
Page Size:    16 x 512B = 8192B
Page Header:            = 96B
Maximum In_Row Row:     = 8060B

This leaves (8192 - 96 - 8060)B = 36B.

Ok, this is logical and correct. The question I have is this: why do so many people say that the remaining 36B is reserved for the slot array?

Obviously, the slot array gives 2B per row on the page; so, it can be as small as 2B and as large as 1472B:

2B: 1 row * 2B = 2B

1472B:
8096B = n*9B (min row size with overhead...think single TINYINT column) + n*2B (slot array cost per row)
=> 8096 = 11n
=> n = 8096 / 11 = 736.

736*2B = 1472B.

This gets me to 20 due to the 14B version tag.

USE master ;
GO

CREATE DATABASE test ;
GO

USE test ;
GO

ALTER DATABASE test
    SET ALLOW_SNAPSHOT_ISOLATION ON ;
GO

ALTER DATABASE test
    SET READ_COMMITTED_SNAPSHOT ON ;
GO

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i CHAR(8000) DEFAULT(REPLICATE('a',8000))
    , j CHAR(53)   DEFAULT(REPLICATE('a',53))
) ;

INSERT INTO tbl
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

Another example. If you go to 50 from 49, you get the VARCHAR(MAX) going to LOB_DATA.

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i VARCHAR(MAX) DEFAULT(REPLICATE('a',8000))
    , j CHAR(49)   DEFAULT(REPLICATE('a',49))
) ;

sp_tableoption N'tbl', 'large value types out of row', 'OFF' ;
GO

INSERT INTO tbl
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

It appears that this issue remains, even in SQL Server 2012. @SQLKiwi points to this post by Kimberly Tripp - http://www.sqlskills.com/blogs/kimberly/a-simple-start-table-creation-best-practices/.

Where pages are used for internal purposes like sort runs, the maximum row size is 8094 bytes. For data pages, the maximum in-row size including internal row overhead is 8060 bytes.

Internal row overhead can expand significantly if certain engine features are in use.  For example, using sparse columns reduces the user-accessible data size to 8019 bytes.

The only example of external row overhead I know of up to SQL Server 2012 is the 14 bytes needed for versioned rows.  This external overhead brings the maximum space usage for a single row to 8074 bytes, plus 2 bytes for the single slot array entry, making 8076 bytes total. This is still 20 bytes short of the 8096 limit (8192 page size - 96 byte fixed header).

The most likely explanation is that the original 8060 byte limit left 34 bytes for future expansion, of which 14 were used for the row-versioning implementation.


Recovering from removal of ibdata1 I just upgraded from Debian Squeeze to Wheezy and mysql stopped working. Blindly following some advice without any understanding of what I was doing, I removed ibdata1 in an attempt to solve the problem. Unfortunately, it was only after doing so that I realised this was a terrible idea, especially as I was missing this option in my.cnf: innodb_file_per_table = 1.

I found the cause of my problem and now my DB server is working again. However, now, I am left with a mess. My problem is that some (but not all) of my tables, which appear when I do show tables;, are giving me this error:

ERROR 1146 (42S02): Table 'db.tablename' doesn't exist

I have since enabled the setting innodb_file_per_table = 1 (perhaps this was a bad idea?) and before I came to realise that I probably didn't have a chance, I was trying out the innodb_force_recovery flags.

I'm fairly confident that these tables are gone for good so I'm trying to restore my database from earlier dumps. The problem is that the import breaks on these tables that "do but don't" exist. I have tried dropping the database before doing the import but there are clearly some other files lying around. Needless to say I'm reluctant to do any more speculative deletion of files...

Any advice on how to clean up this mess?

THere are three ways to recover your tables:

http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/
http://www.chriscalender.com/?p=28
Extract records from the ibd files with page_parser/constraints_parser from Data Recovery Toolkit

UPDATE: Data recovery toolkit moved to GitHub


Oracle index on column with length > maximum varchar2 length Description:

We have a table on PostgreSQL 9.3 and we migrate it (data & structure) to Oracle 11.0.2.4. Here is our table:

create table cstb_temp_log
(
id NUMBER PRIMARY KEY,
log_info data_type(max_length) -- max_length < 10000
)
-- log_info : data is inserted/updated every second (**)
-- data_type: may be we can use "clob" because max_length of varchar2 in Oracle is 4000 (SQL).
-- cstb_temp_log : size = 1 GB, row = 400000

We want to index and search "text" on log_info column, so we tried Oracle Text 11g .

Problems:

If we use "data_type" is "clob", we can use "context index" and it have to synchronize after DML . ( we can not use this way because of (**) )

If any, how can we index and search on "log_info" column (max_length < 10000 and data is changed every second) ?

I think its a good suggestion done by @raj moving to 12C as 11g is nearing EOSL.
If you are considering using CLOB with Oracle Text Search, consider below.

Maintaining Indexes and Synchronization
In 11g users can specify at index creation the index update preference: manually,
on commit, or at regular intervals. Users can also specify a transnational text index,
where documents are searchable immediately after being inserted or updated. Note
that the catalog index type - designed specifically for the short pieces of text
typically found in eBusiness catalogs – is always transactional and needs no
synchronization.

Pleaese read: http://www.oracle.com/technetwork/testcontent/11goracletexttwp-133192.pdf (Page 12)


Execution plan index suggestion - difference between similar queries I am trying to analyze queries on this table:

CREATE TABLE [dbo].[Values](
    [tid] [smallint] NOT NULL,
    [t] [datetime2](3) NOT NULL,
    [v] [real] NOT NULL,
 CONSTRAINT [PK_Values_Unique] PRIMARY KEY CLUSTERED
(
    [t] ASC,
    [tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I am reviewing some Estimated Execution Plans using SQL Server Management Studio, first query.

SELECT *
FROM [dbo].[Values]
WHERE  [tid] = 1

Displaying Estimated Execution Plan for this query yields:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Values] ([tid])
INCLUDE ([timestamp],[v])

Which, I can understand, it wants to have a quick look up by tid, altough I don't fully why the INCLUDE is suggested.

Then I tried a second query (another tid), which is pretty the same with another tid:

SELECT *
FROM [dbo].[Values]
WHERE  [tid] = 1 or [tid] = 2

But now, SSMS, doesn't suggest the previous index.

Why does SSMS suggests an INDEX for the first query but not for the second now although they are pretty the same ?

Should adding this suggested INDEX be helpful for both types of queries?

EDIT: This table has over 2 * 10^9 rows.

Some context

The first things to observe is that your clustered index does not help with the lookup of the tid column, because t is the leading column in the index.

If you flipped the order of t and tid in the key, I would expect the index hint to go away and the query to run faster without adding any new indexes.

Specific Answer

The most likely reason that the second query plan does not suggest an index is that the new filter on tid picks more than around 30% of all the values in the table. When that is the case, SQL Server will generally prefer to table scan instead of seeking the index (because this is the better strategy). Hence, suggesting an index is no longer the right thing to do. Selecting two values instead of one may be exactly the tipping point of this 30% constant in the optimiser

To explore this effect, you can force the index hint to return by doing this:

SELECT *
FROM [dbo].[Values] WITH (FORCESEEK)
WHERE  [tid] = 1 or [tid] = 2

.. But please don't do this in production, only to explore the effects.

Why the include?

The include is there because if it wasn't, the execution would have to do this:

Find the value you are looking for in the newly created index on
tid
Go to the main index (the primary key) to pick up the values
of column v and t

The INCLUDE makes sure all the columns are contained in the index so the second step is not needed. Incidentally, this is one of the reasons why you should eschew SELECT *.


Error: Invalid object for global temporary table I'm new to stored procedure & my application requirement is to collect data in global temporary table & bulk copy using bcp.

I'm calling stored procedure, which copies data into global temporary table. When I execute stored procedure it throws an error

  Invalid object name 'tempdb..qaw_temp'

Code for stored procedure:

CREATE table tempdb..qaw_temp
  (id      char(12),
   i_test    int))

print 'Before Insert'
  insert into tempdb..qaw_temp
  values('0411',
    3077,

print 'After Insert'

This work's if we execute code from Aqua Data Studio. But When I get executed in the stored procedure, it throws above mentioned error.

Stored procedure code

create PROC Xtest
as
Begin
 << Code >>
End

Why does the stored procedure execution fail?

Please help, thank you.

There are 2 types of temporary tables in SQL Server. Local and Global.
Differenence between these 2 are:-

1.
Local temporary table starts with #,
Global temporary table starts with ##

2.
Local temporary table exists for a session only.
Global temporary table alwyas exists in tempdb.

3.
Because life of local temporary table is within a session so only the session can query on that.
Multiple session can query Global temporary table, but data is limited to a session.

Here in you post you are using table to insert value (Not global or local temporary table).

Try bellow query.

CREATE table ##qaw_temp
  (id      char(12),
   i_test    int)

print 'Before Insert'
  insert into ##qaw_temp
  values('0411',
    3077)

print 'After Insert'

Thanks


Downsampling to (eg) "last result on Sunday night" We have a largish PostgreSQL table that records user's disk usage.
id      | bigint                      |           | not null | (unique constraint)
time    | timestamp without time zone |           | not null |
quota   | bigint                      |           | not null | 0
usage   | bigint                      |           | not null | 0
zuser   | character varying(20)       |           | not null |

We present data usage to users when they login, and we report on data usage up the org.
In order for the data to be reliable for users, we poll our storage every five minutes. But for our reporting we only need a smaller sample size. We would like to keep some of the data for internal use as well.
Our plan is to keep the month of data, and the last reading for each Sunday night for everything older than that. Because it's data usage, we don't want an average, we just want their last reading before midnight Sunday turns into Monday.
I have discovered date_trunc and have even an idea for an algorithm that includes something along the lines of
SELECT date_trunc('week', time), zuser, quota, usage GROUP BY zuser ORDER BY date_trunc('week', time) DESC

But I'm struggling to work out how to just get a single entry, and how to loop over all data back in time. I could do it in Python, but it seems a little clumsy and then it's something that needs to be run regularly.
In my head I had some sort of database trigger - a PostgreSQL event hitting a function every night at midnight that cleans up (NOW()-1 month)? - that would do it all.
But at the moment I'm looking for help on how to down sample each week into the single reading that happens at the end of that week.
----- edit -----
I ended up coming to
SELECT *
FROM storage.home
WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month)
EXCEPT
SELECT *
FROM storage.home
WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes'
AND extract (dow FROM time) = 0;

But when I change that to a DELETE, it fails on the EXCEPT clause because (I presume) it's no longer a SELECT...EXCEPT SELECT but rather a DELETE...EXCEPT SELECT which is non grammatical. Searching for DELETE EXCEPT is not simple due to language.
Is what I should be doing something more like this:
DELETE FROM storage.home
WHERE id IN
  (SELECT id
  FROM storage.home
  WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month)
  EXCEPT SELECT id
  FROM storage.home
  WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes'
  AND extract (dow FROM time) = 0);

Yes. I took a backup and ran the above. Worked exactly as expected.
I considered the below for the left side of that EXCEPT because it's more true to needs, but I thought I'd start with the less accurate.
WHERE date_trunc('day', time) = CURRENT_DATE - interval '35 days')

You can select only those rows that are less than five minutes before midnight:
... WHERE
      -- less than 5 nimutes before midnight
      TIME '24:00:00' - CAST(time AS time) < INTERVAL '5 minutes'
      -- on a Sunday
      AND extract (dow FROM time) = 0


A SQL query optimzation issue with ORDER BY An SQL query optimzation issue with ORDER BY

Current status
I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and 'GamePosition'. GamePosition has an index on the fen column and Game has an index on white_elo. I currently have 170471 games and 14813401 positions. I'm running mysql 5.7.28.

Object
I'm trying to fetch top rated games based on elo rating of the players. I've simplified my query a bit here, but the point and performance problem is the same.

SELECT Game.id
FROM Game
JOIN GamePosition ON Game.id = game_id
WHERE fen = 'rnbqkbnr/pppppppp/8/8/3P4/8/PPP1PPPP/RNBQKBNR'
ORDER BY white_elo DESC
LIMIT 10

This query tends to be a bit slow (1.2 s) if I get a lot of results (typically the first move gives 67k+ results). Since I plan to exand the database 10x at least, I want to optimize my query. I've run a EXPLAIN which shows that it needs to do a filesort before finding the top rated games on all the results. This seems to be the issue. Removing the ORDER BY from the query makes it superfast (0.0008 s).

Any ideas if it's possible to optimize the query, or if I could store the data differently?

UPDATE:

       Table: GamePosition
Create Table: CREATE TABLE `GamePosition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `fen` varchar(100) NOT NULL,
  `move_color` char(1) NOT NULL,
  `castling_rights` varchar(4) NOT NULL,
  `en_passant_square` varchar(2) NOT NULL,
  `half_move` smallint(6) NOT NULL,
  `full_move` smallint(6) NOT NULL,
  `timestamp` time DEFAULT NULL COMMENT 'time in video',
  `move` varchar(10) DEFAULT NULL,
  `from_square` varchar(4) DEFAULT NULL,
  `to_square` varchar(4) DEFAULT NULL,
  `parent_position` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `position_ibfk_1` (`game_id`),
  KEY `fen` (`fen`),
  KEY `parent_position` (`parent_position`),
  CONSTRAINT `game_key` FOREIGN KEY (`game_id`) REFERENCES `Game` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

       Table: Game
Create Table: CREATE TABLE `Game` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pgn` text NOT NULL,
  `white_id` int(11) NOT NULL,
  `black_id` int(11) NOT NULL,
  `white_elo` int(11) DEFAULT NULL,
  `black_elo` int(11) DEFAULT NULL,
  `result` tinyint(4) DEFAULT NULL COMMENT '0=white, 1=draw 2=black',
  `date` date DEFAULT NULL,
  `event_id` int(11) NOT NULL,
  `eco` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_key` (`event_id`),
  KEY `black_key` (`black_id`),
  KEY `white_key` (`white_id`),
  CONSTRAINT `black_key` FOREIGN KEY (`black_id`) REFERENCES `Player` (`id`),
  CONSTRAINT `event_key` FOREIGN KEY (`event_id`) REFERENCES `Event` (`id`),
  CONSTRAINT `white_key` FOREIGN KEY (`white_id`) REFERENCES `Player` (`id`)
)

       Table: Player
Create Table: CREATE TABLE `Player` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`id`)
)

Actual Query

SELECT GamePosition.id AS position_id, Game.id AS id,
       white.name AS white, black.name AS black, Game.id,
       white_elo, black_elo, result, date
    FROM Game
    JOIN GamePosition ON Game.id = game_id
    JOIN Player white ON white.id = white_id
    JOIN Player black ON black.id = black_id
    WHERE fen = '$fen'
    ORDER BY white_elo+black_elo DESC
    LIMIT $limit
    OFFSET $offset";

Kind Regards,
Bjorn

The only way you are going to avoid a filesort on this one is if you maintain a materialized view of a JOIN between Game and GamePosition, so that you can create an index across (fen, white_elo).


Got fatal error 1236 Slave has more GTIDs than the master has, using the master's SERVER_UUID We are testing replication using GTID's on two servers running MySQL 5.6. Replication was running fine, but now it is not functioning. When I run show slave status\G on the slave, I get the following error message

  Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master
  when reading data from binary log: 'Slave has more GTIDs than the
  master has, using the master's SERVER_UUID. This may indicate that the
  end of the binary log was truncated or that the last binary log file
  was lost, e.g., after a power or disk failure when sync_binlog != 1.
  The master may or may not have rolled back transactions that were
  already replica'

I have tried to reset master and stop, reset, and restart slave, with no luck, and cannot find any information about this particular issue online.

Oddly, when checking show slave status\G on master, I get a different error.

  Last_Errno: 1007 Last_Error: Error 'Can't create database 'repltest';
  database exists' on query. Default database: 'repltest'. Query:
  'create database repltest'

But when I check both the slave and the master, the database 'repltest' does not exist. This was just a database we created on master to check that replication was working, and we deleted it from the master afterward.

Edit - Note: this is a staging environment that does not have active database connections from any applications. This was set up merely to test replication with GTIDs

You probably have to restore the slave based on the procedure outlined on this blogpost. Once replication is fixed, you may then want to run pt-table-checksum and pt-table-sync to synch data between the two servers.


Mysql. Block/disconnect slow queries I am experiencing issue.

Someone 'attacked' my server: simply by searching the same phrase with multiple requests.

As it is text search request and database indices are not used, the engine searches through every row. In general I will deal with myisam full-text indices, but I am curious, is there any way to block/disconnect/interrupt the query which is longer than x seconds?

P.S. I have wait_timeout set to 30, is there anything else I can set(I don't think interactive timeout will be useful).

If you are using MySQL 5.1+, you can create a scheduled event in MySQL or a cronjob to poll the processlist from the information schema.

What queries do you look for in the processlist? Any DB Connection that ...

runs longer that 30 seconds
not used for replication
runs a SELECT query

Here is that query:

SELECT id FROM information_schema.processlist
WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

Here is a Stored Procedure to collect those IDs and terminate the query using the KILL command:

CREATE DATABASE myutil;
USE myutil
DELIMITER $$
DROP PROCEDURE IF EXISTS `Kill_Long_Queries` $$
CREATE PROCEDURE `Kill_Long_Queries` ()
BEGIN

    CREATE TABLE IF NOT EXISTS IDs_To_Kill;
    (ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL) ENGINE=MyISAM;
    TRUNCATE TABLE IDs_To_Kill;

    INSERT INTO IDs_To_Kill (ProcessID)
    SELECT id FROM information_schema.processlist
    WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

    SET @ndxnext = 0;
    SELECT MAX(ndx) INTO @ndxlast FROM IDs_To_Kill;
    WHILE @ndxnext < @ndxlast DO
        SET @ndxnext = @ndxnext + 1;
        SELECT id INTO @ProcessID from IDs_To_Kill WHERE ndx = @ndxnext;
        SET @SQL = CONCAT('KILL QUERY ',@ProcessID);
        PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$
DELIMITER ;

If you want to go beyond terminating the query and just terminate the DB Connection, replace

SET @SQL = CONCAT('KILL QUERY ',@ProcessID);

with this

SET @SQL = CONCAT('KILL ',@ProcessID);

Launch Stored Procedure Using MySQL Event

USE myutil
DELIMITER $$
CREATE EVENT kill_query_event
    ON SCHEDULE EVERY 1 MINUTE
    STARTS (NOW() + INTERVAL 1 MINUTE + INTERVAL SECOND(NOW()) SECOND) DO
BEGIN
    CALL Kill_Long_Queries();
END $$
DELIMITER ;

Launch Stored Procedure Using cron

In the OS, set up the crontab to go off every minute as follows

0 * * * * mysql -u... -p... -Dmyutil -ANe"CALL Kill_Long_Queries()"

CAVEAT for MySQL 5.0 Users

Since there are no events in the MySQL 5.0 and prior, you will have to do something like this:

for PROC_TO_KILL in `mysql -h... -u... -p... -ANe"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -ANe"KILL QUERY ${PROC_TO_KILL}" ; done

SPECIAL CAVEAT

Without a MySQL Event, you would have to connect to mysql each time and poll the processlist. In the event that there are too many connections or if the mysqld starts blocking connection due to connect errors, your only hope is to write a perl script whose sole job is to kill long running query and using only one live session at the same time.

I wrote a perl script two years to run flush hosts every 10 minutes (How do you tell which host is close to being blocked in MySQL?)

Here is that script

#!/usr/bin/perl

use DBI;
use DBD::mysql;

my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;

$check_replication = 1;

$username = "whateveruser";
$password = "whateverpassword";

$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE  = "/tmp/MySQLFlatLine.txt";

$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";

if ( $SLEEP_TIME_INTERVAL == 0 )
{
        open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
        printf fh_output "DONE !!!\n";
        close fh_output;
        exit 0;
}

$db = DBI->connect("DBI:mysql:information_schema;host=127.0.0.1",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";

$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts  = $db->prepare("FLUSH HOSTS")        or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";

$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
{
        $st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
        {
                $StatusCount  = 0;
                while ( my $row = $st_globalstatus->fetchrow_hashref() )
                {
                        my $uptime = $row->{Value};
                }
                $st_globalstatus->finish();
                open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
                printf fh_output "%s : %s\n",$uptime;
                close fh_output;
        }
        for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
        {
                if ( -f $MYSQL_FLATLINE_FILE )
                {
                        unlink $MYSQL_HEARTBEAT_FILE;
                        unlink $MYSQL_FLATLINE_FILE;
                        $my_heart_is_beating = 0;
                }
                sleep 1;
        }
        $heartbeat_count++;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
        {
                $heartbeat_count = 0;
                $st_flush_hosts->execute();
        }
}

$db->disconnect();

I am not a perl expert, but I suggest that you alter this script to collect and kill the process IDs. Save it to a file called killquery.pl. All you need to do is launch this once from the command line as a background process.

nohup perl killquery.pl 5 30 &

This makes the perl script heartbeat every 5 sec, and run the Stored Procedure every 30 second

Give it a Try !!!


How to store/organize all of these scripts? I am about 10 months in to my DBA role and I am already accumulating quite the collection of scripts. Does anyone have any best practices or recommendations on how to store all of these scripts? I manage about 70 instances of SQL server and am looking for a way to have quick access to all of these scripts as I need them throughout the day and right now they are just piling up in a file system folder.

Is this the best way? Are there 3rd party tools that help organize them? Anyone have an amazing solution/recommendation?

Thanks ahead of time!

Get them into source control, pronto. They are your code, your source of truth about how the environments should be configured and operate. They are as important as the application and schema and deserve the same level of care and attention.

Once there you can version and branch them as required. Having 20 copies with no indication which is for what purpose will be a thing of the past.

Within the root folder I'd split them by purpose. For me this means a sub-folder for migration scripts, one for analysis, one for maintenance etc. Use what makes sense for your environment and for your way of thinking.


Optimizing User Defined Custom Columns I am building a web based app that has the potential to have very high traffic and collect a lot of data. Most of this traffic will be utilizing the database. Database is MySQL InnoDB. I want to give users the ability to define their own data fields along with a predefined set of columns that are common. As an example I would have a table (restaurants) with the following columns:

record_id //INT PK auto_inc
  record_uuid //indexed stored as binary(16)
  user_uuid //indexed stored as binary(16)
  restaurant_uuid //indexed stored as binary(16)
  date //indexed
  price //indexed
  party_count //indexed
  rating //indexed
  ...

User "A" would then add his own fields such as (chef_name,phone,table_count,rank)
While user "B" might add fields such as (contract_amount,license_fee,cuisine_type)

My thinking is to have a number of tables to hold a row of data for each one of the custom fields.  So I would have restaurants_extension_varchar, restaurants_extension_int and restaurants_extension_float.

Each of the three tables would look something like this:

extension_id //INT PK auto_increment
extension_uuid //indexed stored as binary(16) used by app to update data value
record_id //indexed INT - used as a key to the main restaurants table
field_identifier // TINYINT
the_data //indexed  varchar/int/float - depending on which table this is

I would then partition each of these tables into 6 partitions. The field identifiers would be held in a memory array for that user and would map the record to a label and page placement data for that custom field. These custom fields will be updated individually through Ajax and not as a group like when you click submit on a multi field form. I expect these tables to hold millions of rows. I have decided to use an auto increment ID as the primary key for all tables plus a UUID ID as the application ID that I will pass to and from the web app to prevent users from manipulating the primary key ID in an attempt to effect other rows.

All of the custom fields need to be searchable.

I have two questions. The first being about the structure. From a pure performance angle, is this a good solution or would I be better off with one row with an abundance of columns.

The second question is regarding the use of an auto increment primary key ID along with an app generated uuid key which will be indexed. From what I understand if I use the uuid as the primary key it will be duplicated in each index that is created. I'm not a DBA so if there is any advice that can be offered I will be grateful. Thanks you!

A lot of people want to do what you are describing.  They get into big trouble when the dataset gets big.

  All of the custom fields need to be searchable.

Only one solution:  MariaDB with "Dynamic Columns".  For discussion of why it is impractical otherwise, and discussion of lesser solutions:
http://mysql.rjweb.org/doc.php/eav

  an auto increment primary key ID along with an app generated uuid key which will be indexed

Once the table (or at least the index) is too big to be cached, UUID/GUIDs lead to lots of random disk I/O and destroy the cache.  Further discussion (and a workaround):  http://mysql.rjweb.org/doc.php/uuid