<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Thanks a lot for the info and advise Aaron. Don't suppose you kept the scripts you used to dump the attachments and load them back in did you?<div><br></div><div>I'm going to talk to my sysadmins and see if they are using that default-character-set option in the backup dump. If they aren't I'll get them to do me a new dump with that option on and see if it works that time.</div><div><br></div><div>Cheers,</div><div><br></div><div>Justin</div><div><br><div><div>On 15 Sep 2009, at 23:36, Aaron Guise wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite">I had similar problems when moving upto 3.8.1. The previous sysadmin
responsible for RT had failed to upgrade the DB properly when going
from 3.6.5 to 3.8.0 some time back. All our attachments went screwy
too when I tried to upgrade to 3.8.1. <br>
<br>
In the end what I did is dump the database before upgrade in case I
need to go back. Dumped all attachment records to disk via perl, ran
the RT upgrade scripts and then updated the attachments table from the
ones I had dumped out earlier. <br>
<br>
This then made all the attachments become working again. RT itself
also seemed to get a performance boost !YAY!. And the two ALTER
entries in the upgrade script I found as well, Prior to running the
upgrade I removed the ones that weren't binary columns e.g. VARBINARY
so removing the lines which mentioned something like LONGBLOB. <br><br>When you use mysqldump to backup the database you just need to make sure to place this "--opt --default-character-set=binary" in the commandline arguments. That will mean it exports in binary mode to avoid corruption.<br>
<br><div dir="ltr"><div><blockquote style="margin: 1.5em 0pt;"><p><b>Regards,<br>Aaron Guise<br>
<img src="http://www.guise.net.nz/images/signatures/ph.jpg" border="0" height="22" width="25">
07 838 7793<br>
<img src="http://www.guise.net.nz/images/signatures/mob.gif" border="0" height="24" width="30">027
212 6638<br>
<img src="http://www.guise.net.nz/images/signatures/email.gif" border="0" height="26" width="29"><a href="mailto:aaron@guise.net.nz" target="_blank">aaron@guise.net.nz</a></b><br>
<img src="http://www.guise.net.nz/images/signatures/itil_logo.gif" height="30"> <img style="width: 25px; height: 41px;" src="http://www.guise.net.nz/images/signatures/officiallogo-nd-25.jpg"> <img src="http://www.guise.net.nz/images/signatures/ubuntu.png" height="30"></p>
</blockquote></div></div><br><br><div class="gmail_quote">On Wed, Sep 16, 2009 at 7:54 AM, Justin Hayes <span dir="ltr"><<a href="mailto:justin.hayes@orbisuk.com">justin.hayes@orbisuk.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="word-wrap: break-word;"><div>Hi guys,</div><div><br></div><div>I'm just testing an upgrade from 3.6.3 to 3.8.4. I ran the rt-setup-database fine:</div><div><br></div><blockquote style="border: medium none ; margin: 0pt 0pt 0pt 40px; padding: 0px;">
/opt/<a href="http://rt_support.openbet.com/sbin/rt-setup-database" target="_blank">rt_support.openbet.com/sbin/rt-setup-database</a> -dba rt_support --prompt-for-dba-password --action upgrade</blockquote><div><div><br></div>
<div>Then created the schema upgrade script:</div></div><div><br></div><blockquote style="border: medium none ; margin: 0pt 0pt 0pt 40px; padding: 0px;">perl /opt/<a href="http://rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl" target="_blank">rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl</a> *blah* *blah* *password* > upgrade.sql<br>
Use of uninitialized value in join or string at /opt/<a href="http://rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl" target="_blank">rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl</a> line 261.<br>
.Tickets.status has type VARCHAR however mapping is missing.<br>Use of uninitialized value in join or string at /opt/<a href="http://rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl" target="_blank">rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl</a> line 261.<br>
.Users.BlockImg has type CHAR however mapping is missing.<br>-- ** NOTICE: No database changes have been made. **<br>-- Please review the generated SQL, ensure you have a full backup of your database <br>-- and apply it to your database using a command like:<br>
-- mysql -u rt_support -p rt_support < queries.sql";<br><br>cat upgrade.sql <br>ALTER DATABASE rt_support DEFAULT CHARACTER SET utf8;<br>ALTER TABLE ACL<br> DEFAULT CHARACTER SET utf8,<br> MODIFY RightName VARBINARY(25) NOT NULL,<br>
MODIFY PrincipalType VARBINARY(25) NOT NULL,<br> MODIFY ObjectType VARBINARY(25) NOT NULL;<br>ALTER TABLE ACL<br> MODIFY RightName VARCHAR(25) CHARACTER SET ascii NOT NULL,<br> MODIFY PrincipalType VARCHAR(25) CHARACTER SET ascii NOT NULL,<br>
MODIFY ObjectType VARCHAR(25) CHARACTER SET ascii NOT NULL;<br>ALTER TABLE Attachments<br> DEFAULT CHARACTER SET utf8,<br> MODIFY Subject VARBINARY(255) NULL DEFAULT NULL,<br> MODIFY ContentType VARBINARY(80) NULL DEFAULT NULL,<br>
MODIFY Filename VARBINARY(255) NULL DEFAULT NULL,<br> MODIFY Headers LONGBLOB NULL DEFAULT NULL,<br> MODIFY MessageId VARBINARY(160) NULL DEFAULT NULL,<br> MODIFY Content LONGBLOB NULL DEFAULT NULL,<br> MODIFY ContentEncoding VARBINARY(80) NULL DEFAULT NULL;<br>
ALTER TABLE Attachments<br> MODIFY Subject VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,<br> MODIFY ContentType VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL,<br> MODIFY Filename VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,<br>
MODIFY Headers LONGTEXT CHARACTER SET utf8 NULL DEFAULT NULL,<br> MODIFY MessageId VARCHAR(160) CHARACTER SET ascii NULL DEFAULT NULL,<br> MODIFY ContentEncoding VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL;</blockquote>
<blockquote style="border: medium none ; margin: 0pt 0pt 0pt 40px; padding: 0px;">.</blockquote><blockquote style="border: medium none ; margin: 0pt 0pt 0pt 40px; padding: 0px;">.</blockquote><blockquote style="border: medium none ; margin: 0pt 0pt 0pt 40px; padding: 0px;">
.</blockquote><blockquote style="border: medium none ; margin: 0pt 0pt 0pt 40px; padding: 0px;"><br></blockquote><div><br></div><div>
<span style="font-size: 12px;"><div style="word-wrap: break-word;"><div>Now that looks a bit odd as there are 2 ALTERS per table and the second seems to reverse some bits of the first?</div><div><br></div><div>Anyway I ran that into my DB. Now when I go into a ticket with an image attached and click on it no image is returned, which is a bit worrying.</div>
<div><br></div><div>I'm wondering if it was a problem with the upgrade, or the original db dump provided by my IT systems guys. Perhaps the DB wasn't dumped using binary character set? How could I check that and how should the IT guys have dumped the DB to make sure it was in binary?</div>
<div><br></div><div>Any thoughts?</div><div><br></div><div>Justin</div><div><br></div><div>-------------------------------------------------</div><div>Justin Hayes</div><div>Orbis Support Manager</div><div><a href="mailto:justin.hayes@orbisuk.com" target="_blank">justin.hayes@orbisuk.com</a></div>
<div><br></div></div></span><br><br>
</div>
<br></div><br>_______________________________________________<br>
<a href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users" target="_blank">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</a><br>
<br>
Community help: <a href="http://wiki.bestpractical.com/" target="_blank">http://wiki.bestpractical.com</a><br>
Commercial support: <a href="mailto:sales@bestpractical.com">sales@bestpractical.com</a><br>
<br>
<br>
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<br>
Buy a copy at <a href="http://rtbook.bestpractical.com/" target="_blank">http://rtbook.bestpractical.com</a><br></blockquote></div><br>
</blockquote></div><br><div>
<span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><br class="Apple-interchange-newline">-------------------------------------------------</div><div>Justin Hayes</div><div>Orbis Support Manager</div><div><a href="mailto:justin.hayes@orbisuk.com">justin.hayes@orbisuk.com</a></div><div><br></div></div></span><br class="Apple-interchange-newline"></span><br class="Apple-interchange-newline">
</div>
<br></div></body></html>