Dump from MySQL 4.0.18 won't load into MySQL 4.1.8

Do I need to upgrade?

[trillianjedi]

I'm trying to import a DB dump from one server to another in order to back it up and work on the data locally.

On server1 I did:-

mysqldump --opt mydatabase > backup.sql

On server2 I'm trying:-

mysql mydatabase_backup < backup.sql

... and it generates the following error:-

ERROR 1064 at line 20: 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 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`anumber` bigi

The actual line that appears to be tripping it up is this one, right at the very start and on the first table create:-

CREATE TABLE `CDR` (
 `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `anumber` bigint(20) NOT NULL default '0',

Any thoughts? Is this a cross-compatability error between the differing versions of MySQL?

Server1 is 4.1.8-standard.

Server2 is 4.0.18

Thanks for any thoughts.

TJ

[JollyK]

I've seen issues with specifying "CURRENT_TIMESTAMP" and "on update CURRENT_TIMESTAMP" before. I don't think you could do that until 4.1, so if you're trying to import a 4.1 db into a 4.0 db, you'll need to take out the "default CURRENT_TIMESTAMP " and maybe also the "on update CURRENT_TIMESTAMP."

JK

[trillianjedi]

OK thanks Jolly.

So basically I need to upgrade?

Or is there any way to force 4.1 to dump in 4.0 format?

TJ

[JollyK]

mysqldump --compatible=mysql40

might work (add the --compatible to what you're already doing with mysqldump). I'm not entirely sure that's compatible with 4.0.x, though. To go way back, you can try:

mysqldump --compatible=mysql323

However, that may cause problems due to the difference between

TYPE=MyISAM

and

Engine=MyISAM

in 3.23 vs 4.x . I'd try the 40 compatibility first, then the 323.

Or just take out the "default CURRENT_TIMESTAMP" bits where you find them.

Or upgrade the 4.0 server to 4.1.

JK

[trillianjedi]

Or just take out the "default CURRENT_TIMESTAMP" bits where you find them.

It's a 60 meg file, so I'm trying to avoid anything manual.

Thanks for the compatible= tip though - got that running now. I'll let you know ;-)

TJ

[JollyK]

Good luck. I've gotten bit by that before. :-)

Let me know if either of those work, btw. It would be handy to know. I've used the 3.23, but not the 40 when moving from 4.1 to 4.0 before.

JK

[trillianjedi]

Ah, close but no banana for me:-

ERROR 1062 at line 2623: Duplicate entry 'Ã?Ãâb' for key 1

Looks like it's an upgrade. Is there an easy way to do that, or better to uninstall and reinstall?

Just read your post above - that was a test with 40 - trying 323.....

I am moving 4.1 to 4.0

Thanks!

TJ

[trillianjedi]

No joy:-

ERROR 1062 at line 2623: Duplicate entry 'Ã?Ãâb' for key 1

What's the easiest way to upgrade server2 - uninstall and reinstall from scratch, or is there an update process?

Thanks!

TJ

[JollyK]

It sort of depends on how you've got it installed, and whether you're depending on the current installation. I usually run on Fedora Linux, so it's either a matter of "rpm -Uvh NewVersion.rpm" or "yum upgrade mysql".

I've also been known to compile the servers from source, though.

Do you know how the 4.0.x got installed in the first place? I don't *think* there is an official update process, but you might go check mysql.com and see if there is. Most of their info seems to be for version 5.x now, though.

JK

[trillianjedi]

I'd love to have Fedora on this box but it won't install (SCSI driver issues). I'm currently running Mandrake 10.0 on it (only thing that would install!).

MySQL 4.0.18 came with it.

I've grabbed the 4.1.8 RPM from MySQL (that matches the version currently on my live server).

I guess I need to uninstall the old one first?

Thanks - I'm OK at running things and generally messing with *nix, but I'm not up to speed on installing stuff....!

TJ

[Demaestro]

trillianjedi

Just a quick question. Have you read the release notes on this newer version? What new features does this latest version have that you want to take advantage of?

If there is nothing major that you want to take advantage of you may want to consider holding off on your upgrade. I mean upgrading for the sake of upgrading isn't always worth it especially considering the headache you seem to be having with the whole thing.

Which I could help with the export but I am not a MySQL guy.

[JollyK]

Demaestro: I think TJ is upgrading for backup purposes (being able to mysqldump one server to another).

TJ: Doesn't Mandrake use rpm? If so, you may be able to do "rpm -Uvh new-mysql.rpm" and have it upgrade to the new rpm.

The problem is that if you got the new version from Mysql.com, and the old version came with Mandrake, they're likely to be two different setups.

So ...

I would first see if there is a later version available from Mandrake.

If not, then, yes, you may have to uninstall the current Mysql and install the new RPM.

(I feel like I'm being awfully vague, but there are a lot of variables to consider, and I'm trying to cover all the bases without getting too far afield.)

IMPORTANT! Do a mysqldump on your current data before upgrading "just in case."

JK

[trillianjedi]

Demaestro - yes as Jolly says, it's for backup (spare machine), so I really only want to upgrade to make it compatible with the current "live" server so I can import DB dumps into it.

Jolly - this is a "virgin" server - there's nothing on it at all. Might actually be easier for me to just download the latest version of Mandriva, or even try Fedora Core 4 (3 was the last one I tried and that didn't like my SCSI card).

You're right about version compatability - I tried the rpm -Uvh with the MySQL RPM and it barfed...

As I don't have anything installed on this machine yet, I am cosidering toying with OS's a bit more. Fedora Core 4 being downloaded now. I'll know by the first CD if it's going to work with the SCSI card or not!

TJ

[JollyK]

By the way, what OS is the server with 4.1.8 running?

Also, did you try uninstalling all MySQL rpms before installing the new ones? You'd probably have to uninstall mysql, mysql-devel, and maybe mysql-perl then reinstall from the new MySQL rpms. On the other hand, if the rpms from MySQL are built for Redhat or Fedora, there may be library incompatibilities with Mandrake.

Grr.

So many variables, like I said.

JK

[old_expat]

TJ, if you have to do this manually, search for "Freeware Hex Editor XVI32"

This thing handles B-I-I-I-G files and edits FAAAAAST!

Intuitive enough that even old expats can use it.:)

[trillianjedi]

By the way, what OS is the server with 4.1.8 running?

Red Hat Enterprise 3

XVI32

Yes, used that before (it's great). But I'll be doing this regularly, so I need it to be right rather than having to put it through something first.

TJ

[JollyK]

Hey, TJ, did you ever get the upgrade working?

JK

[trillianjedi]

No, couldn't get the install to work on Mandrake, so I reinstalled Mandrake without MySQL and tried to install it from scratch. That didn't work either.

Currently downloading Red Hat Enterprise and I'll give that a go.

Apparently Red Hat SMB 9.0 will work on this box with this SCSI card, but I can't find anywhere to download that anymore.

Where's Archie when you need it?!

TJ