Description:
It is possible to create a view which will not be properly dumped by mysqldump, such that it will fail with a syntax error when the generated SQL file is run again against the database to recreate the view.
This has a number of ramifications:
1) If you backup the database that contains an offending view using MySql Administrator, a subsequent restore will fail with a syntax error
2) If you "edit" the offending view in MySql Query Browser (via right-click->edit view), and then attempt to execute the script that is generated, it will fail with a syntax error.
This can occur if one of the fields in the view is an expression that is very long, but does not have an alias (e.g. an "AS xxx" clause after the expression). mysqldump will, to be thorough, assign a default field name which is equal to the expression. If the expression is long enough, the field name will be longer than the allowed maximum field name length, and fail.
This is a serious bug because a user can accidentally do this if they are using a nested select statement, and will never know that the view is un-restorable until they attempt to restore a backup. At this point the backup is unrestorable without significant understanding of the SQL in the backup file.
How to repeat:
Using MySql Query Browser, connect to a fresh database and run the following code to create a schema called "test" with two tables "table1" and "table2", and an offending view "myview".
-------------------------
SQL CODE STARTS
-------------------------
drop database if exists test;
create database test;
use test;
CREATE TABLE `test`.`table2` (
`recid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(45) NOT NULL,
PRIMARY KEY (`recid`)
)
ENGINE = InnoDB;
CREATE TABLE `test`.`table1` (
`recid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`item1` INTEGER UNSIGNED NOT NULL,
`item2` INTEGER UNSIGNED NOT NULL,
`item3` INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (`recid`)
)
ENGINE = InnoDB;
insert into table1 (item1, item2, item3) values (100, 50, 100);
insert into table1 (item1, item2, item3) values (10, 34, 100);
insert into table1 (item1, item2, item3) values (50, 70, 100);
insert into table1 (item1, item2, item3) values (30, 23, 100);
insert into table1 (item1, item2, item3) values (80, 2, 100);
insert into table1 (item1, item2, item3) values (23, 5, 100);
insert into table2 (title) values ('something number one');
insert into table2 (title) values ('something number two');
insert into table2 (title) values ('something number three');
create view myview as
select recid, title,
(select sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3) from table1) as bigsum
from table2
order by recid;
------------------------
SQL CODE ENDS
------------------------
You now have a simple schema with a view "myview" which will cause the failure. You can doublecheck that myview is indeed a valid view by doing a SELECT on it:
select * from myview
Attempt to edit "myview" from MySql Query Browser by right clicking on MyView, and clicking on Edit. A new Script pane will open with code in it that is supposed to recreate the view:
DROP VIEW IF EXISTS `test`.`myview`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview` AS select `table2`.`recid` AS `recid`,`table2`.`title` AS `title`,(select ((((((((sum(`table1`.`item1`) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) AS `sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)` from `table1` where (`table1`.`recid` > 3)) AS `bigsum` from `table2` order by `table2`.`recid`;
And if you attempt to execute this generated script you will get an error:
Script line: 2 Incorrect column name 'sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)'
The reason for the error is because the column name is too long.
Similarely, if you open MySql Administrator, go to the backup tab, and create a new backup project assigning it to backup the entire "test" database (including the view), an attempt to restore it via the restore tab will result in an error:
Error while executing this query:CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview` AS select `table2`.`recid` AS `recid`,`table2`.`title` AS `title`,(select ((((((((sum(`table1`.`item1`) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) AS `sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)` from `table1` where (`table1`.`recid` > 3)) AS `bigsum` from `table2` order by `table2`.`recid`;
The server has returned this error message:Incorrect column name 'sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)'
MySQL Error.
And the restore will fail.
Suggested fix:
It is possible to circumvent this error by adding an "AS xxx" clause to the field, giving the field an explicit alias. For example, in the schema above, you can fix the view as follows:
create view myview as
select recid, title,
(select sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3) as a from table1 where recid > 3) as bigsum
from table2
order by recid;
Now mysqldump will not assign the big expression field a default name, because you have explicitely specified an alias yourself.
Description: It is possible to create a view which will not be properly dumped by mysqldump, such that it will fail with a syntax error when the generated SQL file is run again against the database to recreate the view. This has a number of ramifications: 1) If you backup the database that contains an offending view using MySql Administrator, a subsequent restore will fail with a syntax error 2) If you "edit" the offending view in MySql Query Browser (via right-click->edit view), and then attempt to execute the script that is generated, it will fail with a syntax error. This can occur if one of the fields in the view is an expression that is very long, but does not have an alias (e.g. an "AS xxx" clause after the expression). mysqldump will, to be thorough, assign a default field name which is equal to the expression. If the expression is long enough, the field name will be longer than the allowed maximum field name length, and fail. This is a serious bug because a user can accidentally do this if they are using a nested select statement, and will never know that the view is un-restorable until they attempt to restore a backup. At this point the backup is unrestorable without significant understanding of the SQL in the backup file. How to repeat: Using MySql Query Browser, connect to a fresh database and run the following code to create a schema called "test" with two tables "table1" and "table2", and an offending view "myview". ------------------------- SQL CODE STARTS ------------------------- drop database if exists test; create database test; use test; CREATE TABLE `test`.`table2` ( `recid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(45) NOT NULL, PRIMARY KEY (`recid`) ) ENGINE = InnoDB; CREATE TABLE `test`.`table1` ( `recid` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `item1` INTEGER UNSIGNED NOT NULL, `item2` INTEGER UNSIGNED NOT NULL, `item3` INTEGER UNSIGNED NOT NULL, PRIMARY KEY (`recid`) ) ENGINE = InnoDB; insert into table1 (item1, item2, item3) values (100, 50, 100); insert into table1 (item1, item2, item3) values (10, 34, 100); insert into table1 (item1, item2, item3) values (50, 70, 100); insert into table1 (item1, item2, item3) values (30, 23, 100); insert into table1 (item1, item2, item3) values (80, 2, 100); insert into table1 (item1, item2, item3) values (23, 5, 100); insert into table2 (title) values ('something number one'); insert into table2 (title) values ('something number two'); insert into table2 (title) values ('something number three'); create view myview as select recid, title, (select sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3) from table1) as bigsum from table2 order by recid; ------------------------ SQL CODE ENDS ------------------------ You now have a simple schema with a view "myview" which will cause the failure. You can doublecheck that myview is indeed a valid view by doing a SELECT on it: select * from myview Attempt to edit "myview" from MySql Query Browser by right clicking on MyView, and clicking on Edit. A new Script pane will open with code in it that is supposed to recreate the view: DROP VIEW IF EXISTS `test`.`myview`; CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview` AS select `table2`.`recid` AS `recid`,`table2`.`title` AS `title`,(select ((((((((sum(`table1`.`item1`) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) AS `sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)` from `table1` where (`table1`.`recid` > 3)) AS `bigsum` from `table2` order by `table2`.`recid`; And if you attempt to execute this generated script you will get an error: Script line: 2 Incorrect column name 'sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)' The reason for the error is because the column name is too long. Similarely, if you open MySql Administrator, go to the backup tab, and create a new backup project assigning it to backup the entire "test" database (including the view), an attempt to restore it via the restore tab will result in an error: Error while executing this query:CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview` AS select `table2`.`recid` AS `recid`,`table2`.`title` AS `title`,(select ((((((((sum(`table1`.`item1`) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) + sum(`table1`.`item1`)) + sum(`table1`.`item2`)) + sum(`table1`.`item3`)) AS `sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)` from `table1` where (`table1`.`recid` > 3)) AS `bigsum` from `table2` order by `table2`.`recid`; The server has returned this error message:Incorrect column name 'sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)' MySQL Error. And the restore will fail. Suggested fix: It is possible to circumvent this error by adding an "AS xxx" clause to the field, giving the field an explicit alias. For example, in the schema above, you can fix the view as follows: create view myview as select recid, title, (select sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3)+sum(item1)+sum(item2)+sum(item3) as a from table1 where recid > 3) as bigsum from table2 order by recid; Now mysqldump will not assign the big expression field a default name, because you have explicitely specified an alias yourself.