Friday, December 19, 2014

XtraBackup error "InnoDB: Operating system error number 24 in a file operation."

This morning, I tried to backup the MariaDB database using xtrabackup:

$ xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/


and ran into this error:

...
InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file
InnoDB: ./roei_cc/transport.ibd!
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
...

This is because Linux / UNIX sets soft and hard limit for the number of file handles and open files. So, the solution is to increase the limit:

* First, check the current limit:

$ ulimit -n
1024

* Increase the limit until you get no error:

$ ulimit -n 8192