By elly on

Hiya. MySQL died, saying too many connections. After a shaky restart (had to kill mysql, clean kill, not kill -9), Drupal is giving me tons of these session database errors:

# user warning: Can't open file: 'cache.MYI'. (errno: 145) query: SELECT data, created, headers, expire FROM cache WHERE cid = 'filter:1:149a9c41dd5a81b21e4252fe12a153c8' in /var/www/robin.eff.org/drupal/includes/database.mysql.inc on line 120.

I'm going to restore the db from backup, but I'd really like to understand what this error is and why it happens, and if there is a better way to fix it than restoring the db from backup.

thanks!

-elly

⋅ Categories: Drupal 4.7.x

Comments

elly’s picture

elly commented

it seems many problems were caused by disk filling up on my dev machine.

asb’s picture

asb commented

> it seems many problems were caused by disk filling up on my dev machine.

I just experienced a similar problem; instead of Drupal, the browser delivers:

Fatal error: Can't open file: 'sessions.MYI'. (errno: 145) query: SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = '3a8429bc281752c4f0ab8111a9cbb321' AND u.status < 3 LIMIT 0, 1 in /var/www/portal/includes/database.mysql.inc on line 66

In my case, the disk has enough space (used: 35%, available: 48G according to df -h).

However, the error seems to affect even mysqldump:

# mysqldump -AaCceQ -root -p -r 2006-07-17.dbs
mysqldump: Can't get CREATE TABLE for table `cache` (Can't open file: 'cache.MYI'. (errno: 145))

Toold like mysqlcheck will report something like:

drupal.cache
warning  : 1 client is using or hasn't closed the table properly
error    : Found key at page 51200 that points to record outside datafile
error    : Corrupt
drupal.sessions
warning  : 1 client is using or hasn't closed the table properly
error    : Found key at page 42545152 that points to record outside datafile
error    : Corrupt

A reboot usually doesn't solve the problem, the table remains corrpupt. With mysqlcheck, however, the chances are good, that the database can be repaired. The appropriate options are e.g.

mysqlcheck -repair -extended --all-databases -uroot -p

Also, there is an option "-auto-repair" (check mysqlcheck --help and the manpage). In my case, the problem was gone after two runs of mysqlcheck.

Regards, -asb

mediameriquat’s picture

mediameriquat commented

I had this kind of error messages because the cache, sessions and watchdog tables were corrupted.

In PHP My Admin, I manually deleted them.

Then I recreated the missing tables by exporting the "structure" (not the data) of said tables from another website's database (of the same Drupal version) so I could make the relevant SQL query in the faulty database.

I lost 2 weeks of watchdog data, which is no big deal. Of course this cannot be done if corrupted tables contain important information.

jetsetter’s picture

jetsetter commented

I also had this happen in my Drupal installation. I was pretty worried. I used the database repair instructions on this page, (although i just repaired every table)

http://paulstamatiou.com/2006/05/31/how-to-quickie-repair-mysql-tables-i...

It worked for me.

tobias’s picture

tobias commented

lesson learned about the need to turn off statistics. I'll be relying on google analytics.

cheers,

tobias

planctus’s picture

planctus commented

on inkscapegallery...it happened unexpectedly
but the solution here worked at the first time...
i've one situation more with mysql...it cause me to clear the cache_views table everytime i do domething like changing blocks or enabling modules...
maybe this will work with that too!
Thanks
Da.