| Bug #36287 | column invalid with "mysql < dump_containing_views.mysqldump" | ||
|---|---|---|---|
| Submitted: | 23 Apr 2008 11:57 | Modified: | 7 Apr 2009 18:31 |
| Reporter: | Ilyas -- | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
| Version: | 5.0.56 | OS: | Linux (CentOS5) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | mysql dump restore create view failure regression | ||
[23 Apr 2008 14:00]
MySQL Verification Team
Thank you for the bug report. Could you please provide the create statements of the table/views involved you can do in private comment if you wish?. Thanks in advance.
[23 Apr 2008 14:55]
MySQL Verification Team
Thank you for the feedback. Looks like both were editing the bug report while you are adding the feedback requested.
[24 Apr 2008 4:22]
Ilyas --
# mysql -u root --password='HIDDEN' aa < /tmp/check.sql ERROR 1166 (42000) at line 45: Incorrect column name '(select group_concat(distinct concat(`test`.`a`) separator ' ') ' check.sql (also attached as file): -- MySQL dump 10.11 -- -- Host: localhost Database: aa -- ------------------------------------------------------ -- Server version 5.0.56-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `test` -- DROP TABLE IF EXISTS `test`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `test` ( `a` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; SET character_set_client = @saved_cs_client; -- -- Dumping data for table `test` -- LOCK TABLES `test` WRITE; /*!40000 ALTER TABLE `test` DISABLE KEYS */; /*!40000 ALTER TABLE `test` ENABLE KEYS */; UNLOCK TABLES; -- -- Temporary table structure for view `vw_test` -- DROP TABLE IF EXISTS `vw_test`; /*!50001 DROP VIEW IF EXISTS `vw_test`*/; /*!50001 CREATE TABLE `vw_test` ( `(select group_concat(distinct concat(``test``.``a``) separator ' ') ` longblob ) */; -- -- Dumping routines for database 'aa' -- DELIMITER ;; DELIMITER ; -- -- Final view structure for view `vw_test` -- /*!50001 DROP TABLE `vw_test`*/; /*!50001 DROP VIEW IF EXISTS `vw_test`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */ /*!50001 VIEW `vw_test` AS select (select group_concat(distinct concat(`test`.`a`) separator ' ') AS `group_concat(distinct concat(``test``.``a``) separator ' ')` from `test`) AS `(select group_concat(distinct concat(``test``.``a``) separator ' ') from ``test``)` */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2008-04-24 4:20:30
[24 Apr 2008 4:24]
Ilyas --
If import this dump to mysql-5.0.22 or 5.0.45 all OK, restore from dump happen successfully.
[24 Apr 2008 4:26]
Ilyas --
small dump for reproduce
Attachment: check.sql (application/octet-stream, text), 2.42 KiB.
[25 Apr 2008 6:57]
Sveta Smirnova
Thank you for the report. Verified as described. Change has been introduced in version 5.0.52.
[2 Jun 2008 9:09]
James Wilson
I'm also experiencing this with 5.0.60 ERROR 1166 (42000) at line 686: Incorrect column name 'group_concat(concat(r2.name,'@',o2.domain) order by r2.name, o2.domain separator ';')' It should be noted the original SQL statement imports perfectly when creating a VIEW. The error occurs when restoring from a mysqldump. This was not occurring in 5.0.45
[5 Jan 2009 13:51]
James Wilson
Hi,
I can also confirm this breaks replication in 5.0.74:
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db.example.com
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 297252
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 113690
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1166
Last_Error: Error 'Incorrect column name 'group_concat(concat(r2.name,'@',o2.domain) order by r2.name, o2.domain separator ';')'' on query. Default database: 'test'. Query: 'CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `valias` AS select `a`.`id` AS `id`,`a`.`identifier` AS `identifier`,`a`.`organisationid` AS `organisationid`,`a`.`userid` AS `userid`,`a`.`forename` AS `forename`,`a`.`surname` AS `surname`,`a`.`ncyeargroup` AS `ncyeargroup`,`a`.`roleid` AS `roleid`,`a`.`active` AS `active`,`a`.`editable` AS `editable`,`a`.`outgoing` AS `outgoing`,concat(`a`.`identifier`,_latin1'@',`o`.`suffix`) AS `caption`,if(((select `test`.`setting`.`value` AS `value` from `test`.`setting` where (`test`.`setting`.`name` = _latin1'eppnformat')) = _latin1'user'),concat(`pa`.`identifier`,_latin1'@',`po`.`domain`),concat(`a`.`identifier`,_latin1'@',`o`.`domain`)) AS `eppn`,if(((select `test`.`setting`.`value` AS `value` from `test`.`setting` where (`MyIdent
Skip_Counter: 0
Exec_Master_Log_Pos: 113553
Relay_Log_Space: 297949
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
[18 Feb 2009 20:49]
Chad MILLER
This is closely bound to Bug#40277. 40277 doesn't address the case of aliases being mapped to table names. Aliases may be 256 (manual says 255) characters long, but column names may be only 64. In creating a view, the column names as the result of the SELECT become the names of the columns for the pseudo-table representing what the view emits. Because of Bug#27695, we now check that these new column names fit the constraints imposed on identifiers. http://dev.mysql.com/doc/refman/5.0/en/identifiers.html CREATE VIEW vbug36287 AS SELECT 1 as `column-names-may-be-64-characters-long------------------------64`; CREATE VIEW vbug36287 AS SELECT 1 as `column-names-may-not-be-65-characters-long---------------------65`; First, the transparent mapping of aliases to column names isn't obvious and the change in length constraints should be documented, at the very least. (If, it isn't already.) Next, instead of errors being raised or names rewritten, I propose that aliases in view->table instead behave just like aliases in SELECT statements, that is, usually, truncation. SELECT 3 as `aliases-may-not-be-257-characters-long---is-truncated---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------257`; This yields a table with column name ending in "-25", not emits an error. This CREATE VIEW statement, I say, should create a view with table column ending in "-6", not always emit an ER_WRONG_COLUMN_NAME error. CREATE VIEW vbug36287 AS SELECT 1 as `column-names-may-not-be-65-characters-long---------------------65`; (Always warn. The SQL mode could promote a warning to an error. I'm undecided.)
[6 Mar 2009 22:00]
Chad MILLER
I no longer think this is a bug at all. It is surprising, for two reasons: == 1) SELECT `aliases-may-be-256-characters-long---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------256` FROM (SELECT 1 as `aliases-may-be-256-characters-long---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------256-but-longer-is-silently-truncated-in-SELECT`) d; This does not yield an error, in current 5.0. It should, as it's wrong to accept an alias that's too long and then silently truncate it. Truncating is wrong, and we shouldn't do that for that case, or the case of a too-long column name (generated from alias) as demonstrated in this bug report. It's not sufficient to point to other bad behavior to justify this behavior. == == 2) Bug#27695, another alias-mapped-to-column-name bug, which incidentally is the cause of this bug report, makes it clear that aliases should be treated as precious and if we can't use them we should stop the user and say why. If no alias is specified, as in Bug#40277, then we act as though it's acceptable to silently change the name. Aliases used in views are supposed to mean something on the output of the view, so if it's illegal, then we should say so and refuse it. It's not sufficient to point to historical acceptance of bad data to justify further acceptance of it. == So, this bug is an expected effect of the fix of Bug#27695. It is not a regression except as refusing to continue to be illegal (as defined by our docs). I'm handing this bug back to Triage and Support to decide its fate. I suspect that the best case is to require that the dumped data be corrected via script or hand to be short enough to be a legal column name, and then the problem disappears.
[7 Apr 2009 18:31]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. Added the following to http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html: SHOW CREATE VIEW displays view definitions using an AS alias_name clause for each column. If a column is created from an expression, the default alias is the expression text, which can be quite long. As of 5.0.52/5.1.23/6.0.4, aliases for column names in CREATE VIEW statements are checked against the column length restriction of 64 characters. As a result, views created from the output of SHOW CREATE VIEW fail if any column alias exceeds 64 characters. This can cause problems in the following circumstances for views with too-long aliases: * View definitions fail to replicate to newer slaves that enforce the column-length restriction. * Dump files created with mysqldump cannot be loaded into servers that enforce the column-length restriction. A workaround for either problem is the modify each problematic view definition to use aliases that provide shorter column names. Then the view will replicate properly, and can be dumped and reloaded without causing an error. To modify the definition, drop and create the view again with DROP VIEW and CREATE VIEW, or replace the definition with CREATE OR REPLACE VIEW. For problems that occur when reloading view definitions in dump files, another workaround is to edit the dump file to modify its CREATE VIEW statements. However, this does not change the original view definitions, which may cause problems for subsequent dump operations. Also added a note about the problem to the upgrading section, with a cross reference to the view restrictions section for more information.
[18 Jun 2014 15:12]
Ivan Mednev
We have the same issue and the fact that a workaround is explained in the documentation, doesn't save the unnecessary extra work that needs to be done between the data-dump and the data-load in MySQL. Quite the opposite - it suggests it :) In our case - we have to modify the dump file every time, so that it can be loaded without errors. About the reasonable workaround to "use aliases that provide shorter column names" - our view is a union of several selects and we provide short enough aliases in the first select, but instead of them being used also in the other selects (as it finally happens when the query is executed) they are ignored by SHOW CREATE VIEW in the other selects and the aliases given, are the column names, or the expression text. And there is that MySQL hits its maximum column length restriction, for examples in expressions like: CASE WHEN B.`name` IS NULL THEN B.maven_deployment_path ELSE NULL END AS (72 chars) So, according to the proposed solution, we have to either continue to modify the dump file, or generate redundant information by repeating the aliases in all selects, in order to meet a restriction mysqldump should be aware of, when it generates the dump file.
Description: We have mysql-5.0.56 and create dump.sql by mysqldump. We try import dump.sql an another server, but mysql fails with message: # mysql -u root -p --password='HIDDEN' aa < /tmp/dump.sql ERROR 1166 (42000) at line 982: Incorrect column name 'GROUP_CONCAT( DISTINCT CONCAT('microdistrict__', s2m.microdistrict_id) S' How to repeat: # mysql -u root -p --password='HIDDEN' aa < /tmp/dump.sql Suggested fix: objects which can't create mysql can be created my console client with more plain sql syntax ex.: CREATE OR REPLACE VIEW <view_name> AS SELECT <view_definition>; But we have huge problem with restore backups :(.