DB upgrade from 3.4.4 to 3.8.0

Curtis_Bruneau · July 24, 2008, 7:50pm

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

Curtis_Bruneau · July 24, 2008, 8:33pm

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@blahtest.com’ for key 2

Curtis

Curtis_Bruneau · July 24, 2008, 9:00pm

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@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