Description:
Szenario:
1. Failing ALTER TABLE ADD PRIMARY KEY, because the column
used for partitioning is not included
2. Now the metadata of the table known by the server are somehow
rotten. The SHOW CREATE TABLE output contains a garbled
partitioning function.
3. INSERTs which should be successful fail now.
The error message says that there is no appropriate partition.
4. An INSERT using @variables which should be successful causes
a crash of the server
Additional tests after restart of the server show
- correct SHOW CREATE TABLE output
- The frm file of the table has the same content like before
the failing ADD PRIMARY KEY
- Now the INSERTs are successful
So it looks like the metadata are only temporary rotten.
Example:
SET @max_row = 100;
CREATE TABLE t1 (f_int1 INTEGER, f_int2 INTEGER,
f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000))
PARTITION BY LIST(f_int1)
(PARTITION part_4 VALUES IN (-4),
PARTITION part_3 VALUES IN (-3),
PARTITION part_2 VALUES IN (-2),
PARTITION part_1 VALUES IN (-1),
PARTITION part0 VALUES IN (0),
PARTITION part1 VALUES IN (1),
PARTITION part2 VALUES IN (2),
PARTITION part3 VALUES IN (3),
PARTITION part4 VALUES IN (4));
INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row,
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR);
DELETE FROM t1 WHERE f_int1 IS NULL;
SELECT * FROM t1;
f_int1 f_int2 f_char1 f_char2 f_charbig
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f_int1` int(11) default NULL,
`f_int2` int(11) default NULL,
`f_char1` char(10) default NULL,
`f_char2` char(10) default NULL,
`f_charbig` varchar(1000) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (f_int1) (PARTITION part_4 VALUES IN (-4) ENGINE = MyISAM, PARTITION part_3 VALUES IN (-3) ENGINE = MyISAM, PARTITION part_2 VALUES IN (-2) ENGINE = MyISAM, PARTITION part_1 VALUES IN (-1) ENGINE = MyISAM, PARTITION part0 VALUES IN (0) ENGINE = MyISAM, PARTITION part1 VALUES IN (1) ENGINE = MyISAM, PARTITION part2 VALUES IN (2) ENGINE = MyISAM, PARTITION part3 VALUES IN (3) ENGINE = MyISAM, PARTITION part4 VALUES IN (4) ENGINE = MyISAM)
ALTER TABLE t1 ADD PRIMARY KEY(f_int2);
ERROR HY000: A PRIMARY KEY need to include all fields in the partition function
<--- This error is to be expected.
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`f_int1` int(11) default NULL,
`f_int2` int(11) default NULL,
`f_char1` char(10) default NULL,
`f_char2` char(10) default NULL,
`f_charbig` varchar(1000) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (??????) (PARTITION part_4 VALUES IN (-4) ENGINE = MyISAM, PARTITION part_3 VALUES IN (-3) ENGINE = MyISAM, PARTITION part_2 VALUES IN (-2) ENGINE = MyISAM, PARTITION part_1 VALUES IN (-1) ENGINE = MyISAM, PARTITION part0 VALUES IN (0) ENGINE = MyISAM, PARTITION part1 VALUES IN (1) ENGINE = MyISAM, PARTITION part2 VALUES IN (2) ENGINE = MyISAM, PARTITION part3 VALUES IN (3) ENGINE = MyISAM, PARTITION part4 VALUES IN (4) ENGINE = MyISAM)
<----- What is PARTITION BY LIST (??????) ?
INSERT INTO t1 SET f_int1 = NULL , f_int2 = - 100,
f_char1 = CAST(- 100 AS CHAR), f_char2 = CAST(- 100 AS CHAR);
ERROR HY000: Table has no partition for value 0
<------ This INSERT should be successful and we have a partition for value 0.
DELETE FROM t1 WHERE f_int1 IS NULL;
INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row,
f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR);
ERROR HY000: Lost connection to MySQL server during query
<------- This INSERT should be successful. And the additional
curious effect is, that this INSERT fails without crash,
when I replace @max_row with the constant 100.
But the current content of @max_row is 100 !!
My environment:
- Intel PC with Linux(SuSE 9.3)
- MySQL compiled from source
Version 5.1 last ChangeSet@1.2126, 2006-02-01
S2, because it is very unlikely that somebody executes statements
like INSERT just after failing ALTER TABLE ADD PRIMARY KEY
P1, because this crash harms the development of tests
ALTER TABLE attempts with following INSERTs appear often.
How to repeat:
Please use my attached testscript ml022.test
copy it to mysql-test/t
echo "Dummy" > r/ml022.result # Produce a dummy file with
# expected results
./mysql-test-run ml022
Description: Szenario: 1. Failing ALTER TABLE ADD PRIMARY KEY, because the column used for partitioning is not included 2. Now the metadata of the table known by the server are somehow rotten. The SHOW CREATE TABLE output contains a garbled partitioning function. 3. INSERTs which should be successful fail now. The error message says that there is no appropriate partition. 4. An INSERT using @variables which should be successful causes a crash of the server Additional tests after restart of the server show - correct SHOW CREATE TABLE output - The frm file of the table has the same content like before the failing ADD PRIMARY KEY - Now the INSERTs are successful So it looks like the metadata are only temporary rotten. Example: SET @max_row = 100; CREATE TABLE t1 (f_int1 INTEGER, f_int2 INTEGER, f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000)) PARTITION BY LIST(f_int1) (PARTITION part_4 VALUES IN (-4), PARTITION part_3 VALUES IN (-3), PARTITION part_2 VALUES IN (-2), PARTITION part_1 VALUES IN (-1), PARTITION part0 VALUES IN (0), PARTITION part1 VALUES IN (1), PARTITION part2 VALUES IN (2), PARTITION part3 VALUES IN (3), PARTITION part4 VALUES IN (4)); INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR); DELETE FROM t1 WHERE f_int1 IS NULL; SELECT * FROM t1; f_int1 f_int2 f_char1 f_char2 f_charbig SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) default NULL, `f_int2` int(11) default NULL, `f_char1` char(10) default NULL, `f_char2` char(10) default NULL, `f_charbig` varchar(1000) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (f_int1) (PARTITION part_4 VALUES IN (-4) ENGINE = MyISAM, PARTITION part_3 VALUES IN (-3) ENGINE = MyISAM, PARTITION part_2 VALUES IN (-2) ENGINE = MyISAM, PARTITION part_1 VALUES IN (-1) ENGINE = MyISAM, PARTITION part0 VALUES IN (0) ENGINE = MyISAM, PARTITION part1 VALUES IN (1) ENGINE = MyISAM, PARTITION part2 VALUES IN (2) ENGINE = MyISAM, PARTITION part3 VALUES IN (3) ENGINE = MyISAM, PARTITION part4 VALUES IN (4) ENGINE = MyISAM) ALTER TABLE t1 ADD PRIMARY KEY(f_int2); ERROR HY000: A PRIMARY KEY need to include all fields in the partition function <--- This error is to be expected. SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `f_int1` int(11) default NULL, `f_int2` int(11) default NULL, `f_char1` char(10) default NULL, `f_char2` char(10) default NULL, `f_charbig` varchar(1000) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (??????) (PARTITION part_4 VALUES IN (-4) ENGINE = MyISAM, PARTITION part_3 VALUES IN (-3) ENGINE = MyISAM, PARTITION part_2 VALUES IN (-2) ENGINE = MyISAM, PARTITION part_1 VALUES IN (-1) ENGINE = MyISAM, PARTITION part0 VALUES IN (0) ENGINE = MyISAM, PARTITION part1 VALUES IN (1) ENGINE = MyISAM, PARTITION part2 VALUES IN (2) ENGINE = MyISAM, PARTITION part3 VALUES IN (3) ENGINE = MyISAM, PARTITION part4 VALUES IN (4) ENGINE = MyISAM) <----- What is PARTITION BY LIST (??????) ? INSERT INTO t1 SET f_int1 = NULL , f_int2 = - 100, f_char1 = CAST(- 100 AS CHAR), f_char2 = CAST(- 100 AS CHAR); ERROR HY000: Table has no partition for value 0 <------ This INSERT should be successful and we have a partition for value 0. DELETE FROM t1 WHERE f_int1 IS NULL; INSERT INTO t1 SET f_int1 = NULL , f_int2 = -@max_row, f_char1 = CAST(-@max_row AS CHAR), f_char2 = CAST(-@max_row AS CHAR); ERROR HY000: Lost connection to MySQL server during query <------- This INSERT should be successful. And the additional curious effect is, that this INSERT fails without crash, when I replace @max_row with the constant 100. But the current content of @max_row is 100 !! My environment: - Intel PC with Linux(SuSE 9.3) - MySQL compiled from source Version 5.1 last ChangeSet@1.2126, 2006-02-01 S2, because it is very unlikely that somebody executes statements like INSERT just after failing ALTER TABLE ADD PRIMARY KEY P1, because this crash harms the development of tests ALTER TABLE attempts with following INSERTs appear often. How to repeat: Please use my attached testscript ml022.test copy it to mysql-test/t echo "Dummy" > r/ml022.result # Produce a dummy file with # expected results ./mysql-test-run ml022