mysql-test/main/partition_myisam.result

DROP TABLE IF EXISTS t1, t2;
#
# BUG#11933226 - 60681: CHECKSUM TABLE RETURNS 0 FOR PARTITIONED TABLE
#
CREATE TABLE t1 (
i INT
)
ENGINE=MyISAM
PARTITION BY RANGE (i)
(PARTITION p3 VALUES LESS THAN (3),
PARTITION p5 VALUES LESS THAN (5),
PARTITION pMax VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
CHECKSUM TABLE t1;
Table	Checksum
test.t1	2653438147
ALTER TABLE t1 CHECKSUM = 1;
CHECKSUM TABLE t1 EXTENDED;
Table	Checksum
test.t1	2653438147
# Before patch this returned 0!
CHECKSUM TABLE t1;
Table	Checksum
test.t1	2653438147
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1
 PARTITION BY RANGE (`i`)
(PARTITION `p3` VALUES LESS THAN (3) ENGINE = MyISAM,
 PARTITION `p5` VALUES LESS THAN (5) ENGINE = MyISAM,
 PARTITION `pMax` VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
DROP TABLE t1;
# Same test without partitioning
CREATE TABLE t1 (
i INT
) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
CHECKSUM TABLE t1;
Table	Checksum
test.t1	2653438147
ALTER TABLE t1 CHECKSUM = 1;
CHECKSUM TABLE t1 EXTENDED;
Table	Checksum
test.t1	2653438147
CHECKSUM TABLE t1;
Table	Checksum
test.t1	2653438147
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1
DROP TABLE t1;
#
# Bug#31931: Mix of handlers error message
#
CREATE TABLE t1 (a INT)
PARTITION BY HASH (a)
( PARTITION p0 ENGINE=MyISAM,
PARTITION p1);
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB
CREATE TABLE t1 (a INT)
PARTITION BY LIST (a)
SUBPARTITION BY HASH (a)
( PARTITION p0 VALUES IN (0)
( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
PARTITION p1 VALUES IN (1)
( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB
#
# Bug#49161: Out of memory; restart server and try again (needed 2 bytes)
#
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY HASH (a);
FLUSH TABLES;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	Error	Failed to read from the .par file
test.t1	check	error	Corrupt
SELECT * FROM t1;
ERROR HY000: Failed to read from the .par file
# Note that it is currently impossible to drop a partitioned table
# without the .par file
DROP TABLE t1;
ERROR HY000: Got error 1 "Operation not permitted" from storage engine partition
#
# Bug#50392: insert_id is not reset for partitioned tables
#            auto_increment on duplicate entry
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
ENGINE = MyISAM;
SET INSERT_ID= 13;
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID= 12;
# For transactional engines, 12 will not be inserted, since the failing
# statement is rolled back.
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES (NULL);
# NOTE: 12 exists only in non transactional engines!
SELECT * FROM t1;
a
12
13
14
DROP TABLE t1;
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY)
ENGINE = MyISAM
PARTITION BY KEY(a);
SET INSERT_ID= 13;
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID= 12;
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
 PARTITION BY KEY (`a`)
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1;
a
12
13
14
DROP TABLE t1;
# Bug#30102 test
CREATE TABLE t1 (a INT)
ENGINE = MyISAM
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (6),
PARTITION `p1....................` VALUES LESS THAN (9),
PARTITION p2 VALUES LESS THAN MAXVALUE);
# List of files in database `test`, all original t1-files here
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYD
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYI
t1#P#p2.MYD
t1#P#p2.MYI
t1.frm
t1.par
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
# Renaming to a file name where the first partition is 250 chars
# and the second partition is 350 chars
RENAME TABLE t1 TO `t2_new..............................................end`;
Got one of the listed errors
# List of files in database `test`, should not be any t2-files here
# List of files in database `test`, should be all t1-files here
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYD
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYI
t1#P#p2.MYD
t1#P#p2.MYI
t1.frm
t1.par
SELECT * FROM t1;
a
1
10
2
3
4
5
6
7
8
9
# List of files in database `test`, should be all t1-files here
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYD
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYI
t1#P#p2.MYD
t1#P#p2.MYI
t1.frm
t1.par
# Renaming to a file name where the first partition is 156 chars
# and the second partition is 256 chars
RENAME TABLE t1 TO `t2_............................_end`;
Got one of the listed errors
# List of files in database `test`, should not be any t2-files here
# List of files in database `test`, should be all t1-files here
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYD
t1#P#p1@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.MYI
t1#P#p2.MYD
t1#P#p2.MYI
t1.frm
t1.par
SELECT * FROM t1;
a
1
10
2
3
4
5
6
7
8
9
DROP TABLE t1;
# Should not be any files left here
# End of bug#30102 test.
# Test of post-push fix for bug#11766249/59316
CREATE TABLE t1 (a INT, b VARCHAR(255), PRIMARY KEY (a))
ENGINE = MyISAM
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (0) MAX_ROWS=100,
PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=100,
PARTITION pMax VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1, "Partition p1, first row");
DROP TABLE t1;
#
# MDEV-10418 Assertion `m_extra_cache' failed
#    in ha_partition::late_extra_cache(uint)
#
CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (f2 INT) ENGINE=MyISAM PARTITION BY RANGE(f2) (PARTITION pmax VALUES LESS THAN MAXVALUE);
INSERT INTO t2 VALUES (8);
CREATE ALGORITHM = MERGE VIEW v AS SELECT f2 FROM t2, t1;
UPDATE v SET f2 = 1;
SELECT * FROM t2;
f2
1
DROP VIEW v;
DROP TABLE t2;
DROP TABLE t1;
#
# bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED
#                                 TABLE CORRUPTS MYISAM
DROP TABLE if exists `t1`;
CREATE TABLE  `t1`(`a` INT)ENGINE=myisam;
ALTER TABLE `t1` ADD COLUMN `b` INT;
CREATE UNIQUE INDEX `i1` ON `t1`(`b`);
CREATE UNIQUE INDEX `i2` ON `t1`(`a`);
ALTER TABLE `t1` ADD PRIMARY KEY  (`a`);
ALTER TABLE `t1` REMOVE PARTITIONING;
ERROR HY000: Partition management on a not partitioned table is not possible
CHECK TABLE `t1` EXTENDED;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;