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 '[EMAIL PROTECTED]' 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 _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
