Searching on TransactionDate > xxx = no results

Ever since I upgraded from RT2 to RT3, a search like:

TransactionDate > 2003-10-01 04:00:00 GMT

always fails to return anything. Searching for TransactionDate < xxx
does return stuff though.

I’ve tried to debug this but I haven’t had much luck. It’s hard to grok
the stuff in the mysql SQL log especially with all the high-ascii chars in
the session table.

I’ve tested on production and in development. Both RH7.3 systems… I’ve
even tried moving to a self-compiled Perl 5.8.1 since 7.3 comes with
5.6.1. The dev environment has all the latest perl modules (as of a
couple hours ago) except searchbuilder which is at 0.88 cuz the newer
versions appear to have issues.

Can someone lend a hand?

MySQL 4.0.14 & .15, RT 3.0.6 (both upgraded and fresh pristine installs),
mod_perl/apache 1.3.28

Thanks,

-=| Ben

Ever since I upgraded from RT2 to RT3, a search like:

TransactionDate > 2003-10-01 04:00:00 GMT

always fails to return anything. Searching for TransactionDate < xxx
does return stuff though.

I’ve tried to debug this but I haven’t had much luck. It’s hard to grok
the stuff in the mysql SQL log especially with all the high-ascii chars in
the session table.

less and/or grep are your friends. We really need to see the SELECT
that’s searching the Tickets table.

I’ve tested on production and in development. Both RH7.3 systems… I’ve
even tried moving to a self-compiled Perl 5.8.1 since 7.3 comes with
5.6.1. The dev environment has all the latest perl modules (as of a
couple hours ago) except searchbuilder which is at 0.88 cuz the newer
versions appear to have issues.

I’m not sure what you’re talking about. What issues are you referring
to?

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

less and/or grep are your friends. We really need to see the SELECT
that’s searching the Tickets table.

I wrote a CLI script to help. Here’s the relevant script snippet:

$tickets->LimitTransactionDate(OPERATOR => ‘>’, VALUE => ‘2003-10-01’);

while (my $Ticket = $tickets->Next) {
print $Ticket->id . “\n”;
}

And the resulting queries:

SELECT * FROM Users WHERE Name = ‘RT_System’
SELECT * FROM Users WHERE Name = ‘Nobody’
SELECT * FROM Queues WHERE Name = ‘Incoming’
SELECT DISTINCT main.* FROM Tickets main, Transactions Transactions_1,
Attachments Attachments_2 WHERE ((main.EffectiveId = main.id)) AND
((main.Type = ‘ticket’)) AND ( ( ( (Attachments_2.Created > ‘2003-10-30
19:05:04’) ) ) ) AND Attachments_2.TransactionId = Transactions_1.id AND
main.id = Transactions_1.Ticket

I’ve noticed that the Attachments_2.Created > ‘XXX’ has a date in XXX
that’s always NOW() … In fact it’s NOW() for the “<” operator as well.

I’m not sure what you’re talking about. What issues are you referring
to?

0.93_3 caused Can’t call method “quote” on an undefined value for a
dbh->quote call… I don’t have the precise message anymore but I can
reinstall it and get it to you if you need. It always happened so I
figured it would be easily reproduceable.
I also noticed that 0.92 caused the list of tickets I own to change
order… to an order I couldn’t figure out.

-=| Ben

0.93_3 caused Can’t call method “quote” on an undefined value for a
dbh->quote call… I don’t have the precise message anymore but I can
reinstall it and get it to you if you need. It always happened so I
figured it would be easily reproduceable.

Right. But if nobody tells us what the error is or even that they’re
getting an error, then we’re never, ever going to fix it. Versions with
_ in them are development versions and shouldn’t be automatically
installed. But if you can send a stacktrace of the error, we’d really
appreciate it.

I also noticed that 0.92 caused the list of tickets I own to change
order… to an order I couldn’t figure out.

I haven’t noticed this one, but 0.92 did fix important issues relative
to 0.88. Can you upgrade to 0.92 and send us the query that RT generates
to find tickets that you own? That’s how we can trace down the issue you
report.

-=| Ben


rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

less and/or grep are your friends. We really need to see the SELECT
that’s searching the Tickets table.

I wrote a CLI script to help. Here’s the relevant script snippet:

$tickets->LimitTransactionDate(OPERATOR => ‘>’, VALUE => ‘2003-10-01’);

while (my $Ticket = $tickets->Next) {
print $Ticket->id . “\n”;
}

SELECT DISTINCT main.* FROM Tickets main, Transactions Transactions_1,
Attachments Attachments_2 WHERE ((main.EffectiveId = main.id)) AND
((main.Type = ‘ticket’)) AND ( ( ( (Attachments_2.Created > ‘2003-10-30
19:05:04’) ) ) ) AND Attachments_2.TransactionId = Transactions_1.id AND
main.id = Transactions_1.Ticket

This is now fsck.com ticket #4075.

-jesse

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

SELECT DISTINCT main.* FROM Tickets main, Transactions Transactions_1,
Attachments Attachments_2 WHERE ((main.EffectiveId = main.id)) AND
((main.Type = ‘ticket’)) AND ( ( ( (Attachments_2.Created > ‘2003-10-30
19:05:04’) ) ) ) AND Attachments_2.TransactionId = Transactions_1.id AND
main.id = Transactions_1.Ticket

This is now fsck.com ticket #4075.

Actually, could you see if upgrading to the latest
Locale::Maketext::Lexicon (0.32) resolves this?

-jesse


Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.


rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

Right. But if nobody tells us what the error is or even that they’re
getting an error, then we’re never, ever going to fix it. Versions with
_ in them are development versions and shouldn’t be automatically
installed. But if you can send a stacktrace of the error, we’d really
appreciate it.

Sorry I meant I didn’t take down the error and trace since it was so readily
reproduceable at the time. I’ve attached crash.txt with the results. This
happens with both 0.93_3 and _4, seemingly no matter what search I try
(specifically this crash was searching for tickets owned by me, no other
parameters).

I haven’t noticed this one, but 0.92 did fix important issues relative
to 0.88. Can you upgrade to 0.92 and send us the query that RT generates
to find tickets that you own? That’s how we can trace down the issue you
report.

I’ve attached search_order.txt to demonstrate. It looks like it’s related
to the SELECT DISTINCT change made in 0.89_01

BTW (saw your later emails) this is all with Locale::Maketext::Lexicon 0.32

BTW #2 cpan2rpm didn’t find an author in searchbuilder 0.93_4

-=| Ben

crash.txt (822 Bytes)

search_order.txt (6.61 KB)

I just had to find the bug… at least this way I’m now more familiar with
RT’s internal workings :slight_smile: (4 hours later)

The offending code is line 432 (RT 3.0.6) of lib/RT/Tickets_Overlay.pm:

$d->Set($value);

I set it to

$d->Set( Format => ‘ISO’, Value => $value);

and that appears to have done the trick. However, that seems like a silly
step - take an ISO date, create a date object, set it according to a known
ISO value, and then use the result date object’s ISO value as the final
input… unless the input format should be ‘unknown’ and you’re trying to
guarantee an ISO date from the original $value? Anyway, I’m sure you know
what to do now, but can you let me know the final verdict on what’s
supposed to be there so I can patch my version properly? Thanks!

-=| Ben