php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44300 mssql_connect fails sometimes
Submitted: 2008-03-01 03:30 UTC Modified: 2012-11-20 13:05 UTC
Votes:18
Avg. Score:4.8 ± 0.5
Reproduced:14 of 15 (93.3%)
Same Version:9 (64.3%)
Same OS:9 (64.3%)
From: alfa77 at gmail dot com Assigned:
Status: Wont fix Package: MSSQL related
PHP Version: 5.2.5 OS: Windows 2000
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2008-03-01 03:30 UTC] alfa77 at gmail dot com
Description:
------------
Hello!
The number of my site users has recently increased and I have faced the following bug: 
mssql_connect function sometimes fails to connect to MSSQL. It works fine for few hours, 
then suddenly fails, and the work is resumed in a few minutes.

Here is the part of my DB class (CSql)
function connect_db($server, $user, $password)
{
	$this->dbc=mssql_connect($server, $user, $password);
	if(!$this->dbc) return false;
	return true;
}

The warnings in Apache log are
[Thu Feb 21 20:50:58 2008] [error] [client 82.200.***.***] PHP Warning: mssql_connect() 
[<a href='function.mssql-connect'>function.mssql-connect</a>]: Unable to connect to server: 
****** in ******.php on line 22

When I reload the page on site, connection sometimes succeeds, sometimes ? fails.
Web-server configuration: Windows 2000 Server (SP4) + Apache 2 + PHP 5.2.5. Peak usage 
of CPUs is no more than 70%, RAM usage ? no more than 50%. There is a program on this server 
that works with MSSQL through ADO, and it never has connection problems. There are no 
connection problems from LAN too.
SQL Server 2000 (SP4) is working on dedicated server (Windows 2000 Server (SP4)) 
which is connected to the web server by gigabit LAN. I have tested the network adapters, 
they work fine. CPUs usage is no more than 60%, RAM usage ? no more than 70%.

I have already:
1) replaced ntwdblib.dll in PHP directory with a new version. Also, I have installed MDAC 2.81
2) moved SQL Server to a  new, fast server.
3) tried to use mssql_connect or  mssql_pconnect.
4) tried to use server name or server IP and port as the first parameter for mssql_connect
5) tried to connect through TCP/IP or Named Pipes.
6) When I cycled connection attempts, it succeeded in connection almost every time (usually 
from the second or third attempt)
$db=new CSql;
$db_failed=1;
while(!$db->connect_db(DBHost, DBLogin, DBPassw))
{ 
       if($db_failed>=5) break;
        sleep(2);
        $db_failed++;
}
if(!$db->dbc) die("DB connection failed!");
7) I checked the network connection by using sockets or ODBC when mssql_connect had failed. It worked fine!
if(!fsockopen("192.168.0.3", 1433, $errno, $errstr, 10)) WrLog("C:\\sqlerr.txt","Err1 (($errstr ($errno)))");
if(!odbc_connect("sqlsrv",DBLogin, DBPassw)) WrLog("C:\\sqlerr.txt","Err2");
8) There are no connection limits in SQL Server or PHP.ini
[MSSQL]
mssql.max_persistent = -1
mssql.max_links = -1

I examined Apache logs few months ago, when MSSQL worked on the same server with Apache. 
There were no messages about connection problems. 


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-03-08 07:38 UTC] alfa77 at gmail dot com
Well, I found two solutions.
1) I use ADO to connect to MSSQL. ADO is more slower (up to 2 times!), than mssql_* functions, but there are no connection problems
2) I set connection timeout in code:
function getmt()
{
   list($usec, $sec)=explode(" ",microtime());
   return ((float)$usec+(float)$sec);
}
$time_st=getmt();
$db=new CSql;
while(!$db->connect_db($Host, $Name, $Login, $Passw))
   if(round(getmt()-$time_st, 0)>60) break;
if(!$db->dbc) die("Connection failed!"); 

It is too hard to use odbc_* functions, because there are a lot of bugs with TEXT field type ? I must put it on the last position in query and use CONVERT(varbinary, other way I get ODBC errors.

I hope that mssql_* and odbc_* problems will be solved in future PHP releases.
 [2008-03-10 05:41 UTC] alfa77 at gmail dot com
I think that this problem occurs due to usage of old DB-Lib for connecting to MSSQL. There are another bug ? that you can?t fetch varchar more than 256-character length (you have to convert it to TEXT).
It?s a pity, but the best way to work with MSSQL now is using ADO (no connection/long varchar/Unicode problems).
 [2008-03-26 08:31 UTC] william at nettsite dot co dot za
I am running three Windows XP SP2 boxes as developer workstations, all PHP 5.2.5, installed into c:\php to avoid the space in "Program Files", and the MS Sql connection works perfectly on one of the machines, but not the other two. All three boxes are on the same LAN, can connect to the database using MS SQL Studio, and all have IIS 5.1. I have copied the PHP installation from the working PC to the others, no difference. This is whether the connection is to a remote or local SQL server.
 [2008-09-26 11:53 UTC] yusefhassan at gmail dot com
Have you try editing php.ini mssql.max_procs?
mssql.max_procs = -1
 [2008-12-09 15:25 UTC] frosty dot z at freesbee dot fr
Hi, same problem detected here (connection "rarely" successful with mssql_connect, with a MSSQL server under quite heavy load).

Happens only with PHP on Windows, not on Linux (FreeDTS).

But for some reason I needed to connect from PHP/Windows, so I have used the "ADO workaround", as previously suggested by alfa77.

At first, I didn't understand very well that workaround, so here are some details :

Do not use the ADOdb engine 'mssql' because it will still use mssql_connect(). Instead, use 'ado_mssql' which uses COM objects ; that makes all the difference.

Here is a basic database functions lib :

function db_open($db_host, $db_login, $db_pass, $db_name)
{
  $db = NewADOConnection('ado_mssql');
		
  $dsn="PROVIDER=MSDASQL;DRIVER={SQL Server};"
  . "SERVER=".$db_host.";DATABASE=".$db_name.";UID=".$db_login.";PWD=".$db_pass.";";
		
  $db->Connect($dsn);
		
  return $db;
}

function db_query($db, $query)
{
  return $db->Execute($query);
}
   
function db_fetch_assoc($res)
{
  $obj = $res->FetchNextObj();
  return get_object_vars($obj);
}
   
function db_close($db)
{
  $db->Close();
}
 [2009-02-13 19:26 UTC] b116d at mail dot ru
Same problem here.
Apache 2.2.10
Php 5.2.5 as module.
OS win2003 sp1+all critical updates

I even try upgrade to php 5.2.8, but it still appears.
 [2009-12-03 17:37 UTC] dbuerer at leviton dot com
I too have suffered this same problem for the last couple of years on all of the 5.x release of PHP.  It works great for days then has problem for a few days then works great.  1-3 page reloads often solves the problem but not always. Unfortunatley support for this extension has been discontinued--I wonder if this is one of the reasons why?  I would like to try using Microsofts SQLSRV driver but converting an entire website from mssql to sqlsrv is going to be a lot of work!
 [2010-01-19 08:41 UTC] t dot zander at tuneup dot de
We're experiencing the same problem with Microsofts SQLSRV driver.
So it might be it's not just a driver problem.
 [2010-02-25 10:18 UTC] healerx78 at yahoo dot com
trials
 [2010-07-17 18:59 UTC] opc dot three at gmail dot com
Related to Bug #35217.
 [2012-11-20 11:50 UTC] kishorekumar dot doreshetty at gmail dot com
Problem is due to connection limit. it would be good if you close connection once your done with sql part. use mssql_free_statement(resouceid) and connection close to db every page of you site.
 [2012-11-20 12:20 UTC] alfa77 at gmail dot com
I think that this bug is not related to connection limit.
I have closed each connection at the end of the script (although it is not necessary due to documentation: 
----------------------------
mssql_close <...>
Note that this isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. 
----------------------------
Also, exec sp_who2 did not show a lot of connections (no more than 200).

I have solved this problem by using another extension (odbtp as a gateway on Windows server, PHP is hosted now on UNIX) - everything is all right now.
 [2012-11-20 13:05 UTC] pajoye@php.net
-Status: Open +Status: Wont fix
 [2012-11-20 13:05 UTC] pajoye@php.net
You should really move to 5.3+ and use http://pecl.php.net/sqlsrv
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Tue Nov 24 06:01:23 2020 UTC