MySQL Bugs: #51183: bug when creating a partitionned table by key with more than 497 partitions

Bug #51183	bug when creating a partitionned table by key with more than 497 partitions
Submitted:	15 Feb 2010 11:37	Modified:	15 Feb 2010 16:51
Reporter:	Cyril SCETBON	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.43	OS:	Linux (debian etch)
Assigned to:		CPU Architecture:	Any
Tags:	partitioning

Description:
the table is created but can't be accessed. However I can drop it.

How to repeat:
mysql [localhost] {msandbox} (test) > CREATE TABLE logs_web (d1 datetime DEFAULT NULL,host enum('host01','host02','host03','host04') DEFAULT NULL, msg varchar(500) DEFAULT NULL ) PARTITION BY KEY (host)  PARTITIONS 1024;
Query OK, 0 rows affected (0.24 sec)

mysql [localhost] {msandbox} (test) > select * from logs_web;
ERROR 1016 (HY000): Can't open file: './test/logs_web.frm' (errno: 24)

mysql [localhost] {msandbox} (test) > drop table logs_web;
Query OK, 0 rows affected (0.08 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE logs_web (d1 datetime DEFAULT NULL,host enum('host01','host02','host03','host04') DEFAULT NULL, msg varchar(500) DEFAULT NULL ) PARTITION BY KEY (host)  PARTITIONS 496;
Query OK, 0 rows affected (0.18 sec)

mysql [localhost] {msandbox} (test) > select * from logs_web;
Empty set (0.12 sec)

mysql [localhost] {msandbox} (test) > drop table logs_web;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE logs_web (d1 datetime DEFAULT NULL,host enum('host01','host02','host03','host04') DEFAULT NULL, msg varchar(500) DEFAULT NULL ) PARTITION BY KEY (host)  PARTITIONS 497;
Query OK, 0 rows affected (0.10 sec)

mysql [localhost] {msandbox} (test) > select * from logs_web;
Empty set (0.07 sec)

mysql [localhost] {msandbox} (test) > drop table logs_web;
Query OK, 0 rows affected (0.05 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE logs_web (d1 datetime DEFAULT NULL,host enum('host01','host02','host03','host04') DEFAULT NULL, msg varchar(500) DEFAULT NULL ) PARTITION BY KEY (host)  PARTITIONS 498;
Query OK, 0 rows affected (0.20 sec)

mysql [localhost] {msandbox} (test) > select * from logs_web;
ERROR 1016 (HY000): Can't open file: './test/logs_web.frm' (errno: 24)

Looks like the only problem is open files limit at OS level. Look:

C:\Documents and Settings\Satellite>cd "..\..\Program Files\MySQL\MySQL Server 5
.1"\bin

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.43-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE logs_web (d1 datetime DEFAULT
    -> NULL,host enum('host01','host02','host03','host04') DEFAULT NULL, msg var
char(500)
    -> DEFAULT NULL ) PARTITION BY KEY (host)  PARTITIONS 1024;
Query OK, 0 rows affected (39.36 sec)

mysql> select * from logs_web;
Empty set (0.16 sec)

So, please, check the results of ulimit -a and, if OS allows to have at least 2048 open files per process, try to set opn_files_limit server variable to 2048 at least. Check http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_open-files-limit.

you're right. setting the limit to 2048 was not enough, but 4096 was the solution to access the 1024 partitions. Maybe the error should be clearer

thanks

I think error is clear enough:

77-52-24-143:5.1 openxs$ bin/perror 24
OS error code  24:  Too many open files

MySQL server was not able to open (one of the) partition files because of too many open file handles for the mysqld process. This problem should be solved on OS or my.cnf level.

sorry you're still right I didn't use perror on the number provided and was thinking it meant "could not open file"

forget it

thanks