Resolving MySQL Workbench 8.0 Database Export Error: Unknown table 'column_statistics' in information_schema

Problem Background and Error Analysis

When using MySQL Workbench 8.0 for database export operations, users frequently encounter the following error message:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,
JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'mydb' AND TABLE_NAME = 'courses';
': Unknown table 'column_statistics' in information_schema (1109)

The core cause of this error is version compatibility issues. The mysqldump tool in MySQL 8.0 enables the --column-statistics option by default, which queries the information_schema.COLUMN_STATISTICS system table to collect column statistics. However, this system table doesn't exist in MySQL 5.7 and earlier versions, causing the query to fail.

Primary Solution: Disabling Column Statistics via GUI

Based on community-verified best practices, in MySQL Workbench 8.0.13 and later versions, the problem can be resolved through the following steps:

1. Navigate to the Management menu and select Data Export
2. Choose the target database schema in the Tables to Export list
3. Click the Advanced Options... button in the top-right corner
4. Search for column-statistics in the search box
5. Set the value of the Other/column-statistics option to 0
6. Click the Return button to save settings and proceed with export

This method's advantage lies in its intuitive operation, requiring no modification of system files or configuration files. However, note that this setting reverts to default when MySQL Workbench restarts, so it needs to be reconfigured each time you perform an export.

Alternative Solution Comparison

Method 1: Modifying mysqldump Configuration File

By adding the following content to the MySQL configuration file, column statistics can be permanently disabled:

[mysqldump]
column-statistics=0

Configuration files can be placed in the following locations:

• System-level: /etc/my.cnf (Linux) or C:\ProgramData\MySQL\MySQL Server 8.0\my.ini (Windows)
• User-level: ~/.my.cnf (Linux) or %USERPROFILE%\my.ini (Windows)

This approach provides a permanent fix but requires basic understanding of MySQL configuration files.

Method 2: Specifying Older mysqldump Path

In MySQL Workbench settings, you can manually specify using an older version of the mysqldump tool:

1. Go to the Edit menu and select Preferences
2. Switch to the Administration tab
3. In the Path to mysqldump Tool field, enter the full path to mysqldump.exe from MySQL 5.7 or earlier
4. Click OK to save settings

Example: C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe

Method 3: Modifying Workbench Source Code (Advanced)

For technically proficient users, modifying MySQL Workbench's Python source code can force-disable column statistics:

# Locate file: C:\Program Files\MySQL\MySQL Workbench 8.0 CE\modules\wb_admin_export.py
# Find this line:
skip_column_statistics = True if get_mysqldump_version() > Version(8, 0, 2) and self.owner.ctrl_be.target_version < Version(8, 0, 0) else False
# Modify to:
skip_column_statistics = True

Important Note: Do not add any comments when modifying, as this may cause syntax errors. Restart MySQL Workbench after modification for changes to take effect.

Technical Principle Deep Dive

The column statistics feature introduced in MySQL 8.0 aims to optimize query performance by collecting statistical information about table columns to help the query optimizer make better execution plan decisions. The information_schema.COLUMN_STATISTICS system table stores this statistical information, including histogram data, number of distinct values, etc.

When mysqldump uses the --column-statistics option, it attempts to query this system table. If the target MySQL server version is below 8.0, the system returns error code 1109, indicating an unknown table.

From a programming perspective, MySQL Workbench includes version detection logic when building command-line parameters for calling mysqldump. When detecting that the mysqldump version is above 8.0.2 and the target server version is below 8.0.0, it should theoretically automatically add the --skip-column-statistics parameter. However, in some cases, this logic may fail, causing the problem to occur.

Best Practice Recommendations

Based on different usage scenarios, we recommend the following solutions:

• Temporary Export Needs: Use the GUI method to set column-statistics=0 in advanced options
• Frequent Export Requirements: Modify the MySQL configuration file to permanently disable column statistics
• Mixed Environments: Consider upgrading all MySQL servers to version 8.0 or above to fundamentally resolve compatibility issues
• Development Environments: The source code modification method can be used, but note that MySQL Workbench version updates may require re-modification

Regardless of the chosen method, it's recommended to back up relevant configuration files before making changes to prevent unexpected situations. Understanding the technical principles behind these solutions helps quickly identify and resolve similar compatibility issues when encountered.