Help with mySQL query to prune RT3 database before upgrading to RT 4.2.5

Hi,

As I have posted recently, I am working on upgrading our current
production RT 3.8.1 to RT 4.2.5

I have a working copy of RT 3.8.1 on my test server including a copy of
our current production database which is over 18 gig of old data. I
have imported this database into RT 4 and when I run make
upgrade-database against the RT4 database, it runs a while and then dies
with a message that the script was killed.

What I would like to be able to do before our “real” production upgrade
is to wipeout everything from a given date back, including
objects/attachments or anything associated with tickets older than 3
years. Our current production RT3 database, in all it’s glory will be
archived for posterity. I was not the original admin of our RT system,
so I am still on the learning curve and not completely familiar with all
of the database tables and how they are linked.

Will RT:Shredder do this or do I need to use a custom mysql query to
catch everything?

Thanks,

Dave

http://requesttracker.wikia.com/wiki/Shredder
Later,
DarinOn Thu, Jun 19, 2014 at 12:56 PM, Lists lists@lolling.org wrote:

Hi,

As I have posted recently, I am working on upgrading our current production
RT 3.8.1 to RT 4.2.5

I have a working copy of RT 3.8.1 on my test server including a copy of our
current production database which is over 18 gig of old data. I have
imported this database into RT 4 and when I run make upgrade-database
against the RT4 database, it runs a while and then dies with a message that
the script was killed.

What I would like to be able to do before our “real” production upgrade is
to wipeout everything from a given date back, including objects/attachments
or anything associated with tickets older than 3 years. Our current
production RT3 database, in all it’s glory will be archived for posterity.
I was not the original admin of our RT system, so I am still on the learning
curve and not completely familiar with all of the database tables and how
they are linked.

Will RT:Shredder do this or do I need to use a custom mysql query to catch
everything?

Thanks,

Dave


RT Training - Boston, September 9-10
Training — Best Practical Solutions

I have a working copy of RT 3.8.1 on my test server including a copy of
our current production database which is over 18 gig of old data. I
have imported this database into RT 4 and when I run make
upgrade-database against the RT4 database, it runs a while and then dies
with a message that the script was killed.

It would be extremely helpful to see the output of the failed upgrade.

What I would like to be able to do before our “real” production upgrade
is to wipeout everything from a given date back, including
objects/attachments or anything associated with tickets older than 3
years. Our current production RT3 database, in all it’s glory will be
archived for posterity. I was not the original admin of our RT system,
so I am still on the learning curve and not completely familiar with all
of the database tables and how they are linked.

Will RT:Shredder do this or do I need to use a custom mysql query to
catch everything?

Shredder is the solution to removing data while preserving integrity,
but I would strongly advise instead determining and addressing what your
actual problem is.

  • Alex

[snip]

Please keep all replies on-list.

I will try the upgrade-database again. The script did not report any
additional information other than it was killed.

The precise error is important, as is the location in the upgrade
process where it was reported.

  • Alex

The make upgrade-database process runs for a short while and this is
what I get from the command line.

Proceed [y/N]:y
Processing 3.8.2
Now inserting data.
[25360] [Fri Jun 20 15:49:26 2014] [warning]: Going to add [OLD] prefix
to all templates in approvals queue. If you have never used approvals,
you can safely delete all the templates with the [OLD] prefix. Leave the
new Approval templates because you may eventually want to start using
approvals. (./etc/upgrade/3.8.2/content:6)
make: *** [upgrade-database] Killed

I am attempting to upgrade from 3.8.1 to 4.2.5.

Is there additional logging/debug I can turn on or log files elsewhere I
can check to see what is happening?

Thanks,
DaveOn 6/20/2014 10:16 AM, Alex Vandiver wrote:

On 06/19/2014 03:24 PM, Lists wrote:

[snip]
Please keep all replies on-list.

I will try the upgrade-database again. The script did not report any
additional information other than it was killed.
The precise error is important, as is the location in the upgrade
process where it was reported.

  • Alex

The make upgrade-database process runs for a short while and this is what I
get from the command line.

Proceed [y/N]:y
Processing 3.8.2
Now inserting data.
[25360] [Fri Jun 20 15:49:26 2014] [warning]: Going to add [OLD] prefix to
all templates in approvals queue. If you have never used approvals, you can
safely delete all the templates with the [OLD] prefix. Leave the new
Approval templates because you may eventually want to start using approvals.
(./etc/upgrade/3.8.2/content:6)
make: *** [upgrade-database] Killed

I am attempting to upgrade from 3.8.1 to 4.2.5.

Is there additional logging/debug I can turn on or log files elsewhere I can
check to see what is happening?

Connect to your database and run;
select count() from Attributes;
select count(
) from Attributes where Name = ‘DeferredRecipients’ and Content IS NULL;

-kevin

The make upgrade-database process runs for a short while and this is what I
get from the command line.

Proceed [y/N]:y
Processing 3.8.2
Now inserting data.
[25360] [Fri Jun 20 15:49:26 2014] [warning]: Going to add [OLD] prefix to
all templates in approvals queue. If you have never used approvals, you can
safely delete all the templates with the [OLD] prefix. Leave the new
Approval templates because you may eventually want to start using approvals.
(./etc/upgrade/3.8.2/content:6)
make: *** [upgrade-database] Killed

I am attempting to upgrade from 3.8.1 to 4.2.5.

Is there additional logging/debug I can turn on or log files elsewhere I can
check to see what is happening?
Connect to your database and run;
select count() from Attributes;
select count(
) from Attributes where Name = ‘DeferredRecipients’ and Content IS NULL;

-kevin

When I run this against the database I am trying to upgrade, I get:

mysql> select count() from Attributes;
| count(
) |
| 1831962 |
1 row in set (2.50 sec)

mysql> select count() from Attributes where Name = 'DeferredRecipients’
and Content IS NULL;
| count(
) |
| 1829169 |
1 row in set (10.32 sec)

What do these queries indicate?

Dave

When I run this against the database I am trying to upgrade, I get:

mysql> select count() from Attributes;
±---------+
| count(
) |
±---------+
| 1831962 |
±---------+
1 row in set (2.50 sec)

mysql> select count() from Attributes where Name = 'DeferredRecipients’
and Content IS NULL;
±---------+
| count(
) |
±---------+
| 1829169 |
±---------+
1 row in set (10.32 sec)

What do these queries indicate?

RT 3.8.1 contained a bug that caused it to create an excess of
Attributes – one for every transaction. Upgrading attempts to load
these into memory. You can safely remove them before upgrading:

DELETE FROM Attributes
 WHERE Name = 'DeferredRecipients'
   AND Content IS NULL;

Your upgrade should then be able to complete.

  • Alex

Thanks for the info Alex, I appreciate it and will give it a try.

DaveOn 6/23/2014 11:29 AM, Alex Vandiver wrote:

On 06/20/2014 07:16 PM, Lists wrote:

When I run this against the database I am trying to upgrade, I get:

mysql> select count() from Attributes;
±---------+
| count(
) |
±---------+
| 1831962 |
±---------+
1 row in set (2.50 sec)

mysql> select count() from Attributes where Name = 'DeferredRecipients’
and Content IS NULL;
±---------+
| count(
) |
±---------+
| 1829169 |
±---------+
1 row in set (10.32 sec)

What do these queries indicate?
RT 3.8.1 contained a bug that caused it to create an excess of
Attributes – one for every transaction. Upgrading attempts to load
these into memory. You can safely remove them before upgrading:

 DELETE FROM Attributes
  WHERE Name = 'DeferredRecipients'
    AND Content IS NULL;

Your upgrade should then be able to complete.

  • Alex