MySQL Bugs: #43203: Overflow from auto incrementing causes server segv

Bug #43203	Overflow from auto incrementing causes server segv
Submitted:	25 Feb 2009 17:42	Modified:	19 Jun 2010 17:41
Reporter:	Colin Guthrie	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.31, 5.1.33-bzr	OS:	Linux (Mandriva cooker package on x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, innodb, regression, segv

Description:
The server can crash when the value overflow the data storage during and auto_increment insert operation.

How to repeat:
Setup the tables:

drop table if exists overflow_source;
drop table if exists overflow_dest;
create table overflow_source(id int(10) unsigned NOT NULL auto_increment PRIMARY KEY) engine=innodb;
insert into overflow_source values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
create table overflow_dest(id tinyint(3) unsigned NOT NULL auto_increment PRIMARY KEY) engine=innodb;

-- Selecting the id field specifically works as expected
test> insert into overflow_dest SELECT id from overflow_source;
ERROR 1062 (23000): Duplicate entry '255' for key 'PRIMARY'

test> select * from overflow_dest;
Empty set (0.00 sec)

-- Selecting a NULL value to trigger auto-increment fails, badly!
test> insert into overflow_dest SELECT NULL from overflow_source;
ERROR 2013 (HY000): Lost connection to MySQL server during query
test>

-- If we reduce our dataset so that auto-increment values will not go over our limit, things work again:
test> delete from overflow_source LIMIT 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 1 row affected (0.01 sec)

test> insert into overflow_dest SELECT NULL from overflow_source;
Query OK, 255 rows affected (0.01 sec)
Records: 255  Duplicates: 0  Warnings: 0

Here is the appropriate info from the log:

090225 17:27:27  InnoDB: Assertion failure in thread 140583524956496 in file handler/ha_innodb.cc line 7651
InnoDB: Failing assertion: prebuilt->autoinc_last_value >= *first_value
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
090225 17:27:27 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=2
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 133885 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x1744590
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7fdc2710f0c8 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3c) [0x8a9f7c]
/usr/sbin/mysqld(handle_segfault+0x393) [0x5dae93]
/lib64/libpthread.so.0 [0x7fdc26d73d30]
/lib64/libc.so.6(gsignal+0x35) [0x7fdc25837a15]
/lib64/libc.so.6(abort+0x183) [0x7fdc25839243]
/usr/sbin/mysqld [0x76ef08]
/usr/sbin/mysqld(handler::update_auto_increment()+0x228) [0x6caa98]
/usr/sbin/mysqld(ha_innobase::write_row(unsigned char*)+0x393) [0x76e233]
/usr/sbin/mysqld(handler::ha_write_row(unsigned char*)+0x83) [0x6cdbc3]
/usr/sbin/mysqld(write_record(THD*, st_table*, st_copy_info*)+0x6c) [0x65c45c]
/usr/sbin/mysqld(select_insert::send_data(List<Item>&)+0xc0) [0x65cdc0]
/usr/sbin/mysqld [0x636978]
/usr/sbin/mysqld [0x6390e5]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0xa6) [0x645136]
/usr/sbin/mysqld [0x6455fd]
/usr/sbin/mysqld(JOIN::exec()+0x97d) [0x65664d]
/usr/sbin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x1b8) [0x652798]
/usr/sbin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x19c) [0x657f7c]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x50d1) [0x5ec541]
/usr/sbin/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x240) [0x5eccd0]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xdcf) [0x5eee1f]
/usr/sbin/mysqld(do_command(THD*)+0xf8) [0x5ef658]
/usr/sbin/mysqld(handle_one_connection+0x22e) [0x5e227e]
/lib64/libpthread.so.0 [0x7fdc26d6c263]
/lib64/libc.so.6(clone+0x6d) [0x7fdc258e378d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x179a750 = insert into overflow_dest SELECT NULL from overflow_source
thd->thread_id=4
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Suggested fix:
Dunno!

Verified just as described with 5.1.33 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.33-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists overflow_source;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop table if exists overflow_dest;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table overflow_source(id int(10) unsigned NOT NULL auto_increment PRIMARY KEY)
    -> engine=innodb;
Query OK, 0 rows affected (0.40 sec)

mysql> insert into overflow_source values
    -> (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into overflow_source select NULL from overflow_source;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into overflow_source select NULL from overflow_source;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

...

mysql> insert into overflow_source select NULL from overflow_source;
Query OK, 1024 rows affected (0.04 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> create table overflow_dest(id tinyint(3) unsigned NOT NULL auto_increment PRIMARY KEY)
    -> engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into overflow_dest SELECT id from overflow_source;
ERROR 1062 (23000): Duplicate entry '255' for key 'PRIMARY'
mysql> select * from overflow_dest;
Empty set (0.00 sec)

mysql> insert into overflow_dest SELECT NULL from overflow_source;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 090225 20:59:33 mysqld_safe mysqld restarted

mysql> insert into overflow_dest SELECT NULL from overflow_source LIMIT 10;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 10 rows affected (0.16 sec)
Records: 10  Duplicates: 0  Warnings: 0

With 5.0.79 it works as expected (no crash):

mysql> create table overflow_dest(id tinyint(3) unsigned NOT NULL auto_increment PRIMARY KEY) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into overflow_dest SELECT NULL from overflow_source;ERROR 1062 (23000): Duplicate entry '255' for key 1

So, this is a regression bug.

Committed as r4325

Pushed to 5.1.33; Docs please return to "Patch approved" waiting for a 6.0 snapshot.

  Applying InnoDB snashot 5.1-ss4350, part 5.  Fixes
  
  Bug #43203    Overflow from auto incrementing causes server segv
  
  Detailed revision comments:
  
  r4325 | sunny | 2009-03-02 02:28:52 +0200 (Mon, 02 Mar 2009) | 10 lines
  branches/5.1: Bug#43203: Overflow from auto incrementing causes server segv
  It was not a SIGSEGV but an assertion failure. The assertion was checking
  the invariant that *first_value passed in by MySQL doesn't contain a value
  that is greater than the max value for that type. The assertion has been
  changed to a check and if the value is greater than the max we report a
  generic AUTOINC failure.
  
  rb://93
  Approved by Heikki

Noted in 5.1.33 changelog.

For InnoDB tables, overflow in an AUTO_INCREMENT column could cause a
server crash. 

Setting report to Patch Approved pending push into 6.0.x.

Noted in 5.4.2 changelog.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)