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)
210 servers backed up successfully, 0 had errors. 4 Virtualmin configuration settings backed up successfully.
Backup is complete. Final size was 19.24 GB.
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?
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_upgrade
If you’re getting an access denied error, pass along the username + password.
I 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 = 6144
because 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!
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?
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.