Debian Codegraph @

mysql-test/suite/innodb/t/alter_persistent_autoinc.test

raw
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;