Bug #1196: Mysql connection hangs

From: webmaster@softgallery.fr Operating system: Linux 2.0.36 PHP version: 3.0.6 PHP Bug Type: MySQL related Bug description: Mysql connection hangs This a REALLY annoying bug. Since the installation of PHP 3.0.7 (the html form has been updated). Some big sql requests hangs for ever. It never occured before (I tried 3.0.7pre and the bug was here too). The mysql daemon is locked, so every requests is queued and my load explodes, something like 23 in a few minutes. The request I send takes normally 15 seconds. Here is a mysqladmin processlist one minute after the hanging request has been sent : | 382 | root | localhost | newsg | Query | 67 | Sending data | select SUM(SF.Quantite*(((P.Prix*D.Cours)/ 5.9260)/(1+(D.TVA/100)))) as Revenue,SUM(SF.Quantite) as | | 383 | root | localhost | newsg | Query | 57 | Copying to tmp table | SELECT DISTINCT Type.Nom_Logiciel,Type.Nom_Editeur,Type.Categorie1,Type.Category1 From Produit,Type | | 384 | root | localhost | newsg | Query | 34 | Copying to tmp table | SELECT DISTINCT Type.Nom_Logiciel,Type.Nom_Editeur,Type.Categorie1,Type.Category1 From Produit,Type | | 386 | root | localhost | newsg | Query | 28 | Sending data | SELECT Produit.*,Type.Nom_Editeur,Caddies.*,Devises.* FROM Produit,Type,Caddies,Devises WHERE Produi | | 388 | root | sg.mhnet.fr | newsg | Query | 46 | Locked | UPDATE Sous_Facture SET Telecharge = Telecharge+1 WHERE Numero_Facture = '47800' | | 389 | root | localhost | newsg | Query | 16 | Copying to tmp table | SELECT Produit.*,Type.*,Devises.* From Produit,Type,Devises WHERE ((Produit.Nom_Logiciel LIKE '%web% | | 390 | root | localhost | newsg | Query | 9 | Sorting for order | SELECT DISTINCT Type.Nom_Logiciel,Type.Nom_Editeur,Type.Categorie1,Type.Category1 From Produit,Type | | 392 | root | localhost | newsg | Query | 25 | Copying to tmp table | SELECT DISTINCT Produit.Prix,Pubs.*,Devises.* FROM Pubs,Produit,Devises WHERE Produit.Nom_Logiciel = | | 393 | root | localhost | newsg | Query | 6 | Copying to tmp table | SELECT DISTINCT Produit.Prix,Pubs.*,Devises.* FROM Pubs,Produit,Devises WHERE Produit.Ref_SG = Pubs. | | 394 | root | localhost | | Processes | 1 | | and so on... Sorry the fields name are in french. Here is the request : select SUM(SF.Quantite*(((P.Prix*D.Cours)/ 5.9260)/(1+(D.TVA/100)))) as Revenue,SUM(SF.Quantite) as Numbe r From Facture as F, Sous_Facture as SF, Produit as P, Client as C , Devises as D WHERE D.Nom=P.Monnaie AND F.Numero_Client=C.Numero AND F.Numero_Facture=SF.Numero_Facture AND SF.Ref_SG=P.Ref_SG AND DAYOFMONTH(Date_De_Facture)='02' AND Month(Date_De_Facture)='03' AND Year(Date_D e_Facture)='1999' AND F.Mode_De_Paiement<>'' AND F.Validation='1' AND C.Banni<>'1' One more time sorry for the french langage. By the way this problem does not accur each time I execute this request ! For the moment I would advise against upgrading to php 3.0.7 on a production server running mysql. I really need some of the new features so please tell me when (if ?) this bug has been corrected. By the way thanks to all the dev team for PHP, all my business runs it and I never had a real problem since today. J-F Mammet webmaster@softgallery.fr -- PHP Development Mailing List http://www.php.net/ To unsubscribe send an empty message to php-dev-unsubscribe@lists.php.net For help: php-dev-help@lists.php.net