Bug #34192 mysqldump from mysql 5.0.51 silently fails on dumping databases from 4.0 server
Submitted: 31 Jan 2008 11:45 Modified: 16 Apr 2008 22:42
Reporter: Arkadiusz Miskiewicz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.0.51a OS:Any
Assigned to: Chad MILLER CPU Architecture:Any
Tags: Contribution, regression

[31 Jan 2008 11:45] Arkadiusz Miskiewicz
Description:
mysqldump in 5.0.51 SILENTLY fails to dump databases from mysql 4.0 server.

The dump of any 4.0 database looks like this:

-- Server version       4.0.30-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

HERE SHOULD BE DUMP DATA

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2008-01-31 10:11:11

stracing reveals:
31385 write(3, "-\0\0\0\3SET SESSION character_set_results = \'binary\'", 49) = 49
31385 read(3, "2\0\0\1\377\251\4Unknown system variable \'character_set_results\'", 16384) = 54

It's because now mysqldump unconditionally runs (client/mysqldump.c):

      my_snprintf(buff, sizeof(buff), "show create table %s", result_table);

      if (switch_character_set_results(mysql, "binary") ||
          mysql_query_with_error_report(mysql, &result, buff) ||
          switch_character_set_results(mysql, default_charset))             
                 DBUG_RETURN(0);

where switch_character_set_results() are not supported on mysql < 4.1

How to repeat:
Try using mysqldump tool from 5.0.51 on a 4.0.30 server database.

Suggested fix:
Ignore errors from these commands:

--- mysql-5.0/client/mysqldump.c        2008-01-11 15:43:27.000000000 +0100
+++ mysql-5.0/client//mysqldump.c       2008-01-31 12:40:02.093115455 +0100
@@ -1687,10 +1687,16 @@

       my_snprintf(buff, sizeof(buff), "show create table %s", result_table);

-      if (switch_character_set_results(mysql, "binary") ||
-          mysql_query_with_error_report(mysql, &result, buff) ||
-          switch_character_set_results(mysql, default_charset))
-        DBUG_RETURN(0);
+      if (switch_character_set_results(mysql, "binary"))
+             if (opt_set_charset)
+                     DBUG_RETURN(0);
+
+      if (mysql_query_with_error_report(mysql, &result, buff))
+             DBUG_RETURN(0);
+
+      if (switch_character_set_results(mysql, default_charset))
+             if (opt_set_charset)
+                     DBUG_RETURN(0);

       if (path)
       {

based on opt_set_charset.
[31 Jan 2008 13:16] Sveta Smirnova
Thank you for the report.

Verified as described with last development sources.

With verison 3.23 mysqldump works fine.

This is no duplicate of bug #30444, because this is another problem and mysqldump was invoked without any options.
[31 Jan 2008 14:06] Arkadiusz Miskiewicz
5.0.45 is also fine. The change with switch_charset... was introduced later. Bug also applies to 5.1 series.
[31 Jan 2008 18:15] Chad MILLER
5.0 mysqldump against 4.1 mysqld works fine.  Dropping to 4.0 mysqld.
[5 Feb 2008 11:49] Arkadiusz Miskiewicz
Note that proposed "official" patch has also weaknes. If due to something switch_character_set_result() fails then still data won't be dumped and everything will happen silently.
[3 Mar 2008 15:13] Gatis Rumbens
I was confused when suddenly my backup script doesn't work after i upgraded on backup server from 5.0.45 to 5.0.51a (mysqldump  Ver 10.11 Distrib 5.0.51a). I checked several times syntax...tried to use --compatible= flag etc but no effect.

i tried to dump data from: Server version: 4.0.24-standard

result was only some use database; characters set etc info to create db, but no table data.
[12 Mar 2008 21:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43884

ChangeSet@1.2572, 2008-03-12 17:03:50-04:00, cmiller@zippy.cornsilk.net +1 -0
  Bug#34192: mysqldump from mysql 5.0.51 silently fails on dumping \
  	databases from 4.0 server
  
  mysqldump treated a failure to set the results charset as a severe
  error.  
  
  Now, don't try to set the charset for the SHOW CREATE TABLE statement,
  if remote server's version is earlier than 4.1, which means it 
  doesn't support changing charsets.
[13 Mar 2008 18:01] Chad MILLER
Queued to 5.0-build, 5.1-build, 6.0-build.
[27 Mar 2008 22:03] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 22:11] Bugs System
Pushed into 5.0.60
[28 Mar 2008 11:10] Bugs System
Pushed into 6.0.5-alpha
[16 Apr 2008 22:42] Paul DuBois
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

mysqldump attempts to set the character_set_results system variable
after connecting to the server. This failed for pre-4.1 servers that
have no such variable, but mysqldump did not account for this and 1)
failed to dump database contents; 2) failed to produce any error 
message alerting the user to the problem.
[6 May 2008 20:45] Sveta Smirnova
Bug #36482 was marked as duplicate of this one.