[MDEV-23571] InnoDB does not raise a warning if ALTER TABLE index operations are optimized away Created: 2020-08-25 Updated: 2023-05-24 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Data Definition - Alter Table, Storage Engine - InnoDB |
| Affects Version/s: | 10.2, 10.3, 10.4, 10.5 |
| Fix Version/s: | 10.4, 10.5 |
| Type: | Bug | Priority: | Minor |
| Reporter: | Geoff Montee (Inactive) | Assignee: | Oleksandr Byelkin |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Issue Links: |
|
||||||||||||||||
| 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:
We can check the index ID by looking at INNODB_SYS_INDEXES with this query:
For example:
If we drop and re-add the index, then the INDEX_ID will change:
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:
This happens with innodb_strict_mode enabled:
InnoDB should probably raise a warning when it "optimizes away" an ALTER TABLE operation--especially if innodb_strict_mode is enabled. |
| Comments |
| Comment by Marko Mäkelä [ 2020-08-26 ] | |
|
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 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 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. | |
| Comment by Sergei Golubchik [ 2020-08-28 ] | |
|
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. | |
| Comment by Geoff Montee (Inactive) [ 2020-08-28 ] | |
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. | |
| Comment by Sergei Golubchik [ 2020-08-29 ] | |
|
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. | |
| Comment by Geoff Montee (Inactive) [ 2020-08-29 ] | |
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. | |
| Comment by Sergei Golubchik [ 2020-08-30 ] | |
|
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. | |
| Comment by Rick James [ 2023-05-24 ] | |
|
"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. |