Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_partition.inc
3
4 CREATE TABLE t1 (
5 c1 INT PRIMARY KEY,
6 c2 VARCHAR(50),
7 c3 VARCHAR(50),
8 c4 VARCHAR(50))ENGINE=InnoDB;
9
10 INSERT INTO t1 VALUES(1, 'AAA_2', 'AAA_3', 'AAA_4'),
11 (2, 'BBB_2', 'BBB_3', 'BBB_4'),
12 (3, 'CCC_2', 'CCC_3', 'CCC_4'),
13 (4, 'DDD_2', 'DDD_3', 'DDD_4'),
14 (5, 'EEE_2', 'EEE_3', 'EEE_4');
15
16 ALTER TABLE t1 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
17 SELECT n_rows, database_name, lower(table_name)
18 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
19
20 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
21 SELECT n_rows, database_name, lower(table_name)
22 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
23
24 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
25 SELECT n_rows, database_name, lower(table_name)
26 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
27
28 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
29 SELECT n_rows, database_name, lower(table_name)
30 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
31
32 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
33 SELECT n_rows, database_name, lower(table_name)
34 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
35
36 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c4), ALGORITHM=COPY;
37 SELECT n_rows, database_name, lower(table_name)
38 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
39
40 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c1), ALGORITHM = COPY;
41 SELECT n_rows, database_name, lower(table_name)
42 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
43
44 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = INPLACE;
45 SELECT n_rows, database_name, lower(table_name)
46 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
47
48 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = INPLACE;
49 SELECT n_rows, database_name, lower(table_name)
50 FROM mysql.innodb_table_stats WHERE table_name IN ('t1');
51
52 CREATE TABLE t2 (
53 c1 INT PRIMARY KEY,
54 c2 VARCHAR(50),
55 c3 VARCHAR(50),
56 c4 VARCHAR(50)
57 ) ENGINE=InnoDB PARTITION BY RANGE (c1) (
58 PARTITION p1 VALUES LESS THAN (6),
59 PARTITION p2 VALUES LESS THAN (11),
60 PARTITION p3 VALUES LESS THAN (16),
61 PARTITION p4 VALUES LESS THAN (21)
62 );
63 INSERT INTO t2 VALUES(1, 'AAA_2', 'AAA_3', 'AAA_4'),
64 (2, 'BBB_2', 'BBB_3', 'BBB_4'),
65 (3, 'CCC_2', 'CCC_3', 'CCC_4'),
66 (4, 'DDD_2', 'DDD_3', 'DDD_4'),
67 (5, 'EEE_2', 'EEE_3', 'EEE_4'),
68 (6, 'FFF_2', 'DDD_3', 'DDD_4'),
69 (7, 'GGG_2', 'DDD_3', 'DDD_4'),
70 (8, 'HHH_2', 'DDD_3', 'DDD_4'),
71 (9, 'III_2', 'DDD_3', 'DDD_4'),
72 (10, 'JJJ_2', 'DDD_3', 'DDD_4'),
73 (13, 'KKK_2', 'DDD_3', 'DDD_4'),
74 (20, 'LLL_2', 'DDD_3', 'DDD_4');
75
76 ALTER TABLE t2 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
77 SELECT n_rows, database_name, lower(table_name)
78 FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
79
80 ALTER TABLE t2 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
81 SELECT n_rows, database_name, lower(table_name)
82 FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
83
84 ALTER TABLE t2 DROP COLUMN c5, ALGORITHM = COPY;
85 SELECT n_rows, database_name, lower(table_name)
86 FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
87
88 --echo # Test Cleanup.
89 DROP TABLE t1;
90 DROP TABLE t2;
91
92 --echo #
93 --echo # MDEV-38667 Assertion in diagnostics area on DDL stats timeout
94 --echo #
95 CREATE TABLE t ENGINE=InnoDB AS SELECT * FROM mysql.innodb_table_stats;
96 DROP TABLE t;
97
98 --echo #
99 --echo # MDEV-38882 Assertion in diagnostics area on DDL stats
100 --echo #
101 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
102 CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=InnoDB;
103 --connect (con1,localhost,root,,)
104 --send ALTER TABLE t1 NOWAIT FORCE, ALGORITHM=COPY;
105 --connection default
106 ALTER TABLE t2 FORCE, ALGORITHM=COPY;
107 --disable_warnings
108 --connection con1
109 --error 0,ER_LOCK_WAIT_TIMEOUT
110 --reap
111 --enable_warnings
112 DROP TABLE t1, t2;