[MDEV-30910] Error 1062 reported on insert after deletion of all table tuples Created: 2023-03-23  Updated: 2023-06-18  Resolved: 2023-06-18

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Data Manipulation - Insert, Platform RedHat, Storage Engine - InnoDB
Affects Version/s: 10.4.26
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Andy Liu Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None

Attachments: File my.cnf    

 Description   

The error 1062 (Duplicate entry) was reported when trying to insert tuple immediately after deleting all table tuples (The delete operation has been commited via mysql_query C API before insert):

at 89661187
#230322 7:46:48 server id 1 end_log_pos 89661299 CRC32 0x6f24d9f8 Query thread_id=451878 exec_time=0 error_code=0
SET TIMESTAMP=1679471208/!/;
delete from appcnfg.sipia_port_status

+++ 2023/03/22 07:46:48.549 FAULT HIGH dbload:4023999 E:7128 S:1 (MariadbTran.cpp 1536 v-0001 169.254.64.1 oam-a)

Error: Statement 'insert into appcnfg.sipia_port_status (node_id, port_name, filler1, port_type, administrative_state, service_state) values (320, 'ecsf-stdn', 0, 10, 1, 1);' failed, rc = 1.
Error Number:1062; Error String:Duplicate entry '320-ecsf-stdn' for key 'PRIMARY'.

Is it possible that the delete operation has not been fully committed (even mysql_query ("commit") returned success) while the insert comes? How to ensure a clean start for insert?

 Comments   
Comment by Daniel Black [ 2023-03-23 ]

The return of commit success should be enough to say the transaction is committed.

Can you include SHOW CREATE TABLE appcnfg.sipia_port_status.

Any non-standard server settings (apart from binlog which I see active)?

Was there a version that previously worked?

Comment by Andy Liu [ 2023-03-23 ]

Thanks for the quick analysis.

See below for the requested info:

1) Can you include SHOW CREATE TABLE appcnfg.sipia_port_status.

MariaDB [(none)]> SHOW CREATE TABLE appcnfg.sipia_port_status; 
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                             |
 
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| sipia_port_status | CREATE TABLE `sipia_port_status` (
 
  `node_id` smallint(5) unsigned NOT NULL,
 
  `port_name` varchar(15) NOT NULL,
 
  `filler1` smallint(5) unsigned NOT NULL,
 
  `port_type` int(11) NOT NULL,
 
  `administrative_state` int(11) NOT NULL,
 
  `service_state` int(11) NOT NULL,
 
  PRIMARY KEY (`node_id`,`port_name`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2) Any non-standard server settings (apart from binlog which I see active)?
The server config file is attached my.cnf

3) Was there a version that previously worked?
Not tested on other versions. It does not always happen and no such error reported during the second run.

Comment by Daniel Black [ 2023-03-23 ]

assumptions the delete and insert are in different connections?

this is occuring at a master rather than a replica?

possibility 1 - insert into appcnf .. was in a repeatable read transaction that looked at the duplicate row before the delete SQL was executed?

Comment by Andy Liu [ 2023-03-23 ]

assumptions the delete and insert are in different connections?
Separate mysql_real_connect() are called for delete and insert, respectively. The delete and insert operations are performed in the single application thread. It can be confirmed that the delete operation has mysql_query ("commit") returned success prior to insert.

this is occuring at a master rather than a replica?
This host serves as both a master and replica. Having checked the relay bin log on this host, no DB operations against the table were replicated from other master as of the point of failure.

Comment by Andy Liu [ 2023-04-03 ]

Hi,

Is there any advice for moving forward?

Comment by Sergei Golubchik [ 2023-05-19 ]

I'm sorry, I failed to repeat the problem. Could you provide more info that would help me to repeat it? Or, ideally, provide a repeatable test case. Thanks.

Generated at Thu Feb 08 10:19:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.