In-place migration from MySQL 5.7 causes invalid AUTO_INCREMENT values

After a migration of an RDS MySQL 5.7 instance with a large number of tables to MariaDB 10.6.14, significant numbers of tables had incorrect auto_increment values selected that were less than the maximum value of the column. This application heavily uses IODKU, so too-low auto_increment has quite significant negative effects, causing overwritten rows when creation of new ones is instead expected.

This problem occurred across many different tables with different structures and columns (though the auto_increment column in all cases was a simple int primary key). All used the InnoDB engine.

The problem does not appear immediately; I attempted to discover and manually change the auto_increment values of affected tables using information_schema.tables to look for tables where auto_increment was less than or equal to the max of the relevant column. As best I could tell, the auto_increment only became "wrong" once an insert (or possibly any update) was made. The result was that a scan over all tables for this problem would fix some tables, then later others would show the problem.

Ultimately the only solution was to run ALTER TABLE x AUTO_INCREMENT = y for every table, even where the new autoinc value "y" was the same as what information_schema and SHOW CREATE TABLE reported already. As far as I can tell the problem no longer occurs after taking this step.

I'm aware that since MDEV-6076 MariaDB stores the AUTO_INCREMENT for InnoDB persistently, and this was not the case for MySQL 5.7, the source DB for these tables. As far as I can see this is the same issue as MDEV-20892 as well as a few others.

The affected tables ranged from ones that were several years old and originally created by older MySQL versions to those created by 5.7. Prior discussion on other issues leads me to believe it's therefore unlikely that the source of the problem is junk data written into the field repurposed for the persistent autoincrement value. Unfortunately as this is an RDS instance I don't have direct access to the data files to investigate.

I only noted values that were too low (though admittedly too high would not have drawn the same kind of notice). As far as I can tell the initialization of the in-memory autoincrement goes wrong in these cases. My working theories include that initialization is setting the value to the first "gap" in the column, rather than above the max, or alternatively, the first INSERT/IODKU is "setting" the autoinc when it shouldn't.

Some discussion in the prior issues noted that since 10.2 is getting old, this issue while still not solved wouldn't really occur much anymore. I filed this in part just to note that moving from MySQL continues to create a "vector" for this problem.