2

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)

Update:

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.

Howard
  • 2,135
  • 13
  • 48
  • 72
  • when I restart using command -- using what command? – chx Nov 06 '11 at 05:54
  • sudo /etc/init.d/mysql restart – Howard Nov 06 '11 at 06:00
  • What's the hard filehandle limit for the mysql user? (You can check by su-ing to mysql and running "ulimit -Hn".) – Mike Scott Nov 06 '11 at 07:14
  • Could this simply be because you have two instances of MySQL installed, and one of them fails to start at boot? – Kvisle Nov 06 '11 at 11:58
  • @MikeScott, MySQL user does not have a shell, so I tried to edit the /etc/init.d/mysql script, and put ulimit -n 10000 at the beginning, does not help either. – Howard Nov 07 '11 at 14:47
  • @Kvisle, confirmed I am running only one instance of MySQL. – Howard Nov 07 '11 at 14:50
  • add the mysql user into a group that owns the directory where the data files are located and give permission to group for both directory and the files. – A.Rashad Nov 10 '11 at 05:53
  • http://serverfault.com/questions/168957/changing-mysql-data-directory-in-ubuntu-server-10-04 – quanta Nov 10 '11 at 10:58
  • Update: Problem solved by removing a large number of tables, so it should be something related to the file limit, not permission. – Howard Nov 10 '11 at 14:25
  • It might be any other ulimit than number of files, such as data seg size, max memory size, stack size, virtual memory .. – Antti Rytsölä Nov 12 '11 at 09:44

3 Answers3

6

The problem is not the limit on file handles. That would be error 24, not 13. The problem is what it says: permissions. I've seen this dozens of times, and even when people think that everything is set up correctly and has the right permissions, it IS permissions, every time, and someone always figures out that they missed something :) You either have permissions set wrong or you have something like the following:

  • There is a weird permission set on a higher level directory, such as / being restricted. (This sometimes matters.)
  • You have another process, such as two instances of mysqld, running and interfering with privileges.
  • SELinux or AppArmor is interfering.

You "solved" the problem by removing a large number of tables -- I don't think this is really a solution. You probably just removed some tables that had wrong permissions set on them :)

  • If it is due to the permission issue, the MySQL it will not able to start when I login into the shell and restart it manually. It only halt at boot time or I need to delete tables to make it start during boot. Anyway, I can restart manually using command everytime. – Howard Nov 13 '11 at 13:45
  • +1 for the 'having another process running' line. I killed that process and this problem disappeared. – allesklar Mar 25 '15 at 12:26
1

I'd recommend you to check write permission for DB user in the directory where your DB files are located.

splattne
  • 28,508
  • 20
  • 98
  • 148
Michael
  • 80
  • 3
1

Is this an AppArmor issue? It could be something like AppArmor being started or killed between when you reboot and when you are trying to start it manually.

Check your syslog around the same times.

crb
  • 7,998
  • 1
  • 38
  • 53
  • Update: Problem solved by removing a large number of tables, so it should be something related to the file limit, not permission. – Howard Nov 10 '11 at 14:25