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

No Duplicate entry error on ALTER TABLE ADD UNIQUE KEY for Aria table

    XMLWordPrintable

Details

    Description

      I originally came across this during RQG Replication testing for MDEV-16329, but I think this is more an issue with the Aria engine rather than Replication. In the test case below, rpl_end.inc will time out because the slave SQL thread has stopped with the 1062 error as it cannot execute the ALTER statement that was successful on the primary.

      With MyISAM and InnoDB, the ALTER TABLE is not allowed on the primary with ER_DUP_ENTRY (1062). If the CREATE TABLE statement defines the key as UNIQUE, then ER_DUP_ENTRY (1062) will occur upon INSERT with the Aria engine.

      --source include/master-slave.inc
      --source include/have_aria.inc
       
      --connection master
      USE test;
      # No replication error with MyISAM and InnoDB
      # Need ROW_FORMAT DYNAMIC for replication error
      CREATE TABLE t9 (`col_int_nokey` SMALLINT UNSIGNED DEFAULT 0 ) ROW_FORMAT DYNAMIC ENGINE=Aria;
      LOCK TABLE t9 WRITE;
      INSERT INTO t9 () VALUES (),(),();
      FLUSH TABLES;
      ALTER TABLE t9 ADD UNIQUE (`col_int_nokey`), ALGORITHM=COPY;
      # or fails with the following in bb-11.0-oalter (1d16113)
      # ALTER ONLINE TABLE t9 ADD UNIQUE (`col_int_nokey`);
      --connection slave
      --connection master
      DROP table t9;
      --source include/rpl_end.inc
      

      Last_SQL_Errno	1062
      Last_SQL_Error	Error 'Duplicate entry '0' for key 'col_int_nokey'' on query. Default database: 'test'. Query: 'ALTER TABLE t9 ADD UNIQUE (`col_int_nokey`), ALGORITHM=COPY'
      ...
      Wrong value for Last_SQL_Errno. Expected '0', got '1062'
      

      MDEV-18745 looked similar based on the queries.

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              angelique.sklavounos Angelique Sklavounos (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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