export sql and then import sql to new server woes!

Rollo Tamasi

hi, i'm having trouble using bigdump to move a fairly large sql file from one host to another. BigDump keeps preventing the upload process at the same stage due to a syntax error but i uploaded the exact same file to another server using the bigdump with any problems before.

I'm fed up off BigDump and i'm now looking for an alternative (PHP based)

DonkeyStyle \o/

Big dumps are always a pain in the arse.

forbairt

not knowing the problem too well ...

mysqldump isn't an option ?? ssh access to both servers ?

leeroybrown

Are you sure that the cause of the problem is BigDump and not the contents of the dump itself?

What versions of MySQL are running on the servers?

Rollo Tamasi

Error at the line 117905: INSERT INTO phpbb_asearch_wordlist (word_text, word_id, word_common) VALUES('françois', '6715', '0');
Query: INSERT INTO phpbb_asearch_wordlist (word_text, word_id, word_common) VALUES('françois', '6715', '0');
MySQL: Duplicate entry 'françois' for key 1
Stopped on error
Start from the beginning[/URL] (DROP the old tables before restarting)

^

The error from big dump




OLD server is MySQL 4.1.12
New server is 3.23.58
Is that the problem?

leeroybrown

Upgrades from 3.23 to 4.X can be problematic as some protected keywords changed for the new major version. A downgrade might be awkward for the same reason. A list of the upgrade issues can be found here. They might give you some idea what could be breaking when you go in the other direction.

EDIT:

MySQL 3 has no CHARSET support. mysqldump itself has a couple of handy options for these situations '--compatible=mysql323' and '--default-character-set=XXXX' (where XXX is latin1,etc). Perhaps you can find a way to use these options to create a dump compatible with the new server.

Rollo Tamasi

friggin webhosts are a nightmare for me. They upgrading my panel from HSP to Plesk, but downgraded mysql on me. Madness! I'm sorting them out now. I'll send them an email with this in the subject > :mad:

jmcc

Rollo Tamasi wrote:

^

The error from big dump

OLD server is MySQL 4.1.12
New server is 3.23.58
Is that the problem?

Is the new database clean? I am not sure about Bigdump - I tend to use mysqldump generally. But if you have an --add-drop-table option and have not used it, that could be the problem. The db may have partially loaded in the new db so every subsequent attempt to load will fail if the table isn't cleared/deleted first.

Regards...jmcc

Rollo Tamasi

after each fail i was dumping. SSH is the most viable option alright, live and learn i suppose.

tis' gonna be a late one for myself now......:(

jmcc

Rollo Tamasi wrote:

after each fail i was dumping. SSH is the most viable option alright, live and learn i suppose.

tis' gonna be a late one for myself now......:(

Just on the offchance - do a select for francois (can't rem the code for the cidella(?) squiggle) on the new db before you try to load anything. While it could be a character related problem, the chances are that the dump does not drop the existing table if it exists and recreate it with the new data.

Regards...jmcc

Rollo Tamasi

i was thinking that myself. Especially since françois actually appears as francois when i search for it in the forum. I had an issue with chars which i couldn't understand. The old mysql settings where set to UTF8 too.

Rollo Tamasi

christ look at the time! i'm going nowhere fast here. Could really do with some help....


Summary of problem
Trying to export a db into a new server. I keep getting sql syntax errors (see below for another example). I have opened the sql file and removed the code, yet a replica of the same error appears.

Exporting Server is running:
MYSQL 4.1.12
Language: English (en-utf-8)
MySQL Charset: UTF-8 Unicode
MySQL connection collation: utf8_bin
All rows are MyISAM and have collation latin1_swedish_ci

Importing Server is running:
MYSQL 4.1.12
Language: English (en-utf-8)
MySQL Charset: UTF-8 Unicode
MySQL connection collation: utf8_general_ci
All rows are MyISAM and have collation latin1_swedish_ci

Would the difference in the collation types make any difference?