mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
282 lines
8.1 KiB
Text
282 lines
8.1 KiB
Text
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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
|
|
DROP TABLE t1;
|
|
Warnings:
|
|
Warning 1017 Can't find file: './test/t1.par' (errno: 2 "No such file or directory")
|
|
#
|
|
# 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
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;
|
|
#
|
|
# MDEV-31122 Server crash in get_lock_data / mysql_lock_abort_for_thread
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (b INT, c varchar(5))
|
|
PARTITION BY RANGE COLUMNS(c)
|
|
SUBPARTITION by key(b) SUBPARTITIONS 2 (
|
|
PARTITION p0 VALUES LESS THAN ('m'),
|
|
PARTITION p1 VALUES LESS THAN ('z')
|
|
);
|
|
connect con1,localhost,root,,;
|
|
HANDLER t1 OPEN;
|
|
SELECT b FROM t2 PARTITION (p0);
|
|
connection default;
|
|
SET lock_wait_timeout= 1;
|
|
ALTER TABLE t1 FORCE;
|
|
connection con1;
|
|
b
|
|
disconnect con1;
|
|
connection default;
|
|
DROP TABLE t2, t1;
|