[rt-users] Upgrade Issues

[Peter Barton]

I have recently upgraded from a long running 3.4.5 RT installation to
3.8.6.  The way I upgraded was by building a completely new RT server
running MySQL 5.0.51a where my original MySQL version was 4.0.24.  I
have read the UPGRADE and UPGRADE.mysql files numerous times and I have
done a lot of Google searches to make sure I was doing this right.  Here
is what I did.

Built my new server on Ubuntu 8.04 LTS server version.
Downloaded the source code for RT 3.8.6 and built it from scratch on the
server.
Once I had a vanilla version of RT running perfectly I went to my RT
3.4.5 machine and ran the command:

mysqldump --user=rtuser --password=xxxxxx --default-character-set=latin1
rtdb > rtdb.sql

This created a 2.5 GB file which I then transferred over to the new RT
3.8.6 server.
I tried to import this directly to the MySQL but it failed every time
due to the tables being stored in MyISAM.  I ran this script to change
everything to InnoDB.

sed -i 's/MyISAM/InnoDB/g' rtdb.sql

Once this completed I could successfully import the sql dump file. By
using these commands:

mysql --user=root --password=xxxxxx --execute="CREATE DATABASE rt3
CHARACTER SET utf8 COLLATE utf8_general_ci";

&

mysql --user=root --password=xxxxxx --default-character-set=utf8 rt3 <
rtdb.sql

Next I ran:

/opt/rt3/sbin/rt-setup-database --dba root --prompt-for-dba-password
--action upgrade

I upgraded from 3.4.5 to 3.7.87 like it says to do in the UPGRADE.mysql
file.  I then ran:

perl etc/upgrade/upgrade-mysql-schema.pl rt3 root xxxxxx > sql.queries

& 

mysql -u root -p rt3 < sql.queries

followed by:

/opt/rt3/sbin/rt-setup-database --dba root --prompt-for-dba-password
--action upgrade

Upgrading from 3.7.87 to 3.8.6 like it also says in the UPGRADING.mysql
file.

Once this was all complete I was able to access everything perfectly in
the new version of RT.  Everything ran fast and looked perfect.  

However, I have now discovered that any binary attachments to any of my
tickets are now unaccessable/corrupted.  Attachments of ASCII files are
still present though and accessible though.  Also, any new binary type
attachments are working fine.

Here is a Copy of "SHOW CREATE TABLE Attachments;"

| Attachments | CREATE TABLE `Attachments` (
  `id` int(11) NOT NULL auto_increment,
  `TransactionId` int(11) NOT NULL default '0',
  `Parent` int(11) NOT NULL default '0',
  `MessageId` varchar(160) character set ascii default NULL,
  `Subject` varchar(255) default NULL,
  `Filename` varchar(255) default NULL,
  `ContentType` varchar(80) character set ascii default NULL,
  `ContentEncoding` varchar(80) character set ascii default NULL,
  `Content` longblob,
  `Headers` longtext,
  `Creator` int(11) NOT NULL default '0',
  `Created` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `Attachments1` (`Parent`),
  KEY `Attachments2` (`TransactionId`),
  KEY `Attachments3` (`Parent`,`TransactionId`)
) ENGINE=InnoDB AUTO_INCREMENT=504231 DEFAULT CHARSET=utf8 |

Can anyone please give me some guidance on where to go from here to
recover those attachments?

Thanks in advance,

----------

Peter Barton