Setting owner with _Set breaks queries (may be a bug)

Scotto_Alberto · October 4, 2012, 11:05am

Hi all,
I think I found a bug.
I have a scrip called “On closure Auto set owner”, which sets the owner while closing the ticket in case nobody has never taken it, so that I don’t get resolved tickets assigned to “Nobody in particular”.
Everything seemed ok until today a colleague of mine noticed something weird with a chart that acts like a hall of fame, showing the top Owners. This chart is very simple: it retrieves all the tickets and it is grouped by Owner.RealName.
The weird thing is not the chart itself, but the fact that the following two queries should return the same tickets but they don’t:

     Owner.RealName = 'Nobody in particular' returns 706 tickets

     Owner = 'Nobody in particular' returns 369 tickets

The results of the former are not correct: it includes also the tickets for which the owner was set by my scrip, though the column “Owner” is correct (filled with “John Doe”, not “Nobody in particular”).

Here are the relevant lines of the code of my scrip:

get actor ID

my $Actor = $self->TransactionObj->Creator;
my $tkt = $self->TicketObj;

try to change owner

my ($status, $msg) = $tkt->_Set(Field => ‘Owner’, Value => $Actor, RecordTransaction => 0);

I solved the problem using SetOwner instead of _Set, like this:

my ($status, $msg) = $tkt->SetOwner($Actor);

With the new code, the tickets whose owner is set by my scrip don’t get caught by the query Owner.RealName = ‘Nobody in particular’.

Looks like _Set doesn’t update correctly some field in some table in the DB. But I have no idea of what field and what table! I had a quick look at the DB, and it seems that the relation ticket-owner is based on an user ID, not a string (the real name).

Finally, a weird thing with the chart: on the right side, it displays all the owners and the number of tickets associated. For “Nobody in particular”, the number is 369 (correct); but if I click on this number it generates the query “Owner.RealName = ‘Nobody in particular’” saying “Found 706 tickets”.

The questions are:
Should I file a bug?
How can I “convert” the tickets that were processed with the old code?

Hope everything is clear

Thank you very much!

Alberto Scotto

Blue Reply
Via Cardinal Massaia, 83
10147 - Torino - ITALY
phone: +39 011 29100
al.scotto@reply.it
www.reply.it

[Blue Reply]

The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

Joe_Harris · October 4, 2012, 11:36am

This is a shot in the dark and I may be way off, but is there a chance your scrip to set the owner runs in a transaction that happens after the transaction that resolves the ticket?

And the chart looks for the resolved transaction to get owner at the time of resolution?

Just a thought.On Oct 4, 2012, at 7:05 AM, Scotto Alberto al.scotto@reply.it wrote:

Hi all,
I think I found a bug.
I have a scrip called “On closure Auto set owner”, which sets the owner while closing the ticket in case nobody has never taken it, so that I don’t get resolved tickets assigned to “Nobody in particular”.
Everything seemed ok until today a colleague of mine noticed something weird with a chart that acts like a hall of fame, showing the top Owners. This chart is very simple: it retrieves all the tickets and it is grouped by Owner.RealName.
The weird thing is not the chart itself, but the fact that the following two queries should return the same tickets but they don’t:
     Owner.RealName = 'Nobody in particular' returns 706 tickets
     Owner = 'Nobody in particular' returns 369 tickets
The results of the former are not correct: it includes also the tickets for which the owner was set by my scrip, though the column “Owner” is correct (filled with “John Doe”, not “Nobody in particular”).

Here are the relevant lines of the code of my scrip:

get actor ID

my $Actor = $self->TransactionObj->Creator;
my $tkt = $self->TicketObj;

try to change owner

my ($status, $msg) = $tkt->_Set(Field => ‘Owner’, Value => $Actor, RecordTransaction => 0);

I solved the problem using SetOwner instead of _Set, like this:

my ($status, $msg) = $tkt->SetOwner($Actor);

With the new code, the tickets whose owner is set by my scrip don’t get caught by the query Owner.RealName = ‘Nobody in particular’.

Looks like _Set doesn’t update correctly some field in some table in the DB. But I have no idea of what field and what table! I had a quick look at the DB, and it seems that the relation ticket-owner is based on an user ID, not a string (the real name).

Finally, a weird thing with the chart: on the right side, it displays all the owners and the number of tickets associated. For “Nobody in particular”, the number is 369 (correct); but if I click on this number it generates the query “Owner.RealName = ‘Nobody in particular’” saying “Found 706 tickets”.

The questions are:
Should I file a bug?
How can I “convert” the tickets that were processed with the old code?

Hope everything is clear

Thank you very much!

Alberto Scotto

Blue Reply
Via Cardinal Massaia, 83
10147 - Torino - ITALY
phone: +39 011 29100
al.scotto@reply.it
www.reply.it

<blue.png>

–
The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

Final RT training for 2012 in Atlanta, GA - October 23 & 24
http://bestpractical.com/training

We’re hiring! Careers — Best Practical Solutions

Scotto_Alberto · October 4, 2012, 1:09pm

Joe,
Thank for you your quick answer!

I’ll take ticket #2985 as an example, which is one of those affected by the issue.

The scrip correctly happens in the transaction #38624, “Status changed from ‘new’ to ‘resolved’”, as the (custom) scrip condition is (taken from the DB):

| 17 | On closure | If the status is set to inactive | StatusChange | old: initial, active; new: inactive | Status | 1 | 2012-05-30 15:54:19 | 1 | 2012-05-30 15:54:19 |

Here is some output from rt.log:

[Mon Sep 10 13:24:00 2012] [debug]: About to think about scrips for transaction #38624 (/opt/rt4/sbin/…/lib/RT/Transaction.pm:173)
[Mon Sep 10 13:24:00 2012] [debug]: About to prepare scrips for transaction #38624 (/opt/rt4/sbin/…/lib/RT/Transaction.pm:177)
[Mon Sep 10 13:24:00 2012] [debug]: Found 15 scrips for TransactionCreate stage with applicable type(s) Status for txn #38624 on ticket #2985 (/opt/rt4/sbin/…/lib/RT/Scrips.pm:367)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #19 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #35 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #18 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #12 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #2 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #21 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #27 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #23 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #29 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #37 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: Skipping Scrip #38 because it isn’t applicable (/opt/rt4/sbin/…/lib/RT/Scrips.pm:229)
[Mon Sep 10 13:24:00 2012] [debug]: About to commit scrips for transaction #38624 (/opt/rt4/sbin/…/lib/RT/Transaction.pm:197)
[Mon Sep 10 13:24:00 2012] [debug]: Committing scrip #34 on txn #38624 of ticket #2985 (/opt/rt4/sbin/…/lib/RT/Scrips.pm:183)
[Mon Sep 10 13:24:00 2012] [info]: Attempting to auto assign ticket #2985 to user #3444 ((eval 3201):12)
[Mon Sep 10 13:24:00 2012] [debug]: Committing scrip #10 on txn #38624 of ticket #2985 (/opt/rt4/sbin/…/lib/RT/Scrips.pm:183)
[Mon Sep 10 13:24:00 2012] [debug]: Calling SetRecipientDigests for transaction RT::Transaction=HASH(0xe6582b0), id 38624 (/opt/rt4/sbin/…/lib/RT/Action/SendEmail.pm:6

Attached you can find two queries I’ve just executed in my DB. It shows that the owner is correctly set as the id of the user John Doe.

Ø And the chart looks for the resolved transaction to get owner at the time of resolution?
I don’t think it’s a matter of transactions, as I think the chart queries tickets, not transactions.

Alberto Scotto

Blue Reply
Via Cardinal Massaia, 83
10147 - Torino - ITALY
phone: +39 011 29100
al.scotto@reply.it
www.reply.it

[Blue Reply]From: Joe Harris [mailto:drey111@gmail.com]
Sent: giovedì 4 ottobre 2012 13:37
To: Scotto Alberto
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Setting owner with _Set breaks queries (may be a bug)

This is a shot in the dark and I may be way off, but is there a chance your scrip to set the owner runs in a transaction that happens after the transaction that resolves the ticket?

And the chart looks for the resolved transaction to get owner at the time of resolution?

Just a thought.

rt_tkt2985_from_db.html (7.45 KB)

Thomas_Sibley · October 4, 2012, 4:49pm

I think I found a bug.

Not a bug in RT, only your scrip.

Looks like _Set doesn’t update correctly some field in some table in the
DB. But I have no idea of what field and what table! I had a quick look
at the DB, and it seems that the relation ticket-owner is based on an
user ID, not a string (the real name).

Using _Set to change the Owner is incorrect as it only updates the Owner
column in the Tickets table. It doesn’t also update the Owner role
group for the ticket. SetOwner does both.

In general you shouldn’t be calling private methods (starting with an
underscore) from code you write, for reasons just like this.

rt-validator may be able to fix your existing tickets, but I haven’t
checked so I’m not certain.

http://bestpractical.com/rt/docs/latest/rt-validator.html

Scotto_Alberto · October 5, 2012, 3:15pm

Very interesting, thank you!
Now I understand the relationships.
So I think I should correct all the _Set I’ve used all around in my custom scrips

Talking about correcting the old tickets processed with _Set, I think rt-validator doesn’t help, can you please confirm?
Here is the output of rt-validator --check: rt-validator output - Pastebin.com
To verify if it finds the errors caused by _Set I searched in the ouput for “2985”, which is the number of a ticket affected by the issue. I also searched for “12626”, the ID of the owner group associated with that ticket. But none of them are there.

Thanks

PS: (OT) if at every moment the Owner is one and only one, I wonder why you defined a group for role Owner. Maybe in order to have a history of past owners? Or for some code consistency?

Alberto Scotto

Blue Reply
Via Cardinal Massaia, 83
10147 - Torino - ITALY
phone: +39 011 29100
al.scotto@reply.it
www.reply.itFrom: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: giovedì 4 ottobre 2012 18:50
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Setting owner with _Set breaks queries (may be a bug)

I think I found a bug.

Not a bug in RT, only your scrip.

Looks like _Set doesn’t update correctly some field in some table in
the DB. But I have no idea of what field and what table! I had a quick
look at the DB, and it seems that the relation ticket-owner is based
on an user ID, not a string (the real name).

Using _Set to change the Owner is incorrect as it only updates the Owner column in the Tickets table. It doesn’t also update the Owner role group for the ticket. SetOwner does both.

In general you shouldn’t be calling private methods (starting with an
underscore) from code you write, for reasons just like this.

rt-validator may be able to fix your existing tickets, but I haven’t checked so I’m not certain.

http://bestpractical.com/rt/docs/latest/rt-validator.html

Final RT training for 2012 in Atlanta, GA - October 23 & 24
http://bestpractical.com/training

We’re hiring! Careers — Best Practical Solutions

The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

Thomas_Sibley · October 9, 2012, 5:14pm

Very interesting, thank you! Now I understand the relationships. So I
think I should correct all the _Set I’ve used all around in my custom
scrips

Yes, rarely is _Set the right answer.

To verify if it finds the errors caused by _Set I searched in the
ouput for “2985”, which is the number of a ticket affected by the
issue. I also searched for “12626”, the ID of the owner group
associated with that ticket. But none of them are there.

rt-validator does not validate or fix this problem. There’s a comment
in the code:

# XXX: check that owner is only member of owner role group

PS: (OT) if at every moment the Owner is one and only one, I wonder
why you defined a group for role Owner. Maybe in order to have a
history of past owners? Or for some code consistency?

All access control in RT is based on groups (even individual users have
equivalence groups containing only them). The reason Owner is a column
on the Tickets table is for historical reasons, performance, and
convenience.

Scotto_Alberto · October 9, 2012, 6:14pm

Ok, thanks for the explanations.

I tried executing an UPDATE statement, but nothing has changed. The TicketSQL query "Owner.RealName = ‘Nobody in particular’ " still returns more tickets than it should.

Here is the UPDATE, which sets GroupMembers.MemberId with the value in Tickets.Owner:
UPDATE Tickets t join Groups g on t.id=g.Instance join GroupMembers m on m.GroupId=g.id
SET m.MemberId=t.Owner
WHERE
g.Domain = ‘RT::Ticket-Role’ and
g.Type = ‘Owner’ and
t.Owner <> m.MemberId

Now the query that looks for tickets where GroupMembers.MemberId differs from Tickets.Owner
returns 0 rows:
select * from Tickets t join Groups g on t.id=g.Instance join GroupMembers m on m.GroupId=g.id
WHERE g.Domain = ‘RT::Ticket-Role’ and g.Type = ‘Owner’ and t.Owner <> m.MemberId;

What am I missing?
Thank you again

Da: rt-users-bounces@lists.bestpractical.com [rt-users-bounces@lists.bestpractical.com] per conto di Thomas Sibley [trs@bestpractical.com]
Inviato: martedì 9 ottobre 2012 19.14
A: rt-users@lists.bestpractical.com
Oggetto: Re: [rt-users] Setting owner with _Set breaks queries (may be a bug)

Alberto Scotto

Blue Reply
Via Cardinal Massaia, 83
10147 - Torino - ITALY
phone: +39 011 29100
al.scotto@reply.it
www.reply.it

Very interesting, thank you! Now I understand the relationships. So I
think I should correct all the _Set I’ve used all around in my custom
scrips

Yes, rarely is _Set the right answer.

To verify if it finds the errors caused by _Set I searched in the
ouput for “2985”, which is the number of a ticket affected by the
issue. I also searched for “12626”, the ID of the owner group
associated with that ticket. But none of them are there.

rt-validator does not validate or fix this problem. There’s a comment
in the code:

# XXX: check that owner is only member of owner role group

PS: (OT) if at every moment the Owner is one and only one, I wonder
why you defined a group for role Owner. Maybe in order to have a
history of past owners? Or for some code consistency?

All access control in RT is based on groups (even individual users have
equivalence groups containing only them). The reason Owner is a column
on the Tickets table is for historical reasons, performance, and
convenience.

Final RT training for 2012 in Atlanta, GA - October 23 & 24
http://bestpractical.com/training

We’re hiring! Careers — Best Practical Solutions

The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

Thomas_Sibley · October 9, 2012, 9:29pm

What am I missing?

The CachedGroupMembers table, I expect. rt-validator should be able to
fix that, I believe.

Scotto_Alberto · October 9, 2012, 10:54pm

It worked tah!
Powerful thank you! Great tool rt-validator!

Just hope I haven’t screwed up all the rest

Da: rt-users-bounces@lists.bestpractical.com [rt-users-bounces@lists.bestpractical.com] per conto di Thomas Sibley [trs@bestpractical.com]
Inviato: martedì 9 ottobre 2012 23.29
A: rt-users@lists.bestpractical.com
Oggetto: Re: [rt-users] R: Setting owner with _Set breaks queries (may be a bug)

Alberto Scotto

Blue Reply
Via Cardinal Massaia, 83
10147 - Torino - ITALY
phone: +39 011 29100
al.scotto@reply.it
www.reply.itOn 10/09/2012 11:14 AM, Scotto Alberto wrote:

What am I missing?

The CachedGroupMembers table, I expect. rt-validator should be able to
fix that, I believe.

Final RT training for 2012 in Atlanta, GA - October 23 & 24
http://bestpractical.com/training

We’re hiring! Careers — Best Practical Solutions

The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.