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

[MariaDB 10.3 -> MariaDB 10.6][InnoDB] Duplicate entry '...' for key 'unique_index'

    XMLWordPrintable

Details

    Description

      Recently we upgraded mariadb from 10.3 to 10.6 and we are facing a weird problem.
      Although there is a unique key on 3 columns (`table_hash`,`table_id`,`meta_key`) there are multiple records with same data.
      It didn't happen when we were on mariadb 10.3.

      *Table structure:*
      ```

      CREATE TABLE `ordini_meta` (
        `meta_id` int(11) NOT NULL,
        `table_hash` bigint(64) NOT NULL DEFAULT 0,
        `table_id` int(11) NOT NULL DEFAULT 0,
        `meta_key` varchar(50) DEFAULT NULL,
        `meta_value` longtext DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
       
      ALTER TABLE `ordini_meta`
        ADD PRIMARY KEY (`meta_id`),
        ADD UNIQUE KEY `unique_index` (`table_hash`,`table_id`,`meta_key`),
        ADD KEY `table_hash` (`table_hash`),
        ADD KEY `table_id` (`table_id`),
        ADD KEY `meta_key` (`meta_key`);
       
      ALTER TABLE `ordini_meta`
        MODIFY `meta_id` int(11) NOT NULL AUTO_INCREMENT;
      

      ```

      *Example of duplicates*:
      Duplicates

      Rows in the table are managed with PHP and the query used is:
      ```

      INSERT INTO ordini_meta (table_hash,table_id,meta_key,meta_value) VALUES (%d,%d,%s,%s) ON DUPLICATE KEY UPDATE meta_id=LAST_INSERT_ID(meta_id), meta_value=%s"
      
      

      ```
      *SHOW VARIABLES 10.6(now):*
      pastebin

      *SHOW VARIABLES 10.3(before upgrade):*
      pastebin

      DB structure is the same that was in 10.3, unique key was already there before upgrade. In 10.3 there weren't any duplicate rows. We restored the database from a backup(mysqldump) and everyday we need to delete duplicate rows. We also tried to re-create index many times. I suppose I just need to create a bug report

      We really don't understand why it happens.
      Any help is really appreciated.

      Attachments

        Activity

          People

            Unassigned Unassigned
            Gioggiolo Luigi Troiano
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.