Difference between revisions of "Troubleshooting MySQL table errors"

From Notes_Wiki
m
m
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Troubleshooting table errors=
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Mariadb configuration|MariaDB configuration]] > [[Troubleshooting MySQL table errors|Troubleshooting table errors]]


==Table not found or file not found errors==
==Table not found or file not found errors==
Line 22: Line 22:




Back to [[Mysql configuration]]
==Crashed table error==
 
If you see following error message:
<pre>
is marked as crashed and should be repaired when using LOCK TABLES
</pre>
then to repair the table use following steps:
#Login to mysql command line tool and select db
#Check table using '<tt>CHECK TABLE &lt;table-name&gt;</tt>'
#Repair table using '<tt>REPAIR TABLE &lt;table-name&gt;</tt>'
 
Example I/O is:
<pre>
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)
</pre>
 
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 '<tt>Table ‘mysql.servers’ doesn’t exist</tt>' 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:
<pre>
mysql_upgrade -u root -p
service mysqld restart
</pre>
 
Steps learned from http://itfixed.blogspot.in/2013/01/table-mysqlservers-doesnt-exist.html
 
 
 
 
[[Main Page|Home]] > [[CentOS]] > [[CentOS 6.x]] > [[Mariadb configuration|MariaDB configuration]] > [[Troubleshooting MySQL table errors|Troubleshooting table errors]]

Latest revision as of 02:53, 5 March 2022

Home > CentOS > CentOS 6.x > MariaDB configuration > 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



Home > CentOS > CentOS 6.x > MariaDB configuration > Troubleshooting table errors