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 11:57] Ilyas --
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)

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.:

AS SELECT <view_definition>;

But we have huge problem with restore backups :(.
[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 NAMES utf8 */;
/*!40103 SET TIME_ZONE='+00:00' */;

-- Table structure for table `test`

SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
  `a` int(11) default NULL
SET character_set_client = @saved_cs_client;

-- Dumping data for table `test`

/*!40000 ALTER TABLE `test` DISABLE KEYS */;
/*!40000 ALTER TABLE `test` ENABLE KEYS */;

-- Temporary table structure for view `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'

-- Final view structure for view `vw_test`

/*!50001 DROP TABLE `vw_test`*/;
/*!50001 DROP VIEW IF EXISTS `vw_test`*/;
/*!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``)` */;


-- 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

I can also confirm this breaks replication in 5.0.74:

*************************** 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
                 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_Pos: 0
         Master_SSL_Allowed: No
      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.

	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

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

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.