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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.13 OS:Linux (Linux 2.6.13)
Assigned to: Hartmut Holzgraefe CPU Architecture:Any

[7 Apr 2006 9:56] Joerg Sprung
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
[7 Apr 2006 13:30] Hartmut Holzgraefe
works fine for me with 3.23.58 server and 4.1.18 mysqldump
[17 Apr 2006 14:42] Klaus Stenner
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.
[6 Aug 2009 23:47] Steven Oxley
I am having the exact same problem with server version 3.23.28-gamma and client Ver. 14.14 Distrib 5.1.31
[6 Aug 2009 23:49] Steven Oxley
oops, sorry, that's mysqldump Ver 10.13 Distrib 5.1.31
[11 Jan 2011 13:26] Phil Hobson
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.