Binary files broken since upgrade to RT 3.8

Dominic_Lepiane · June 2, 2009, 8:55pm

Dear RT users,

I am trying to get our RT installation moved from RT 3.6.6 to 3.8.3.
I’ve tried following the upgrade steps in the UPGRADING.mysql and the
README files as diligently as I can. The DBMS is MySQL 5.0.45. I
updated all the perl modules… When I try the new version, everything
coming in works okay, but all the old binary attachments are broken, in
truth, anything other than regular ascii is mangled (e.g. diacritical
marks like ï¿½, ï¿½, ï¿½ etc). If I try to open an image, it says “The image
http://rt/Ticket/Attachment/672/883/spots1.bmp"cannot be displayed,
because it contains errors.”.

Clearly I’ve either missed something or something has to be done to
convert this data. If I convert the Content column back to longtext
from longblob, then the binary data works again.

Please advise what can I do to get the data into the new table schema.

Thanks,

Dominic

James_Moseley · June 2, 2009, 9:10pm

Did you apply the schema updates as indicated in steps 4-6 of
UPGRADING.mysql?

James MoseleyDominic Lepiane Dominic.Lepiane@ptgrey.com wrote:

Dear RT users,

I am trying to get our RT installation moved from RT 3.6.6 to 3.8.3.
I’ve tried following the upgrade steps in the UPGRADING.mysql and the
README files as diligently as I can. The DBMS is MySQL 5.0.45. I
updated all the perl modules… When I try the new version, everything
coming in works okay, but all the old binary attachments are broken, in
truth, anything other than regular ascii is mangled (e.g. diacritical
marks like é, ô, ç etc). If I try to open an image, it says “The image
http://rt/Ticket/Attachment/672/883/spots1.bmp"cannot be displayed,
because it contains errors.”.

Clearly I’ve either missed something or something has to be done to
convert this data. If I convert the Content column back to longtext
from longblob, then the binary data works again.

Please advise what can I do to get the data into the new table schema.

Dominic_Lepiane · June 2, 2009, 9:27pm

I did run steps 4-6 from the UPGRADING.mysql , yes. I’ve included the
generated SQL below. I should note the DBMS is on a separate host from
the RT (Apache / Postfix) server. Actually, now that I think about it,
when I generated the SQL, there was this message:

“Use of uninitialized value in join or string at
etc/upgrade/upgrade-mysql-schema.pl line 261.”

But as I recall, when I looked for more info on this only, other people
reported seeing this message but then did not have any problems. When I
ran the SQL (below), there was no error. It took a while, but did not
produce any errors.

Thanks,

Dominic

ALTER DATABASE rt3 DEFAULT CHARACTER SET utf8;
ALTER TABLE ACL
DEFAULT CHARACTER SET utf8,
MODIFY RightName VARBINARY(25) NOT NULL DEFAULT ‘’,
MODIFY PrincipalType VARBINARY(25) NOT NULL DEFAULT ‘’,
MODIFY ObjectType VARBINARY(25) NOT NULL DEFAULT ‘’;
ALTER TABLE ACL
MODIFY RightName VARCHAR(25) CHARACTER SET ascii NOT NULL DEFAULT ‘’,
MODIFY PrincipalType VARCHAR(25) CHARACTER SET ascii NOT NULL DEFAULT ‘’,
MODIFY ObjectType VARCHAR(25) CHARACTER SET ascii NOT NULL DEFAULT ‘’;
ALTER TABLE Attachments
DEFAULT CHARACTER SET utf8,
MODIFY ContentType VARBINARY(80) NULL DEFAULT NULL,
MODIFY MessageId VARBINARY(160) NULL DEFAULT NULL,
MODIFY Content LONGBLOB NULL DEFAULT NULL,
MODIFY ContentEncoding VARBINARY(80) NULL DEFAULT NULL;
ALTER TABLE Attachments
MODIFY ContentType VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY MessageId VARCHAR(160) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY ContentEncoding VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE Attributes
DEFAULT CHARACTER SET utf8,
MODIFY ContentType VARBINARY(16) NULL DEFAULT NULL,
MODIFY Content BLOB NULL DEFAULT NULL,
MODIFY ObjectType VARBINARY(64) NULL DEFAULT NULL;
ALTER TABLE Attributes
MODIFY ContentType VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY ObjectType VARCHAR(64) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE CustomFields
DEFAULT CHARACTER SET utf8,
MODIFY LookupType VARBINARY(255) NOT NULL DEFAULT ‘’,
MODIFY Type VARBINARY(200) NULL DEFAULT NULL;
ALTER TABLE CustomFields
MODIFY LookupType VARCHAR(255) CHARACTER SET ascii NOT NULL DEFAULT ‘’,
MODIFY Type VARCHAR(200) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE CustomFieldValues
DEFAULT CHARACTER SET utf8;
ALTER TABLE GroupMembers
DEFAULT CHARACTER SET utf8;
ALTER TABLE Groups
DEFAULT CHARACTER SET utf8,
MODIFY Domain VARBINARY(64) NULL DEFAULT NULL,
MODIFY Type VARBINARY(64) NULL DEFAULT NULL;
ALTER TABLE Groups
MODIFY Domain VARCHAR(64) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Type VARCHAR(64) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE Links
DEFAULT CHARACTER SET utf8,
MODIFY Target VARBINARY(240) NULL DEFAULT NULL,
MODIFY Base VARBINARY(240) NULL DEFAULT NULL,
MODIFY Type VARBINARY(20) NOT NULL DEFAULT ‘’;
ALTER TABLE Links
MODIFY Target VARCHAR(240) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Base VARCHAR(240) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Type VARCHAR(20) CHARACTER SET ascii NOT NULL DEFAULT ‘’;
ALTER TABLE ObjectCustomFields
DEFAULT CHARACTER SET utf8;
ALTER TABLE ObjectCustomFieldValues
DEFAULT CHARACTER SET utf8,
MODIFY ContentType VARBINARY(80) NULL DEFAULT NULL,
MODIFY LargeContent LONGBLOB NULL DEFAULT NULL,
MODIFY ContentEncoding VARBINARY(80) NULL DEFAULT NULL,
MODIFY ObjectType VARBINARY(255) NOT NULL DEFAULT ‘’;
ALTER TABLE ObjectCustomFieldValues
MODIFY ContentType VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY ContentEncoding VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY ObjectType VARCHAR(255) CHARACTER SET ascii NOT NULL DEFAULT ‘’;
ALTER TABLE Principals
DEFAULT CHARACTER SET utf8,
MODIFY PrincipalType VARBINARY(16) NOT NULL DEFAULT ‘’;
ALTER TABLE Principals
MODIFY PrincipalType VARCHAR(16) CHARACTER SET ascii NOT NULL DEFAULT ‘’;
ALTER TABLE Queues
DEFAULT CHARACTER SET utf8,
MODIFY CorrespondAddress VARBINARY(120) NULL DEFAULT NULL,
MODIFY CommentAddress VARBINARY(120) NULL DEFAULT NULL;
ALTER TABLE Queues
MODIFY CorrespondAddress VARCHAR(120) CHARACTER SET ascii NULL
DEFAULT NULL,
MODIFY CommentAddress VARCHAR(120) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE ScripActions
DEFAULT CHARACTER SET utf8,
MODIFY Argument VARBINARY(255) NULL DEFAULT NULL,
MODIFY ExecModule VARBINARY(60) NULL DEFAULT NULL;
ALTER TABLE ScripActions
MODIFY ExecModule VARCHAR(60) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE ScripConditions
DEFAULT CHARACTER SET utf8,
MODIFY ApplicableTransTypes VARBINARY(60) NULL DEFAULT NULL,
MODIFY Argument VARBINARY(255) NULL DEFAULT NULL,
MODIFY ExecModule VARBINARY(60) NULL DEFAULT NULL;
ALTER TABLE ScripConditions
MODIFY ApplicableTransTypes VARCHAR(60) CHARACTER SET ascii NULL
DEFAULT NULL,
MODIFY ExecModule VARCHAR(60) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE Scrips
DEFAULT CHARACTER SET utf8,
MODIFY Stage VARBINARY(32) NULL DEFAULT NULL;
ALTER TABLE Scrips
MODIFY Stage VARCHAR(32) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE sessions
DEFAULT CHARACTER SET utf8,
MODIFY id VARBINARY(32) NOT NULL DEFAULT ‘’,
MODIFY a_session LONGBLOB NULL DEFAULT NULL;
ALTER TABLE Templates
DEFAULT CHARACTER SET utf8,
MODIFY Type VARBINARY(16) NULL DEFAULT NULL,
MODIFY Language VARBINARY(16) NULL DEFAULT NULL;
ALTER TABLE Templates
MODIFY Type VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Language VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Content TEXT CHARACTER SET utf8 NULL DEFAULT NULL;
ALTER TABLE Tickets
DEFAULT CHARACTER SET utf8,
MODIFY Status VARBINARY(10) NULL DEFAULT NULL,
MODIFY Type VARBINARY(16) NULL DEFAULT NULL;
ALTER TABLE Tickets
MODIFY Status VARCHAR(10) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Type VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL;
ALTER TABLE Transactions
DEFAULT CHARACTER SET utf8,
MODIFY Field VARBINARY(40) NULL DEFAULT NULL,
MODIFY Type VARBINARY(20) NULL DEFAULT NULL,
MODIFY ReferenceType VARBINARY(255) NULL DEFAULT NULL,
MODIFY ObjectType VARBINARY(64) NOT NULL DEFAULT ‘’;
ALTER TABLE Transactions
MODIFY Field VARCHAR(40) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Type VARCHAR(20) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY ReferenceType VARCHAR(255) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY ObjectType VARCHAR(64) CHARACTER SET ascii NOT NULL DEFAULT ‘’;
ALTER TABLE Users
DEFAULT CHARACTER SET utf8,
MODIFY WebEncoding VARBINARY(16) NULL DEFAULT NULL,
MODIFY PGPKey BLOB NULL DEFAULT NULL,
MODIFY Password VARBINARY(40) NULL DEFAULT NULL,
MODIFY Timezone VARBINARY(50) NULL DEFAULT NULL,
MODIFY EmailAddress VARBINARY(120) NULL DEFAULT NULL,
MODIFY EmailEncoding VARBINARY(16) NULL DEFAULT NULL,
MODIFY Lang VARBINARY(16) NULL DEFAULT NULL;
ALTER TABLE Users
MODIFY WebEncoding VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Comments TEXT CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Timezone VARCHAR(50) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY FreeformContactInfo TEXT 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 Signature TEXT CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Lang VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL;

jmoseley@corp.xanadoo.com wrote:

Ruslan_Zakirov · June 2, 2009, 9:34pm

I believe you used backup&restore of original production DB, didn’t
you? In this case you probably mangled data on load. Use
–default-character-set=binary on mysqldump and later on mysql load.
This only required for not upgraded DBs, after proper upgrade you
don’t need this option.On Wed, Jun 3, 2009 at 12:55 AM, Dominic Lepiane Dominic.Lepiane@ptgrey.com wrote:

Dear RT users,

I am trying to get our RT installation moved from RT 3.6.6 to 3.8.3.
I’ve tried following the upgrade steps in the UPGRADING.mysql and the
README files as diligently as I can. The DBMS is MySQL 5.0.45. I
updated all the perl modules… When I try the new version, everything
coming in works okay, but all the old binary attachments are broken, in
truth, anything other than regular ascii is mangled (e.g. diacritical
marks like é, ô, ç etc). If I try to open an image, it says “The image
http://rt/Ticket/Attachment/672/883/spots1.bmp"cannot be displayed,
because it contains errors.”.

Clearly I’ve either missed something or something has to be done to
convert this data. If I convert the Content column back to longtext
from longblob, then the binary data works again.

Please advise what can I do to get the data into the new table schema.

Thanks,

Dominic

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

James_Moseley · June 2, 2009, 9:41pm

Hmmm, usually the cause of binary attachments not displaying properly is
because folks didn’t apply the ‘mysql 4.1’ update…

James MoseleyDominic Lepiane Dominic.Lepiane@ptgrey.com wrote:

I did run steps 4-6 from the UPGRADING.mysql , yes. I’ve included the
generated SQL below. I should note the DBMS is on a separate host from
the RT (Apache / Postfix) server. Actually, now that I think about it,
when I generated the SQL, there was this message:

“Use of uninitialized value in join or string at
etc/upgrade/upgrade-mysql-schema.pl line 261.”

But as I recall, when I looked for more info on this only, other people
reported seeing this message but then did not have any problems. When I
ran the SQL (below), there was no error. It took a while, but did not
produce any errors.

Dominic_Lepiane · June 2, 2009, 11:14pm

Hi,

I’m not too clear what you mean. In creating the test system, I did
backup & restore the db. But I built a 3.6.6 system and then tested the
upgrade from that database. So I stopped Apache and Postfix, removed
the old /opt/rt3 folder, make install to get the new 3.8 files, and then
followed the upgrade instructions including the rt-setup-database and
upgrade-mysql-schema.pl scripts.

At this point, I’m simply trying to get a handful of binary attachments
working with a simple script to spit out the image entirely independent
of RT. So I’m doing this:

mysqldump --default-character-set=binary --compact -c -t -q -Q -w
“TransactionId < 2500 AND ContentType="image/bmp"” -u root rt3
Attachments > /data/attachments-dump4.sql

mysql --default-character-set=binary -u root rttest < attachments-dump4.sql

As you can sortof see, I’m trying to dump a couple attachments from the
3.6.6 rt3 database which contain image/bmp data and then restoring to
the test database. I have a couple PHP scripts which simply spit out
the ContentType as the Content-type header first and then echo the
Content second. And it looks to me like the data coming out of the
rttest database still is broken. However, I know that if I push new
tickets in to the upgraded RT 3.8.3, those images do come out good the
same as the old data does under 3.6.6.

I must still be confused or missing something though, what do I do now?

Thanks,

Dominic

Ruslan Zakirov wrote:

Tom_Lahti · June 2, 2009, 11:33pm

I’m not too clear what you mean. In creating the test system, I did
backup & restore the db. But I built a 3.6.6 system and then tested the
upgrade from that database. So I stopped Apache and Postfix, removed
the old /opt/rt3 folder, make install to get the new 3.8 files, and then
followed the upgrade instructions including the rt-setup-database and
upgrade-mysql-schema.pl scripts.
[snip]

Check the list archives. Odds are, you ran upgrade-mysql-schema.pl but did
not run the SQL script that it generates to actually upgrade the schema.

update-mysql-schema.pl does NOT upgrade the mysql schema. It generates a
script to do so. I asked Jesse about changing the name of the script but he
didn’t seem to think it was a good idea, and yet people keep thumping into this.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

Aaron_Guise · June 2, 2009, 11:47pm

Hi,

I too had a similar problem. I inherited our RT System from an earlier
administrator whom didn’t complete some step correctly earlier in the life
of the system. We were going from 3.6.5 to 3.8.0 and all worked fine.
Since then to enable some plugins I attempted to update to 3.8.2.

I did use --default-character-set=binary on mysqldump and completed all of
the upgrade steps as per UPGRADING.mysql but upon browsing the newly updated
RT System to my surprise all the binary attachments had been corrupted as
you are mentioning.

To get around this I created a couple of perl scripts.

Pulls all attachments out of the functioning database (Pre-Upgrade) and
dumps them to the filesystem
Inserts all attachments back into the newly updated schema.

This approach worked for me, I was then able to use 3.8.2 without any
trouble at all.

Regards,

*Aaron Guise
027 212 6638
aaron@guise.net.nzOn Wed, Jun 3, 2009 at 11:14 AM, Dominic Lepiane <Dominic.Lepiane@ptgrey.com wrote:

Hi,

I’m not too clear what you mean. In creating the test system, I did backup
& restore the db. But I built a 3.6.6 system and then tested the upgrade
from that database. So I stopped Apache and Postfix, removed the old
/opt/rt3 folder, make install to get the new 3.8 files, and then followed
the upgrade instructions including the rt-setup-database and
upgrade-mysql-schema.pl scripts.

At this point, I’m simply trying to get a handful of binary attachments
working with a simple script to spit out the image entirely independent of
RT. So I’m doing this:

mysqldump --default-character-set=binary --compact -c -t -q -Q -w
“TransactionId < 2500 AND ContentType="image/bmp"” -u root rt3
Attachments > /data/attachments-dump4.sql

mysql --default-character-set=binary -u root rttest < attachments-dump4.sql

As you can sortof see, I’m trying to dump a couple attachments from the
3.6.6 rt3 database which contain image/bmp data and then restoring to the
test database. I have a couple PHP scripts which simply spit out the
ContentType as the Content-type header first and then echo the Content
second. And it looks to me like the data coming out of the rttest database
still is broken. However, I know that if I push new tickets in to the
upgraded RT 3.8.3, those images do come out good the same as the old data
does under 3.6.6.

I must still be confused or missing something though, what do I do now?

Thanks,

Dominic

Ruslan Zakirov wrote:

I believe you used backup&restore of original production DB, didn’t
you? In this case you probably mangled data on load. Use
–default-character-set=binary on mysqldump and later on mysql load.
This only required for not upgraded DBs, after proper upgrade you
don’t need this option.

On Wed, Jun 3, 2009 at 12:55 AM, Dominic LepianeDominic.Lepiane@ptgrey.com Dominic.Lepiane@ptgrey.com wrote:

Dear RT users,

I am trying to get our RT installation moved from RT 3.6.6 to 3.8.3.
I’ve tried following the upgrade steps in the UPGRADING.mysql and the
README files as diligently as I can. The DBMS is MySQL 5.0.45. I
updated all the perl modules… When I try the new version, everything
coming in works okay, but all the old binary attachments are broken, in
truth, anything other than regular ascii is mangled (e.g. diacritical
marks like é, ô, ç etc). If I try to open an image, it says “The imagehttp://rt/Ticket/Attachment/672/883/spots1.bmp"cannot be displayed,
because it contains errors.”.

Clearly I’ve either missed something or something has to be done to
convert this data. If I convert the Content column back to longtext
from longblob, then the binary data works again.

Please advise what can I do to get the data into the new table schema.

Thanks,

Dominic

_______________________________________________The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Dominic_Lepiane · June 3, 2009, 7:40pm

Thanks, it looks like this might work. We basically have a script which
selects the data out of the 3.6 db and then update the corresponding row
in the 3.8 db and so far I’m getting better results.

Thanks,

Dominic

Aaron Guise wrote:

Dominic_Lepiane · June 8, 2009, 4:00pm

This worked fine for us in the end. Did a select on the Content from
the restored old database, and then just updated the Content in the
corresponding row in the new database. We also have similar problems
with other fields, like Headers, where there is any unicode / extended
acsii characters (diacritics or Asian characters) and these fields are
fixed in the same way.

Thank all for your help,

Dominic

Dominic Lepiane wrote: