Bug Report: Incompatible queries when using `mysqldump` from `percona-server-client`

[hkdsun]

Overview of the Issue

When dumping a database structure file with mysqldump provided by Percona Server's client and then later loading it into the database, I seem to be running into incompatible queries.

There are some version-dependent MySQL comments that throw a "Query was empty" error:

ERROR 1065 (42000) at line 12 in file: 'schema.sql': Query was empty

Line 12 is as follows:

/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;

I ran into this when running Ruby-on-Rails schema dump operations. Specifially:

• Ran SCHEMA_FORMAT=sql rails db:schema:dump
• Followed by SCHEMA_FORMAT=sql rails db:schema:load:

Reproduction Steps

1. Create the following docker-compose file:

# docker-compose.yml

version: "3.0"
services:
  vttest_dev:
    build:
      context: /path/to/vitess/docker/vttestserver
      dockerfile: Dockerfile.mysql57
    command:
      - "/vt/bin/vttestserver"
      - "--alsologtostderr"
      - "--port=2222"
      - "--mysql_bind_host=0.0.0.0"
      - "--num_shards=1"
      - "--keyspaces=test-app"
      - "--vschema_ddl_authorized_users=%"
      - "--enable_system_settings=false"

2. Run the compose cluster:

podman-compose up

3. In another terminal, open the container that was just created:

podman exec -it <container name> bash

5. Download percona-server-client tarball binary and extract it:

cd /vt
mkdir percona && cd percona
wget https://repo.percona.com/apt/pool/main/p/percona-server-5.7/percona-server-client-5.7_5.7.42-46-1.focal_amd64.deb
dpkg -x percona-server-client-5.7_5.7.42-46-1.focal_amd64.deb ./
./usr/bin/mysqldump --version

6. Execute mysqldump from the percona package:

./usr/bin/mysqldump --no-create-db --no-tablespaces --lock-tables=off --set-gtid-purged=off --host=127.0.0.1 --port=2225 --user=root --default-character-set=utf8 --result-file schema.sql --no-data --routines --skip-comments test-app@primary

7. Ensure erroneous comments are present (if not, double-check that you are executing the percona mysqldump and not the plain/oracle package):

cat schema.sql  | grep @rocksdb_has_p_s_session_variables

8. Try loading the schema dump into vtgate as Rails would do and observe the error:

vitess@d29028147b2d:/vt/percona$ mysql --init-command='SET ENABLE_SYSTEM_SETTINGS = true; SET TRANSACTION_MODE = multi;' --host=127.0.0.1 --port=2225 --user=root --default-character-set=utf8 --execute 'SET FOREIGN_KEY_CHECKS = 0; SOURCE schema.sql; SET FOREIGN_KEY_CHECKS = 1' --database test-app@primary

ERROR 1065 (42000) at line 12 in file: 'schema.sql': Query was empty

Binary Version

Version information:

Rails 7.0.4.3:

 -> rails --version                                                                                                                                                                                                                                                     
Rails 7.0.4.3

Vitess 15.0.3:

vitess@afc3ac25a617:/vt/percona$ /vt/bin/vttestserver --version
Version: 15.0.3 (Git revision f3899036152acc676612494938a6c1f48e3e5068 branch 'HEAD') built on Tue Apr 18 20:21:46 UTC 2023 by vitess@buildkitsandbox using go1.18.9 linux/amd64

MySQL 5.7.31:

vitess@afc3ac25a617:/vt/percona$ mysqld --version
/usr/sbin/mysqld  Ver 5.7.31 for Linux on x86_64 (MySQL Community Server (GPL))

Percona Server Client 5.7.42-46:

vitess@afc3ac25a617:/vt/percona$ ./usr/bin/mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.42-46, for debian-linux-gnu (x86_64)

Operating System and Environment details

vitess@d29028147b2d:/vt/percona$ uname -sr
Linux 6.1.14-200.fc37.aarch64
vitess@d29028147b2d:/vt/percona$ cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"
vitess@d29028147b2d:/vt/percona$ uname -m 
x86_64

Log Fragments

No response

[mattlord]

Thank you, @hkdsun!

I'm able to repeat the problem this way on my laptop:

brew install percona-server@8.0

brew services start percona-server

mysql -u root -e "create database dumptest; create table dumptest.t1 (id int); insert into dumptest.t1 values (1), (2), (3)"

/opt/homebrew/opt/percona-server/bin/mysqldump -u root dumptest > /tmp/percona-dump.sql

brew services stop percona-server

git checkout main && make build

cd examples/local && ./101_initial_cluster.sh

command mysql --no-defaults -h 127.0.0.1 -P 15306 commerce < /tmp/percona-dump.sql

With the final output being:

❯ command mysql --no-defaults -h 127.0.0.1 -P 15306 commerce < /tmp/percona-dump.sql
ERROR 1105 (HY000) at line 17: syntax error at position 178 near 'rocksdb_has_p_s_session_variables'

From the percona-server's mysqldump source, it prints this whether the MyRocks storage engine is installed and enabled or not: https://github.com/percona/percona-server/blob/8.0/client/mysqldump.cc#L888-L913

/cc @vitessio/query-serving and @vitessio/cluster-management