memory leak with php and sqlsrv

Question

Hi,

I encountered a problem with a memory leak using Microsoft Windows Server 2008 R2, IIS/FastCGI, sqlsrv 2.0.182 and php 5.2.17 (php_sqlsrv_52_ts_vc6.dll) / php 5.3.8 (php_sqlsrv_53_nts_vc9.dll).

Please have a look at the test script appended and the results it produces. Freeing the statement or closing and reopening the connection did not make a difference in my tests.

Running the same script with mysqli does not show the effect of constantly increasing memory usage.

Any help would be appreciated.

Best regards
Lars Teuber


<?php

$db_conn = get_db_conn_sqlsrv();
#$db_conn = get_db_conn_mysqli();

$query = "SELECT 1 AS dummy"; // FROM DUAL
$seconds = 10; //60 * 60;

echo '<br>php version: [' . phpversion() . ']';
echo '<br>sapi name: [' . php_sapi_name() . ']';
echo '<br>runtime: [' . $seconds . 's]';
echo '<br>memory_get_usage start: [' . memory_get_usage() . ']';
echo '<br>memory usage by tasklist start: [' . memory_get_usage_by_tasklist() . ']';

$start_time = time();
while (time() - $start_time < $seconds) {
    db_query_sqlsrv($db_conn, $query);
    #db_query_mysqli($db_conn, $query);
}

echo '<br>memory_get_usage end: [' . memory_get_usage() . ']';
echo '<br>memory_get_peak_usage: [' . memory_get_peak_usage() . ']';
echo '<br>memory_get_peak_usage real: [' . memory_get_peak_usage(true) . ']';
echo '<br>memory usage by tasklist end: [' . memory_get_usage_by_tasklist() . ']';

function db_query_sqlsrv($conn, $query)
{
    $res = sqlsrv_query($conn, $query);
    if (!$res) {
        throw new Exception('query failed');
    }

    #sqlsrv_free_stmt($res);
    #sqlsrv_close($conn);
}

function db_query_mysqli($conn, $query)
{
    $query_id = mysqli_query($conn, $query);
    if (!$query_id) {
        throw new Exception('Invalid SQL: ' . $query);
    }
}

function get_db_conn_sqlsrv()
{
    $host = "xxx,1234";
    $conn_info = array(
        'Database'                  => 'db_name',
        'MultipleActiveResultSets'  => true,
        'CharacterSet'              => 'UTF-8',
        'UID'                       => 'uid',
        'PWD'                       => 'pwd'
        );

    sqlsrv_configure('WarningsReturnAsErrors', 0);
    $conn = sqlsrv_connect($host, $conn_info);
    if (!$conn) {
        throw new Exception('no link');
    }

    return $conn;
}

function get_db_conn_mysqli()
{
    $host = 'xxx';
    $user = 'user';
    $pass = 'pwd';
    $port = '1234';
    $database = 'db';

    $link_id = mysqli_connect($host, $user, $pass, '', $port);

    if (!$link_id) {
        throw new Exception('no connection');
    }

    if (!mysqli_select_db($link_id, $database)) {
        throw new Exception('cannot use database');
    }

    return $link_id;
}

// = "working set memory" in taskmgr (windows server 2008r2)
function memory_get_usage_by_tasklist()
{
    if (!is_windows()) {
        return 'n/a';
    }

    $output = array();
    exec('tasklist ', $output);
    foreach ($output as $value) {
        $ex = explode(" ", $value);
        $count_ex = count($ex);
        if (preg_match('/ ' . getmypid() . ' Services/', $value)) {
            $memory_size = $ex[$count_ex - 2] . ' Kb';
            return $memory_size;
        }
    }
}

function is_windows()
{
    return (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN');
}

?>

-->

sqlsrv
------

php version: [5.2.17]
sapi name: [cgi-fcgi]
runtime: [60s]
memory_get_usage start: [94384]
memory usage by tasklist start: [16.028 Kb]
memory_get_usage end: [93072]
memory_get_peak_usage: [125696]
memory_get_peak_usage real: [262144]
memory usage by tasklist end: [29.392 Kb]

php version: [5.2.17]
sapi name: [cgi-fcgi]
runtime: [3600s]
memory_get_usage start: [91384]
memory usage by tasklist start: [14.768 Kb]
memory_get_usage end: [89904]
memory_get_peak_usage: [123424]
memory_get_peak_usage real: [262144]
memory usage by tasklist end: [262.692 Kb]

php version: [5.3.8]
sapi name: [cgi-fcgi]
runtime: [3600s]
memory_get_usage start: [326688]
memory usage by tasklist start: [14.760 Kb]
memory_get_usage end: [325128]
memory_get_peak_usage: [359304]
memory_get_peak_usage real: [524288]
memory usage by tasklist end: [618.376 Kb]

Answer 1

Hi Lars,

For this issue, I would suggest opening a case with Microsoft Customer Support Services (CSS) (http://support.microsoft.com), so that a dedicated Support Professional can assist you in a more efficient manner.

 

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

Hi Peja,

thanks for your answer.

As I don't have a Microsoft Access ID I'd have to pay 300 Euro for it.

I'm not willing to do so as I think this is a bug report.

Do you see an alternative?

Best regards
Lars

Answer 3

Hi Lars

That's an interesting finding.

I don't think you need to post a bug report because the MSFT team pays attention to the posts here, but if you did want to file an official bug, this is the right place to do it: Codeplex Site.

It would be useful if you could post just the results of a series of attempts, with both mysqli and sqlsrv, but only for one version of PHP, probably just 5.3.8.

The memory_get_usage_end value looks good, what is the result that you don't like?  The peak usage or the task list?

The task list memory allocation could merely be caching, which might flat-line after a while.  If you reported intermittent snap shots of memory consumption during the long test, it would be much more useful, maybe every 10 seconds.  A constant rise would indicate a leak somewhere.

Edit: Sorry I forgot to ask, but how is your PHP running as a service?  If you are using fast-cgi, I think it should appear in tasklist as [PID] Console, with a process name of php-cgi.exe.

Edit 2: Ignore last edit, it is [PID] Services on Win 2008 for me too.

---

Rob

Answer 4

Hi Robert,

thanks! I'll wait with a bug report as you suggested, maybe we find a solution here.

You are right, the memory_get_usage() results look normal, but I don't know if they are reliable.

When looking at the taskmanager a constant rise in memory consumption is visible. The same constant rise can be seen with that memory_get_usage_by_tasklist() function from the test script.

I slightly modified the script to make snap shots of memory consumption every 10 seconds as you suggested and let it run for 10 minutes. Results show a continuous growth of memory usage.

Best regards
Lars


code:

+++
$start_time = $last_time = time();
$interval_seconds = 10;
while (time() - $start_time < $seconds) {
    db_query_sqlsrv($db_conn, $query);
    #db_query_mysqli($db_conn, $query);

    $curr_time = time();
    if ($last_time != $curr_time && $curr_time % $interval_seconds == 0) {
        echo '<br>memory usage by tasklist: [' . memory_get_usage_by_tasklist() . '] runtime: ' . ($curr_time - $start_time) . ' s';
        flush();
    }
    $last_time = $curr_time;
}
+++

results:

+++
php version: [5.3.8]
sapi name: [cgi-fcgi]
runtime: [600s]
memory_get_usage start: [336656]
memory usage by tasklist start: [14.776 Kb]
memory usage by tasklist: [17.180 Kb] runtime: 8 s
memory usage by tasklist: [19.900 Kb] runtime: 18 s
memory usage by tasklist: [22.652 Kb] runtime: 28 s
memory usage by tasklist: [25.216 Kb] runtime: 38 s
memory usage by tasklist: [27.832 Kb] runtime: 48 s
memory usage by tasklist: [30.468 Kb] runtime: 58 s
memory usage by tasklist: [32.880 Kb] runtime: 68 s
memory usage by tasklist: [35.704 Kb] runtime: 78 s
memory usage by tasklist: [38.400 Kb] runtime: 88 s
memory usage by tasklist: [40.232 Kb] runtime: 98 s
memory usage by tasklist: [42.612 Kb] runtime: 108 s
memory usage by tasklist: [45.432 Kb] runtime: 118 s
memory usage by tasklist: [48.260 Kb] runtime: 128 s
memory usage by tasklist: [50.828 Kb] runtime: 138 s
memory usage by tasklist: [53.200 Kb] runtime: 148 s
memory usage by tasklist: [55.376 Kb] runtime: 158 s
memory usage by tasklist: [57.684 Kb] runtime: 168 s
memory usage by tasklist: [60.188 Kb] runtime: 178 s
memory usage by tasklist: [62.364 Kb] runtime: 188 s
memory usage by tasklist: [64.740 Kb] runtime: 198 s
memory usage by tasklist: [67.240 Kb] runtime: 208 s
memory usage by tasklist: [69.968 Kb] runtime: 218 s
memory usage by tasklist: [72.352 Kb] runtime: 228 s
memory usage by tasklist: [75.240 Kb] runtime: 238 s
memory usage by tasklist: [78.084 Kb] runtime: 248 s
memory usage by tasklist: [80.948 Kb] runtime: 258 s
memory usage by tasklist: [83.832 Kb] runtime: 268 s
memory usage by tasklist: [86.724 Kb] runtime: 278 s
memory usage by tasklist: [89.160 Kb] runtime: 288 s
memory usage by tasklist: [92.052 Kb] runtime: 298 s
memory usage by tasklist: [95.016 Kb] runtime: 308 s
memory usage by tasklist: [97.644 Kb] runtime: 318 s
memory usage by tasklist: [100.208 Kb] runtime: 328 s
memory usage by tasklist: [102.988 Kb] runtime: 338 s
memory usage by tasklist: [104.908 Kb] runtime: 348 s
memory usage by tasklist: [107.448 Kb] runtime: 358 s
memory usage by tasklist: [110.136 Kb] runtime: 368 s
memory usage by tasklist: [112.552 Kb] runtime: 378 s
memory usage by tasklist: [114.168 Kb] runtime: 388 s
memory usage by tasklist: [115.768 Kb] runtime: 398 s
memory usage by tasklist: [117.372 Kb] runtime: 408 s
memory usage by tasklist: [119.168 Kb] runtime: 418 s
memory usage by tasklist: [120.300 Kb] runtime: 428 s
memory usage by tasklist: [121.064 Kb] runtime: 438 s
memory usage by tasklist: [121.392 Kb] runtime: 448 s
memory usage by tasklist: [121.852 Kb] runtime: 458 s
memory usage by tasklist: [122.292 Kb] runtime: 468 s
memory usage by tasklist: [122.616 Kb] runtime: 478 s
memory usage by tasklist: [123.200 Kb] runtime: 488 s
memory usage by tasklist: [124.432 Kb] runtime: 498 s
memory usage by tasklist: [125.632 Kb] runtime: 508 s
memory usage by tasklist: [126.484 Kb] runtime: 518 s
memory usage by tasklist: [127.036 Kb] runtime: 528 s
memory usage by tasklist: [127.368 Kb] runtime: 538 s
memory usage by tasklist: [127.872 Kb] runtime: 548 s
memory usage by tasklist: [128.916 Kb] runtime: 558 s
memory usage by tasklist: [129.892 Kb] runtime: 568 s
memory usage by tasklist: [130.732 Kb] runtime: 578 s
memory usage by tasklist: [132.560 Kb] runtime: 598 s
memory_get_usage end: [335424]
memory_get_peak_usage: [382600]
memory_get_peak_usage real: [524288]
memory usage by tasklist end: [132.832 Kb]
+++

Answer 5

Hi,

I tried the "Denali" Native Client with CTP1 of the 3.0 driver version and also the combination "Denali" with current version 2 of the driver. Same effect using php 5.3.8.

Best regards
Lars

Answer 6

Thanks for the full Report, Lars. Robert is correct, the product team watches this forum closely...and they will investigate this. (I will continue to ping them until they do. :-))

-Brian

---

This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan

Answer 7

Hi Lars,

The 2.0 driver will only load Native Client 10, so I am assuming you also have this installed?

Would you be able to try the RC of the Native Client 11 (Denali)? http://www.microsoft.com/download/en/details.aspx?id=28147

Thanks for the detailed information you have provided! The Microsoft offices are very quiet at this time of year, but I want to assure you that we are working on this. :)

I also noticed that you are not freeing the statement after you execute the query:

    #sqlsrv_free_stmt($res);

Would you be able to try to allow the statement to be freed and let us know if you are still seeing the memory leak?

Thanks!

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 8

Hi Jonathan,

thanks for clarifying the client loading possibilities.

I added this line to the script not to get confused about this again:

+++
echo '<br>sqlsrv_client_info: [' . print_r(sqlsrv_client_info($db_conn), true) . ']';
+++

->

sqlsrv_client_info: [Array ( [DriverDllName] => sqlncli10.dll [DriverODBCVer] => 03.52 [DriverVer] => 10.00.4000 [ExtensionVer] => 2.0.1802.200 ) ]


Currently I cannot try out the RC of Native Client 11.

Freeing the statement doesn't make a difference, probably because "a resource with no more references to it is detected automatically, and it is freed by the garbage collector. For this reason, it is rarely necessary to free the memory manually."
http://www.php.net/manual/en/language.types.resource.php

Happy New Year!
Lars

Answer 9

Lars,

Calling 'free' passes the call down to the ODBC driver. If there was a problem with the destructor, then explicitely freeing the memory may have helped. Thanks for trying!

We will investigate this as soon as we are able. Thanks for reporting it.

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 10

php version: [5.3.9]
sapi name: [apache2handler]
runtime: [10s]
memory_get_usage start: [358568]
memory usage by tasklist start: [28.508 Kb]
memory_get_usage end: [358656]
memory_get_peak_usage: [387768]
memory_get_peak_usage real: [524288]
memory usage by tasklist end: [31.288 Kb]

 

php version: [5.3.9]
sapi name: [apache2handler]
runtime: [60s]
memory_get_usage start: [359440]
memory usage by tasklist start: [51я388 Kb]
memory_get_usage end: [359528]
memory_get_peak_usage: [388312]
memory_get_peak_usage real: [524288]
memory usage by tasklist end: [78я856 Kb]
sqlsrv_client_info: [Array ( [DriverDllName] => sqlncli10.dll [DriverODBCVer] => 03.52 [DriverVer] => 10.00.2531 [ExtensionVer] => 2.0.1802.200 ) ]

 

It's very actual theme! Please help.

This topic can't resolve problem too.

Answer 11

Hi Taron133,

Unfortunately, we need to understand the call which is making the application crash in the thread you have linked. Can you please provide the logs, configuration, and a code snippet where you see this crash happen? It is very difficult for us to understand your problem without these.

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 12

Hi Lars,

I setup the following configuration:

Microsoft Windows Server 2008 R2, IIS/FastCGI, sqlsrv 2.0.182 and php 5.3.8 (php_sqlsrv_53_nts_vc9.dll on a test machine and did see similar behavior you mentioned in your post during the first few runs.

I proceeded to hit the website 10,000 times using Visual Studio load test from a remote machine, but did not see any substantial growth in the php-cgi exectables that would indicate a leak was occuring.   None of the processes ever climbed above 9k of memory use.

Can you run your test again and let me know how many iterations it takes you to drive the memory up to say ...40 or 50 MB ?

If the php-cgi memory steadily increases during your tests then I'd say there's a leak.  You can view the memory either through perfmon or task manager.

 

Thanks,

- Greg

 

 

Answer 13

I can confirm Lars' findings, and believe it's a significant discovery, which should be noted.

I use PHP to serve web pages or short scripts, and had not noticed the problem before - it takes a lot of cycles to show itself.

Below is a script that should prove it.  It can be run as a web page or a PHP console script.  It requires a trusted connection to the SQL Server.

To run from the console, save the PHP text to a file (e.g. memtest.php), and run it like these examples:

• PHP memtest.php (run constantly on server .\SQLEXPRESS)
• PHP memtest.php ms=60000 (run for 60 seconds)
• PHP memtest.php "server=MYSERVER\INSTANCE" (choose different server, quotes needed)
• PHP memtest.php driver=SQLSRV (run with sqlsrv functions - big leak)
• PHP memtest.php driver=ODBC (run with PHP ODBC interface - no leak)

There are no timings in this script, just run it and watch task manager or process explorer for some frightening results (~1MB per second lost).

 

<?php
// PHP 5.3 required.
header('Content-type: text/plain; charset=UTF-8');

if ( version_compare(PHP_VERSION, '5.3.0') < 0 )
{
	echo 'PHP 5.3 is required';
	exit;
}

$driver = 'SQLSRV'; // change this to ODBC or SQLSRV, or put driver=SQLSRV on URL, or command line
$milliseconds = 0; // number of milliseconds to run for, or '0' for no limit.
$sqlserver = '.\SQLEXPRESS'; // default sql server - trusted connection only.

if ( PHP_SAPI==='cli' )
{
	chdir(__DIR__);
	for ( $ct=1 ; $ct < $argc ; ++$ct )
	{
		$arg = preg_split('/[=:]/', $argv[$ct], 2);
		if ( strpos('-/', $arg[0]{0}) !== false )
			$arg[0]=substr($arg[0], 1);
		if ( count($arg) == 1 )
			if ( $ct < $argc+1 && strpos(':=', $argv[$ct+1]{0}) !== false )
			{
				++$ct;
				if ( !($arg[1] = substr($argv[$ct], 1)) && $ct < $argc+1 )
					$arg[1] = $argv[++$ct];
			}
			else 
				$arg[1]='';
		$_GET[$arg[0]] = $arg[1];
	}
}
if ( !empty($_GET['driver']) )
	$driver = strtoupper($_GET['driver']);
if ( isset($_GET['ms']) )
	if ( ($milliseconds=intval($_GET['ms'])) < 0 )
		$milliseconds = 0; 
if ( !empty($_GET['server']) )
	$sqlserver = $_GET['server'];

echo "Starting test on $sqlserver with $driver...\r\n";

if ( $milliseconds )
{
	$stop = microtime(true) + floatval($milliseconds)/1000.0;
	$TimeToStop = function() use ($stop) { return microtime(true) >= $stop; };
	echo 'Stopping after ' . $milliseconds . ' millisecond' . ($millisecond==1 ? '' : 's') . ' or ';
}
else if ( PHP_SAPI==='cli' )
{
	$TimeToStop = function() { return false; };
}
else
{
	$TimeToStop = function() { return file_exists('stop.txt'); };
}

echo ( PHP_SAPI==='cli' ) ? "Press ctrl-C to stop...\r\n" : "Create a text file named stop.txt to stop...\r\n";

if ( $driver == 'ODBC' )
{
	$driver_connect = function() use ($sqlserver)
	{ 
		return odbc_connect("Driver={SQL Server Native Client 10.0};Server=$sqlserver;Trusted_Connection=Yes;", '', ''); 
	};
	$driver_query = 'odbc_exec';
	$driver_fetch_array = 'odbc_fetch_array';
	$driver_free_result = 'odbc_free_result';
	$driver_close = 'odbc_close';
	$driver_errormsg = 'odbc_errormsg';
	$driver_fetch = 'odbc_fetch_row';
}
else 
{
	$driver_connect = function() use ($sqlserver) { return sqlsrv_connect($sqlserver); };
	$driver_query = 'sqlsrv_query';
	$driver_fetch_array = 'sqlsrv_fetch_array';
	$driver_free_result = 'sqlsrv_free_stmt';
	$driver_close = 'sqlsrv_close';
	$driver_errormsg = function() { return print_r(sqlsrv_errors(), true); };
	$driver_fetch = 'sqlsrv_fetch';
	$driver = 'SQLSRV';
}

$cTest = 1;

do 
{
	echo 'TEST ' . $cTest . '...';
	$conn = $driver_connect();
	$cRow=0;
	if ( $conn )
	{
		$stmt = $driver_query($conn, "select * from sysobjects");
		if ( $stmt )
		{
			while ( ($row = $driver_fetch($stmt)) )
			{
				++$cRow;
			}
			$driver_free_result($stmt);
			$stmt = NULL;
		}
		else 
		{
			echo 'Exec error: ' . $driver_errormsg();
		}
		
		$driver_close($conn);
		$conn = NULL;
	}
	else 
	{
		echo 'Connection error: ' . $driver_errormsg();
	}
	echo ' END TEST ' . $cTest . " ($cRow row" . ($cRow==1 ? '' : 's') . ")\r\n";
	usleep(100000);
	++$cTest;
} while ( !$TimeToStop() ) ;

echo "STOPPED.\r\n";
?>

 

---

Rob

Answer 14

Thanks Rob.

I will take a look at this

 

Thanks,

Greg

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

 

Answer 15

Hi Greg,

I added a counter to the script and let it run again for 10 minutes with an output of the memory usage and this time also the number of iterations (=queries run) every 10 seconds.

50 MB are reached after about 900.000 iterations.

Best regards
Lars

+++

php version: [5.3.8]
sapi name: [cgi-fcgi]
sqlsrv_client_info: [Array ( [DriverDllName] => sqlncli10.dll [DriverODBCVer] => 03.52 [DriverVer] => 10.00.4000 [ExtensionVer] => 2.0.1802.200 ) ]
runtime: [600s]
memory usage by tasklist start: [14.784 Kb]
memory usage by tasklist: [17.124 Kb] runtime: 9 s, num queries: 52512
memory usage by tasklist: [18.948 Kb] runtime: 19 s, num queries: 98835
memory usage by tasklist: [21.188 Kb] runtime: 29 s, num queries: 156223
memory usage by tasklist: [23.540 Kb] runtime: 39 s, num queries: 215988
memory usage by tasklist: [25.888 Kb] runtime: 49 s, num queries: 276514
memory usage by tasklist: [28.420 Kb] runtime: 59 s, num queries: 339387
memory usage by tasklist: [31.080 Kb] runtime: 69 s, num queries: 407739
memory usage by tasklist: [33.768 Kb] runtime: 79 s, num queries: 477417
memory usage by tasklist: [36.676 Kb] runtime: 89 s, num queries: 550735
memory usage by tasklist: [38.356 Kb] runtime: 99 s, num queries: 592726
memory usage by tasklist: [41.108 Kb] runtime: 109 s, num queries: 663425
memory usage by tasklist: [43.884 Kb] runtime: 119 s, num queries: 734845
memory usage by tasklist: [45.792 Kb] runtime: 129 s, num queries: 783864
memory usage by tasklist: [48.112 Kb] runtime: 139 s, num queries: 841870
memory usage by tasklist: [50.420 Kb] runtime: 149 s, num queries: 901981
...
memory usage by tasklist: [100.960 Kb] runtime: 339 s, num queries: 2192776
...
memory usage by tasklist: [151.828 Kb] runtime: 539 s, num queries: 3486133
memory usage by tasklist: [154.724 Kb] runtime: 549 s, num queries: 3560455
memory usage by tasklist: [157.664 Kb] runtime: 559 s, num queries: 3635649
memory usage by tasklist: [160.464 Kb] runtime: 569 s, num queries: 3705477
memory usage by tasklist: [163.380 Kb] runtime: 579 s, num queries: 3779892
memory usage by tasklist: [165.864 Kb] runtime: 589 s, num queries: 3842629
memory usage by tasklist: [168.292 Kb] runtime: 599 s, num queries: 3905631
memory usage by tasklist end: [168.244 Kb]

Answer 16

Hi Lars,

We're investigating this now, we'll let you know what we find. It looks like there is some kind of leak there, but we're trying to find exactly where this is happening.

Thanks for the additional information!

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 17

Hi Jonathan - Lars was replying to a request for more information from Gregory.  He isn't spamming the forum (like me!) :-)

---

Rob

Answer 18

I did confirm a small memory leak in the fetch operations and identified the area within the driver that is the culprit. We are currently working to make the correction.

Thanks for the repro samples.

 

Greg

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

 

 

 

Answer 19

Hi everyone,

We'd like to send out many thanks for the people who gave us the information to reproduce this issue. We have identified the source of the leak, and are currently evaluating a fix, which we expect to release in a future release (by future, I mean soon!).

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 20

500 error

default_charset no value

$connectionInfo = array("Database"=>$bd, 'UID'=>$rootlogin, 'PWD'=>$rootpass, 'CharacterSet'=>'UTF-8');

If you have more errors(>4000) from query and:

$e = sqlsrv_errors();

str_replace('[Microsoft][SQL Server Native Client 10.0][SQL Server]', '', $e[0]['message']);

 

If you have mb_ereg_replace('\[Microsoft\]\[SQL Server Native Client 10\.0\]\[SQL Server\]', '', $this->dbcon->GetErrorMessage()), it work's fine.

Answer 21

Hi Jonathan,

 

We've been lurking on this thread as we encountered the same issue (memory leak) about the same time that this thread came to be (looking to upgrade to PHP 5.3.9 from 5.2.9-2 and noticed a leak during benchmark testing).

 

I was wondering if there was an ETA on when an update might be released and will we see that applied to sqlsrv 2.0 or the soon to be released 3.0?

 

Is it viable / recommended to use the sqlsrv 1.1 until this is fixed?  We have some urgency to upgrade our PHP to the latest 5.3.9, but can't do so until we can connect to sql server reliably.

 

thx

 

 

 

Answer 22

ccraze,

How serious is the leak? How quickly does your memory add up before it becomes a problem? I would not recommend switching to 1.x, as we have deprecated that download, and it will be very difficult to find.

Our new 3.0 driver will be released with SQL Server 2012: http://www.microsoft.com/sqlserver/en/us/default.aspx :)

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 23

The leak is serious enough that eventually, Apache is entirely unable to connect to the database server without a restart.  (This could take 4 hours of off-peak production traffic; I've been able to reproduce it on a test server in half an hour under even a little bit of JMeter load testing, even with a very simple test script running just one query.)

Will there be a fix released for the 2.0 driver?  If so, is there an ETA for that?

If not, do you know whether the fix will be included at the time that the 3.0 driver is released?

Answer 24

Hi,

We believe that we have identified the source of the problem and the fix has been incorporated into the 3.0 release. We do not have any current plans to backport this fix to 2.0.

The 3.0 driver will be released with SQL Server 2012 on March 7 2012.

Thanks,

Jonathan

 

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 25

Jonathan,

thx for the quick reply!  

Sadly our QA team didn't catch it at first and the first thing we saw was after the project rolled into production.  In that environment, we saw our apache process grow from 80MB to start to 1.9GB (18hrs later) where it stalled and apache had to be restarted.

The error it was giving was SQLSTATE: IMSSPcode: -14.  If the apache process was restarted, it would start working fine again until the apache process again grew so big that it stalled and needed to be restarted.  Please note, despite this error that was showing up on pages that required db connection, php and apache seemed to work fine otherwise and other pages like <?php phpinfo(); ?> pages would come up no problem.

relevant Info:

WEB_SERVER (dedicated web server)
win 2k3 sp2
E5405 @2GHz w/ 4GB RAM
Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/0.9.8r PHP/5.3.9 (from apachelounge)
PHP 5.3.9 from PHP.net 

SQL_SERVER (different box from webserver, same subnet, dedicated sql server)
Win2k8
SQL Server 2k5 

 

Possible workarounds we tried included setting Maxrequestperchild to 5000.  This *works*, but as this server is under some load, some page hits would give the "page can not be displayed" as the process was restarted, which wasn't something we could live with :(

Let me know if any other info is helpful to know

 

Answer 26

ccraze,

Could you please contact me directly? jguerin /at/ microsoft /dot/ com

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 27

Hi,

I just tried the new driver version (3.0.3313) with my test script and can confirm that this memory leak issue is solved.

Thanks for the good work!

Best regards
Lars

+++

php version: [5.3.8]
sapi name: [cgi-fcgi]
sqlsrv_client_info: [Array ( [DriverDllName] => sqlncli11.dll [DriverODBCVer] => 03.80 [DriverVer] => 11.00.2100 [ExtensionVer] => 3.0.3313.0 ) ]
runtime: [600s]
memory usage by tasklist start: [15.180 Kb]
memory usage by tasklist: [15.544 Kb] runtime: 8 s, num queries: 51005
memory usage by tasklist: [15.596 Kb] runtime: 18 s, num queries: 122246
memory usage by tasklist: [15.552 Kb] runtime: 28 s, num queries: 189800
...
memory usage by tasklist: [15.656 Kb] runtime: 338 s, num queries: 2293657
...
memory usage by tasklist: [15.728 Kb] runtime: 578 s, num queries: 3928599
memory usage by tasklist: [15.728 Kb] runtime: 588 s, num queries: 4003550
memory usage by tasklist: [15.728 Kb] runtime: 598 s, num queries: 4076710
memory usage by tasklist end: [15.728 Kb]

Answer 28

Lars,

Very happy to hear that! :-)

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 29

Hi I would like to know if there is any change/fix planned for the sqlsrv extension v2.0.

Unfortunately I cannot use the 3.0.1 as my system does not support the latest SQL Server native client (2012). Also on the php website, the 2.0 version is suggested for all the systems that does not support the latest native client.

Could you please backport your fix for the 2.0 client too?

Many thanks for your help.

Answer 30

Hi Pier,

We will not be backporting any fixes to the 2.0 version. What system are you trying to run your PHP application on?

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 31

Hi Jonathan,

thanks for your reply. I'm using windows 2003 so my only option for sqlserver is the 2.0.

Do you know if there is any workaround or any way to make the 3.0 work with my windows version?

Thanks for the help.

Answer 32

Pier,

Please take a look at: http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/2b4b88d6-5d7b-4446-83ef-955a611f08c4

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 33

Hi Jonathan,

I'd like to stay with the official Microsoft release. The PDO option seems to be a solution (I saw that the 2.0 PDO version is not affected by the memory leak or at least it does not seem to be), but the problem with the PDO is that the fetch method always returns arrays with all the data 'defaulted' to type string that is not really a good solution for me.

Thanks

Answer 34

Hi Pier,

Unfortunately, we rely on the SQL Server Native Access Client 11, which is part of SQL Server 2012. SQL Server 2012 has dropped support for Windows XP and Windows Server 2012 as they are out of mainstream support: http://connect.microsoft.com/SQLServer/feedback/details/620107/denali-support-for-windows-xp

Thanks,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 35

Hi - we use the 2.0 driver because we need to connect to SQL server 2000 still. We use PHP 5.3 on IIS 7.5 (not apache) on Server 2008.   Can't you guys make the fix on Driver 2.0 or what can we do?   Can we safely use driver 2 on IIS? Our site gets lots of hits though.