Description:
# Nonsense test, where the MERGE table is partitioned
CREATE TABLE t1_first (f1 INTEGER, f2 CHAR(3)) ;
CREATE TABLE t1_second (f1 INTEGER, f2 CHAR(3)) ;
CREATE TABLE t1 (f1 INTEGER, f2 CHAR(3))
ENGINE = MERGE UNION=(t1_first,t1_second) INSERT_METHOD=FIRST
PARTITION BY HASH(f1) PARTITIONS 2;
<------- Why is the partitioning clause accepted ?
The base object, where the rows are stored
are the tables t1_first and t1_second.
SELECT * FROM t1;
f1 f2
INSERT INTO t1 VALUES(1,'A');
SELECT * FROM t1;
f1 f2
1 A <------- Why do I get the same physical record
1 A <------- two times ?
DELETE FROM t1 WHERE f1 = 1;
# Test where the "base" tables are partitioned
DROP TABLE t1;
DROP TABLE t1_first;
DROP TABLE t1_second;
CREATE TABLE t1_first (f1 INTEGER, f2 CHAR(3))
PARTITION BY HASH(f1) PARTITIONS 2;
CREATE TABLE t1_second (f1 INTEGER, f2 CHAR(3))
PARTITION BY HASH(f1) PARTITIONS 2;
CREATE TABLE t1 (f1 INTEGER, f2 CHAR(3))
ENGINE = MERGE UNION=(t1_first,t1_second) INSERT_METHOD=FIRST;
SHOW CREATE TABLE t1;
ERROR HY000: Can't find file: 't1' (errno: 2)
INSERT INTO t1 VALUES(1,'A');
ERROR HY000: Can't find file: 't1' (errno: 2)
SELECT * FROM t1;
ERROR HY000: Can't find file: 't1' (errno: 2)
DELETE FROM t1 WHERE f1 = 1;
ERROR HY000: Can't find file: 't1' (errno: 2)
<----- Conclusion: The server is currently unable to handle
a merge table being a collection of partitioned
tables.
My environment:
- Intel PC with Linux(SuSE 9.3)
- MySQL compiled from source
Version 5.1 ChangeSet@1.2190, 2006-02-28
I selected S2/P3 because the first testcase with the MERGE
table which is partitioned gets wrong result sets.
The second testcase is more S3/P4, because the server rejects
to work with the table. That means he does not do really bad things
like crashing, mangling stored data or sends wrong result sets.
How to repeat:
Please execute the statements above or use
the attached testscript ml1110.test.
copy it to mysql-test/t
echo "Dummy" > r/ml1110.result # Produce a dummy file with
# expected results
./mysql-test-run ml1110
Suggested fix:
I can think of three solutions:
1) Please alter the checks around CREATE TABLE ... ENGINE=MERGE
statement in such a way, that MERGE tables with partitioning
clause or MERGE tables being a collection of partitioned tables
are rejected.
2) Please remove the MERGE storage engine from MySQL >= 5.1.
See also Bug#17766
3) Reject the creation of a partitioned MERGE table and
document the remaining behaviour (testcase two) within the manual.
I would prefer 2)
Description: # Nonsense test, where the MERGE table is partitioned CREATE TABLE t1_first (f1 INTEGER, f2 CHAR(3)) ; CREATE TABLE t1_second (f1 INTEGER, f2 CHAR(3)) ; CREATE TABLE t1 (f1 INTEGER, f2 CHAR(3)) ENGINE = MERGE UNION=(t1_first,t1_second) INSERT_METHOD=FIRST PARTITION BY HASH(f1) PARTITIONS 2; <------- Why is the partitioning clause accepted ? The base object, where the rows are stored are the tables t1_first and t1_second. SELECT * FROM t1; f1 f2 INSERT INTO t1 VALUES(1,'A'); SELECT * FROM t1; f1 f2 1 A <------- Why do I get the same physical record 1 A <------- two times ? DELETE FROM t1 WHERE f1 = 1; # Test where the "base" tables are partitioned DROP TABLE t1; DROP TABLE t1_first; DROP TABLE t1_second; CREATE TABLE t1_first (f1 INTEGER, f2 CHAR(3)) PARTITION BY HASH(f1) PARTITIONS 2; CREATE TABLE t1_second (f1 INTEGER, f2 CHAR(3)) PARTITION BY HASH(f1) PARTITIONS 2; CREATE TABLE t1 (f1 INTEGER, f2 CHAR(3)) ENGINE = MERGE UNION=(t1_first,t1_second) INSERT_METHOD=FIRST; SHOW CREATE TABLE t1; ERROR HY000: Can't find file: 't1' (errno: 2) INSERT INTO t1 VALUES(1,'A'); ERROR HY000: Can't find file: 't1' (errno: 2) SELECT * FROM t1; ERROR HY000: Can't find file: 't1' (errno: 2) DELETE FROM t1 WHERE f1 = 1; ERROR HY000: Can't find file: 't1' (errno: 2) <----- Conclusion: The server is currently unable to handle a merge table being a collection of partitioned tables. My environment: - Intel PC with Linux(SuSE 9.3) - MySQL compiled from source Version 5.1 ChangeSet@1.2190, 2006-02-28 I selected S2/P3 because the first testcase with the MERGE table which is partitioned gets wrong result sets. The second testcase is more S3/P4, because the server rejects to work with the table. That means he does not do really bad things like crashing, mangling stored data or sends wrong result sets. How to repeat: Please execute the statements above or use the attached testscript ml1110.test. copy it to mysql-test/t echo "Dummy" > r/ml1110.result # Produce a dummy file with # expected results ./mysql-test-run ml1110 Suggested fix: I can think of three solutions: 1) Please alter the checks around CREATE TABLE ... ENGINE=MERGE statement in such a way, that MERGE tables with partitioning clause or MERGE tables being a collection of partitioned tables are rejected. 2) Please remove the MERGE storage engine from MySQL >= 5.1. See also Bug#17766 3) Reject the creation of a partitioned MERGE table and document the remaining behaviour (testcase two) within the manual. I would prefer 2)