From: Martin Gainty Date: June 3 2009 8:25pm Subject: RE: Why doesn't mySQL stop a query when the browser tab is closedL List-Archive: http://lists.mysql.com/mysql/217795 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_6c90f155-708d-4c4d-81c7-b680a8380024_" --_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@stripped > To: mysql@stripped > 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@stripped]=20 > > Sent: Wednesday=2C June 03=2C 2009 8:41 AM > > To: Jerry Schwartz > > Cc: Jay Blanchard=3B Daevid Vincent=3B mysql@stripped > > 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@stripped] > > >>Sent: Wednesday=2C June 03=2C 2009 8:46 AM > > >>To: Daevid Vincent=3B mysql@stripped > > >>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@stripped > > > > > > > >=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@stripped= om >=20 _________________________________________________________________ Hotmail=AE has ever-growing storage! Don=92t worry about storage limits.=20 http://windowslive.com/Tutorial/Hotmail/Storage?ocid=3DTXT_TAGLM_WL_HM_Tuto= rial_Storage_062009= --_6c90f155-708d-4c4d-81c7-b680a8380024_--