I have around 6000 InnoDB tables across 15 databases, and using the innodb_file_per_table, I found out when everytime I reboot the Ubuntu server, the MySQL failed to start, with error..
Version: '5.1.56-rel12.7-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Percona Server (GPL), 12.7, Revision 224) 111106 5:09:00 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./db1/tbl182.ibd InnoDB: File operation call: 'open'.
Since the filename of the problematic table (i.e. tbl182.ibd) is not the same everytime, so I suspect is due MySQL is doing something with all the tables?
However, when I restart using command (sudo /etc/init.d/mysql restart), MySQL can be started successfully. This is very strange!
My Env: MySQL version: 5.1.56-rel12.7-log (Ubuntu 10.04 LTS + Percona)
Problem solved by removing a large number of tables, so it should be something related to the file limit, not permission.
I have already changed /etc/init.d/mysql to hard coded value ulimit -n 50000, still not help when I creating massive tables.