Cancelling long running MySQL queries with PhP

[The Excession]

Platform Linux (CentOS 5.1 64 bit) Apache, MySQL 5.x, PHP 5.x

I'm a bit stuck.

I have a PhP script which queries a large table, sometime this can sometimes take a long time to complete.

It's not running using AJAX, but instead is using a 3rd party grid control which displays the result set in a table. I can't seem to find away to stop the backend query if the user clicks off the page that is running the grid control.

I'm wondering if anyone has any experience with stopping a backend query running once the user leaves a page, or perhaps even clicks a Cancel button. (I've used top and I can launch a long running query, close the browser and I still see the query running after the browser has closed)

Is is possible to store the current database connection that the user is running in a PhP Session variable such that the user can click a Cancel button, or I can detect the navigation is going to a new page and thus call an Async AJAX request to kill the query?


I'm a bit stumped really.

[The Excession]

Any takers?

I'm still a bit stuck on this one.

I've made huge progress on shortening the query time, in fact I've been at this solidly for the last 18 hours.

At about 2am this morning I finally discovered the joys of using tempory tables, my God did that speed things up a lot. I just copied a large block of records (about 50,000 out of 50,000,000) into a temp table.

This took about 2 seconds to complete and then I run my real query on that subset in the temp table, knowing full well that there is no way a user is going to scroll through 50K records

But then

At the last hurdle, the light in sight, I run my real SQL query that uses table alias' - oh joy! MySQL doesn't support alias' on temporary tables. That'll be me right back to square one.

Ho Hum.

I just would like to be able to stop a background query on a dead connection.
Even restarting httpd doesn't do it, [alan partridge] smell my top [/alan partridge] after service httpd restart!

top - 16:44:29 up 3 days, 23:34,  4 users,  load average: 4.01, 4.52, 3.77
Tasks: 126 total,   3 running, 123 sleeping,   0 stopped,   0 zombie
Cpu(s): 71.7%us,  3.7%sy,  0.0%ni, 22.6%id,  0.1%wa,  0.3%hi,  1.7%si,  0.0%st
Mem:  32962188k total, 22254652k used, 10707536k free,   243208k buffers
Swap:  2031608k total,        0k used,  2031608k free,  3372180k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                
13795 mysql     15   0 30.7g  16g  21m S  118 53.6 488:08.38 mysqld  

[the kack} drool drool drool drool Look at the RAM [/the knack]

So, just before you all shout 'Use PostgreSQL' I haven't got time for the port and learning curve.

Did I mention I hate XML ('oh I hear you cry, we know you hate XML'), well I bloody well hate middle tier database apps more.

It's just my lack of experience (sorry, exposure) I know, but right about now I'm ready for

• pack bag
• book plane ticket
• cd /
• rm -f *
• shutdown -blowup now
• house keys through the letter box
• [mark_the_sparrow] see yah bye... [/mark_the_sparrow]

[B19GRR]

Can't help you mate, but if it makes you feel any better, I hate XML too! Fecking nasty inflexible POS!

Solidarity bro'

Cheers,
Rob

[JamieBeeston]

mysqladmin -u username -ppassword processlist

parse this and search for something with a long execution time

mysqladmin -u username -ppassword kill pidofquery

i'm sure you can code this..

[The Excession]

JamieBeeston said:

mysqladmin -u username -ppassword processlist

parse this and search for something with a long execution time

mysqladmin -u username -ppassword kill pidofquery

i'm sure you can code this..

Thanks Jamie,

That certainly looks juicy!

[LivinLaVidaLotus]

You can actually kill it without having to resort to mysqladmin, if you open a connection with root level privs you can kill a process id:

http://dev.mysql.com/doc/refman/5.0/en/kill.html

assuming you're using the standard PHP mySQL lib:

http://uk.php.net/manual/en/function.mysql-thread-...

or for mySQLi:

http://uk.php.net/manual/en/mysqli.thread-id.php

Not sure if the various DB abstractions like PDO etc. can get the process ID, in case you're using them (I always tend to, but have never had to grab the PID)

Bobs your mothers brother

[JamieBeeston]

LivinLaVidaLotus said:

You can actually kill it without having to resort to mysqladmin, if you open a connection with root level privs you can kill a process id

Aye, phpMyAdmin allows pids to be killed via it in such a fashion.

[The Excession]

Thanks for all that info.

I'll take a look tomorrow.
Right now my Internet connection is stalling left right and centre (must be those kids home from university updating their iPods), so I can't reliably try anything at the moment.

Plus, I'm shattered.

Thanks again