[rt-users] Upgrade from 3.6.3 to 3.8.4 - image attachments missing/corrupt

Wed Sep 16 16:57:22 EDT 2009

I'll have a look, I'm sure they are here somewhere.   Might take a day
though.

*Regards,
Aaron Guise
  07 838 7793
027 212 6638
aaron at guise.net.nz*

On Wed, Sep 16, 2009 at 9:20 PM, Justin Hayes <justin.hayes at orbisuk.com>wrote:

> 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?
> 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.
>
> Cheers,
>
> Justin
>
> On 15 Sep 2009, at 23:36, Aaron Guise wrote:
>
> 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.
>
> 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.
>
> 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.
>
> 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.
>
> *Regards,
> Aaron Guise
>   07 838 7793
> 027 212 6638
> aaron at guise.net.nz*
>
>
>
>
> On Wed, Sep 16, 2009 at 7:54 AM, Justin Hayes <justin.hayes at orbisuk.com>wrote:
>
>> Hi guys,
>>
>> I'm just testing an upgrade from 3.6.3 to 3.8.4. I ran the
>> rt-setup-database fine:
>>
>> /opt/rt_support.openbet.com/sbin/rt-setup-database -dba rt_support
>> --prompt-for-dba-password --action upgrade
>>
>>
>> Then created the schema upgrade script:
>>
>> perl /opt/rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl*blah* *blah* *password* > upgrade.sql
>> Use of uninitialized value in join or string at /opt/
>> rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl line 261.
>> .Tickets.status has type VARCHAR however mapping is missing.
>> Use of uninitialized value in join or string at /opt/
>> rt_support.openbet.com/etc/upgrade/upgrade-mysql-schema.pl line 261.
>> .Users.BlockImg has type CHAR however mapping is missing.
>> -- ** NOTICE: No database changes have been made. **
>> -- Please review the generated SQL, ensure you have a full backup of your
>> database
>> -- and apply it to your database using a command like:
>> -- mysql -u rt_support -p rt_support < queries.sql";
>>
>> cat upgrade.sql
>> ALTER DATABASE rt_support DEFAULT CHARACTER SET utf8;
>> ALTER TABLE ACL
>>    DEFAULT CHARACTER SET utf8,
>>    MODIFY RightName VARBINARY(25) NOT NULL,
>>    MODIFY PrincipalType VARBINARY(25) NOT NULL,
>>    MODIFY ObjectType VARBINARY(25) NOT NULL;
>> ALTER TABLE ACL
>>    MODIFY RightName VARCHAR(25) CHARACTER SET ascii NOT NULL,
>>    MODIFY PrincipalType VARCHAR(25) CHARACTER SET ascii NOT NULL,
>>    MODIFY ObjectType VARCHAR(25) CHARACTER SET ascii NOT NULL;
>> ALTER TABLE Attachments
>>    DEFAULT CHARACTER SET utf8,
>>    MODIFY Subject VARBINARY(255) NULL DEFAULT NULL,
>>    MODIFY ContentType VARBINARY(80) NULL DEFAULT NULL,
>>    MODIFY Filename VARBINARY(255) NULL DEFAULT NULL,
>>    MODIFY Headers LONGBLOB 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 Subject VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,
>>    MODIFY ContentType VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL,
>>    MODIFY Filename VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,
>>    MODIFY Headers LONGTEXT CHARACTER SET utf8 NULL DEFAULT NULL,
>>    MODIFY MessageId VARCHAR(160) CHARACTER SET ascii NULL DEFAULT NULL,
>>    MODIFY ContentEncoding VARCHAR(80) CHARACTER SET ascii NULL DEFAULT
>> NULL;
>>
>> .
>>
>> .
>>
>> .
>>
>>
>>
>> Now that looks a bit odd as there are 2 ALTERS per table and the second
>> seems to reverse some bits of the first?
>>
>> 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.
>>
>> 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?
>>
>> Any thoughts?
>>
>> Justin
>>
>> -------------------------------------------------
>> Justin Hayes
>> Orbis Support Manager
>> justin.hayes at orbisuk.com
>>
>>
>>
>>
>>
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>>
>>
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>>
>
>
>
> -------------------------------------------------
> Justin Hayes
> Orbis Support Manager
> justin.hayes at orbisuk.com
>
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090917/a6d927ac/attachment.htm>