| Bug #59398 | mysqldump 5.1 can't handle a dash ("-") in database names in ALTER DATABASE | ||
|---|---|---|---|
| Submitted: | 10 Jan 2011 18:11 | Modified: | 1 Mar 2011 1:28 |
| Reporter: | Jiri Fartak | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 5.1.54, 5.6.2 | OS: | Linux |
| Assigned to: | Nirbhay Choubey | CPU Architecture: | Any |
| Tags: | regression | ||
[10 Jan 2011 19:26]
Peter Laursen
I am not able to understand why ALTER DATABASE statement(s) is required in the dump at all. The ALTER DATABASE statements in the script that created the database are *historical* at dump-time. And there is not even a CREATE DATABASE statement. So the dump may actually be imported to a database named differently. On 5.1.54 on Windows I get the dump below (using the mysqldump shipped with this server). Not quite what Jiri reports, but maybe he simplified.: -- MySQL dump 10.13 Distrib 5.1.54, for Win64 (unknown) -- -- Host: localhost Database: test-database7 -- ------------------------------------------------------ -- Server version 5.1.54-community /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!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 */; -- -- Table structure for table `test` -- DROP TABLE IF EXISTS `test`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test` ( `col` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `test` -- LOCK TABLES `test` WRITE; /*!40000 ALTER TABLE `test` DISABLE KEYS */; /*!40000 ALTER TABLE `test` ENABLE KEYS */; UNLOCK TABLES; ALTER DATABASE test-database7 CHARACTER SET utf8 COLLATE utf8_general_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `anketa_bins` BEFORE INSERT ON `test` FOR EACH ROW BEGIN END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE test-database7 CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2011-01-10 20:10:33 Peter (not a MySQL serson)
[10 Jan 2011 19:41]
Peter Laursen
clarification: And there is not even a CREATE DATABASE statement >> And there is not even a CREATE DATABASE and/or a USE statement
[10 Jan 2011 20:55]
Peter Laursen
5.5.8 has exactly same issue(s): 1) database name is not `quoted` 2) ALTER DATABASE statements are executed without ensuring that other statements are executed here (no USE statement) (and it is not ENGINE-related. Same with MyISAM) But I am really mystified 1) ALTER DATABASE test-database7 CHARACTER SET utf8 COLLATE utf8_general_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `anketa_bins` BEFORE INSERT ON `test` FOR EACH ROW BEGIN END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE test-database7 CHARACTER SET utf8 COLLATE utf8_unicode_ci ; What statements between the two ALTERs would not work with utf8_unicode_ci collation so that it is necessary to set utf8_general_ci for the database? 2) If Jiri's script to create the database omits the two ALTER DATABASE statements mysqldump also does not generate ALTER DATABASE statements. I am not able to figure out from where mysqldump gets the information! :-( Maybe because of the 'db.opt' file (a new construction?) for the database reads: default-character-set=utf8 default-collation=utf8_general_ci So it will use default collation and since ALTER?? If so it is a weird construction IMHO. SHOW CREATE has all information.
[11 Jan 2011 7:06]
Jiri Fartak
First I would like to thank you all, who replied. The "script" I've sent looks like crazy excersize, I know - but as I wrote: "...it took me a lot of time to make mysqldump to write ALTER DATABASE statement (at least one) into the dumped file to show you a bug...It looks that is quite hardly reproducible. The history was simple - I tried to backup all databases on our old test server, where web apps. etc are being developed and transfer them to the purchased new one. But what a surprise - when I was restoring databases from the big dumped file, mysql throwed mentioned errors. That's all. There were about 6 databases containing hyphens in their names in one big 1,5 GB dumped file (approx. 100 databases in total). All of them were not quoted in the ALTER DATABASE statements. I am not expert in MySQL, I'm working with other DBMS, so I cannot tell you, why the problematic statements were generated by mysqldump or even not quoted. However, these databases are used in our production environment without any problems. I will try to get some additional info from our developers working with MySQL and if they tell me something valuable, I will post a comment. First I've encountered this error on 5.1.46, so I tried the newest version in 5.1 branch (5.1.54), upgraded all databases, then made a dump, but the results were the same.
[11 Jan 2011 9:57]
Jiri Fartak
For completion: I found out, that if using older version of mysqldump >charon:/ # /usr/sbin/mysqldump -V >mysqldump Ver 10.11 Distrib 5.0.45, for unknown-linux-gnu (x86_64) against 5.1.46 server (in production environment), then the dumped database called bilbo-smak (see first post) DOES NOT contain ALTER DATABASE statements. If using the newer one bundled in 5.1.46 (or 5.1.54), then it includes ALTER DATABASE statements into the dumped file. That's indicating that the bug could be in the utility mysqldump itself.
[17 Jan 2011 20:52]
Sveta Smirnova
Thank you for the report. Verified as described.
[24 Jan 2011 19:20]
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/129480 3564 Nirbhay Choubey 2011-01-25 Bug#59398 : mysqldump 5.1 can't handle a dash ("-") in database names in ALTER DATABASE. mysqldump did not quote database name in 'ALTER DATABASE' statements in its output. This can further cause a failure while loading if database name contains a hyphen '-'. This happened as, while printing the 'ALTER DATABASE' statements, the database name was not quoted. Fixed by quoting the database name. @ client/mysqldump.c Bug#59398 : mysqldump 5.1 can't handle a dash ("-") in database names in ALTER DATABASE. Modified the print statement in order to print the quoted database name for 'ALTER DATABASE' statements. @ mysql-test/r/mysqldump.result Added a test case for bug#59398. @ mysql-test/t/mysqldump.test Added a test case for bug#59398.
[1 Mar 2011 1:28]
Paul DuBois
Noted in 5.1.57, 5.5.11, 5.6.2 changelogs. mysqldump did not quote database names in ALTER DATABASE statements in its output, which could cause an error at reload time for database names containing a dash. CHANGESET - http://lists.mysql.com/commits/131855
Description: This bug is similar to resolved one called: mysqldump 5.1 can't handle a dash ("-") in database names, Bug #31113. When backup all databases via: /usr/local/mysql/bin/mysqldump --quote-names --all-databases >/home/httpd/html/mysql_dump.sql -p or just the problematic one (called bilbo-smak) only: /usr/local/mysql/bin/mysqldump --quote-names bilbo-smak >/home/httpd/html/mysql_dump.sql -p the generated scripts are containing ALTER DATABASE statements to set up proper encoding, but these statements FAIL because lack of quotes in the database name following the ALTER STATEMENT. Here is an excerpt... -- MySQL dump 10.13 Distrib 5.1.54, for pc-linux-gnu (i686) -- -- Host: localhost Database: bilbo-smak -- ------------------------------------------------------ -- Server version 5.1.54 /*!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 */; -- -- Table structure for table `anketa` -- DROP TABLE IF EXISTS `anketa`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `anketa` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `id_db_objekt` bigint(20) NOT NULL, `nazev` text COLLATE utf8_unicode_ci NOT NULL, `skryt` tinyint(1) NOT NULL DEFAULT '0', `hlasovani` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `skryt` (`skryt`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `anketa` -- LOCK TABLES `anketa` WRITE; /*!40000 ALTER TABLE `anketa` DISABLE KEYS */; /*!40000 ALTER TABLE `anketa` ENABLE KEYS */; UNLOCK TABLES; ALTER DATABASE bilbo-smak CHARACTER SET latin2 COLLATE latin2_czech_cs ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER = `root`@`%`*/ /*!50003 TRIGGER `anketa_bins` BEFORE INSERT ON `anketa` FOR EACH ROW BEGIN INSERT INTO db_objekt (datum_vytvoreni, datum_upravy) VALUES (NOW(), NOW()); SET NEW.id_db_objekt = LAST_INSERT_ID(); END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE bilbo-smak CHARACTER SET latin1 COLLATE latin1_swedish_ci ; ALTER DATABASE bilbo-smak CHARACTER SET latin2 COLLATE latin2_czech_cs ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; .... Look at last lines begining with ALTER DATABASE statements - no ` quoting visible. When restoring databases using mysql cmd tool a la: mysql -p <databases.sql it starts to argue, that : ERROR 1064 (42000) at line 5826: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-smak CHARACTER SET latin2 COLLATE latin2_czech_cs' at line 1 This bug is displeasing, because it brokes the dumped backup sql scripts and some replacing operations (using sed, etc) are needed. Maybe, I missed some other needed argument for mysqldump, but I thing that there are no others to have someting to do with quoting than used --quote-names. How to repeat: Truly said, it took me a lot of time to find the minimal sequence to reproduce this - to get ALTER DATABASE statements to be generated in the dump file. This is the minimal script, that produces one ALTER DATABASE statement in the dump file: ---------------------------------------------------------- create database `test-database7`; use `test-database7`; create table test (col varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; DELIMITER ;; CREATE TRIGGER `anketa_bins` BEFORE INSERT ON `test` FOR EACH ROW BEGIN END ;; DELIMITER ; ALTER DATABASE `test-database7` CHARACTER SET latin1 COLLATE latin1_swedish_ci; ALTER DATABASE `test-database7` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; ------------------------------------------------------------- If I run the script above (called bug.sql) using mysql command line tool: /usr/local/mysql/bin/mysql -p <./bug.sql and then make a dump of test-database7: /usr/local/mysql/bin/mysqldump --quote-names test-database7 >/home/httpd/html/mysql_dump.sql -p I get: -- MySQL dump 10.13 Distrib 5.1.54, for pc-linux-gnu (i686) -- -- Host: localhost Database: test-database7 -- ------------------------------------------------------ -- Server version 5.1.54 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!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 */; -- -- Table structure for table `test` -- DROP TABLE IF EXISTS `test`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test` ( `col` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `test` -- LOCK TABLES `test` WRITE; /*!40000 ALTER TABLE `test` DISABLE KEYS */; /*!40000 ALTER TABLE `test` ENABLE KEYS */; UNLOCK TABLES; ALTER DATABASE test-database7 CHARACTER SET latin1 COLLATE latin1_swedish_ci ; ... - test-database7 is not quoted as it should be. Suggested fix: mysqldump should quote database names in ALTER DATABASE statements too.