Bug #27536 | INSERT INTO ... VALUES (),(),...,() + autocommit enabled not Atomic!! | ||
---|---|---|---|
Submitted: | 29 Mar 2007 20:09 | Modified: | 11 Apr 2007 9:21 |
Reporter: | Roland Bouman | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.1.16 | OS: | Linux (linux) |
Assigned to: | Sveta Smirnova | CPU Architecture: | Any |
Tags: | autocommit, insert, MaxNoOfConcurrentOperations, ndb, temporary error 233 |
[29 Mar 2007 20:09]
Roland Bouman
[29 Mar 2007 20:13]
Roland Bouman
violation of A in ACID
Attachment: ins.sql.gz (application/x-gzip, text), 214.32 KiB.
[29 Mar 2007 20:14]
Roland Bouman
config.ini for the cluster
Attachment: config.ini (application/octet-stream, text), 416 bytes.
[3 Apr 2007 8:37]
Hartmut Holzgraefe
how does innodb handle this?
[3 Apr 2007 12:30]
Roland Bouman
Hi Hartmut, Good Spot!! on InnoDB, an INSERT INTO VALUES (),...,() statement will leave the rows in the table. I checked by adding the first row in the insert script again at the bottom of the script. Inserting it will eventually lead to a primary key constraint violation after which the rows seem to be committed. BAD! A INSERT INTO .. SELECT .. FROM statement does *NOT* show this behaviour. A failure results in all a rollback just like it should. This makes it a far more serious bug in my opinion - it will probably affect all transactional tables. There must be some problem in how the handler deals with an error.
[10 Apr 2007 10:48]
Sveta Smirnova
Thank you for the report. According to AUTOCOMMIT description: #AUTOCOMMIT = {0 | 1} Set the autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0 you have to use COMMIT to accept a transaction or ROLLBACK to cancel it. By default, client connections begin with AUTOCOMMIT set to 1. If you change AUTOCOMMIT mode from 0 to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 13.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”. (http://dev.mysql.com/doc/refman/5.0/en/set-option.html) And multiple-row INSERT behaviour (http://dev.mysql.com/doc/refman/5.0/en/insert.html) it is not a bug. If you think more detiled documenatation needed, feel free reopen the report and change category to "Documentation"
[10 Apr 2007 11:20]
Roland Bouman
Sveta, thanks for checking but I think you are missing the point. The documentation concerning autocommit you refer to does not specify the scope of the changes. Many people believe that it works on the level of the row, but this would defeat the entire concept of transactions. All database products known to me take autocommit to mean that an implicit commit is executed after each successful execution of a *statement*. A simple test proves that MySQL seems to adhere to this notion of autocommit: /* autocommit enabled throughout the script */ mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) /* myisam tables don't care about transactions */ mysql> create table t_myisam(id int unsigned not null primary key, seq int unsigned not null) engine = MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> insert into t_myisam(id,seq) values (1,1),(2,2),(1,3); ERROR 1582 (23000): Duplicate entry '1' for key 'PRIMARY' /* no concept of transaction so nothing to rollback */ mysql> select * from t_myisam; +----+-----+ | id | seq | +----+-----+ | 1 | 1 | | 2 | 2 | +----+-----+ 2 rows in set (0.00 sec) /* innodb table does care about transactions */ mysql> create table t_innodb(id int unsigned not null primary key, seq int unsigned not null) engine = InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> insert into t_innodb(id,seq) values (1,1),(2,2),(1,3); ERROR 1582 (23000): Duplicate entry '1' for key 'PRIMARY' /* violation of the constraint results in implicit rollback as expected*/ mysql> select * from t_innodb; Empty set (0.00 sec) /* ndb also cares about transactions */ mysql> create table t_ndb(id int unsigned not null primary key, seq int unsigned not null) engine = ndb; Query OK, 0 rows affected (0.94 sec) mysql> insert into t_ndb(id,seq) values (1,1),(2,2),(1,3); ERROR 1022 (23000): Can't write; duplicate key in table 't_ndb' /* violation of the constraint results in implicit rollback as expected*/ mysql> select * from t_ndb; Empty set (0.00 sec) So - it really is down to the data set i attached in the file. Something there causes an implicit commit. Note that adding one row that violates one of the constraints to the dataset and inserting it into an INNODB table using the INSERT INTO ... SELECT .. syntax does not commit the data - whereas the equivalent INSERT INTO ... VALUES (),....,() statement does result in an commit. Please verify with the attached dataset and let me know if you need help in reproducing it
[11 Apr 2007 9:06]
Sveta Smirnova
test case
Attachment: ndb_bug27536.test.tar.bz2 (application/octet-stream, text), 130.56 KiB.
[11 Apr 2007 9:21]
Sveta Smirnova
Thank you for the feedback. I can not repeat described behaviour with current sources and attached test case: DROP TABLE IF EXISTS `test_ndb`; Warnings: Note 1051 Unknown table 'test_ndb' CREATE TABLE `test_ndb` ( `pk_name` varchar(41) NOT NULL, `uk_name1` varchar(41) NOT NULL, `uk_name2` varchar(41) NOT NULL, `name` varchar(41) DEFAULT NULL, PRIMARY KEY (`pk_name`), UNIQUE KEY `uk_name1` (`uk_name1`), UNIQUE KEY `uk_name2` (`uk_name2`) USING HASH ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; set autocommit=on; ERROR HY000: Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER rollback; select count(*) from test_ndb; count(*) 0