Rt-validator does not delete osbolete ObjectCustomFieldValue records

Hi,

We’re trying to do a large cleanup of our database. There are a lot of obsolete ObjectCustomFieldValue records that are related to deleted tickets.
I would expect rt-validator to both delete related Transactions AND The ObjectCustomFieldValue records themselves.

This is not happening, although it does detect issues - for example:
Record #33716942 in ObjectCustomFieldValues references a nonexistent record in Tickets
ObjectId => ‘6417200’ => id
Record #1 in ObjectCustomFieldValues references a nonexistent record in Transactions
ObjectId => ‘8651463’ => id

After spending a lot on time on this, I came to realize that the sub in rt-validator for custom fields integrity does not use delete_record at all.

Example that uses delete_record:
push @CHECKS, ‘Tickets <-> Role Groups’ => sub {
# XXX: we check only that there is at least one group for a queue
# from queue to group
check_integrity(
‘Tickets’, ‘id’ => ‘Groups’, ‘Instance’,
join_condition => ‘t.Domain = ?’,
bind_values => [ ‘RT::Ticket-Role’ ],
);
# from group to ticket
check_integrity(
‘Groups’, ‘Instance’ => ‘Tickets’, ‘id’,
condition => ‘s.Domain = ?’,
bind_values => [ ‘RT::Ticket-Role’ ],
action => sub {
my $id = shift;
return unless prompt(
‘Delete’, “Found a role group of a nonexistent ticket.”
);

        delete_record( 'Groups', $id );
    },
);

};

Now, custom field checks are done this way, without delete_record:
push @CHECKS, ‘CustomFields and friends’ => sub {
#XXX: ObjectCustomFields needs more love
check_integrity(
‘CustomFieldValues’, ‘CustomField’ => ‘CustomFields’, ‘id’,
);
check_integrity(
‘ObjectCustomFieldValues’, ‘CustomField’ => ‘CustomFields’, ‘id’,
);
foreach my $model ( @models ) {
check_integrity(
‘ObjectCustomFieldValues’, ‘ObjectId’ => m2t($model), ‘id’,
condition => ‘s.ObjectType = ?’,
bind_values => [ “RT::$model” ],
);
}
};

I’m not entirely safe with putting a delete_record somewhere in there.

Can someone chime in about what would be my best course of action ?

Thanks,

David Moreau Simard

Hi,

Do you care enough to send patch or git pull request? If not then file a
feature request on http://issues.bestpractical.com .On Thu, Aug 29, 2013 at 5:18 PM, David Moreau Simard dmsimard@iweb.comwrote:

Hi,

We’re trying to do a large cleanup of our database. There are a lot of
obsolete ObjectCustomFieldValue records that are related to deleted tickets.
I would expect rt-validator to both delete related Transactions AND The
ObjectCustomFieldValue records themselves.

This is not happening, although it does detect issues - for example:

Record #33716942 in ObjectCustomFieldValues references a nonexistent
record in Tickets
ObjectId => ‘6417200’ => id

Record #1 in ObjectCustomFieldValues references a nonexistent record in
Transactions
ObjectId => ‘8651463’ => id

After spending a lot on time on this, I came to realize that the sub in
rt-validator for custom fields integrity does not use delete_record at all.

Example that uses delete_record:

push @CHECKS, ‘Tickets ↔ Role Groups’ => sub {
# XXX: we check only that there is at least one group for a queue
# from queue to group
check_integrity(
‘Tickets’, ‘id’ => ‘Groups’, ‘Instance’,
join_condition => ‘t.Domain = ?’,
bind_values => [ ‘RT::Ticket-Role’ ],
);
# from group to ticket
check_integrity(
‘Groups’, ‘Instance’ => ‘Tickets’, ‘id’,
condition => ‘s.Domain = ?’,
bind_values => [ ‘RT::Ticket-Role’ ],
action => sub {
my $id = shift;
return unless prompt(
‘Delete’, “Found a role group of a nonexistent ticket.”
);

        delete_record( 'Groups', $id );
    },
);

};

Now, custom field checks are done this way, without delete_record:

push @CHECKS, ‘CustomFields and friends’ => sub {
#XXX: ObjectCustomFields needs more love
check_integrity(
‘CustomFieldValues’, ‘CustomField’ => ‘CustomFields’, ‘id’,
);
check_integrity(
‘ObjectCustomFieldValues’, ‘CustomField’ => ‘CustomFields’, ‘id’,
);
foreach my $model ( @models ) {
check_integrity(
‘ObjectCustomFieldValues’, ‘ObjectId’ => m2t($model), ‘id’,
condition => ‘s.ObjectType = ?’,
bind_values => [ “RT::$model” ],
);
}
};

I’m not entirely safe with putting a delete_record somewhere in there.

Can someone chime in about what would be my best course of action ?

Thanks,

David Moreau Simard

Best regards, Ruslan.

Hi,

Does these commits make sense to you ? It seems to work well.

David Moreau SimardOn 2013-08-30, at 9:37 AM, Ruslan Zakirov <ruz@bestpractical.commailto:ruz@bestpractical.com> wrote:

Hi,

Do you care enough to send patch or git pull request? If not then file a feature request on http://issues.bestpractical.comhttp://issues.bestpractical.com/ .

On Thu, Aug 29, 2013 at 5:18 PM, David Moreau Simard <dmsimard@iweb.commailto:dmsimard@iweb.com> wrote:
Hi,

We’re trying to do a large cleanup of our database. There are a lot of obsolete ObjectCustomFieldValue records that are related to deleted tickets.
I would expect rt-validator to both delete related Transactions AND The ObjectCustomFieldValue records themselves.

This is not happening, although it does detect issues - for example:
Record #33716942 in ObjectCustomFieldValues references a nonexistent record in Tickets
ObjectId => ‘6417200’ => id
Record #1 in ObjectCustomFieldValues references a nonexistent record in Transactions
ObjectId => ‘8651463’ => id

After spending a lot on time on this, I came to realize that the sub in rt-validator for custom fields integrity does not use delete_record at all.

Example that uses delete_record:
push @CHECKS, ‘Tickets <-> Role Groups’ => sub {
# XXX: we check only that there is at least one group for a queue
# from queue to group
check_integrity(
‘Tickets’, ‘id’ => ‘Groups’, ‘Instance’,
join_condition => ‘t.Domain = ?’,
bind_values => [ ‘RT::Ticket-Role’ ],
);
# from group to ticket
check_integrity(
‘Groups’, ‘Instance’ => ‘Tickets’, ‘id’,
condition => ‘s.Domain = ?’,
bind_values => [ ‘RT::Ticket-Role’ ],
action => sub {
my $id = shift;
return unless prompt(
‘Delete’, “Found a role group of a nonexistent ticket.”
);

        delete_record( 'Groups', $id );
    },
);

};

Now, custom field checks are done this way, without delete_record:
push @CHECKS, ‘CustomFields and friends’ => sub {
#XXX: ObjectCustomFields needs more love
check_integrity(
‘CustomFieldValues’, ‘CustomField’ => ‘CustomFields’, ‘id’,
);
check_integrity(
‘ObjectCustomFieldValues’, ‘CustomField’ => ‘CustomFields’, ‘id’,
);
foreach my $model ( @models ) {
check_integrity(
‘ObjectCustomFieldValues’, ‘ObjectId’ => m2t($model), ‘id’,
condition => ‘s.ObjectType = ?’,
bind_values => [ “RT::$model” ],
);
}
};

I’m not entirely safe with putting a delete_record somewhere in there.

Can someone chime in about what would be my best course of action ?

Thanks,

David Moreau Simard

Best regards, Ruslan.

Add some handling for ObjectCustomFieldValues to rt-validator by dmsimard · Pull Request #64 · bestpractical/rt · GitHub

Commenting on the site. Let’s keep discussion closer to the code.

Best regards, Ruslan.