Bug #65675 Certain VIEWs not restorable from mysqldump

• Submitted: 19 Jun 2012 18:08
• Modified: 19 Jun 2012 20:42
• Reporter: Hartmut Holzgraefe
• Status: Duplicate
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 5.5.21
• OS: Linux
• 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