MySQL Bugs: #19479: mysqldump creates invalid dump

Bug #19479	mysqldump creates invalid dump
Submitted:	2 May 2006 13:40	Modified:	23 Jul 2006 3:41
Reporter:	Dmitry Bakhvalov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	mysql 5.0.18/5.0BK/4.1BK	OS:	Linux (linux 2.6.11.4-20a-smp)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
mysqldump produces incorrect dump when dumping a database with tables
having "unusual" names, like `about:text`. The problem is in the 
CREATE TABLE statement, which has incorrect name `text` instead of `about:text`

How to repeat:
1) in mysql console:

create database db;
use db

drop table if exists `about:text`;
create table `about:text` ( 
_id int not null auto_increment,
`about:text` varchar(255) not null default '',
primary key (_id)
);

insert into `about:text` set `about:text`='some text';

2) shell:
$> mysqldump -u user -psecret db > dump.sql

3) shell:
cat dump.sql

-- MySQL dump 10.10
--
-- Host: localhost    Database: djem20
-- ------------------------------------------------------
-- Server version       5.0.18-log

/*!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 `about:text`
--

DROP TABLE IF EXISTS `about:text`;
CREATE TABLE `text` (
  `_id` int(11) NOT NULL auto_increment,
  `about:text` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `about:text`
--

/*!40000 ALTER TABLE `about:text` DISABLE KEYS */;
LOCK TABLES `about:text` WRITE;
INSERT INTO `about:text` VALUES (1,'some text');
UNLOCK TABLES;
/*!40000 ALTER TABLE `about:text` ENABLE KEYS */;
/*!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 */;

Suggested fix:
Fix the bug in mysqldump :))

Thank you for the bug report.

-- ------------------------------------------------------
-- Server version       4.1.19-debug-log
<cut>

--
-- Table structure for table `about:text`
--

DROP TABLE IF EXISTS `about:text`;
CREATE TABLE `text` (
  `_id` int(11) NOT NULL auto_increment,

-- ------------------------------------------------------
-- Server version       5.0.22-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
<cut>
--
-- Table structure for table `about:text`
--

DROP TABLE IF EXISTS `about:text`;
CREATE TABLE `text` (
  `_id` int(11) NOT NULL auto_increment,

-- ------------------------------------------------------
-- Server version       5.1.10-beta-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
<cut>

--
-- Table structure for table `about:text`
--

DROP TABLE IF EXISTS `about:text`;
CREATE TABLE `about:text` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,

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/7252

Noted in 5.0.23 changelog.

mysqldump did not dump the table name correctly for some table
identifiers that contained unusual characters such as `:'.