"Can't create table" when having to many partitions

Posted by Chris on Server Fault See other posts from Server Fault or by Chris
Published on 2014-08-23T09:00:16Z Indexed on 2014/08/23 10:22 UTC
Read the original article Hit count: 289

I am currently having a problem I dont understand. Wherever I look it says mySQL (5.5) / InnoDB doesnt have a table limit.

I wanted to test the InnoDB compression and was about to create an empty copy of an existing table and ran into the following problem.

this one works:

CREATE TABLE `hsc` (
    LOTS OF STUFF
) ENGINE=InnoDB  CHARSET=utf8
PARTITION BY RANGE (pid)
SUBPARTITION BY HASH (cons)
SUBPARTITIONS 2
(PARTITION hsc_p0 VALUES LESS THAN (10000) ,
 PARTITION hsc_p1 VALUES LESS THAN (20000) ,
 PARTITION hsc_p2 VALUES LESS THAN (30000) ,
 PARTITION hsc_p3 VALUES LESS THAN (40000) ,
 PARTITION hsc_p4 VALUES LESS THAN (50000) ,
 PARTITION hsc_p40 VALUES LESS THAN (4000000) );

this one doesn't:

CREATE TABLE `hsc` (
    LOTS OF STUFF
) ENGINE=InnoDB  CHARSET=utf8
PARTITION BY RANGE (pid)
SUBPARTITION BY HASH (cons)
SUBPARTITIONS 2
(PARTITION hsc_p0 VALUES LESS THAN (10000) ,
 PARTITION hsc_p1 VALUES LESS THAN (20000) ,
 PARTITION hsc_p2 VALUES LESS THAN (30000) ,
 PARTITION hsc_p3 VALUES LESS THAN (40000) ,
 PARTITION hsc_p4 VALUES LESS THAN (50000) ,
 PARTITION hsc_p5 VALUES LESS THAN (75000) ,
 PARTITION hsc_p6 VALUES LESS THAN (100000) ,
 PARTITION hsc_p7 VALUES LESS THAN (125000) ,
 PARTITION hsc_p8 VALUES LESS THAN (150000) ,
 PARTITION hsc_p9 VALUES LESS THAN (175000) ,
 PARTITION hsc_p40 VALUES LESS THAN (4000000) );

ERROR 1005 (HY000): Can't create table 'hsc' (errno: 1)

Its reproducable by removing the number of partitions and adding them again. it does not have to do anything with the name of the table as i tried various names. there is also enough empty space on the HDD.

/dev/simfs         230G   26G  192G  12% /var/lib/mysql.mnt

There should be no limit on the partitions

http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html    
Maximum number of partitions.  The maximum possible number of partitions for a given table (that does not use the NDB storage engine) is 1024. This number includes subpartitions.

i have increased both open_files

show variables where variable_name LIKE '%open_files%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 512   |
| open_files_limit  | 1536  |
+-------------------+-------+

No change. Any clues where should I start looking?


UPDATE: the whole thing is running in an openvz environment. i saw in users_beancounters that the numflock was a problem, so i increased it. but the problem still persists.

maybe this helps:

ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 515011
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 515011
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

cat /proc/user_beancounters

Version: 2.5
       uid  resource                     held              maxheld              barrier                limit              failcnt
      200:  kmemsize                  9309653             13357056             14372700             14790164                    0
            lockedpages                     0                 1008                 2048                 2048                    0
            privvmpages                675424               686528              1048576              1572864                    0
            shmpages                       33                  673                21504                21504                    0
            dummy                           0                    0  9223372036854775807  9223372036854775807                    0
            numproc                        49                   90                  240                  240                    0
            physpages                  243761               246945                    0  9223372036854775807                    0
            vmguarpages                     0                    0              1048576              1048576                    0
            oomguarpages                81672                83305              1048576              1048576                    0
            numtcpsock                      6                    8                  360                  360                    0
            numflock                      175                  188                  512                  512                    8
            numpty                          1                    9                   16                   16                    0
            numsiginfo                      0                   48                  256                  256                    0
            tcpsndbuf                  104640               263912              1720320              2703360                    0
            tcprcvbuf                   98304               131072              1720320              2703360                    0
            othersockbuf                32368                89304              1126080              2097152                    0
            dgramrcvbuf                     0                 2312               262144               262144                    0
            numothersock                   19                   28                  360                  360                    0
            dcachesize                2285052              3624426              3409920              3624960                    0
            numfile                       616                  870                 9312                 9312                    0
            dummy                           0                    0  9223372036854775807  9223372036854775807                    0
            dummy                           0                    0  9223372036854775807  9223372036854775807                    0
            dummy                           0                    0  9223372036854775807  9223372036854775807                    0
            numiptent                      24                   24                  128                  128                    0

© Server Fault or respective owner

Related posts about mysql

Related posts about openvz