diff options
author | Vlad Lesin <vlad_lesin@mail.ru> | 2022-04-12 13:39:04 +0300 |
---|---|---|
committer | Vlad Lesin <vlad_lesin@mail.ru> | 2022-04-21 15:23:21 +0300 |
commit | 188aae65e4fa43b73ee1af6ce26724c3dca61380 (patch) | |
tree | f638e47a41d5e15bd6d8f8abf488c9a9fcf46521 | |
parent | aec856073df12e95b68667587bfd8e469b60e7d4 (diff) | |
download | mariadb-git-188aae65e4fa43b73ee1af6ce26724c3dca61380.tar.gz |
MDEV-26224 InnoDB fails to remove AUTO_INCREMENT attribute
Reset dict_table_t::persistent_autoinc when inplace alter table is
committed successfully.
-rw-r--r-- | mysql-test/suite/innodb/r/alter_persistent_autoinc.result | 42 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/alter_persistent_autoinc.test | 86 | ||||
-rw-r--r-- | sql/ha_partition.cc | 11 | ||||
-rw-r--r-- | storage/innobase/handler/handler0alter.cc | 16 |
4 files changed, 153 insertions, 2 deletions
diff --git a/mysql-test/suite/innodb/r/alter_persistent_autoinc.result b/mysql-test/suite/innodb/r/alter_persistent_autoinc.result new file mode 100644 index 00000000000..52ac4d83ad9 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_persistent_autoinc.result @@ -0,0 +1,42 @@ +CREATE TABLE t1 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; +ALTER TABLE t1 MODIFY c INT NULL, ALGORITHM=INSTANT; +INSERT INTO t1 SET c=1; +CREATE TABLE t2 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; +ALTER TABLE t2 MODIFY c INT NULL, FORCE, ALGORITHM=INPLACE; +INSERT INTO t2 SET c=1; +CREATE TABLE t3 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; +ALTER TABLE t3 MODIFY c INT NULL, ALGORITHM=INPLACE; +INSERT INTO t3 SET c=1; +CREATE TABLE t4 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT) ENGINE=InnoDB; +ALTER TABLE t4 MODIFY c1 INT NULL, CHANGE COLUMN c2 c3 INT, ALGORITHM=INPLACE; +INSERT INTO t4 SET c1=1; +CREATE TABLE t5 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT, c3 INTEGER GENERATED ALWAYS AS (c2)) ENGINE=InnoDB; +ALTER TABLE t5 MODIFY c1 INT NULL, MODIFY COLUMN c2 INT FIRST, ALGORITHM=INPLACE; +INSERT INTO t5 SET c1=1; +CREATE TABLE t6 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB +PARTITION BY LIST(c) ( +PARTITION p1 VALUES IN (1), +PARTITION p2 VALUES IN (2) +); +ALTER TABLE t6 MODIFY c INT NULL, ALGORITHM=INSTANT; +INSERT INTO t6 SET c=1; +INSERT INTO t6 SET c=2; +CREATE TABLE t7 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB +PARTITION BY LIST(c) ( +PARTITION p1 VALUES IN (1), +PARTITION p2 VALUES IN (2) +); +ALTER TABLE t7 MODIFY c INT NULL, ALGORITHM=INPLACE; +INSERT INTO t7 SET c=1; +INSERT INTO t7 SET c=2; +CREATE TABLE t8 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT) ENGINE=InnoDB +PARTITION BY LIST(c1) ( +PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); +ALTER TABLE t8 MODIFY c1 INT NULL, CHANGE COLUMN c2 c3 INT, ALGORITHM=INPLACE; +INSERT INTO t8 SET c1=1; +INSERT INTO t8 SET c1=2; +FLUSH TABLES t1, t2, t3, t4, t5, t6, t7, t8 FOR EXPORT; +AUTO_INCREMENT not partitioned: 0 0 0 0 0 +AUTO_INCREMENT partitioned: (0, 0) (0, 0) (0, 0) +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8; diff --git a/mysql-test/suite/innodb/t/alter_persistent_autoinc.test b/mysql-test/suite/innodb/t/alter_persistent_autoinc.test new file mode 100644 index 00000000000..dcaea32700c --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_persistent_autoinc.test @@ -0,0 +1,86 @@ +# Test the ability to remove AUTO_INCREMENT attribute +--source include/have_innodb.inc +--source include/have_partition.inc + +CREATE TABLE t1 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; +ALTER TABLE t1 MODIFY c INT NULL, ALGORITHM=INSTANT; +INSERT INTO t1 SET c=1; + +CREATE TABLE t2 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; +ALTER TABLE t2 MODIFY c INT NULL, FORCE, ALGORITHM=INPLACE; +INSERT INTO t2 SET c=1; + +CREATE TABLE t3 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; +ALTER TABLE t3 MODIFY c INT NULL, ALGORITHM=INPLACE; +INSERT INTO t3 SET c=1; + +CREATE TABLE t4 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT) ENGINE=InnoDB; +ALTER TABLE t4 MODIFY c1 INT NULL, CHANGE COLUMN c2 c3 INT, ALGORITHM=INPLACE; +INSERT INTO t4 SET c1=1; + +CREATE TABLE t5 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT, c3 INTEGER GENERATED ALWAYS AS (c2)) ENGINE=InnoDB; +# ha_innobase::commit_inplace_alter_table() should invoke innobase_reload_table() +ALTER TABLE t5 MODIFY c1 INT NULL, MODIFY COLUMN c2 INT FIRST, ALGORITHM=INPLACE; +INSERT INTO t5 SET c1=1; + +CREATE TABLE t6 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB + PARTITION BY LIST(c) ( + PARTITION p1 VALUES IN (1), + PARTITION p2 VALUES IN (2) + ); +ALTER TABLE t6 MODIFY c INT NULL, ALGORITHM=INSTANT; +INSERT INTO t6 SET c=1; +INSERT INTO t6 SET c=2; + +CREATE TABLE t7 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB + PARTITION BY LIST(c) ( + PARTITION p1 VALUES IN (1), + PARTITION p2 VALUES IN (2) + ); +ALTER TABLE t7 MODIFY c INT NULL, ALGORITHM=INPLACE; +INSERT INTO t7 SET c=1; +INSERT INTO t7 SET c=2; + +CREATE TABLE t8 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT) ENGINE=InnoDB + PARTITION BY LIST(c1) ( + PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); +ALTER TABLE t8 MODIFY c1 INT NULL, CHANGE COLUMN c2 c3 INT, ALGORITHM=INPLACE; +INSERT INTO t8 SET c1=1; +INSERT INTO t8 SET c1=2; + +FLUSH TABLES t1, t2, t3, t4, t5, t6, t7, t8 FOR EXPORT; + +--let INNODB_PAGE_SIZE=`select @@innodb_page_size` +--let MYSQLD_DATADIR = `SELECT @@datadir` + +--perl +my $ps= $ENV{INNODB_PAGE_SIZE}; +my $PAGE_HEADER = 38; +my $PAGE_ROOT_AUTO_INC = 18; +print "AUTO_INCREMENT not partitioned: "; +for (my $i = 1; $i <= 5; ++$i) { + my $autoinc= read_autoinc("$ENV{MYSQLD_DATADIR}/test/t$i.ibd"); + print "$autoinc "; +} +print "\n"; +print "AUTO_INCREMENT partitioned: "; +for (my $i = 6; $i <= 8; ++$i) { + my $p1= read_autoinc("$ENV{MYSQLD_DATADIR}/test/t$i#P#p1.ibd"); + my $p2= read_autoinc("$ENV{MYSQLD_DATADIR}/test/t$i#P#p2.ibd"); + print "($p1, $p2) "; +} +print "\n"; +sub read_autoinc { + my $file= shift; + open(FILE, "<", $file) || die "Unable to open $file\n"; + sysseek(FILE, 3*$ps + $PAGE_HEADER + $PAGE_ROOT_AUTO_INC + 4, 0) + || die "Unable to seek $file\n"; + die "Unable to read $file\n" unless sysread(FILE, $_, 4) == 4; + my $t1=unpack("N",$_); + close(FILE); + return $t1; +} +EOF + +UNLOCK TABLES; +DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index ac6f4696895..0fcf966f71b 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -10437,7 +10437,16 @@ bool ha_partition::commit_inplace_alter_table(TABLE *altered_table, Loop over all other partitions as to follow the protocol! */ uint i; - DBUG_ASSERT(0); + /* + InnoDB does not set ha_alter_info->group_commit_ctx to NULL in the + case if autoincrement attribute is necessary to reset for all + partitions for INNOBASE_INPLACE_IGNORE handler flags. It does not + affect durability, because it is solely about updating the InnoDB data + dictionary caches (one InnoDB dict_table_t per partition or + sub-partition). + */ + DBUG_ASSERT(table->found_next_number_field + && !altered_table->found_next_number_field); for (i= 1; i < m_tot_parts; i++) { ha_alter_info->handler_ctx= part_inplace_ctx->handler_ctx_array[i]; diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index e79d9d67dbf..91a5fd3ca4a 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -10711,6 +10711,10 @@ commit_cache_norebuild( : NULL; DBUG_ASSERT((ctx->new_table->fts == NULL) == (ctx->new_table->fts_doc_id_index == NULL)); + if (table->found_next_number_field + && !altered_table->found_next_number_field) { + ctx->prebuilt->table->persistent_autoinc = 0; + } DBUG_RETURN(found); } @@ -11035,7 +11039,15 @@ ha_innobase::commit_inplace_alter_table( if (!(ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE)) { DBUG_ASSERT(!ctx0); MONITOR_ATOMIC_DEC(MONITOR_PENDING_ALTER_TABLE); - ha_alter_info->group_commit_ctx = NULL; + if (table->found_next_number_field + && !altered_table->found_next_number_field) { + m_prebuilt->table->persistent_autoinc = 0; + /* Don't reset ha_alter_info->group_commit_ctx to make + partitions engine to call this function for all + partitions. */ + } + else + ha_alter_info->group_commit_ctx = NULL; DBUG_RETURN(false); } @@ -11543,6 +11555,8 @@ foreign_fail: row_mysql_unlock_data_dictionary(trx); trx->free(); MONITOR_ATOMIC_DEC(MONITOR_PENDING_ALTER_TABLE); + /* There is no need to reset dict_table_t::persistent_autoinc + as the table is reloaded */ DBUG_RETURN(false); } |