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

Posted on 2004-09-22
Last Modified: 2010-08-05
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?
Question by:haackhenry
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 26

Expert Comment

ID: 12121881

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..
LVL 26

Expert Comment

ID: 12121998


>>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 for more..

Author Comment

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

LVL 26

Expert Comment

ID: 12122173
LVL 26

Expert Comment

ID: 12131715
Any  updates?

Author Comment

ID: 12132359

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.
LVL 17

Accepted Solution

Squeebee earned 500 total points
ID: 12140824
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.

Author Comment

ID: 12140994
Ok that willl be a lot of work but it works fine. Thanks

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question