RepairSearches

From Request Tracker Wiki
Jump to navigation Jump to search

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:

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     =>
  1. '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)