Troubleshooting MySQL table errors
<yambe:breadcrumb>Mysql configuration</yambe:breadcrumb>
Troubleshooting table errors
Table not found or file not found errors
Normally mysql database is kept in '/var/lib/mysql' folder. In this folder there is generally one directory for each database. Inside this directory based on table format 'Innodb' or 'MyISM' there are multiple files. If the file extensions are '.frm', '.ism' and '.isd' for each table then '.frm' should be lower case and '.ISM' amd '.ISD' should be in upper case
Error from table handler at row n
If you get error like 'Error from table handler at row n' then you can try from command line
mysqlcheck -r <database_name>
where <database_name> is the name of the database inside which some table is giving error
Access denied/Can't write
- Make sure the temporary directory mentioned in '/etc/my.cnf' exists, is writable and has free space.
- All files, folders and sub-folders inside '/var/lib/mysql' shown be owned by mysql:mysql
Crashed table error
If you see following error message:
is marked as crashed and should be repaired when using LOCK TABLES
then to repair the table use following steps:
- Login to mysql command line tool and select db
- Check table using 'CHECK TABLE <table-name>'
- Repair table using 'REPAIR TABLE <table-name>'
Example I/O is:
mysql> check table sessions; +--------------+-------+----------+-----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+-----------------------------------------------------------+ | web.sessions | check | warning | Table is marked as crashed | | web.sessions | check | warning | 22 clients are using or haven't closed the table properly | | web.sessions | check | error | Checksum for key: 3 doesn't match checksum for records | | web.sessions | check | error | Corrupt | +--------------+-------+----------+-----------------------------------------------------------+ 4 rows in set (2.20 sec) mysql> repair table sessions; +--------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------+----------+----------+ | web.sessions | repair | status | OK | +--------------+--------+----------+----------+ 1 row in set (0.21 sec) mysql> check table sessions; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | web.sessions | check | status | OK | +--------------+-------+----------+----------+ 1 row in set (0.01 sec)
This is useful in case drupal 5.5 shows "You are not authorized to access this page." error for no other good reason (file permissions, cookies, etc.)
<yambe:breadcrumb>Mysql configuration</yambe:breadcrumb>