Hi, Federico! On Dec 13, Federico Razzoli wrote:

INSERT SELECT is not standard SQL. But there is no reason to have a duplicate value error.

Why do you think it's not? Here, in sql:2003 standard, part 2: <insert statement> ::= INSERT INTO <insertion target> <insert columns and source> <insert columns and source> ::= <from subquery> | <from constructor> | <from default> <from subquery> ::= [ <left paren> <insert column list> <right paren> ] [ <override clause> ] <query expression> <query expression> ::= [ <with clause> ] <query expression body> and so on, from <query expression body> via <query term> to <query primary> to <simple table> to <query specification>, which is <query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression> So, INSERT INTO ... SELECT seems to be a valid standard syntax. Regards, Sergei

-------------------------------------------- Sab 13/12/14, Roberto Spadim ha scritto:

Oggetto: [Maria-discuss] Doubt about 'atomic' insert A: "Maria Discuss" Data: Sabato 13 dicembre 2014, 19:13

Hi guysi'm with a doubt about the "standard" (sql standand?) error reporting , about INSERT SELECT... should this insert select return duplicate key?INSERT INTO errorsX (id) SELECT MAX(id)+1 FROM errorsX;

considering:----CREATE TABLE errorsX( id INT NOT NULL DEFAULT 0, PRIMARY KEY (id)) ;----

some more points, that i read at mysql documentation and mariadb jira (i didn't read the mariadb kb yet) ---- 1) reported at MDEV-7314, from Jean Weisbuch (jean@phpnet.org) ... Hitting a duplicate key on such query means that no lock has been issued. The KB on concurrent INSERTS on *MyISAM* states that : If the binary log is used, CREATE TABLE ... SELECT and INSERT ... SELECT statements cannot use concurrent inserts. These statements acquire a read lock on the table, so concurrent inserts will need to wait. This way the log can be safely used to restore data. ---- 2) from mysql documentation http://dev.mysql.com/doc/refman/5.7/en/insert-select.html - To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT http://dev.mysql.com/doc/refman/5.7/en/insert-select.html statements. The order in which rows are returned by a SELECT http://dev.mysql.com/doc/refman/5.7/en/select.html statement with no ORDER BY clause is not determined. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave; this can lead to inconsistencies between them. To prevent this from occurring, you should always write INSERT ... SELECT statements that are to be replicated as INSERT ... SELECT ... ORDER BY *column*. The choice of *column* does not matter as long as the same order for returning the rows is enforced on both the master and the slave. See also Section 17.4.1.16, “Replication and LIMIT” http://dev.mysql.com/doc/refman/5.7/en/replication-features-limit.html. Due to this issue, INSERT ... SELECT ON DUPLICATE KEY UPDATE http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html and INSERT IGNORE ... SELECT http://dev.mysql.com/doc/refman/5.7/en/insert-select.htmlstatements are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when usingMIXED mode. (Bug #11758262, Bug #50439) See also Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication” http://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html. In MySQL 5.7, an INSERT ... SELECT statement that acted on partitioned tables using a storage engine such asMyISAM http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html that employs table-level locks locks all partitions of the target table; however, only those partitions that are actually read from the source table are locked. (This does not occur with tables using storage engines such as InnoDB http://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html that employ row-level locking.) See Section 18.6.4, “Partitioning and Locking” http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking.html, for more information. ---- 3) from http://dev.mysql.com/doc/refman/5.7/en/replication-features-limit.html 17.4.1.16 Replication and LIMIT Statement-based replication of LIMIT clauses in DELETE http://dev.mysql.com/doc/refman/5.7/en/delete.html, UPDATE http://dev.mysql.com/doc/refman/5.7/en/update.html, and INSERT ... SELECT http://dev.mysql.com/doc/refman/5.7/en/insert-select.html statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY clause.) When such a statement is encountered: - When using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued. Currently, when using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue. (Bug #42851) - When using MIXED mode, the statement is now automatically replicated using row-based mode. ------ i don't see a standard yet, any idea?

Hi Elena! 2014-12-13 21:02 GMT-02:00 Elena Stepanova :

I suppose you forgot to mention that you are doing it on an Aria table, concurrently, simultaneously from several threads?

no, i'm considering any engine, just to know when it's possible to a insert return duplicate error, why, how, etc... i don't see a standard here, i didn't see a standard in others databases too, that's why i'm asking to community

Hi elena! I'm considering all engines The query insert into table select max(pk)+1 from table; Should be executed different with different storage engines? Or the results should be the same? For example, i consider that max(pk)+1 Will always get the highest value of pk, and never insert a duplicate value, if it insert a duplicate value, that's not the max() value of pk, or i'm wrong? Em domingo, 14 de dezembro de 2014, Elena Stepanova escreveu:

Roberto,

On 14.12.2014 8:37, Roberto Spadim wrote:

...

Hi Elena!

2014-12-13 21:02 GMT-02:00 Elena Stepanova :

...

I suppose you forgot to mention that you are doing it on an Aria table, concurrently, simultaneously from several threads?

no, i'm considering any engine, just to know when it's possible to a

insert return duplicate error, why, how, etc... i don't see a standard here, i didn't see a standard in others databases too, that's why i'm asking to community

Without a context, "no" is a natural answer because nobody can predict all preconditions for such a generic question. But the context does matter. I know that you raised the question while playing with MDEV-7314, others don't.

Aria allows concurrent INSERTs to the same table, while MyISAM does not. ( https://mariadb.com/kb/en/mariadb/documentation/storage- engines/aria/aria-faq/#advantages-of-aria-compared-to-myisam)

You can see the difference if you modify your initial example to something easily "serializable" for the naked eye.

Compare:

*MyISAM*:

# CONNECTION 1

MariaDB [test]> create table t_myisam (pk int primary key) engine=MyISAM; Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> insert into t_myisam values (1); Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t_myisam select max(pk)+sleep(10)+1 from t_myisam;

# It starts sleeping

# CONNECTION 2

MariaDB [test]> insert into t_myisam select max(pk)+1 from t_myisam;

# waits until the one in the 1st connection has finished, and then inserts the next pk:

# CONNECTION 1

Query OK, 1 row affected (10.01 sec) Records: 1 Duplicates: 0 Warnings: 0

# CONNECTION 2

Query OK, 1 row affected (8.66 sec) Records: 1 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from t_myisam; +----+ | pk | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)

*Aria*:

# CONNECTION 1

MariaDB [test]> create table t_aria (pk int primary key) engine=Aria; Query OK, 0 rows affected (0.44 sec)

MariaDB [test]> insert into t_aria values (1); Query OK, 1 row affected (0.06 sec)

MariaDB [test]> insert into t_aria select max(pk)+sleep(10)+1 from t_aria;

It starts sleeping

# CONNECTION 2

MariaDB [test]> insert into t_aria select max(pk)+1 from t_aria; Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0

# CONNECTION 1

MariaDB [test]> insert into t_aria select max(pk)+sleep(10)+1 from t_aria; ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

That's why you are getting the duplicate key errors on Aria tables in that particular scenario.

Whether it's a bug or not, is another question. I don't see how it can work any other way and still allow concurrent INSERTs, but maybe somebody who knows more about Aria internals does.

Regards, Elena

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

multiple< concurrent inserts

Hi guys, i agree with you federico, sql is a language, only, and engine do what it's supossed to do considering all, could anyone check if i'm right, just to explain the 'standard' that i was looking: 1) aria report duplicate keys errors with row format = page, because of 2) innodb it don't report errors with read-repeatable (maybe with a differente tx_isolation it report i must test) 3) myisam don't report cause it allow only one concurrent insert (not multiple as aria) 4) toku works like innodb if that's right, could we include a topic at KB, to explain how concurrent inserts, and transaction level, could 'change' how inserts are handled? to have a good documentation if not, please point where it's wrong 2014-12-15 10:00 GMT-02:00 Federico Razzoli :

Roberto,

The meaning of an SQL is not engine-dependent. But here you have a timing problem.

As Elena explained, Aria allows concurrent inserts to the same table. As a consequence, if MAX(id) is 100, several threads could try to insert 101, resulting in duplicate key errors.

Your application should be prepared to handle this, for the same reasons an application should normally be able to handle InnoDB's deadlocks.

Regards Federico

-------------------------------------------- Dom 14/12/14, Roberto Spadim ha scritto:

Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert A: "Elena Stepanova" Cc: "Maria Discuss" Data: Domenica 14 dicembre 2014, 19:11

Hi elena! I'm considering all engines The query insert into table select max(pk)+1 from table;

Should be executed different with different storage engines? Or the results should be the same?

For example, i consider that max(pk)+1 Will always get the highest value of pk, and never insert a duplicate value, if it insert a duplicate value, that's not the max() value of pk, or i'm wrong?

Em domingo, 14 de dezembro de 2014, Elena Stepanova escreveu: Roberto,

On 14.12.2014 8:37, Roberto Spadim wrote:

Hi Elena!

2014-12-13 21:02 GMT-02:00 Elena Stepanova :

I suppose you forgot to mention that you are doing it on an Aria table,

concurrently, simultaneously from several threads?

no, i'm considering any engine, just to know when it's possible to a insert

return duplicate error, why, how, etc... i don't see a standard here, i

didn't see a standard in others databases too, that's why i'm asking to

community

Without a context, "no" is a natural answer because nobody can predict all preconditions for such a generic question. But the context does matter. I know that you raised the question while playing with MDEV-7314, others don't.

Aria allows concurrent INSERTs to the same table, while MyISAM does not. ( https://mariadb.com/kb/en/mariadb/documentation/storage-engines/aria/aria-fa... )

You can see the difference if you modify your initial example to something easily "serializable" for the naked eye.

Compare:

*MyISAM*:

# CONNECTION 1

MariaDB [test]> create table t_myisam (pk int primary key) engine=MyISAM;

Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> insert into t_myisam values (1);

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t_myisam select max(pk)+sleep(10)+1 from t_myisam;

# It starts sleeping

# CONNECTION 2

MariaDB [test]> insert into t_myisam select max(pk)+1 from t_myisam;

# waits until the one in the 1st connection has finished, and then inserts the next pk:

# CONNECTION 1

Query OK, 1 row affected (10.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

# CONNECTION 2

Query OK, 1 row affected (8.66 sec)

Records: 1 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from t_myisam;

+----+

| pk |

+----+

| 1 |

| 2 |

| 3 |

+----+

3 rows in set (0.00 sec)

*Aria*:

# CONNECTION 1

MariaDB [test]> create table t_aria (pk int primary key) engine=Aria;

Query OK, 0 rows affected (0.44 sec)

MariaDB [test]> insert into t_aria values (1);

Query OK, 1 row affected (0.06 sec)

MariaDB [test]> insert into t_aria select max(pk)+sleep(10)+1 from t_aria;

It starts sleeping

# CONNECTION 2

MariaDB [test]> insert into t_aria select max(pk)+1 from t_aria;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

# CONNECTION 1

MariaDB [test]> insert into t_aria select max(pk)+sleep(10)+1 from t_aria;

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

That's why you are getting the duplicate key errors on Aria tables in that particular scenario.

Whether it's a bug or not, is another question. I don't see how it can work any other way and still allow concurrent INSERTs, but maybe somebody who knows more about Aria internals does.

Regards,

Elena

-- Roberto Spadim SPAEmpresarialEng. Automação e Controle

-----Segue allegato-----

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

right i forgot to set autocommit=0 while testing, i will continue tests with innodb any others errros about (1),(2),(3),(4) ? 2014-12-15 15:50 GMT-02:00 Federico Razzoli :

With InnoDB you will get a deadlock error. Your application will need to retry the query if you get error 1213. Don't know about TokuDB, but I suppose it's the same.

Regards Federico

-------------------------------------------- Lun 15/12/14, Roberto Spadim ha scritto:

Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert A: "Federico Razzoli" Cc: "Maria Discuss" Data: Lunedì 15 dicembre 2014, 18:23

Hi guys, i agree with you federico, sql is a language, only, and engine do what it's supossed to do considering all, could anyone check if i'm right, just to explain the 'standard' that i was looking:1) aria report duplicate keys errors with row format = page, because of >multiple< concurrent inserts 2) innodb it don't report errors with read-repeatable (maybe with a differente tx_isolation it report i must test) 3) myisam don't report cause it allow only one concurrent insert (not multiple as aria)4) toku works like innodb

if that's right, could we include a topic at KB, to explain how concurrent inserts, and transaction level, could 'change' how inserts are handled? to have a good documentation if not, please point where it's wrong

2014-12-15 10:00 GMT-02:00 Federico Razzoli :Roberto,

The meaning of an SQL is not engine-dependent. But here you have a timing problem.

As Elena explained, Aria allows concurrent inserts to the same table. As a consequence, if MAX(id) is 100, several threads could try to insert 101, resulting in duplicate key errors.

Your application should be prepared to handle this, for the same reasons an application should normally be able to handle InnoDB's deadlocks.

Regards

Federico

--------------------------------------------

Dom 14/12/14, Roberto Spadim ha scritto:

Oggetto: Re: [Maria-discuss] Doubt about 'atomic' insert

A: "Elena Stepanova"

Cc: "Maria Discuss"

Data: Domenica 14 dicembre 2014, 19:11

Hi elena! I'm

considering all engines

The query insert into table select max(pk)+1 from table;

Should be executed different with

different storage engines? Or the results should be the

same?

For example, i

consider that

max(pk)+1 Will always get the

highest value of pk, and never insert a duplicate value, if

it insert a duplicate value, that's not the max() value

of pk, or i'm wrong?

Em domingo, 14 de dezembro de

2014, Elena Stepanova

escreveu:

Roberto,

On 14.12.2014 8:37, Roberto Spadim wrote:

Hi Elena!

2014-12-13 21:02 GMT-02:00 Elena Stepanova :

I suppose you forgot to mention that you are doing it on an

Aria table,

concurrently, simultaneously from several threads?

no, i'm considering any engine, just to know when

it's possible to a insert

return duplicate error, why, how, etc... i don't see a

standard here, i

didn't see a standard in others databases too,

that's why i'm asking to

community

Without a context, "no" is a natural answer

because nobody can predict all preconditions for such a

generic question. But the context does matter. I know that

you raised the question while playing with MDEV-7314, others

don't.

Aria allows concurrent INSERTs to the same table, while

MyISAM does not. ( https://mariadb.com/kb/en/mariadb/documentation/storage-engines/aria/aria-fa... )

You can see the difference if you modify your initial

example to something easily "serializable" for the

naked eye.

Compare:

*MyISAM*:

# CONNECTION 1

MariaDB [test]> create table t_myisam (pk int primary

key) engine=MyISAM;

Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> insert into t_myisam values (1);

Query OK, 1 row affected (0.01 sec)

MariaDB [test]> insert into t_myisam select

max(pk)+sleep(10)+1 from t_myisam;

# It starts sleeping

# CONNECTION 2

MariaDB [test]> insert into t_myisam select max(pk)+1

from t_myisam;

# waits until the one in the 1st connection has finished,

and then inserts the next pk:

# CONNECTION 1

Query OK, 1 row affected (10.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

# CONNECTION 2

Query OK, 1 row affected (8.66 sec)

Records: 1 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from t_myisam;

+----+

| pk |

+----+

| 1 |

| 2 |

| 3 |

+----+

3 rows in set (0.00 sec)

*Aria*:

# CONNECTION 1

MariaDB [test]> create table t_aria (pk int primary key)

engine=Aria;

Query OK, 0 rows affected (0.44 sec)

MariaDB [test]> insert into t_aria values (1);

Query OK, 1 row affected (0.06 sec)

MariaDB [test]> insert into t_aria select

max(pk)+sleep(10)+1 from t_aria;

It starts sleeping

# CONNECTION 2

MariaDB [test]> insert into t_aria select max(pk)+1 from

t_aria;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

# CONNECTION 1

MariaDB [test]> insert into t_aria select

max(pk)+sleep(10)+1 from t_aria;

ERROR 1062 (23000): Duplicate entry '2' for key

'PRIMARY'

That's why you are getting the duplicate key errors on

Aria tables in that particular scenario.

Whether it's a bug or not, is another question. I

don't see how it can work any other way and still allow

concurrent INSERTs, but maybe somebody who knows more about

Aria internals does.

Regards,

Elena

--

Roberto Spadim

SPAEmpresarialEng. Automação e

Controle

-----Segue allegato-----

_______________________________________________

Mailing list: https://launchpad.net/~maria-discuss

Post to : maria-discuss@lists.launchpad.net

Unsubscribe : https://launchpad.net/~maria-discuss

More help : https://help.launchpad.net/ListHelp

_______________________________________________

Mailing list: https://launchpad.net/~maria-discuss

Post to : maria-discuss@lists.launchpad.net

Unsubscribe : https://launchpad.net/~maria-discuss

More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarialEng. Automação e Controle

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

Hi Sergei! 2014-12-13 21:06 GMT-02:00 Sergei Golubchik :

Depends on how many rows the table errorsX has.

If it has only one row, than this INSERT ... SELECT will insert a one row with a different id. No error.

If it has more than one row, this INSERT ... SELECT will try to insert more than one row, all with the same id value - there will be a duplicate key error.

Regards, Sergei

well, let me reformulate the query... INSERT INTO errorsX (id) SELECT IFNULL(MAX(id),0)+1 FROM errorsX GROUP BY NULL; considering only a single row result with always >0 not null values