Upgrade from 3.6.3 to 3.8.4 - image attachments missing/corrupt

Justin_Hayes · September 15, 2009, 7:54pm

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@orbisuk.com

Aaron_Guise · September 15, 2009, 10:36pm

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@guise.net.nzOn Wed, Sep 16, 2009 at 7:54 AM, Justin Hayes justin.hayes@orbisuk.comwrote:

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.plblah 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@orbisuk.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Ruslan_Zakirov · September 16, 2009, 2:31am

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:

[snip]

Now that looks a bit odd as there are 2 ALTERS per table and the second
seems to reverse some bits of the first?

It’s not reverse, but metadata change about tables without changing
the data. It’s documented way to change character sets in mysql
without changing data.

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

As you’re pretty sure that you applied the schema change then it’s
problem with backup and restore.

character set? How could I check that and how should the IT guys have dumped
the DB to make sure it was in binary?

mysqldump --opt --default-character-set=binary rt3olddb > backup.mysql.dump
mysql --default-character-set=binary rt38newdb < backup.mysql.dump

Any thoughts?
Justin

Justin Hayes
Orbis Support Manager
justin.hayes@orbisuk.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

Justin_Hayes · September 16, 2009, 9:20am

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,

JustinOn 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@guise.net.nz

On Wed, Sep 16, 2009 at 7:54 AM, Justin Hayes <justin.hayes@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@orbisuk.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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@orbisuk.com

Aaron_Guise · September 16, 2009, 8:57pm

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@guise.net.nzOn Wed, Sep 16, 2009 at 9:20 PM, Justin Hayes justin.hayes@orbisuk.comwrote:

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@guise.net.nz

On Wed, Sep 16, 2009 at 7:54 AM, Justin Hayes justin.hayes@orbisuk.comwrote:

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.plblah 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@orbisuk.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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@orbisuk.com

Justin_Hayes · September 16, 2009, 10:21pm

Thanks Aaron. I’ve always wondered why file attachments are stored in
the db at all. I’d have thought those would have been better placed
out in the filesystem.

Cheers,

JustinOn 16 Sep 2009, at 21:59, Aaron Guise aaron@guise.net.nz wrote:

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@guise.net.nz

On Wed, Sep 16, 2009 at 9:20 PM, Justin Hayes <justin.hayes@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@guise.net.nz

On Wed, Sep 16, 2009 at 7:54 AM, Justin Hayes <justin.hayes@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@orbisuk.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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@orbisuk.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Tom_Lahti · September 16, 2009, 11:00pm

Justin Hayes wrote:

Thanks Aaron. I’ve always wondered why file attachments are stored in
the db at all. I’d have thought those would have been better placed out
in the filesystem.

Egads! What if the storage database is not local to the web server? How will
you perform comprehensive backups? What if your RT has a million attachments,
or more? Not to mention the performance hit of using a filesystem as a
database, especially with high concurrency at the HTTP level.

I have a custom database application designed specifically to store PDFs in
the database. It has 30 million documents in it, the database storage is over
4TB. The web-based front-end for it is efficient enough to saturate a
100MBit/sec Internet connection with a single Core-2 duo web server. When I
tested this against our old filesystem version of the application, it
outperformed the filesystem by more than 100%. Backup is done by dumping the
database in chunks in a rotating schedule. Scalability can be accomplished
with simple replication to additional read-only SQL servers and using a SQL
relay to dispatch SQL commands in a load-balancing fashion.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

Aaron_Guise · September 16, 2009, 11:33pm

I fully agree Tom, SQL Servers totally own the filesystem equivalent in
this regard. Our attachments table is huge and it would be rather difficult
to keep a track of them all and ensure every last one is backed up without
the MySQL storage system

Regards,
Aaron Guise
07 838 7793
027 212 6638
aaron@guise.net.nzOn Thu, Sep 17, 2009 at 11:00 AM, Tom Lahti toml@bitstatement.net wrote:

Justin Hayes wrote:

Thanks Aaron. I’ve always wondered why file attachments are stored in
the db at all. I’d have thought those would have been better placed out
in the filesystem.

Egads! What if the storage database is not local to the web server? How
will
you perform comprehensive backups? What if your RT has a million
attachments,
or more? Not to mention the performance hit of using a filesystem as a
database, especially with high concurrency at the HTTP level.

I have a custom database application designed specifically to store PDFs in
the database. It has 30 million documents in it, the database storage is
over
4TB. The web-based front-end for it is efficient enough to saturate a
100MBit/sec Internet connection with a single Core-2 duo web server. When
I
tested this against our old filesystem version of the application, it
outperformed the filesystem by more than 100%. Backup is done by dumping
the
database in chunks in a rotating schedule. Scalability can be accomplished
with simple replication to additional read-only SQL servers and using a SQL
relay to dispatch SQL commands in a load-balancing fashion.

–
– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Aaron_Guise · October 15, 2009, 9:09pm

Hi Justin,

Sorry it took so long. I was on leave and then couldn’t test that my
scripts still worked. I have found them now and tested it all out. They
are attached here. If you have any trouble please let me know.

Regards,
Aaron Guise
07 838 7793
027 212 6638
aaron@guise.net.nzOn Thu, Sep 17, 2009 at 12:33 PM, Aaron Guise aaron@guise.net.nz wrote:

I fully agree Tom, SQL Servers totally own the filesystem equivalent in
this regard. Our attachments table is huge and it would be rather difficult
to keep a track of them all and ensure every last one is backed up without
the MySQL storage system

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

On Thu, Sep 17, 2009 at 11:00 AM, Tom Lahti toml@bitstatement.net wrote:

Justin Hayes wrote:

Thanks Aaron. I’ve always wondered why file attachments are stored in
the db at all. I’d have thought those would have been better placed out
in the filesystem.

Egads! What if the storage database is not local to the web server? How
will
you perform comprehensive backups? What if your RT has a million
attachments,
or more? Not to mention the performance hit of using a filesystem as a
database, especially with high concurrency at the HTTP level.

I have a custom database application designed specifically to store PDFs
in
the database. It has 30 million documents in it, the database storage is
over
4TB. The web-based front-end for it is efficient enough to saturate a
100MBit/sec Internet connection with a single Core-2 duo web server. When
I
tested this against our old filesystem version of the application, it
outperformed the filesystem by more than 100%. Backup is done by dumping
the
database in chunks in a rotating schedule. Scalability can be
accomplished
with simple replication to additional read-only SQL servers and using a
SQL
relay to dispatch SQL commands in a load-balancing fashion.

–
– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

GrabAndInsert.zip (1.93 KB)

Justin_Hayes · October 19, 2009, 7:54am

I didn’t mean the whole attachments table. Putting that in the
filesystem would be crazy. I was more talking about files you manually
attach (word docs, images etc). These tend to me more throwaway for me
than the text of the replies/comments themselves, and we don’t have
anywhere near as many… They could just live in the filesystem in
neat subdirectories and be retrieved when someone actually clicks on
one to look at. Backup would be easy - just rsync/tar/other option of
your choice.

But as long as mysql can handle the large DB sizes then I guess it’s
fine where it is

JustinOn 17 Sep 2009, at 00:33, Aaron Guise wrote:

I fully agree Tom, SQL Servers totally own the filesystem
equivalent in this regard. Our attachments table is huge and it
would be rather difficult to keep a track of them all and ensure
every last one is backed up without the MySQL storage system

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

On Thu, Sep 17, 2009 at 11:00 AM, Tom Lahti toml@bitstatement.net wrote:
Justin Hayes wrote:

Thanks Aaron. I’ve always wondered why file attachments are stored
in
the db at all. I’d have thought those would have been better
placed out
in the filesystem.

Egads! What if the storage database is not local to the web server?
How will
you perform comprehensive backups? What if your RT has a million
attachments,
or more? Not to mention the performance hit of using a filesystem
as a
database, especially with high concurrency at the HTTP level.

I have a custom database application designed specifically to store
PDFs in
the database. It has 30 million documents in it, the database
storage is over
4TB. The web-based front-end for it is efficient enough to saturate a
100MBit/sec Internet connection with a single Core-2 duo web
server. When I
tested this against our old filesystem version of the application, it
outperformed the filesystem by more than 100%. Backup is done by
dumping the
database in chunks in a rotating schedule. Scalability can be
accomplished
with simple replication to additional read-only SQL servers and
using a SQL
relay to dispatch SQL commands in a load-balancing fashion.

–
– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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@orbisuk.com

Justin_Hayes · October 19, 2009, 7:55am

Thanks Aaron for taking the time to dig them out. I’ll take a look at
them (though fingers crossed adding the binary format options to the
DB dump seems to be working so far).

JustinOn 15 Oct 2009, at 22:09, Aaron Guise wrote:

Hi Justin,

Sorry it took so long. I was on leave and then couldn’t test that
my scripts still worked. I have found them now and tested it all
out. They are attached here. If you have any trouble please let me
know.

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

On Thu, Sep 17, 2009 at 12:33 PM, Aaron Guise aaron@guise.net.nz wrote:
I fully agree Tom, SQL Servers totally own the filesystem
equivalent in this regard. Our attachments table is huge and it
would be rather difficult to keep a track of them all and ensure
every last one is backed up without the MySQL storage system

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

On Thu, Sep 17, 2009 at 11:00 AM, Tom Lahti toml@bitstatement.net wrote:
Justin Hayes wrote:

Thanks Aaron. I’ve always wondered why file attachments are stored
in
the db at all. I’d have thought those would have been better
placed out
in the filesystem.

Egads! What if the storage database is not local to the web server?
How will
you perform comprehensive backups? What if your RT has a million
attachments,
or more? Not to mention the performance hit of using a filesystem
as a
database, especially with high concurrency at the HTTP level.

I have a custom database application designed specifically to store
PDFs in
the database. It has 30 million documents in it, the database
storage is over
4TB. The web-based front-end for it is efficient enough to saturate a
100MBit/sec Internet connection with a single Core-2 duo web
server. When I
tested this against our old filesystem version of the application, it
outperformed the filesystem by more than 100%. Backup is done by
dumping the
database in chunks in a rotating schedule. Scalability can be
accomplished
with simple replication to additional read-only SQL servers and
using a SQL
relay to dispatch SQL commands in a load-balancing fashion.

–
– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

<GrabAndInsert.zip>

Justin Hayes
Orbis Support Manager
justin.hayes@orbisuk.com