我目前遇到了一个我不明白的问题。无论我在哪里查看，它都说 mySQL (5.5) / InnoDB 没有表限制。
我想测试 InnoDB 压缩并准备创建现有表的空副本，但遇到了以下问题。
这个有效：
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) );
这个没有：
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)
通过删除分区数并再次添加它们，可以重现此问题。它与表的名称无关，因为我尝试了各种名称。硬盘上也有足够的空闲空间。
/dev/simfs 230G 26G 192G 12% /var/lib/mysql.mnt
分区不应有限制
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.
我已经增加了 open_files
show variables where variable_name LIKE '%open_files%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| innodb_open_files | 512 |
| open_files_limit | 1536 |
+-------------------+-------+
没有变化。有什么线索我应该从哪里开始寻找吗？
更新：整个过程在 openvz 环境中运行。我在 users_beancounters 中看到 numflock 是一个问题，因此我增加了它。但问题仍然存在。
也许这有帮助：
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
猫/ 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
答案1
解决了整件事。
最初的问题是 openvz 中的 numflock。
我增加 numflock 之后出现的错误是基于 mysql 数据目录中一个废弃的文件。
我删除了表格，从表中删除了剩余的文件，然后它就起作用了。