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

Curtis Bruneau curtisb at vianet.ca
Thu Jul 24 17:00:51 EDT 2008


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



More information about the rt-users mailing list