BUG in rt2->rt3 conversion re: keywords

Hello

In my rt2 database I had only global keywords.

In my rt3 database they are now queue specific, and exist in every queue.
So when we move tickets from one queue to another they lose the keyword
settings (though the keyword selection lists are there because the
conversion program created them in every queue).

Is there any way to fix this now that I have those custom fields/keywords
in every queue instead of globally?

Thanks
Nick
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

Nicolae P. Costescu wrote:

In my rt2 database I had only global keywords.

In my rt3 database they are now queue specific, and exist in every
queue. So when we move tickets from one queue to another they lose the
keyword settings (though the keyword selection lists are there because
the conversion program created them in every queue).

Is there any way to fix this now that I have those custom
fields/keywords in every queue instead of globally?

I too would be interested in knowing if there is a way to keep keyword
values when moving tickets from queue to queue in rt3. Our workflow is
quite dependent on moving tickets, and we are using keywords heavily,
so it would be kind of a showstopper for us if there’s no way to do this
with rt3.

There’s at least one other RT user who also feels this way – his was
the only reply when I asked about this problem last November
(http://lists.fsck.com/pipermail/rt-users/2002-November/010685.html) –
but nobody provided any pointers on how to preserve the keyword values.

@alex
mailto:dupuy@sysd.com

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).

Now I’m left with a bunch of duplicate custom fields, and I’m finding it
hard to remove duplicates with mysql’s limitations (no views,no functions,
no subselects). So I’m writing a C program to do it. I thought about
importing the table into postgres and doing it there…

Remember - the issue is not that you can’t keep keywords when moving from
queue to queue.

The issue is that the rt2->rt3 upgrade script takes your global keywords
from rt2 and replicates them as queue specific keywords in every queue in rt3!

Ouch.

At 03:36 PM 4/14/2003 -0400, Alexander Dupuy wrote:

Nicolae P. Costescu wrote:

In my rt2 database I had only global keywords.
In my rt3 database they are now queue specific, and exist in every queue.
So when we move tickets from one queue to another they lose the keyword
settings (though the keyword selection lists are there because the
conversion program created them in every queue).
Is there any way to fix this now that I have those custom fields/keywords
in every queue instead of globally?

I too would be interested in knowing if there is a way to keep keyword
values when moving tickets from queue to queue in rt3. Our workflow
is quite dependent on moving tickets, and we are using keywords heavily,
so it would be kind of a showstopper for us if there’s no way to do this
with rt3.

There’s at least one other RT user who also feels this way – his was the
only reply when I asked about this problem last November
(http://lists.fsck.com/pipermail/rt-users/2002-November/010685.html) –
but nobody provided any pointers on how to preserve the keyword values.

@alex

mailto:dupuy@sysd.com

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

Does it also create the global custom fields?
The right place to fix this is in the import/export tools, not by making
you do nasty things after the fact.

-jOn Mon, Apr 14, 2003 at 04:07:27PM -0400, 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).

Now I’m left with a bunch of duplicate custom fields, and I’m finding it
hard to remove duplicates with mysql’s limitations (no views,no functions,
no subselects). So I’m writing a C program to do it. I thought about
importing the table into postgres and doing it there…

Remember - the issue is not that you can’t keep keywords when moving from
queue to queue.

The issue is that the rt2->rt3 upgrade script takes your global keywords
from rt2 and replicates them as queue specific keywords in every queue in
rt3!

Ouch.

At 03:36 PM 4/14/2003 -0400, Alexander Dupuy wrote:

Nicolae P. Costescu wrote:

In my rt2 database I had only global keywords.
In my rt3 database they are now queue specific, and exist in every queue.
So when we move tickets from one queue to another they lose the keyword
settings (though the keyword selection lists are there because the
conversion program created them in every queue).
Is there any way to fix this now that I have those custom fields/keywords
in every queue instead of globally?

I too would be interested in knowing if there is a way to keep keyword
values when moving tickets from queue to queue in rt3. Our workflow
is quite dependent on moving tickets, and we are using keywords heavily,
so it would be kind of a showstopper for us if there’s no way to do this
with rt3.

There’s at least one other RT user who also feels this way – his was the
only reply when I asked about this problem last November
(http://lists.fsck.com/pipermail/rt-users/2002-November/010685.html) –
but nobody provided any pointers on how to preserve the keyword values.

@alex

mailto:dupuy@sysd.com


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


rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

At 04:11 PM 4/14/2003 -0400, you wrote:

Does it also create the global custom fields?

Nope. I looked in CustomFields for rows with queue value 0 and there were
NONE. But I had a row for each queue for each custom field that used to be
global,

The right place to fix this is in the import/export tools, not by making
you do nasty things after the fact.

I certainly agree :slight_smile: But I only noticed the problem when users started
complaining that their keywords got lost when they moved tickets from queue
to queue. We’re committed now buddy - there’s no going back to rt2 :wink:

Right now I’m finishing my program to elminate duplicate entries in
TicketCustomFieldValues (created by users who put a ticket in 1 queue, put
in keywords, went to another queue, put the keywords in again - all before
I figured out the pblm and put in the global keywords).

When I mapped them all to the new global CustomFields I ended up with
duplicates in those cases.

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

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.

jaq@spacepants.org http://spacepants.org/jaq.gpg

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