Why doesn"t mySQL stop a query when the browser tab is closedL

------=_NextPart_000_0091_01C9E3AD.D4184EE0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I just noticed a horrible thing.

I have a query (report) that can take 15 minutes or more to generate with
mySQL. We have > 500 Million rows. This used to be done in real time when we
had less rows, but recently we got a big dump of data that shot it up.

So, noticing via myTop the query taking so long, I closed my web page tab.
The query did NOT go away! WTF? So mysqld continued to peg the CPU at 75% to
135% (yes, top shows that if you have quad cpus. *sigh*)

Is there some way to force this to work sanely? Some php.ini or my.cnf file
that has a setting to abort queries when the web page has gone away?

Not sure which mailing list this belongs on so I'll post to both PHP and
mySQL. Although it feels this is a PHP problem as it should know that the
Apache thread went away and therefore close the mySQL connection and kill
the query. Conversely, mysql should know that it's connection (via PHP) went
away and should equally abort. So you're both wrong! :)

------=_NextPart_000_0091_01C9E3AD.D4184EE0--

RE: Why doesn"t mySQL stop a query when the browser tab is closedL

[snip]
I just noticed a horrible thing.=20
[/snip]

Keep in mind that the query event is server side and is not tied to the
browser (client side) once it has begun because of the statelessness of
the connection. You would have to have some sort of onClose() event from
the browser that would trigger a query cancellation.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Why doesn"t mySQL stop a query when the browser tab is closedL

>-----Original Message-----
>From: Jay Blanchard [mailto:jblanchard@pocket.com]
>Sent: Wednesday, June 03, 2009 8:46 AM
>To: Daevid Vincent; mysql@lists.mysql.com
>Subject: RE: Why doesn't mySQL stop a query when the browser tab is
>closedL
>
>[snip]
>I just noticed a horrible thing.
>[/snip]
>
>Keep in mind that the query event is server side and is not tied to the
>browser (client side) once it has begun because of the statelessness of
>the connection. You would have to have some sort of onClose() event from
>the browser that would trigger a query cancellation.
>
[JS] Going beyond that, the browser is at several removes from the MySQL
server. Typically the browser talks to the web server, then the web server
runs some application code (PHP or whatever), and then the application code
talks to the MySQL server. The only part of this chain that "knows" what the
MySQL server is doing is the last bit, the application code, which is
typically waiting for a response.

Getting back to the user, HTTP itself is a stateless protocol. That means
the web server has no way of knowing if the user, the browser, or even the
user's computer is still there; it also doesn't really know what the user
last did (it's up to the application code to remember that somehow).

In order for an end user to cancel a query, there would have to be some way
for the user to tell the browser to tell the web server to tell the
application code to tell the MySQL server to stop. I'm pretty sure you could
create a "tired of waiting" button for the user, but I haven't done it
myself.

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com





>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Why doesn"t mySQL stop a query when the browser tab is closedL

Hi Daevid,

You can always stop the query by running

SHOW PROCESSLIST;

from the command line or your MySql Admin tool. The above command will
show you all of the queries that are currently running along with
their PID# and state. Find the query your want to stop, and run the
following command
KILL #; (where # is the process id)

As far as the relationship between killing queries and the browser, I
think the previous comments pretty much summed it up.


On Wed, Jun 3, 2009 at 10:22 AM, Jerry Schwartz
wrote:
>
>
>>-----Original Message-----
>>From: Jay Blanchard [mailto:jblanchard@pocket.com]
>>Sent: Wednesday, June 03, 2009 8:46 AM
>>To: Daevid Vincent; mysql@lists.mysql.com
>>Subject: RE: Why doesn't mySQL stop a query when the browser tab is
>>closedL
>>
>>[snip]
>>I just noticed a horrible thing.
>>[/snip]
>>
>>Keep in mind that the query event is server side and is not tied to the
>>browser (client side) once it has begun because of the statelessness of
>>the connection. You would have to have some sort of onClose() event from
>>the browser that would trigger a query cancellation.
>>
> [JS] Going beyond that, the browser is at several removes from the MySQL
> server. Typically the browser talks to the web server, then the web serve=
r
> runs some application code (PHP or whatever), and then the application co=
de
> talks to the MySQL server. The only part of this chain that "knows" what =
the
> MySQL server is doing is the last bit, the application code, which is
> typically waiting for a response.
>
> Getting back to the user, HTTP itself is a stateless protocol. That means
> the web server has no way of knowing if the user, the browser, or even th=
e
> user's computer is still there; it also doesn't really know what the user
> last did (it's up to the application code to remember that somehow).
>
> In order for an end user to cancel a query, there would have to be some w=
ay
> for the user to tell the browser to tell the web server to tell the
> application code to tell the MySQL server to stop. I'm pretty sure you co=
uld
> create a "tired of waiting" button for the user, but I haven't done it
> myself.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
>
>
>
>
>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djschwartz@the=
-
>>infoshop.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Ddsteplight@gm=
ail.com
>
>



--=20
A: It reverses the normal flow of conversation.
Q: What's wrong with top-posting?
A: Top-posting.
Q: What's the biggest scourge on plain text email discussions?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Why doesn"t mySQL stop a query when the browser tab is closedL

We have a "NOC" that displays this sort of thing and shows myTop in six
windows for DEV/TEST/PROD and Master/Slave for
each.(http://www.daevid.com/content/examples/snippets.php scroll down to
"Automatic Monitoring of remote servers" for a handy script)

So we do monitor this and will kill long running queries by hand. I was
hoping for something a little more automated however. It just seems odd =
to
me that PHP does a mysql_connect and gets a resource ID. Then it uses =
that
socket (or whatever it is) to do subsequent queries. Mysql has to know =
about
it too in order for it to send back results to the same
resource/handle/socket/whatever. So either PHP should do some cleanup, =
which
I assume it does when the page goes away, otherwise PHP would leak =
memory
like a sieve all over the place as pages are closed/aborted, OR mySQL =
should
get a signal that, "hey, your handle to return your data on just STB, so
stop what you're doing please".

On a related note then, is there a way to set a my.cnf setting so that
queries that are going for longer than X seconds are re-niced or =
something
to not bog down the system? Ideally I'd assume you'd want each query to =
"peg
the CPU" so that they're in and out quickly and the user can get on with
their life. But these long running ones at some point X are then taking =
over
the entire server with 135% CPU usage and killing the experience for any
other user on the web site -- and for a duration too!
=20

> -----Original Message-----
> From: Darryle Steplight [mailto:dsteplight@gmail.com]=20
> Sent: Wednesday, June 03, 2009 8:41 AM
> To: Jerry Schwartz
> Cc: Jay Blanchard; Daevid Vincent; mysql@lists.mysql.com
> Subject: Re: Why doesn't mySQL stop a query when the browser=20
> tab is closedL
>=20
> Hi Daevid,
>=20
> You can always stop the query by running
>=20
> SHOW PROCESSLIST;
>=20
> from the command line or your MySql Admin tool. The above command will
> show you all of the queries that are currently running along with
> their PID# and state. Find the query your want to stop, and run the
> following command
> KILL #; (where # is the process id)
>=20
> As far as the relationship between killing queries and the browser, I
> think the previous comments pretty much summed it up.
>=20
>=20
> On Wed, Jun 3, 2009 at 10:22 AM, Jerry Schwartz
> wrote:
> >
> >
> >>-----Original Message-----
> >>From: Jay Blanchard [mailto:jblanchard@pocket.com]
> >>Sent: Wednesday, June 03, 2009 8:46 AM
> >>To: Daevid Vincent; mysql@lists.mysql.com
> >>Subject: RE: Why doesn't mySQL stop a query when the browser tab is
> >>closedL
> >>
> >>[snip]
> >>I just noticed a horrible thing.
> >>[/snip]
> >>
> >>Keep in mind that the query event is server side and is not=20
> tied to the
> >>browser (client side) once it has begun because of the=20
> statelessness of
> >>the connection. You would have to have some sort of=20
> onClose() event from
> >>the browser that would trigger a query cancellation.
> >>
> > [JS] Going beyond that, the browser is at several removes=20
> from the MySQL
> > server. Typically the browser talks to the web server, then=20
> the web server
> > runs some application code (PHP or whatever), and then the=20
> application code
> > talks to the MySQL server. The only part of this chain that=20
> "knows" what the
> > MySQL server is doing is the last bit, the application=20
> code, which is
> > typically waiting for a response.
> >
> > Getting back to the user, HTTP itself is a stateless=20
> protocol. That means
> > the web server has no way of knowing if the user, the=20
> browser, or even the
> > user's computer is still there; it also doesn't really know=20
> what the user
> > last did (it's up to the application code to remember that somehow).
> >
> > In order for an end user to cancel a query, there would=20
> have to be some way
> > for the user to tell the browser to tell the web server to tell the
> > application code to tell the MySQL server to stop. I'm=20
> pretty sure you could
> > create a "tired of waiting" button for the user, but I=20
> haven't done it
> > myself.
> >
> > Regards,
> >
> > Jerry Schwartz
> > The Infoshop by Global Information Incorporated
> > 195 Farmington Ave.
> > Farmington, CT 06032
> >
> > 860.674.8796 / FAX: 860.674.8341
> >
> > www.the-infoshop.com
> >
> >
> >
> >
> >
> >>--
> >>MySQL General Mailing List
> >>For list archives: http://lists.mysql.com/mysql
> >>To unsubscribe: =A0 =
=A0http://lists.mysql.com/mysql?unsub=3Djschwartz@the-
> >>infoshop.com
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: =A0 =A0
> http://lists.mysql.com/mysql?unsub=3Ddsteplight@gmail.com
> >
> >
>=20
>=20
>=20
> --=20
> A: It reverses the normal flow of conversation.
> Q: What's wrong with top-posting?
> A: Top-posting.
> Q: What's the biggest scourge on plain text email discussions?
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Why doesn"t mySQL stop a query when the browser tab is closedL

--_6c90f155-708d-4c4d-81c7-b680a8380024_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


my.cnf supports these timeout options
http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html
connect-timeout=3Dseconds

#innodb_lock_wait_timeout =3D 50
The timeout in seconds an InnoDB
transaction may wait for a row lock before giving up. The
default value is 50 seconds. A transaction that tries to
access a row that is locked by another
InnoDB transaction will hang for at most
this many seconds before issuing the following error:
=20

//there are also driver specific timeouts available for ODBC/JDBC drivers
HTH
Martin Gainty=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9
=20
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..




> From: daevid@daevid.com
> To: mysql@lists.mysql.com
> Subject: RE: Why doesn't mySQL stop a query when the browser tab is close=
dL
> Date: Wed=2C 3 Jun 2009 12:03:19 -0700
>=20
> We have a "NOC" that displays this sort of thing and shows myTop in six
> windows for DEV/TEST/PROD and Master/Slave for
> each.(http://www.daevid.com/content/examples/snippets.php scroll down to
> "Automatic Monitoring of remote servers" for a handy script)
>=20
> So we do monitor this and will kill long running queries by hand. I was
> hoping for something a little more automated however. It just seems odd t=
o
> me that PHP does a mysql_connect and gets a resource ID. Then it uses tha=
t
> socket (or whatever it is) to do subsequent queries. Mysql has to know ab=
out
> it too in order for it to send back results to the same
> resource/handle/socket/whatever. So either PHP should do some cleanup=2C =
which
> I assume it does when the page goes away=2C otherwise PHP would leak memo=
ry
> like a sieve all over the place as pages are closed/aborted=2C OR mySQL s=
hould
> get a signal that=2C "hey=2C your handle to return your data on just STB=
=2C so
> stop what you're doing please".
>=20
> On a related note then=2C is there a way to set a my.cnf setting so that
> queries that are going for longer than X seconds are re-niced or somethin=
g
> to not bog down the system? Ideally I'd assume you'd want each query to "=
peg
> the CPU" so that they're in and out quickly and the user can get on with
> their life. But these long running ones at some point X are then taking o=
ver
> the entire server with 135% CPU usage and killing the experience for any
> other user on the web site -- and for a duration too!
> =20
>=20
> > -----Original Message-----
> > From: Darryle Steplight [mailto:dsteplight@gmail.com]=20
> > Sent: Wednesday=2C June 03=2C 2009 8:41 AM
> > To: Jerry Schwartz
> > Cc: Jay Blanchard=3B Daevid Vincent=3B mysql@lists.mysql.com
> > Subject: Re: Why doesn't mySQL stop a query when the browser=20
> > tab is closedL
> >=20
> > Hi Daevid=2C
> >=20
> > You can always stop the query by running
> >=20
> > SHOW PROCESSLIST=3B
> >=20
> > from the command line or your MySql Admin tool. The above command will
> > show you all of the queries that are currently running along with
> > their PID# and state. Find the query your want to stop=2C and run the
> > following command
> > KILL #=3B (where # is the process id)
> >=20
> > As far as the relationship between killing queries and the browser=2C I
> > think the previous comments pretty much summed it up.
> >=20
> >=20
> > On Wed=2C Jun 3=2C 2009 at 10:22 AM=2C Jerry Schwartz
> > wrote:
> > >
> > >
> > >>-----Original Message-----
> > >>From: Jay Blanchard [mailto:jblanchard@pocket.com]
> > >>Sent: Wednesday=2C June 03=2C 2009 8:46 AM
> > >>To: Daevid Vincent=3B mysql@lists.mysql.com
> > >>Subject: RE: Why doesn't mySQL stop a query when the browser tab is
> > >>closedL
> > >>
> > >>[snip]
> > >>I just noticed a horrible thing.
> > >>[/snip]
> > >>
> > >>Keep in mind that the query event is server side and is not=20
> > tied to the
> > >>browser (client side) once it has begun because of the=20
> > statelessness of
> > >>the connection. You would have to have some sort of=20
> > onClose() event from
> > >>the browser that would trigger a query cancellation.
> > >>
> > > [JS] Going beyond that=2C the browser is at several removes=20
> > from the MySQL
> > > server. Typically the browser talks to the web server=2C then=20
> > the web server
> > > runs some application code (PHP or whatever)=2C and then the=20
> > application code
> > > talks to the MySQL server. The only part of this chain that=20
> > "knows" what the
> > > MySQL server is doing is the last bit=2C the application=20
> > code=2C which is
> > > typically waiting for a response.
> > >
> > > Getting back to the user=2C HTTP itself is a stateless=20
> > protocol. That means
> > > the web server has no way of knowing if the user=2C the=20
> > browser=2C or even the
> > > user's computer is still there=3B it also doesn't really know=20
> > what the user
> > > last did (it's up to the application code to remember that somehow).
> > >
> > > In order for an end user to cancel a query=2C there would=20
> > have to be some way
> > > for the user to tell the browser to tell the web server to tell the
> > > application code to tell the MySQL server to stop. I'm=20
> > pretty sure you could
> > > create a "tired of waiting" button for the user=2C but I=20
> > haven't done it
> > > myself.
> > >
> > > Regards=2C
> > >
> > > Jerry Schwartz
> > > The Infoshop by Global Information Incorporated
> > > 195 Farmington Ave.
> > > Farmington=2C CT 06032
> > >
> > > 860.674.8796 / FAX: 860.674.8341
> > >
> > > www.the-infoshop.com
> > >
> > >
> > >
> > >
> > >
> > >>--
> > >>MySQL General Mailing List
> > >>For list archives: http://lists.mysql.com/mysql
> > >>To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djschwartz@the=
-
> > >>infoshop.com
> > >
> > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: =20
> > http://lists.mysql.com/mysql?unsub=3Ddsteplight@gmail.com
> > >
> > >
> >=20
> >=20
> >=20
> > --=20
> > A: It reverses the normal flow of conversation.
> > Q: What's wrong with top-posting?
> > A: Top-posting.
> > Q: What's the biggest scourge on plain text email discussions?
> >=20
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20

____________________________________________________________ _____
Hotmail=AE has ever-growing storage! Don=92t worry about storage limits.=20
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=3DTXT_T AGLM_WL_HM_Tuto=
rial_Storage_062009=

--_6c90f155-708d-4c4d-81c7-b680a8380024_--

Re: Why doesn"t mySQL stop a query when the browser tab is closedL

--0016e64c1ad0f9117d046b78b456
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

It just seems odd to me that PHP does a mysql_connect and gets a resource
ID. Then it uses that socket (or whatever it is) to do subsequent queries.
Mysql has to know about it too in order for it to send back results to the
same resource/handle/socket/whatever. So either PHP should do some
cleanup, which
I assume it does when the page goes away, otherwise PHP would leak memory
like a sieve all over the place as pages are closed/aborted.
PHP has no idea the browser left/aborted/died/etc. When you begin a request
that PHP handles, it begins processing the script, which in turn begins
processing whatever instructions the script has. At no point in time is it
aware if a "browser window" is waiting on its response, it simply spits it
response back to the HTTP server which may or may not have a client reading
the response to display to a user.

PHP's clean up is when the script ends processing. You can see this by
creating a PHP script that takes ages to complete (no mysql involved, maybe
just a loop and sleep statements that sends output to a text file) and
kicking it off by browsing to the page, then closing your browser and
tail'ing the output file, you will see it continuing to write to this file
until the loop is complete.

You can assume what you like about PHP, but that doesn't make it true.

-johnny


On Wed, Jun 3, 2009 at 2:03 PM, Daevid Vincent wrote:

> We have a "NOC" that displays this sort of thing and shows myTop in six
> windows for DEV/TEST/PROD and Master/Slave for
> each.(http://www.daevid.com/content/examples/snippets.php scroll down to
> "Automatic Monitoring of remote servers" for a handy script)
>
> So we do monitor this and will kill long running queries by hand. I was
> hoping for something a little more automated however. It just seems odd to
> me that PHP does a mysql_connect and gets a resource ID. Then it uses that
> socket (or whatever it is) to do subsequent queries. Mysql has to know
> about
> it too in order for it to send back results to the same
> resource/handle/socket/whatever. So either PHP should do some cleanup,
> which
> I assume it does when the page goes away, otherwise PHP would leak memory
> like a sieve all over the place as pages are closed/aborted, OR mySQL
> should
> get a signal that, "hey, your handle to return your data on just STB, so
> stop what you're doing please".
>
> On a related note then, is there a way to set a my.cnf setting so that
> queries that are going for longer than X seconds are re-niced or something
> to not bog down the system? Ideally I'd assume you'd want each query to
> "peg
> the CPU" so that they're in and out quickly and the user can get on with
> their life. But these long running ones at some point X are then taking
> over
> the entire server with 135% CPU usage and killing the experience for any
> other user on the web site -- and for a duration too!
>
>
> > -----Original Message-----
> > From: Darryle Steplight [mailto:dsteplight@gmail.com]
> > Sent: Wednesday, June 03, 2009 8:41 AM
> > To: Jerry Schwartz
> > Cc: Jay Blanchard; Daevid Vincent; mysql@lists.mysql.com
> > Subject: Re: Why doesn't mySQL stop a query when the browser
> > tab is closedL
> >
> > Hi Daevid,
> >
> > You can always stop the query by running
> >
> > SHOW PROCESSLIST;
> >
> > from the command line or your MySql Admin tool. The above command will
> > show you all of the queries that are currently running along with
> > their PID# and state. Find the query your want to stop, and run the
> > following command
> > KILL #; (where # is the process id)
> >
> > As far as the relationship between killing queries and the browser, I
> > think the previous comments pretty much summed it up.
> >
> >
> > On Wed, Jun 3, 2009 at 10:22 AM, Jerry Schwartz
> > wrote:
> > >
> > >
> > >>-----Original Message-----
> > >>From: Jay Blanchard [mailto:jblanchard@pocket.com]
> > >>Sent: Wednesday, June 03, 2009 8:46 AM
> > >>To: Daevid Vincent; mysql@lists.mysql.com
> > >>Subject: RE: Why doesn't mySQL stop a query when the browser tab is
> > >>closedL
> > >>
> > >>[snip]
> > >>I just noticed a horrible thing.
> > >>[/snip]
> > >>
> > >>Keep in mind that the query event is server side and is not
> > tied to the
> > >>browser (client side) once it has begun because of the
> > statelessness of
> > >>the connection. You would have to have some sort of
> > onClose() event from
> > >>the browser that would trigger a query cancellation.
> > >>
> > > [JS] Going beyond that, the browser is at several removes
> > from the MySQL
> > > server. Typically the browser talks to the web server, then
> > the web server
> > > runs some application code (PHP or whatever), and then the
> > application code
> > > talks to the MySQL server. The only part of this chain that
> > "knows" what the
> > > MySQL server is doing is the last bit, the application
> > code, which is
> > > typically waiting for a response.
> > >
> > > Getting back to the user, HTTP itself is a stateless
> > protocol. That means
> > > the web server has no way of knowing if the user, the
> > browser, or even the
> > > user's computer is still there; it also doesn't really know
> > what the user
> > > last did (it's up to the application code to remember that somehow).
> > >
> > > In order for an end user to cancel a query, there would
> > have to be some way
> > > for the user to tell the browser to tell the web server to tell the
> > > application code to tell the MySQL server to stop. I'm
> > pretty sure you could
> > > create a "tired of waiting" button for the user, but I
> > haven't done it
> > > myself.
> > >
> > > Regards,
> > >
> > > Jerry Schwartz
> > > The Infoshop by Global Information Incorporated
> > > 195 Farmington Ave.
> > > Farmington, CT 06032
> > >
> > > 860.674.8796 / FAX: 860.674.8341
> > >
> > > www.the-infoshop.com
> > >
> > >
> > >
> > >
> > >
> > >>--
> > >>MySQL General Mailing List
> > >>For list archives: http://lists.mysql.com/mysql
> > >>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
> > >>infoshop.com
> > >
> > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=dsteplight@gmail.com
> > >
> > >
> >
> >
> >
> > --
> > A: It reverses the normal flow of conversation.
> > Q: What's wrong with top-posting?
> > A: Top-posting.
> > Q: What's the biggest scourge on plain text email discussions?
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e64c1ad0f9117d046b78b456--

Re: Why doesn"t mySQL stop a query when the browser tab is closedL

PHP will notice if the browser has disconnected when it tries to write
data. If it detects the browser has disconnected it will stop script
execution. You can control what php should do in the event of a
disconnect with the ignore_user_abort() function. See the connection
handling page for more details:

http://us3.php.net/manual/en/features.connection-handling.ph p

On Wed, Jun 3, 2009 at 2:54 PM, Johnny Withers wrote:
> It just seems odd to me that PHP does a mysql_connect and gets a resource
> ID. Then it uses that socket (or whatever it is) to do subsequent queries=
..
> Mysql has to know about it too in order for it to send back results to th=
e
> same resource/handle/socket/whatever. So either PHP should do some
> cleanup, which
> I assume it does when the page goes away, otherwise PHP would leak memory
> like a sieve all over the place as pages are closed/aborted.
> PHP has no idea the browser left/aborted/died/etc. When you begin a reque=
st
> that PHP handles, it begins processing the script, which in turn begins
> processing whatever instructions the script has. At no point in time is i=
t
> aware if a "browser window" is waiting on its response, it simply spits i=
t
> response back to the HTTP server which may or may not have a client readi=
ng
> the response to display to a user.
>
> PHP's clean up is when the script ends processing. You can see this by
> creating a PHP script that takes ages to complete (no mysql involved, may=
be
> just a loop and sleep statements that sends output to a text file) and
> kicking it off by browsing to the page, then closing your browser and
> tail'ing the output file, you will see it continuing to write to this fil=
e
> until the loop is complete.
>
> You can assume what you like about PHP, but that doesn't make it true.
>
> -johnny
>
>
> On Wed, Jun 3, 2009 at 2:03 PM, Daevid Vincent wrote:
>
>> We have a "NOC" that displays this sort of thing and shows myTop in six
>> windows for DEV/TEST/PROD and Master/Slave for
>> each.(http://www.daevid.com/content/examples/snippets.php scroll down to
>> "Automatic Monitoring of remote servers" for a handy script)
>>
>> So we do monitor this and will kill long running queries by hand. I was
>> hoping for something a little more automated however. It just seems odd =
to
>> me that PHP does a mysql_connect and gets a resource ID. Then it uses th=
at
>> socket (or whatever it is) to do subsequent queries. Mysql has to know
>> about
>> it too in order for it to send back results to the same
>> resource/handle/socket/whatever. So either PHP should do some cleanup,
>> which
>> I assume it does when the page goes away, otherwise PHP would leak memor=
y
>> like a sieve all over the place as pages are closed/aborted, OR mySQL
>> should
>> get a signal that, "hey, your handle to return your data on just STB, so
>> stop what you're doing please".
>>
>> On a related note then, is there a way to set a my.cnf setting so that
>> queries that are going for longer than X seconds are re-niced or somethi=
ng
>> to not bog down the system? Ideally I'd assume you'd want each query to
>> "peg
>> the CPU" so that they're in and out quickly and the user can get on with
>> their life. But these long running ones at some point X are then taking
>> over
>> the entire server with 135% CPU usage and killing the experience for any
>> other user on the web site -- and for a duration too!
>>
>>
>> > -----Original Message-----
>> > From: Darryle Steplight [mailto:dsteplight@gmail.com]
>> > Sent: Wednesday, June 03, 2009 8:41 AM
>> > To: Jerry Schwartz
>> > Cc: Jay Blanchard; Daevid Vincent; mysql@lists.mysql.com
>> > Subject: Re: Why doesn't mySQL stop a query when the browser
>> > tab is closedL
>> >
>> > Hi Daevid,
>> >
>> > You can always stop the query by running
>> >
>> > SHOW PROCESSLIST;
>> >
>> > from the command line or your MySql Admin tool. The above command will
>> > show you all of the queries that are currently running along with
>> > their PID# and state. Find the query your want to stop, and run the
>> > following command
>> > KILL #; (where # is the process id)
>> >
>> > As far as the relationship between killing queries and the browser, I
>> > think the previous comments pretty much summed it up.
>> >
>> >
>> > On Wed, Jun 3, 2009 at 10:22 AM, Jerry Schwartz
>> > wrote:
>> > >
>> > >
>> > >>-----Original Message-----
>> > >>From: Jay Blanchard [mailto:jblanchard@pocket.com]
>> > >>Sent: Wednesday, June 03, 2009 8:46 AM
>> > >>To: Daevid Vincent; mysql@lists.mysql.com
>> > >>Subject: RE: Why doesn't mySQL stop a query when the browser tab is
>> > >>closedL
>> > >>
>> > >>[snip]
>> > >>I just noticed a horrible thing.
>> > >>[/snip]
>> > >>
>> > >>Keep in mind that the query event is server side and is not
>> > tied to the
>> > >>browser (client side) once it has begun because of the
>> > statelessness of
>> > >>the connection. You would have to have some sort of
>> > onClose() event from
>> > >>the browser that would trigger a query cancellation.
>> > >>
>> > > [JS] Going beyond that, the browser is at several removes
>> > from the MySQL
>> > > server. Typically the browser talks to the web server, then
>> > the web server
>> > > runs some application code (PHP or whatever), and then the
>> > application code
>> > > talks to the MySQL server. The only part of this chain that
>> > "knows" what the
>> > > MySQL server is doing is the last bit, the application
>> > code, which is
>> > > typically waiting for a response.
>> > >
>> > > Getting back to the user, HTTP itself is a stateless
>> > protocol. That means
>> > > the web server has no way of knowing if the user, the
>> > browser, or even the
>> > > user's computer is still there; it also doesn't really know
>> > what the user
>> > > last did (it's up to the application code to remember that somehow).
>> > >
>> > > In order for an end user to cancel a query, there would
>> > have to be some way
>> > > for the user to tell the browser to tell the web server to tell the
>> > > application code to tell the MySQL server to stop. I'm
>> > pretty sure you could
>> > > create a "tired of waiting" button for the user, but I
>> > haven't done it
>> > > myself.
>> > >
>> > > Regards,
>> > >
>> > > Jerry Schwartz
>> > > The Infoshop by Global Information Incorporated
>> > > 195 Farmington Ave.
>> > > Farmington, CT 06032
>> > >
>> > > 860.674.8796 / FAX: 860.674.8341
>> > >
>> > > www.the-infoshop.com
>> > >
>> > >
>> > >
>> > >
>> > >
>> > >>--
>> > >>MySQL General Mailing List
>> > >>For list archives: http://lists.mysql.com/mysql
>> > >>To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djschwart=
z@the-
>> > >>infoshop.com
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > --
>> > > MySQL General Mailing List
>> > > For list archives: http://lists.mysql.com/mysql
>> > > To unsubscribe:
>> > http://lists.mysql.com/mysql?unsub=3Ddsteplight@gmail.com
>> > >
>> > >
>> >
>> >
>> >
>> > --
>> > A: It reverses the normal flow of conversation.
>> > Q: What's wrong with top-posting?
>> > A: Top-posting.
>> > Q: What's the biggest scourge on plain text email discussions?
>> >
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixel=
ated.net
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>



--=20
Eric Bergen
eric.bergen@gmail.com
http://www.ebergen.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Why doesn"t mySQL stop a query when the browser tab is closedL

I'm not sure closing the browser window sends the RST packet back to
the server, if it does, that's great! However, if php is waiting on
mysql to return a result, it wouldn't notice this anyway until mysql
finished processing and returned control back to the script. I could
be completly off-base here, I have no way to test this.

On Saturday, July 25, 2009, Eric Bergen wrote:
> PHP will notice if the browser has disconnected when it tries to write
> data. If it detects the browser has disconnected it will stop script
> execution. You can control what php should do in the event of a
> disconnect with the ignore_user_abort() function. See the connection
> handling page for more details:
>
> http://us3.php.net/manual/en/features.connection-handling.ph p
>
> On Wed, Jun 3, 2009 at 2:54 PM, Johnny Withers wrote:
>> It just seems odd to me that PHP does a mysql_connect and gets a resource
>> ID. Then it uses that socket (or whatever it is) to do subsequent queries.
>> Mysql has to know about it too in order for it to send back results to the
>> same resource/handle/socket/whatever. So either PHP should do some
>> cleanup, which
>> I assume it does when the page goes away, otherwise PHP would leak memory
>> like a sieve all over the place as pages are closed/aborted.
>> PHP has no idea the browser left/aborted/died/etc. When you begin a request
>> that PHP handles, it begins processing the script, which in turn begins
>> processing whatever instructions the script has. At no point in time is it
>> aware if a "browser window" is waiting on its response, it simply spits it
>> response back to the HTTP server which may or may not have a client reading
>> the response to display to a user.
>>
>> PHP's clean up is when the script ends processing. You can see this by
>> creating a PHP script that takes ages to complete (no mysql involved, maybe
>> just a loop and sleep statements that sends output to a text file) and
>> kicking it off by browsing to the page, then closing your browser and
>> tail'ing the output file, you will see it continuing to write to this file
>> until the loop is complete.
>>
>> You can assume what you like about PHP, but that doesn't make it true.
>>
>> -johnny
>>
>>
>> On Wed, Jun 3, 2009 at 2:03 PM, Daevid Vincent wrote:
>>
>>> We have a "NOC" that displays this sort of thing and shows myTop in six
>>> windows for DEV/TEST/PROD and Master/Slave for
>>> each.(http://www.daevid.com/content/examples/snippets.php scroll down to
>>> "Automatic Monitoring of remote servers" for a handy script)
>>>
>>> So we do monitor this and will kill long running queries by hand. I was
>>> hoping for something a little more automated however. It just seems odd to
>>> me that PHP does a mysql_connect and gets a resource ID. Then it uses that
>>> socket (or whatever it is) to do subsequent queries. Mysql has to know
>>> about
>>> it too in order for it to send back results to the same
>>> resource/handle/socket/whatever. So either PHP should do some cleanup,
>>> which
>>> I assume it does when the page goes away, otherwise PHP would leak memory
>>> like a sieve all over the place as pages are closed/aborted, OR mySQL
>>> should
>>> get a signal that, "hey, your handle to return your data on just STB, so
>>> stop what you're doing please".
>>>
>>> On a related note then, is there a way to set a my.cnf setting so that
>>> queries that are going for longer than X seconds are re-niced or something
>>> to not bog down the system? Ideally I'd assume you'd want each query to
>>> "peg
>>> the CPU" so that they're in and out quickly and the user can get on with
>>> their life. But these long running ones at some point X are then taking
>>> over
>>> the entire server with 135% CPU usage and killing the experience for any
>>> other user on the web site -- and for a duration too!
>>>
>>>
>>> > -----Original Message-----
>>> > From: Darryle Steplight [mailto:dsteplight@gmail.com]
>>> > Sent: Wednesday, June 03, 2009 8:41 AM
>>> > To: Jerry Schwartz
>>> > Cc: Jay Blanchard; Daevid Vincent; mysql@lists.mysql.com
>>> > Subject: Re: Why doesn't mySQL stop a query when the browser
>>> > tab is closedL
>>> >
>>> > Hi Daevid,
>>> >
>>> > You can always stop the query by running
>>> >
>>> > SHOW PROCESSLIST;
>>> >
>>> > from the command line or your MySql Admin tool. The above command will
>>> > show you all of the queries that are currently running along with
>>> > their PID# and state. Find the query your want to stop, and run the
>>> > following command
>>> > KILL #; (where # is --
> Eric Bergen
> eric.bergen@gmail.com
> http://www.ebergen.net
>

--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Why doesn"t mySQL stop a query when the browser tab is closedL

Even if it did, it would be by convention only, there is no guarantee. Not
only that, but it would likewise be dependent on the browser closing
normally, which is likewise no guarantee.
The fact of the matter here however is that there is NO set of behavior that
would suit everyone. I can think of several examples (modifying table
structure, inserting data, etc) where I would want query execution to
continue even after the client has gone away.
Lastly, and I'll test this later, is this strictly php behavior? I seem to
remember queries started with MySQL query browser running to completion even
after losing my connection (although I think these were all writes, not
reads).

-----Original Message-----
From: Johnny Withers [mailto:johnny@pixelated.net]
Sent: Sunday, July 26, 2009 6:34 PM
To: Eric Bergen
Cc: Daevid Vincent; mysql@lists.mysql.com
Subject: Re: Why doesn't mySQL stop a query when the browser tab is closedL

I'm not sure closing the browser window sends the RST packet back to
the server, if it does, that's great! However, if php is waiting on
mysql to return a result, it wouldn't notice this anyway until mysql
finished processing and returned control back to the script. I could
be completly off-base here, I have no way to test this.

On Saturday, July 25, 2009, Eric Bergen wrote:
> PHP will notice if the browser has disconnected when it tries to write
> data. If it detects the browser has disconnected it will stop script
> execution. You can control what php should do in the event of a
> disconnect with the ignore_user_abort() function. See the connection
> handling page for more details:
>
> http://us3.php.net/manual/en/features.connection-handling.ph p
>
> On Wed, Jun 3, 2009 at 2:54 PM, Johnny Withers
wrote:
>> It just seems odd to me that PHP does a mysql_connect and gets a resource
>> ID. Then it uses that socket (or whatever it is) to do subsequent
queries.
>> Mysql has to know about it too in order for it to send back results to
the
>> same resource/handle/socket/whatever. So either PHP should do some
>> cleanup, which
>> I assume it does when the page goes away, otherwise PHP would leak memory
>> like a sieve all over the place as pages are closed/aborted.
>> PHP has no idea the browser left/aborted/died/etc. When you begin a
request
>> that PHP handles, it begins processing the script, which in turn begins
>> processing whatever instructions the script has. At no point in time is
it
>> aware if a "browser window" is waiting on its response, it simply spits
it
>> response back to the HTTP server which may or may not have a client
reading
>> the response to display to a user.
>>
>> PHP's clean up is when the script ends processing. You can see this by
>> creating a PHP script that takes ages to complete (no mysql involved,
maybe
>> just a loop and sleep statements that sends output to a text file) and
>> kicking it off by browsing to the page, then closing your browser and
>> tail'ing the output file, you will see it continuing to write to this
file
>> until the loop is complete.
>>
>> You can assume what you like about PHP, but that doesn't make it true.
>>
>> -johnny
>>
>>
>> On Wed, Jun 3, 2009 at 2:03 PM, Daevid Vincent wrote:
>>
>>> We have a "NOC" that displays this sort of thing and shows myTop in six
>>> windows for DEV/TEST/PROD and Master/Slave for
>>> each.(http://www.daevid.com/content/examples/snippets.php scroll down to
>>> "Automatic Monitoring of remote servers" for a handy script)
>>>
>>> So we do monitor this and will kill long running queries by hand. I was
>>> hoping for something a little more automated however. It just seems odd
to
>>> me that PHP does a mysql_connect and gets a resource ID. Then it uses
that
>>> socket (or whatever it is) to do subsequent queries. Mysql has to know
>>> about
>>> it too in order for it to send back results to the same
>>> resource/handle/socket/whatever. So either PHP should do some cleanup,
>>> which
>>> I assume it does when the page goes away, otherwise PHP would leak
memory
>>> like a sieve all over the place as pages are closed/aborted, OR mySQL
>>> should
>>> get a signal that, "hey, your handle to return your data on just STB, so
>>> stop what you're doing please".
>>>
>>> On a related note then, is there a way to set a my.cnf setting so that
>>> queries that are going for longer than X seconds are re-niced or
something
>>> to not bog down the system? Ideally I'd assume you'd want each query to
>>> "peg
>>> the CPU" so that they're in and out quickly and the user can get on with
>>> their life. But these long running ones at some point X are then taking
>>> over
>>> the entire server with 135% CPU usage and killing the experience for any
>>> other user on the web site -- and for a duration too!
>>>
>>>
>>> > -----Original Message-----
>>> > From: Darryle Steplight [mailto:dsteplight@gmail.com]
>>> > Sent: Wednesday, June 03, 2009 8:41 AM
>>> > To: Jerry Schwartz
>>> > Cc: Jay Blanchard; Daevid Vincent; mysql@lists.mysql.com
>>> > Subject: Re: Why doesn't mySQL stop a query when the browser
>>> > tab is closedL
>>> >
>>> > Hi Daevid,
>>> >
>>> > You can always stop the query by running
>>> >
>>> > SHOW PROCESSLIST;
>>> >
>>> > from the command line or your MySql Admin tool. The above command will
>>> > show you all of the queries that are currently running along with
>>> > their PID# and state. Find the query your want to stop, and run the
>>> > following command
>>> > KILL #; (where # is --
> Eric Bergen
> eric.bergen@gmail.com
> http://www.ebergen.net
>

--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=walton@vyper.hopto.org



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org