Debian Codegraph @

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

raw
1 # Test for BUG #35127747: The n_rows value becomes 0 when repeated altering with algorithm=copy within 10 seconds.
2
3 --echo # Test setup.
4
5 CREATE TABLE t1 (
6 c1 INT PRIMARY KEY,
7 c2 VARCHAR(50),
8 c3 VARCHAR(50),
9 c4 VARCHAR(50)
10 );
11
12 INSERT INTO t1 (c1, c2, c3, c4)
13 VALUES
14 (1, 'AAA_2', 'AAA_3', 'AAA_4'),
15 (2, 'BBB_2', 'BBB_3', 'BBB_4'),
16 (3, 'CCC_2', 'CCC_3', 'CCC_4'),
17 (4, 'DDD_2', 'DDD_3', 'DDD_4'),
18 (5, 'EEE_2', 'EEE_3', 'EEE_4');
19
20
21 --echo # Start test.
22 ALTER TABLE t1 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
23 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
24
25 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
26 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
27
28 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
29 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
30
31 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
32 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
33
34 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = COPY;
35 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
36
37 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c4), ALGORITHM=COPY;
38 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
39
40 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (c1), ALGORITHM=INPLACE;
41 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
42
43 ALTER TABLE t1 ADD COLUMN c5 VARCHAR(15), ALGORITHM = INPLACE;
44 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
45
46 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM = INPLACE;
47 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name IN ('t1') ;
48
49 CREATE TABLE t2 (
50 c1 INT PRIMARY KEY,
51 c2 VARCHAR(50),
52 c3 VARCHAR(50),
53 c4 VARCHAR(50)
54 ) PARTITION BY RANGE (c1) (
55 PARTITION p1 VALUES LESS THAN (6),
56 PARTITION p2 VALUES LESS THAN (11),
57 PARTITION p3 VALUES LESS THAN (16),
58 PARTITION p4 VALUES LESS THAN (21)
59 );
60 INSERT INTO t2 (c1, c2, c3, c4)
61 VALUES
62 (1, 'AAA_2', 'AAA_3', 'AAA_4'),
63 (2, 'BBB_2', 'BBB_3', 'BBB_4'),
64 (3, 'CCC_2', 'CCC_3', 'CCC_4'),
65 (4, 'DDD_2', 'DDD_3', 'DDD_4'),
66 (5, 'EEE_2', 'EEE_3', 'EEE_4'),
67 (6, 'FFF_2', 'DDD_3', 'DDD_4'),
68 (7, 'GGG_2', 'DDD_3', 'DDD_4'),
69 (8, 'HHH_2', 'DDD_3', 'DDD_4'),
70 (9, 'III_2', 'DDD_3', 'DDD_4'),
71 (10, 'JJJ_2', 'DDD_3', 'DDD_4'),
72 (13, 'KKK_2', 'DDD_3', 'DDD_4'),
73 (20, 'LLL_2', 'DDD_3', 'DDD_4');
74
75 ALTER TABLE t2 CONVERT TO CHARACTER SET 'utf8mb4',ALGORITHM=COPY;
76 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
77
78 ALTER TABLE t2 ADD COLUMN c5 VARCHAR(15), ALGORITHM = COPY;
79 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
80
81 ALTER TABLE t2 DROP COLUMN c5, ALGORITHM = COPY;
82 SELECT n_rows, database_name, table_name FROM mysql.innodb_table_stats WHERE table_name LIKE '%t2%';
83
84 --echo # Test Cleanup.
85 DROP TABLE t1;
86 DROP TABLE t2;