If you are using MySQL and dealing with the Unknown Table 'column_statistics' error (1109) while executing a query on INFORMATION_SCHEMA, you are in the right place. This post provides a step-by-step guide on how to fix this error.
When executing a query such as:
SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
The server throws the following error message:
 Unknown table 'column_statistics' in information_schema
The root cause of this error is that the
COLUMN_STATISTICS table does not exist in
INFORMATION_SCHEMA. This is because the
COLUMN_STATISTICS table was introduced in version 8.0 of MySQL, while the previous version was 5.7.
The solution is to upgrade your MySQL version to the latest version 8.0. After upgrade, you will be able to query the
COLUMN_STATISTICS table. The steps to upgrade MySQL is given below:
Backup existing database. This can be done by creating a dump by executing
mysqldump database_name > database_name.sql
Download the latest version of MySQL from the official MySQL website.
Install the downloaded version of MySQL. Make sure to use the same configuration settings as the existing version.
Import the backup database into the new version. This can be done by running
mysql -u root -p database_name < database_name.sql
The database should now have been imported into the new version of MySQL and the error should have been resolved.
Q1: Is it possible to maintain the same version of MySQL and still fix this issue?
A1: No, this issue can only be fixed by upgrading MySQL to version 8.0 or later.
Q2: Is it safe to upgrade MySQL version to 8.0?
A2: Yes, upgrading to 8.0 is generally safe and doesn’t require any major change. However, it is always a good idea to backup your existing database before upgrading, as an extra precaution.