Bug #103116 Make removing an auto-increment on a table a meta-data only ALTER.
Submitted: 25 Mar 20:34 Modified: 26 Mar 5:48
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:8.0.23, 5.7.33 OS:Any
Assigned to: CPU Architecture:Any

[25 Mar 20:34] Jean-François Gagné

an ALTER TABLE to remove an auto-increment is currently rewriting the table, which takes a long time when the table is big.  From my understanding, this does not need a table rewrite.  Please consider making this a meta-data only ALTER, which would be much better.

See how to repeat for details.  Note that the test is done with 8.0.23, but similar results are obtained with 5.7.33.

For convincing you that this is not unimportant, the context is as follow.  We have been moving to an external way to generate unique ids that behave like auto-increment, so we are removing auto-increments from many tables (if MySQK would allocate an auto-inc, it would conflict with the next id allocated by our external system).  So removing auto-increments from many tables is not something that is only a theoretical need, and having to wait for a table rewrite (of run a pt-osc or ghost) is very inconvenient.

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
# Let's create a sandbox with 8.0.23.
dbdeployer deploy single mysql_8.0.23

# Let's initialize our test environment by creating a schema and a table, we are using CHAR columns to create big rows to have a big table on disk.
./use <<< "create database test_jfg"
./use test_jfg <<< "create table t (id bigint unsigned not null auto_increment primary key)"
for i in $(seq 0 19); do ./use test_jfg <<< "alter table t add column c$i char(255) not null default ''"; done

# Let's put data in that table.
seq 256 | while read l; do ( echo "insert into t(id) values (NULL)"; yes "(NULL)"| head -n 1023; ) | paste -s -d "," | ./use test_jfg; done

# Now, let's test an alter that we expect to rewrite the table.
time ./use test_jfg <<< "alter table t add column c char(255) not null default '' after id"

real    1m58.232s
user    0m0.006s
sys     0m0.003s

# Now an alter that is meta-data only.
time ./use test_jfg <<< "alter table t modify column c char(255) not null default '0'"

real    0m0.022s
user    0m0.008s
sys     0m0.000s

# And finally, removing the auto-increment, which according to the time, is rewriting the table.
time ./use test_jfg <<< "alter table t modify column id bigint unsigned not null"

real    1m48.964s
user    0m0.010s
sys     0m0.000s

# And we can convince ourselves that the table is rewritten by checking in the data directory.
jgagne@ip-172-31-34-221:~/sandboxes/msb_mysql_8_0_23/data/test_jfg$ ls -lah
total 2.5G
-rw-r----- 1 jgagne jgagne 144M Mar 25 20:20 '#sql-ba1_622.ibd'
drwxr-x--- 2 jgagne jgagne 4.0K Mar 25 20:20  .
drwxr-xr-x 8 jgagne jgagne 4.0K Mar 25 19:41  ..
-rw-r----- 1 jgagne jgagne 2.4G Mar 25 20:20  t.ibd
[26 Mar 5:48] MySQL Verification Team
Hello Jean-François,

Thank you for the feature request!