Bug #65675 Certain VIEWs not restorable from mysqldump
Submitted: 19 Jun 2012 18:08 Modified: 19 Jun 2012 20:42
Reporter: Hartmut Holzgraefe Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.5.21 OS:Linux
Assigned to: CPU Architecture:Any

[19 Jun 2012 18:08] Hartmut Holzgraefe
Description:
Restoring a view from a mysqldump may fail with 

  Incorrect column name ''

under certain conditions, e.g. when having a constant blank string in one query that is part of a UNION, e.g. 

  CREATE VIEW v1 AS SELECT id FROM table UNION SELECT ' ';

becomes 

  CREATE VIEW `v1` AS SELECT `id` AS `id` FROM `table` UNION SELECT ' ' AS ``

in SHOW CREATE VIEW ouput (and so in mysqldump output, too). This rewritten statement fails as `` is not a valid alias.

The UNION trick is necessary to reproduce this, a simple

  CREATE VIEW v2 AS SELECT ' ';

becomes 

  CREATE VIEW `v2` AS SELECT ' ' AS `Name_exp_1`

  

How to repeat:
mysql -u root -h 127.0.0.1 -e "DROP DATABASE IF EXISTS view_bug; CREATE DATABASE view_bug;"
mysql -u root -h 127.0.0.1 -e "CREATE VIEW view_bug.v1 AS select User from mysql.user union all select ' ';"
mysqldump -u root -h 127.0.0.1 --add-drop-database --databases view_bug > view_bug.sql
mysql -u root -h 127.0.0.1 < view_bug.sql

Suggested fix:
Change the rewriting code so that it never emits empty `` aliases
[19 Jun 2012 18:32] Valeriy Kravchuk
I think this is a duplicate of Bug #65388. Please, check.
[19 Jun 2012 18:53] Hartmut Holzgraefe
Yes, seems to be a duplicate indeed