4 files changed, 547 insertions, 17 deletions
diff --git a/mysql-test/suite/innodb/t/autoinc_persist.test b/mysql-test/suite/innodb/t/autoinc_persist.test
new file mode 100644
index 00000000000..e4ea2db6bd2
--- /dev/null
+++ b/mysql-test/suite/innodb/t/autoinc_persist.test
@@ -0,0 +1,533 @@
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+
+--echo #
+--echo # MDEV-6076 Persistent AUTO_INCREMENT for InnoDB
+--echo #
+--echo # WL#6204 InnoDB persistent max value for autoinc columns
+--echo #
+--echo # Most of this test case is copied from the test innodb.autoinc_persist
+--echo # that was introduced in MySQL 8.0.0. The observable behaviour
+--echo # of MDEV-6076 is equivalent to WL#6204, with the exception that
+--echo # there is less buffering taking place and redo log checkpoints
+--echo # are not being treated specially.
+--echo # Due to less buffering, there is no debug instrumentation testing
+--echo # for MDEV-6076.
+--echo #
+
+--echo # Pre-create several tables
+
+SET SQL_MODE='STRICT_ALL_TABLES';
+
+CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
+INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
+(20), (30), (31);
+SELECT * FROM t1;
+
+CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
+--error ER_WARN_DATA_OUT_OF_RANGE
+INSERT INTO t2 VALUES(-5);
+INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
+(20), (30), (31);
+SELECT * FROM t2;
+
+CREATE TABLE t3(a SMALLINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
+INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
+(20), (30), (31), (1024), (4096);
+SELECT * FROM t3;
+
+CREATE TABLE t4(a SMALLINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
+--error ER_WARN_DATA_OUT_OF_RANGE
+INSERT INTO t4 VALUES(-5);
+INSERT INTO t4 VALUES(0), (0), (0), (0), (8), (10), (0),
+(20), (30), (31), (1024), (4096);
+SELECT * FROM t4;
+
+CREATE TABLE t5(a MEDIUMINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
+INSERT INTO t5 VALUES(0), (0), (0), (0), (-1), (-10), (0),
+(20), (30), (31), (1000000), (1000005);
+SELECT * FROM t5;
+
+CREATE TABLE t6(a MEDIUMINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
+--error ER_WARN_DATA_OUT_OF_RANGE
+INSERT INTO t6 VALUES(-5);
+INSERT INTO t6 VALUES(0), (0), (0), (0), (8), (10), (0),
+(20), (30), (31), (1000000), (1000005);
+SELECT * FROM t6;
+
+CREATE TABLE t7(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB;
+INSERT INTO t7 VALUES(0), (0), (0), (0), (-1), (-10), (0),
+(20), (30), (31), (100000000), (100000008);
+SELECT * FROM t7;
+
+CREATE TABLE t8(a INT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
+--error ER_WARN_DATA_OUT_OF_RANGE
+INSERT INTO t8 VALUES(-5);
+INSERT INTO t8 VALUES(0), (0), (0), (0), (8), (10), (0),
+(20), (30), (31), (100000000), (100000008);
+SELECT * FROM t8;
+
+CREATE TABLE t9(a BIGINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
+INSERT INTO t9 VALUES(0), (0), (0), (0), (-1), (-10), (0),
+(20), (30), (31), (100000000000), (100000000006);
+SELECT * FROM t9;
+
+CREATE TABLE t10(a BIGINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
+--error ER_WARN_DATA_OUT_OF_RANGE
+INSERT INTO t10 VALUES(-5);
+INSERT INTO t10 VALUES(0), (0), (0), (0), (8), (10), (0),
+(20), (30), (31), (100000000000), (100000000006);
+SELECT * FROM t10;
+
+CREATE TABLE t11(a FLOAT AUTO_INCREMENT KEY) ENGINE = InnoDB;
+INSERT INTO t11 VALUES(0), (0), (0), (0), (-1), (-10), (0),
+(20), (30), (31);
+SELECT * FROM t11;
+
+# Since autoinc counter is persisted by redo logs, we don't want to
+# lose them on kill and restart, so to make the result after restart stable.
+set global innodb_flush_log_at_trx_commit=1;
+
+CREATE TABLE t12(a DOUBLE AUTO_INCREMENT KEY) ENGINE = InnoDB;
+INSERT INTO t12 VALUES(0), (0), (0), (0), (-1), (-10), (0),
+(20), (30), (31);
+SELECT * FROM t12;
+
+CREATE TABLE t13(a INT AUTO_INCREMENT PRIMARY KEY) ENGINE = InnoDB,
+AUTO_INCREMENT = 1234;
+
+--echo # Scenario 1: Normal restart, to test if the counters are persisted
+--source include/restart_mysqld.inc
+
+--echo # We expect these results should be equal to above SELECTs
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT * FROM t4;
+SELECT * FROM t5;
+SELECT * FROM t6;
+SELECT * FROM t7;
+SELECT * FROM t8;
+SELECT * FROM t9;
+SELECT * FROM t10;
+SELECT * FROM t11;
+SELECT * FROM t12;
+
+SELECT * FROM t13;
+SHOW CREATE TABLE t13;
+INSERT INTO t13 VALUES(0);
+SELECT a AS `Expect 1234` FROM t13;
+
+--echo # Scenario 2: Delete some values, to test the counters should not be the
+--echo # one which is the largest in current table
+
+set global innodb_flush_log_at_trx_commit=1;
+
+DELETE FROM t1 WHERE a > 30;
+SELECT MAX(a) AS `Expect 30` FROM t1;
+DELETE FROM t3 WHERE a > 2000;
+SELECT MAX(a) AS `Expect 2000` FROM t3;
+DELETE FROM t5 WHERE a > 1000000;
+SELECT MAX(a) AS `Expect 1000000` FROM t5;
+DELETE FROM t7 WHERE a > 100000000;
+SELECT MAX(a) AS `Expect 100000000` FROM t7;
+DELETE FROM t9 WHERE a > 100000000000;
+SELECT MAX(a) AS `Expect 100000000000` FROM t9;
+
+--source include/restart_mysqld.inc
+
+INSERT INTO t1 VALUES(0), (0);
+SELECT MAX(a) AS `Expect 33` FROM t1;
+INSERT INTO t3 VALUES(0), (0);
+SELECT MAX(a) AS `Expect 4098` FROM t3;
+INSERT INTO t5 VALUES(0), (0);
+SELECT MAX(a) AS `Expect 1000007` FROM t5;
+INSERT INTO t7 VALUES(0), (0);
+SELECT MAX(a) AS `Expect 100000010` FROM t7;
+INSERT INTO t9 VALUES(0), (0);
+SELECT MAX(a) AS `Expect 100000000008` FROM t9;
+
+--echo # Scenario 3: Insert some bigger counters, the next counter should start
+--echo # from there
+
+INSERT INTO t1 VALUES(40), (0);
+INSERT INTO t1 VALUES(42), (0);
+SELECT a AS `Expect 43, 42` FROM t1 ORDER BY a DESC LIMIT 4;
+INSERT INTO t3 VALUES(5000), (0);
+INSERT INTO t3 VALUES(5010), (0);
+SELECT a AS `Expect 5011, 5010` FROM t3 ORDER BY a DESC LIMIT 4;
+INSERT INTO t5 VALUES(1000010), (0);
+INSERT INTO t5 VALUES(1000020), (0);
+SELECT a AS `Expect 1000021, 1000020` FROM t5 ORDER BY a DESC LIMIT 4;
+INSERT INTO t7 VALUES(100000020), (0);
+INSERT INTO t7 VALUES(100000030), (0);
+SELECT a AS `Expect 100000031, 100000030` FROM t7 ORDER BY a DESC LIMIT 4;
+INSERT INTO t9 VALUES(100000000010), (0);
+INSERT INTO t9 VALUES(100000000020), (0);
+SELECT a AS `Expect 100000000021, 100000000020` FROM t9 ORDER BY a DESC LIMIT 4;
+
+--echo # Scenario 4: Update some values, to test the counters should be updated
+--echo # to the bigger value, but not smaller value.
+
+INSERT INTO t1 VALUES(50), (55);
+# Updating to bigger value will update the auto-increment counter
+UPDATE t1 SET a = 105 WHERE a = 5;
+# Updating to smaller value will not update the counter
+UPDATE t1 SET a = 100 WHERE a = 55;
+--echo # This should insert 102, 106, 107, and make next counter 109.
+INSERT INTO t1 VALUES(102), (0), (0);
+SELECT a AS `Expect 107, 106` FROM t1 ORDER BY a DESC LIMIT 2;
+DELETE FROM t1 WHERE a > 105;
+INSERT INTO t1 VALUES(0);
+SELECT MAX(a) AS `Expect 109` FROM t1;
+
+--echo # Test the same things on t3, t5, t7, t9, to test if DDTableBuffer would
+--echo # be updated accordingly
+
+INSERT INTO t3 VALUES(60), (65);
+# Updating to bigger value will update the auto-increment counter
+UPDATE t3 SET a = 6005 WHERE a = 5;
+# Updating to smaller value will not update the counter
+UPDATE t3 SET a = 6000 WHERE a = 60;
+--echo # This should insert 6002, 6006, 6007, and make next counter 6009.
+INSERT INTO t3 VALUES(6002), (0), (0);
+SELECT a AS `Expect 6007, 6006` FROM t3 ORDER BY a DESC LIMIT 2;
+DELETE FROM t3 WHERE a > 6005;
+INSERT INTO t3 VALUES(0);
+SELECT MAX(a) AS `Expect 6009` FROM t3;
+
+INSERT INTO t5 VALUES(100), (200);
+# Updating to bigger value will update the auto-increment counter
+UPDATE t5 SET a = 1000105 WHERE a = 5;
+# Updating to smaller value will not update the counter
+UPDATE t5 SET a = 1000100 WHERE a = 100;
+--echo # This should insert 1000102, 1000106, 1000107, and make next counter
+--echo # 1000109.
+INSERT INTO t5 VALUES(1000102), (0), (0);
+SELECT a AS `Expect 1000107, 1000106` FROM t5 ORDER BY a DESC LIMIT 2;
+DELETE FROM t5 WHERE a > 1000105;
+INSERT INTO t5 VALUES(0);
+SELECT MAX(a) AS `Expect 1000109` FROM t5;
+
+INSERT INTO t7 VALUES(100), (200);
+# Updating to bigger value will update the auto-increment counter
+UPDATE t7 SET a = 100000105 WHERE a = 5;
+# Updating to smaller value will not update the counter
+UPDATE t7 SET a = 100000100 WHERE a = 100;
+--echo # This should insert 100000102, 1100000106, 100000107, and make next
+--echo # counter 100000109.
+INSERT INTO t7 VALUES(100000102), (0), (0);
+SELECT a AS `Expect 100000107, 100000106` FROM t7 ORDER BY a DESC LIMIT 2;
+DELETE FROM t7 WHERE a > 100000105;
+INSERT INTO t7 VALUES(0);
+SELECT MAX(a) AS `Expect 100000109` FROM t7;
+
+set global innodb_flush_log_at_trx_commit=1;
+
+INSERT INTO t9 VALUES(100), (200);
+# Updating to bigger value will update the auto-increment counter
+UPDATE t9 SET a = 100000000105 WHERE a = 5;
+# Updating to smaller value will not update the counter
+UPDATE t9 SET a = 100000000100 WHERE a = 100;
+--echo # This should insert 100000000102, 100000000106, 100000000107, and make
+--echo # next counter 100000000109.
+INSERT INTO t9 VALUES(100000000102), (0), (0);
+SELECT a AS `Expect 100000000107, 100000000106` FROM t9 ORDER BY a DESC LIMIT 2;
+DELETE FROM t9 WHERE a > 100000000105;
+INSERT INTO t9 VALUES(0);
+SELECT MAX(a) AS `Expect 100000000109` FROM t9;
+
+--source include/restart_mysqld.inc
+
+INSERT INTO t1 VALUES(0), (0);
+SELECT a AS `Expect 110, 111` FROM t1 ORDER BY a DESC LIMIT 2;
+
+INSERT INTO t3 VALUES(0), (0);
+SELECT a AS `Expect 6010, 6011` FROM t3 ORDER BY a DESC LIMIT 2;
+
+INSERT INTO t5 VALUES(0), (0);
+SELECT a AS `Expect 1100111, 1100110` FROM t5 ORDER BY a DESC LIMIT 2;
+
+INSERT INTO t7 VALUES(0), (0);
+SELECT a AS `Expect 100000111, 100000110` FROM t7 ORDER BY a DESC LIMIT 2;
+
+INSERT INTO t9 VALUES(0), (0);
+SELECT a AS `Expect 100000000111, 100000000110` FROM t9 ORDER BY a DESC LIMIT 2;
+
+--echo # Scenario 5: Test kill the server
+
+INSERT INTO t1 VALUES(125);
+DELETE FROM t1 WHERE a = 125;
+
+INSERT INTO t3 VALUES(6100);
+DELETE FROM t3 WHERE a = 6100;
+
+INSERT INTO t5 VALUES(1100200);
+DELETE FROM t5 WHERE a = 1100200;
+
+INSERT INTO t7 VALUES(100000200);
+DELETE FROM t7 WHERE a = 100000200;
+
+set global innodb_flush_log_at_trx_commit=1;
+
+INSERT INTO t9 VALUES(100000000200);
+DELETE FROM t9 WHERE a = 100000000200;
+
+--source include/kill_and_restart_mysqld.inc
+
+INSERT INTO t1 VALUES(0);
+SELECT a AS `Expect 126` FROM t1 ORDER BY a DESC LIMIT 1;
+
+INSERT INTO t3 VALUES(0);
+SELECT a AS `Expect 6101` FROM t3 ORDER BY a DESC LIMIT 1;
+
+INSERT INTO t5 VALUES(0);
+SELECT a AS `Expect 1100201` FROM t5 ORDER BY a DESC LIMIT 1;
+
+INSERT INTO t7 VALUES(0);
+SELECT a AS `Expect 100000201` FROM t7 ORDER BY a DESC LIMIT 1;
+
+INSERT INTO t9 VALUES(0);
+SELECT a AS `Expect 100000000201` FROM t9 ORDER BY a DESC LIMIT 1;
+
+--echo # Scenario 6: Test truncate will reset the counters to 0
+
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t3;
+TRUNCATE TABLE t5;
+TRUNCATE TABLE t7;
+TRUNCATE TABLE t9;
+
+INSERT INTO t1 VALUES(0), (0);
+SELECT * FROM t1;
+
+INSERT INTO t3 VALUES(0), (0);
+SELECT * FROM t3;
+
+INSERT INTO t5 VALUES(0), (0);
+SELECT * FROM t5;
+
+INSERT INTO t7 VALUES(0), (0);
+SELECT * FROM t7;
+
+INSERT INTO t9 VALUES(0), (0);
+SELECT * FROM t9;
+
+set global innodb_flush_log_at_trx_commit=1;
+
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t3;
+TRUNCATE TABLE t5;
+TRUNCATE TABLE t7;
+TRUNCATE TABLE t9;
+
+--source include/kill_and_restart_mysqld.inc
+
+INSERT INTO t1 VALUES(0), (0);
+SELECT * FROM t1;
+
+INSERT INTO t3 VALUES(0), (0);
+SELECT * FROM t3;
+
+INSERT INTO t5 VALUES(0), (0);
+SELECT * FROM t5;
+
+INSERT INTO t7 VALUES(0), (0);
+SELECT * FROM t7;
+
+INSERT INTO t9 VALUES(0), (0);
+SELECT * FROM t9;
+
+--echo # Scenario 7: Test explicit rename table won't change the counter
+
+set global innodb_flush_log_at_trx_commit=1;
+
+RENAME TABLE t9 to t19;
+INSERT INTO t19 VALUES(0), (0);
+SELECT * FROM t19;
+DELETE FROM t19 WHERE a = 4;
+
+--source include/kill_and_restart_mysqld.inc
+
+RENAME TABLE t19 to t9;
+INSERT INTO t9 VALUES(0), (0);
+SELECT * FROM t9;
+
+TRUNCATE TABLE t9;
+
+INSERT INTO t9 VALUES(0), (0);
+SELECT * FROM t9;
+
+--echo # Scenario 8: Test ALTER TABLE operations
+
+INSERT INTO t3 VALUES(0), (0), (100), (200), (1000);
+SELECT * FROM t3;
+DELETE FROM t3 WHERE a > 300;
+SELECT MAX(a) AS `Expect 200` FROM t3;
+--echo # This will not change the counter to 150, but to 201, which is the next
+--echo # of current max counter in the table
+ALTER TABLE t3 AUTO_INCREMENT = 150;
+SHOW CREATE TABLE t3;
+INSERT INTO t3 VALUES(0);
+SELECT MAX(a) AS `Expect 201` FROM t3;
+--echo # This will change the counter to 500, which is bigger than any counter
+--echo # in the table
+ALTER TABLE t3 AUTO_INCREMENT = 500;
+SHOW CREATE TABLE t3;
+INSERT INTO t3 VALUES(0);
+SELECT MAX(a) AS `Expect 500` FROM t3;
+
+TRUNCATE TABLE t3;
+ALTER TABLE t3 AUTO_INCREMENT = 100;
+SHOW CREATE TABLE t3;
+INSERT INTO t3 VALUES(0), (0);
+SELECT * FROM t3;
+
+INSERT INTO t3 VALUES(150), (180);
+UPDATE t3 SET a = 200 WHERE a = 150;
+INSERT INTO t3 VALUES(220);
+--echo # This still fails to set to 120, but just 221
+ALTER TABLE t3 AUTO_INCREMENT = 120;
+SHOW CREATE TABLE t3;
+INSERT INTO t3 VALUES(0);
+SELECT MAX(a) AS `Expect 221` FROM t3;
+
+DELETE FROM t3 WHERE a > 120;
+
+ALTER TABLE t3 AUTO_INCREMENT = 120;
+SHOW CREATE TABLE t3;
+
+--echo # MDEV-6076: Test adding an AUTO_INCREMENT COLUMN
+CREATE TABLE mdev6076a (b INT) ENGINE=InnoDB;
+INSERT INTO mdev6076a VALUES(2),(1);
+CREATE TABLE mdev6076b (b INT) ENGINE=InnoDB;
+INSERT INTO mdev6076b VALUES(2),(1);
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, LOCK=NONE;
+ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE;
+ALTER TABLE mdev6076b ADD COLUMN a SERIAL FIRST, AUTO_INCREMENT=100,
+ALGORITHM=INPLACE;
+
+--source include/kill_and_restart_mysqld.inc
+
+INSERT INTO t3 VALUES(0);
+SELECT MAX(a) AS `Expect 120` FROM t3;
+
+INSERT INTO mdev6076a SET b=0;
+SELECT * FROM mdev6076a;
+INSERT INTO mdev6076b SET b=0;
+SELECT * FROM mdev6076b;
+DROP TABLE mdev6076a, mdev6076b;
+
+set global innodb_flush_log_at_trx_commit=1;
+
+INSERT INTO t3 VALUES(0), (0), (200), (210);
+
+--echo # Test the different algorithms in ALTER TABLE
+
+--let $template = t3
+--let $algorithm = INPLACE
+--let $table = t_inplace
+--source suite/innodb/include/autoinc_persist_alter.inc
+--let $algorithm = COPY
+--let $table = t_copy
+--source suite/innodb/include/autoinc_persist_alter.inc
+
+--echo # Scenario 9: Test the sql_mode = NO_AUTO_VALUE_ON_ZERO
+
+CREATE TABLE t30 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, key(b)) ENGINE = InnoDB;
+
+set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
+
+INSERT INTO t30 VALUES(NULL, 1), (200, 2), (0, 3);
+INSERT INTO t30(b) VALUES(4), (5), (6), (7);
+SELECT * FROM t30 ORDER BY b;
+ALTER TABLE t30 MODIFY b MEDIUMINT;
+SELECT * FROM t30 ORDER BY b;
+
+set global innodb_flush_log_at_trx_commit=1;
+
+CREATE TABLE t31 (a INT) ENGINE = InnoDB;
+INSERT INTO t31 VALUES(1), (2);
+ALTER TABLE t31 ADD b INT AUTO_INCREMENT PRIMARY KEY;
+INSERT INTO t31 VALUES(3, 0), (4, NULL), (5, NULL);
+--error ER_DUP_ENTRY
+INSERT INTO t31 VALUES(6, 0);
+SELECT * FROM t31;
+
+--source include/kill_and_restart_mysqld.inc
+
+--echo # This will not insert 0
+INSERT INTO t31(a) VALUES(6), (0);
+SELECT * FROM t31;
+DROP TABLE t31;
+
+set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
+
+DELETE FROM t30 WHERE a = 0;
+UPDATE t30 set a = 0 where b = 5;
+SELECT * FROM t30 ORDER BY b;
+DELETE FROM t30 WHERE a = 0;
+
+UPDATE t30 SET a = NULL WHERE b = 6;
+UPDATE t30 SET a = 300 WHERE b = 7;
+
+SELECT * FROM t30 ORDER BY b;
+
+SET SQL_MODE = 0;
+
+--echo # Scenario 10: Rollback would not rollback the counter
+CREATE TABLE t32 (
+a BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+
+INSERT INTO t32 VALUES(0), (0);
+
+set global innodb_flush_log_at_trx_commit=1;
+
+START TRANSACTION;
+INSERT INTO t32 VALUES(0), (0);
+SELECT MAX(a) AS `Expect 4` FROM t32;
+DELETE FROM t32 WHERE a >= 2;
+ROLLBACK;
+
+--source include/kill_and_restart_mysqld.inc
+
+SELECT MAX(a) AS `Expect 2` FROM t32;
+INSERT INTO t32 VALUES(0), (0);
+SELECT MAX(a) AS `Expect 6` FROM t32;
+
+--echo # Scenario 11: Test duplicate primary key/secondary key will not stop
+--echo # increasing the counter
+
+CREATE TABLE t33 (
+a BIGINT NOT NULL PRIMARY KEY,
+b BIGINT NOT NULL AUTO_INCREMENT,
+KEY(b)) ENGINE = InnoDB;
+
+INSERT INTO t33 VALUES(1, NULL);
+INSERT INTO t33 VALUES(2, NULL);
+--error ER_DUP_ENTRY
+INSERT INTO t33 VALUES(2, NULL);
+
+INSERT INTO t33 VALUES(3, NULL);
+SELECT MAX(b) AS `Expect 4` FROM t33;
+
+TRUNCATE TABLE t33;
+
+INSERT INTO t33 VALUES(1, NULL);
+INSERT INTO t33 VALUES(2, NULL);
+
+set global innodb_flush_log_at_trx_commit=1;
+
+START TRANSACTION;
+UPDATE t33 SET a = 10 WHERE a = 1;
+--error ER_DUP_ENTRY
+INSERT INTO t33 VALUES(2, NULL);
+COMMIT;
+
+--source include/kill_and_restart_mysqld.inc
+
+INSERT INTO t33 VALUES(3, NULL);
+SELECT MAX(b) AS `Expect 4` FROM t33;
+
+DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t30, t32, t33;
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-44030-master.opt b/mysql-test/suite/innodb/t/innodb-autoinc-44030-master.opt
deleted file mode 100644
index 303ec1be1d0..00000000000
--- a/mysql-test/suite/innodb/t/innodb-autoinc-44030-master.opt
+++ /dev/null
@@ -1,3 +0,0 @@
---default-storage-engine=MyISAM
---innodb-strict-mode=0
---innodb-file-per-table=0
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-44030.test b/mysql-test/suite/innodb/t/innodb-autoinc-44030.test
index 256e7d838ea..61c56127351 100644
--- a/mysql-test/suite/innodb/t/innodb-autoinc-44030.test
+++ b/mysql-test/suite/innodb/t/innodb-autoinc-44030.test
@@ -2,27 +2,24 @@
 # embedded server does not support restarting
 -- source include/not_embedded.inc
 
-#
-# 44030: Error: (1500) Couldn't read the MAX(ID) autoinc value from
+# Before MDEV-6076 Persistent AUTO_INCREMENT for InnoDB
+# this was a test for
+# Bug #44030: Error: (1500) Couldn't read the MAX(ID) autoinc value from
 # the index (PRIMARY)
 # This test requires a restart of the server
 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
 CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
 INSERT INTO t1 VALUES (null);
 INSERT INTO t1 VALUES (null);
+--enable_info
 ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT;
+--disable_info
 SELECT * FROM t1;
-# Restart the server
 -- source include/restart_mysqld.inc
-# The MySQL and InnoDB data dictionaries should now be out of sync.
-# The select should print message to the error log
-SELECT * FROM t1;
-# MySQL have made a change (http://lists.mysql.com/commits/75268) that no
-# longer results in the two data dictionaries being out of sync. If they
-# revert their changes then this check for ER_AUTOINC_READ_FAILED will need
-# to be enabled. Also, see http://bugs.mysql.com/bug.php?id=47621.
-#-- error ER_AUTOINC_READ_FAILED,1467
+SHOW CREATE TABLE t1;
 INSERT INTO t1 VALUES(null);
+SELECT * FROM t1;
+
 # Before WL#5534, the following statement would copy the table,
 # and effectively set AUTO_INCREMENT to 4, because while copying
 # it would write values 1,2,3 to the column.
diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test
index dfe1a0b4995..e5e4b45a861 100644
--- a/mysql-test/suite/innodb/t/innodb.test
+++ b/mysql-test/suite/innodb/t/innodb.test
@@ -1463,15 +1463,18 @@ select * from t1;
 drop table t1;
 
 #
-# Test that update does not change internal auto-increment value
+# Test that update does change internal auto-increment value
 #
 
 create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
 insert into t1 (val) values (1);
 update t1 set a=2 where a=1;
-# We should get the following error because InnoDB does not update the counter
+# This should insert 3, since the counter has been updated to 2 already
+insert into t1 (val) values (3);
+select * from t1;
+# We should get the following error because InnoDB does update the counter
 --error ER_DUP_ENTRY
-insert into t1 (val) values (1);
+insert into t1 values (2, 2);
 select * from t1;
 drop table t1;
 #