[rt-users] ERROR 1062 during 3.4.5 -> 3.8.2 database update

[Jim Faulkner]

Just wanted to follow up on this.  I was able to work around the 
"Duplicate entry" mysql error by modifying the "ALTER TABLE" command that 
caused the problem to be "ALTER IGNORE TABLE."  The IGNORE mysql extension 
is described on this page:
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Basically it will delete any duplicate rows encountered.  This is fine for 
my purposes, because I am confident that any duplicate rows in the Users 
table were caused by spam e-mails.  I will be running rt-shredder and 
rt-validator as well to delete any spam tickets associated with those 
users and make sure that the RT database is OK.

On Wed, 18 Feb 2009, Jim Faulkner wrote:

>
> I'm getting the "ERROR 1062 (23000) at line 210: Duplicate entry '' for key 2"
> error when updating the database from 3.4.5 to 3.8.2.  I see from this message:
> http://www.mail-archive.com/rt-users@lists.bestpractical.com/msg17534.html
>
> that I have a few different choices as to how to solve this.  However, I'm not
> a mysql expert, so I'm afraid I'll need a little more help.
>
> I've tried changing the Users table's collation to utf8_unicode_ci, however I
> still get the same error when updating the database.  What collation should I
> use to prevent this error?  The original collation is latin1_swedish_ci.
>
> I've also tried to find the affected rows so that I can rename the users.
> However, I'm not getting anywhere.  The error says that the duplicate
> entry is '', however "select * from Users where Name='';" returns zero
> rows.  I've also tried dumping the Name column into a text file and using
> the uniq unix command, but it finds no duplicates.  What is the best way
> to find duplicate entries?
>
>

-- 
Jim Faulkner / james.faulkner at cs.yale.edu
Systems Administrator, Linux Systems Design & Support (LSDS)
Yale University Information Technology Services (ITS)
===============================================================
NOTE: Yale ITS will NEVER request passwords or other personal
information via email. Messages requesting such information are
fraudulent and should be deleted.