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

[Jim Faulkner]

One more follow up -- turns out that the ERROR 1062 was due to a failed 
database dump and restore.  I needed to add "--set-charset=binary" to my 
mysqldump command line when dumping the old RT database, and just in case 
I added "set names 'binary'" to the top of the resulting dump before 
importing it.  After that the sql.queries generated by 
upgrade-mysql-schema.pl worked fine without modifying the queries.


On Thu, 19 Feb 2009, 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?
>> 
>> 
>
>

-- 
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.