Re: [rt-users] missing peoples problem
Shredder overzealously wiped some information a few months back when I was cleaning out spam, and I'm just now discovering a very odd problem. I have a ticket whose requestor was in an odd state. Search results and the ticket display page showed no requestor, but the people page listed the requestor correctly. I removed the requestor in the hopes that adding him back would fix things, but this requestor does not show up when I search for him by name, email or id. I tried creating another user to use as the requestor, but this user also does not show up when searching, although plenty of other valid matches do. I've since gone back, found the removed entries from Principals (Cached)GroupMembers to no avail. No joy? Why should two users, one newly created, be invisible to most of RT? ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] missing peoples problem
Shredder overzealously wiped some information a few months back when I was cleaning out spam, and I'm just now discovering a very odd problem. I have a ticket whose requestor was in an odd state. Search results and the ticket display page showed no requestor, but the people page listed the requestor correctly. I removed the requestor in the hopes that adding him back would fix things, but this requestor does not show up when I search for him by name, email or id. I tried creating another user to use as the requestor, but this user also does not show up when searching, althouh plenty of other valid matches do. I've since gone back, found the removed entries from Principals (Cached)GroupMembers to no avail. Any ideas what might be wrong and how to fix it? -- Cambridge Energy Alliance: Save money. Save the planet. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] missing peoples problem
Jerrad, I suspect that the ticket history for any tickets with these users as requestors will error out. This has happened to me before. I can show you how to fix it, but I will need some info. First, get the UserID for each of these missing Requestors. Once we have those, we will have to run a series of SQL commands to find out just who is missing and what tickets are affected, including closed tickets. I hope you are familiar with SQL. What Database are you using? We're on Oracle, but the sequence of events should still be the same. Kenn LBNL On 12/8/2009 1:34 PM, Jerrad Pierce wrote: Shredder overzealously wiped some information a few months back when I was cleaning out spam, and I'm just now discovering a very odd problem. I have a ticket whose requestor was in an odd state. Search results and the ticket display page showed no requestor, but the people page listed the requestor correctly. I removed the requestor in the hopes that adding him back would fix things, but this requestor does not show up when I search for him by name, email or id. I tried creating another user to use as the requestor, but this user also does not show up when searching, althouh plenty of other valid matches do. I've since gone back, found the removed entries from Principals (Cached)GroupMembers to no avail. Any ideas what might be wrong and how to fix it? ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] missing peoples problem
I suspect that the ticket history for any tickets with these users as requestors will error out. This has happened to me before. I can show you how to fix it, but I will need some info. Nope, we actually have several tickets in that state, where part way through the display of transaction history RT croaks. This ticket displays fine, though it (now) has no requestor, and I cannot find the requestor via the search form on the modify people page, nor the admin user search form. However the original requesting user exists, and can be loaded in the modify user page if the correct id is specified in the URL, or via Shredder's search results. First, get the UserID for each of these missing Requestors. Once we have those, we will have to run a series of SQL commands to find out just who is missing and what tickets are affected, including closed tickets. I hope you are familiar with SQL. What Database are you using? We're on Oracle, but the sequence of events should still be the same. It might be worth posting this Shredder recovery as a how-to to the wiki. Additional details: mysqlcheck reports no errors, and there is nothing of consequence in the logs. We recently upgraded from 3.8.1 to 3.8.6, and I did perform the database upgrades. I'm not sure if the problem existed before the switch, but it seems doubtful given the frequency of the activity (spreadsheet dumping) that lead me to uncover this. None of our local customizations seem likely candidates to be interfering with this, especially on such a limited basis: local/lib/RT/Transaction_Local.pm -- recognize VCF is text local/lib/RT/CustomFields_Local.pm -- simplified code, switch sort order local/lib/RT/Interface/Email/Filter/SpamAssassin.pm local/lib/RT/Interface/Email_Local.pm -- tag parser with Q in ticket ID local/lib/RT/Interface/Web_Local.pm -- long life cookies local/lib/RT/Shredder/Plugin/Users.pm -- search on other fields: real, name local/lib/RT/Shredder/Plugin/Attachments.pm -- minimum file size ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] missing peoples problem
Jerrad,
It looks to me as though the actual User record is missing from the USER
Table. The reason the history croaks is because RT is walking thru the
ticket history and there is a reference to a UserID that no longer
exists. You need to look at the Transaction history to determine what is
missing. This is what I have been doing:
1) Find all transactions for a ticket that might refer to a User. I use
this SQL:
Select *
from TRANSACTIONS
where OBJECTTYPE = 'RT::Ticket'
and Type in ('AddWatcher', 'DelWatcher', 'Force', 'Give', 'Steal',
'Take', 'Untake');
Select *
from TRANSACTIONS
where OBJECTTYPE = 'RT::Ticket'
and CREATOR in (61876);
This will allow you to determine the UserID that is missing. Then I look
for all instances for that User:
Select *
from TRANSACTIONS
where OBJECTTYPE = 'RT::Ticket'
and Type in ('AddWatcher', 'DelWatcher', 'Force', 'Give', 'Steal',
'Take', 'Untake')
and NewValue in ('61876'); I also use this with NewValue as well.
Once you do this for all missing Users, you will have a list of who is
missing.
2) Next, I check all the various Tables that could have references to
the missing UserID:
Select *
from GROUPMEMBERS
where MEMBERID in (61876);
Select *
from ACL
Where ObjectType = 'RT::Queue'
and PRINCIPALID in (61876);
select *
from CACHEDGROUPMEMBERS
where MEMBERID in (61876, 61877);
Select *
from ATTRIBUTES
where CREATOR in (61876)
or OBJECTID in (61876);
Select *
from TICKETS
where OWNER in (61876)
or CREATOR in (61876)
or LASTUPDATEDBY in (61876);
Select *
from ATTACHMENTS
where CREATOR in (161876);
For referencing purposes, I also look for every ticket where that User
was used:
Select *
from GROUPS
where INSTANCE = 61876;
This last SQL will give an Id number based on the type of group/role.
There will be a Group record for each instance of use per ticket. If
that User is a Requestor and an owner, there will be 2 records one with
type = Requestor and one with type = owner.. Each will point to the
ticket with that particular relationship. The Domain field will describe
the relationship ie. RT::Ticket-Role.
Anyway, I think you get the jist of this. Once you do this for each and
every missing User, you will be able to /manually modify/ these records
using SQL. This is how:
1. Create a new user that will be, in fact, a replacement for the
missing one.
2. Once you have created the new user, you will have a new UserID
that corresponds to this new User.
3. That new UserID is what you put into all those table records that
are using the OLD UserID.
This has to be done manually because the old UserID is missing. So, you
have to replace all the references to that OLD ID with the NEW ID. Does
that make sense?
Kenn
LBNL
On 12/8/2009 2:22 PM, Jerrad Pierce wrote:
I suspect that the ticket history for any tickets with these users as
requestors will error out. This has happened to me before. I can show you
how to fix it, but I will need some info.
Nope, we actually have several tickets in that state, where part way through
the display of transaction history RT croaks. This ticket displays fine, though
it (now) has no requestor, and I cannot find the requestor via the search
form on the modify people page, nor the admin user search form. However the
original requesting user exists, and can be loaded in the modify user page if
the correct id is specified in the URL, or via Shredder's search results.
First, get the UserID for each of these missing Requestors. Once we have
those, we will have to run a series of SQL commands to find out just who is
missing and what tickets are affected, including closed tickets. I hope you
are familiar with SQL. What Database are you using? We're on Oracle,
but the sequence of events should still be the same.
It might be worth posting this Shredder recovery as a how-to to the wiki.
Additional details: mysqlcheck reports no errors, and there is nothing of
consequence in the logs. We recently upgraded from 3.8.1 to 3.8.6, and
I did perform the database upgrades. I'm not sure if the problem existed
before the switch, but it seems doubtful given the frequency of the activity
(spreadsheet dumping) that lead me to uncover this.
None of our local customizations seem likely candidates to be interfering
with this, especially on such a limited basis:
local/lib/RT/Transaction_Local.pm -- recognize VCF is text
local/lib/RT/CustomFields_Local.pm -- simplified code, switch sort order
local/lib/RT/Interface/Email/Filter/SpamAssassin.pm
local/lib/RT/Interface/Email_Local.pm -- tag parser with Q in ticket ID
local/lib/RT/Interface/Web_Local.pm -- long life cookies
local/lib/RT/Shredder/Plugin/Users.pm -- search on other fields: real, name
local/lib/RT/Shredder/Plugin/Attachments.pm -- minimum file size
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial
