commit 8f936e0ea806ebb87a455ee6726bf4a46c52cf42
Author: Dmitry Lenev <dmitry.lenev@percona.com>
Date:   Wed Feb 8 10:02:57 2023 +0100

    Bug #105092 AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1
    
    It was possible to set too low auto-increment value for an InnoDB table
    by using ALTER TABLE ... AUTO_INCREMENT = ... right after ALTER TABLE that
    added auto-increment column to the table by modifying existing column.
    In this case auto-increment value was not adjusted to be greater than
    already-used values in the column, unlike it is done in case when one
    tries to set too low auto-increment value for column which was created as
    an auto-increment from the start. The value is also adjusted if one uses
    COPY algorithm to executed ALTER TABLE ... AUTO_INCREMENT=...
    
    Not adjusting the value resulted in eventual duplicate errors on attempts
    to insert new auto-increment values in the table.
    Also the fact that behavior of ALTER TABLE ... AUTO_INCREMENT = ... was
    different depending on whether columns was created as auto-increment from
    the start or were made auto-increment later, even though both cases seem
    identical in SHOW CREATE TABLE output, meant that there might have been
    problems when combination of SQL-dump and binary log was used in backup/
    restore scenarios.
    
    The problematic behavior occurred due to fact that after ALTER TABLE which
    modified existing column to be auto-increment table got 0 as persisted
    auto-increment value, even in cases when column had some other values in it.
    As result code of INPLACE ALTER TABLE in InnoDB storeage engine responsible
    for checking whether new auto-increment value is lower than existing one and
    needs to be adjusted didn't work properly.
    
    This patch solves the problem by changing code in InnoDB SE handling INPLACE
    ALTER TABLE ... AUTO_INCREMENT = ... to not to trust 0 persisted auto-increment
    value and do index check instead. This is similar to what happens when
    we discover 0 persisted auto-increment value during ha_innobase::open().
    
    Alternative approach, which involves persisting correct auto-increment value
    during ALTER TABLE which modifies existing column to be auto-increment was
    considered more intrusive.

diff --git a/mysql-test/suite/innodb/r/auto_increment.result b/mysql-test/suite/innodb/r/auto_increment.result
index a0442628674..22d57f43c92 100644
--- a/mysql-test/suite/innodb/r/auto_increment.result
+++ b/mysql-test/suite/innodb/r/auto_increment.result
@@ -1977,3 +1977,354 @@ ALTER TABLE t1 DROP INDEX `PRIMARY`, ADD COLUMN c INT NOT NULL AUTO_INCREMENT,
 ADD KEY (c);
 DROP TABLE t1;
 SET @@SESSION.information_schema_stats_expiry = DEFAULT;
+#
+# Bug #105092 "AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1"
+#
+# Test case which is similar to one from the bug report.
+CREATE TABLE t1 (id INT PRIMARY KEY);
+INSERT INTO t1 VALUES (3);
+ALTER TABLE t1 MODIFY id INT NOT NULL AUTO_INCREMENT;
+# Use maximum existing value from auto-increment column + 1 as new
+# auto-increment value if someone tries to set this parameter too low.
+ALTER TABLE t1 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+# As result the below INSERT should not cause duplicate key errors
+# (like it has happened before the fix).
+INSERT INTO t1 VALUES (), (), ();
+SELECT * FROM t1;
+id
+3
+4
+5
+6
+DROP TABLE t1;
+#
+# Case when column was created as auto-increment from the start
+# worked as expected even before the fix.
+CREATE TABLE t2 (id INT PRIMARY KEY AUTO_INCREMENT);
+INSERT INTO t2 VALUES (3);
+ALTER TABLE t2 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t2 VALUES (), (), ();
+SELECT * FROM t2;
+id
+3
+4
+5
+6
+DROP TABLE t2;
+#
+# Versions of the first test case with explicit ALGORITHM clause.
+#
+# Case with ALGORITHM=INPLACE failed before the fix.
+CREATE TABLE t3 (id INT PRIMARY KEY);
+INSERT INTO t3 VALUES (3);
+ALTER TABLE t3 MODIFY id INT NOT NULL AUTO_INCREMENT;
+ALTER TABLE t3 AUTO_INCREMENT = 1, ALGORITHM=INPLACE;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t3 VALUES (), (), ();
+SELECT * FROM t3;
+id
+3
+4
+5
+6
+DROP TABLE t3;
+#
+# But case with ALGORITHM=COPY worked as expected.
+CREATE TABLE t4 (id INT PRIMARY KEY);
+INSERT INTO t4 VALUES (3);
+ALTER TABLE t4 MODIFY id INT NOT NULL AUTO_INCREMENT;
+ALTER TABLE t4 AUTO_INCREMENT = 1, ALGORITHM=COPY;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t4 VALUES (), (), ();
+SELECT * FROM t4;
+id
+3
+4
+5
+6
+DROP TABLE t4;
+#
+# Test scenarios involving import of tablespace which were also
+# affected by the same problem.
+CREATE TABLE t5 (id INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t5 VALUES (3);
+# Flush the table and put IBD/CFG files aside for later import.
+FLUSH TABLE t5 FOR EXPORT;
+UNLOCK TABLES;
+# Import tablespace with .CFG into new table and try set
+# too low auto-increment value for it after that.
+CREATE TABLE t6(id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t6 DISCARD TABLESPACE;
+# Copy and then import IBD and CFG file saved earlier.
+ALTER TABLE t6 IMPORT TABLESPACE;
+# The below ALTER TABLE should adjust auto-increment value
+# taking maximum already used value into account, so later
+# INSERT doesn't fail (unlike before the fix).
+ALTER TABLE t6 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t6;
+Table	Create Table
+t6	CREATE TABLE `t6` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t6 VALUES (), (), ();
+SELECT * FROM t6;
+id
+3
+4
+5
+6
+DROP TABLE t6;
+# Test import of tablespace without .CFG which was affected as well.
+CREATE TABLE t7(id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t7 DISCARD TABLESPACE;
+# Copy and then import only IBD saved earlier.
+ALTER TABLE t7 IMPORT TABLESPACE;
+Warnings:
+Warning	1810	InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t7.cfg', will attempt to import without schema verification
+ALTER TABLE t7 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t7;
+Table	Create Table
+t7	CREATE TABLE `t7` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t7 VALUES (), (), ();
+SELECT * FROM t7;
+id
+3
+4
+5
+6
+DROP TABLE t7;
+# Clean up.
+DROP TABLE t5;
+#
+# Additional test coverage showing how AUTO_INCREMENT option is handled
+# by InnoDB export and import.
+#
+#
+# 1) Start from a trivial case when AUTO_INCREMENT option value
+#    matches the max value used in column (plus one).
+CREATE TABLE t_exp (id INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t_exp VALUES (1), (2), (3);
+SHOW CREATE TABLE t_exp;
+Table	Create Table
+t_exp	CREATE TABLE `t_exp` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+# Flush the table and put IBD/CFG files aside for the import.
+FLUSH TABLE t_exp FOR EXPORT;
+UNLOCK TABLES;
+CREATE TABLE t_imp (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp DISCARD TABLESPACE;
+# Copy and then import IBD and CFG file saved earlier.
+ALTER TABLE t_imp IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp;
+Table	Create Table
+t_imp	CREATE TABLE `t_imp` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp VALUES (), (), ();
+SELECT * FROM t_imp;
+id
+1
+2
+3
+4
+5
+6
+DROP TABLE t_imp;
+CREATE TABLE t_imp_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp_no_cfg DISCARD TABLESPACE;
+# Copy and then import only IBD file saved earlier.
+ALTER TABLE t_imp_no_cfg IMPORT TABLESPACE;
+Warnings:
+Warning	1810	InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t_imp_no_cfg.cfg', will attempt to import without schema verification
+SHOW CREATE TABLE t_imp_no_cfg;
+Table	Create Table
+t_imp_no_cfg	CREATE TABLE `t_imp_no_cfg` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_no_cfg;
+id
+1
+2
+3
+4
+5
+6
+DROP TABLE t_imp_no_cfg;
+#
+# 2) Case when value of AUTO_INCREMENT option is higher than
+#    max value used in column.
+ALTER TABLE t_exp AUTO_INCREMENT = 10;
+# Flush the table and put IBD/CFG files aside for the import.
+FLUSH TABLE t_exp FOR EXPORT;
+UNLOCK TABLES;
+CREATE TABLE t_imp_10 (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp_10 DISCARD TABLESPACE;
+# Copy and then import IBD and CFG file saved earlier.
+ALTER TABLE t_imp_10 IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_10;
+Table	Create Table
+t_imp_10	CREATE TABLE `t_imp_10` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp_10 VALUES (), (), ();
+SELECT * FROM t_imp_10;
+id
+1
+2
+3
+10
+11
+12
+DROP TABLE t_imp_10;
+# Note that import without CFG loses original AUTO_INCREMENT value
+# from the exported table (since at the moment we use value of
+# AUTO_INCREMENT option stored in CFG during import).
+CREATE TABLE t_imp_10_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp_10_no_cfg DISCARD TABLESPACE;
+# Copy and then import only IBD file saved earlier.
+ALTER TABLE t_imp_10_no_cfg IMPORT TABLESPACE;
+Warnings:
+Warning	1810	InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t_imp_10_no_cfg.cfg', will attempt to import without schema verification
+SHOW CREATE TABLE t_imp_10_no_cfg;
+Table	Create Table
+t_imp_10_no_cfg	CREATE TABLE `t_imp_10_no_cfg` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp_10_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_10_no_cfg;
+id
+1
+2
+3
+4
+5
+6
+DROP TABLE t_imp_10_no_cfg;
+#
+# 3) Case when AUTO_INCREMENT option is set for table being imported
+#    into. This option value is ignored in both with CFG and sans
+#    CFG cases.
+CREATE TABLE t_imp_into_15 (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 15;
+ALTER TABLE t_imp_into_15 DISCARD TABLESPACE;
+# Copy and then import IBD and CFG file saved earlier.
+ALTER TABLE t_imp_into_15 IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_into_15;
+Table	Create Table
+t_imp_into_15	CREATE TABLE `t_imp_into_15` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp_into_15 VALUES (), (), ();
+SELECT * FROM t_imp_into_15;
+id
+1
+2
+3
+4
+5
+6
+DROP TABLE t_imp_into_15;
+CREATE TABLE t_imp_into_15_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 15;
+ALTER TABLE t_imp_into_15_no_cfg DISCARD TABLESPACE;
+# Copy and then import only IBD file saved earlier.
+ALTER TABLE t_imp_into_15_no_cfg IMPORT TABLESPACE;
+Warnings:
+Warning	1810	InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t_imp_into_15_no_cfg.cfg', will attempt to import without schema verification
+SHOW CREATE TABLE t_imp_into_15_no_cfg;
+Table	Create Table
+t_imp_into_15_no_cfg	CREATE TABLE `t_imp_into_15_no_cfg` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp_into_15_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_into_15_no_cfg;
+id
+1
+2
+3
+4
+5
+6
+DROP TABLE t_imp_into_15_no_cfg;
+# Repeat for cases when both source and target of import have
+# non-trivial value of AUTO_INCREMENT option. Again in both
+# cases target table option is ignored.
+CREATE TABLE t_imp_into_20 (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 20;
+ALTER TABLE t_imp_into_20 DISCARD TABLESPACE;
+# Copy and then import IBD and CFG file saved earlier.
+ALTER TABLE t_imp_into_20 IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_into_20;
+Table	Create Table
+t_imp_into_20	CREATE TABLE `t_imp_into_20` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp_into_20 VALUES (), (), ();
+SELECT * FROM t_imp_into_20;
+id
+1
+2
+3
+10
+11
+12
+DROP TABLE t_imp_into_20;
+# In sans CFG case the AUTO_INCREMENT value is based on max used
+# column value from exported table.
+CREATE TABLE t_imp_into_20_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 20;
+ALTER TABLE t_imp_into_20_no_cfg DISCARD TABLESPACE;
+# Copy and then import only IBD file saved earlier.
+ALTER TABLE t_imp_into_20_no_cfg IMPORT TABLESPACE;
+Warnings:
+Warning	1810	InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t_imp_into_20_no_cfg.cfg', will attempt to import without schema verification
+SHOW CREATE TABLE t_imp_into_20_no_cfg;
+Table	Create Table
+t_imp_into_20_no_cfg	CREATE TABLE `t_imp_into_20_no_cfg` (
+  `id` int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+INSERT INTO t_imp_into_20_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_into_20_no_cfg;
+id
+1
+2
+3
+4
+5
+6
+DROP TABLE t_imp_into_20_no_cfg;
+# Clean up.
+DROP TABLE t_exp;
diff --git a/mysql-test/suite/innodb/t/auto_increment.test b/mysql-test/suite/innodb/t/auto_increment.test
index e98884f5667..94ab892fd45 100644
--- a/mysql-test/suite/innodb/t/auto_increment.test
+++ b/mysql-test/suite/innodb/t/auto_increment.test
@@ -2306,3 +2306,237 @@ ADD KEY (c);
 DROP TABLE t1;
 
 SET @@SESSION.information_schema_stats_expiry = DEFAULT;
+
+
+--echo #
+--echo # Bug #105092 "AUTO_INCREMENT can be set to less than MAX + 1 and not forced to MAX + 1"
+--echo #
+--echo # Test case which is similar to one from the bug report.
+CREATE TABLE t1 (id INT PRIMARY KEY);
+INSERT INTO t1 VALUES (3);
+ALTER TABLE t1 MODIFY id INT NOT NULL AUTO_INCREMENT;
+--echo # Use maximum existing value from auto-increment column + 1 as new
+--echo # auto-increment value if someone tries to set this parameter too low.
+ALTER TABLE t1 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t1;
+--echo # As result the below INSERT should not cause duplicate key errors
+--echo # (like it has happened before the fix).
+INSERT INTO t1 VALUES (), (), ();
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Case when column was created as auto-increment from the start
+--echo # worked as expected even before the fix.
+CREATE TABLE t2 (id INT PRIMARY KEY AUTO_INCREMENT);
+INSERT INTO t2 VALUES (3);
+ALTER TABLE t2 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t2;
+INSERT INTO t2 VALUES (), (), ();
+SELECT * FROM t2;
+DROP TABLE t2;
+
+--echo #
+--echo # Versions of the first test case with explicit ALGORITHM clause.
+--echo #
+--echo # Case with ALGORITHM=INPLACE failed before the fix.
+CREATE TABLE t3 (id INT PRIMARY KEY);
+INSERT INTO t3 VALUES (3);
+ALTER TABLE t3 MODIFY id INT NOT NULL AUTO_INCREMENT;
+ALTER TABLE t3 AUTO_INCREMENT = 1, ALGORITHM=INPLACE;
+SHOW CREATE TABLE t3;
+INSERT INTO t3 VALUES (), (), ();
+SELECT * FROM t3;
+DROP TABLE t3;
+
+--echo #
+--echo # But case with ALGORITHM=COPY worked as expected.
+CREATE TABLE t4 (id INT PRIMARY KEY);
+INSERT INTO t4 VALUES (3);
+ALTER TABLE t4 MODIFY id INT NOT NULL AUTO_INCREMENT;
+ALTER TABLE t4 AUTO_INCREMENT = 1, ALGORITHM=COPY;
+SHOW CREATE TABLE t4;
+INSERT INTO t4 VALUES (), (), ();
+SELECT * FROM t4;
+DROP TABLE t4;
+
+--echo #
+--echo # Test scenarios involving import of tablespace which were also
+--echo # affected by the same problem.
+--let $MYSQLD_DATADIR = `SELECT @@datadir`
+--disable_query_log
+CALL mtr.add_suppression("\\[Warning\\] .* Reading max\\(auto_inc_col\\) = .* for table .*, because there was an IMPORT without cfg file.");
+--enable_query_log
+CREATE TABLE t5 (id INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t5 VALUES (3);
+--echo # Flush the table and put IBD/CFG files aside for later import.
+FLUSH TABLE t5 FOR EXPORT;
+--copy_file $MYSQLD_DATADIR/test/t5.ibd $MYSQLD_DATADIR/t5.ibd_back
+--copy_file $MYSQLD_DATADIR/test/t5.cfg $MYSQLD_DATADIR/t5.cfg_back
+UNLOCK TABLES;
+
+--echo # Import tablespace with .CFG into new table and try set
+--echo # too low auto-increment value for it after that.
+CREATE TABLE t6(id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t6 DISCARD TABLESPACE;
+--echo # Copy and then import IBD and CFG file saved earlier.
+--copy_file $MYSQLD_DATADIR/t5.ibd_back $MYSQLD_DATADIR/test/t6.ibd
+--copy_file $MYSQLD_DATADIR/t5.cfg_back $MYSQLD_DATADIR/test/t6.cfg
+ALTER TABLE t6 IMPORT TABLESPACE;
+--echo # The below ALTER TABLE should adjust auto-increment value
+--echo # taking maximum already used value into account, so later
+--echo # INSERT doesn't fail (unlike before the fix).
+ALTER TABLE t6 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t6;
+INSERT INTO t6 VALUES (), (), ();
+SELECT * FROM t6;
+DROP TABLE t6;
+
+--echo # Test import of tablespace without .CFG which was affected as well.
+CREATE TABLE t7(id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t7 DISCARD TABLESPACE;
+--echo # Copy and then import only IBD saved earlier.
+--copy_file $MYSQLD_DATADIR/t5.ibd_back $MYSQLD_DATADIR/test/t7.ibd
+ALTER TABLE t7 IMPORT TABLESPACE;
+ALTER TABLE t7 AUTO_INCREMENT = 1;
+SHOW CREATE TABLE t7;
+INSERT INTO t7 VALUES (), (), ();
+SELECT * FROM t7;
+DROP TABLE t7;
+
+--echo # Clean up.
+DROP TABLE t5;
+--remove_file $MYSQLD_DATADIR/t5.cfg_back
+--remove_file $MYSQLD_DATADIR/t5.ibd_back
+
+
+--echo #
+--echo # Additional test coverage showing how AUTO_INCREMENT option is handled
+--echo # by InnoDB export and import.
+--echo #
+
+--echo #
+--echo # 1) Start from a trivial case when AUTO_INCREMENT option value
+--echo #    matches the max value used in column (plus one).
+CREATE TABLE t_exp (id INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t_exp VALUES (1), (2), (3);
+SHOW CREATE TABLE t_exp;
+--echo # Flush the table and put IBD/CFG files aside for the import.
+FLUSH TABLE t_exp FOR EXPORT;
+--copy_file $MYSQLD_DATADIR/test/t_exp.ibd $MYSQLD_DATADIR/t_exp.ibd_back
+--copy_file $MYSQLD_DATADIR/test/t_exp.cfg $MYSQLD_DATADIR/t_exp.cfg_back
+UNLOCK TABLES;
+
+CREATE TABLE t_imp (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp DISCARD TABLESPACE;
+--echo # Copy and then import IBD and CFG file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp.ibd_back $MYSQLD_DATADIR/test/t_imp.ibd
+--copy_file $MYSQLD_DATADIR/t_exp.cfg_back $MYSQLD_DATADIR/test/t_imp.cfg
+ALTER TABLE t_imp IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp;
+INSERT INTO t_imp VALUES (), (), ();
+SELECT * FROM t_imp;
+DROP TABLE t_imp;
+
+CREATE TABLE t_imp_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp_no_cfg DISCARD TABLESPACE;
+--echo # Copy and then import only IBD file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp.ibd_back $MYSQLD_DATADIR/test/t_imp_no_cfg.ibd
+ALTER TABLE t_imp_no_cfg IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_no_cfg;
+INSERT INTO t_imp_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_no_cfg;
+DROP TABLE t_imp_no_cfg;
+
+--echo #
+--echo # 2) Case when value of AUTO_INCREMENT option is higher than
+--echo #    max value used in column.
+ALTER TABLE t_exp AUTO_INCREMENT = 10;
+
+--echo # Flush the table and put IBD/CFG files aside for the import.
+FLUSH TABLE t_exp FOR EXPORT;
+--copy_file $MYSQLD_DATADIR/test/t_exp.ibd $MYSQLD_DATADIR/t_exp_10.ibd_back
+--copy_file $MYSQLD_DATADIR/test/t_exp.cfg $MYSQLD_DATADIR/t_exp_10.cfg_back
+UNLOCK TABLES;
+
+CREATE TABLE t_imp_10 (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp_10 DISCARD TABLESPACE;
+--echo # Copy and then import IBD and CFG file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp_10.ibd_back $MYSQLD_DATADIR/test/t_imp_10.ibd
+--copy_file $MYSQLD_DATADIR/t_exp_10.cfg_back $MYSQLD_DATADIR/test/t_imp_10.cfg
+ALTER TABLE t_imp_10 IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_10;
+INSERT INTO t_imp_10 VALUES (), (), ();
+SELECT * FROM t_imp_10;
+DROP TABLE t_imp_10;
+
+--echo # Note that import without CFG loses original AUTO_INCREMENT value
+--echo # from the exported table (since at the moment we use value of
+--echo # AUTO_INCREMENT option stored in CFG during import).
+CREATE TABLE t_imp_10_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY);
+ALTER TABLE t_imp_10_no_cfg DISCARD TABLESPACE;
+--echo # Copy and then import only IBD file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp_10.ibd_back $MYSQLD_DATADIR/test/t_imp_10_no_cfg.ibd
+ALTER TABLE t_imp_10_no_cfg IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_10_no_cfg;
+INSERT INTO t_imp_10_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_10_no_cfg;
+DROP TABLE t_imp_10_no_cfg;
+
+--echo #
+--echo # 3) Case when AUTO_INCREMENT option is set for table being imported
+--echo #    into. This option value is ignored in both with CFG and sans
+--echo #    CFG cases.
+CREATE TABLE t_imp_into_15 (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 15;
+ALTER TABLE t_imp_into_15 DISCARD TABLESPACE;
+--echo # Copy and then import IBD and CFG file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp.ibd_back $MYSQLD_DATADIR/test/t_imp_into_15.ibd
+--copy_file $MYSQLD_DATADIR/t_exp.cfg_back $MYSQLD_DATADIR/test/t_imp_into_15.cfg
+ALTER TABLE t_imp_into_15 IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_into_15;
+INSERT INTO t_imp_into_15 VALUES (), (), ();
+SELECT * FROM t_imp_into_15;
+DROP TABLE t_imp_into_15;
+
+CREATE TABLE t_imp_into_15_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 15;
+ALTER TABLE t_imp_into_15_no_cfg DISCARD TABLESPACE;
+--echo # Copy and then import only IBD file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp.ibd_back $MYSQLD_DATADIR/test/t_imp_into_15_no_cfg.ibd
+ALTER TABLE t_imp_into_15_no_cfg IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_into_15_no_cfg;
+INSERT INTO t_imp_into_15_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_into_15_no_cfg;
+DROP TABLE t_imp_into_15_no_cfg;
+
+--echo # Repeat for cases when both source and target of import have
+--echo # non-trivial value of AUTO_INCREMENT option. Again in both
+--echo # cases target table option is ignored.
+CREATE TABLE t_imp_into_20 (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 20;
+ALTER TABLE t_imp_into_20 DISCARD TABLESPACE;
+--echo # Copy and then import IBD and CFG file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp_10.ibd_back $MYSQLD_DATADIR/test/t_imp_into_20.ibd
+--copy_file $MYSQLD_DATADIR/t_exp_10.cfg_back $MYSQLD_DATADIR/test/t_imp_into_20.cfg
+ALTER TABLE t_imp_into_20 IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_into_20;
+INSERT INTO t_imp_into_20 VALUES (), (), ();
+SELECT * FROM t_imp_into_20;
+DROP TABLE t_imp_into_20;
+
+--echo # In sans CFG case the AUTO_INCREMENT value is based on max used
+--echo # column value from exported table.
+CREATE TABLE t_imp_into_20_no_cfg (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 20;
+ALTER TABLE t_imp_into_20_no_cfg DISCARD TABLESPACE;
+--echo # Copy and then import only IBD file saved earlier.
+--copy_file $MYSQLD_DATADIR/t_exp_10.ibd_back $MYSQLD_DATADIR/test/t_imp_into_20_no_cfg.ibd
+ALTER TABLE t_imp_into_20_no_cfg IMPORT TABLESPACE;
+SHOW CREATE TABLE t_imp_into_20_no_cfg;
+INSERT INTO t_imp_into_20_no_cfg VALUES (), (), ();
+SELECT * FROM t_imp_into_20_no_cfg;
+DROP TABLE t_imp_into_20_no_cfg;
+
+--echo # Clean up.
+--remove_file $MYSQLD_DATADIR/t_exp.cfg_back
+--remove_file $MYSQLD_DATADIR/t_exp.ibd_back
+--remove_file $MYSQLD_DATADIR/t_exp_10.cfg_back
+--remove_file $MYSQLD_DATADIR/t_exp_10.ibd_back
+DROP TABLE t_exp;
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index e7f98cb57e3..0d1dcb41cd5 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -6637,8 +6637,15 @@ static void innobase_rename_or_enlarge_columns_cache(
     we expect the next value allocated from 201, but not 150.
 
     We could only search the tree to know current max counter
-    in the table and compare. */
-    if (ctx->max_autoinc <= max_value_table) {
+    in the table and compare.
+
+    If persisted auto-increment value is 0, it can't be trusted.
+    It might be an indication that auto-increment column just has
+    been added to the table by modifying existing column, so
+    the real maximum value in it has not been persisted yet.
+    This situation can also occur if table has been recently imported.
+    So we do index search in this case as well. */
+    if (max_value_table == 0 || ctx->max_autoinc <= max_value_table) {
       dberr_t err;
       dict_index_t *index;