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

Atomic DDL is not very atomic on partitioned tables

Details

    Description

      Note: To repeat what was also said in the comments, it was already known at the time of atomic DDL development/release (MDEV-25180) that partitioned tables still have problems and aren't made atomic in the scope of the task. However, I was asked to register reproducible test cases anyway, so they could be looked at with a low priority, so that's what this report is for.

      The test case is non-deterministic, run with --repeat=N. It usually fails for me within several attempts (takes longer on non-debug builds), but it can vary on different machines and builds.

      --source include/have_partition.inc
      --source include/have_innodb.inc
      --source include/have_sequence.inc
       
      CREATE TABLE t (pk INT PRIMARY KEY, a INT) ENGINE=InnoDB PARTITION BY HASH(pk);
      INSERT INTO t SELECT seq, seq FROM seq_1_to_1000;
       
      --connect (con1,localhost,root,,test)
      --let $shutdown_timeout= 0
      --send
      ALTER TABLE t ADD KEY (a), ALGORITHM=INPLACE;
       
      --connection default
      --source include/restart_mysqld.inc
       
      CHECK TABLE t;
       
      # Cleanup
      DROP TABLE t;
      

      10.6 8171f9da

      bug.t 'innodb'                           [ fail ]  Found warnings/errors in server log file!
              Test ended at 2023-06-17 22:19:56
      line
      2023-06-17 22:19:56 3 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t#P#p0
      2023-06-17 22:19:56 3 [ERROR] InnoDB: Table test/t#P#p0 contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/
      2023-06-17 22:19:56 3 [ERROR] InnoDB could not find key no 1 with name a from dict cache for table test/t#P#p0
      2023-06-17 22:19:56 3 [ERROR] InnoDB: Table test/t#P#p0 contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/
      2023-06-17 22:19:56 3 [ERROR] InnoDB: Table test/t#P#p0 contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/
      2023-06-17 22:19:56 3 [ERROR] InnoDB could not find key no 1 with name a from dict cache for table test/t#P#p0
      2023-06-17 22:19:56 3 [ERROR] InnoDB: Table test/t#P#p0 contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/
      ^ Found warnings in /mnt8t/bld/10.6-asan/mysql-test/var/log/mysqld.1.err
      ok
      

      When it doesn't fail right away, it can also leave a temporary .par file behind

      MTR's internal check of the test case 'bug.t' failed.
      ...
      +#sql-alter-60b0a-5.par
      

      Attachments

        Issue Links

          Activity

            Like I noted in MDEV-22168, I believe that when the native InnoDB ALTER TABLE is being used (explicit ALGORITHM=INPLACE is not refused), the InnoDB side should be atomic. But, the SQL layer (.par or .frm files) could very well get out of sync with the storage engine. I vaguely remember that partitioning was not exhaustively tested during MDEV-25180, or the scope was reduced to not cover partitioning.

            marko Marko Mäkelä added a comment - Like I noted in MDEV-22168 , I believe that when the native InnoDB ALTER TABLE is being used (explicit ALGORITHM=INPLACE is not refused), the InnoDB side should be atomic. But, the SQL layer ( .par or .frm files) could very well get out of sync with the storage engine. I vaguely remember that partitioning was not exhaustively tested during MDEV-25180 , or the scope was reduced to not cover partitioning.

            Yes, I remember that as well, but I was also told to register encountered partitioning-related issues anyway, and if there is enough information to look at, they will be at least analyzed, with moderate priority. Hence the minor Jira entry.

            elenst Elena Stepanova added a comment - Yes, I remember that as well, but I was also told to register encountered partitioning-related issues anyway, and if there is enough information to look at, they will be at least analyzed, with moderate priority. Hence the minor Jira entry.

            MDEV-29566 is one more special case of this, with my suggestion to implement a storage engine API that would allow a single DDL statement to be executed in a single transaction. Currently, for example CREATE TABLE or DROP TABLE of a partitioned table will be executed as several storage engine transactions for no good reason. Also any ALTER TABLE outside the handler::inplace_alter_table() API would be executed as several storage engine transactions, I think 1 to 3 transactions per affected partition.

            marko Marko Mäkelä added a comment - MDEV-29566 is one more special case of this, with my suggestion to implement a storage engine API that would allow a single DDL statement to be executed in a single transaction. Currently, for example CREATE TABLE or DROP TABLE of a partitioned table will be executed as several storage engine transactions for no good reason. Also any ALTER TABLE outside the handler::inplace_alter_table() API would be executed as several storage engine transactions, I think 1 to 3 transactions per affected partition.

            People

              monty Michael Widenius
              elenst Elena Stepanova
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.