Solved

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

Posted on 2004-09-22
8
487 Views
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?
0
Comment
Question by:haackhenry
  • 4
  • 3
8 Comments
 
LVL 26

Expert Comment

by:ushastry
ID: 12121881
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
 
LVL 26

Expert Comment

by:ushastry
ID: 12121998


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
 

Author Comment

by:haackhenry
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.

Thanks
Henry
0
 
LVL 26

Expert Comment

by:ushastry
ID: 12122173
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:ushastry
ID: 12131715
Any  updates?
0
 

Author Comment

by:haackhenry
ID: 12132359
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
 
LVL 17

Accepted Solution

by:
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.
0
 

Author Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now