mysqldump: Unknown table 'column_statistics' in information_schema – TecAdmin

If you have ever attempted to back up a MySQL or MariaDB database using the mysqldump tool, you might have encountered the error:

mysqldump: Unknown table ‘column_statistics’ in information_schemaWhat Causes This Error?

This error is particularly associated with MariaDB, version 10.2.2 and above. Starting with this version, MariaDB introduced a new table called column_statistics in the information_schema database, which was meant to support some advanced features such as histogram statistics.

The mysqldump utility, when used with certain options, tries to access this table. However, if you are using the MySQL client tools (rather than MariaDB’s tools) to take the dump of a MariaDB database, you might encounter this error. The MySQL version of mysqldump doesn’t recognize this column_statistics table.

How to Solve the Error?

There are multiple ways to resolve this issue, and the right solution often depends on your particular scenario:

1. Use the --column-statistics=0 Option with mysqldump:

The simplest workaround is to turn off the column statistics when using mysqldump. This is done using the --column-statistics=0 option.

mysqldump --column-statistics=0 -u username -p database_name > dump_file.sql

This tells mysqldump not to retrieve column statistic information, effectively bypassing the issue.

2. Use MariaDB’s mysqldump Tool:

If you’re working with a MariaDB database, it makes sense to use the mysqldump tool that comes with MariaDB. This tool is familiar with the column_statistics table and will not throw the error.

If you installed MariaDB from a package manager, you likely have the correct version of mysqldump. Make sure you’re using the correct binary by specifying the full path or checking its version.

3. Upgrade Your MySQL Client Tools:

As software evolves, incompatibilities like this might get addressed in future versions. It’s always a good practice to regularly update your tools and check if the issue has been resolved in a newer version.

4. Avoid Mixing MySQL and MariaDB Tools:

As highlighted by this error, even though MariaDB is a fork of MySQL and is mostly compatible, there are differences that can cause issues. Whenever possible, try to use tools and binaries that match the server version/type you’re using to avoid unforeseen complications.

Conclusion

The 「Unknown table ‘column_statistics’ in information_schema」 error in mysqldump highlights the subtle incompatibilities that can arise between MariaDB and MySQL, despite their shared lineage. Thankfully, with an understanding of the underlying cause, it’s straightforward to remedy the issue.