MySQL Bugs: #79197: mysqldbcopy fails with HY000

Bug #79197	mysqldbcopy fails with HY000
Submitted:	10 Nov 2015 0:56	Modified:	10 Nov 2015 10:08
Reporter:	Andrew MacKinlay	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	1.3.5-2	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	mysqldbcopy

Description:
Using utils version 1.3.5-2 and mysql server version 5.7.9, I find it's impossible to execute mysqldbcopy, even from a fresh empty or non-empty DB, instead getting the following error:

ERROR: Query failed. 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'information_schema.TABLES.TABLE_SCHEMA' which is not in SELECT list; this is incompatible with DISTINCT

Adding verbose flags doesn't give me more useful information.

I presume there is something unique about my system which is causing this, but I have no idea what it could be.

How to repeat:
mysql> create database foo1;
Query OK, 1 row affected (0.00 sec)

mysql> use foo1;
Database changed
mysql> create table bar (id int primary key);
Query OK, 0 rows affected (0.04 sec)

# mysqldbcopy --source root:s3kret@localhost:3306 --destination root:s3kret@localhost:3306 -vvvv -f foo1:foo2
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database foo1 renamed as foo2
ERROR: Query failed. 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'information_schema.TABLES.TABLE_SCHEMA' which is not in SELECT list; this is incompatible with DISTINCT

Thank you for the bug report. I could not repeat on CentOS 7:

# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database foo1 renamed as foo2
# Copying TABLE foo1.bar
CREATE TABLE `foo2`.`bar` LIKE `foo1`.`bar`
# Copying data for TABLE foo1.bar
INSERT INTO `foo2`.`bar` SELECT * FROM `foo1`.`bar`
#...done.
Time: 0.74 sec

[miguel@lempa ~]$ mysqldbcopy --version
MySQL Utilities mysqldbcopy version 1.5.6 
License type: GPLv2

I have also run into this issue using the official mysql docker container from docker hub.

root@49808f27257a:/# mysql -V
mysql  Ver 14.14 Distrib 5.7.9, for Linux (x86_64) using  EditLine wrapper

root@49808f27257a:/# mysqldbcopy --source=root:pass@127.0.0.1:3306 --destination=root:pass@127.0.0.1:3306 production:production_copy
# Source on 127.0.0.1: ... connected.
# Destination on 127.0.0.1: ... connected.
# Copying database production renamed as production_copy
ERROR: Query failed. 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'information_schema.TABLES.TABLE_SCHEMA' which is not in SELECT list; this is incompatible with DISTINCT

Some additional steps I have taken:
1) I cleared out the directory where mysql was storing data and restarted mysql-server. I loaded a mysql dump and mysqldbcopy. The same failure occurred.
2) I rolled back the docker image I was using to mysql:5.6

docker exec -it `docker ps | grep mysql:5.6 | awk '{print $1}'` mysql -V
mysql  Ver 14.14 Distrib 5.6.29, for Linux (x86_64) using  EditLine wrapper

I cleared out the data directory again, loaded a mysql dump, then tried mysqldbcopy. It was successful this time.

For both attempts, I used the same mysqldbcopy distribution.

root@16077f96a373:/# mysqldbcopy --version
MySQL Utilities mysqldbcopy version 1.3.5 (part of MySQL Workbench Distribution 5.2.47)

I've got precisely the same problem on ubuntu on google cloud.

Vagrantfile for VM for reproducing the bug (use with the script)

Attachment: Vagrantfile (application/octet-stream, text), 3.28 KiB.

script to reproduce the bug. Use inside the vagrant VM, See the attached Vagrantfile

Attachment: mysql-bug-79197-vagrant-reproduce.sh (application/x-shellscript, text), 277 bytes.

I face this bug as well, and I created vagrant config file and script to reproduce the issue.

Grab vagrantfile and script that I attached to this issue, and put them in the same directory. Then from the bash shell switch to that directory and run:

vagrant up && vagrant ssh # you will need vagrant installed

once inside the vagrant, run:

cd /vagrant
./mysql-bug-79197-vagrant-reproduce.sh

You will see the following output:

Warning: Using a password on the command line interface can be insecure.
# Source on 127.0.0.1: ... connected.
# Destination on 127.0.0.1: ... connected.
# Copying database foo1 renamed as foo2
ERROR: Query failed. 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'information_schema.TABLES.TABLE_SCHEMA' which is not in SELECT list; this is incompatible with DISTINCT

====================================
Notes:

Be advised, the VM needs 2Gb of memory. You might be able to get away with 1Gb, but not less, at least not in my experience.

Also, I'm not sure whether I should do anything else except leaving this comment. Do I need to contact somebody so they revisit this issue and change it's status (since it is now reproducible)?

UPDATE to my previous comments:

seems that at least in my case the problem was from mismatching the versions of client and server, mysql-utilities and server, or something of that sort.

For instance, if you would go to Vagrantfile I provided, and specify version for mysql-server docker container as 5.6, matching the version of mysql-client that was installed on the VM, problem disappears. So you would change this line:

d.run "mysql_1", image: "mysql/mysql-server",

to this line:

d.run "mysql_1", image: "mysql/mysql-server:5.6",