4

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?

  • 1
    Please show all of the lines from 65203 through the next semicolon, a few lines down. – Michael - sqlbot Sep 25 '13 at 3:52
  • @Michael-sqlbot updated. – scalopus Sep 26 '13 at 6:56
  • 3
    There have been several votes to close this question as "too localized" but I don't think that's exactly the case, in spite of appearances to the contrary. This looks like a case of a known issue where mysqldump actually generates an invalid and non-restorable view definition from a working view because of quirks in the way the view was originally defined. – Michael - sqlbot Sep 26 '13 at 12:09
  • If you take the view definition as seen in the file, paste it into a client and try to execute it, does it fail? If this is the situation I think it is, there will be cases of two backticks `` adjacent to each other. Please find all of such cases and un-snip the output in your question with the additional lines and let's see if we find what I expect. We can then fix this question to apply more broadly to the underlying issue. – Michael - sqlbot Sep 26 '13 at 12:15
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.

  • 1
    It can't be a view defined against a column that doesn't exist, because if that had been the case, mysqldump would have refused to dump the view in the first place, ERROR 1356 (HY000) ... View 'xxx' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. I'm expecting, instead, to find that condition where SHOW CREATE VIEW generates an invalid definition of a working view, usually involving subqueries or unions, which could be fixed by redefining the view on the server where the dump was sourced. – Michael - sqlbot Sep 26 '13 at 12:03
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.