ID: 29074 User updated by: david dot blair at nsi1 dot com Reported By: david dot blair at nsi1 dot com Status: Open Bug Type: MSSQL related Operating System: MS 2003 PHP Version: 4.3.7 New Comment:
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 Previous Comments: ------------------------------------------------------------------------ [2004-07-12 23:58:22] david dot blair at nsi1 dot com 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... ------------------------------------------------------------------------ [2004-07-09 19:51:39] david dot blair at nsi1 dot com 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. ------------------------------------------------------------------------ [2004-07-09 18:46:16] [EMAIL PROTECTED] You can set the number of allowed connections to the server in php.ini. The default value is 25. mssql.max_procs = 25 ------------------------------------------------------------------------ [2004-07-09 16:12:46] 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. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=29074&edit=1
