Marko will look into what the problem could be.

However, I don't believe things would be any different if one would upgrade from 10.1 -> 10.3 or 10.1 -> 10.2 -> 10.3
The reason is that the mysql_upgrade process will not touch any old InnoDB tables (that is not in the mysql database) and thus the tables and data dictionary would be identical independent of the upgrade process.
The upgrade process does know when tables are created and if this is indeed a problem how auto-increment is stored in 10.2 and above, it should not be hard to clear this storage area when upgrading a table from 10.1
Do you happen to have a backup of the database before the upgrade? Getting access to one of the tables before and after the upgrade could be very helpful.

When the values of auto-increment was 11209, 11309... do you know what the real value should have been? One or two examples would help a lot!

In MDEV-6076 (MariaDB 10.2.4) the file format was extended by repurposing a previously unused field that I thought was always written as 0 on clustered index root pages, in PAGE_MAX_TRX_ID.

.ibd files and the innodb_file_per_table parameter were introduced in MySQL 4.1.12. Back then, InnoDB left some unused bytes uninitialized. Therefore, we must be careful when repurposing previously unused data bytes. And I thought I was careful, but I may have missed something.

The page initialization was finally added in MySQL 5.1.48 for all data files. The original author of InnoDB opposed the initialization for many years, because he feared that it might cost some performance.

If the affected tables were originally created before MySQL 5.1.48 and never rebuilt since then, the cause could hypothetically be some garbage bytes. I did not recheck the old code for this at this time.

I am happy to analyze this, once we get a reproducible test case that only involves SQL statements and upgrading the server.

I already posted some analysis about the PAGE_MAX_TRX_ID field in MDEV-20357 (which was closed as "cannot reproduce"). It appears that in that ticket, some obviously wrong steps were taken, such as discarding the redo log (ib_logfile*). That might explain why the AUTO_INCREMENT values were slightly wrong. It should not explain huge jumps, though.

The AUTO_INCREMENT values are stored in that field as unsigned 64-bit integers. Could the "always ending in 9" be related to the settings of auto_increment_offset and auto_increment_increment?

I think that this same bug should be repeatable when upgrading to 10.2 or any later major version. Skipping one or two versions should not matter. The interesting question is: How exactly were those data files created, modified, backed up or restored before upgrading?

I asked whether a pre-upgrade backup is still available to test whether a ->10.2 upgrade produces the same, and for auto_increment_* settings.

Regarding MDEV-20357, note that there not only was there a huge jump for most tables, but that the more weird part of it was that from one table to the next (in mysqldump order) it went up by 11 from one table to the next each time.

On the case that lead to this MDEV-22167 on the other hand, there were several tables where the counter had actually jumped back, not forward, quite a bit even though larger values already existed in the table, and that this could only be fixed by explicit

ALTER TABLE...AUTO_INCREMENT=####

hholzgra, are the tables partitioned? If yes, this might share a root cause with MDEV-21842.

I don't think so. I don't know schema details from the customer affected, but as they've seen it on almost every table I don't think that they had all of them partitioned. Also this was not only about having duplicate values in concurrency situations, it was about all affected tables auto_increment counter being reset to a much lower value than before, and a similar one across all affected tables ...

I think that we would really need something reproducible to be able to fix this.

I suppose that all customers have upgraded from 10.1, because it went end of life in October 2020.

The storage format of AUTO_INCREMENT was changed already in MariaDB 10.2.4 in MDEV-6076.