[PHP-DEV] Bug #1196: Mysql connection hangs From: webmaster <email protected>
Date: 03/02/99

From: webmaster <email protected>
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 <email protected> 

--
PHP Development Mailing List   http://www.php.net/
To unsubscribe send an empty message to php-dev-unsubscribe <email protected>
For help: php-dev-help <email protected>