[MDEV-7150] Wrong auto increment values on INSERT .. ON DUPLICATE KEY UPDATE when the inserted columns include NULL in an auto-increment column Created: 2014-11-20  Updated: 2014-12-18  Resolved: 2014-12-18

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.0.14, 5.5, 10.0
Fix Version/s: 5.5.41, 10.0.16

Type: Bug Priority: Major
Reporter: Stoykov (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None
Environment:

Red Hat Enterprise Linux

Attachments: File innodb-mdev7150.test     File innodb.patch     Text File mariadb-10.0.14-with-updates.txt     Text File mysql-5.6.15-with-updates.txt     Text File mysql-5.7.5-with-updates.txt    

 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.

 Comments   
Comment by Stoykov (Inactive) [ 2014-11-20 ]

Note, in case of SBR, it will cause a severe inconsistency between the slaves data.

Comment by Elena Stepanova [ 2014-11-20 ]

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.

Comment by Jan Lindström (Inactive) [ 2014-11-22 ]

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
 
 

Comment by Jan Lindström (Inactive) [ 2014-11-22 ]

Test case

Comment by Jan Lindström (Inactive) [ 2014-11-22 ]

Patch to make InnoDB/Xtradb auto increment code exactly as in Oracle 5.6.

Comment by Jan Lindström (Inactive) [ 2014-11-22 ]

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.

Generated at Thu Feb 08 07:17:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.