TL;DR
ALTER TABLE and OPTIMIZE TABLE on an InnoDB table, which rebuilds the table without blocking concurrent changes to it (i.e., executed using INPLACE algorithm) and concurrent DML or purge activity on the table can occasionally lead to two significant problems:
- ALTER/OPTIMIZE TABLE failing with an unnecessary duplicate key error (even though there are no duplicate keys in it).
- Data loss. Yes, seriously!
Versions affected:
- Old LTS series of MySQL Server: 8.0.27 and all 8.0.x versions after this.
- New LTS series of MySQL Server: 8.4.0 and all 8.4.y versions after this.
- Percona Server for MySQL: 8.0.27-18 .. 8.0.37-29 and 8.4.0-1.
- Percona XtraDB Cluster: 8.0.27-18.1 .. 8.0.37-29 and 8.4.0-1.
We advise you to upgrade to the Percona Server for MySQL version where these problems have been fixed (or to a newer MySQL Server version once the fix is available there) or use workarounds to avoid them.
Versions fixed:
- MySQL Server – There are no versions that have fixed these problems yet.
- Percona Server for MySQL 8.0.x series: 8.0.39-30 (released 2024-10-08) or later.
- Percona Server for MySQL 8.4.x series: 8.4.2-2 (released 2024-11-04) or later.
- Percona XtraDB Cluster: 8.0.39-30 (released 2024-12-02) or later.
Longer story
In early spring 2024, one of our customers discovered that a row was unexpectedly missing from an InnoDB table on one of their replicas running Percona Server for MySQL 8.0.28-19. Investigation has shown that there were no delete operations for the row in question (as witnessed by binary log contents). It turned out that, at some point, they accidentally ran null ALTER TABLE ALGORITHM=INPLACE on this table on the replica to reclaim disk space without stopping replication for its duration (so deletions and insertions replicated from the source were still happening concurrently to this ALTER TABLE). Typically, they used ALTER TABLE ALGORITHM=COPY for the same purpose, stopped replication before its execution, and restarted replication afterward, and did not observe such problems (see: PS-9144 “Missing rows after ALGORITHM=INPLACE ALTER under same workload as PS-9092”).
Given the absence of even a non-deterministic test case and the uncertainty surrounding the underlying factors, progress on this issue was impeded.
Fast-forward to summer 2024. While working on another problem in ALTER TABLE INPLACE (PS-9214 “Alter table online results in “duplicate key” error on the primary key (only index)”), for which we did have a non-deterministic test case, we were looking at InnoDB’s code responsible for parallel index creation/table scan and realized, that indeed, there is a bug in this code which might cause row loss in the scenario described by our customer in PS-9144.
Our analysis shows that both these problems are caused by the new parallel online ALTER TABLE code introduced in MySQL Server 8.0.27, which is now also used for non-parallel, single-threaded cases. This code iterates over all the rows in the table, in general case, handling different subtrees of a B-tree in different threads. This iteration over table rows needs to be paused, from time to time, to commit InnoDB mini-transaction/release page latches it holds. This is necessary to give a way to concurrent actions on the B-tree scanned or before flushing rows of new versions of the table from in-memory buffer to the disk. This code saves the cursor position before the pause and then resumes iteration from the saved position after it.
Unfortunately, it turned out that this save/restore combination didn’t always work correctly.
Sometimes, it resulted in iteration revisiting the row that was already processed and subsequent duplicate key error when concurrent insertion happened around the position saved/restored (see more details about this scenario in the description of the fix for this problem). Occasionally, it resulted in skipping of a row by iteration if purge threads were concurrently removing rows around the position saved (again, please look at the description of commit fixing this problem for more details).
In practice, it means that online ALTER TABLE INPLACE, which rebuilds an InnoDB table internally, and OPTIMIZE TABLE, which is mapped to the former by default, might sometimes fail unexpectedly with a duplicate key error if there are concurrent insertions into the table. The statement is rolled back in this case, and the original state of the table, which we have tried to rebuild, is restored. Also, if you are unlucky enough, such a statement might occasionally omit a row from the table being rebuilt if there are purge threads removing old rows from the table concurrently. Since there is no error in this case, the statement is not rolled back, and the table loses a row as a result. There is no indication to the user that such a problem is happening, which makes it really serious! So the answer to the question from the title of the post is – it is the ALTER TABLE code in the InnoDB engine that ate your rows!
We fixed the lost row problem (PS-9144, see commit) and the problem with unexpected duplicate key errors from the online ALTER TABLE mentioned above (PS-9214, see commit) and released them in Percona Server for MySQL versions 8.0.39-30 and 8.4.2-2.
We also reported both these issues to the Oracle MySQL team and submitted our fixes as contributions (see bug #115608, “Inplace ALTER TABLE might cause lost rows if concurrent purge,” and bug #115511, “Inplace ALTER TABLE might fail with a duplicate key error if concurrent insertions”). Unfortunately, upstream has not yet accepted the patches or proposed alternative fixes for these problems yet.
Why do we think these issues are important?
While the problem with an unexpected duplicate key error is not likely and doesn’t result in any kind of persistent effects on data, it still can result in a waste of resources and cause disruption of operations. For example, imagine ALTER TABLE, which rebuilds a 100Gb table and the effect of it failing suddenly without a valid reason at some point close to the end of its execution. All IO capacity and CPU consumed by such an ALTER TABLE will be wasted.
The second problem is much more severe. Even though a row loss is unlikely, it might happen occasionally as a result of fairly ordinary OPTIMIZE TABLE or ALTER TABLE statements. When it happens, it does so completely silently (there is no error, no message in the error log, no crash), so it is likely to stay unnoticed for a long time, if ever!
What can you do to avoid this problem?
- Upgrade to Percona Server for MySQL 8.0.39-30 or 8.4.2-2, where this problem is fixed.
- Alternatively, if you can’t upgrade, you can resort to ALTER TABLE .. ALGORITHM=COPY for cases when ALTER TABLE needs to rebuild the table like our customer was doing (the obvious downside is that concurrent changes to the table will be blocked for its duration). Cases that do not need table rebuild (e.g., those that can be executed using ALGORITHM=INSTANT) should be safe. For OPTIMIZE TABLE, a user has to set the old_alter_table system variable to disable the INPLACE algorithm
- You can also use external tools like pt-online-schema-change instead of resorting to ALTER TABLE ALGORITHM=COPY to change the table definition without blocking concurrent changes to the table.
Percona offers enterprise-grade scalability and performance without traditional enterprise drawbacks. We deliver secure, tested, open source software complete with advanced features like backup, monitoring, and encryption only otherwise found in MySQL Enterprise Edition.
Hi Dmitry,
Thank you for bug reports, the code contribution and a beautiful test cases in them. Just wanted to inform that fixes for these bugs were pushed to trunk last month.
Upcoming release(s) 8.0.41 and up would have the fixes.
We also found that problem could happen with Spatial index as well and fixed that too.