| 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 | ||
[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: ... DROP TABLE IF EXISTS `v1`; /*!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) ) ENGINE=MyISAM */; 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. 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 in the face of a SQL error.
Description: 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.