MySQL Bugs: #57890: Assertion failed: next_insert_id == 0 with on duplicate key update

Bug #57890	Assertion failed: next_insert_id == 0 with on duplicate key update
Submitted:	1 Nov 2010 11:17	Modified:	13 Dec 2010 7:46
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.53-debug, 5.5.8-debug	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
Version: '5.1.51-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Editi
Assertion failed: next_insert_id == 0, file .\handler.cc, line 4611

mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2086]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!handler::ha_external_lock()[handler.cc:4611]
mysqld-debug.exe!ha_partition::external_lock()[ha_partition.cc:2756]
mysqld-debug.exe!handler::ha_external_lock()[handler.cc:4617]
mysqld-debug.exe!unlock_external()[lock.cc:786]
mysqld-debug.exe!mysql_unlock_tables()[lock.cc:391]
mysqld-debug.exe!close_thread_tables()[sql_base.cc:1346]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1672]
mysqld-debug.exe!do_command()[sql_parse.cc:888]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1136]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:293]
mysqld-debug.exe!_threadstart()[thread.c:277]

How to repeat:
on debug server:

drop table if exists t;
create table `t` (`a` int auto_increment primary key) engine=myisam
partition by key(a) partitions 2;
set sql_mode='';
insert into `t` set `a` = 0.7 on duplicate key update `a` = 0;
insert into `t` set `a` = 0.7 on duplicate key update `a` = 0;

Verified on Ubuntu:

openxs@ubuntu:~/dbs/5.5$ bin/mysql --no-defaults -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.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> create table `t` (`a` int auto_increment primary key) engine=myisam
    -> partition by key(a) partitions 2;
Query OK, 0 rows affected (0.09 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into `t` set `a` = 0.7 on duplicate key update `a` = 0;
Query OK, 1 row affected (0.09 sec)

mysql> insert into `t` set `a` = 0.7 on duplicate key update `a` = 0;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 101101 13:36:41 mysqld_safe Number of processes running now: 0
101101 13:36:41 mysqld_safe mysqld restarted

mysql> exit
Bye
openxs@ubuntu:~/dbs/5.5$ tail -80 var/ubuntu.err 
101101 12:14:25 [Note] /home/openxs/dbs/5.5/libexec/mysqld: Normal shutdown

101101 12:14:25 [Note] Event Scheduler: Purging the queue. 0 events
101101 12:14:25  InnoDB: Starting shutdown...
101101 12:14:26  InnoDB: Shutdown completed; log sequence number 1606401
101101 12:14:26 [Note] /home/openxs/dbs/5.5/libexec/mysqld: Shutdown complete

101101 12:14:26 mysqld_safe mysqld from pid file /home/openxs/dbs/5.5/var/ubuntu.pid ended
101101 13:36:22 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/5.5/var
101101 13:36:23 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
101101 13:36:23  InnoDB: highest supported file format is Barracuda.
101101 13:36:23 InnoDB 1.1.2 started; log sequence number 1606401
101101 13:36:23 [Note] Event Scheduler: Loaded 0 events
101101 13:36:23 [Note] /home/openxs/dbs/5.5/libexec/mysqld: ready for connections.
Version: '5.5.7-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: handler.cc:4634: int handler::ha_external_lock(THD*, int): Assertion `next_insert_id == 0' failed.
101101 13:36:41 - 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=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337925 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xa07cff8
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 = 0xa91fa35c thread_stack 0x30000
/home/openxs/dbs/5.5/libexec/mysqld(my_print_stacktrace+0x26)[0x863a838]
/home/openxs/dbs/5.5/libexec/mysqld(handle_segfault+0x2dd)[0x827be54]
[0x3b7400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182)[0x479a82]
/lib/tls/i686/cmov/libc.so.6(__assert_fail+0xf8)[0x46f718]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN7handler16ha_external_lockEP3THDi+0x62)[0x83d06f0]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN12ha_partition13external_lockEP3THDi+0xe0)[0x83d8c54]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN7handler16ha_external_lockEP3THDi+0x84)[0x83d0712]
/home/openxs/dbs/5.5/libexec/mysqld[0x8274660]
/home/openxs/dbs/5.5/libexec/mysqld(_Z19mysql_unlock_tablesP3THDP13st_mysql_lock+0x73)[0x8273d65]
/home/openxs/dbs/5.5/libexec/mysqld(_Z19close_thread_tablesP3THD+0x33f)[0x82de6e8]
/home/openxs/dbs/5.5/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x7eb3)[0x8294b4b]
/home/openxs/dbs/5.5/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1d6)[0x8296ea5]
/home/openxs/dbs/5.5/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x9ce)[0x828b10f]
/home/openxs/dbs/5.5/libexec/mysqld(_Z10do_commandP3THD+0x242)[0x828a547]
/home/openxs/dbs/5.5/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x199)[0x82887b1]
/home/openxs/dbs/5.5/libexec/mysqld(handle_one_connection+0x28)[0x8288611]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e)[0x94d96e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e)[0x519a4e]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa0a4040 = insert into `t` set `a` = 0.7 on duplicate key update `a` = 0
thd->thread_id=1
thd->killed=NOT_KILLED
...

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/123574

3553 Mattias Jonsson	2010-11-11
      Bug#57890: Assertion failed: next_insert_id == 0
                 with on duplicate key update
      
      There was a missed corner case in the partitioning
      handler, which caused the next_insert_id to be changed
      in the second level handlers (i.e the hander of a partition),
      which caused this debug assertion.
      
      The solution was to always ensure that only the partitioning
      level generates auto_increment values, since if it was done
      within a partition, it may fail to match the partition
      function.
     @ mysql-test/suite/parts/inc/partition_auto_increment.inc
        Added tests
     @ mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
        updated results
     @ mysql-test/suite/parts/r/partition_auto_increment_innodb.result
        updated results
     @ mysql-test/suite/parts/r/partition_auto_increment_memory.result
        updated results
     @ mysql-test/suite/parts/r/partition_auto_increment_myisam.result
        updated results
     @ sql/ha_partition.cc
        In <engine>::write_row the auto_inc value is generated
        through handler::update_auto_increment (which calls <engine>::get_auto_increment() if needed).
        If:
        * INSERT_ID was set to 0
        * it was updated to 0 by 'INSERT ... ON DUPLICATE KEY UPDATE' and changed partitions for the row
        Then it would try to generate a auto_increment value in the
        <engine for a specific partition>::write_row, which will
        trigger the assert.
        
        So the solution is to prevent this by,
        in ha_partition::write_row set auto_inc_field_not_null and
        add MODE_NO_AUTO_VALUE_ON_ZERO
        in ha_partition::update_row (when changing partition) temporary
        set table->next_number_field to NULL which calling the
        partitions ::write_row().

pushed to mysql-5.1-bugteam and merged into mysql-5.5-bugteam and mysql-trunk-bugfixing.

Documented in the 5.1.54 and 5.5.8 changelogs as follows:

        An INSERT ... ON DUPLICATE KEY UPDATE column = 0 on an
        AUTO_INCREMENT column caused the debug server to crash.

Set Need Merge, waiting for push to -trunk.

Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)

Issue does not occur in a 5.6 release; no additional changelog entry required.

Closed.

Pushed into mysql-5.1 5.1.55 (revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (version source revid:sunanda.menon@oracle.com-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)

Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)