| Bug #17331 | create table with list-partitions runs forever/server crash | ||
|---|---|---|---|
| Submitted: | 12 Feb 2006 15:45 | Modified: | 20 Feb 2006 17:35 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.1.6 mysqld-nt/5.0.7 BK | OS: | Windows (Win XP) |
| Assigned to: | CPU Architecture: | Any | |
[12 Feb 2006 17:01]
Peter Laursen
and this: select * from `information_schema`.`PARTITIONS` limit 0, 50; crashes the server! (INFORMATION_SCHEMA.PARTITIONS -table is empty)
[12 Feb 2006 17:09]
Peter Laursen
And of course it is a typo that I wrote 4.1.3. Should be 5.1.3 :-(
[12 Feb 2006 17:26]
Peter Laursen
I now tried all partition-types with examples from the docs, and not a single one seems to work. I can add that the /datadir is 'clean' - that is a installed with the installer. Thus it is not an issue with tables originating from a previous version.
[13 Feb 2006 11:52]
MySQL Verification Team
Which server version are you using, notice that only the mysqld-max-nt
has partition:
c:\mysql\bin>mysql -uroot db15
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.7-beta-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show variables like "%partition%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | NO |
+-------------------+-------+
1 row in set (0.02 sec)
mysql> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT,
-> store_id INT
-> )
-> PARTITION BY LIST(store_id) (
-> PARTITION pNorth VALUES IN (3,5,6,9,17),
-> PARTITION pEast VALUES IN (1,2,10,11,19,20),
-> PARTITION pWest VALUES IN (4,12,13,14,18),
-> PARTITION pCentral VALUES IN (7,8,15,16)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`fname` varchar(30) default NULL,
`lname` varchar(30) default NULL,
`hired` date NOT NULL default '1970-01-01',
`separated` date NOT NULL default '9999-12-31',
`job_code` int(11) default NULL,
`store_id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
However I was able to repeat the crash:
mysql> select * from `information_schema`.`PARTITIONS` limit 0, 50;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
and also with the mysqld-max-nt server:
c:\mysql\bin>mysql -uroot db15
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.7-beta-nt-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from `information_schema`.`PARTITIONS` limit 0, 50;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Notice below the error messages launched by the server at start after
to try to create the table with partition with a server mysqld-nt:
c:\mysql\bin>mysqld-max-nt --standalone --console
mysqld-max-nt: Error on delete of 'C:\DOCUME~1\miguel\CONFIG~1\Temp#sql_19c_0.MYD' (Errcode: 2)
mysqld-max-nt: Error on delete of 'C:\DOCUME~1\miguel\CONFIG~1\Temp#sql_19c_0.MYI' (Errcode: 2)
060213 9:32:20 InnoDB: Started; log sequence number 0 43665
060213 9:32:23 [Note] mysqld-max-nt: ready for connections.
Version: '5.1.7-beta-nt-max' socket: '' port: 3306 Source distribution
c:\mysql\bin>mysqld-max-nt --standalone --console
mysqld-max-nt: Error on delete of 'C:\DOCUME~1\miguel\CONFIG~1\Temp#sql_19c_0.MYD' (Errcode: 2)
mysqld-max-nt: Error on delete of 'C:\DOCUME~1\miguel\CONFIG~1\Temp#sql_19c_0.MYI' (Errcode: 2)
mysqld-max-nt: Error on delete of 'C:\DOCUME~1\miguel\CONFIG~1\Temp#sql_b44_0.MYD' (Errcode: 2)
mysqld-max-nt: Error on delete of 'C:\DOCUME~1\miguel\CONFIG~1\Temp#sql_b44_0.MYI' (Errcode: 2)
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
Then I deleted these temporary files manually:
c:\mysql\bin>mysqld-max-nt --standalone --console
060213 9:48:40 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060213 9:48:40 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43655.
InnoDB: Doing recovery: scanned up to log sequence number 0 43655
060213 9:48:40 InnoDB: Started; log sequence number 0 43655
060213 9:48:40 [Note] mysqld-max-nt: ready for connections.
Version: '5.1.7-beta-nt-max' socket: '' port: 3306 Source distribution
and still the server crash:
c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.7-beta-nt-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from `information_schema`.`PARTITIONS` limit 0, 50;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
I was unable to repeat with Linux server.
[13 Feb 2006 13:07]
Peter Laursen
well ... there are no max server binaries in either of the windows packages (with/without installer) from the FTP-mirror. Could you explain a little more ? Also I see that you use 5.1.7. Where did yo get it from ?
[20 Feb 2006 15:46]
Peter Laursen
anybody on the air? :-) I have additional info. A "select @@have_partitioning;" returns "yes" A "select version();" returns "5.1.6-alpha-nt-max" I this in accordance with what you write? But other max-server features are told to be disabled - BDB for instance when querying the system variables. I also can add that it is a completely 'fresh' install. No tables - not system tables either - from other versions.
[20 Feb 2006 17:35]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.8-beta-nt-max-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE employees (
-> id INT NOT NULL,
-> fname VARCHAR(30),
-> lname VARCHAR(30),
-> hired DATE NOT NULL DEFAULT '1970-01-01',
-> separated DATE NOT NULL DEFAULT '9999-12-31',
-> job_code INT,
-> store_id INT
-> )
-> PARTITION BY LIST(store_id) (
-> PARTITION pNorth VALUES IN (3,5,6,9,17),
-> PARTITION pEast VALUES IN (1,2,10,11,19,20),
-> PARTITION pWest VALUES IN (4,12,13,14,18),
-> PARTITION pCentral VALUES IN (7,8,15,16)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`fname` varchar(30) default NULL,
`lname` varchar(30) default NULL,
`hired` date NOT NULL default '1970-01-01',
`separated` date NOT NULL default '9999-12-31',
`job_code` int(11) default NULL,
`store_id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY LIST (store_id) (PARTITION pNorth VALUES IN (3,5,6,9,1
7) ENGINE = MyISAM, PARTITION pEast VALUES IN (1,2,10,11,19,20) ENGINE = MyISAM, PARTITION pWest VALUES IN
(4,12,13,14,18) ENGINE = MyISAM, PARTITION pCentral VALUES IN (7,8,15,16) ENGINE = MyISAM)
1 row in set (0.00 sec)
mysql> select * from `information_schema`.`PARTITIONS` limit 0, 50
-> \G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: information_schema
TABLE_NAME: CHARACTER_SETS
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: NULL
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: NULL
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: NULL
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 0
AVG_ROW_LENGTH: 576
DATA_LENGTH: 0
MAX_DATA_LENGTH: 16661376
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: 0
TABLESPACE_NAME:
*************************** 2. row ***************************
TABLE_CATALOG: NULL
<cut>
TABLESPACE_NAME: default
*************************** 46. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: employees
PARTITION_NAME: pCentral
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 4
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: LIST
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: store_id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 7,8,15,16
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 281474976710655
INDEX_LENGTH: 1024
DATA_FREE: 0
CREATE_TIME: 2006-02-20 14:30:56
UPDATE_TIME: 2006-02-20 14:30:56
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT: default
NODEGROUP: 0
TABLESPACE_NAME: default
46 rows in set, 2 warnings (0.06 sec)
Description: This statement (as copied from the docs) CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY LIST(store_id) ( PARTITION pNorth VALUES IN (3,5,6,9,17), PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18), PARTITION pCentral VALUES IN (7,8,15,16) ); never ends. Or maybe never starts executing (no CPU-load to see in task manager). It DID work on a previous version (4.1.3 I think) It is the same with Command-line client, Query Browser and third-party clients. How to repeat: Install the 5.1.6 Windows installer-build, execute above create statement and you can wait for hours ... It executes fine without partitions! Suggested fix: no idea.