MySQL Database Export Errors and Solutions

I recently encountered a couple of frustrating MySQL database export errors and wanted to share the solutions I used with other folks, as well as document it for future me.

Here’s my setup. These issues could appear on any platform, though.

• OS: Windows 10 Pro
• Local web stack: Local Lightning v5.90
• Database client: MySQL Workbench v8.0.22

Issue 1: MySQL database export error due to client/server version mismatch

The first error I ran into when trying to export a DB was:

Unknown table 'column_statistics' in information_schema (1109)

TL;DR: Make sure that the version of mysqldump that your MySQL client (MySQL Workbench in my case) points to is the same as the DB version as what is used by the server. (You know, like the warning message suggests when you try and do otherwise.) Use MySQL v5.7 or v8 when setting up a site in Local by Flywheel rather than using Maria DB.

My Local setup was configured to use MariaDB v10.4.10. When setting the site up in MySQL Workbench (my preferred MySQL client), I was presented with the following warning when testing the connection:

I made a mental note of the warning, but decided to proceed anyway. I didn’t have any problems running queries, so I didn’t think about it again.

To export a DB using MySQL Workbench, connect to a DB server, then navigate to Server > Data Export.

When attempting the export, a dialogue showing the following warning indicating a version mismatch was displayed:

The export was not successful. Here’s the full log of the error:

11:52:38 Dumping local (all tables)
Running: mysqldump.exe --defaults-file="c:usersuser1appdatalocaltemptmpnkltww.cnf" --user=root --host=127.0.0.1 --protocol=tcp --port=10142 --default-character-set=utf8 --skip-triggers "local"
mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'local' AND TABLE_NAME = 'wp_commentmeta';': Unknown table 'column_statistics' in information_schema (1109)
Operation failed with exitcode 2
11:52:38 Export of D:devlocal-sitesmysqlexporttestmariadbmysqlexporttestmariadb-2020-11-16.sql has finished with 1 errors

Issue 1, Solution 1

I did some Googling, and found this helpful post on ServerFault that explained a workaround for Windows/Mac/Linux.

The solution is to create a script that runs mysqldump with the flag --column-statistics=0, then configure Workbench to point to the script:

Windows

mysqldump_nostatistics.cmd

@ECHO OFF
"C:Program FilesMySQLMySQL Workbench 8.0 CEmysqldump.exe" %* --column-statistics=0

Mac/Linux

mysqldump_nostatistics.sh

#!/bin/sh
_mysqldump $@ --column-statistics=0

This allowed me to successfully create a an export of the DB, but the dialogue indicating a MySQL version mismatch was still displayed:

Issue 1, Solution 2

I followed the dialogue’s guidance and configured MySQL Workbench’s settings to point to the version of mysqldump provided with the MariaDB version used by Local.

Edit > Preferences > Administration > Path to mysqldump Tool:

C:Program Files (x86)LocalresourcesextraResourceslightning-servicesmariadb-10.4.10+4binwin32binmysqldump.exe

I then tried to export the DB again, and finally it worked without any errors and without needing to pass additional parameters to the client:

11:59:54 Dumping local (all tables)
Running: "C:Program Files (x86)LocalresourcesextraResourceslightning-servicesmariadb-10.4.10+4binwin32binmysqldump.exe" --defaults-file="c:usersuser1appdatalocaltemptmp_mjmqv.cnf" --user=root --host=127.0.0.1 --protocol=tcp --port=10142 --default-character-set=utf8 --skip-triggers "local"
11:59:55 Export of D:devlocal-sitesmysqlexporttestmariadbmysqlexporttestmariadb-2020-11-16.sql has finished

Issue 2: MySQL export fails due to user not having PROCESS privileges

The second error I encountered was when I tried to make a backup of a remote database. In this case, I was not using the root user. The error was:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

The error would also happen when connecting via the WP CLI:

$ wp db export
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

When searching for a solution for this error, I found this comprehensive answer on the DBA StackExchange site, which explains that the issue crops up due to a security-related breaking change added to MySQL minor updates in v5.7.31 and v8.2.21.

This issue does not affect the root user because it has inherently has all privileges. To resolve the problem, log into the the MySQL server and grant the PROCESS privilege to the affected user:

GRANT PROCESS ON *.* TO someuser@localhost;
FLUSH PRIVILEGES;

After the above commands were run on the server, I could once again make backups, and I was out of this rabbit hole.

Don’t get stuck in your own rabbit hole. Use my recommended solutions, or contact our team to help dig you out of any holes or trenches where you’re currently trapped.