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

      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

              Assignee:
              mleich Matthias Leich
              Reporter:
              johnkiller Gianni Angelozzi
              Votes:
              5 Vote for this issue
              Watchers:
              14 Start watching this issue

                Dates

                Created:
                Updated: