CentOS 6.3 / VM Pro
I just migrated to a new server on Saturday. On both nights since, mySQL stopped responding during the nightly backup and didn't start again until I did a mySQL restart.
The backup of a high percent of virtual servers fail with this error:
Dumping MySQL database fxxxxxxxxxr_joomla ..
.. dump failed! mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_1332_2.MYI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'fxxxxxxxxxr_joomla'': Out of resources when opening file '/tmp/#sql_1332_0.MYI' (Errcode: 24) (23)
Do I need to increase some resource to fix?
Brian
Howdy -- MySQL's error code 24 means that it received a "Too many files open" error.
What we would need to do is configure MySQL to allow more open files.
One way to do that would be to edit /etc/init.d/mysql, and a few lines down, you'll see a line that looks like this:
exec="/usr/bin/mysqld_safe"
Right above that line, add a line that looks like this:
ulimit -n 16384
And then restart MySQL:
/etc/init,d/mysql restart
That will allow a lot more open files than the default, and should prevent that error that you're seeing.
Hi, thanks for this - that worked for me this morning.. last night I've done huge update on one of my dev servers running debian where database is around 5gigs and had 24 issue after update.
Configuring/troubleshooting Debian servers is always great fun
Perfect! I knew it would be something like that.
Actual the file is "mysqld"
I'm going to do a manual backup now and will confirm this fixed it.
Thank you, Brian
Bingo - That did it!
210 servers backed up successfully, 0 had errors. 4 Virtualmin configuration settings backed up successfully. Backup is complete. Final size was 19.24 GB.
Thank you very much!
Looks like a recent update of mySQL replaced the mysqld file, wiping out my modification. Is there a better way to do this so it is permanent or do I just need to make a mental note to add the extra line anytime I see that mySQL gets updated?
Brian
The reason why is this happening is because MySQL 5.5 provided an update to the schemas, which are not automatically applied to already existing databases. To provide that schema update, it's important to run the MySQL upgrade command, as it also helps with missing tables or prefixed tables.
Just run this
mysql_upgradeIf you're getting an access denied error, pass along the username + password.
mysql_upgrade -u root -pI just wonder will applying the following line to /etc/my.cnf file will save us from modifications in /etc/init.d/mysql getting replaced with every MySQL update:
open-files-limit = 6144because my.cnf doesn't get changed with MySQL updates/upgrades. Is it the same thing or not?
Well, there may very well be a better way of doing that... and I'm open to suggestions! I'm not thrilled about that method either :-)
The issue I've run into is that in many cases, setting the value in MySQL's my.cnf won't help, as the open files limit is actually set elsewhere, and is lower than that.
For example, notice the limits for your root user... run "ulimit -a".
Many systems, by default, have a limit of 4096 open files. And whenever your root user restarts MySQL, MySQL will inherit that 4096 limit.
So, even if you set MySQL's "open-files-limit" to a higher number, that won't always solve the problem -- as it can't be higher than the ulimit value.
Feel free to tinker around with that though, if you find a better way to set the open files limit, let us know!
-Eric
I'm getting a slightly different issue which I thought might have been related:
The backup dumps the two user generated databases then shows: Dumping MySQL database performance_schema .. .. dump failed!
mysqldump: Couldn't execute 'show events': Access denied for user 'root'@'localhost' to database 'performance_schema' (1044)
Any ideas as to what might cause this as I can't see anything weird in the syslog?
Thanks in advance. Karu
We've seen that error after upgrading to a non-standard MySQL version... are you by chance using MySQL from a third party repository?
-Eric
No it was using all the normal virtualmin install scripts for debian that we've done so many times in the past. You've got me interested to know how you identify if mysql is a standard version or not, can you tell on a running instance?
Thanks, Karu
What I'd do is compare the package names/versions to what comes standard in Debian.
For example, what output do you see if you run this command:
dpkg -l 'mysql*'Then, if you go to packages.debian.org, you can see what the package name and version if for that particular distro/version.
-Eric
Everything matches the debian.org repository. 5.5.31+dfsg-0+wheezy1
Hope that helps?