Debian Codegraph @

mysql-test/suite/innodb/r/default_row_format_alter.result

raw
1 SET @row_format = @@GLOBAL.innodb_default_row_format;
2 ####################################
3 # Check if table rebuilding alter isn't affect if table is created
4 # with explicit row_format
5 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=INNODB;
6 INSERT INTO t1 VALUES (1, 'abc');
7 SHOW TABLE STATUS LIKE 't1';
8 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
9 t1 InnoDB # Compact # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL row_format=COMPACT 0 N
10 SET GLOBAL innodb_default_row_format=DYNAMIC;
11 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
12 # Here we expect COMPACT because it was explicitly specified at CREATE
13 SHOW TABLE STATUS LIKE 't1';
14 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
15 t1 InnoDB # Compact # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL row_format=COMPACT 0 N
16 DROP TABLE t1;
17 ####################################
18 # Check if table rebuilding alter is affected when there is no
19 # row_format specified at CREATE TABLE.
20 SET GLOBAL innodb_default_row_format = COMPACT;
21 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
22 INSERT INTO t1 VALUES (1, 'abc');
23 SHOW TABLE STATUS LIKE 't1';
24 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
25 t1 InnoDB # Compact # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL 0 N
26 CREATE TABLE t2 (b VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL) ENGINE=InnoDB;
27 SET GLOBAL innodb_default_row_format = DYNAMIC;
28 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
29 # Here we expect DYNAMIC because there is no explicit ROW_FORMAT and the
30 # default_row_format is changed to DYNAMIC just before ALTER
31 SHOW TABLE STATUS LIKE 't1';
32 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
33 t1 InnoDB # Dynamic # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL 0 N
34 DROP TABLE t1;
35 ALTER TABLE t2 ADD INDEX(b);
36 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
37 ALTER TABLE t2 FORCE, ADD INDEX(b);
38 DROP TABLE t2;
39 ####################################
40 # Check the row_format effect on ALTER, ALGORITHM=COPY
41 SET GLOBAL innodb_default_row_format = REDUNDANT;
42 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
43 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
44 SHOW TABLE STATUS LIKE 't1';
45 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
46 t1 InnoDB # Redundant # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL 0 N
47 CREATE TABLE t2 (b VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL) ENGINE=InnoDB;
48 SET GLOBAL innoDB_default_row_format = COMPACT;
49 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
50 # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table isn't
51 # created with explicit row_format, so we expect ROW_FORMAT=COMPACT
52 SHOW TABLE STATUS LIKE 't1';
53 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
54 t1 InnoDB # Compact # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL 0 N
55 DROP TABLE t1;
56 ALTER TABLE t2 ADD INDEX(b);
57 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
58 ALTER TABLE t2 FORCE, ADD INDEX(b);
59 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
60 SET GLOBAL innodb_default_row_format = DYNAMIC;
61 ALTER TABLE t2 ADD INDEX(b);
62 ERROR HY000: Index column size too large. The maximum column size is 767 bytes
63 ALTER TABLE t2 FORCE, ADD INDEX(b);
64 DROP TABLE t2;
65
66 ###################################
67 # Check the row_format effect on ALTER, ALGORITHM=COPY on
68 # create table with explicit row_format
69 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=INNODB;
70 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
71 SHOW TABLE STATUS LIKE 't1';
72 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
73 t1 InnoDB # Redundant # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL row_format=REDUNDANT 0 N
74 SET GLOBAL innoDB_default_row_format = COMPACT;
75 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
76 # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table is
77 # created with explicit row_format, so we expect original
78 # ROW_FORMAT=REDUNDANT
79 SHOW TABLE STATUS LIKE 't1';
80 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
81 t1 InnoDB # Redundant # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL row_format=REDUNDANT 0 N
82 DROP TABLE t1;
83
84 ##################################
85 # Check row_format on ALTER ALGORITHM=INPLACE
86 SET GLOBAL innodb_default_row_format=COMPACT;
87 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT, KEY k1(b(10))) ENGINE=INNODB;
88 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
89 SHOW TABLE STATUS LIKE 't1';
90 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
91 t1 InnoDB # Compact # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL 0 N
92 SET GLOBAL innodb_default_row_format=DYNAMIC;
93 ALTER TABLE t1 DROP INDEX k1;
94 # Because it is in-place operation, there is no rebuild, so the
95 # original format has to be retained.
96 SHOW TABLE STATUS LIKE 't1';
97 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
98 t1 InnoDB # Compact # # # # # # NULL # # NULL utf8mb4_uca1400_ai_ci NULL 0 N
99 DROP TABLE t1;
100 #
101 # MDEV-24758 heap-use-after-poison in innobase_add_instant_try/rec_copy
102 #
103 CREATE TABLE t1 (pk INT PRIMARY KEY) CHARACTER SET utf8 ENGINE=InnoDB;
104 INSERT INTO t1 VALUES (1);
105 SET GLOBAL innodb_default_row_format = REDUNDANT;
106 ALTER TABLE t1 ADD a CHAR(8) DEFAULT '';
107 DROP TABLE t1;
108 SET GLOBAL innodb_default_row_format = @row_format;
109 # End of 10.3 tests
110 #
111 # MDEV-23295 Assertion fields[i].same(instant.fields[i]) failed
112 #
113 SET GLOBAL innodb_default_row_format = @row_format;
114 CREATE TABLE t1 (a char(8)) ENGINE=InnoDB DEFAULT CHARSET utf8;
115 SET GLOBAL innodb_default_row_format= COMPACT;
116 ALTER TABLE t1 ADD b INT;
117 SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
118 ROW_FORMAT
119 Dynamic
120 DROP TABLE t1;
121 #
122 # MDEV-23345 Assertion not_redundant() == old.not_redundant() failed
123 #
124 SET GLOBAL innodb_default_row_format = @row_format;
125 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b)) ENGINE=InnoDB;
126 SET GLOBAL innodb_default_row_format= COMPACT;
127 ALTER TABLE t1 DROP b;
128 SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
129 ROW_FORMAT
130 Dynamic
131 DROP TABLE t1;
132 #
133 # MDEV-31025 Redundant table alter fails when fixed column
134 # stored externally
135 #
136 set @old_sql_mode = @@sql_mode;
137 SET @@sql_mode='';
138 CREATE TABLE t1(pk INT,c CHAR(255),c2 CHAR(255),c3 CHAR(255),
139 c4 char(255), c5 char(255), c6 char(255),
140 c7 char(255), c8 char(255), primary key(pk)
141 )Engine=InnoDB character set utf32 ROW_FORMAT=REDUNDANT;
142 INSERT INTO t1(pk, c) VALUES (1, repeat('a', 255));
143 ALTER TABLE t1 FORCE;
144 CHECK TABLE t1;
145 Table Op Msg_type Msg_text
146 test.t1 check status OK
147 SELECT LENGTH(c) FROM t1;
148 LENGTH(c)
149 1020
150 DROP TABLE t1;
151 set @@sql_mode = @old_sql_mode;
152 # End of 10.4 tests
153 SET GLOBAL innodb_default_row_format = @row_format;