I have reviewed a number of threads regarding the causes of the HTTP 500 when using php through fast-cgi, but none of them (that I could find) were related to the odbc_result call, and is intermittent based either on the size (likely) or content (seems unlikely) of the data being extracted from the result set.
My php code looks like the following:
$sql = "select [errorSummary],[messageLog],[scanType],[runName]
FROM [SDITriage].[dbo].[SourceJobs]
Where id = $sourceJobID";
Debug("Import job scan message query = $sql");
# perform the query
$result = odbc_exec($odbcConn, $sql);
if (! $result)
{
# get the odbc error message
$returnData["error"] = "Unable to query the import job scan message data from the triage database: " . odbc_errormsg($odbcConn);
$returnData["rc"] = FALSE;
Debug("GetScanMessageData exit. (2) " . print_r($return,TRUE));
$nestingLevel--;
return($returnData);
}
# fetch import job data
$fetchResult = odbc_fetch_row($result);
Debug("Fetch Result = $fetchResult");
$returnData["errorSummary"] = ($fetchResult ? odbc_result($result,1) : "");
$returnData["messageLog"] = ($fetchResult ? odbc_result($result,2) : "");
$returnData["type"] = ($fetchResult ? odbc_result($result,3) : "");
$returnData["name"] = ($fetchResult ? odbc_result($result,4) : "");
On most invocations of the query above, I get the http 500 error and it is triggered on the odbc_result($result,2) line. There is no information in my php_errors.log file. I did an odbc trace and it showed:
php-cgi 2624-1c30 ENTER SQLExtendedFetch HSTMT
0x01FA9968 UWORD 1 SQLLEN 1 SQLULEN * 0x0118E9EC UWORD * 0x0118E9F8php-cgi 2624-1c30 EXIT SQLExtendedFetch with return code 1 (SQL_SUCCESS_WITH_INFO) HSTMT 0x01FA9968 UWORD
1 SQLLEN 1 SQLULEN *
0x0118E9EC (1) UWORD * 0x0118E9F8 (5)DIAG [01004] [Microsoft][ODBC SQL Server Driver]String data, righttruncation (0)
DIAG [01004] [Microsoft][ODBC SQL Server Driver]String data, righttruncation (0)
DIAG [01004] [Microsoft][ODBC SQL Server Driver]String data, righttruncation (0)
DIAG [01004] [Microsoft][ODBC SQL Server Driver]String data, righttruncation (0)
but that doesn't show me any explicit errors I can follow up on. I assumed that perhaps it was a memory issue because the data being pulled out of the results set is pretty big so I increased the php memory_limit directive to 512M but that had no affect. Occassionaly, the query works and the data is extracted and displayed properly in my application, but only for relatively small values. The content is a fragment of html code if that makes any difference.
The event log confirms that it is fastcgi/php-cgi issue, but I can't glean much else from it:
Faulting application name: php-cgi.exe, version: 5.4.13.0, time stamp: 0x514274c8
Faulting module name: MSVCR90.dll, version: 9.0.30729.6161, time stamp: 0x4dace5b9
Exception code: 0xc0000005
Fault offset: 0x0002466e
Faulting process id: 0x1f70
Faulting application start time: 0x01cef76dab28188c
Faulting application path: c:\php\php-cgi.exe
Faulting module path: C:\Windows\WinSxS\x86_microsoft.vc90.crt_1fc8b3b9a1e18e3b_9.0.30729.6161_none_50934f2ebcb7eb57\MSVCR90.dll
Report Id: f82eac41-6360-11e3-9a7c-00505684708c
At this point, I am not sure if it is ODBC causing the crash or php itself. Any help would be appreciated.
Thanks, Chris
It turns out the issue was with the php odbc.defaultlrl setting. If my data was less than the size of that value, everything was fine. If it was slightly bigger, it wouldn't crash but the output included garbage that looked a lot like a buffer over read. If the data was way bigger, php-cgi.exe would crash.
The fix I employed was straight forward. I did a pre-query where I used the datalengh() function to get the size of the large message log, then I used ini_set to increase the size of the odbc.defaultlrl value dynamically, then I executed the query to get the data. It now works fine.