|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2004-07-09 16:12 UTC] david dot blair at nsi1 dot com
Description: ------------ This is related to bug 19541 http://bugs.php.net/bug.php?id=19541 (mssql_connect fails under stress) When mssql_connect is being used heavily it will automatically fail and return "Unable to connect to server:" instantaneously. We've had this issue on PHP 4.3.6 and last nights current stable build of 4.3.7 when we attempted upgrading to fix the issue. I'm certain this is a problem with php_mssql.dll and however it interacts with ISAPI. We are using Apache 1.3.29 on a Windows 2003 server. Our database is Microsoft SQL 2003 sp3. It is running on the server above. Our development server is also running Windows 2003 with Apache 1.3.29. It will produce the same error under heavy load connecting to the SQL database on the production server. This tells me the SQL server can be local or remote and still have this problem. I would say this is a PHP problem, except that it stays stable and only severely degrades when the SQL server is under stress. On the opposite side of that, I'd say it's a Microsoft SQL server problem except that when PHP is to the point of never being able to connect, programs like Enterprise Manager and Query Analyzer work fine. So I'm guessing it's a joint problem between the two, but it only shows up when PHP is running as an ISAPI module. (we switched from fast cgi and iis to apache and isapi 3 months ago to get away from Bug #25863 IIS: The specified CGI application misbehaved) For three months I've had this problem, but it would only happen say once or twice a week. I put in a failover code to immediately send another mssql_connect if the first one failed and to email me that a double attempt occurred. So it was buggy but easily worked around. Yesterday the entire system fell apart on me. Constant failover to where it wasn't connecting. Hence this bug report. Our system is a web interface project management system for techs in the field that has about 15 people at any given time interacting with it. The SQL server itself runs this system and three reporting systems as well. What gets me is that it will instantly return the message: "Unable to connect..." it's like it's not even trying to connect, it's not like a 60 second timeout where it can't find the server...it's an instantaneous return. The system was already unstable when we tried it, but we tried switching over to mssql_pconnect instead of mssql_connect on the development server and we experienced the same issue. It makes me wonder how pconnect and connect are routed in php_mssql.dll I've seen comments like this one (pulled from bug page 19541): "The Microsoft Library (dblib) used for the mssql extension is not thread safe." but no other information about the mssql extension and how it uses the dblib exists. If anyone can get me information on how to help out on fixing this extension issue, email me! Because I'm more than willing to drop my time into this problem. PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Fri Apr 19 12:01:27 2024 UTC |
I can recreate the behavior described in this report and it does not seem to be a PHP or SQL Server problem. Further it does not seem to be affected by the mssql.max_procs setting. Changing the max_procs setting up, down or to unlimited has no effect in the reproduce code below. Rather it appears to be related to the underlying driver used in my particular setup, namely the Microsoft DB-LIB driver ntwdblib.dll, plus the value of the $new_link parameter being set to true in the call to mssql_connect. I am using ntwdblib.dll version 2000.80.194.0 but have also tried this with the most current version I could find, 2000.80.2187.0, which is included in patch set "Microsoft SQL Server 2000 Post-SP4 Rollup". I am not sure if it is common knowledge but the SQL Server DB-LIB interface has been officially deprecated since the release of SQL Server 2005. While the server API was left in the product to allow legacy apps to continue to function, support for new data-types and features are not being added to the interface and Microsoft discourages new development from using the interface as it will eventually be dropped from the product. For example the VARCHAR(MAX), NVARCHAR(MAX) & XML data types introduced in SQL Server 2005 are not supported by DB-LIB and thus cannot be reliably communicated to and from the server via the DB-LIB API. I think I read somewhere that ntwdblib.dll has a limit of 25 concurrent connections per process but I cannot seem to find that info now that I want to reference it. The results from the reproduce code are consistent with that however as the first 25 connections are made but the 26th fails. In my environment all connections from all pages in the IIS site will be opened within the same server process so the limit of open connections would be 25 per site. I have looked around and I do not see a way to increase this limit. For a given web page, setting $new_link to false in the call to mssql_connect will avoid the issue, however I think the real-world scenario is when multiple pages within a site are trying to open connections to the database and the total open connections for the entire site exceeds 25. Theoretically I think this could be dealt with by having a dedicated data access layer that emulated a connection pool to limit the # of open connections from the site to 25 or less. In my environment I worked arouind the issue by switching to ODBC since perfromance was not of utmost concern. The odbc libs are slower than the mssql libs but the SQL Server 2005 Native Client ODBC driver referenced by my ODBC DSN does not suffer from the concurrent connection limit of 25. Environment: ----------------- - 32-bit Windows Server 2003 Standard - IIS 6 - PHP 5.2.6 - SQL Server 2005 Standard w/SP3 (all post-SP3 patches) - DB-LIB driver ntwdblib.dll 2000.80.194.0 Reproduce code: ----------------- <?php ini_set("display_errors", 1); // ...so we can see all output, good or bad ini_set("mssql.max_procs", 5); // default is -1 (unlimited) but changing this has no effect on the results $new_link = true; // when true each call to mssql_connect opens a new connection and we see problems after 25 connections $links = array(); for ($k = 1; $k <= 30; $k++) { echo "Connection attempt $k..."; $link = mssql_connect("localhost", "sa", "hidden", $new_link); $links[] = $link; echo "<br/>"; } ?> Expected result: ---------------- Connection attempt 1... Connection attempt 2... Connection attempt 3... Connection attempt 4... Connection attempt 5... Connection attempt 6... Connection attempt 7... Connection attempt 8... Connection attempt 9... Connection attempt 10... Connection attempt 11... Connection attempt 12... Connection attempt 13... Connection attempt 14... Connection attempt 15... Connection attempt 16... Connection attempt 17... Connection attempt 18... Connection attempt 19... Connection attempt 20... Connection attempt 21... Connection attempt 22... Connection attempt 23... Connection attempt 24... Connection attempt 25... Connection attempt 26... Connection attempt 27... Connection attempt 28... Connection attempt 29... Connection attempt 30... Actual result: -------------- Connection attempt 1... Connection attempt 2... Connection attempt 3... Connection attempt 4... Connection attempt 5... Connection attempt 6... Connection attempt 7... Connection attempt 8... Connection attempt 9... Connection attempt 10... Connection attempt 11... Connection attempt 12... Connection attempt 13... Connection attempt 14... Connection attempt 15... Connection attempt 16... Connection attempt 17... Connection attempt 18... Connection attempt 19... Connection attempt 20... Connection attempt 21... Connection attempt 22... Connection attempt 23... Connection attempt 24... Connection attempt 25... Connection attempt 26... Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8 Connection attempt 27... Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8 Connection attempt 28... Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8 Connection attempt 29... Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8 Connection attempt 30... Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: localhost in D:\phptest\_test_mssql.max_procs.php on line 8There was an error in my reproduce code. Here is the updated version: <?php ini_set("display_errors", 1); // ...so we can see all output, good or bad ini_set("mssql.max_procs", 50); // default is -1 for library default (25 for DB Lib), but setting this to higher than 25 has no effect on the results $new_link = true; // when true each call to mssql_connect opens a new connection and we see problems after 25 connections $links = array(); for ($k = 1; $k <= 30; $k++) { echo "Connection attempt $k..."; $link = mssql_connect("localhost", "sa", "hidden", $new_link); $links[] = $link; echo "<br/>"; } ?>