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

[Curtis Bruneau]

I had the same problem and it was caused by accents, since mysql has no 
accent sensitive collations (_as) aside from utf8_bin which is 
everything sensitive, I basically just removed the user (or rename) to 
fix the issue. Since emails generally shouldn't have them it's pretty 
safe to do, the old latin collations did support them to some degree.

Jim Faulkner wrote:
> 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?
>>
>>
>>     
>
>