| Bug #65914 | DB names with special characters like '.' and '-' do not get quoted in binlog | ||
|---|---|---|---|
| Submitted: | 16 Jul 2012 19:35 | Modified: | 14 Feb 2013 11:47 |
| Reporter: | Grant McAlister | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.5.25a | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | binlog, Database name, special character, Use | ||
[17 Jul 2012 4:27]
Valeriy Kravchuk
I can confirm that database names in USE statements are not quoted. But I do not see any problems as a result: macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.26-debug-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test.1-2; ERROR 1064 (42000): 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 '.1-2' at line 1 mysql> create database `test.1-2`; Query OK, 1 row affected (0.01 sec) mysql> use test.1-2; Database changed
[17 Jul 2012 9:20]
Peter Laursen
Now .. what about database names with a SPACE-character in them? Are such also not quoted in binlog? The full unicoce range (except for NULL) is valid in identifiers and not quoting is gambling IMO! CREATE DATABASE `my database`; USE my database; -- 1064 error
[17 Jul 2012 10:34]
Valeriy Kravchuk
Peter, Yes, your case with space in the database name shows that we do have a real bug in mysqlbinlog: ... # at 3168 #120717 13:29:34 server id 1 end_log_pos 3255 Query thread_id=7 exec_time=0 error_code=0 SET TIMESTAMP=1342520974/*!*/; create database `my db` /*!*/; # at 3255 #120717 13:30:43 server id 1 end_log_pos 3342 Query thread_id=8 exec_time=0 error_code=0 use my db/*!*/; SET TIMESTAMP=1342521043/*!*/; create table t1(c1 int) /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; This USE will not work properly. mysqlbinlog should quote database names in USE statements generated. Like SHOW BINLOG EVENTS does: ... | macbook-pro-bin.000001 | 3075 | Query | 1 | 3168 | create database `test.1-2` | | macbook-pro-bin.000001 | 3168 | Query | 1 | 3255 | create database `my db` | | macbook-pro-bin.000001 | 3255 | Query | 1 | 3342 | use `my db`; create table t1(c1 int) | ...
[14 Feb 2013 11:47]
Erlend Dahl
This was fixed along with the fix for bug#66550 in 5.1.67, 5.5.29, and 5.6.8.
Description: Database names with special characters like '.' and '-' do not get quoted in the output of mysqlbinlog for "use" commands. When using mysqlbinlog to generate changes to be applied in another instance for replay/recovery, this will cause the statement to fail with an ERROR 1064 (42000) This occurs in all current versions 5.1.63,5.5.25a and 5.6.5. How to repeat: Crete database `test.1-2` Use `test.1-2` Create table test (a int); Insert into test values (1); ----- Then run mysqlbinlog on the bin log and the output: # at 106 #120710 18:01:53 server id 565002312 end_log_pos 199 Query thread_id=17608 exec_time=0 error_code=0 SET TIMESTAMP=1341943313/*!*/; SET @@session.pseudo_thread_id=17608/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*! SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create database `test.1-2` /*!*/; # at 199 #120710 18:02:56 server id 565002312 end_log_pos 291 Query thread_id=17608 exec_time=0 error_code=0 use test.1-2/*!*/; SET TIMESTAMP=1341943376/*!*/; create table test(a int ) /*!*/; # at 291 #120710 18:03:23 server id 565002312 end_log_pos 363 Query thread_id=17608 exec_time=0 error_code=0 SET TIMESTAMP=1341943403/*!*/; BEGIN /*!*/; # at 363 #120710 18:03:23 server id 565002312 end_log_pos 457 Query thread_id=17608 exec_time=0 error_code=0 SET TIMESTAMP=1341943403/*!*/; insert into test values (1) /*!*/; # at 457 #120710 18:03:23 server id 565002312 end_log_pos 484 Xid = 752580 COMMIT/*!*/; The use statement does not quote the name of the database causing ERROR 1064 (42000) at line 9: 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 '.1-2/*!*/' when trying to replay binlog Suggested fix: On sql/log_event.cc there are 3 instances of generation of 'use' command. 2 of them have quotes added. 1 one them seem to be missing the quoting.