G'Day,

I've Googled away, and read through documentation, so I'm not
sure ifthis is possible.

I was asked last night if it was possible to stop a SELECT statement
whilst it is executing.

SELECT *
FROM VERY_BIG_TABLE ;

Wrapping an ALARM around it with a timeout is all I can think
of. Any nicer ways anyone knows?

Cheers,
Mark.

> I was asked last night if it was possible to stop a SELECT statement
> whilst it is executing.
>
> SELECT *
> FROM VERY_BIG_TABLE ;

You can use SHOW PROCESSLIST to see what thread number the query has and
kill that thread:
http://dev.mysql.com/doc/refman/4.1/en/kill.html

--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
> > I was asked last night if it was possible to stop a SELECT statement
> > whilst it is executing.
> >
> > SELECT *
> > FROM VERY_BIG_TABLE ;
>
> You can use SHOW PROCESSLIST to see what thread number the query has and
> kill that thread:
> http://dev.mysql.com/doc/refman/4.1/en/kill.html

Thank you for that. I'm unsure if I can use it. It relies
on a DBA tracking transactions I think. In my instance, say

# blah blah blah....

$sql = "SELECT * from VERY_LARGE_TABLE" ;
$sth = $dbh->prepare($sql);
$sth->execute($sql)
or die_with_help( $language->maketext("SELECT failed 2: "
.. $sth->errstr ), MAJOR_ERROR ) ;

# chug chug chug.....

Now without a DBA or a SysAdmin looking at this thread,
how can a user terminate this request at will?ie: "taking to long, hit
the big red STOP
button on the web page!

I can wrap an ALARM around it, and call a timeout.
I was wondering if a nicer AUTOMATED way was possible.

I can add start_row and num_rows onto the select statement.
I can't seem to find a way of doing a pretty timeout without
trashing the connection.

But again, thank you for your response.

Mark Addinall.




>
> --
> Willem Bogaerts
>
> Application smith
> Kratz B.V.
> http://www.kratz.nl/

"addinall" <EMAIL REMOVED> wrote:
> Willem Bogaerts wrote:

>> > I was asked last night if it was possible to stop a SELECT statement
>> > whilst it is executing.
>> >
>> > SELECT *
>> > FROM VERY_BIG_TABLE ;
>>
>> You can use SHOW PROCESSLIST to see what thread number the query has and
>> kill that thread:
>> http://dev.mysql.com/doc/refman/4.1/en/kill.html
>
> Thank you for that. I'm unsure if I can use it. It relies
> on a DBA tracking transactions I think. In my instance, say
>
> $sql = "SELECT * from VERY_LARGE_TABLE" ;
> $sth = $dbh->prepare($sql);
> $sth->execute($sql)
> or die_with_help( $language->maketext("SELECT failed 2: "
> . $sth->errstr ), MAJOR_ERROR ) ;
>
> # chug chug chug.....
>
> Now without a DBA or a SysAdmin looking at this thread,
> how can a user terminate this request at will?
>
> I can wrap an ALARM around it, and call a timeout.

That wouldn't do the trick. MySQL uses a client-server architecture.
You can use alarm() to wake the client from it's blocking read() but
the server will still be running the query and only finally find out
that the client has gone away.

Even when you interrupt the client, this database connection will be
unusable unless the server has finished the query.

What you can do: in the signalhandler for alarm() find out the thread
id of your client (from $dbh->{'mysql_thread_id'}), connect to the
server using a separate connection and then KILL the query.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> "addinall" <EMAIL REMOVED> wrote:
> > Willem Bogaerts wrote:
>
> >> > I was asked last night if it was possible to stop a SELECT statement
> >> > whilst it is executing.
> >> >
> >> > SELECT *
> >> > FROM VERY_BIG_TABLE ;
> >>
> >> You can use SHOW PROCESSLIST to see what thread number the query has and
> >> kill that thread:
> >> http://dev.mysql.com/doc/refman/4.1/en/kill.html
> >
> > Thank you for that. I'm unsure if I can use it. It relies
> > on a DBA tracking transactions I think. In my instance, say
> >
> > $sql = "SELECT * from VERY_LARGE_TABLE" ;
> > $sth = $dbh->prepare($sql);
> > $sth->execute($sql)
> > or die_with_help( $language->maketext("SELECT failed 2: "
> > . $sth->errstr ), MAJOR_ERROR ) ;
> >
> > # chug chug chug.....
> >
> > Now without a DBA or a SysAdmin looking at this thread,
> > how can a user terminate this request at will?
> >
> > I can wrap an ALARM around it, and call a timeout.
>
> That wouldn't do the trick. MySQL uses a client-server architecture.
> You can use alarm() to wake the client from it's blocking read() but
> the server will still be running the query and only finally find out
> that the client has gone away.

$sth=$dbh->prepare($sql);
eval
{
local $SIG{ALRM} = sub {die "sql TIMEOUT\n"};
alarm 90;
my $response = $sth->execute or die " $sth->errstr";
[...,<statements>]
alarm 0;
};

That seems to work. I was hoping for a human interaction
die, rather than an arbitary timeout.



>
> Even when you interrupt the client, this database connection will be
> unusable unless the server has finished the query.

$sth->(finish);

>
> What you can do: in the signalhandler for alarm() find out the thread
> id of your client (from $dbh->{'mysql_thread_id'}), connect to the
> server using a separate connection and then KILL the query.

Hmmmm, thanks for your response. It doesn't seem that easy!

Cheers,
Mark.

>
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/