MySQL Bugs: #98092: The desc of SELECT MAX usage for 8.0 autoinc is inaccurate

Bug #98092	The desc of SELECT MAX usage for 8.0 autoinc is inaccurate
Submitted:	30 Dec 2019 3:27	Modified:	10 Feb 2020 14:00
Reporter:	Fungo Wang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the doc about auto inc handling https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-in....

It says
"The only circumstance in which InnoDB uses the equivalent of a SELECT MAX(ai_col) FROM table_name FOR UPDATE statement in MySQL 8.0 and later to initialize an auto-increment counter is when importing a table without a .cfg metadata file."

But actually this is not the only circumstance, there is another one for `ALTER TABLE ... AUTO_INCREMENT = N `.

Check the function `commit_get_autoinc()` in handler0alter.cc.

We can use the example in comments to trigger the invoke of `row_search_max_autoinc()`, i.e SELECT MAX(ai_col) .

```
6373    /* We still have to search the index here when we want to
6374    set the AUTO_INCREMENT value to a smaller or equal one.
6375
6376    Here is an example:
6377    Let's say we have a table t1 with one AUTOINC column, existing
6378    rows (1), (2), (100), (200), (1000), after following SQLs:
6379    DELETE FROM t1 WHERE a > 200;
6380    ALTER TABLE t1 AUTO_INCREMENT = 150;
6381    we expect the next value allocated from 201, but not 150.
6382
6383    We could only search the tree to know current max counter
6384    in the table and compare. */
```

The call stack is 
```
(gdb) bt
#0  row_search_max_autoinc (index=0x7f1bc80afdd8, col_name=0x7f1bc8b8a950 "id", value=0x7f1c7c043918)
    at /home/fungo/mysql-server-8/storage/innobase/row/row0sel.cc:6208
#1  0x0000000005e03b9c in commit_get_autoinc (ha_alter_info=0x7f1c7c044c10, ctx=0x7f1bc8c033c8, altered_table=0x7f1bc8c04520, old_table=0x7f1bc8c0aba0)
    at /home/fungo/mysql-server-8/storage/innobase/handler/handler0alter.cc:6392
#2  0x0000000005e36900 in ha_innobase::commit_inplace_alter_table_impl<dd::Table> (this=0x7f1bc8c0dcd8, altered_table=0x7f1bc8c04520, ha_alter_info=0x7f1c7c044c10, commit=true,
    old_dd_tab=0x7f1bc8b79340, new_dd_tab=0x7f1bc8bf9560) at /home/fungo/mysql-server-8/storage/innobase/handler/handler0alter.cc:7352
#3  0x0000000005df5eed in ha_innobase::commit_inplace_alter_table (this=0x7f1bc8c0dcd8, altered_table=0x7f1bc8c04520, ha_alter_info=0x7f1c7c044c10, commit=true,
    old_dd_tab=0x7f1bc8b79340, new_dd_tab=0x7f1bc8bf9560) at /home/fungo/mysql-server-8/storage/innobase/handler/handler0alter.cc:1321
#4  0x00000000037fcf99 in handler::ha_commit_inplace_alter_table (this=0x7f1bc8c0dcd8, altered_table=0x7f1bc8c04520, ha_alter_info=0x7f1c7c044c10, commit=true,
    old_table_def=0x7f1bc8b79340, new_table_def=0x7f1bc8bf9560) at /home/fungo/mysql-server-8/sql/handler.cc:4762
#5  0x0000000003289d07 in mysql_inplace_alter_table (thd=0x7f1bc8b6dcf0, schema=..., new_schema=..., table_def=0x7f1bc8b79340, altered_table_def=0x7f1bc8bf9560,
    table_list=0x7f1bc8c00a68, table=0x7f1bc8c0aba0, altered_table=0x7f1bc8c04520, ha_alter_info=0x7f1c7c044c10, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE,
    alter_ctx=0x7f1c7c045a30, columns=..., fk_key_info=0x7f1bc8c02f30, fk_key_count=0, fk_invalidator=0x7f1c7c045960)
    at /home/fungo/mysql-server-8/sql/sql_table.cc:12750
#6  0x00000000032a03e2 in mysql_alter_table (thd=0x7f1bc8b6dcf0, new_db=0x7f1bc8c01008 "test", new_name=0x0, create_info=0x7f1c7c047450, table_list=0x7f1bc8c00a68,
    alter_info=0x7f1c7c047300) at /home/fungo/mysql-server-8/sql/sql_table.cc:16434
#7  0x0000000003d0690f in Sql_cmd_alter_table::execute (this=0x7f1bc8c01010, thd=0x7f1bc8b6dcf0) at /home/fungo/mysql-server-8/sql/sql_alter.cc:348
#8  0x000000000312bd66 in mysql_execute_command (thd=0x7f1bc8b6dcf0, first_level=true) at /home/fungo/mysql-server-8/sql/sql_parse.cc:4453
#9  0x0000000003131ad2 in mysql_parse (thd=0x7f1bc8b6dcf0, parser_state=0x7f1c7c048b30) at /home/fungo/mysql-server-8/sql/sql_parse.cc:5257
#10 0x000000000311986d in dispatch_command (thd=0x7f1bc8b6dcf0, com_data=0x7f1c7c049bd0, command=COM_QUERY) at /home/fungo/mysql-server-8/sql/sql_parse.cc:1765
#11 0x000000000311595b in do_command (thd=0x7f1bc8b6dcf0) at /home/fungo/mysql-server-8/sql/sql_parse.cc:1273
#12 0x000000000348d006 in handle_connection (arg=0xe9fb8a0) at /home/fungo/mysql-server-8/sql/conn_handler/connection_handler_per_thread.cc:302
#13 0x0000000006a706f7 in pfs_spawn_thread (arg=0xe9c0e40) at /home/fungo/mysql-server-8/storage/perfschema/pfs.cc:2854
#14 0x00007f1c8e13fe25 in start_thread () from /lib64/libpthread.so.0
#15 0x00007f1c8c38bf1d in clone () from /lib64/libc.so.6
```

How to repeat:
Read the doc and code.

Suggested fix:
Fix the desc in doc.

Hello Fungo Wang,

Thank you for the report.

regards,
Umesh

Posted by developer:

The reference documentation has been updated with the following content. Changes should appear online soon.

"Aside from counter value initialization, the equivalent of a SELECT MAX(ai_col) FROM table_name statement is used to determine the current maximum auto-increment counter value of the table when attempting to set the counter value to one that is smaller than or equal to the persisted counter value using an ALTER TABLE ... AUTO_INCREMENT = N FOR UPDATE statement. For example, you might try to set the counter value to a lesser value after deleting some records. In this case, the table must be searched to ensure that the new counter value is not less than or equal to the actual current maximum counter value."

Reference:
https://dev.mysql.com/worklog/task/?id=6204

"3.1 DDLs('ALTER TABLE' and 'TRUNCATE TABLE')
We have to handle 'ALTER TABLE', 'RENAME TABLE' and 'TRUNCATE TABLE' specially.
There are several different cases:
a) Reset the auto_increment to 0(row_truncate_table_for_mysql)
b) Reset to some smaller one, other than
0(ha_innobase::commit_inplace_alter_table())
c) Reset to bigger or equal one(ha_innobase::commit_inplace_alter_table() or
row_rename_table_for_mysql())

For a), we will just set it to 0.
For b), if we find it's going to be set to smaller than the
table::autoinc_persisted, we should check what's that biggest counter in
the table in commit_get_autoinc(), so that we won't set the counter to one
that's smaller than existing biggest counter. This is kept as is and the
tree search is necessary. For example, the autoinc_persisted is 10, and
the biggest counter in table is 8 since we have deleted 9 and 10, reseting
the counter to 8 or 9 is allowable, but any value less than 8 is prohibited
and we will use 8 instead.
For c), we should be able to set it directly, without any search."