Bug #50619 assert in handler::update_auto_increment
Submitted: 26 Jan 2010 11:02 Modified: 10 Jan 2011 3:31
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.44,5.5.99-m3,6.0.14-alpha OS:Any
Assigned to: Jon Olav Hauglid
Tags: assert, auto_increment
Triage: Triaged: D1 (Critical)

[26 Jan 2010 11:02] Matthias Leich
Description:
The assert happens in handler.cc line 2326:
DBUG_ASSERT(next_insert_id >= auto_inc_interval_for_cur_row.minimum());

My script:
----------
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 ( f2 BIGINT, f1 INT AUTO_INCREMENT, PRIMARY KEY (f1));
# Harmless INSERT INTO t1 ( f1 , f2 ) VALUES ( 2147483646 , 0 );
# Needed for crash
INSERT INTO t1 (f1,f2) VALUES (2147483647,0);
# Harmless INSERT INTO t1 (f2) VALUES (100),(26) ON DUPLICATE KEY UPDATE f1 = 1.1;
# Here comes the crash
INSERT INTO t1 (f2) VALUES (100),(26) ON DUPLICATE KEY UPDATE f1 = -1.1;

DROP TABLE t1;

Result on 5.1.44-debug
(mysql-5.1 , revno: 3315 2010-01-15)
------------------------------------
...
main.ml102                               [ fail ]
        Test ended at 2010-01-26 11:45:14

CURRENT_TEST: main.ml102
mysqltest: At line 11: query 'INSERT INTO t1 (f2) VALUES (100),(26) ON DUPLICATE KEY UPDATE f1 = -1.1' failed: 2013: Lost connection to MySQL server during query
...
Version: '5.1.44-debug-log'  socket: '/work2/5.1/mysql-5.1-work/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution
mysqld: handler.cc:2326: int handler::update_auto_increment(): Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed.
100126 13:45:14 - mysqld got signal 6 ;
...
#0  0x00007f83f0f03ce6 in pthread_kill () from /lib64/libpthread.so.0
#0  0x00007f83f0f03ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b1d813 in my_write_core (sig=6) at stacktrace.c:329
#2  0x00000000006bbfd7 in handle_segfault (sig=6) at mysqld.cc:2569
#3  <signal handler called>
#4  0x00007f83f010c5c5 in raise () from /lib64/libc.so.6
#5  0x00007f83f010dbb3 in abort () from /lib64/libc.so.6
#6  0x00007f83f01051e9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000008091ac in handler::update_auto_increment (this=0x11f34e8) at handler.cc:2326
#8  0x0000000000a1dd44 in ha_myisam::write_row (this=0x11f34e8, buf=0x11f3670 "ý\032") at ha_myisam.cc:777
#9  0x00000000008067bb in handler::ha_write_row (this=0x11f34e8, buf=0x11f3670 "ý\032") at handler.cc:4650
#10 0x000000000076afdc in write_record (thd=0x11bd5c8, table=0x121ef48, info=0x40d22280) at sql_insert.cc:1379
#11 0x000000000076fbe2 in mysql_insert (thd=0x11bd5c8, table_list=0x1241920, fields=@0x11bf928, values_list=@0x11bf970, update_fields=@0x11bf958, update_values=@0x11bf940, duplic=DUP_UPDATE, ignore=false) at sql_insert.cc:835
#12 0x00000000006d0eaa in mysql_execute_command (thd=0x11bd5c8) at sql_parse.cc:3181
#13 0x00000000006d70bf in mysql_parse (thd=0x11bd5c8, inBuf=0x12417e8 "INSERT INTO t1 (f2) VALUES (100),(26) ON DUPLICATE KEY UPDATE f1 = -1.1", length=71, found_semicolon=0x40d23ef0) at sql_parse.cc:5961
#14 0x00000000006d7f3a in dispatch_command (command=COM_QUERY, thd=0x11bd5c8, packet=0x1212039 "INSERT INTO t1 (f2) VALUES (100),(26) ON DUPLICATE KEY UPDATE f1 = -1.1", packet_length=71) at sql_parse.cc:1233
#15 0x00000000006d93a4 in do_command (thd=0x11bd5c8) at sql_parse.cc:874
#16 0x00000000006c5765 in handle_one_connection (arg=0x11bd5c8) at sql_connect.cc:1127
#17 0x00007f83f0eff040 in start_thread () from /lib64/libpthread.so.0
#18 0x00007f83f01ad08d in clone () from /lib64/libc.so.6
#19 0x0000000000000000 in ?? ()

Releases showing this bug:
--------------------------
- 5.1.44-debug
  (mysql-5.1 , revno: 3315 2010-01-15)
- 5.1.43-debug
  (mysql-5.1-bugteam revno: 3331 2010-01-25)
- 5.5.99-m3-debug
  (mysql-next-mr , revno: 2965 2010-01-25)
- 6.0.14-alpha-debug
  (mysql-6.0-codebase-bugfixing, revno: 3844 2010-01-25)

My environment:
---------------
- MySQL compiled from source with
  BUILD/compile-pentium64-debug-max
- Intel Core2Duo (64 Bit)
- Linux OpenSuSE 11.0 (64 Bit)

This bug looks similar to
   Bug#46902 Assertion failed: next_insert_id >= 
             auto_inc_interval_for_cur_row.minimum()
but my testcase is quite different.

How to repeat:
See above
[23 Feb 2010 12:17] Matthias Leich
Test hitting the same assert:
CREATE TABLE t1 ( pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
INSERT INTO t1 ( pk ) VALUES (-1);
CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW SET @aux = 1 ;
COMMIT ;
REPLACE INTO t1 ( pk ) VALUES ( NULL ) , ( -1 );
[10 Nov 2010 15:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123436

3179 Jon Olav Hauglid	2010-11-10
      Bug #50619 assert in handler::update_auto_increment
      
      This assert could happen during subsequent inserts if -1 had
      already been inserted into an auto increment column.
      
      The root cause of the problem is that auto increment values are
      internally stored unsigned while auto increment column types can
      be signed values. When an explict value is set for the auto 
      increment column, the internal auto increment value is reset to
      the explicit value + 1. However, if the explicit value is -1,
      converting this value to unsigned and adding 1 will yield 0 as
      a result (due to overflow). Since 0 would be outside the reserved
      auto increment interval for the insert (auto increment values
      should start at 1), the assert would be triggered.
      
      This patch fixes the problem by checking if the explicit value
      is negative. If this is the case, auto increment values are not
      reset.
      
      Test case added to auto_increment.test.
[13 Dec 2010 14:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126652

3424 Jon Olav Hauglid	2010-12-13
      Bug #50619 assert in handler::update_auto_increment
      
      This assert could be triggered if -1 was inserted into
      an auto increment column by a statement writing more than
      one row.
      
      Unless explicitly given, an interval of auto increment values
      is generated when a statement first needs an auto increment
      value. The triggered assert checks that the auto increment
      counter is equal to or higher than the lower bound of this
      interval.
      
      Generally, the auto increment counter starts at 1 and is
      incremented by 1 each time it is used. However, inserting an
      explicit value into the auto increment column, sets the auto
      increment counter to this value + 1 if this value is higher
      than the current value of the auto increment counter.
      
      This bug was triggered if the explicit value was -1. Since the
      value was converted to unsigned before any comparisons were made,
      it was found to be higher than the current vale of the auto
      increment counter and the counter was set to -1 + 1. This value
      was below the reserved interval and caused the assert to be
      triggered the next time the statement tried to write a row.
      
      This patch fixes the problem by only allowing the auto increment
      counter to be set if the given explicit value is positive.
      
      Test case added to auto_increment.test.
[4 Jan 2011 13:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127874

3532 Jon Olav Hauglid	2011-01-04
      Bug #50619 assert in handler::update_auto_increment
      
      This assert could be triggered if -1 was inserted into
      an auto increment column by a statement writing more than
      one row.
      
      Unless explicitly given, an interval of auto increment values
      is generated when a statement first needs an auto increment
      value. The triggered assert checks that the auto increment
      counter is equal to or higher than the lower bound of this
      interval.
      
      Generally, the auto increment counter starts at 1 and is
      incremented by 1 each time it is used. However, inserting an
      explicit value into the auto increment column, sets the auto
      increment counter to this value + 1 if this value is higher
      than the current value of the auto increment counter.
      
      This bug was triggered if the explicit value was -1. Since the
      value was converted to unsigned before any comparisons were made,
      it was found to be higher than the current vale of the auto
      increment counter and the counter was set to -1 + 1. This value
      was below the reserved interval and caused the assert to be
      triggered the next time the statement tried to write a row.
      
      With the patch for Bug#39828, this bug is no longer repeatable.
      Now, -1 + 1 is detected as an "overflow" which causes the auto
      increment counter to be set to ULONGLONG_MAX. This avoids hitting
      the assert for the next insert and causes a new interval of
      auto increment values to be generated. This resolves the issue.
      
      This patch therefore only contains a regression test and no code
      changes. Test case added to auto_increment.test.
[4 Jan 2011 14:31] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:jon.hauglid@oracle.com-20110104143005-920jhiyi67x17kez) (version source revid:jon.hauglid@oracle.com-20110104143005-920jhiyi67x17kez) (merge vers: 5.6.2) (pib:24)
[4 Jan 2011 14:32] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jon.hauglid@oracle.com-20110104142803-kxkbv0ud3x8g2ejd) (version source revid:jon.hauglid@oracle.com-20110104142803-kxkbv0ud3x8g2ejd) (merge vers: 5.5.9) (pib:24)
[4 Jan 2011 14:32] Bugs System
Pushed into mysql-5.1 5.1.55 (revid:jon.hauglid@oracle.com-20110104133637-33qff7ri9vz47r27) (version source revid:jon.hauglid@oracle.com-20110104133637-33qff7ri9vz47r27) (merge vers: 5.1.55) (pib:24)
[4 Jan 2011 14:34] Jon Olav Hauglid
Bug#46902 was marked as a duplicate of this bug.
[10 Jan 2011 3:31] Paul Dubois
Noted in 5.1.55, 5.5.9, 5.6.2 changelogs.

An assertion could be raised if -1 was inserted into an
AUTO_INCREMENT column by a statement writing more than one row.