Re: [rt-users] ERROR 1062 during 3.4.5 - 3.8.2 database update
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.faulk...@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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] ERROR 1062 during 3.4.5 - 3.8.2 database update
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.faulk...@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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] ERROR 1062 during 3.4.5 - 3.8.2 database update
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? ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] ERROR 1062 during 3.4.5 - 3.8.2 database update
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.faulk...@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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com