RepairSearches
Introduction
Reasons and impacts of broken searches
- You played around with the searches and now the links disappeared
- RT links the own tickets to the wrong location:
- rt lives below /rt, but clicking on my own tickets gives a link to /Ticket/Display.html?...
- search works, selecting unowned tickets also works
- This is a known bug in 3.6.1 (at least)
- The Webpath variable is set correct:
- 09:32 < telmich> rt23# grep WebPath /usr/local/rt3/etc/RT_SiteConfig.pm
- 09:32 < telmich> Set($WebPath , "/rt");
How to fix
Missing Take and view links in 3.6.3
Editing a saved search can remove the Take or View links from the "top N issues" searches. These can be put back as follows
- remove the field that you wish to have as a link from the field list (usually ID and subject)
- re-select the field in the left hand list
- in the links section choose Take or View from the drop-down, set the title (to view or take), the size to large and the format to bold
- click the right arrow button to add the link to your field list
- move the field back up the list to where it used to be
- save the search
Pre 3.6
The problem is in the database, so we have to fix it there.
Backup your database BEFORE CONTINUING!
Like: pg_dump -U rt rt3 > rt3-before-delete-repair.psql
Normally nothing goes wrong, but having a backup is much better than having none and breaking it.
The "My Tickets"-Problem (3.6.1-problem)
Use your database tool (I use psql, because I use postgresql) and find out the id of the search:
select id, Name from Attributes WHERE Name = 'Search - My Tickets';
Write down that id. Do not mess it up.
Then delete that entry:
DELETE FROM Attributes WHERE id = <YOUR-ID>;
In my case, this was "DELETE FROM Attributes WHERE id = 1;". Be sure to use '''YOUR''' id!
Now create a file named "repair-search", that contains the data we want to import with the following content (that is also found in etc/initialdata):
@Attributes = (
{ Name => 'Search - My Tickets',
Description => '[_1] highest priority tickets I own', # loc
Content =>
{ Format => "'<a href=\"__WebPath__/Ticket/Display.html?id=__id__\">__id__</a>/TITLE:#', '<a href=\"__WebPath__/Ticket/Display.html?id=__id__\">__Subject__</a>/TITLE:Subject', Priority, QueueName, ExtendedStatus",
Query => " Owner = '__CurrentUser__' AND ( Status = 'new' OR Status = 'open')",
OrderBy => 'Priority',
Order => 'DESC' },
},
);
Pay attention: Copy and paste may be broken, please extract that lines from etc/initialdata to be sure!
Now import it into the rt:
sudo ./sbin/rt-setup-database --action insert --datafile repair-search --dba postgres
You should know, whether you have to use sudo and to to select the correct dba.
And tada: Now it works!
The "Unowned Tickets" problem
I just screwed it up when playing around with rt. The way is very similar to the one above:
select id, Name from Attributes WHERE Name = 'Search - Unowned Tickets';
Remember the id, delete that attribute:
DELETE FROM Attributes WHERE id = <YOUR-ID>;
In my case this was DELETE FROM Attributes WHERE id = 2;
Create a file named 'rt-patch-search' containg the original search:
@Attributes = (
{ Name => 'Search - Unowned Tickets',
Description => '[_1] newest unowned tickets', # loc
Content =>
- 'Take' #loc
{ Format => "'<a href=\"__WebPath__/Ticket/Display.html?id=__id__\">__id__</a>/TITLE:#', '<a href=\"__WebPath__/Ticket/Display.html?id=__id__\">__Subject__</a>/TITLE:Subject', QueueName, ExtendedStatus, CreatedRelative, '<A HREF=\"__WebPath__/Ticket/Display.html?Action=Take&id=__id__\">__loc(Take)__</a>/TITLE: ' ", Query => " Owner = 'Nobody' AND ( Status = 'new' OR Status = 'open')",
OrderBy => 'Created',
Order => 'DESC' },
},
);
(again: please copy from your etc/initialdata, so now copy and paste error will make things worse)
And then import it:
/usr/local/rt3/sbin/rt-setup-database --action insert --datafile rt-patch-search --dba postgres
It runs again!
You screwed your own search preferences
Get rid of them very easy:
SELECT id FROM Users WHERE EmailAddress = 'Your@E-Mail';
Write down that id and use it in the following query:
DELETE FROM Attributes WHERE ObjectType = 'RT::User' AND ObjectId = <YOUR-ID> AND Name LIKE 'Pref-RT::Attribute-%';
In my case this was DELETE FROM Attributes WHERE ObjectType = 'RT::User' AND ObjectId = 22 AND Name LIKE 'Pref-RT::Attribute-%';
You screwed your Saved Search
Error message "Error near ->days<- expecting a PAREN in <TicketSQL query clause> I'm lost" (RT 3.6.7) may come when loading previously messed up saved search query.
Heres how to get rid of saved search:
Fetch your numeric userid with username or email address:
SELECT id,Name,EmailAddress FROM Users WHERE Name=<YOUR-USERID> OR EmailAddress='<Your@E-Mail>'
Write down your user id and perform query to fetch all saved searches for that id:
SELECT id,Name,Description,ContentType,ObjectType,ObjectId,Creator,Created,LastUpdatedBy,LastUpdated FROM Attributes WHERE Name = 'SavedSearch' AND Creator=<YOUR-ID> AND ObjectType='RT::User';
Identify the id of problematic search from Description column and delete it:
DELETE FROM Attributes WHERE id=<SEARCH-ID>
Example:
SELECT id,Name,EmailAddress FROM Users WHERE Name='joe' OR EmailAddress='joe@example.invalid'; +------+------+---------------------+ | id | Name | EmailAddress | +------+------+---------------------+ | 6596 | joe | joe@example.invalid | +------+------+---------------------+ 1 row in set (0.01 sec)
SELECT id,Name,Description,ObjectType,ObjectId,Creator FROM Attributes WHERE Name = 'SavedSearch' AND Creator=6596 AND ObjectType='RT::User'; +------+-------------+-------------------+------------+----------+---------+ | id | Name | Description | ObjectType | ObjectId | Creator | +------+-------------+-------------------+------------+----------+---------+ | 1242 | SavedSearch | Some other search | RT::User | 6596 | 6596 | | 2347 | SavedSearch | Screwed up search | RT::User | 6596 | 6596 | +------+-------------+-------------------+------------+----------+---------+ 2 rows in set (0.01 sec)
DELETE FROM Attributes WHERE id=2347; Query OK, 1 row affected (0.03 sec)