MySQL Bugs: #27536: INSERT INTO ... VALUES (),(),...,() + autocommit enabled not Atomic!!

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:	None
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

Description:
The NDB engine is a transactional engine and should support ACID transactions at least for DML. 

However, when a temporary error condition (temporary error 233 out of MaxNoOfConcurrentOperations) is encountered during insertion of multiple rows with a single INSERT statement, the statement fails without rolling back if autocommit is enabled.

How to repeat:
DROP TABLE IF EXISTS `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;

INSERT INTO `test_ndb` VALUES
 ('5139','5139','5139','5139')
,...,
,..., (many rows)
,...,
('39860','39860','39860','39860');

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
Query OK, 9004 rows affected (0.60 sec)
Records: 9004  Duplicates: 0  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from test_ndb;
+----------+
| count(*) |
+----------+
|     9004 |
+----------+
1 row in set (0.00 sec)

Interestingly, if autocommit is turned off you get two error message and no rows are left in the table:

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER
ERROR 1296 (HY000): Got error 4350 'Transaction already aborted' from NDBCLUSTER

Suggested fix:
It's almost as if enabling autocommit results in each row being committed immediately. If that is the case, it should be fixed - autocommit should not do that. Atomicity should have at least statement scope when autocommit is enabled.

violation of A in ACID

Attachment: ins.sql.gz (application/x-gzip, text), 214.32 KiB.

config.ini for the cluster

Attachment: config.ini (application/octet-stream, text), 416 bytes.

how does innodb handle this?

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.

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"

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

test case

Attachment: ndb_bug27536.test.tar.bz2 (application/octet-stream, text), 130.56 KiB.

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