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



    • 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


      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`)
      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.


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

        Issue Links



              thiru Thirunarayanan Balathandayuthapani
              johnkiller Gianni Angelozzi
              7 Vote for this issue
              19 Start watching this issue



                Git Integration

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