Bug #57770 mysqldump: version-dependency SET statements not consistent
Submitted: 27 Oct 2010 13:50 Modified: 16 Nov 2010 11:34
Reporter: Kai Sautter (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.1.51, 5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc, regression

[27 Oct 2010 13:50] Kai Sautter
Dumping tables and views also generates SET statements for character set, like

before CREATE:
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;

after CREATE:
SET character_set_client = @saved_cs_client;

For tables, these statements are made version dependent by /*!40101 <statement> */; For views as well as the temporary tables generated for views these are unconditioned (no version indication of statement). As view related statements are set to be dependent on version 50001 or above ("/*!50001 ... */"), this should (probably) also apply to the character set statements.

How to repeat:
(1) Create a table
(2) Create a view

Dump the database using mysqldump. Specifically, I used the following options:
--allow-keywords -E -n -d -rsomefile.sql -R somedatabase

Defaults are (installation default):
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
all                               TRUE
all-databases                     FALSE
all-tablespaces                   FALSE
no-tablespaces                    FALSE
add-drop-database                 FALSE
add-drop-table                    TRUE
add-locks                         TRUE
allow-keywords                    FALSE
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   FALSE
compress                          FALSE
create-options                    TRUE
databases                         FALSE
debug-check                       FALSE
debug-info                        FALSE
default-character-set             utf8
delayed-insert                    FALSE
delete-master-logs                FALSE
disable-keys                      TRUE
events                            FALSE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
first-slave                       FALSE
flush-logs                        FALSE
flush-privileges                  FALSE
force                             FALSE
hex-blob                          FALSE
host                              (No default value)
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
log-error                         (No default value)
master-data                       0
max_allowed_packet                25165824
net_buffer_length                 1046528
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
order-by-primary                  FALSE
port                              0
quick                             TRUE
quote-names                       TRUE
replace                           FALSE
routines                          FALSE
set-charset                       TRUE
shared-memory-base-name           (No default value)
single-transaction                FALSE
dump-date                         TRUE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              (No default value)
verbose                           FALSE
where                             (No default value)

Suggested fix:
Always add version dependency of statements. If version dependency of object (table, view, procedure, ...) to be created by the primary statement of the block is higher, make statement dependent on this version.
[7 Nov 2010 13:46] Valeriy Kravchuk
5.1.52 is already available. Please, check if it is still affected.
[8 Nov 2010 7:18] Kai Sautter
I have briefly tested this against a Windows installation of 5.1.52. First tests confirm this issue for the temporary tables created for views.
[16 Nov 2010 11:34] Sveta Smirnova
Thank you for the report.

Verified as described:

/*!50001 DROP VIEW IF EXISTS `v1`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v1` (
  `f1` int(11)
SET character_set_client = @saved_cs_client;

-- Final view structure for view `v1`

Thought does not harm anyway.

Version 5.0 has no information for character sets in views at all.
[17 Jan 2011 18:15] Sveta Smirnova
Bug #59442 was marked as duplicate of this one.
[17 Jan 2011 23:07] Phil Hobson
My comments from Bug #59442 that includes a possible temporary circumvention to the problem.

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 TIME_ZONE='+00:00' */;

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


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"


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"


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"


/*!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 


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 in the face of a SQL error.