Re: [rt-users] Binary files broken since upgrade to RT 3.8
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: 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: 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. 1. Pulls all attachments out of the functioning database (Pre-Upgrade) and dumps them to the filesystem 2. 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 * ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Binary files broken since upgrade to RT 3.8
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: 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. 1. Pulls all attachments out of the functioning database (Pre-Upgrade) and dumps them to the filesystem 2. 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 * ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Binary files broken since upgrade to RT 3.8
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.bmpcannot 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 ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Binary files broken since upgrade to RT 3.8
Did you apply the schema updates as indicated in steps 4-6 of UPGRADING.mysql? James Moseley Dominic Lepiane dominic.lepi...@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.bmpcannot 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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Binary files broken since upgrade to RT 3.8
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
Re: [rt-users] Binary files broken since upgrade to RT 3.8
I believe you used backuprestore 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.lepi...@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.bmpcannot 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 ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Binary files broken since upgrade to RT 3.8
Hmmm, usually the cause of binary attachments not displaying properly is because folks didn't apply the 'mysql 4.1' update... James Moseley Dominic Lepiane dominic.lepi...@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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Binary files broken since upgrade to RT 3.8
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 backuprestore 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.lepi...@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.bmpcannot 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 ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Binary files broken since upgrade to RT 3.8
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/ -- ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Binary files broken since upgrade to RT 3.8
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. 1. Pulls all attachments out of the functioning database (Pre-Upgrade) and dumps them to the filesystem 2. 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 aa...@guise.net.nz * On Wed, Jun 3, 2009 at 11:14 AM, Dominic Lepiane dominic.lepi...@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 backuprestore 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.lepi...@ptgrey.com dominic.lepi...@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.bmpcannot 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 ___http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
