Jamie
I’m sorry, I don’t have a list of statements for you. But the basic outline
I gave is correct I think…
You can do most of it from inside of Rt3.
First, check and make sure your global keywords did not get created in Rt3.
You can look in RT3 or you can look at the CustomFields table, and look for
rows with queue = 0.
If you have the same problem I did, what you’ll see is one set of rows of
CustomFields for each queue, instead of just one global set.
Now add the custom field values (to CustomFieldValues) for your new global
custom fields. You can do it in RT3 (be careful to use the exact same
spelling as your existing values) or you can just do it with sql statements
(unfortunately since mysql has no views, no functions, no subselects, you
have to make some temp tables). If you’re using postgres it’s much easier,
you just do it in one statement with a subselect.
Now that you have that set up, here’s the part you can’t do in Rt3. You
must do it in the database.
You have to remap all TicketCustomFieldValues from the queue specific
CustomFields to the global CustomFields. This is a many to one mapping.
Say you have a global custom field which now has id Kg in the CustomFields
table. Say the upgrade script created identical custom fields based on
this one, with id fields K1…Kn, one per queue. You want to upate any
TicketCustomFieldValue rows with CustomField in (k1…Kn) to Kg. Repeat
this for each global keyword with its corresponding queue specific clones.
Then you’ll want to check and make sure you haven’t missed any by looking
at TicketCustomFieldValues that have CustomFields that do not map to your
global CustomFields (again harder in mysql thanks to no subselects).
Then disable all the customfields that were queue specific clones of the
global ones. Can do this in Rt3, or quicker in sql.
If users have used your system while you had those queue spec. clones,
tickets may now have more than one value even for single select keywords -
remove duplicates (I wrote a program, was easier to do that than to
struggle w/mysql limits - again in postgres you could do it with a subselect).
At this point I also deleted TicketCustomFieldValues rows that had Content
fields that no longer existed in the CustomFieldValues table.
Sorry I can’t be more specific, I made the queries up as I went along and
didn’t save them. I also didn’t find a 3.0 schema anywhere in the docs
(there was one for 2.0).
Someone could write a fairly simple program based on the outline above to
“fix up” botched 2->3 upgrades.
Good luck,
Nick
Date: Tue, 15 Apr 2003 09:23:12 +1000
From: Jamie Wilkinson jaq@spacepants.org
To: rt-users@lists.fsck.com
Subject: Re: [rt-users] Re: BUG in rt2->rt3 conversion re: keywords
This one time, at band camp, Nicolae P. Costescu wrote:
I was able to straighten the keywords out with a lot of SQL gymnastics.
Basically I had to create new CustomFields, then new CustomFieldValues,
then map all the customfields from the TicketCustomFieldValues to the new
CustomFields. Then I disabled the queue specific custom fields for every
queue except 0 (global), except for the queues that really were supposed to
have queue specific custom fields (whew).
Can you post the specific sql statements you used? I’ve also been hit by
this, and it’s the last thing I need to fix for our RT3 migration.
Nicolae P. Costescu, Ph.D. / Senior Developer
Stronghold Technologies
46040 Center Oak Plaza, Suite 160 / Sterling, Va 20166
Tel: 571-434-1472 / Fax: 571-434-1478
But I only noticed the problem when users started