Re: [dspam-users] Migration of MySQL data from 3.23 schema to 4.1 schema

Upgrade almost finished, but it fails at the very last step (the one I
kept as last), which is the migration of dspam_token_data.token from
char(20) to bigint(20).

Following 3.2's UPGRADING, I did:
mysql> alter table dspam_token_data modify token bigint unsigned;

It starts on it, but after a while I get:
ERROR 1062 (23000): Duplicate entry '12852-0' for key 1

Which I find to be very odd, because there's a UNIQUE index, called
'id_token_data_01' on 'uid,token', which should've made it impossible to
have a duplicate in the original table in the first place (or am I wrong
there?).

Anyone has any ideas on how to circumvent this problem or what to do
next? Or is the only real way to drop the index, change the type, then
recreate the index (note that there are about 6M records in that table,
so it would take some time ;) ).

Sincerely

Jelle Hillen
Cats & Dogs bvba
Industrieweg 1506
3540 HERK-DE-STAD - BELGIUM
http://www.catsanddogs.com/
Tel : +32 13 539110
Fax : +32 13 539111

John Peacock wrote:
> Jelle Hillen wrote:
>
>> Is it somewhere remotely possible to move the data from the old schema
>> to the new schema, or would this be virtually impossible?
>
>
> It isn't a problem at all. Dump the old schema out of 3.23 and then
> load it into 4.1. Then perform `alter table` commands until the tables
> match the new table definitions; MySQL is smart enough to know that if
> you change a char(20) containing something that looks like an integer,
> it will put the correct value into the bigint(20) field.
>
> You will want to look at the UPGRADING document for dspam as well, since
> that has instructions for changing the tables when going from various
> versions of dspam. In particular, the upgrade from 3.0 to 3.2 may
> require other changes than you already mentioned (I don't remember);
> you'll need to download 3.2's version of UPGRADING to see.
>
> HTH
>
> John
>
Received on Tue Nov 15 06:07:07 2005