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

    XMLWordPrintable

    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

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.