Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.24, 10.0
-
Component/s: Views
-
Labels:
-
Environment:linux
Description
(see also http://bugs.mysql.com/bug.php?id=65675 )
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
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Also exhibits a query rewrite bug with AS.
5.7 Fix seems to be in this changeset
https://github.com/mysql/mysql-server/commit/e1b1e0f7435116e123decb5ad18fe351a9114102
I tried a backport to MariaDB 10.0 in the attached patched, a quick test show that my original test case works without problem now. I didn't run a full test so I don't know whether this has any nasty side effects ...
I cannot repeat it. Your test case doesn't end up having AS ``.
Looks like it was fixed few months ago in 5.5.
Yes, it is no longer reproducible starting with MariaDB 5.5.39
fixed in mysql-5.7.0