Bugzilla will be upgraded to version 5.0 on a still to be determined date in the near future. The original upgrade date has been delayed.
Bug 590893 - Use DBIx::Timeout to kill off long running queries so that they do not back up the database
Use DBIx::Timeout to kill off long running queries so that they do not back u...
Status: CLOSED CURRENTRELEASE
Product: Bugzilla
Classification: Community
Component: Query/Bug List (Show other bugs)
3.6
All Linux
low Severity medium (vote)
: ---
: ---
Assigned To: Kevin Baker
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2010-05-10 17:47 EDT by David Lawrence
Modified: 2014-12-01 18:09 EST (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2011-11-02 20:37:55 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Patch to add DBIx::Timeout support for long running bug queries (v1) (12.60 KB, patch)
2010-05-10 17:47 EDT, David Lawrence
no flags Details | Diff
Patch to add DBIx::Timeout support for long running bug queries (v2) (12.79 KB, patch)
2010-05-11 17:33 EDT, David Lawrence
nelhawar: review+
dkl: review? (tfu)
Details | Diff

  None (edit)
Description David Lawrence 2010-05-10 17:47:27 EDT
Created attachment 412976 [details]
Patch to add DBIx::Timeout support for long running bug queries (v1)

Description of problem:
Currently some queries being executed from buglist.cgi, Bug.search, or bugzilla.runQuery sometimes take a large amount of time to execute causing the database to get backed up. The backup eventually causes connections to be denied and the admin needs to restart the db to get everything back to normal.

The CPAN DBIx::Timeout module tries to fix that by forking off and then killing the parent process if a specified amount of time has passed. The benefit of doing it this way is we can then send a more human friendly error message to the client as well as send an email to the BZ admins with the params and SQL generated. The admins can use the information to figure out how to optimize the database for the future.

Attaching a patch that adds DBIx::Timeout support as well as a config param to set the amount of seconds for the timeout value.
Comment 1 David Lawrence 2010-05-10 17:56:32 EDT
Changes were also needed to the redhat/bugs-functions.mysql stored procedures definitions as the functions needed to run as the invoker and not the definer for the processes to be able to be killed properly.

So I added "SQL SECURITY INVOKER" to the CREATE FUNCTION statement to do this. Now when for example AliasList(bugs.bug_id) runs, it shows up as the 'bugs' user in the mysql process list instead of 'root'. If it is 'root' then the 'bugs' user cannot kills its own process if AliasList() is running at the moment.

I will have the eng-systems admins re create the functions to make this change take effect before rolling out this feature.

Dave
Comment 2 Kevin Baker 2010-05-11 10:00:14 EDT
Hi,

Dave, one good idea I heard recently was to do this upon error
 1) generate a unqiue hash
 2) write the hash to the log
 3) email the hash plus stack trace info to bugzilla-owner
 4) put up a nice screen for the user informing them what happened with the hash

The hash becomes the thread through which you can pull the log and the email together. Makes sense to me.


Another thing, I thought we discussed just putting a Data::Dumper of the error report into the email so that we can slurp it up easily later on? 

Kev
Comment 3 David Lawrence 2010-05-11 16:15:58 EDT
(In reply to comment #2)
> Dave, one good idea I heard recently was to do this upon error
>  1) generate a unqiue hash
>  2) write the hash to the log
>  3) email the hash plus stack trace info to bugzilla-owner
>  4) put up a nice screen for the user informing them what happened with the
> hash
> 
> The hash becomes the thread through which you can pull the log and the email
> together. Makes sense to me.

Just to be clear, you are not talking about storing the hash in the database, just include it in the email that is mailed, and also in the bz.log file along side the timestamp when the email was sent out? I can simply reuse Bugzilla's token generating code to get the value to include.

> Another thing, I thought we discussed just putting a Data::Dumper of the error
> report into the email so that we can slurp it up easily later on? 

I am doing something similar to this already in the latest patch. I am including the SQL that was generated, the query string used to generate the search (same for the XMLRPC calls), a stack trace (so we know how the call came in) and some other date such as hostname, pid, etc. I used some of the same format as the current Issue Tracker error emails. I will attach a sample email I have from my testing to this bug.

Dave
Comment 5 Kevin Baker 2010-05-11 16:30:02 EDT
(In reply to comment #3)

> Just to be clear, you are not talking about storing the hash in the database,
> just include it in the email that is mailed, and also in the bz.log file along
> side the timestamp when the email was sent out? I can simply reuse Bugzilla's
> token generating code to get the value to include.

Correct. Don't store it in the database. Just write a log message with the hash (unique key) and write an email with the hash. So if you need to hunt through the logs you have the hash-cookie trail laid out for you. You can even show the hash to the user on the web ui so you can link it together too.

> I am doing something similar to this already in the latest patch. I am
> including the SQL that was generated, the query string used to generate the
> search (same for the XMLRPC calls), a stack trace (so we know how the call came
> in) and some other date such as hostname, pid, etc. I used some of the same
> format as the current Issue Tracker error emails. I will attach a sample email
> I have from my testing to this bug.

Yep, as long as it is all in some dump format that can easily be slurped into a perl script for processing. Perhaps even attach it as a Storable blob? Perhaps YAML is another alternative. I'm just trying to save you time on the other end when you come to debug an issue. Something like this use -case

- app blows up
 - writes log message w/ hash
 - sends data to bugzilla-owner
- dkl sees email
  - runs script to slurp up error message (perhaps it can even reach out to pull in the log messages) for diagnosis
  - dkl solves issue
- sit back smoke cigars
Comment 6 David Lawrence 2010-05-11 17:33:29 EDT
Created attachment 413270 [details]
Patch to add DBIx::Timeout support for long running bug queries (v2)

New patch which adds a hash key to the log output as well as the email sent to be able to match them together.

Dave
Comment 7 Noura El hawary 2010-05-12 08:30:30 EDT
Hi Dave,

the package DBIx-Timeout is not packaged, I asked release eng to add it to dist-5E-eso and I will package it.

Thanks,
Noura
Comment 8 David Lawrence 2010-05-12 11:02:36 EDT
(In reply to comment #7)
> Hi Dave,
> 
> the package DBIx-Timeout is not packaged, I asked release eng to add it to
> dist-5E-eso and I will package it.
> 
> Thanks,
> Noura    

Yeah I have it built in Brew but not sent to eng-systems yet to add to the eso yum repo.

https://brewweb.devel.redhat.com/taskinfo?taskID=2425899

It is however installed on bz-web1-test already for testing purposes.

Dave
Comment 9 Noura El hawary 2010-05-13 08:14:50 EDT
Comment on attachment 413270 [details]
Patch to add DBIx::Timeout support for long running bug queries (v2)

looks good Dave, seems to be working fine in from the UI and the xmlrpc interfaces, also I sent request to eng-systems and rel-engineering to add the new package to the yum eso and bz3 repos.

The patch and db changes are applied to bz-web1

Thanks,
Noura
Comment 10 David Lawrence 2010-08-25 17:40:03 EDT
Red Hat has now upgraded to Bugzilla 3.6 and this bug will now be reassigned to that version. It would be helpful to the Bugzilla Development Team if this bug is verified to still be an issue with the latest version. If it is no longer an issue, then feel free to close, otherwise please comment that it is still a problem and we will try to address the issue as soon as we can.

Thanks
Bugzilla Development Team
Comment 13 Simon Green 2011-11-02 02:26:57 EDT
I assume this can be closed as this functionality is now live?

  -- simon
Comment 14 Meethune Bhowmick 2011-11-02 08:26:14 EDT
Yeah this was implemented long ago.

meethune

Note You need to log in before you can comment on or make changes to this bug.