[SOLVED] Errors Upgrading to 3.1

I was running 3.0.2. Trying to upgrade to 3.1. I backed up, and ran the maintenance upgrade, and got the below:

A fatal MySQL error was encountered.
Query: create unique index byitem on hpfanfiction_favorites (item,type,uid);
Error: (1062) Duplicate entry '439-ST-316' for key 1

When I tried to run it again, I got:

A fatal MySQL error was encountered.
Query: alter table hpfanfiction_authorinfo add primary key(uid,field);
Error: (1068) Multiple primary key defined

EDIT: Here are the fixes per JRabbit:

The following mysql statements run before the update should de-duplicate the table. You'll need to add your table prefix to fanfiction_favorites on the middle 4 lines.  WARNING: This deletes data from your database - back it up first!

NOTE: Don't forget to put a (Prefix) before the fanfiction part if you have one. Mine was HP so I had to make all of mine hpfanfiction_blah.

create temporary table t (uid int, item int, type char(2), id int);
alter table fanfiction_favorites add column id int not null key auto_increment;
insert into t select uid,item,type,min(id) from fanfiction_favorites where id is not null group by uid,item,type having count(*) >1;
delete f from fanfiction_favorites f inner join t on f.uid=t.uid and f.item=t.item and f.type=t.type and f.id>t.id;
alter table fanfiction_favorites drop column id;
drop table t;

The authorinfo table is a bit trickier as the "duplicate" rows may have the same user ID and field names but different field values. There are two versions of the clean script here. This is the safe script - it will only remove rows that are absolute duplicates. Again, you need to put your prefix in front of the table name in the middle 4 lines.

Warning: This script deletes data from your database. Back it up first!

create temporary table t (uid int, field int, info varchar(255), id int);
alter table fanfiction_authorinfo add column id int not null key auto_increment;
insert into t select uid,field,info, min(id) from fanfiction_authorinfo where id is not null group by uid,field,info having count(*) >1;
delete f from fanfiction_authorinfo f inner join t on f.uid=t.uid and f.field=t.field and f.info=t.info and f.id>t.id;
alter table fanfiction_authorinfo drop column id;
drop table t;

After running this the following query will reveal if there remain any duplicate fields with different values:

select uid,field from fanfiction_authorinfo group by uid,field having count(*) >1;

If it does you should look at cleaning them up by hand as the script cant tell which one of the two different values for the field are correct. However, you should be aware that efiction will be retrieving one of these values at random when ever they are queries so the system is unstable while they both exist. If you really don't mind which one is kept, the following script will keep one copy of each field but I'd ask for advice if you don't know what the meaning of the particular field in question is before doing so, and still recommend manual cleanup unless there are too many duplicates to remove.

Warning: This script deletes data from your database. Back it up first!

create temporary table t (uid int, field int, id int);
alter table fanfiction_authorinfo add column id int not null key auto_increment;
insert into t select uid,field,min(id) from fanfiction_authorinfo where id is not null group by uid,field having count(*) >1;
delete f from fanfiction_authorinfo f inner join t on f.uid=t.uid and f.field=t.field and f.id>t.id;
alter table fanfiction_authorinfo drop column id;
drop table t;

I did all of these and the upgrade worked fine. Thanks JRabbit !

Dreamy