ID: 11895
Updated by: derick
Reported By: tigger@ebom.org
Status: Open
Bug Type: ODBC related
Operating System: win2k
PHP Version: 4.0.6
New Comment:
Update by: Chris Vanderschaaf <chrisv@terrigal.net.au>
Derick
I have a similar problem only using NT4 SP 6 , Apache 1.3.20 win32, PHP
4.0.6 cgi and Access 97 through an ODBC dsn. Only happens on large
queries. It give the access violation error to Dr Watson, but subsequent
request still execute, so it must start a new thread without taking out
apache. One of the tables involved has about 3.5 million records.
By default all queries in Access97 seem to have an execution time of 60
secs. This can be changed within Access queries, but i don't know how to
send that command via ODBC.
Hope this helps a bit
Chris
Previous Comments:
------------------------------------------------------------------------
[2001-07-06 11:23:37] tigger@ebom.org
We played with the indexes and now the data is returning a
lot faster (2 seconds where it was over 60!) Someone who was
meant to have placed these indexes did not test for this
query :]
Anyway, you can still crash php/apache with the following
code IF you go beyond the script timeout limits. Apache
spawns an instance for each request so it only crashes
individual instances of apache, but its still not to good. I
think if you load php as a CGI you can run into bigger
problems. Here is some code;
<html>
<body>
<?php
//change these to suit your set-up
//change $PC to any 4 numbers
$PC = "2000";
$DB_DSN = "SomeDNS";
$DB_USR = "SomeUser";
$DB_PWD = "SomePass";
$query = "SELECT TOP 500 PVMAL.STATE, " .
" PVMAL.POSTCODE, " .
" PVMAL.UNIT, " .
" PVMAL.UNIT_NO, " .
" PVMAL.HOUSE_NO, " .
" PVMAL.ST_NAME, " .
" PVMAL.ST_TY, " .
" PVSALES.SALE_PRICE, " .
" PVSALES.SALE_DATE, " .
" PVSUBURB.SUBURB " .
" FROM PVMAL " .
" JOIN PVSUBURB " .
" ON PVMAL.SUBURB_ID = PVSUBURB.SUBURB_ID " .
" JOIN PVSALES " .
" ON PVMAL.MALID = PVSALES.MALID " .
"WHERE PVMAL.POSTCODE = " . $PC . " AND " .
"PVSALES.SALE_DATE > '01-jan-2000' " .
"ORDER BY PVSALES.SALE_DATE DESC";
$alt = 0;
$CON = odbc_connect($DB_DSN, $DB_USR, $DB_PWD);
$RS = odbc_exec($CON,$query);
if (odbc_num_rows($RS) > 0) {
//echo($State . " " . $PC);
?>
<table cellspacing="2" cellpadding="3" border="0">
<tr>
<td width="210"><b>Sales History With Valid Address</b></td>
<td width="65"><b>Sale Price</b></td>
<td width="65"><b>Sale Date</b></td>
<td width="55"><b>Type</b></td>
<td width="65"><b>Details</b></td>
</tr>
<?
for ($i=1;odbc_fetch_row($RS,$i);$i++) {
$Address1 = "xx " .
trim(odbc_result($RS,"ST_NAME")) . " " .
trim(odbc_result($RS,"ST_TY")) . ", ";
$Address2 = odbc_result($RS,"SUBURB") . ", " .
odbc_result($RS,"STATE") . " " .
odbc_result($RS,"POSTCODE");
if (odbc_result($RS,"UNIT") == "Y") {
$Address1 = "x/" . $Address1;
$PropType = "UNIT";
} else {
$PropType = "HOUSE";
}
$SalePrice = "\$" . "xxxxxx";
$SD = explode("-",substr(odbc_result($RS,"SALE_DATE"),0,10));
$SaleDate = "$SD[2]/$SD[1]/$SD[0]";
$PropDetails = " ";
if ($alt == 1) {
$bgcol = "FFFFFF";
} else {
$bgcol = "FFE2CC";
}
?>
<tr bgcolor="#<? echo($bgcol); ?>" valign="top">
<td><? echo($Address1 . "<br />" . $Address2); ?></td>
<td><? echo($SalePrice); ?></td>
<td><? echo($SaleDate); ?></td>
<td><? echo($PropType); ?></td>
<td><? echo($PropDetails); ?></td>
</tr>
<?
$alt = abs($alt - 1);
$Address = "";
$SalePrice= "";
$SaleDate = "";
odbc_fetch_row($RS); // MoveNext
}
}
odbc_close($CON);
if (($i == 1) && ($alt == 0)) {
?>
<tr>
<td colspan="5" bgcolor="#FFE2CC">There have been no sales
in your specified postcode in the last twelve months.</td>
</tr>
<?
}
?>
</table>
</body>
</html>
------------------------------------------------------------------------
[2001-07-06 10:41:03] kalowsky@php.net
any chance you can post a sample db (small schema), and a sample script for us to try?
------------------------------------------------------------------------
[2001-07-05 03:12:00] tigger@ebom.org
After adding set_time_limit(60); the following is dumped to
the web browser.
Fatal error: Maximum execution time of 60 seconds exceeded
in c:\websites\tig\sample_report2.php on line 49
and 'Program Error' box is popped up on the server (Dr
Watson) saying 'Apache.exe has generated errors and will be
.... '
When php is run as a CGI the error msg says that 'php.exe
has generated...'
Line 49 of the code is;
$RS = odbc_exec($CON,$query);
The weirdest bit is if I re-run the same query with the same
variables (after the restart of apache) the query is nice
and fast. I think SQL2000 is caching the results somehow.
The variable is required and will be any number between 700
and 9999
------------------------------------------------------------------------
[2001-07-05 02:52:39] derick@php.net
Can you confirm that it crashes at around 60 seconds if you use set_time_limit(60); at the top of
your script?
Derick
------------------------------------------------------------------------
[2001-07-04 22:24:55] tigger@ebom.org
an ODBC query that takes a long time to excute (> 30 sec)
will crash php and requires apache to restart. This is when
loading php as an apache modual or as a CGI
apache 1.3.20 (win2k)
php 4.0.6 (win2k)
SQL2000 (win2k)
The SQL2000 database has over 30 million records and is
performing a scan (on indexed records)
I'm more than willing to test code or help out to fix this btw
------------------------------------------------------------------------
Edit this bug report at http://bugs.php.net/?id=11895&edit=1