Ticket Links missing post upgrade/conversion to Oracle 4.2.11

RT Users
1

Good Morning,

After upgrading and converting request tracker from MySQL 4.0.6 to Oracle 4.2.11, users noticed that the linked tickets were no longer visible.

There are bullet points for each linked ticket in the “Links” pane and the “Link ticket” tab, however the ticket text, number, and hyperlink are not visible.

Interestingly enough, if you go into the “Link ticket” tab and use chrome’s Inspect Element feature on the “check to delete box” for the blank linked ticket lines, you can see where the element lists the ticket that should be linked (example: input type=“checkbox” class=“checkbox” id=“DeleteLink –RefersTo-fsck.com-rt://example.come/ticket/110566” name=DeleteLink—RefersTo-fsck.com-rt://example.com/ticket/110556” value=”1”>).

Has anyone else experienced a similar issue and found a way to make the text/link re-appear? It is obvious that the data still exists.

Note: For our conversion, we used rt-validator, rt-serializer, and rt-importer. New links, created after the upgrade/conversion, do appear and function as expected.

Thank you for your time and support.

Greg

2

Hey Greg,

please check if your $Organization variable is set in RT_SiteConfig.pm
It must fit to the values of field “base” and “target” in table “links”

If your code snipped is correct then
Set( $Organization, ‘example.com’ );

best
Maik

smime.p7s (5.29 KB)

3

Good morning, Maik.

My technical team checked our production RT and found the $organization variable. It appeared to be correct, however, they were unsure what you meant when you said (It must fit to the values of field “base” and “target” in table “links”).

Can you provide more directions on what you meant? The more specific you can be, the better it would be for my non-Request Tracker DBA’s.

Some additional information: we have a stage server that is currently running the same version of request tracker (4.2.11 Oracle). It is a copy of our production database from a few months ago. It is showing the ticket links and has the same $Organization variable as the production account.

Thanks for the help!
GregFrom: rt-users [rt-users-bounces@lists.bestpractical.com] on behalf of Maik Nergert [maik.nergert@uni-hamburg.de]
Sent: Friday, October 09, 2015 2:12 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.

Hey Greg,

please check if your $Organization variable is set in RT_SiteConfig.pm
It must fit to the values of field “base” and “target” in table “links”

If your code snipped is correct then
Set( $Organization, ‘example.com’ );

best
Maik

4

Hi Greg,

This certainly sounds like a mismatch as Maik suggested. This happens
when I refresh my test/dev instance of RT from production also.

I then run the rt-validator over the database and this corrects all the
mismatched links. This is needed as I use the server hostname as the
Organization and this does differ between environments.

I simply import the database and then execute /opt/rt4/sbin/rt-validator
–check --resolve --force

Once that is completed all ticket links are working again.

Regards,

Aaron Guise

Good morning, Maik.

My technical team checked our production RT and found the $organization
variable. It appeared to be correct, however, they were unsure what you
meant when you said (It must fit to the values of field “base” and
“target” in table “links”).

Can you provide more directions on what you meant? The more specific you
can be, the better it would be for my non-Request Tracker DBA’s.

Some additional information: we have a stage server that is currently
running the same version of request tracker (4.2.11 Oracle). It is a copy
of our production database from a few months ago. It is showing the ticket
links and has the same $Organization variable as the production account.

Thanks for the help!

Greg

From: rt-users [rt-users-bounces@lists.bestpractical.com] on behalf of
Maik Nergert [maik.nergert@uni-hamburg.de]
Sent: Friday, October 09, 2015 2:12 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Ticket Links missing post upgrade/conversion to
Oracle 4.2.11.

Hey Greg,

please check if your $Organization variable is set in RT_SiteConfig.pm
It must fit to the values of field “base” and “target” in table “links”

If your code snipped is correct then
Set( $Organization, ‘example.com’ );

best
Maik

5

Aaron,

Thank you for your suggestion! I had the technical team run RT-Validator with links-only. This fixed the links in the “Refers to” category, but did not fix the tickets in the “Referred to by:” category. We next ran RT Validator with –check—resolve—force and it did not resolve the “Referred to by:” category.

Looking at the output from the RT-Validator run, we received a lot of errors.

23159 reports of “Record #123456 in Transactions. Value of NewValue column most probably is an incorrect link”
176 reports of “Record #1234567 in Transactions. Value of OldValue column most probably is an incorrect link”
20275 reports of “Record #12345 in Links. Value of Target column most probably is an incorrect link”
20323 reports of “Record #12345 in Links. Value of Base column most probably is an incorrect link”

A little bit of this:
Warning: Use of “localtime” without parentheses is ambiguous at /public/server/apps/rt-4.2.11/sbin/…/lib/RT/Interface/Email.pm line 526.
[7344] [Thu Oct 22 21:59:40 2015] [warning]: Nonexistant status refferred in action in default lifecycle at /public/server/apps/rt-4.2.11/sbin/…/lib/RT/Lifecycle.pm line 744. (/public/server/apps/rt-4.2.11/sbin/…/lib/RT/Lifecycle.pm:744)

A little bit of that:
[7344] [Thu Oct 22 22:00:15 2015] [warning]: DBD::Oracle::st execute failed: ORA-00001: unique constraint (RT3EMTS.LINKS1) violated (DBD ERROR: OCIStmtExecute) [for Statement “UPDATE Links SET Base = CONCAT(?, SUBSTR(Base, ?)) WHERE Base LIKE ?” with ParamValues: :p1=‘fsck.com-rt://abc.com’, :p2=26, :p3=“fsck.com-rt://example.com/%”] at ./rt-validator line 1316. (./rt-validator:1316)

[7344] [Thu Oct 22 22:00:15 2015] [critical]: couldn’t execute UPDATE Links SET Base = CONCAT(?, SUBSTR(Base, ?)) WHERE Base LIKE ?
Error: ORA-00001: unique constraint (RT3EMTS.LINKS1) violated (DBD ERROR: OCIStmtExecute) at ./rt-validator line 1316. (/public/server/apps/rt-4.2.11/sbin/…/lib/RT.pm:389)
couldn’t execute UPDATE Links SET Base = CONCAT(?, SUBSTR(Base, ?)) WHERE Base LIKE ?
Error: ORA-00001: unique constraint (RT3EMTS.LINKS1) violated (DBD ERROR: OCIStmtExecute) at ./rt-validator line 1316.
Warning: Use of “localtime” without parentheses is ambiguous at /public/server/apps/rt-4.2.11/sbin/…/lib/RT/Interface/Email.pm line 526.
[15012] [Fri Oct 23 02:23:07 2015] [warning]: Nonexistant status refferred in action in default lifecycle at /public/server/apps/rt-4.2.11/sbin/…/lib/RT/Lifecycle.pm line 744. (/public/server/apps/rt-4.2.11/sbin/…/lib/RT/Lifecycle.pm:744)

Record #3665 in Principals references a nonexistent record in Users
id => ‘3665’ => id

Record #3356 in GroupMembers references a nonexistent record in Principals
MemberId => ‘3665’ => id

Record #1356849 in CachedGroupMembers references a nonexistent record in GroupMembers
GroupId => ‘5’ => GroupId
MemberId => ‘3665’ => MemberId

Record #328577 in Transactions references a nonexistent record in Groups
ObjectId => ‘64505’ => id
Transaction #328577 → object RT::Group #64505
Record #3042 in ObjectCustomFieldValues references a nonexistent record in Articles
ObjectId => ‘8’ => id

The entire output is around 2580 pages.

Prior to the upgrade (with all the links working), I printed off the System Configuration webpage. I just double checked and our Organization was ‘abc.com’ . In our system configuration after the upgrade(with broken links), the organization is ‘abc.com’.

Would having the app down effect the RT-Validator? My technical team did not take Request Tracker down during the process and they were wondering if this could make a difference. Is there anything else we can do or check?

Thank you for your time and support.

Sincerely,
GregFrom: Aaron Guise [mailto:aaron@guise.net.nz]
Sent: Thursday, October 22, 2015 5:43 AM
To: Hummer, Greg
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.

Hi Greg,
This certainly sounds like a mismatch as Maik suggested. This happens when I refresh my test/dev instance of RT from production also.
I then run the rt-validator over the database and this corrects all the mismatched links. This is needed as I use the server hostname as the Organization and this does differ between environments.
I simply import the database and then execute /opt/rt4/sbin/rt-validator --check --resolve --force
Once that is completed all ticket links are working again.

Regards,

Aaron Guise

[http://www.guise.net.nz/images/signatures/ph.jpg] 07 850 3231
[http://www.guise.net.nz/images/signatures/mob.gif]027 704 5306
[http://www.guise.net.nz/images/signatures/email.gif]aaron@guise.net.nzmailto:aaron@guise.net.nz

[http://www.guise.net.nz/images/signatures/itil_logo.gif] [http://www.guise.net.nz/images/signatures/officiallogo-nd-25.jpg] [http://www.guise.net.nz/images/signatures/ubuntu.png] [http://www.linkedin.com/img/webpromo/btn_profile_greytxt_80x15.png] http://nz.linkedin.com/pub/aaron-guise/35/953/b86

6

What about new links after the upgrade? Are they displayed correctly?

Do 'select * from links where localbase=TICKETID’
and compare the base and target value of old TICKETIDs (before upgrade)
and new Tickets. Both has to be fsck.com-rt://abc.com/ticket/…

smime.p7s (5.29 KB)

7

New tickets links, created post upgrade/conversion, are displayed correctly. It is only the old ticket links are not displaying correctly.

Greg-----Original Message-----
From: rt-users [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Maik Nergert
Sent: Monday, October 26, 2015 4:05 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.

What about new links after the upgrade? Are they displayed correctly?

Do ‘select * from links where localbase=TICKETID’
and compare the base and target value of old TICKETIDs (before upgrade)
and new Tickets. Both has to be fsck.com-rt://abc.com/ticket/…

8

Good afternoon,

I can confirm it is most likely due to an organization. The organization in our System Configuration prior to the Conversion/Upgrade matches the setting after. This was confirmed though googles “Inspect Element” feature.

On the bad links I get the following:

On recently created- good links, I get this:

When we pulled two lines out of the Link table, we get the following:
15367 fsck.com-rt://example.com/ticket/100005 fsck.com-rt://abc.com/ticket/99902 MergedInto 99902 99902 184988 4/29/2014 4:10:48 PM 184988 4/29/2014 4:10:48 PM

In the sage account (which has all good links) this same ID shows up like this:
15367 fsck.com-rt://abc.com/ticket/100005 fsck.com-rt://abc.com/ticket/99902 MergedInto 99902 99902 184988 4/29/2014 4:10:48 PM 184988 4/29/2014 4:10:48 PM

So then we went about trying to fix it. We used RT-Validator with force, RT-Validator with links, and finally the MySQL below. Each time, with each of the previous three items, we got the following error:

ORA-00001: unique constraint (RT3EMTS.LINKS1) violated

Here is the MySQL we tried to use:
UPDATE links SET BASE = REPLACE(BASE,‘example.com’,‘abc.com’) WHERE BASE LIKE ‘%example.com%’;

Does anyone have any suggestions how to get around this Oracle error when trying to adjust the domain in the links?

Thanks for the help.

GregFrom: rt-users [rt-users-bounces@lists.bestpractical.com] on behalf of Hummer, Greg
Sent: Tuesday, October 27, 2015 7:47 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.

New tickets links, created post upgrade/conversion, are displayed correctly. It is only the old ticket links are not displaying correctly.

Greg

9

Good afternoon,

I can confirm it is most likely due to an organization. The
organization in our System Configuration prior to the
Conversion/Upgrade matches the setting after. This was confirmed
though googles “Inspect Element” feature.

On the bad links I get the following:
//

On recently created- good links, I get this:
//

When we pulled two lines out of the Link table, we get the following:
/15367 fsck.com-rt://example.com/ticket/100005
fsck.com-rt://abc.com/ticket/99902 MergedInto 99902 99902 184988
4/29/2014 4:10:48 PM 184988 4/29/2014 4:10:48 PM /

In the sage account (which has all good links) this same ID shows up
like this:
/15367 fsck.com-rt://abc.com/ticket/100005
fsck.com-rt://abc.com/ticket/99902 MergedInto 99902 99902 184988
4/29/2014 4:10:48 PM 184988 4/29/2014 4:10:48 PM/

So then we went about trying to fix it. We used RT-Validator with
force, RT-Validator with links, and finally the MySQL below. Each
time, with each of the previous three items, we got the following error:
/
ORA-00001: unique constraint (RT3EMTS.LINKS1) violated/

Here is the MySQL we tried to use:
UPDATE links SET BASE = REPLACE(BASE,‘example.com’,‘abc.com’) WHERE
BASE LIKE ‘%example.com%’;

Does anyone have any suggestions how to get around this Oracle error
when trying to adjust the domain in the links?

Your update (replacing example.com with abc.com) creates a row in the
LINKS table which violates the LINKS1 constraint. Look at the definition
of the LINKS to to find what is being violated. This means that you
already have a row with abc.com and trying to create a second one from
an old example.com.
Questions is which one to keep, or where/why is there already a abc.com row.

I have done I think exactly the same update and didn’t have any problems
BUT I didn’t move from MySQL to Oracle. I’m going to have a wild guess:
did you check the Oracle sequences after rt-importer? They should all be
@max(id)+1 before inserting new tickets/links etc.

Joop