Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
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-35169 ALTER TABLE...IMPORT TABLESPACE does not work with INDEX DESC
-
- Closed
-
-
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
-
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 inMDEV-7318andMDEV-16290.The optimizing away of DROP INDEX, ADD INDEX pairs is somewhat inconsistent with the statement
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-13756is 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.