Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

mysqldump stops with Unknown table 'COLUMN_STATISTICS' in information_schema

ErrorUnknown table 'COLUMN_STATISTICS' in information_schema (1109)

Short answer

The mysqldump client is newer than the server it dumps. A MySQL 8.0 client asks for information_schema.COLUMN_STATISTICS, a histogram table that MySQL 5.7 and MariaDB do not have, so the server answers error 1109. Add --column-statistics=0 to the command, or use a client that matches the server.

  • Applies to mysqldump 8.0 client, MySQL 5.7 server, MariaDB 10.x server, Linux
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Compare the version of the client you run with the version of the server it connects to. A client at 8.0 and a server below it is the pattern.

    mysqldump --version
    mysql -h 127.0.0.1 -u root -p -e "SELECT VERSION();"
  2. 2Match what you see, then jump to its fix

    1. 1Unknown table 'COLUMN_STATISTICS' in information_schema (1109), and the server reports a 5.x versionA MySQL 8.0 client is dumping a MySQL 5.x serverFix
    2. 2The same error 1109 with a MySQL 8 mysqldump binary against a MariaDB hostThe server is MariaDB, which has no such tableFix
    3. 3The error appears from a backup job, an export button or a framework command, not from a command you typedA tool starts mysqldump for you and you cannot add the flagFix

What does the error look like?

  • Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
  • Unknown table 'column_statistics' in information_schema (1109)

Cause 1 of 3

A MySQL 8.0 client is dumping a MySQL 5.x server

1Check

COLUMN_STATISTICS holds column histograms, a feature that came with MySQL 8.0. When it dumps, the 8.0 client queries that table for each dumped table. A 5.x server has no such table, so the query fails with error 1109.

mysqldump --version
mysql -h 127.0.0.1 -u root -p -e "SELECT VERSION();"

2Fix

Tell the client not to look at column statistics. The dump then contains the same data and schema, only without the histogram statements. If you can, run a client whose version matches the server instead.

mysqldump --column-statistics=0 -h 127.0.0.1 -u root -p mydb > mydb.sql

3Verify

The command ends without an error and the file has CREATE TABLE and INSERT statements.

grep -c "CREATE TABLE" mydb.sql

Confirmed by bugs.mysql.com

Did this fix it?

Cause 2 of 3

The server is MariaDB, which has no such table

1Check

A MariaDB server has no COLUMN_STATISTICS table to answer the query. A MariaDB ticket, filed against MariaDB 10.3.7, describes exactly this failure with this flag as the workaround. Check which binary the shell runs and what the server calls itself.

mysqldump --version
mysql -h 127.0.0.1 -u root -p -e "SELECT VERSION();"

2Fix

Pass --column-statistics=0 to the MySQL client, or dump with the client that comes with MariaDB. That client is called mariadb-dump now, and mysqldump still works as another name for it on Linux.

mysqldump --column-statistics=0 -h 127.0.0.1 -u root -p mydb > mydb.sql
# or, with the MariaDB client
mariadb-dump -h 127.0.0.1 -u root -p mydb > mydb.sql

3Verify

The dump finishes and the file is not empty.

ls -l mydb.sql

Confirmed by jira.mariadb.org

Did this fix it?

Cause 3 of 3

A tool starts mysqldump for you and you cannot add the flag

1Check

Some programs build the mysqldump command line themselves. A bug report for this problem notes that an export screen in MySQL Workbench 8.0.25 had no setting for this option. Option files are another place the client takes options from.

ls -l ~/.my.cnf /etc/my.cnf /etc/mysql/my.cnf

2Fix

Put the option in the [mysqldump] group of an option file that the tool's mysqldump process reads, for example ~/.my.cnf of the user that runs the job. An option file line has no leading dashes. If the tool starts mysqldump with --no-defaults, option files are skipped, and you must change the tool's own settings or call mysqldump from a small wrapper script.

# ~/.my.cnf
[mysqldump]
column-statistics=0

3Verify

Run the tool's export again, or run mysqldump by hand without the flag as the same user.

mysqldump -h 127.0.0.1 -u root -p mydb > /dev/null

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Install a mysqldump that matches the server major version on the machine that runs backups, or pin the flag in a [mysqldump] option file.
  • After upgrading the client package or a Docker image tag, run one test dump before the nightly job relies on it.
  • Keep the client version in the backup script log (mysqldump --version) so a mismatch shows up in the first line you read.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.