Edit report at https://bugs.php.net/bug.php?id=35217&edit=1
ID: 35217
Comment by: kishorekumar dot doreshetty at gmail dot com
Reported by: vovik at getart dot ru
Summary: mssql.max_procs strange behaviour
Status: Not a bug
Type: Bug
Package: MSSQL related
Operating System: RHEL 4
PHP Version: 4.4.1
Block user comment: N
Private report: N
New Comment:
Use close after connecting
Modified code
$links = array();
for ($k = 1; $k <= 30; $k++) {
echo "Connection attempt $k...";
$link = mssql_connect("localhost", "sa", "hidden", $new_link);
$links[] = $link;
echo "<br/>";
mssql_close($link);
}
Previous Comments:
------------------------------------------------------------------------
[2010-07-19 02:03:06] opc dot three at gmail dot com
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/>";
}
?>
------------------------------------------------------------------------
[2010-07-17 18:24:41] opc dot three at gmail dot com
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
------------------------------------------------------------------------
[2005-11-15 00:33:48] [email protected]
I've just tested this with the FreeTDS 0.64 and it works as expected.
If you are running PHP under Apache yoy need to restart the server when you
make changes to the php.ini file.
------------------------------------------------------------------------
[2005-11-14 20:36:31] [email protected]
Please try using this CVS snapshot:
http://snaps.php.net/php5-latest.tar.gz
For Windows:
http://snaps.php.net/win32/php5-win32-latest.zip
------------------------------------------------------------------------
[2005-11-14 16:34:47] vovik at getart dot ru
Description:
------------
When I try to limit maximum number of MS SQL open connections via php.ini it
doesn't work properly. It doesn't work at all. Instead of this, connections
actually established, but some error messages (from FreeTDS ?) appeared on
every mssql_connect() above mssql.max_procs.
I've added sleep() to end of script for checking number of connections on SQL
server side and in example above it is 30.
Reproduce code:
---------------
<?php
// mssql.max_procs set to 26 in php.ini
$links = array();
for ($k = 0; $k < 30; ++$k) {
$link = mssql_connect("mssqlserver", "xxx", "yyy", true);
if (!$link) {
exit("Connect failed.");
}
$links[] = $link;
}
sleep(20);
?>
Expected result:
----------------
PHP warning: Too many open links (26)
Connect failed.
Actual result:
--------------
(on stderr)
Max connections reached, increase value of TDS_MAX_CONN
Max connections reached, increase value of TDS_MAX_CONN
Max connections reached, increase value of TDS_MAX_CONN
Max connections reached, increase value of TDS_MAX_CONN
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=35217&edit=1
