[rt-users] DB upgrade from 3.4.4 to 3.8.0.

[Curtis Bruneau]

Curtis Bruneau wrote:
> Curtis Bruneau wrote:
>> I'm having an issue with the mysql 4.0 -> 4.1 conversion, the script 
>> performs ok (i had to update my DBD::mysql) it generates the proper 
>> SQL, I went a step further and merged each tables ALTER into two 
>> commands (before-after) so our bigger tables didn't have to dump more 
>> then it needs to (very time consuming)..
>>
>> I have run into an issue with our Users table, we have several emails 
>> with french accents (é) that seem to convert properly but the UNIQUE 
>> contraint complains that é and e are the same, I had tried removing 
>> the constraint and it converts but won't let me add the index again, 
>> i had to change my client charset to view the chars properly in mysql 
>> client once converted to utf8 from latin1. Does anyone know how I can 
>> solve this? Does the server need a setting to differentiate the two 
>> in UTF8?
>>
>> Thanks
>>
>> Curtis
>>
> Interesting to note for Users, when i do a direct conversion (skip the 
> first alter) it seems to work out fine, it's able to differentiate the 
> two chars. Also something to note we sometimes have emails show up 
> with accents so the ascii char set for EmailAddress breaks the char 
> where the old latin1 was fine, whether or not those chars are valid 
> emails i'm not sure but if the user sets it as that it will be 
> recognized. Below is an example of both conversions. I'll probably 
> have to apply my own logic to some of these conversions to make sure 
> they go ok, I'll be testing shortly not that the tables have somewhat 
> converted.
>
>
> ##DIRECT
> mysql> ALTER TABLE Users DEFAULT CHARACTER SET 
> utf8;                                                                                                       
>
> Query OK, 183991 rows affected (6.97 sec)
> Records: 183991  Duplicates: 0  Warnings: 0
>                                                                                                         
>
> mysql> ALTER TABLE Users MODIFY WebEncoding VARCHAR(16) CHARACTER SET 
> ascii NULL DEFAULT NULL, MODIFY AuthSystem VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY MobilePhone VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY WorkPhone VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY PagerPhone VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY ExternalContactInfoId VARCHAR(100) 
> CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY ContactInfoSystem 
> VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY HomePhone 
> VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Address1 
> VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> ExternalAuthId VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY NickName VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY Address2 VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY Timezone VARCHAR(50) CHARACTER SET ascii NULL DEFAULT NULL, 
> MODIFY RealName VARCHAR(120) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY City VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> EmailAddress VARCHAR(120) CHARACTER SET ascii NULL DEFAULT NULL, 
> MODIFY EmailEncoding VARCHAR(16) CHARACTER SET ascii NULL DEFAULT 
> NULL, MODIFY State VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY Zip VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> Organization VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> Lang VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL, MODIFY Gecos 
> VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Country 
> VARCHAR(50) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Name 
> VARCHAR(200) CHARACTER SET utf8 NOT NULL DEFAULT '';
> Query OK, 183991 rows affected, 34 warnings (5.19 sec)
> Records: 183991  Duplicates: 0  Warnings: 0
>
> ## BINARY CONVERT
> mysql> ALTER TABLE Users DROP INDEX Users1;
> Query OK, 183991 rows affected (4.97 sec)
> Records: 183991  Duplicates: 0  Warnings: 0
>
> mysql> ALTER TABLE Users DEFAULT CHARACTER SET utf8;
> Query OK, 183991 rows affected (6.95 sec)
> Records: 183991  Duplicates: 0  Warnings: 0
>
> mysql> ALTER TABLE Users MODIFY WebEncoding VARBINARY(16) NULL DEFAULT 
> NULL, MODIFY AuthSystem VARBINARY(30) NULL DEFAULT NULL, MODIFY PGPKey 
> BLOB NULL DEFAULT NULL, MODIFY Password VARBINARY(40) NULL DEFAULT 
> NULL, MODIFY MobilePhone VARBINARY(30) NULL DEFAULT NULL, MODIFY 
> WorkPhone VARBINARY(30) NULL DEFAULT NULL, MODIFY PagerPhone 
> VARBINARY(30) NULL DEFAULT NULL, MODIFY ExternalContactInfoId 
> VARBINARY(100) NULL DEFAULT NULL, MODIFY ContactInfoSystem 
> VARBINARY(30) NULL DEFAULT NULL, MODIFY HomePhone VARBINARY(30) NULL 
> DEFAULT NULL, MODIFY Address1 VARBINARY(200) NULL DEFAULT NULL, MODIFY 
> ExternalAuthId VARBINARY(100) NULL DEFAULT NULL, MODIFY Comments TEXT 
> CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY NickName VARBINARY(16) 
> NULL DEFAULT NULL, MODIFY Address2 VARBINARY(200) NULL DEFAULT NULL, 
> MODIFY Timezone VARBINARY(50) NULL DEFAULT NULL, MODIFY 
> FreeformContactInfo TEXT CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> RealName VARBINARY(120) NULL DEFAULT NULL, MODIFY City VARBINARY(100) 
> NULL DEFAULT NULL, MODIFY EmailAddress VARBINARY(120) NULL DEFAULT 
> NULL, MODIFY EmailEncoding VARBINARY(16) NULL DEFAULT NULL, MODIFY 
> State VARBINARY(100) NULL DEFAULT NULL, MODIFY Signature TEXT 
> CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Zip VARBINARY(16) NULL 
> DEFAULT NULL, MODIFY Organization VARBINARY(200) NULL DEFAULT NULL, 
> MODIFY Lang VARBINARY(16) NULL DEFAULT NULL, MODIFY Gecos 
> VARBINARY(16) NULL DEFAULT NULL, MODIFY Country VARBINARY(50) NULL 
> DEFAULT NULL, MODIFY Name VARBINARY(200) NOT NULL DEFAULT '';
> Query OK, 183991 rows affected (8.25 sec)
> Records: 183991  Duplicates: 0  Warnings: 0
>
> mysql> ALTER TABLE Users MODIFY WebEncoding VARCHAR(16) CHARACTER SET 
> ascii NULL DEFAULT NULL, MODIFY AuthSystem VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY MobilePhone VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY WorkPhone VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY PagerPhone VARCHAR(30) CHARACTER SET 
> utf8 NULL DEFAULT NULL, MODIFY ExternalContactInfoId VARCHAR(100) 
> CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY ContactInfoSystem 
> VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY HomePhone 
> VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Address1 
> VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> ExternalAuthId VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY NickName VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY Address2 VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY Timezone VARCHAR(50) CHARACTER SET ascii NULL DEFAULT NULL, 
> MODIFY RealName VARCHAR(120) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY City VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> EmailAddress VARCHAR(120) CHARACTER SET ascii NULL DEFAULT NULL, 
> MODIFY EmailEncoding VARCHAR(16) CHARACTER SET ascii NULL DEFAULT 
> NULL, MODIFY State VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL, 
> MODIFY Zip VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> Organization VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY 
> Lang VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL, MODIFY Gecos 
> VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Country 
> VARCHAR(50) CHARACTER SET utf8 NULL DEFAULT NULL, MODIFY Name 
> VARCHAR(200) CHARACTER SET utf8 NOT NULL DEFAULT '';
> Query OK, 183991 rows affected (8.57 sec)
> Records: 183991  Duplicates: 0  Warnings: 0
>
> mysql> ALTER TABLE Users ADD UNIQUE KEY `Users1` USING BTREE (`Name`);
> ERROR 1062 (23000): Duplicate entry 'securite at blahtest.com' for key 2
>
>
> Curtis
>
This has brought up a similar issue where if the client charset is in 
UTF8 it doesn't see the converted characters as valid UTF8, so I'm 
definitely in a bind to get it converted properly where the constraints 
work properly but the display works fine in UTF8 (presumably). Has 
anyone else had these issues?

Thanks for your time
Curtis