Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.14, 5.5(EOL), 10.0(EOL)
-
None
-
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.
Attachments
Activity
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
|
|
|
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.