MySQL Bugs: #42994: REPLACE INTO results in a DUPLICATE KEY error on the AUTO

Bug #42994	REPLACE INTO results in a DUPLICATE KEY error on the AUTO_INCREMENT primary key
Submitted:	18 Feb 2009 21:47	Modified:	19 Feb 2009 23:04
Reporter:	Chris Calender	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.30, 5.1.31	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	auto_increment, duplicate key, innodb, REPLACE

Description:
A multi-row REPLACE INTO results in a DUPLICATE KEY error on the AUTO_INCREMENT primary key in InnoDB tables.

Ultimately, I get the following error:

(1062) - Duplicate entry '1' for key 'PRIMARY': REPLACE INTO t1 (`c2`,`c4`,`c3` [...]

This obviously should not occur as the AUTO_INCREMENT should fill the primary key with a proper ID that is not a duplicate.

This only occurs on InnoDB tables.  If changed to MyISAM, it works properly.

This could be related to bug #42714:

http://bugs.mysql.com/bug.php?id=42714

However, it is unclear yet, and/or this bug report here might show some additional cases where it could arise.

Note I can reproduce this easier in 5.1.30 than I can in 5.1.31.  However, if I simply increase auto_increment_offset to 5, then it is easily reproducible in 5.1.31.  However, then this leads me to wonder if it is two separate bugs.

Also, as for reproducing it, it was easier to reproduce with a binlog_format of 'mixed' (as opposed to 'row'), though I'm not sure if that makes a difference.

Additionally, it was easier to reproduce if I have the UNIQUE KEY defined (as below).

How to repeat:
First of all, I was only able to reproduce this with a multi-threaded test case.  That's not to say that's the only way to trigger this, but it is the only way I can reproduce it reliably.

DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
  `c1` bigint(20) NOT NULL AUTO_INCREMENT,
  `c2` bigint(20) NOT NULL,
  `c3` varchar(20) NOT NULL,
  `c4` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c2_c3` (`c2`,`c3`)
) ENGINE=InnoDB;

Now, this statement must be run simultaneously from multiple clients.  I ran it from 5 threads.

REPLACE INTO t1 (`c2`,`c4`,`c3`) VALUES (912122,0,'aaa'),(988055,0,'aaa'),(1377918,0,'aaa'),(1410447,0,'aaa'),(1412188,0,'aaa'),(1416087,0,'aaa'),(1509212,0,'aaa'),(1515064,0,'aaa'),(1667880,0,'aaa'),(1694432,0,'aaa'),(1786013,0,'aaa'),(1807244,0,'aaa'),(1833993,0,'aaa'),(1838362,0,'aaa'),(1867017,0,'aaa'),(1884884,0,'aaa'),(1887234,0,'aaa'),(1889736,0,'aaa'),(1939686,0,'aaa'),(1940259,0,'aaa'),(1962820,0,'aaa'),(1973410,0,'aaa'),(1983157,0,'aaa'),(2005527,0,'aaa'),(2011339,0,'aaa'),(2060041,0,'aaa'),(2063487,0,'aaa'),(2078493,0,'aaa'),(2104851,0,'aaa'),(2134673,0,'aaa'),(2137254,0,'aaa'),(2190380,0,'aaa'),(2196670,0,'aaa'),(2220382,0,'aaa'),(2244659,0,'aaa'),(2260624,0,'aaa'),(2267322,0,'aaa'),(2277001,0,'aaa'),(2294642,0,'aaa'),(2343996,0,'aaa'),(2361494,0,'aaa'),(2367100,0,'aaa'),(2368295,0,'aaa'),(2376103,0,'aaa'),(2397809,0,'aaa'),(2398959,0,'aaa'),(2402161,0,'aaa'),(2409510,0,'aaa'),(2410532,0,'aaa'),(2418288,0,'aaa'),(2452089,0,'aaa'),(2457228,0,'aaa'),(2466274,0,'aaa'),(2505551,0,'aaa'),(2506189,0,'aaa'),(2538470,0,'aaa'),(2559498,0,'aaa'),(2571417,0,'aaa'),(2580700,0,'aaa'),(2586908,0,'aaa'),(2593540,0,'aaa'),(2599824,0,'aaa'),(2610591,0,'aaa'),(2626939,0,'aaa'),(2627062,0,'aaa'),(2656009,0,'aaa'),(2657488,0,'aaa'),(2666958,0,'aaa'),(2704169,0,'aaa'),(2710179,0,'aaa'),(2713116,0,'aaa'),(2720179,0,'aaa'),(2721967,0,'aaa'),(2730712,0,'aaa'),(2732692,0,'aaa'),(2733527,0,'aaa'),(2734566,0,'aaa'),(2741610,0,'aaa'),(2750669,0,'aaa'),(2758539,0,'aaa'),(2763788,0,'aaa'),(2767640,0,'aaa'),(2768530,0,'aaa'),(2774850,0,'aaa'),(2782279,0,'aaa'),(2782566,0,'aaa'),(2782980,0,'aaa'),(2789200,0,'aaa'),(2789267,0,'aaa'),(2790686,0,'aaa'),(2791851,0,'aaa'),(2797476,0,'aaa'),(2797579,0,'aaa'),(2803707,0,'aaa'),(2805874,0,'aaa'),(2806519,0,'aaa'),(2815371,0,'aaa'),(2817571,0,'aaa'),(2821041,0,'aaa'),(2826396,0,'aaa');

You will eventually see errors like the following:

[ERROR] query failed (1062) - Duplicate entry '1' for key 'PRIMARY': REPLACE INTO t1 (`c2`,`c4`,`c3` [...]

Suggested fix:
N/A

Sunny, could you have a look? In the meantime could we have the values of autoinc related variables in your testcase, mostly innodb_autoinc_lock_mode,
but just in case you could do something like  mysqladmin variables | grep auto

mysql> select version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.30-enterprise-gpl-advanced-log |
+------------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
| autocommit                  | ON    |
| automatic_sp_privileges     | ON    |
| innodb_autoextend_increment | 8     |
| innodb_autoinc_lock_mode    | 1     |
| sql_auto_is_null            | ON    |
+-----------------------------+-------+
7 rows in set (0.00 sec)

mysql> select version();
+--------------------------------+
| version()                      |
+--------------------------------+
| 5.1.31-enterprise-gpl-advanced |
+--------------------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 2     |
| autocommit                  | ON    |
| automatic_sp_privileges     | ON    |
| innodb_autoextend_increment | 8     |
| innodb_autoinc_lock_mode    | 1     |
| sql_auto_is_null            | ON    |
+-----------------------------+-------+
7 rows in set (0.00 sec)

Note the fist os from 5.1.30 and the second from 5.1.31.  In 5.1.31, I had to increase auto_increment_offset to 2 to trigger the bug.

Note:

I just tested both 5.1.30 and 5.1.31 with the other values for 'innodb_autoinc_lock_mode'.

The tests do not exhibit the bug with 'innodb_autoinc_lock_mode' set to 0 or 2.

So thus far, the bug seems to only arise if innodb_autoinc_lock_mode=1 (which is default, btw).

"The allowable values are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively."

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_m...

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Chris, thank you for the information. This sounds in fact something
like Sunny fixed recently, maybe the fix is already in the patch pipeline,
but I could be wrong. I'm sure Sunny will sort it out in no time.

bug#42714 looks similar, but that one isn't fixed yet.
42714 is said not to occur in 5.1.30 but probably just because the testcase
is much simpler

Hi,

This is a duplicate of Bug#42714. I wrote a small C program that spawned 15 threads and each thread continuously did a "REPLACE ..." . No errors or warnings were reported in the 15 minute test. This test was run against the proposed patch for Bug#42714.

With the following setting in every worker thread:
   SET @@SESSION.AUTO_INCREMENT_INCREMENT=1
   SET @@SESSION.AUTO_INCREMENT_OFFSET=5

And the variables from the MySQL server were:

mysql> show global variables like 'innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_hash_index      | ON                     | 
| innodb_additional_mem_pool_size | 4194304                | 
| innodb_autoextend_increment     | 8                      | 
| innodb_autoinc_lock_mode        | 1                      | 
| innodb_buffer_pool_size         | 536870912              | 
| innodb_checksums                | ON                     | 
| innodb_commit_concurrency       | 0                      | 
| innodb_concurrency_tickets      | 500                    | 
| innodb_data_file_path           | ibdata1:10M:autoextend | 
| innodb_data_home_dir            | .                      | 
| innodb_doublewrite              | ON                     | 
| innodb_fast_shutdown            | 1                      | 
| innodb_file_io_threads          | 4                      | 
| innodb_file_per_table           | ON                     | 
| innodb_flush_log_at_trx_commit  | 1                      | 
| innodb_flush_method             | O_DIRECT               | 
| innodb_force_recovery           | 0                      | 
| innodb_lock_wait_timeout        | 50                     | 
| innodb_locks_unsafe_for_binlog  | OFF                    | 
| innodb_log_buffer_size          | 393216                 | 
| innodb_log_file_size            | 16777216               | 
| innodb_log_files_in_group       | 2                      | 
| innodb_log_group_home_dir       | .                      | 
| innodb_max_dirty_pages_pct      | 90                     | 
| innodb_max_purge_lag            | 0                      | 
| innodb_mirrored_log_groups      | 1                      | 
| innodb_open_files               | 300                    | 
| innodb_rollback_on_timeout      | OFF                    | 
| innodb_stats_on_metadata        | ON                     | 
| innodb_support_xa               | ON                     | 
| innodb_sync_spin_loops          | 20                     | 
| innodb_table_locks              | ON                     | 
| innodb_thread_concurrency       | 8                      | 
| innodb_thread_sleep_delay       | 10000                  | 
+---------------------------------+------------------------+

Thanks for the excellent test case.

Regards,
-sunny