You can set the number of allowed connections to the server in php.ini. The default value is 25.

mssql.max_procs = 25

We have mssql.max_procs set to 250.

So what does that mean; processes? Is a process a single SQL connection? Or is it all events running through mssql?
Would:
 mssql_connect(yada yada);
 mssql_close(yada);
be considered two processes or one?

Or is a process a single user requesting thru Apache?

We only have 30 employees in our company and there's never an instance where all 30 would be creating a SQL connection at the same time.

All connections to the database are made per page, so if they wanted to list all open technician projects, an mssql_connect would be called at the beginning, several different fetches would be made, and then an mssql_close command would be sent before the HTML is generated and shot out to the client computer.

If each individual command is a "process" I can see where mssql.max_procs is the problem...but shouldn't each individual command end before the next one begins thus there could only really be 1 or 0 processes at any given time per client connection??

If I used mssql_connect but didn't use mssql_close how long would a process keep alive? Until the script finishes or a specific time frame? And then when we changed mssql_connect to mssql_pconnect last night why did we still have intermittent problems when only one client computer was requesting...that one user should've retained that one persistent connnection as per documentation...it shouldn't have broken instantly with a "Unable to connect..." warning.

What's the max number I can set mssql.max_procs to? I don't believe that is the issue, but if it is, then there's another serious bug where once you meet the max procs, then it always assumes max procs and it will never let you connect until the SQL server and the Apache server are reset.

Today at 4:22 the production server just informed me that it took 15 attempts to get a connection. Normally it's alot lower than this when mssql_connect fails...but on the 15th connect it caught. How am I doing this? for-loop until mssql_connect returns a connection. Since it instantaneously fails the user doesn't even know it occurs.

Is this a good solution. No. Hence this bug report. 

What failover would there be in mssql to cause it to fail right away without even looking for the server? 

Should I have my error email send me any information? I'm not sure what function is out there that would report on the state of mssql. In fact I don't see any reporting functions...I really need someone that knows the workings of this module to work with me on this...

I should probably note that fmk and I have exchanged 2 emails outside of this bug report (just so it's part of this paper trail). 

He brought me up to speed on the thread safety issues of the dblib library.

This next part can be taken with a grain of salt. I'm not sure if it's a seperate issue or something that will help fmk in debugging...I upgraded our development server to PHP 5. It's still showing the intermittent problems of the failed connection, but it's not happening at mssql_connect. It happens at mssql_select_db. The error that's returned is:

Warning: mssql_select_db() [function.mssql-select-db]: Unable to select database: blah blah blah...

I dunno fmk if you made a fix in the 5 version that's not present in the 4.3.7 version? Wondering if you created some magic code that fixes the mssql_connect issue that needs to be applied to the other mssql functions...

DB

Hi,

I have the same experience with mssql_select_db. I use PHP 4.3.2 as a module with FreeTDS. The mssql extension is patched though with a datetime extension that reads the milliseconds as well (as mentioned in some early bugs)

The problem does not seem to occur with heavy load, but after a certain amount of mssql_select_db. According to the documentation:

"In case a second call is made to mssql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned." (mssql_connect) and "Every subsequent call to mssql_query() will be made on the active database." (mssql_select_db)

I have modified my database access layer to call mssql_select_db() before each mssql_query().

After a number of mssql_select_db, I get an instant failure. Retrying helps for some more queries, but then the process hangs with Apache running at 100%.

HTH, Jochen

I've never had Apache hang before but I'm also not running through TDS.

I just tried upgrading the production server to 5.0.1 last night. I've been running 5.0.0 on the development server and 5.0.1 on my laptop for two weeks/one week with no problems.

The database connection failed 6 times last night after we brought everything back up. After 2 more failures this morning I rolled everything back. 

Oh hell, I just remembered I left php_mssql.dll from the 5.0.1 build in the windows directory...I think I'm going to leave it in there and see what happens...two of us have tested the system with no crashes so far...

More info as it comes in...

We are currently running apache_1.3.28, freetds-0.61.2 and php-4.3.3 on linux 2.4.x kernels and experiencing the same problem connecting to a SQL 2000E SP3 database.  Modifying our PHP db connection classes to immediately try mssql_connect again when it fails produces the desired results most of the time.  However not always.

I will be upgrading to the latest and greatest in the current major version for all of the components on the Linux side and testing more.  I will post a follow up then.

Charles Bennington
Oddcast, Inc.

Upgrading seems to make things worse.  I upgraded to freetds-0.62.4, php-4.3.9RC2 (4.3.8 has a compile issue with freetds-0.62.4 according to bug reports), and apache 1.3.31.

Now in addition to mssql_connect failures I also get mssql_select_db failures for one of our busier databases.

Could it be that php/freetds is getting too fast and giving up before a response is received from SQL 2000?

Charles Bennigton
Oddcast, Inc.

This bug should probably be closed.  The problem is on the MSSQL end and ban be resolved by changing the value of the winsocklistenbacklog registry entry.  For those who are experiencing this problem, please see http://support.microsoft.com/default.aspx?kbid=328476

I don't know why this also affects mssql_select_db, but they do appear to go together.

Charles, this doesn't explain why newer versions of PHP cause the error more frequently than older versions, when everything else about our systems remains the same. This also doesn't explain why PHP is the only thing having issues that contacts my SQL server.

I'll try your work-around tomorrow and monitor the server for a few weeks to see what happens. It's a step in the right direction to finding a solution if it does help, but I still see it as a work-around, not a solution.

I am also having the same problem. I have been searching high and low for an answer, but have found none. I ended up creating a loop in my PHP connect code too, and it has attempted to connect more than once, 27 times on my development server in the past week.

I am running the following:
Windows 2003 Server
IIS6
PHP 5.01
SQL Server 2000 SP3

A also previously had the issue on 4.3.8, which didn't fail as often. Unfortunately I need some of the features of PHP5 on my new website.

Cheers,
Matt

(In response to two posts ago) Charles, I tried your fix by setting the winsocklistenbacklog to 16.

Didn't work.

Had three more incidents today where the first connection was refused but on an instant reconnect it worked.

If you have access to a packet sniffer and the basic knowhow, I would recommend sniffing the connection to and from port 1433 of your MSSQL server (if you are using 1433 that is) and the web server having the connect problems.  I was able to determine that this problem was on the SQL side of things by looking at the packet trace.  I would log mssql_connect failures that were corrected by a second attempt and matched their times exactly to TCP RESET packets being sent from the MSSQL server back to the web server requesting a connection to port 1433.  This allowed me to rule out PHP as the source of the failure.

As to why newer versions of PHP cause this more than older versions, all I can assume is that the newer PHP is more efficient in some way in opening TCP connections and this is causing greater concurrency in TCP connection requests to the SQL server.

With a listen backlog of 25 on my SQL server I still see occasional failures but that is within an acceptable range now as they are handled by trying again immediately.  If the number starts to go up, I will try increasing my listen backlog.

I also observed that an entirely separate problem on my SQL server related to the RAID card increased the failure rate so there appear to be other mitigating factors on the SQL end.  Fixing the RAID issue make the connect failures rate drop down to zero again.

Charles

We've retired the server that was having the issue...the new production server is as stable as the development servers...so I'm not sure what to do with this bug report...

Should it be closed? Handed off? Bogused? Do I scratch this up as a corrupt install of SQL or PHP or the OS on that server...?

Bogus it is.

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 8

There 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/>";

}

?>