Bug #13008 SQL Dump made with mysqldump can not be imported

• Submitted: 6 Sep 2005 9:52
• Modified: 19 Sep 2005 18:57
• Reporter: Alex Koval
• Status: Won't fix
• Category: MySQL Server: Replication
• Severity: S1 (Critical)
• Version: 4.0.25-r2, 4.0.24, possible others
• OS: Linux (Gentoo Linux)
• Assigned to: Bugs System
• CPU Architecture: Any

[6 Sep 2005 9:52] Alex Koval

Description:
Short description
--------------------

Problem with mysqldump did not quote correctly field and table names
containing '-' (minus) sign.

Detailed description
------------------------

As usually, during moving all databases from one server to another I used
mysqldump.

One database / tables generated syntax errors between mysqldump. I mean, tables created with mysqldump can not be imported because of 'syntax errors'.

This all happend on tables like that (from jabberd2 product):

--
-- Table structure for table `disco-items`
--
CREATE TABLE disco-items (
  collection-owner text NOT NULL,
  object-sequence bigint(20) NOT NULL auto_increment,
  jid text,
  name text,
  node text,
  KEY object-sequence (object-sequence)
) TYPE=MyISAM;

In fact it should look this way:

--
-- Table structure for table `disco-items`
--
CREATE TABLE `disco-items` (
  `collection-owner` text NOT NULL,
  `object-sequence` bigint(20) NOT NULL auto_increment,
  jid text,
  name text,
  node text,
  KEY `object-sequence` (`object-sequence`)
) TYPE=MyISAM;

How to repeat:
1. Create table with field name or table name containing '-'
2. Do mysqldump of this table structure
3. Try import

Suggested fix:
Everywhere, including comments and DROP table syntax names are quoted properly. Same quoting myst be added to the CREATE TABLE syntax and to field names.

[6 Sep 2005 18:41] Jorge del Conde

Thanks for your bug report:

root-/usr/local# mysqldump test disco-items
-- MySQL dump 9.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       4.0.25

--
-- Table structure for table `disco-items`
--

CREATE TABLE disco-items (
  collection-owner text NOT NULL,
  object-sequence bigint(20) NOT NULL auto_increment,
  jid text,
  name text,
  node text,
  KEY object-sequence (object-sequence)
) TYPE=MyISAM;

--
-- Dumping data for table `disco-items`
--

root-/usr/local#

[6 Sep 2005 18:41] Jorge del Conde

Verified using FC4

[6 Sep 2005 21:14] Hartmut Holzgraefe

not a bug IMHO, the "-Q" / "--quote-names" just isn't on by default in 4.0s mysqldump

[7 Sep 2005 4:34] Alex Koval

> not a bug IMHO, the "-Q" / "--quote-names" just isn't on by default in
> 4.0s mysqldump

Ah, then OK. I were looking for such flag and did not catched it at first glance.
If it is possible to make mysqldump working, then we can switch this bug 
as being resolved. 

What made me confused is that table names in comments, in drop-table syntax were quoted by default, even with -Q/quote-names being OFF by default. But if this is not a bug, please close this entry. 

I've managed to transfer databases now, by adding -Q to the mysqldump.
Thank you for this tip.

[7 Sep 2005 10:45] Jonathan Miller

"drop-table syntax was quoted by default" 

We should be consistent in our approach.

[19 Sep 2005 18:54] Patrick Galbraith

I'll fix this, but a 4.0 release isn't due until 2005-11-02

[19 Sep 2005 18:57] Patrick Galbraith

Just recieved word that this particular behaviour is documented, so I won't change it.