MySQL Bugs: #39407: Auto increment failure on concurrent DML (Innodb and Falcon)

Bug #39407	Auto increment failure on concurrent DML (Innodb and Falcon)
Submitted:	11 Sep 2008 19:12	Modified:	6 Oct 2008 19:28
Reporter:	Philip Stoev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1-bzr, 6.0.7	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	auto_increment falcon innodb regression

Description:
When executing a DML scenario which causes records to be inserted into a log table, the server starts to report:

query 'delete from tb2_eng1 where f1=@tmp_num' failed: 1022: Can't write; duplicate key in table 'tb1_logs'

The table in question has just a single key, an auto_increment field, and all updates to it are just insertions that made by inserting a NULL value in the primary key.

Upon inspection, the table is revealed to contain 283193 records, but the max pk value is 2147483647 which is the maximum allowed int(10) integer. primary key values are distributed evenly throughout the entire integer space.

mysql> select FLOOR(i1/100000000) as Q, count(*) from tb1_logs group by Q;
+------+----------+
| Q    | count(*) |
+------+----------+
|    0 |    33969 |
|    1 |    20120 |
|    2 |    12444 |
|    3 |    11958 |
|    4 |    18747 |
|    5 |    13823 |
|    6 |    12908 |
|    7 |    11760 |
|    8 |     6817 |
|    9 |    11310 |
|   10 |    14957 |
|   11 |     9001 |
|   12 |     7771 |
|   13 |     9306 |
|   14 |    10175 |
|   15 |    10430 |
|   16 |     9823 |
|   17 |    10561 |
|   18 |    12676 |
|   19 |    15626 |
|   20 |    13958 |
|   21 |     5053 |
+------+----------+
22 rows in set (0.40 sec)

This bug was not present in 6.0.6.

How to repeat:
A test case will be uploaded shortly.

Test case for bug 39407

Attachment: bug39407.zip (application/x-zip-compressed, text), 19.95 KiB.

To reproduce, please place the *.txt files in mysql-test and the *.test files in mysql-test/t. Then run:

$ perl ./mysql-test-run.pl \
  --stress \
  --stress-test-file=bug39407_run.txt \
  --stress-init-file=bug39407_init.txt \
  --stress-test-duration=1200 \
  --stress-threads=25 \
  --skip-ndb \
  --mysqld=--innodb

While this is running, observe that SELECT max(i1) from tb1_logs will start returning huge integer values. Shortly afterwards, all insertions in this table will become impossible and mysql-test/var/stress/error* will shart showing the "duplicate key" error.

Both partitioning and triggers appear to be required, however it is possible that that only one trigger and/or one partitioned table is sufficient for the problem to be observed.

Thank you for the report.

I can not repeat described behavior on 32-bit Linux with current bzr sources. Please indicate if special requirements needed to repeat the bug.

Falcon is also affected.

Sveta, please try with 64-bit glibc binary from http://clustra.norway.sun.com/~bteam/my/build-200809051514-6.0.7-alpha/mysql-6.0.7-alpha-b...

Thank you!

Since this affects both InnoDB and Falcon, it is not properly categorized as a Server:InnoDB issue.  Changing Category to just Server.

Thank you for the report.

Verified on 32-bit Linux using mysql-6.0.7-alpha-linux-i686-glibc23.tar.gz package and debug server.

The auto_increment code for partitioning in 6.0 differs from the one in 5.1 currently (but will probably be backported by bug#38804), so it seems strange that the bug affect both versions. I'm looking in to it...

Also present in 5.1.28 release binary (nondebug).

This is only related to triggers and auto_increment, since I was able to repeat it without partitioning.

The problem is that handler::update_auto_increment reserves an increasing auto_increment interval for each triggered insert and stores it in thd->auto_inc_intervals_in_cur_stmt_for_binlog, which is not reused for the next triggered insert, since there was a ha_release_auto_increment call in between.

Solution is to always only request one auto_increment value if called by a trigger:
=== modified file 'sql/handler.cc'
--- sql/handler.cc	2008-09-05 15:21:59 +0000
+++ sql/handler.cc	2008-09-30 20:27:51 +0000
@@ -2262,10 +2262,17 @@
         start, starting from AUTO_INC_DEFAULT_NB_ROWS.
         Don't go beyond a max to not reserve "way too much" (because
         reservation means potentially losing unused values).
+        - if called within a trigger, only request one value, since it
+        could be triggered by many rows, which all is treated as a separate
+        insert within the same statement.
       */
       if (nb_already_reserved_intervals == 0 &&
           (estimation_rows_to_insert > 0))
         nb_desired_values= estimation_rows_to_insert;
+      else if (thd->in_sub_stmt & SUB_STMT_TRIGGER)
+      {
+        nb_desired_values= 1;
+      }
       else /* go with the increasing defaults */
       {
         /* avoid overflow in formula, with this if() */

I have tested this in 5.1-main and with the backport of bug#33479 to 5.1 and it seems like it works, I will propose a patch after some extended testing (especially when using an auto_increment table with a trigger to insert in an other auto_increment table, when inserting with multi-row insert statements...)

Probably a duplicate of bug#31612