1--source include/have_partition.inc
2--disable_warnings
3DROP TABLE IF EXISTS t1, t2;
4--enable_warnings
5
6# These tests is only useful when running on MyISAM,
7# due to DATA/INDEX directory, non transactional behavior, tests with MyISAM
8# files etc.
9
10let $MYSQLD_DATADIR= `SELECT @@datadir`;
11
12--echo #
13--echo # BUG#11933226 - 60681: CHECKSUM TABLE RETURNS 0 FOR PARTITIONED TABLE
14--echo #
15CREATE TABLE t1 (
16  i INT
17)
18ENGINE=MyISAM
19PARTITION BY RANGE (i)
20(PARTITION p3 VALUES LESS THAN (3),
21 PARTITION p5 VALUES LESS THAN (5),
22 PARTITION pMax VALUES LESS THAN MAXVALUE);
23INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
24CHECKSUM TABLE t1;
25ALTER TABLE t1 CHECKSUM = 1;
26CHECKSUM TABLE t1 EXTENDED;
27--echo # Before patch this returned 0!
28CHECKSUM TABLE t1;
29SHOW CREATE TABLE t1;
30DROP TABLE t1;
31
32--echo # Same test without partitioning
33CREATE TABLE t1 (
34  i INT
35) ENGINE=MyISAM;
36SHOW CREATE TABLE t1;
37INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
38CHECKSUM TABLE t1;
39ALTER TABLE t1 CHECKSUM = 1;
40CHECKSUM TABLE t1 EXTENDED;
41CHECKSUM TABLE t1;
42SHOW CREATE TABLE t1;
43DROP TABLE t1;
44
45#
46# Disabled by WL#946: binary format for timestamp column is not compatible.
47# So the trick with replacing FRM file does not work any more.
48#--echo #
49#--echo # Bug#50036: Inconsistent errors when using TIMESTAMP
50#--echo #            columns/expressions
51#
52#--echo # Added test with existing TIMESTAMP partitioning (when it was allowed).
53#CREATE TABLE t1 (a TIMESTAMP)
54#ENGINE = MyISAM
55#PARTITION BY HASH (UNIX_TIMESTAMP(a));
56#INSERT INTO t1 VALUES ('2000-01-02 03:04:05');
57#--sorted_result
58#SELECT * FROM t1;
59#FLUSH TABLES;
60#--echo # replacing t1.frm with TO_DAYS(a) which was allowed earlier.
61#--remove_file $MYSQLD_DATADIR/test/t1.frm
62#--copy_file std_data/parts/t1TIMESTAMP.frm $MYSQLD_DATADIR/test/t1.frm
63#--echo # Disable warnings, since the result would differ when running with
64#--echo # --ps-protocol (only for the 'SELECT * FROM t1' statement).
65#--disable_warnings
66#--sorted_result
67#SELECT * FROM t1;
68#--enable_warnings
69#--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
70#SHOW CREATE TABLE t1;
71#INSERT INTO t1 VALUES ('2001-02-03 04:05:06');
72#--sorted_result
73#SELECT * FROM t1;
74#SELECT a, hex(weight_string(a)) FROM t1;
75#ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
76#--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
77#ALTER TABLE t1
78#PARTITION BY RANGE (TO_DAYS(a))
79#(PARTITION p0 VALUES LESS THAN (10000),
80# PARTITION p1 VALUES LESS THAN (MAXVALUE));
81#SHOW CREATE TABLE t1;
82#CREATE TABLE t2 LIKE t1;
83#SHOW CREATE TABLE t2;
84#DROP TABLE t2;
85#CREATE TABLE t2 SELECT * FROM t1;
86#DROP TABLE t2;
87#ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a));
88#SHOW CREATE TABLE t1;
89#ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
90#SHOW CREATE TABLE t1;
91#--sorted_result
92#SELECT * FROM t1;
93#DROP TABLE t1;
94
95--echo #
96--echo # Bug#31931: Mix of handlers error message
97--echo #
98--error ER_MIX_HANDLER_ERROR
99CREATE TABLE t1 (a INT)
100PARTITION BY HASH (a)
101( PARTITION p0 ENGINE=MyISAM,
102  PARTITION p1);
103--error ER_MIX_HANDLER_ERROR
104CREATE TABLE t1 (a INT)
105PARTITION BY LIST (a)
106SUBPARTITION BY HASH (a)
107( PARTITION p0 VALUES IN (0)
108( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
109  PARTITION p1 VALUES IN (1)
110( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
111
112--echo #
113--echo # Bug#49161: Out of memory; restart server and try again (needed 2 bytes)
114--echo #
115CREATE TABLE t1 (a INT)
116ENGINE = MyISAM
117PARTITION BY HASH (a);
118FLUSH TABLES;
119--remove_file $MYSQLD_DATADIR/test/t1.par
120--replace_result $MYSQLD_DATADIR ./
121CHECK TABLE t1;
122--error ER_FAILED_READ_FROM_PAR_FILE
123SELECT * FROM t1;
124--replace_result $MYSQLD_DATADIR ./
125DROP TABLE t1;
126--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYI
127--remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYD
128
129--echo #
130--echo # Bug#50392: insert_id is not reset for partitioned tables
131--echo #            auto_increment on duplicate entry
132CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
133ENGINE = MyISAM;
134SET INSERT_ID= 13;
135INSERT INTO t1 VALUES (NULL);
136SET INSERT_ID= 12;
137--echo # For transactional engines, 12 will not be inserted, since the failing
138--echo # statement is rolled back.
139--error ER_DUP_ENTRY
140INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
141SHOW CREATE TABLE t1;
142INSERT INTO t1 VALUES (NULL);
143--echo # NOTE: 12 exists only in non transactional engines!
144SELECT * FROM t1;
145DROP TABLE t1;
146CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
147ENGINE = MyISAM
148PARTITION BY KEY(a);
149SET INSERT_ID= 13;
150INSERT INTO t1 VALUES (NULL);
151SET INSERT_ID= 12;
152--error ER_DUP_ENTRY
153INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
154SHOW CREATE TABLE t1;
155INSERT INTO t1 VALUES (NULL);
156SELECT * FROM t1;
157DROP TABLE t1;
158#
159# Bug#30102: rename table does corrupt tables with partition files on failure
160#
161--echo # Bug#30102 test
162CREATE TABLE t1 (a INT)
163ENGINE = MyISAM
164PARTITION BY RANGE (a)
165(PARTITION p0 VALUES LESS THAN (6),
166 PARTITION `p1....................` VALUES LESS THAN (9),
167 PARTITION p2 VALUES LESS THAN MAXVALUE);
168# partition p1 is 't1#P#p1' + @002e * 20 = 107 characters + file ending
169# total path lenght of './test/t1#P#p1@002e@002e<...>@002e.MY[ID]' is 118 chars
170--echo # List of files in database `test`, all original t1-files here
171--list_files $MYSQLD_DATADIR/test t1*
172INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
173--echo # Renaming to a file name where the first partition is 250 chars
174--echo # and the second partition is 350 chars
175# 7,7 avoids the error message, which is not deterministic.
176--error 7,7
177RENAME TABLE t1 TO `t2_new..............................................end`;
178#                         1234567890123456789012345678901234567890123456
179--echo # List of files in database `test`, should not be any t2-files here
180--list_files $MYSQLD_DATADIR/test t2*
181--echo # List of files in database `test`, should be all t1-files here
182--list_files $MYSQLD_DATADIR/test t1*
183--sorted_result
184SELECT * FROM t1;
185--echo # List of files in database `test`, should be all t1-files here
186--list_files $MYSQLD_DATADIR/test t1*
187--echo # Renaming to a file name where the first partition is 156 chars
188--echo # and the second partition is 256 chars
189# 7,7 avoids the error message, which is not deterministic.
190--error 7,7
191RENAME TABLE t1 TO `t2_............................_end`;
192#                      1234567890123456789012345678
193# 7 + 4 + 5 + 28 * 5 = 16 + 140 = 156
194--echo # List of files in database `test`, should not be any t2-files here
195--list_files $MYSQLD_DATADIR/test t2*
196--echo # List of files in database `test`, should be all t1-files here
197--list_files $MYSQLD_DATADIR/test t1*
198--sorted_result
199SELECT * FROM t1;
200DROP TABLE t1;
201--echo # Should not be any files left here
202--list_files $MYSQLD_DATADIR/test t1*
203--list_files $MYSQLD_DATADIR/test t2*
204--echo # End of bug#30102 test.
205
206--echo # Test of post-push fix for bug#11766249/59316
207CREATE TABLE t1 (a INT, b VARCHAR(255), PRIMARY KEY (a))
208ENGINE = MyISAM
209PARTITION BY RANGE (a)
210(PARTITION p0 VALUES LESS THAN (0) MAX_ROWS=100,
211 PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=100,
212 PARTITION pMax VALUES LESS THAN MAXVALUE);
213INSERT INTO t1 VALUES (1, "Partition p1, first row");
214DROP TABLE t1;
215
216--echo #
217--echo # MDEV-10418 Assertion `m_extra_cache' failed
218--echo #    in ha_partition::late_extra_cache(uint)
219--echo #
220
221CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
222INSERT INTO t1 VALUES (1),(2);
223
224CREATE TABLE t2 (f2 INT) ENGINE=MyISAM PARTITION BY RANGE(f2) (PARTITION pmax VALUES LESS THAN MAXVALUE);
225INSERT INTO t2 VALUES (8);
226
227CREATE ALGORITHM = MERGE VIEW v AS SELECT f2 FROM t2, t1;
228
229UPDATE v SET f2 = 1;
230
231SELECT * FROM t2;
232
233DROP VIEW v;
234DROP TABLE t2;
235DROP TABLE t1;
236
237--echo #
238--echo # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
239--echo #                                 TABLE CORRUPTS MYISAM
240--disable_warnings
241DROP TABLE if exists `t1`;
242--enable_warnings
243CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
244ALTER TABLE `t1` ADD COLUMN `b` INT;
245CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
246CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
247ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
248--error ER_PARTITION_MGMT_ON_NONPARTITIONED
249ALTER TABLE `t1` REMOVE PARTITIONING;
250CHECK TABLE `t1` EXTENDED;
251DROP TABLE t1;
252