Mysql not recognized table – Repair Corrupted Tables in MySQL

Sometimes MySQL tables may get damaged, implying that a fault has developed, and information stored inside them is not readable. The server crashes whenever a corrupted table is read; this is a common scenario. Use these steps to fix corrupt tables in Mysql.

First of all, let’s stop the MySQL service:

sudo systemctl stop mysql

After that, create a secondary backup folder and transfer all the information there. Ubuntu’s standard info storage place is /var/lib/mysql/:

cp -r /var/lib/mysql /var/lib/mysql_bkp

After the backup has been done, you are then allowed to proceed with attempting to determine if the table is indeed corrupted. For tables that employ MyISAM as their storage engine, its status can be checked by typing ‘CHECK TABLE‘ at the MySQL prompt and with restarting MYSQL.

sudo systemctl start mysql

CHECK TABLE table_name;

This statement will return a message showing if there’s or not it’s corrupted. If a MyISAM table is corrupted, it is usually possible to restore it by performing a REPAIR TABLE operation:

REPAIR TABLE table_name;

If the repair was successful, you will observe a message such as this in the output:

status | OK

In contrast, if the damaged table uses the InnoDB storage engine, there will readily be a change in tactics. Since the introduction of version 8.0 of MySQL, InnoDB has been the default storage engine and it comes with built-in self-checking as well as self-fixing properties. InnoDB performs checks on every page it reads and if InnoDB detects any checksum errors, it will promptly crash the MySQL server.

There is hardly a scenario that warrants any InnoDB table repairs. This is due to the sturdiness of the InnoDB engine with its built-in crash recovery feature. Should you find yourself in a case when you have to reconstruct a damaged InnoDB table, then Lockwood has illustrated several ways on ‘Repairing InnoDB Tables’ and recommends using the “Dump and Reload” way. This means that it is possible to recover the damaged table, to msqldump but in utilising this program, only the empty table structure and/ or some of the data which has been active is preserved, the table will be later loaded into the database again.

Keeping this in view, let us now check if restarting the MySQL service will give us access to the server.

sudo systemctl restart mysql

Should the server stay in a failed state or otherwise unreachable, it can be of use to turn on InnoDB’s force_recovery option. N this file, insert lines for your configuration by editing /etc/mysql/mysqld.cnf. Typically in Ubuntu and Debian, this file is located in etc/mysql. While on Red Hat and Rocky systems, this file is mostly located in /etc/my.cnf.d.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

After that, append the lines below under the [mysqld] section in the configuration file.

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
. . .
innodb_force_recovery=1

Now save the file then try again starting the MySQL service. In case you are able to access the damaged table, make use of the mysqldump tool to back up your table contents into a different file. You can call this file anything that you want, however we will refer this as out.sql for demonstration purposes:

mysqldump database_name table_name > out.sql

Then the table should be dropped from the database. In order to not have to open the MySQL prompt again, the following syntax can be employed:

mysql -u user -p --execute="DROP TABLE database_name.table_name"

After this, use the dump file that you just created to restore the table:

mysql -u user -p < out.sql

Conclusion

For any organization that uses its database for important tasks, ensuring data accuracy and consistency in MySQL is of utmost importance. The next approach to better secure MySQL databases is understanding the intricacies of the table damage as well as the Table Not Recognized error. Applying such steps as routine backups, hardware supervision, and optimization tasks can lower threats of table being corrupted greatly.