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@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.
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:
Re: [rt-users] Error message in upgrade from 3.6.5 to 3.8.1
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@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.
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:
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:
Re: [rt-users] Error message in upgrade from 3.6.5 to 3.8.1
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@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.