Bug #31202 mysqldump can't handle views with empty column names
Submitted: 26 Sep 2007 9:45 Modified: 24 Oct 2007 8:59
Reporter: Giuseppe Maxia Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Lars Thalmann CPU Architecture:Any
Tags: mysqldump, VIEW

[26 Sep 2007 9:45] Giuseppe Maxia
Description:
mysqldump produces a faulty script when dealing with a view containing an empty column name.
Empty column names are allowed in a view (see Bug#27695) but not in a table.

Creating a view with an empty column name works fine, until you try to dump it with mysqldump and load the script it produced.

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create view v1 as select id as ''  from t1;
Query OK, 0 rows affected (0.00 sec)

$ mysqldump test > t.sql
$ mysql test < t.sql
ERROR 1051 (42S02): Unknown table 'v2'

How to repeat:
use test;
create table t1 (id int);
create view v1 as select id as ''  from t1;

####
mysqldump test > t.sql
mysql test < t.sql

Suggested fix:
The reason for the failure is that mysqldump creates an intermediate table, which is immediately dropped. The usefulness of this intermediate step is not immediately visible. This step could be skipped or made more fault tolerant.

--
-- Temporary table structure for view `v1`
--

DROP TABLE IF EXISTS `v1`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE TABLE `v1` (
  `` int(11)
) */;

--
-- Final view structure for view `v1`
--

/*!50001 DROP TABLE `v2`*/;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
[12 Oct 2007 17:35] Konstantin Osipov
mysqldump unable to produce a correct dump, p2
[24 Oct 2007 8:59] Lars Thalmann
Views should not have all-space column names, so this is not a bug.

The real bug is BUG#27695.