greenman, your interpretation sounds correct to me.
It could be useful in debugging to view the current persistent auto_increment value. But generally, too many rarely used interfaces and parameters tend to be a bad idea.

The regression would be that after a failed INSERT IGNORE and a restart, there would be a "gap" in the AUTO_INCREMENT value. Another case where you can get such a "gap" is a user-initiated ROLLBACK or ROLLBACK TO SAVEPOINT. But also that requires a server restart.
Before MDEV-6076 introduced the persistent AUTO_INCREMENT in 10.2.4, we had the opposite problem that the server would not leave the "gap", but would instead perform the equivalent of SELECT MAX(auto_inc_column) when the table is first accessed. I can imagine that in a distributed system, you would definitely not want to reuse AUTO_INCREMENT values that may have been previously used somewhere else.

So, this regression (gap after restart) would seem to be a desired property of MDEV-6076; in fact, I could claim that it is the main reason to implement MDEV-6076. (The other reason would be to avoid a potentially expensive index scan to determine MAX(autoinc_col), in case there are many NULL values or delete-marked records.)

I have documented the current behaviour, though this still seems undesirable and perhaps it can be solved in another way.

Without knowledge of the underlying implementation, does the following logic hold?

• the single case that persistent autoinc is useful for is when ALTER TABLE yourTable AUTO_INCREMENT = x; is run, and then the server restarted immediately after that.
• if there was a flag in the persistent autoinc, on for when autoinc is set with the above statement, off for when it's set any other way, then, when the server restarts, and the persistent autoinc is only used when the flag is on, it should be correct in all situations? An INSERT IGNORE will leave the flag off, so the autoinc will not be overwritten, and the gap won't exist. But if it is specifically set with an ALTER ..., the flag will be set, and then persistent autoinc can be used when the server restarts?

greenman, I think that the main use case of persistent autoinc is a guarantee that the same values are not reassigned after server restart (except if the counter was reset with ALTER TABLE).

It just occurred to me that the MyISAM and Aria engines (which featured persistent AUTO_INCREMENT from day one) have a uniquely defined AUTO_INCREMENT value: that of the last committed record. With table-level locking and with no support for rollback (except in Aria crash recovery), this works.

InnoDB allows concurrent modifications to the table, and it supports rollback. Therefore, the AUTO_INCREMENT is necessarily different. The value that is persisted in the first root page of the table is the maximum value that was used in an INSERT or UPDATE. Even if the operation was rolled back, that value will remain.

Another AUTO_INCREMENT is the value that is reported to the user in the SHOW CREATE TABLE statement. And yet another one is the LAST_INSERT_ID(). In InnoDB, these are non-persistent.

elenst, what would you think if we extended INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS with a column that shows the persistent auto-increment value?

Marko's suggestion seems like a fairly quick and easy approach. Are there any objections to it?

Sorry, I missed the question addressed to me back then.

We can add all kinds of columns to InnoDB I_S tables, I don't see how it will solve anything. People don't search for the auto-increment value for fun or out of curiosity, much less should they care about the dark secrets of implementation – what's "persistent" and what not. They should see the value which is actually true now, and it should be consistent. Now the obvious discrepancy in SHOW CREATE TABLE breaks simple consistency checks (tables before and after server restart differ), but it's not limited to it. AUTO_INCREMENT in SHOW CREATE TABLE is not cosmetics, it shows what happens next, and the behavior of auto-increment becomes inconsistent after the scenario above. If you keep normally inserting into the table before the restart, it will insert 4 etc. If you insert after restart, it will insert 101 etc.

That said, I am not going to argue now against whichever decision is made, I'll leave it to be resolved by the natural course of events. We have already been questioned why such unexpected jumps can occur. So far it was by Monty, who, I suppose, relayed a question from customers; I expect it to happen again and eventually present itself in a form of an official customer request which we won't be able to ignore.