[MDEV-23571] InnoDB does not raise a warning if ALTER TABLE index operations are optimized away - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Minor
Resolution: Unresolved
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Labels:
None

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

relates to

MDEV-13301 Optimize DROP INDEX, ADD INDEX into RENAME INDEX

Closed

MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

Closed

MDEV-17598 InnoDB index option for per-record transaction ID

Open

Activity

People

Assignee:: Oleksandr Byelkin

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2020-08-25 18:02

Updated:: 2023-05-24 16:11

Git Integration

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