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