Professional Web Applications Themes

Halting a SELECT statement - MySQL

G'Day, I've Googled away, and read through doentation, 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....

  1. #1

    Default Halting a SELECT statement

    G'Day,

    I've Googled away, and read through doentation, 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.

    addinall Guest

  2. #2

    Default Re: Halting a SELECT statement

    > I was asked last night if it was possible to stop a SELECT statement 

    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 Guest

  3. #3

    Default Re: Halting a SELECT statement


    Willem Bogaerts wrote: 
    >
    > 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[/ref]

    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.



     

    addinall Guest

  4. #4

    Default Re: Halting a SELECT statement

    "addinall" <org> wrote: 
     
    >>
    >> 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[/ref]
    >
    > 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.[/ref]

    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 Guest

  5. #5

    Default Re: Halting a SELECT statement


    Axel Schwenke wrote: 

    > >
    > > 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.[/ref]
    >
    > 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.[/ref]

    $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.


     

    $sth->(finish);
     

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

    Cheers,
    Mark.
     

    addinall Guest

Similar Threads

  1. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  2. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  3. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  4. possible to run a select with an if statement in it?
    By Jim in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 17th, 04:48 PM
  5. Select Statement Help Please
    By Bob in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 14th, 02:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139