Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.14, 10.0, 5.5
-
Component/s: Data Manipulation - Update
-
Labels:None
-
Environment:Red Hat Enterprise Linux
Description
An auto increment value of 0 is created at MariaDB 10.0.13/14 when NULL is used
The MySQL 5.6.15 and MySQL 5.7.5 behave properly.
example 10.0.14:
-------------- SELECT @@version -------------- +---------------------+ | @@version | +---------------------+ | 10.0.14-MariaDB-log | +---------------------+ 1 row in set (0.00 sec) -------------- DROP TABLE IF EXISTS iodku -------------- Query OK, 0 rows affected, 1 warning (0.00 sec) -------------- CREATE TABLE iodku ( id int(10) unsigned NOT NULL AUTO_INCREMENT, value int(11) NOT NULL DEFAULT '0', updates int not null default 0, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- Query OK, 0 rows affected (0.02 sec) -------------- INSERT INTO iodku (id, value) VALUES (NULL, 1), (NULL, 2), (NULL, 3) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1 -------------- Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 -------------- SELECT * FROM iodku -------------- +----+-------+---------+ | id | value | updates | +----+-------+---------+ | 1 | 1 | 0 | | 2 | 2 | 0 | | 3 | 3 | 0 | +----+-------+---------+ 3 rows in set (0.00 sec) -------------- INSERT INTO iodku (id, value) VALUES (NULL, 4), (NULL, 5), (3, -3) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1 -------------- Query OK, 4 rows affected (0.00 sec) Records: 3 Duplicates: 1 Warnings: 0 -------------- SELECT * FROM iodku -------------- +----+-------+---------+ | id | value | updates | +----+-------+---------+ | 1 | 1 | 0 | | 2 | 2 | 0 | | 3 | -3 | 1 | | 4 | 4 | 0 | | 5 | 5 | 0 | +----+-------+---------+ 5 rows in set (0.00 sec) -------------- INSERT INTO iodku (id, value) VALUES (1, -1), (NULL, 6), (NULL, 7) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1 -------------- Query OK, 4 rows affected (0.00 sec) Records: 3 Duplicates: 1 Warnings: 0 -------------- SELECT * FROM iodku -------------- +----+-------+---------+ | id | value | updates | +----+-------+---------+ | 1 | -1 | 1 | | 2 | 2 | 0 | | 3 | -3 | 1 | | 4 | 4 | 0 | | 5 | 5 | 0 | | 7 | 6 | 0 | | 8 | 7 | 0 | +----+-------+---------+ 7 rows in set (0.00 sec) -------------- INSERT INTO iodku (id, value) VALUES (NULL, 8), (2, -2), (NULL, 9) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1 -------------- Query OK, 4 rows affected (0.01 sec) Records: 3 Duplicates: 1 Warnings: 0 -------------- SELECT * FROM iodku -------------- +----+-------+---------+ | id | value | updates | +----+-------+---------+ | 0 | 9 | 0 | | 1 | -1 | 1 | | 2 | -2 | 1 | | 3 | -3 | 1 | | 4 | 4 | 0 | | 5 | 5 | 0 | | 7 | 6 | 0 | | 8 | 7 | 0 | | 10 | 8 | 0 | +----+-------+---------+ 9 rows in set (0.00 sec) -------------- INSERT INTO iodku (id, value) VALUES (4, -4), (NULL, 10), (5, -5), (NULL, 11), (NULL, 12), (1, 1), (NULL, 13) ON DUPLICATE KEY UPDATE value = VALUES(value), updates = updates+1 -------------- Query OK, 13 rows affected (0.00 sec) Records: 7 Duplicates: 6 Warnings: 0 -------------- SELECT * FROM iodku -------------- +----+-------+---------+ | id | value | updates | +----+-------+---------+ | 0 | 13 | 3 | | 1 | 1 | 2 | | 2 | -2 | 1 | | 3 | -3 | 1 | | 4 | -4 | 1 | | 5 | -5 | 1 | | 7 | 6 | 0 | | 8 | 7 | 0 | | 10 | 8 | 0 | | 13 | 10 | 0 | +----+-------+---------+ 10 rows in set (0.00 sec) -------------- DROP TABLE IF EXISTS iodku -------------- Query OK, 0 rows affected (0.14 sec) Bye
There are test cases at the uploaded files.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Debug assertion:
10.0 revno 4504
sql/handler.cc:2941: int handler::update_auto_increment(): Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed. 141120 18:44:55 [ERROR] mysqld got signal 6 ; Some pointers may be invalid and cause the dump to abort. Query (0x7fad41a1d088): INSERT INTO iodku (id, value) VALUES (NULL, 8), (2, -2), (NULL, 9) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1 Connection ID (thread ID): 3
#5 0x00007fad54e437c0 in *__GI_abort () at abort.c:92 #6 0x00007fad54e396f1 in *__GI___assert_fail (assertion=0xfd3090 "next_insert_id >= auto_inc_interval_for_cur_row.minimum()", file=<optimized out>, line=2941, function=0xfd56c0 "int handler::update_auto_increment()") at assert.c:81 #7 0x000000000086e97e in handler::update_auto_increment (this=0x7fad41852088) at 10.0/sql/handler.cc:2941 #8 0x0000000000a0a057 in ha_innobase::write_row (this=0x7fad41852088, record=0x7fad4182a288 "\377") at 10.0/storage/xtradb/handler/ha_innodb.cc:7541 #9 0x0000000000874dce in handler::ha_write_row (this=0x7fad41852088, buf=0x7fad4182a288 "\377") at 10.0/sql/handler.cc:5953 #10 0x000000000065fb87 in write_record (thd=0x7fad4eb0b070, table=0x7fad418a2070, info=0x7fad56ffc9a0) at 10.0/sql/sql_insert.cc:1572 #11 0x000000000065d90d in mysql_insert (thd=0x7fad4eb0b070, table_list=0x7fad41a1d240, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_UPDATE, ignore=false) at 10.0/sql/sql_insert.cc:960 #12 0x000000000067d47e in mysql_execute_command (thd=0x7fad4eb0b070) at 10.0/sql/sql_parse.cc:3432 #13 0x00000000006856cf in mysql_parse (thd=0x7fad4eb0b070, rawbuf=0x7fad41a1d088 "INSERT INTO iodku (id, value) VALUES (NULL, 8), (2, -2), (NULL, 9) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1", length=134, parser_state=0x7fad56ffd610) at 10.0/sql/sql_parse.cc:6407 #14 0x00000000006784b2 in dispatch_command (command=COM_QUERY, thd=0x7fad4eb0b070, packet=0x7fad43bf2071 "INSERT INTO iodku (id, value) VALUES (NULL, 8), (2, -2), (NULL, 9) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1", packet_length=134) at 10.0/sql/sql_parse.cc:1299 #15 0x0000000000677857 in do_command (thd=0x7fad4eb0b070) at 10.0/sql/sql_parse.cc:996 #16 0x0000000000794456 in do_handle_one_connection (thd_arg=0x7fad4eb0b070) at 10.0/sql/sql_connect.cc:1379 #17 0x00000000007941a9 in handle_one_connection (arg=0x7fad4eb0b070) at 10.0/sql/sql_connect.cc:1293 #18 0x0000000000ccbc1e in pfs_spawn_thread (arg=0x7fad4ebd9df0) at 10.0/storage/perfschema/pfs.cc:1860 #19 0x00007fad56c33b50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #20 0x00007fad54eea20d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Not fresh, reproducible on 10.0.10 too.
Not only a InnoDB/XtraDB problem. Similar crash on debug build with MyISAM:
mysqld: /home/jan/mysql/10.0-bugs/sql/handler.cc:2941: int handler::update_auto_increment(): Assertion `next_insert_id >= auto_inc_interval_for_cur_row.minimum()' failed. 141122 11:31:31 [ERROR] 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. To report this bug, see http://kb.askmonty.org/en/reporting-bugs 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. Server version: 10.0.15-MariaDB-debug-log key_buffer_size=1048576 read_buffer_size=131072 max_used_connections=1 max_threads=153 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 62987 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x3da25f0 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 = 0x7ff4807aee30 thread_stack 0x48000 mysys/stacktrace.c:246(my_print_stacktrace)[0xe7c0af] sql/signal_handler.cc:155(handle_fatal_signal)[0x874d08] /lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7ff491926340] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x39)[0x7ff490d78bb9] /lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7ff490d7bfc8] /lib/x86_64-linux-gnu/libc.so.6(+0x2fa76)[0x7ff490d71a76] /lib/x86_64-linux-gnu/libc.so.6(+0x2fb22)[0x7ff490d71b22] sql/handler.cc:2943(handler::update_auto_increment())[0x87bfcc] /run/shm/plugins/ha_innodb.so(+0x143fde)[0x7ff48b3eafde] sql/handler.cc:5953(handler::ha_write_row(unsigned char*))[0x8826ba] sql/sql_insert.cc:1574(write_record(THD*, TABLE*, st_copy_info*))[0x65dd98] sql/sql_insert.cc:962(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool))[0x65ba64] sql/sql_parse.cc:3432(mysql_execute_command(THD*))[0x67babf] sql/sql_parse.cc:6407(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x683e5a] sql/sql_parse.cc:1301(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x67696c] sql/sql_parse.cc:996(do_command(THD*))[0x675cca] sql/sql_connect.cc:1375(do_handle_one_connection(THD*))[0x79da05] sql/sql_connect.cc:1290(handle_one_connection)[0x79d76b] perfschema/pfs.cc:1862(pfs_spawn_thread)[0xb20b75] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7ff49191e182] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7ff490e3cfbd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7ff468013148): INSERT INTO iodku (id, value) VALUES (NULL, 8), (2, -2), (NULL, 9) ON DUPLICATE KEY UPDATE value = VALUES(value) , updates = updates+1 Connection ID (thread ID): 3 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
Test case
Patch to make InnoDB/Xtradb auto increment code exactly as in Oracle 5.6.
Percona 5.5 is affected, but not Percona 5.6. Based on my research function int handler::update_auto_increment() is different on Oracle 5.6 and Percona 5.6. Code they have does not work out-of-the-box thus I will not continue. I made changes only to XtraDB/InnoDB code to make it exactly like Oracle/Percona. Reassigning to serg for reassign someone who knows handler code better.
Note, in case of SBR, it will cause a severe inconsistency between the slaves data.