Oddity within the RT database

It looks as though at some point, probably a long time ago during an upgrade, that I accidentally ran part of the upgrade database task twice, and as a result some of the standard portlet searches are duplicated:

select id, Name from Attributes where ObjectType = ‘RT::System’ order by Name

returns:

id,Name
1470,BrandedSubjectTag
494,HomepageSettings
497,HomepageSettings
18200,QueueCacheNeedsUpdate
498,"Search - Bookmarked Tickets"
499,"Search - My Requested Tickets"
492,"Search - My Tickets"
495,"Search - My Tickets"
493,"Search - Unowned Tickets"
496,“Search - Unowned Tickets”

Obviously in the RT-at-a-glance page, and in dashboard content pages, these searches appear twice, which is a bit messy, if harmless.

If I wanted to clean this up, obviously I could remove one of the duplicated entries in each case, but then I presumably need to also sanitise where they are referenced from. Are the references going to be embedded in blobs in other Attributes? I can see some of them are referenced in the Name of some other Attributes (for example Pref-RT::Attribute-492 and likewise 493 and 496) which presumably could be fairly easily fixed with a simple update. But I imagine these things are referenced within saved home page settings and dashboards somewhere, which could be messy to fix.

Any thoughts?

Tim

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

Have you tried to delete them in ui?

Use rt-attributes viewer that is shipped with rt and installed into sbin
directory. Decide which one you need and just delete an attribute. As far
as I recall dashboards handle this properly.

Ruslan from phone.

It looks as though at some point, probably a long time ago during an
upgrade, that I accidentally ran part of the upgrade database task twice,
and as a result some of the standard portlet searches are duplicated:

select id, Name from Attributes where ObjectType = ‘RT::System’ order by
Name

returns:

id,Name
1470,BrandedSubjectTag
494,HomepageSettings
497,HomepageSettings
18200,QueueCacheNeedsUpdate
498,"Search - Bookmarked Tickets"
499,"Search - My Requested Tickets"
492,"Search - My Tickets"
495,"Search - My Tickets"
493,"Search - Unowned Tickets"
496,“Search - Unowned Tickets”

Obviously in the RT-at-a-glance page, and in dashboard content pages,
these searches appear twice, which is a bit messy, if harmless.

If I wanted to clean this up, obviously I could remove one of the
duplicated entries in each case, but then I presumably need to also
sanitise where they are referenced from. Are the references going to be
embedded in blobs in other Attributes? I can see some of them are
referenced in the Name of some other Attributes (for example
Pref-RT::Attribute-492 and likewise 493 and 496) which presumably could be
fairly easily fixed with a simple update. But I imagine these things are
referenced within saved home page settings and dashboards somewhere, which
could be messy to fix.

Have you tried to delete them in ui?

Is that possible? These don’t show up in the query builder is loadable saved searches - they’re the RT::System standard ones, not user or group saved searches.

Use rt-attributes viewer that is shipped with rt and installed into sbin directory.

I’ve been using that to investigate them, yes.

Decide which one you need and just delete an attribute.

From what I can see, both are in use by the various dashboards and things that users have set. I think I may have to write a script to go through all the Attributes blob, and for these duplicated things set them to one particular one, and only once I’ve done that can I safely delete the redundant saved search.

As far as I recall dashboards handle this properly.

If they referred to their saved searches by name, they probably would, but judging by the output of rt-attributes-viewer, they’re referring to them by id.

This could be a fun script to write… :slight_smile:

Tim

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

Ruslan from phone.

Have you tried to delete them in ui?

Is that possible? These don’t show up in the query builder is loadable
saved searches - they’re the RT::System standard ones, not user or group
saved searches.

Use rt-attributes viewer that is shipped with rt and installed into
sbin directory.

I’ve been using that to investigate them, yes.

Decide which one you need and just delete an attribute.

From what I can see, both are in use by the various dashboards and things
that users have set. I think I may have to write a script to go through
all the Attributes blob, and for these duplicated things set them to one
particular one, and only once I’ve done that can I safely delete the
redundant saved search.

This is better way to go.

As far as I recall dashboards handle this properly.

If they referred to their saved searches by name, they probably would,
but judging by the output of rt-attributes-viewer, they’re referring to
them by id.

This could be a fun script to write… :slight_smile:

Nothing extream, enough examples in rt code and easy to find them.

Ruslan from phone.

Have you tried to delete them in ui?

Is that possible? These don’t show up in the query builder is loadable saved searches - they’re the RT::System standard ones, not user or group saved searches.

Use rt-attributes viewer that is shipped with rt and installed into sbin directory.

I’ve been using that to investigate them, yes.

Decide which one you need and just delete an attribute.

From what I can see, both are in use by the various dashboards and things that users have set. I think I may have to write a script to go through all the Attributes blob, and for these duplicated things set them to one particular one, and only once I’ve done that can I safely delete the redundant saved search.

This is better way to go.

Yep. After all that care, it turned out only one dashboard needed updating before I deleted the duplicate rows, but for the record here’s what I did. Up front information:

Two system saved searches were duplicated, so I wanted to replace references to search 495 with 492, and search 496 with 493.

Step 1, update Dashboard references to the redundant searches, for which I used the script attached at the end of this mail.

Step 2, with straightforwards SQL, rename preferences associated with the redundant searches:

UPDATE Attributes SET Name = ‘Pref-RT::Attribute-492’ WHERE Name = ‘Pref-RT::Attribute-495’;
UPDATE Attributes SET Name = ‘Pref-RT::Attribute-493’ WHERE Name = ‘Pref-RT::Attribute-496’;

Step 3, delete the redundant searches:

DELETE FROM Attributes WHERE id = 495;
DELETE FROM Attributes WHERE id = 496;

I think that’s about all that’s required. Certainly no-one’s screaming…

I don’t think anything’s needed for homepage portlet preferences, since those appear to be store by search name rather than by id. It’s possible some users might have saved graphs, but I think I’ll cross that bridge if someone complains, because the numbers will be single digits.

I’d always assumed that the home page was simply a special case of a dashboard, and was rather surprised to see that the implementation is completely different. Is that for historical reasons? I’m trying to remember - I suspect the customisable homepage was written first (3.6?) and dashboards came later (3.8?).

Regards,

Tim

PS. Here’s my little script:

#!/usr/bin/perl
use strict;
use warnings;

use lib qw(/opt/rt4/lib /opt/rt4/local/lib);

use Data::Dumper;
use RT;
use RT::Attributes;
use RT::Dashboard;
use RT::User;

RT::LoadConfig();
RT::Init();

These are the system search id’s I want to map across

my %attr_map = ( 495 => 492,
496 => 493 );

my $attributes = RT::Attributes->new(RT->SystemUser);
my $dashboard = RT::Dashboard->new(RT->SystemUser);
my $user = RT::User->new(RT->SystemUser);

$attributes->Limit( FIELD => ‘Name’, VALUE => ‘Dashboard’);

my ($res, $err);

while (my $attr = $attributes->Next) {
# RT::SystemUser has no rights to load user’s
# dashboards, so we load the creating user’s object to
# use when loading the dashboard.
($res, $err) = $user->LoadById($attr->Creator);
unless ($res) {
warn "Could not load user: ", $attr->Creator, $err;
next;
}

my $dashboard = RT::Dashboard->new($user);
($res, $err) = $dashboard->LoadById($attr->Id);
unless ($res) {
warn "Could not load dashboard ", $attr->Id,
         " as user ", $user->Name, ":", $err;
    next;
}

my ($pane, $portlets);
my $panes = $dashboard->Panes;
my $before = Dumper($panes);
while (($pane, $portlets) = each %$panes) {
foreach my $portlet (@$portlets) {
    next unless ($portlet->{portlet_type} eq 'search');
    if (exists($attr_map{$portlet->{id}})) {
	$portlet->{id} = $attr_map{$portlet->{id}};
    }
}
}

my $after = Dumper($panes);
if ($before ne $after) {
($res, $err) = $dashboard->Update(Panes => $panes);
if ($res) {
    printf q(

User: %s
Dashboard: %s (%d)
Before: %s
After: %s
), $user->Name, $dashboard->Name, $dashboard->Id, $before, $after;
} else {
warn "Failed to update dashboard ", $dashboard->Id,
’: ', $err;
}
}
}

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

It looks as though at some point, probably a long time ago during an upgrade, that I accidentally ran part of the upgrade database task twice, and as a result some of the standard portlet searches are duplicated:

select id, Name from Attributes where ObjectType = ‘RT::System’ order by Name

returns:

id,Name
1470,BrandedSubjectTag
494,HomepageSettings
497,HomepageSettings
18200,QueueCacheNeedsUpdate
498,"Search - Bookmarked Tickets"
499,"Search - My Requested Tickets"
492,"Search - My Tickets"
495,"Search - My Tickets"
493,"Search - Unowned Tickets"
496,“Search - Unowned Tickets”

Obviously in the RT-at-a-glance page, and in dashboard content pages, these searches appear twice, which is a bit messy, if harmless.

If I wanted to clean this up, obviously I could remove one of the duplicated entries in each case, but then I presumably need to also sanitise where they are referenced from. Are the references going to be embedded in blobs in other Attributes? I can see some of them are referenced in the Name of some other Attributes (for example Pref-RT::Attribute-492 and likewise 493 and 496) which presumably could be fairly easily fixed with a simple update. But I imagine these things are referenced within saved home page settings and dashboards somewhere, which could be messy to fix.

You can edit the global RT At a Glance to remove the dupes before
shredding or deleting them, but keep in mind that individual users may
have their own clone of HomepageSettings with their user-specific
homepage and that’s where you would run into problems with references
to the deleted attribute.

-kevin