MySQL Bugs: #59442: mysqldump --compatible=mysql323 is not compatible with 3.23.41-log

Bug #59442	mysqldump --compatible=mysql323 is not compatible with 3.23.41-log
Submitted:	12 Jan 2011 11:53	Modified:	17 Jan 2011 18:14
Reporter:	Phil Hobson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	Ver 10.11 Distrib 5.0.51a	OS:	Linux (lenny (Debian))
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump compatible mysql323

Description:
The output from mysqldump --compatible=mysql323 has several lines that are not suitable for passing as STDIN to mysql 3.23.41-log.

The lines that are not compatible are:

/*!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,MYSQL323' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

which give errors of "ERROR 1065 at line n: Query was empty"

and 

SET @saved_cs_client     = @@character_set_client
which gives an error of "ERROR 1064 at line 23: You have an error in your SQL syntax near '@character_set_client' at line 1"

and 

SET character_set_client = utf8
which gives an error of "ERROR 1064 at line 24: You have an error in your SQL syntax near 'character_set_client = utf8' at line 1"

and 

SET character_set_client = @saved_cs_client

which gives an error of "ERROR 1064 at line 34: You have an error in your SQL syntax near 'character_set_client = @saved_cs_client' at line 1"

and 

/*!40000 ALTER TABLE `countries` DISABLE KEYS */
/*!40000 ALTER TABLE `countries` ENABLE KEYS */

which give errors of "ERROR 1065 at line n: Query was empty"

How to repeat:
On system running MySQL V5.0.51 

mysqldump --compatible=mysql323 database table > dumptedfile.sql

Copy dumptedfile.sql to a system running MySQL 3.23.41-log

mysql -v  databass < dumptedfile.sql

This fails with 

--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------

ERROR 1065 at line 7: Query was empty

Suggested fix:
Do not output /*!4..... lines when --compatible=mysql323 

Do not output the following 

SET @saved_cs_client     = @@character_set_client
SET character_set_client = utf8
SET character_set_client = @saved_cs_client

when --compatible=mysql323 

A circumvention to this problem is (on a system running MySQL 3.23.41-log
) to create the table using 

mysql -f  databass < dumptedfile.sql

That is, use the  -f|--force option so that mysql will continue even if the face of a SQL error.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #57770

Both this one and 57770 are about mysqldump, and about version specific comments in general, but that's where the similarities end already?

57770 is specifically about the VIEW specific output, while this one is about versioned comments in general when --compatible is used to generate output supposed to be compatible with a very old release ...