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

[Curtis Bruneau]

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