php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #33694 IIS needs restart when invalid MSSQL statement run
Submitted: 2005-07-14 11:03 UTC Modified: 2006-04-04 18:35 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: spam at meyrick dot co dot nz Assigned: fmk (profile)
Status: Closed Package: MSSQL related
PHP Version: 4.4.0 OS: Windows 2003 Server Enterprise
Private report: No CVE-ID: None
 [2005-07-14 11:03 UTC] spam at meyrick dot co dot nz
Description:
------------
I am using IIS6 (Win2k3 Server, SQL Server 2000, PHP 4.3.4 DLL)

I am connecting to MSSQL using username/pwd and pconnect

no probs there.

My problem is that if I execute a horribly incorrect SQL statement one of the perminant connections to MSSQL is disabled giving 'unable to connec to database' (pconnect() == false) errors 50% of the time..

For example:

Table: [Stock Items]:
StockItemID int no nulls identity
SupplierID int no nulls  <--**
test varchar(50) allow null
...

$sql = "INSERT INTO [Stock Items] (test) VALUES ('this should fail')";
$result = mssql_query($sql);


Warning: mssql_query(): message: Cannot insert the value NULL into column 'SupplierID', table 'Database Name.dbo.Stock Items'; column does not allow nulls. INSERT fails. (severity 16) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): message: The statement has been terminated. (severity 0) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): General SQL Server error: Check messages from the SQL Server. (severity 5) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): Query failed in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): Attempt to initiate a new SQL Server operation with results pending. (severity 7) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 186



The last error is the one that is the kick in the guts as now EVERY sql statement until the page finishes loading will fail.

When the page is refreshed pconnect() will work 50% of the time (if they get a valid connection it works, if they get the dead connection it fails).

The only way I can fix this problem is restarting IIS which is a pain.


I can replicate the problem by killing the process from MSSQL Enterprise manager.

Any help would be great.

ps. an answer of "just fix your sql statement" is not what I'm looking for as it is not allways related to invalid sql statements, running large querieis using ODBC over the www seems to cause the exact same problem, so does re/syncronizing a large replicated sql database.

Thanks.


Reproduce code:
---------------
Table: [Stock Items]:
StockItemID int no nulls identity
SupplierID int no nulls  <--**
test varchar(50) allow null
...

$sql = "INSERT INTO [Stock Items] (test) VALUES ('this should fail')";
$result = mssql_query($sql);


Expected result:
----------------
$result == valid mssql result

Actual result:
--------------
Warning: mssql_query(): message: Cannot insert the value NULL into column 'SupplierID', table 'Database Name.dbo.Stock Items'; column does not allow nulls. INSERT fails. (severity 16) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): message: The statement has been terminated. (severity 0) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): General SQL Server error: Check messages from the SQL Server. (severity 5) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): Query failed in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 49

Warning: mssql_query(): Attempt to initiate a new SQL Server operation with results pending. (severity 7) in C:\Inetpub\websites\sitename\public_html\acweb\includes\functions\database.php on line 186


+ dead pconnection to MSSQL server until IIS (php dll) is restarted

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-10-18 16:56 UTC] reynardmh at nospam dot lightsky dot com
I experience the same problem with php 5.0.5, win2k, not sure what version of sql server. As a temporary fix, you can change your code to use mssql_connect instead of pconnect, that seems to solve the problem for me.
 [2005-12-24 02:24 UTC] sniper@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.1-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.1-win32-latest.zip


 [2006-01-01 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2006-03-28 14:38 UTC] fjortiz at comunet dot es
I reproduce this bug with the latest 5.1. CVS . It also happens with very long queries that yield timeouts: the persistent connection stops working until you restart the web server.

I think it has to do with this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;165951

Which also refers this article:
"DB-Library prevents you from sending additional queries if there are results from a previous query that need to be handled. For more information, see the following article in the Microsoft Knowledge Base:

http://support.microsoft.com/kb/117143/EN-US/

I saw php_mssql.c and you can call "dbcanquery" in mssql_free_result, but once you have triggered the error, it's impossible to regain control, because you can't get new results to free the affected connection (DBPROCESS).

Frank, maybe this could be solved with an explicit call to dbcanquery(mssql_ptr->link) before returning "FALSE" in mssql_query and mssql_execute. What do you think?
 [2006-04-04 18:35 UTC] fmk@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

Code has been added to make sure all pending results are cleared from the server when an error happens
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Oct 15 04:01:26 2024 UTC