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

InnoDB does not raise a warning if ALTER TABLE index operations are optimized away

Details

    Description

      InnoDB can sometimes "optimize away" ALTER TABLE operations.

      For example, let's say that we create a table and add an index to it:

      CREATE TABLE hq_sales.invoices (
               invoice_id BIGINT UNSIGNED NOT NULL,
               branch_id INT NOT NULL,
               customer_id INT,
               invoice_date DATETIME(6),
               invoice_total DECIMAL(13, 2),
               payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
               PRIMARY KEY(invoice_id)
            );
       
      ALTER TABLE hq_sales.invoices ADD INDEX (invoice_date);
      

      We can check the index ID by looking at INNODB_SYS_INDEXES with this query:

      SELECT isi.INDEX_ID, isi.NAME AS index_name, isf.NAME AS index_column
      FROM information_schema.INNODB_SYS_INDEXES isi
      JOIN information_schema.INNODB_SYS_TABLES ist
      ON isi.TABLE_ID = ist.TABLE_ID
      JOIN information_schema.INNODB_SYS_FIELDS isf
      ON isi.INDEX_ID = isf.INDEX_ID
      WHERE ist.NAME = 'hq_sales/invoices'
      ORDER BY isf.INDEX_ID, isf.POS;
      

      For example:

      MariaDB [(none)]> SELECT isi.INDEX_ID, isi.NAME AS index_name, isf.NAME AS index_column
          -> FROM information_schema.INNODB_SYS_INDEXES isi
          -> JOIN information_schema.INNODB_SYS_TABLES ist
          -> ON isi.TABLE_ID = ist.TABLE_ID
          -> JOIN information_schema.INNODB_SYS_FIELDS isf
          -> ON isi.INDEX_ID = isf.INDEX_ID
          -> WHERE ist.NAME = 'hq_sales/invoices'
          -> ORDER BY isf.INDEX_ID, isf.POS;
      +----------+--------------+--------------+
      | INDEX_ID | index_name   | index_column |
      +----------+--------------+--------------+
      |      104 | PRIMARY      | invoice_id   |
      |      105 | invoice_date | invoice_date |
      +----------+--------------+--------------+
      2 rows in set (0.001 sec)
      

      If we drop and re-add the index, then the INDEX_ID will change:

      MariaDB [(none)]> SELECT isi.INDEX_ID, isi.NAME AS index_name, isf.NAME AS index_column
          -> FROM information_schema.INNODB_SYS_INDEXES isi
          -> JOIN information_schema.INNODB_SYS_TABLES ist
          -> ON isi.TABLE_ID = ist.TABLE_ID
          -> JOIN information_schema.INNODB_SYS_FIELDS isf
          -> ON isi.INDEX_ID = isf.INDEX_ID
          -> WHERE ist.NAME = 'hq_sales/invoices'
          -> ORDER BY isf.INDEX_ID, isf.POS;
      +----------+--------------+--------------+
      | INDEX_ID | index_name   | index_column |
      +----------+--------------+--------------+
      |      104 | PRIMARY      | invoice_id   |
      |      105 | invoice_date | invoice_date |
      +----------+--------------+--------------+
      2 rows in set (0.001 sec)
       
      MariaDB [(none)]> ALTER TABLE hq_sales.invoices DROP INDEX invoice_date;
      Query OK, 0 rows affected (0.015 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [(none)]> ALTER TABLE hq_sales.invoices ADD INDEX (invoice_date);
      Query OK, 0 rows affected (0.019 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [(none)]> SELECT isi.INDEX_ID, isi.NAME AS index_name, isf.NAME AS index_column
          -> FROM information_schema.INNODB_SYS_INDEXES isi
          -> JOIN information_schema.INNODB_SYS_TABLES ist
          -> ON isi.TABLE_ID = ist.TABLE_ID
          -> JOIN information_schema.INNODB_SYS_FIELDS isf
          -> ON isi.INDEX_ID = isf.INDEX_ID
          -> WHERE ist.NAME = 'hq_sales/invoices'
          -> ORDER BY isf.INDEX_ID, isf.POS;
      +----------+--------------+--------------+
      | INDEX_ID | index_name   | index_column |
      +----------+--------------+--------------+
      |      104 | PRIMARY      | invoice_id   |
      |      106 | invoice_date | invoice_date |
      +----------+--------------+--------------+
      2 rows in set (0.001 sec)
      

      However, if we perform the DROP INDEX and ADD INDEX operation in the same ALTER TABLE command, then the operation is "optimized away" with no warning:

      MariaDB [(none)]> SELECT isi.INDEX_ID, isi.NAME AS index_name, isf.NAME AS index_column
          -> FROM information_schema.INNODB_SYS_INDEXES isi
          -> JOIN information_schema.INNODB_SYS_TABLES ist
          -> ON isi.TABLE_ID = ist.TABLE_ID
          -> JOIN information_schema.INNODB_SYS_FIELDS isf
          -> ON isi.INDEX_ID = isf.INDEX_ID
          -> WHERE ist.NAME = 'hq_sales/invoices'
          -> ORDER BY isf.INDEX_ID, isf.POS;
      +----------+--------------+--------------+
      | INDEX_ID | index_name   | index_column |
      +----------+--------------+--------------+
      |      104 | PRIMARY      | invoice_id   |
      |      106 | invoice_date | invoice_date |
      +----------+--------------+--------------+
      2 rows in set (0.001 sec)
       
      MariaDB [(none)]> ALTER TABLE hq_sales.invoices DROP INDEX invoice_date,
          ->    ADD INDEX (invoice_date);
      Query OK, 0 rows affected (0.000 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [(none)]> SHOW WARNINGS;
      Empty set (0.000 sec)
       
      MariaDB [(none)]> SELECT isi.INDEX_ID, isi.NAME AS index_name, isf.NAME AS index_column
          -> FROM information_schema.INNODB_SYS_INDEXES isi
          -> JOIN information_schema.INNODB_SYS_TABLES ist
          -> ON isi.TABLE_ID = ist.TABLE_ID
          -> JOIN information_schema.INNODB_SYS_FIELDS isf
          -> ON isi.INDEX_ID = isf.INDEX_ID
          -> WHERE ist.NAME = 'hq_sales/invoices'
          -> ORDER BY isf.INDEX_ID, isf.POS;
      +----------+--------------+--------------+
      | INDEX_ID | index_name   | index_column |
      +----------+--------------+--------------+
      |      104 | PRIMARY      | invoice_id   |
      |      106 | invoice_date | invoice_date |
      +----------+--------------+--------------+
      2 rows in set (0.002 sec)
      

      This happens with innodb_strict_mode enabled:

      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_strict_mode';
      +--------------------+-------+
      | Variable_name      | Value |
      +--------------------+-------+
      | innodb_strict_mode | ON    |
      +--------------------+-------+
      1 row in set (0.009 sec)
      

      InnoDB should probably raise a warning when it "optimizes away" an ALTER TABLE operation--especially if innodb_strict_mode is enabled.

      Attachments

        Issue Links

          Activity

            I think that this is working as designed in MySQL 5.6. Are MariaDB 10.0 and 10.1 really not affected by this?

            In MDEV-13301, we are extending this design by optimizing pairs of DROP INDEX, ADD INDEX into renaming operations. Explicit syntax for RENAME INDEX and RENAME COLUMN were introduced later in MDEV-7318 and MDEV-16290.

            The optimizing away of DROP INDEX, ADD INDEX pairs is somewhat inconsistent with the statement

            ALTER TABLE t ENGINE=InnoDB;
            

            which before the introduction of the FORCE clause was the canonical way to rebuild a table (even if no change of storage engine is taking place). Maybe that statement should start ‘optimizing away’ as well?

            I fear that when DROP INDEX, ADD INDEX pairs are actually changing the ASC/DESC attributes of some colums, we might be wrongly optimizing away those changes. For example, if one wanted to change an index (name ASC, salary DESC) into (name ASC, salary ASC), the change might be optimized away. This does not really matter until MDEV-13756 is implemented, because currently DESC is being treated as ASC.

            One real deficiency is that if an index is corrupted, the DROP/ADD pair should not be optimized away, but currently that transformation is not consulting the storage engine. Similarly, if we change the format of secondary indexes in MDEV-17598, then the DROP/ADD pairs can only be optimized away if the format is not going to change.

            marko Marko Mäkelä added a comment - I think that this is working as designed in MySQL 5.6. Are MariaDB 10.0 and 10.1 really not affected by this? In MDEV-13301 , we are extending this design by optimizing pairs of DROP INDEX , ADD INDEX into renaming operations. Explicit syntax for RENAME INDEX and RENAME COLUMN were introduced later in MDEV-7318 and MDEV-16290 . The optimizing away of DROP INDEX , ADD INDEX pairs is somewhat inconsistent with the statement ALTER TABLE t ENGINE=InnoDB; which before the introduction of the FORCE clause was the canonical way to rebuild a table (even if no change of storage engine is taking place). Maybe that statement should start ‘optimizing away’ as well? I fear that when DROP INDEX , ADD INDEX pairs are actually changing the ASC / DESC attributes of some colums, we might be wrongly optimizing away those changes. For example, if one wanted to change an index (name ASC, salary DESC) into (name ASC, salary ASC) , the change might be optimized away. This does not really matter until MDEV-13756 is implemented, because currently DESC is being treated as ASC . One real deficiency is that if an index is corrupted, the DROP / ADD pair should not be optimized away, but currently that transformation is not consulting the storage engine. Similarly, if we change the format of secondary indexes in MDEV-17598 , then the DROP / ADD pairs can only be optimized away if the format is not going to change.

            I would think it's fine. Why would you care how the ALTER TABLE was executed internally? Optimized away, dropped and recreated instantly while preserving INDEX_ID, or really copied the whole table to a new one without an index, and then copied again, to a table with an index. The server dropped and added an index, how exactly it was done is implementation detail.

            serg Sergei Golubchik added a comment - I would think it's fine. Why would you care how the ALTER TABLE was executed internally? Optimized away, dropped and recreated instantly while preserving INDEX_ID, or really copied the whole table to a new one without an index, and then copied again, to a table with an index. The server dropped and added an index, how exactly it was done is implementation detail.

            Why would you care how the ALTER TABLE was executed internally?

            If you are trying to rebuild a single index by dropping it and re-adding it, then it can be important to know whether the operation is actually performed or whether it is optimized away. A user might want to do this if the index is corrupt due to a crash, a bug, or some kind of user error.

            It definitely makes sense to optimize away the operation in this case. However, in my opinion, I think the server should warn the user when a requested operation is not actually performed.

            GeoffMontee Geoff Montee (Inactive) added a comment - Why would you care how the ALTER TABLE was executed internally? If you are trying to rebuild a single index by dropping it and re-adding it, then it can be important to know whether the operation is actually performed or whether it is optimized away. A user might want to do this if the index is corrupt due to a crash, a bug, or some kind of user error. It definitely makes sense to optimize away the operation in this case. However, in my opinion, I think the server should warn the user when a requested operation is not actually performed.

            I'd say, you're using the server incorrectly, relying on assumptions that happen to work. Like doing ALTER TABLE to collect the statistics or relying on SELECT without ORDER BY to have some specific ordering of rows. All that might work, but it's not guaranteed to. One should use REPAIR to fix a corrupted table, ANALYZE and ORDER BY respectively — these statements/clauses are designed to do that. Others might, as it happens, to achieve the same as a side effect but it's not guaranteed.

            serg Sergei Golubchik added a comment - I'd say, you're using the server incorrectly, relying on assumptions that happen to work. Like doing ALTER TABLE to collect the statistics or relying on SELECT without ORDER BY to have some specific ordering of rows. All that might work, but it's not guaranteed to. One should use REPAIR to fix a corrupted table, ANALYZE and ORDER BY respectively — these statements/clauses are designed to do that. Others might, as it happens, to achieve the same as a side effect but it's not guaranteed.

            One should use REPAIR to fix a corrupted table

            As far as I know, REPAIR TABLE doesn't do anything for InnoDB tables. However, OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB can be used to rebuild an entire InnoDB table if one of the table's indexes need to be rebuilt due to corruption or other issues.

            Anyway, my point is that, if the user asks the server to do operations X and Y, and the server decides that it doesn't need to do either of those operations, then in my opinion, it would make sense to warn the user. It sounds like you disagree with me. That's fine. I'm not too worried about this.

            GeoffMontee Geoff Montee (Inactive) added a comment - One should use REPAIR to fix a corrupted table As far as I know, REPAIR TABLE doesn't do anything for InnoDB tables. However, OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB can be used to rebuild an entire InnoDB table if one of the table's indexes need to be rebuilt due to corruption or other issues. Anyway, my point is that, if the user asks the server to do operations X and Y, and the server decides that it doesn't need to do either of those operations, then in my opinion, it would make sense to warn the user. It sounds like you disagree with me. That's fine. I'm not too worried about this.

            I conceptually prefer MariaDB SQL dialect to be as declarative as possible. You tell database server what you want to achieve, and the database server gets you there. Somehow. If you want an a corrupted index to be repaired, you ask to repair it. And I'd claim that REPAIR TABLE not repairing a corrupted index is a bug.

            I think it's fine if MariaDB would issue a note (warning seems to be too strong) if it optimizes away drop/add index (it's also fine not to, depending on how difficult it is to achieve). But I wouldn't like users to expect drop/add index to actually do something. Or, generally, rely on a particular execution path instead of relying on a particular end state.

            serg Sergei Golubchik added a comment - I conceptually prefer MariaDB SQL dialect to be as declarative as possible. You tell database server what you want to achieve, and the database server gets you there. Somehow. If you want an a corrupted index to be repaired, you ask to repair it. And I'd claim that REPAIR TABLE not repairing a corrupted index is a bug. I think it's fine if MariaDB would issue a note (warning seems to be too strong) if it optimizes away drop/add index (it's also fine not to, depending on how difficult it is to achieve). But I wouldn't like users to expect drop/add index to actually do something. Or, generally, rely on a particular execution path instead of relying on a particular end state.
            rjasdfiii Rick James added a comment -

            "Declarative" is a good goal. But fixing system problems is a necessary goal in this imperfect world. Already we have "FORCE INDEX" and "ALTER ... ALGORITHM=..." to override the Optimizer's better judgment. Let's have "REPAIR object" and/or "ALTER ... FORCE" both bypass optimizations.

            rjasdfiii Rick James added a comment - "Declarative" is a good goal. But fixing system problems is a necessary goal in this imperfect world. Already we have "FORCE INDEX" and "ALTER ... ALGORITHM=..." to override the Optimizer's better judgment. Let's have "REPAIR object" and/or "ALTER ... FORCE" both bypass optimizations.

            Note: I disagree with the bug title, because it is not InnoDB that is optimizing away the operations; InnoDB gets what it gets from the SQL layer. One more case where we would want to avoid any "optimization" is MDEV-35169 when the table needs to be rebuilt to have the key columns use the expected ASC or DESC ordering. If we want to fix such a problem in the PRIMARY KEY index, I’m afraid that currently the only way to do that may be to change an ASC/DESC attribute twice, to skip the optimization inside InnoDB that would avoid sorting the primary key index when it does not appear to change.

            marko Marko Mäkelä added a comment - Note: I disagree with the bug title, because it is not InnoDB that is optimizing away the operations; InnoDB gets what it gets from the SQL layer. One more case where we would want to avoid any "optimization" is MDEV-35169 when the table needs to be rebuilt to have the key columns use the expected ASC or DESC ordering. If we want to fix such a problem in the PRIMARY KEY index, I’m afraid that currently the only way to do that may be to change an ASC / DESC attribute twice, to skip the optimization inside InnoDB that would avoid sorting the primary key index when it does not appear to change.

            People

              sanja Oleksandr Byelkin
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.