| 1 |
# Test the ability to remove AUTO_INCREMENT attribute |
| 2 |
--source include/have_innodb.inc |
| 3 |
--source include/have_partition.inc |
| 4 |
|
| 5 |
CREATE TABLE t1 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; |
| 6 |
ALTER TABLE t1 MODIFY c INT NULL, ALGORITHM=INSTANT; |
| 7 |
INSERT INTO t1 SET c=1; |
| 8 |
|
| 9 |
CREATE TABLE t2 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; |
| 10 |
ALTER TABLE t2 MODIFY c INT NULL, FORCE, ALGORITHM=INPLACE; |
| 11 |
INSERT INTO t2 SET c=1; |
| 12 |
|
| 13 |
CREATE TABLE t3 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB; |
| 14 |
ALTER TABLE t3 MODIFY c INT NULL, ALGORITHM=INPLACE; |
| 15 |
INSERT INTO t3 SET c=1; |
| 16 |
|
| 17 |
CREATE TABLE t4 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT) ENGINE=InnoDB; |
| 18 |
ALTER TABLE t4 MODIFY c1 INT NULL, CHANGE COLUMN c2 c3 INT, ALGORITHM=INPLACE; |
| 19 |
INSERT INTO t4 SET c1=1; |
| 20 |
|
| 21 |
CREATE TABLE t5 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT, c3 INTEGER GENERATED ALWAYS AS (c2)) ENGINE=InnoDB; |
| 22 |
# ha_innobase::commit_inplace_alter_table() should invoke innobase_reload_table() |
| 23 |
ALTER TABLE t5 MODIFY c1 INT NULL, MODIFY COLUMN c2 INT FIRST, ALGORITHM=INPLACE; |
| 24 |
INSERT INTO t5 SET c1=1; |
| 25 |
|
| 26 |
CREATE TABLE t6 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB |
| 27 |
PARTITION BY LIST(c) ( |
| 28 |
PARTITION p1 VALUES IN (1), |
| 29 |
PARTITION p2 VALUES IN (2) |
| 30 |
); |
| 31 |
ALTER TABLE t6 MODIFY c INT NULL, ALGORITHM=INSTANT; |
| 32 |
INSERT INTO t6 SET c=1; |
| 33 |
INSERT INTO t6 SET c=2; |
| 34 |
|
| 35 |
CREATE TABLE t7 (c INT AUTO_INCREMENT NULL UNIQUE) ENGINE=InnoDB |
| 36 |
PARTITION BY LIST(c) ( |
| 37 |
PARTITION p1 VALUES IN (1), |
| 38 |
PARTITION p2 VALUES IN (2) |
| 39 |
); |
| 40 |
ALTER TABLE t7 MODIFY c INT NULL, ALGORITHM=INPLACE; |
| 41 |
INSERT INTO t7 SET c=1; |
| 42 |
INSERT INTO t7 SET c=2; |
| 43 |
|
| 44 |
CREATE TABLE t8 (c1 INT AUTO_INCREMENT NULL UNIQUE, c2 INT) ENGINE=InnoDB |
| 45 |
PARTITION BY LIST(c1) ( |
| 46 |
PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); |
| 47 |
ALTER TABLE t8 MODIFY c1 INT NULL, CHANGE COLUMN c2 c3 INT, ALGORITHM=INPLACE; |
| 48 |
INSERT INTO t8 SET c1=1; |
| 49 |
INSERT INTO t8 SET c1=2; |
| 50 |
|
| 51 |
FLUSH TABLES t1, t2, t3, t4, t5, t6, t7, t8 FOR EXPORT; |
| 52 |
|
| 53 |
--let INNODB_PAGE_SIZE=`select @@innodb_page_size` |
| 54 |
--let MYSQLD_DATADIR = `SELECT @@datadir` |
| 55 |
|
| 56 |
--perl |
| 57 |
my $ps= $ENV{INNODB_PAGE_SIZE}; |
| 58 |
my $PAGE_HEADER = 38; |
| 59 |
my $PAGE_ROOT_AUTO_INC = 18; |
| 60 |
print "AUTO_INCREMENT not partitioned: "; |
| 61 |
for (my $i = 1; $i <= 5; ++$i) { |
| 62 |
my $autoinc= read_autoinc("$ENV{MYSQLD_DATADIR}/test/t$i.ibd"); |
| 63 |
print "$autoinc "; |
| 64 |
} |
| 65 |
print "\n"; |
| 66 |
print "AUTO_INCREMENT partitioned: "; |
| 67 |
for (my $i = 6; $i <= 8; ++$i) { |
| 68 |
my $p1= read_autoinc("$ENV{MYSQLD_DATADIR}/test/t$i#P#p1.ibd"); |
| 69 |
my $p2= read_autoinc("$ENV{MYSQLD_DATADIR}/test/t$i#P#p2.ibd"); |
| 70 |
print "($p1, $p2) "; |
| 71 |
} |
| 72 |
print "\n"; |
| 73 |
sub read_autoinc { |
| 74 |
my $file= shift; |
| 75 |
open(FILE, "<", $file) || die "Unable to open $file\n"; |
| 76 |
sysseek(FILE, 3*$ps + $PAGE_HEADER + $PAGE_ROOT_AUTO_INC + 4, 0) |
| 77 |
|| die "Unable to seek $file\n"; |
| 78 |
die "Unable to read $file\n" unless sysread(FILE, $_, 4) == 4; |
| 79 |
my $t1=unpack("N",$_); |
| 80 |
close(FILE); |
| 81 |
return $t1; |
| 82 |
} |
| 83 |
EOF |
| 84 |
|
| 85 |
UNLOCK TABLES; |
| 86 |
DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8; |