Fixing Unknown Table 'column_statistics' Error in Information_Schema (1109)

Introduction

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.

Issue Summary

When executing a query such as:

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS 

The server throws the following error message:

[1109] Unknown table 'column_statistics' in information_schema

Root Cause

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.

Solution

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.

FAQ

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.