Please provide SHOW CREATE TABLE for the table before and after ALTER, indicate what exactly UPDATE updates, and attach your config files. Thanks.

I do not need a test case to confirm that this is a problem of my design of WL#5526 and WL#6255 in MySQL 5.6, affecting MariaDB 10.0 and later. The same issue was mentioned in MDEV-14535 but fixed in a special case.

The design problem is that currently, any DML operations that are being executed concurrently with an online index creation (MySQL WL#5526) or online table rebuild (MySQL WL#6625) will be logged immediately. For example, BEGIN;INSERT;ROLLBACK; will be logged as INSERT and DELETE.

To prevent the ‘fake’ duplicates, we can simply write online_log on transaction commit. In that way, any duplicate key errors during online table rebuild will be filtered out from the online_log, because such duplicate key errors would also occur on the original table, leading to full or partial rollback of the transaction.

That is, on transaction commit, we would check if any table in trx_t::mod_tables requires changes to be logged. If yes, we would scan the undo logs that the transaction generated, and write online_log to the affected tables during transaction commit.

In secondary index creation (especially CREATE UNIQUE INDEX or ALTER TABLE…ADD UNIQUE INDEX) there is an additional phenomenon: A secondary index may be complete but not committed. That is, the online secondary index creation operation would wait for an exclusive metadata lock so that the operation can be committed. A conflicting metadata lock could be held by a transaction that is concurrently modifying the table. Such transactions will have to update the uncommitted index directly. Duplicate key errors must not be reported to the DML transaction if the unique index has not been committed yet. They are reported to the DDL transaction. Also such reporting is now premature and could lead to false duplicates. I think that we can apply changes to uncommitted indexes at transaction commit time.

thiru, I hope that you can complete my prototype. I believe that fixing this may simplify the MDEV-26294 fix in the case that row_log_apply() completed but the ALTER TABLE operation was not committed yet (because the SQL layer is waiting for MDL_EXCLUSIVE, which is blocked by concurrent DML transactions).

I posted the first batch of review comments. I did not yet thoroughly check all the new code in trx_t::apply_log(), especially the trx_undo_rec_info. Some source code comments would be very helpful in that area.

• Addressed all review comments.

Code coverage analysis looks better now. This is OK to push after a final round of testing by mleich.

origin/bb-10.6-MDEV-15250 f9c152c9f6b770961cdd8467ce05548347812ed8 2022-04-22T18:15:48+05:30
behaved well in RQG testing. The bad effects observed are known and in the main trees too.