[MDEV-9173] REPLACE INTO not updating auto_increment correctly for galera cluster Created: 2015-11-24  Updated: 2018-07-16  Resolved: 2018-07-16

Status: Closed
Project: MariaDB Server
Component/s: Galera, Storage Engine - InnoDB
Affects Version/s: 10.0.20-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mattias Mikkola Assignee: Jan Lindström (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Debian 7.8
MaxScale 1.2.1
wsrep_auto_increment_control=OFF
wsrep_sync_wait=1
auto_increment_increment=1
MaxScale configured with R/W Split


Issue Links:
Relates
relates to MDEV-10379 Failing assertion: xid_seqno > trx_sy... Closed

 Description   

Similar to, but not exactly the same as http://bugs.mysql.com/bug.php?id=65116.

Only shows up on cluster, standalone install work fine.

Create the following table:
CREATE TABLE `auto_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_UNIQUE` (`data`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some data using REPLACE INTO:

REPLACE INTO auto_inc (data) VALUES ('Hello');

SHOW TABLE STATUS LIKE 'auto_inc' shows Auto_increment set to 2.

Issue the same query again:
REPLACE INTO auto_inc (data) VALUES ('Hello');

The row is now updated with id=2, but auto_increment is unchanged. On next insert this will result in duplicate key error.



 Comments   
Comment by Mattias Mikkola [ 2015-11-30 ]

Found another, more recent, issue on the mysql tracker which seems more applicable: http://bugs.mysql.com/bug.php?id=73563

Comment by Nirbhay Choubey (Inactive) [ 2015-12-01 ]

Right, it seems to be duplicate of MDEV-8827.

Comment by Mattias Mikkola [ 2015-12-01 ]

MDEV-8827 mentions

"This will only happen if innodb_autoinc_lock_mode is 1 or 2 and auto_increment_increment > 1."

However, our cluster has auto_increment_increment=1 since wsrep_auto_increment_controll=OFF

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