[rt-users] 3.6.7 to 3.8.2 upgrade issue (mysql)

[Ruslan Zakirov]

On Wed, Jun 3, 2009 at 11:51 AM, Karel Vervaeke <karel at outerthought.org> wrote:
> Hi list,
>
> Due to persistent memory problems I decided to try and upgrade to a
> more recent version of RT. (RT 3.6 was installed on a xen VM running
> Debian etch using the default apt package.  In the mean time the
> machine has been upgraded to Debian lenny, without side-effects for
> RT.  After the distro upgrade RT 3.8 was installed (again using apt).
>
> The mysql db upgrade script was generated like this:
> perl /usr/share/request-tracker3.8/etc/upgrade/upgrade-mysql-schema.pl
> rtdb rtuser rt > rt-upgrade-mysql.sql
>
> Unfortunately the upgrade script fails at line 210:
> 209  ALTER TABLE Users MODIFY Name VARBINARY(200) NOT NULL;
> 210  ALTER TABLE Users MODIFY Name VARCHAR(200) CHARACTER SET utf8 NOT NULL;
>
> This is due to the fact that we have these records in the users database:
> 703 xxxxxxxxxe at customer.com
> 723 xxxxxxxxxé@customer.com
>
> Both email addresses belong to the same person - the Users table
> currently is using charset latin1.
> What's the best way to move forward?
> * Should I alter the upgrade script to make the Name column accent sensitive?
> * Alternatively I update all tables so they point to the same user
> record and remove the conflicting one (but what will happen when a new
> email from that address arrives?)

If accounts are duplicates then you can:
* rename all duplicates and leave only primary accounts unchanged, by
adding a "-duplicate" suffix or something like that
* apply the rest commands

Next time RT will use primary account cuz mysql treat e equal to é
when default collation is used.

If accounts are different and you don't want to treat e equal to é
then you should adjust SQL commands the script generates and set
different collation for Name column, the same applies to EmailAddress.


> Regards,
> Karel
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.