Problem restoring database 'DEFAULT CHARSET=latin1'

[BianchiDude]

Why am I getting this error?

root@102 [/backup/cpbackup/monthly/kapp/mysql]# mysql kapp_newforum < kapp_newforum.sql
ERROR 1064 at line 21: 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 'DEFAULT CHARSET=latin1' at line 10

[darkelder]

Which mySQL version do you use? My guess is that you're using mySQL 3 or 4.0 and your sql dump is from 4.1

[BianchiDude]

Which mySQL version do you use? My guess is that you're using mySQL 3 or 4.0 and your sql dump is from 4.1

Yes, my dump is from 4.1 and I am restoring to a server that has 4. How can I fix this?

[darkelder]

Edit dump and remove all "DEFAULT CHARSET=latin1" at end of lines that start with CREATE

[Infopro]

I found this via google. Might be worth investigating.

I didn't catch whether the original postee still has access to the old database, but for future reference, the mysql 4.1.x version of mysqldump (the command line backup utility) has a backwards compatibility switch.

--compatible=name
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.

Every time I backup my db I do so with each of mysql323, mysql40 and without the switch so no matter where I go in the future, should the need arise, I have a usable backup.

[sanjuabrahamk]

Hello,

Simple solution. No need to edit the dump.

Please take the dump from the server with mysql version 4.1 with the following command


shell> mysqldump --create-options --compatible=mysql40 db_name > dump_file

After that restore the dump to the server with mysql version 4.0

It will work.

Thanks
Sanju Abraham

www.logicsupport.com