Troubleshooting MySQL table errors

<yambe:breadcrumb self="Troubleshooting MySQL table errors">Mariadb configuration | Mariadb 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:

1. Login to mysql command line tool and select db
2. Check table using 'CHECK TABLE <table-name>'
3. 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.)

Table ‘mysql.servers’ doesn’t exist error

If you receive 'Table ‘mysql.servers’ doesn’t exist' error then perhaps /var/lib/mysql/mysql folder does not has any files with name server.*. This can be caused to due to filesystem problems or sometimes during migrating to a newer version of MySQL. To fix this use:

mysql_upgrade -u root -p
service mysqld restart

Steps learned from http://itfixed.blogspot.in/2013/01/table-mysqlservers-doesnt-exist.html

<yambe:breadcrumb self="Troubleshooting MySQL table errors">Mariadb configuration | Mariadb configuration</yambe:breadcrumb>