Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30910

Error 1062 reported on insert after deletion of all table tuples

Details

    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?

      Attachments

        Activity

          danblack Daniel Black added a comment -

          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?

          danblack Daniel Black added a comment - 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?
          liberiul Andy Liu added a comment - - edited

          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.

          liberiul Andy Liu added a comment - - edited 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.
          danblack Daniel Black added a comment - - edited

          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?

          danblack Daniel Black added a comment - - edited 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?
          liberiul Andy Liu added a comment -

          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.

          liberiul Andy Liu added a comment - 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.
          liberiul Andy Liu added a comment -

          Hi,

          Is there any advice for moving forward?

          liberiul Andy Liu added a comment - Hi, Is there any advice for moving forward?

          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.

          serg Sergei Golubchik added a comment - 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.

          People

            Unassigned Unassigned
            liberiul Andy Liu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.