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