mySQL Errors

More lovely mySQL errors. My sites using mySQL show a bunch of mySQL errors, but when I restart mySQL the errors go away. I’m trying to restore a site now, but before it can create the mySQL user I get:
Restore failed : SQL select password from user where user = ‘urbanwar’ failed : Can’t open file: ‘./mysql/user.frm’ (errno: 24)

Backing up my servers gives me this:

Backup failed : SQL select user.user,user.password from user,db where db.user = user.user and (db.db = ‘getmw3free’ or db.db = ‘getmw3free’) failed : Can’t open file: ‘./mysql/user.frm’ (errno: 24)

After just a few seconds of my sites working it can’t connect to the database. This started happening after I tried restoring a server and database and realized I had to quit and go move the tmp directory since the partition was too small.

After restarting mySQL from the command line I got an error for every single database file saying it can’t open it.

I also recently installed php5-memcache and memcached, although that only affect Apache I think and I tried uninstalling which didn’t work.

Trying to repair databases:

mysqlcheck: Error: Couldn’t execute ‘SHOW DATABASES’: Can’t read dir of ‘.’ (errno: 24)

Howdy,

After a little Googling, it looks like MySQL’s “Error 24” means “Too many open files”.

What you may want to try is edit /etc/init,d/mysql, and somewhere after the first line, add this:

ulimit -n 16384

And then restart MySQL with "/etc/init.d/mysql restart’.

-Eric

Thanks, should I fix whatever’s opening too many files for a real solution or is just allowing more files open ok? Also I’m getting a max_allowed_packet error when I restore a Virtualmin server. I raised the max_allowed_packet variable to 1000M after trying lower numbers and it’s still giving me that error.

It’s no problem to increase the max open files number. That may just mean you have a lot of databases on your system.

As far as the max_allowed_packet – after changing that setting in your my.cnf file, make sure that you restart MySQL.

Also, how large is the database that you’re trying to restore?

-Eric

I tried restarting it after changing it many times. The database is about 2GB I think.

Oops, found the problem. I had created a new entry to set the max_allowed_packet when one already existed below it and I simply had to change the number.

I better repair and optimize my DB after I get it on this new more powerful server.

It’s a dual quad core Nehalem E5506 with 12GB RAM. What would be the best way to attempt a repair on a 2GB database?

Great, I’m glad you got that fixed up!

-Eric

What would be the best way to attempt a repair on a 2GB database?

Well, a repair is only necessary if you’re running into problems – are you seeing errors of some sort?

-Eric