MySQL Bugs: #18882: mysqldump dumps no remotedatabase ver. 3.23

Bug #18882	mysqldump dumps no remotedatabase ver. 3.23
Submitted:	7 Apr 2006 9:56	Modified:	7 Apr 2006 13:30
Reporter:	Joerg Sprung	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.13	OS:	Linux (Linux 2.6.13)
Assigned to:	Hartmut Holzgraefe	CPU Architecture:	Any

Description:
When try todump a remote mysqld ver. 3.23 with mysqldump 4.1.13 i got following errormessage:

mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE='' */': Query was empty (1065)

No mysqldump-option helped me to avoid this Error

How to repeat:
Source mysqldump 4.1.13 try to dump remote mysqld ver. 3.23

mysqldump --compatible=mysql323 -uUSER -p -hHOSTNAME --all-databases

either compatible is ther or not mysqldump raise error

works fine for me with 3.23.58 server and 4.1.18 mysqldump

there's the same behavior on our machines:

remote: Solaris / Mysql 3.23.36

local mysqldump / Ver 10.9 Distrib 4.1.18, for pc-linux-gnu (i686)

I tried another mysqldump from a SuSE 9.0-distribution (Ver 9.09 Distrib 4.0.15, for suse-linux (i686)), this one works fine.

I am having the exact same problem with server version 3.23.28-gamma and client Ver. 14.14 Distrib 5.1.31

oops, sorry, that's mysqldump Ver 10.13 Distrib 5.1.31

I too have this problem with mysqldump Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (i486) connecting to a MySQL server version: 3.23.41-log

I will add some commetary first as, looking at other problem reports, I am not sure the usage has been well explained.

Normally, mysqldump is used on the local server to dump out one or more tables in a form that can be restored to this or other MySQL servers. A typical scenario to copy a table from one server to another is to use the following commands:

mysqldump --add-drop-table database table | mysql -h remote_host database

This used to work fine, and in many instances still does. However, with the new authentication which came in after 3.23.41-log, this no longer works when the above commands are run on a system with MySQL 3.23.41-log when the remote_host is runing MySQL 5.0.51a-24+lenny4-log (Debian). The "mysql -h..." command fails with "ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client".

Many, including me, thought that all I need to do was to invoke mysqldump on remote_host so that the commands issued were:

mysqldump -h remote_host2 --add-drop-table database table | mysql database

where remote_host2 is the host running MySQL 3.23.41-log.

The problem as described in the original bug report then occurs. This is due to the mysqldump Ver 10.11 Distrib 5.0.51a sending the following SQL query to the host running MySQL 3.23.41-log:

/*!40100 SET @@SQL_MODE='' */

This fails on the MySQL 3.23.41-log host with the error message "ERROR 1065: Query was empty".

So, I would suggest that the fix to mysqldump Ver 10.11 is to supress the "/*!40100 SET @@SQL_MODE='' */" when it is connected to a remote host which is running MySQL prior to V4.01.

However, I have found a circumvention that may help.

If running mysqldump on a MySQL 3.23.41-log host which is connecting to remote host that is running MySQL > 4.0 then logon with a username that has been defined as "MySQL 4.0 compatible". This is very easy to do if you are using phpMyAdmin to change user privileges. Indeed, if using phpMyAdmin, find the user and simply reenter the passsword and - in the "Password Hashing" section, ensure that the "MySQL 4.0 compatible" button is on.