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:
None 
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 15:45] Peter Laursen
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.
[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)