Debian Codegraph @

mysql-test/suite/maria/alter.test

raw
1 # Testing of potential problems in Aria and alter table
2
3 -- source include/have_maria.inc
4 -- source include/have_innodb.inc
5 -- source include/have_sequence.inc
6
7 drop table if exists t1;
8
9 #
10 # MDEV-4970 Wrong result with Aria table populated with disabled keys
11 #
12
13 CREATE TABLE t1 (pk INT, d DATETIME, PRIMARY KEY(pk), KEY(d)) ENGINE=Aria;
14 ALTER TABLE t1 DISABLE KEYS;
15 INSERT INTO t1 VALUES (1,'2000-01-01 22:22:22'),(2,'2012-12-21 12:12:12');
16 INSERT INTO t1 VALUES (3, '2008-07-24');
17 ALTER TABLE t1 ENABLE KEYS;
18
19 SELECT t1a.pk FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;
20 SELECT * FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;
21 DROP TABLE t1;
22
23 CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=Aria;
24 ALTER TABLE t1 DISABLE KEYS;
25 INSERT INTO t1 VALUES (1,11);
26 INSERT INTO t1 VALUES (2,0),(3,33),(4,0),(5,55),(6,66),(7,0),(8,88),(9,99);
27 ALTER TABLE t1 ENABLE KEYS;
28 SELECT * FROM t1 WHERE i = 0 OR pk BETWEEN 6 AND 10;
29 DROP TABLE t1;
30
31 #
32 # MDEV-14943
33 # Assertion `block->type == PAGECACHE_EMPTY_PAGE || block->type == type ||
34 # type == PAGECACHE_LSN_PAGE || type == PAGECACHE_READ_UNKNOWN_PAGE ||
35 # block->type == PAGECACHE_READ_UNKNOWN_PAGE' failed in pagecache_read upon
36 # CREATE ... SELECT from Aria table
37 #
38
39 CREATE TABLE t1 (f INT) ENGINE=Aria transactional=1;
40 SHOW CREATE TABLE t1;
41 INSERT INTO t1 VALUES (1),(2);
42 --error ER_BAD_FIELD_ERROR
43 ALTER TABLE t1 ORDER BY unknown_column;
44 SHOW CREATE TABLE t1;
45 CREATE TABLE t2 SELECT * FROM t1;
46 DROP TABLE t1, t2;
47
48 --echo #
49 --echo # MDEV-19055 Assertion `(_my_thread_var())->thr_errno != 0' failed in pagecache_read
50 --echo #
51
52 --disable_warnings
53 CREATE OR REPLACE TABLE t1 (x INT) ENGINE=Aria;
54 CREATE TEMPORARY TABLE t2 (a TIME) ENGINE=Aria;
55 ALTER TABLE t2 ADD b DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
56 ALTER TABLE t2 ADD CHECK (b = 4);
57 INSERT IGNORE INTO t2 () VALUES (),(),(),();
58 ALTER IGNORE TABLE t2 ADD c INT;
59 SELECT count(a),sum(a) FROM t2;
60 DELETE FROM t2 ORDER BY c LIMIT 1;
61 INSERT IGNORE INTO t2 SELECT * FROM t2;
62 OPTIMIZE TABLE t2;
63 SELECT count(a),sum(a) FROM t2;
64 INSERT IGNORE INTO t2 SELECT * FROM t2;
65 SET SQL_MODE= 'STRICT_ALL_TABLES';
66 SELECT count(a),sum(a) FROM t2;
67 --error ER_TRUNCATED_WRONG_VALUE
68 ALTER TABLE t2 CHANGE IF EXISTS d c INT;
69 SELECT count(a),sum(a) FROM t2;
70 ALTER IGNORE TABLE t2 ADD IF NOT EXISTS e BIT;
71 ALTER TABLE t1 MODIFY IF EXISTS xx INT;
72 INSERT IGNORE INTO t2 () VALUES (),(),(),();
73 SELECT count(a),sum(a) FROM t2;
74 --enable_warnings
75 check table t1;
76 check table t2;
77 DROP TABLE t1,t2;
78
79 --echo #
80 --echo # MDEV-17576
81 --echo # Assertion `share->reopen == 1' failed in maria_extra upon ALTER on
82 --echo # Aria table with triggers and locks
83 --echo #
84
85 CREATE TABLE t1 (a INT) ENGINE=Aria;
86 CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t1 SELECT * FROM t1;
87 LOCK TABLE t1 WRITE;
88 ALTER TABLE t1 FORCE, LOCK=EXCLUSIVE;
89 DROP TRIGGER tr;
90 DROP TABLE t1;
91
92 --echo #
93 --echo # End of 10.2 test
94 --echo #
95
96 --echo #
97 --echo # MDEV-17665 Assertion `!share || share->page_type == PAGECACHE_LSN_PAGE',
98 --echo # `type != PAGECACHE_READ_UNKNOWN_PAGE' and similar ones upon concurrent
99 --echo # Aria operations
100 --echo #
101
102 CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=Aria;
103 ALTER TABLE t1 DISABLE KEYS;
104 INSERT INTO t1 SELECT seq, seq FROM seq_1_to_100;
105
106 --connect (con1,localhost,root,,test)
107 --connect (con2,localhost,root,,test)
108 --send
109 INSERT INTO t1 SELECT seq, seq FROM seq_1_to_100;
110
111 --connection con1
112 --send
113 FLUSH TABLES;
114
115 --connection default
116 --error ER_WRONG_VALUE_COUNT_ON_ROW
117 INSERT INTO t1 SELECT 1;
118
119 # Cleanup
120 --connection con2
121 --error ER_DUP_ENTRY
122 --reap
123 --disconnect con2
124 --connection con1
125 --reap
126 --disconnect con1
127 --connection default
128 DROP TABLE t1;
129
130 --echo #
131 --echo # MDEV-22674 Server crash in compare_bin, ASAN heap-buffer-overflow in
132 --echo # _ma_dpointer, Assertion `!info->s->have_versioning ||
133 --echo # info->s->lock_key_trees' failed in restore_table_state_after_repair
134 --echo #
135
136 CREATE TABLE t1 (id INT, d DATETIME, PRIMARY KEY(d,id),INDEX(d)) ENGINE=Aria;
137 INSERT INTO t1 SELECT seq, NOW() FROM seq_1_to_500;
138
139 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
140 INSERT INTO t2 VALUES (1);
141
142 --connect (con1,localhost,root,,test)
143 ALTER TABLE t1 DISABLE KEYS;
144 --send
145 INSERT INTO t1 (id) SELECT b FROM t2;
146
147 --connection default
148 --error ER_WRONG_VALUE_COUNT_ON_ROW
149 INSERT INTO t1 SELECT a FROM t2;
150
151 # Cleanup
152 --connection con1
153 --error ER_BAD_FIELD_ERROR
154 --reap
155 --disconnect con1
156
157 --connection default
158 drop table t1, t2;
159
160 --echo # This is also in MDEV-22674
161
162 CREATE TABLE t1 (id INT, d DATETIME, PRIMARY KEY(d,id),INDEX(d)) ENGINE=Aria;
163 INSERT INTO t1 SELECT seq, NOW() FROM seq_1_to_500;
164 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
165 INSERT INTO t2 VALUES (1);
166 ALTER TABLE t1 DISABLE KEYS;
167 --error ER_BAD_FIELD_ERROR
168 INSERT INTO t1 (id) SELECT b FROM t2;
169 --error ER_WRONG_VALUE_COUNT_ON_ROW
170 INSERT INTO t1 SELECT a FROM t2;
171 SELECT COUNT(*) FROM t1;
172 DROP TABLE t1,t2;
173
174 --echo #
175 --echo # MDEV-23296 Assertion `block->type == PAGECACHE_EMPTY_PAGE ||
176 --echo # block->type == PAGECACHE_READ_UNKNOWN_PAGE || block->type == type ||
177 --echo # (block->type == PAGECACHE_PLAIN_PAGE && type == PAGECACHE_LSN_PAGE)'
178 --echo # with aria_max_sort_file_size
179 --echo #
180
181 SET @max_size.save= @@aria_max_sort_file_size;
182 SET GLOBAL aria_max_sort_file_size= 0;
183 CREATE TABLE t1 (pk INT PRIMARY KEY, a CHAR(255), KEY(a)) ENGINE=Aria;
184 ALTER TABLE t1 DISABLE KEYS;
185 INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
186 ALTER TABLE t1 ENABLE KEYS;
187 INSERT INTO t1 VALUES (3,'nice try');
188 SELECT * FROM t1;
189 DROP TABLE t1;
190 SET GLOBAL aria_max_sort_file_size= @max_size.save;
191
192 --echo #
193 --echo # End of 10.4 test
194 --echo #
195
196 --echo #
197 --echo # MDEV-25505 Assertion `old_flags == ((my_flags & 0x10000U) ? 1 : 0)'
198 --echo # failed in my_realloc
199 --echo #
200
201 CREATE TABLE t1 (pk int, c text, primary key (pk), key(c(32))) ENGINE=Aria ROW_FORMAT=DYNAMIC;
202 ALTER TABLE t1 DISABLE KEYS;
203 INSERT INTO t1 VALUES (1, 'Nine chars or more');
204 ALTER TABLE t1 ENABLE KEYS;
205 DROP TABLE t1;
206
207 --echo #
208 --echo # MDEV-25923 Memory not freed or Assertion `old_flags == ((my_flags &
209 --echo # 0x10000U) ? 1 : 0)' failed in my_realloc upon ALTER on Aria table
210 --echo # with GIS column
211 --echo #
212
213 CREATE TABLE t1 (pk INT PRIMARY KEY, a POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=Aria;
214 INSERT INTO t1 (pk) SELECT seq FROM seq_1_to_100;
215 SET @old_threads= @@SESSION.aria_repair_threads;
216 SET SESSION aria_repair_threads= 2;
217 ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
218 DROP TABLE t1;
219
220 --echo #
221 --echo # MDEV-33562: Assertion `(old_flags & 1) == ((my_flags & 0x10000U) ?
222 --echo # 1 : 0)' failed in my_realloc from sort_get_next_record on INSERT
223 --echo #
224
225 SET @old_mode= @@SESSION.sql_mode;
226 SET sql_mode='';
227 CREATE TEMPORARY TABLE t (b TEXT, INDEX s(b(300))) ROW_FORMAT=DYNAMIC ENGINE=Aria;
228 INSERT INTO t VALUES (REPEAT ('a',10000000));
229 CREATE TABLE ti LIKE t;
230 INSERT INTO ti SELECT * FROM t;
231 DROP TABLE t, ti;
232 SET SESSION aria_repair_threads= @old_threads;
233 SET SESSION sql_mode= @old_mode;