Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ? My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1" my MySQL(dumper) version is "mysqldump Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (x86_64)".

The error is: 
ERROR 1166 (42000) at line 1572: Incorrect column name 'DATEDIFF(current_date,DATE_ADD(created_at,INTERVAL (updated_at*30) DAY))'

head file:

$ head dump.sql



> -- MySQL dump 10.13  Distrib 5.1.34, for apple-darwin9.5.0 (i386)
> --
> -- Host: localhost    Database: moov3_production
> -- ------------------------------------------------------
> -- Server version 5.0.51a-24+lenny2
share|improve this question
1  
Need to see the full output of mysql, and possibly more of your dump file. Does this fail immediately or does it do some work first? On which table is this failing? –  Gaius Nov 18 '11 at 15:28
2  
Specifically we will need line 1572 of the dump file and the full CREATE TABLE statement from the file for the table it is trying to insert into. If that line is very long, it might help to re-do the dump with --skip-extended-insert. –  Ladadadada Nov 18 '11 at 15:57

1 Answer 1

Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

  1. MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
  2. Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
  3. You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!

share|improve this answer
    
i've already tried this but it gives me the same error. this like is a VIEW CREATION.. i'll try with the correct binary.. –  Guilherme Nov 19 '11 at 14:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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