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
    • 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

        Issue Links

          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.