• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

Problem restoring MySQL 3.23 dump files on new server with MySQL 4.0.18

After a HDD crash on my Suse Linux 8.1 Server running MySQL 3.23 I set up a new Server with Suse Linux 9.1 running MySQL 4.0.18.

Now I tried to restore my MySQL database dumpfiles by using the command:
mysql <MyDatabase> -u <user> -p <password> < <MyDumpFileName>

After a while I get the following error:
ERROR 1064 at line 872: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'option varchar(50) NOT NULL default '', ordering
int(11) unsi ...

It seems MySQL 4 canĀ“t read MySQL 3 dumpfiles.

Is there Any Solution to read these "old" dumpfiles or to convert
MySQL 3 Dumpfiles to MySQL 4 format?
0
haackhenry
Asked:
haackhenry
  • 4
  • 3
1 Solution
 
UmeshMySQL Principle Technical Support EngineerCommented:
Hi,

Try with --compatible=name  parameters..

mysql  -uusername -p --compatible=name  databasename < dumpfile.sql


Produce output that is compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See section 5.2.2 The Server SQL Mode. This option requires a server version of 4.1.0 or higher. With older servers, it does nothing.

Check this link for more info..

http://dev.mysql.com/doc/mysql/en/mysqldump.html
0
 
UmeshMySQL Principle Technical Support EngineerCommented:


Hi,

>>This option requires a server version of 4.1.0 or higher. With older servers, it does nothing

I think for this you need mysql server version of 4.1.0 or higher.. which may not be stable at the moment.. check out mysql.com for more..
0
 
haackhenryAuthor Commented:
That does not work:

mysql: ERROR: unknown variable 'compatible=mysql323'

And producing a new dumpfile is impossible because I updated from 3.23 to 4.0.18 and on 3.23 it is not possible to set an output option that produces output for 4.0.18

By the way - I mentioned that I had a HDD crash and I had to set up a new server so it is impossible to create a new output.

Thanks
Henry
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
UmeshMySQL Principle Technical Support EngineerCommented:
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Any  updates?
0
 
haackhenryAuthor Commented:
Thanks

Thats not what I seek - this manual is only useful if you upgrade a running system from 3.23 to 4.x I have 3.23 Dumpfiles and want to restore them on a new 4.0.18 system.
0
 
SqueebeeCommented:
Option is a reserved word. Do a search and replace in your dump file and replace option with `option` (wrapped in back-ticks). Or replace it with a different table name altogether.
0
 
haackhenryAuthor Commented:
Ok that willl be a lot of work but it works fine. Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now