ERROR 1166 (42000) at line 65203: Incorrect column name ''

Question

I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message:

$ mysql -u root < mydata.sql
ERROR 1166 (42000) at line 65203: Incorrect column name '' 

MySQL Version is 5.5.32-0ubuntu0.13.04.1 and this is a head from SQL file:

$ head -n 10 mydata.sql 
-- MySQL dump 10.13  Distrib 5.5.22, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version   5.5.22-55-log

This is a snapshot of the line that making an issue:

$ sed -n '65200,65203p' < mydata.sql 
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */

Line below 65203:

/*!50013 DEFINER=`mysql`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_payments` AS select concat(`paypal_payments`.`id`,1) AS `id`,
***SNIP****
,concat(`paypal_payments`.`payer_firstname`,' ',`paypal_payments`.`payer_lastname`,' ',`paypal_payments`.`payer_email`) AS `customer`
***SNIP***
 from `paypal_payments` union select concat(`payment_data`.`id`,2) AS `CONCAT(id,2)`,`payment_data`.`id` AS `id`,`payment_data`.`txndate` AS `txndate`,`payment_data`.`valuedate` AS `valuedate`,concat('DBS - ',`payment_data`.`category`) AS `CONCAT('T - ',category)`,(`payment_data`.`credit` - `payment_data`.`debit`) AS `credit-debit`
***SNIP***
,NULL AS `NULL`,NULL AS `NULL`,NULL AS `NULL`,`payment_data`.`note` AS `note`,`payment_data`.`remaining` AS `remaining` from `payment_data` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

How can I resolve this issue?

Answer 1

The only database object I can think of with the option CREATE ALGORITHM is a VIEW

To see all the views you have, run this

SELECT table_schema,table_name FROM information_schema.views;

or

SELECT table_schema,table_name FROM information_schema.tables WHERE engine IS NULL;

From the given error message, one of the views in your database has a column that no longer exists. This can easily happen if you drop a table or a column from a table that is referenced in the view.

Please look over all your views and make sure you can SELECT from all of them.

CORRECTIVE ACTION

You may have to recreate the mysqldump after you fix the VIEW in the source database.

If you cannot recreate the mysqldump due to the size, you may have edit the mysqldump to ignore the VIEW definition altogether.

Answer 2

In my case the issue happend with a view using union. It's possible to define a view like

select a as acolumn,b,c from t where ...
union
select a,b,c from t where ...
union 
select a,b,c from t where

so that the alias acolumn is only defined in the first part of the select. This view definition caused trouble.

Changing it to

select a as acolumn,b,c from T where ...
union
select a as acolumn,b,c from T where ...
union 
select a as acolumn,b,c from T where ...

fixed things. So two thing were changed: * the alias was defined for all union parts * the correct upper/lower case name for the table name was used I do not know which one of these or whether both together where the culprit