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

"Can't write; duplicate key in table" when updating some rows in a transaction

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Cannot Reproduce
    • 10.2.12, 10.3.4, 10.1.34, 10.2.14, 10.3.6, 10.3.7, 10.1.42
    • N/A
    • Full-text Search, Storage Engine - InnoDB
    • None
    • Ubuntu Xenial

    Description

      CREATE TABLE `user` (
      		 
       `id` int(11) unsigned NOT NULL,
      		 
       `revision_date` int(14) NOT NULL,
      		 
       `revision_author` int(11) unsigned NOT NULL,
      		 
       `creation_date` int(14) NOT NULL,
      		 
       `creation_author` int(11) unsigned NOT NULL,
      		 
       `deleted` tinyint(1) NOT NULL,
      		 
       `user` varchar(250) NOT NULL,
      		 
       `password` varchar(255) NOT NULL,
      		 
       `name` varchar(250) NOT NULL,
      		 
       `surname` varchar(250) NOT NULL,
      		 
       `lastlogin` int(14) NOT NULL,
      		 
       `lastip` varchar(32) NOT NULL,
      		 
       `email` varchar(60) NOT NULL,
      		 
       PRIMARY KEY (`id`),
      		 
       KEY `id` (`id`,`deleted`),
      		 
       KEY `deleted` (`deleted`),
      		 
       FULLTEXT KEY `user` (`user`,`name`,`surname`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 TRANSACTIONAL=0
      		 
       
      		 
      UPDATE user SET id = 275, revision_date = 1517476994, revision_author = 1, creation_date = 1475149574, creation_author = 1, user = "8502", name = "SOFIA", surname = "ORTONA - TEDESCO", email = "" WHERE id = 275
      		 
       
      		 
      Can't write; duplicate key in table 'user'

      I have an issue on InnoDB with both MariaDB versions 10.3.4 and 10.2.12
      This problem is very difficult for me to reproduce since it doesn't always happen. As you can see, that error can't be true because the ID 275 is already set for that row, so I don't know why it is failing. Also, this is a pretty busy server, and this error pops up randomly on INSERTs and UPDATEs of various tables, but only 2-3 times a day (there are about 10.000 writes every day)

      I don't even know how to debug it properly since that particular error message is associated with the creation of foreign keys. The table is referenced on other tables, but the error should occur on creation of them, not by writing rows.

      The query is run inside a transaction. Please let me know if you need anything else to help diagnose the issue.

      Attachments

        1. custom.cnf
          2 kB
          Gianni Angelozzi
        2. MDEV-15237.patch
          1 kB
          Matthias Leich

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12676 InnoDB FTS duplicate key error

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14141 mysqld got exception 0xc0000005

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14970 MariaDB crashed with signal 11 and Aria table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15304 Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18868 FTS Breaking Replication

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24608 Failing assertion: error == DB_SUCCESS || error == DB_DUPLICATE_KEY OR Assertion `error == DB_DUPLICATE_KEY || error == DB_LOCK_WAIT_TIMEOUT' failed IN trx_t::commit_low

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25581 MS FTS Bug 103523 also applicable to MariaDB Server (Was: Test MS Bug 103523 against MD010521-bb-10.5-thiru)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29335 Crash after Duplicate Key error

          • Major - Major loss of function.
          • Closed

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              johnkiller Gianni Angelozzi
              Votes:
              7 Vote for this issue
              Watchers:
              19 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.