[MDEV-22335] Inserting in a partitioned table sometimes causes error 1062 Created: 2020-04-22  Updated: 2020-05-12

Status: Open
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.3.22
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Anton B Assignee: Matthias Leich
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux 4.19.0-8-cloud-amd64 #1 SMP Debian 4.19.98-1 (2020-01-26) x86_64 GNU/Linux


 Description   

Table

CREATE TABLE `background` (
 
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 
 `name` varchar(255) NOT NULL DEFAULT '',
 
 `data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '[]',
 
 `priority` tinyint(4) NOT NULL DEFAULT 0,
 
 `time` int(11) NOT NULL DEFAULT 0,
 
 `attempt` int(11) NOT NULL DEFAULT 0,
 
 `status` tinyint(4) NOT NULL DEFAULT 0,
 
 `add` int(11) NOT NULL DEFAULT 0,
 
 `update` int(11) NOT NULL DEFAULT 0,
 
 PRIMARY KEY (`id`),
 
 KEY `status` (`status`)
 
) ENGINE=InnoDB AUTO_INCREMENT=2004210000000344340 DEFAULT CHARSET=utf8mb4
 
PARTITION BY RANGE (`id`)
 
(PARTITION `d200419` VALUES LESS THAN (2004200000000000000) ENGINE = InnoDB,
 
PARTITION `d200420` VALUES LESS THAN (2004210000000000000) ENGINE = InnoDB,
 
PARTITION `d200421` VALUES LESS THAN (2004220000000000000) ENGINE = InnoDB)

Query

INSERT INTO `background` (`name`, `data`, `priority`, `time`, `status`, `add`) VALUES ('battle.move', '{\"battle_id\":2004220000000001939,\"frame\":2}', 1, 1587516303, 0, 1587516303)

Error

1062 Duplicate entry '2004210000000245501' for key 'PRIMARY'

Additional Information

  • Default configuration
  • No replication
  • Used transactions
  • The error occurs under high load, a lot of application thread work with the table

 Comments   
Comment by Matthias Leich [ 2020-05-05 ]

Thank you for the bug report.

Would you please send the server error log from the point of time where that 1062 Duplicate entry ... happens?

Are there more features like for example FOREIGN KEYS or TRIGGERS involved?
If yes than please add their definition and also the definition of other tables used by them.

Have you tried a CHECK TABLE `background` EXTENDED ?
I am aware that this statement might require a big execution time
depending on the table size and the SQL load on that.

Many thanks in advance.

Comment by Anton B [ 2020-05-05 ]

1. No FOREIGN KEYS or TRIGGERS

2. Run "CHECK TABLE `background` EXTENDED", result - OK

3. Mysql error.log empty, server (dmesg -T) no error

Transaction example.
Error 1062 on line 6.

1. START TRANSACTION
 
2. SELECT `data`, `status`, `id` FROM `background` WHERE `id` IN (2005050000000327158)
 
3. SELECT * FROM `battle` WHERE `id` IN (2005050000000009824)
 
4. SELECT `units`, `items`, `battle_id` FROM `battle_data` WHERE `battle_id` IN (2005050000000009824) AND `frame` IN (2)
 
5. INSERT INTO `battle` (`id`, `update`, `winner_id`, `active_id`, `active_expire`, `frame`, `round`) VALUES (2005050000000009824, 1588682112, 0, 415, 1588682112, 3, 1) ON DUPLICATE KEY UPDATE `update` = VALUES(`update`), `winner_id` = VALUES(`winner_id`), `active_id` = VALUES(`active_id`), `active_expire` = VALUES(`active_expire`), `frame` = VALUES(`frame`), `round` = VALUES(`round`)
 
6. INSERT INTO `background` (`name`, `data`, `priority`, `time`, `status`, `add`) VALUES ('battle.move', '{\"battle_id\":2005050000000009824,\"frame\":3}', 1, 1588682112, 0, 1588682112)
 
7. INSERT INTO `background` (`id`, `update`, `status`) VALUES (2005050000000327158, 1588682112, 3) ON DUPLICATE KEY UPDATE `update` = VALUES(`update`), `status` = VALUES(`status`)
 
8. ROLLBACK

Statistics 04 May 2020
Total insert query to `background` - 381327
Error 1062 - 427

Comment by Matthias Leich [ 2020-05-07 ]

Many thanks for your fast answer.

I simulated the transaction from above by some RQG test.
But had unfortunately up till today now no success in replaying the problem.

Comment by Anton B [ 2020-05-07 ]

The problem may be related to the environment?
My server on DigitalOcean.

Can the problem be related to competitive requests? In my case, 50 threads are trying to complete a transaction at the same time. And in the future there will be more flows.

Comment by Matthias Leich [ 2020-05-11 ] 

Would you please check to which value your system variable innodb-lock-schedule-algorithm is set to?
 
        SHOW VARIABLES  at server runtime
 
 
 
In case you get than 'vats' than please try what happens in case you start that server with
 
    innodb_lock_schedule_algorithm=fcfs
 
Will the "Error 1062" disappear?
 
 
 
Many thanks in advance.
 
 
 
Some background of the proposal:
 
https://jira.mariadb.org/browse/MDEV-11039 introduced an additional
 
algorithm  for the record lock manager of InnoDB and XtraDB
 
     Variance-Aware-Transaction-Scheduling, VATS
 
and
 
     the old first algorithm got the name FirstComeFirstServed , FCFS.
 
The documentation says
 
     "MariaDB 10.2, VATS is default, but from MariaDB 10.2.12, the value will
 
    "be changed to FCFS and a warning produced when using Galera."
 
So SHOW VARIABLES  during runtime is most probably the best solution.
 
As long as the number of established connections is < 32 the algorithm used
 
is FCFS even if VATS was assigned.
 
In case I recall correct than VATS made
 
- on releases compiled with debug
 
- rare conditions
 
sometimes trouble even if Galera was not used.

Comment by Anton B [ 2020-05-12 ]

Run "SHOW VARIABLES LIKE 'innodb_lock_schedule_algorithm'", result - "innodb_lock_schedule_algorithm fcfs"

Generated at Thu Feb 08 09:13:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.