Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.
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/15 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/15 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-1: 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. Greg From: rt-users [rt-users-boun...@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 -Original Message- From: rt-users [mailto:rt-users-boun...@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/...
Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.
On 17-11-2015 20:04, Hummer, Greg wrote: > > > 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:* > / id=“DeleteLink-fsck.com-rt://*example.com*/ticket/101611-RefersTo” > name = > “DeleteLink-fsck.com-rt://*example.com*/ticket/101611-RefersTo-: > value=”1”>/ > > *On recently created- good links, I get this:* > / id=“DeleteLink-fsck.com-rt://*abc.com*/ticket/101611-RefersTo” name = > “DeleteLink-fsck.com-rt://*abc.com/*ticket/101611-RefersTo-: value=”1”>/ > > > When we pulled two lines out of the Link table, we get the following: > /15367 fsck.com-rt://*example.com*/ticket/15 > 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/15 > 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-1: 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
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 -Original Message- From: rt-users [mailto:rt-users-boun...@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/...
Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.
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-1: 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-1: 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-1: 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, Greg From: Aaron Guise [mailto:aa...@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]aa...@guise.net.nz<mailto:aa...@guise.net.nz> [http://www.guise.net.nz/images/signatures/itil_logo.gif] [http://www.guise.net.nz/images/signat
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* * 07 850 3231 027 704 5306 aa...@guise.net.nz <aa...@guise.net.nz> * <http://nz.linkedin.com/pub/aaron-guise/35/953/b86> On Wed, Oct 14, 2015 at 3:23 AM, Hummer, Greg <greg_hum...@sra.com> wrote: > 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-boun...@lists.bestpractical.com] on behalf of > Maik Nergert [maik.nerg...@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 > >
Re: [rt-users] Ticket Links missing post upgrade/conversion to Oracle 4.2.11.
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-boun...@lists.bestpractical.com] on behalf of Maik Nergert [maik.nerg...@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
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 smime.p7s Description: S/MIME Cryptographic Signature
