MySQL Bugs: #1345: SQL Syntax Error in binarylog with DROP TABLES

Bug #1345	SQL Syntax Error in binarylog with DROP TABLES
Submitted:	19 Sep 2003 3:25	Modified:	28 Sep 2003 9:34
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.12	OS:	Linux (gentoo linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
When using Database Names like w500-details  , and create a table with eg 
"CREATE TEMPORARY TABLE tmp ...) inside this database ( so u have selected the 
database bevore with use) 
everythin is ok so far. 
But then using "DROP TABLE tmp" everything works fine so far on the master 
server.  But on The Slave Server i got an "ERROR: 1064  You have an error in 
your SQL syntax." with the w500-details beeing not correkt.  Well so far that 
is true, normaly when using db names with a - u just use ` elements so that 
mysql understands it correkt.   But  with the Replication the Master does the 
correkt query "DROP TABLE tmp" but seems to log into binlog "DROP TABLE 
w500-details.tmp" . So The Slave executes "DROP TABLE w500-details.tmp" but 
should execute "DROP TABLE `w500-details.tmp`" or "	DROP TABLE tmp" 

How to repeat:
Create and Drop 

Suggested fix:
most simplest way.  Escape all autogenerated Querys ( eg when adding 
w500-details to the query escape the Table Name)

Indeed:
MASTER> show binlog events;
+------------------+-----+------------+-----------+--------------+---------------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | Orig_log_pos | Info                                              |
+------------------+-----+------------+-----------+--------------+---------------------------------------------------+
| gbichot2-bin.001 | 4   | Start      | 1         | 4            | Server ver: 4.0.15-debug-log, Binlog ver: 3       |
| gbichot2-bin.001 | 79  | Query      | 1         | 79           | use test; create database `a-b`                   |
| gbichot2-bin.001 | 135 | Query      | 1         | 135          | use a-b; create temporary table `c+d` (a int)     |
| gbichot2-bin.001 | 205 | Query      | 1         | 205          | use a-b; DROP /*!40005 TEMPORARY */ TABLE a-b.c+d |

I see this problem with 4.0.14 as well.  Exact same issue.  When temporary tables are automatically dropped at the end of a PHP script, the slave replication thread dies with an error indicating that it doesn't like the abc-xyz db name.  No issues on the master though.  In this case, temporary tables are created to get around the missing subqueries feature.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

ChangeSet@1.1584, 2003-09-28 18:31:49+02:00, guilhem@mysql.com