php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #18271 Problems reading MSSQL data of type "real"
Submitted: 2002-07-10 22:41 UTC Modified: 2003-02-12 23:07 UTC
From: Brendan at callaghans dot com dot au Assigned:
Status: Not a bug Package: MSSQL related
PHP Version: 4.3.0 OS: Win2000
Private report: No CVE-ID: None
 [2002-07-10 22:41 UTC] Brendan at callaghans dot com dot au
Platform information:
Windows 2000 Server
IIS 5
MSSQL 2000

Hi,

For some time now, my PHP site has been experiencing an intermittent problem - very elusive.

Every now and then, a user would try to do something and the next page simply would not load.  All my users are accessing the site with IE6, and when the hang occurs, the status bar displays the text "Opening page ****...", and the blue status indicator crawls slowly to the right.  But nothing happens.  The screen doesn't even clear from the previous page.  No errors, no warnings.  I even tried turning on E_NOTICE, but none of those either.  This continues for a couple of minutes, until IIS gives up and reports a CGI Timeout.

Typically, if the user closed all his IE windows, and then went back in, things would work normally again.  So I assumed it had something to do with accumulating too much session data (hence, destroying the session by closing all the IE processes cleared things up).  And since it was not reproducible, I couldn't do very much about it.  However, just recently we discovered a series of actions which reproduces the problem, regardless of how "fresh" the session is.

The page in question is quite simple compared to some of the other pages in the site, which is why I find it curious that it should trigger the bug consistently.  When you first load the page, it presents a HTML form, asking the user for some data.  When the form is submitted (it submits back to the same php file), the page generates a fairly basic report.

The bizarre part is that with certain data inputs, the report runs fine (and total execution time is about a third of a second), and with certain other data inputs, the report will not load at all.  This page doesn't put any data in the session, the code doesn't use any particularly exciting functions, and I can't discern any relevant difference between the data input which works, and the data input which triggers the hang.  I can't debug it using PHP code, because it doesn't even reach the stage of outputting HTML.

I am very confused and looking for a hint as to how I can proceed.  I searched the bug database, but didn't find any matches to my problem.  The bug is causing some serious angst in my user base.  Any assistance will be greatly appreciated.

BJ

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-07-10 22:51 UTC] sniper@php.net
Does this happen with other browsers? Like NS or Mozilla?

 [2002-07-10 22:57 UTC] Brendan at callaghans dot com dot au
Haven't been able to try that - the site is intranet only, with a standardised operating environment on the workstations.  There's no other browser installed and I'd have to get permission from the SA to install one.

But I'll have a chat to him and see if I can arrange a test.
 [2002-07-11 03:24 UTC] Brendan at callaghans dot com dot au
I have been examining various log files, and have identified the following:

When this event occurs, the Windows Event Viewer records an error from the W3CSVC service, stating that the request was not fulfilled within the allowed timeout, and so the request was deleted.

The IIS error logs record the request as having failed with a 502 HTTP code.  A 502 is defined as "Bad Gateway", so not very helpful.

I tried switching on the config directive "display_startup_errors", but this had no effect.

The data being sent in the POST are one small integer (three digits max) and one four-character string.  And the hang is only triggered for very particular combinations of the two variables.  What has got me tearing my hair out is that the script is failing before it has a chance to even try operating on the data.  So how could the contents of the data be making a reproducible difference?  How could "169" and "2002" work perfectly, but "170" and "2002" cause the script to hang?

It just doesn't follow.
 [2002-07-11 05:06 UTC] Brendan at callaghans dot com dot au
Upgraded to 4.2.1, no change.

Did some more searching of the bugs database, found 15324 and 14937 seemed to be similar problems to mine, but didn't find any solutions.

It's becoming evident that the request is being passed to php.exe, but that PHP immediately fails to process the script, and instead of responding with an error, it just hangs.  IIS reacts to this non-response by dropping out a 502 error code and a "CGI Timeout" message.

Might try ADP next.
 [2002-07-12 01:39 UTC] Brendan at callaghans dot com dot au
Have confirmed that the error occurs using Mozilla 0.9.4 and Netscape Communicator 4.78 under Mandrake Linux.

These are older browsers, but it is clear that the error isn't IE6 specific.
 [2002-07-12 04:20 UTC] Brendan at callaghans dot com dot au
Using a process of elimination (commenting out blocks of code), I tracked down what was causing the hang.  As it happens, it is all because of a certain query on the page.  If PHP tries to run this query, it hangs, and the page doesn't return anything at all.

A bit of experimentation with the query revealed that the issue is directly related to one particular table in my database, odometer_log.  Some attempts to query this table cause the hang, do not pass GO do not collect 200 dollars.  No error is reported or logged by either PHP or SQL Server.

There's nothing very interesting about the table that should cause PHP to drop the bundle.  Here's the table definition:

CREATE TABLE odometer_log (
id int not null identity(1,1) primary key,
car int not null references motor_vehicle(id),
kilometres real not null,
date datetime not null,
type int not null references odometer_log_type(id)
)

As you can see, it's all pretty standard.  No exotic binary data types or the like.

I am able to open (and query) the table in SQL Server Enterprise Manager without any hassles, and likewise using Linked Tables in MS Access.  So the problem seems not to be within the data table itself, or ODBC, and rather something to do with how the php_mssql extension accesses it.

Results of various query tests on the table (through PHP)are as follows:

SELECT * FROM odometer_log:  Hangs
SELECT COUNT(*) FROM odometer_log:  Succeeds
SELECT * FROM odometer_log WHERE car=63:  Succeeds
SELECT * FROM odometer_log WHERE car=88:  Hangs

So you might think that the error was being caused by certain car IDs, right?  Well, not quite, since there's no significant difference between the data for car 63 and car 88.  But to prove the point, see this next series of results:

SELECT * FROM odometer_log WHERE car < 10: Succeeds
SELECT * FROM odometer_log WHERE car < 10 ORDER BY car: Hangs
SELECT * FROM odometer_log WHERE car = 3: Succeeds
SELECT * FROM odometer_log WHERE car > 2 and car < 4: Hangs
SELECT * FROM odometer_log WHERE car = 9: Hangs
SELECT * FROM odometer_log WHERE car > 8 and car < 10: Hangs
SELECT * FROM odometer_log WHERE car > 2 and car < 10: Hangs

Try as I might, I can't think of a single rational explanation for this behaviour, nor can I think of a distinguishing factor between the queries which work and those which fail.

Please help before I have an aneurism!
 [2002-07-12 23:55 UTC] Brendan at callaghans dot com dot au
I eventually figured out that the problem was related directly to the "kilometres" column of the table, which was of type real.

I changed the data type of the column to "decimal" - this was more appropriate for the data anyway.  The column contains odometer readings, numbers in the range 0 - 400000 which either had one decimal place or no fractional part at all.  

The problems instantly disappeared.

It seems that something in the php_mssql extension has trouble with the real data type, in certain circumstances.  Because I'm happy to use the decimal data type on this ocassion, my problem is solved, but I would still regard this as an open bug report.

I'm happy to do more testing on demand, if it would help the cause.
 [2002-12-07 01:42 UTC] iliaa@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php4-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php4-win32-latest.zip


 [2002-12-28 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over 2 weeks, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2003-01-15 00:19 UTC] Brendan at callaghans dot com dot au
I've been on holidays for a while, so missed out on the request to use the CVS snapshot.  

However, yesterday I upgraded to 4.3.0, and the "real" data type problem became a whole lot worse.

All my real data is currently being retreived by PHP as approximately 4.330e-304.  For example, a particular cell that holds the value 0.09 is being retreived by PHP as 4.33060646217E-304.

And it's not even consistent.  Another cell in the same column which also contains 0.09 is coming back as 4.33041176821E-304.

Nor is it proportionate.  A cell containing 0.5 is reported as 4.33055085271E-304, which is actually *less* than the first 0.09 example!

Just to test the other end of the range, I put the value 500 into a "real" cell, and this is what came back: 4.33041185318E-302.

If there is a pattern to these infinitestmal numbers, I can't detect it.  Apart from the 4.330 part, the rest appears essentially random, and doesn't appear to have any relationship to the actual data.

I can still acquire the correct data using non-PHP technologies, such as looking at the data directly in SQL Server Query Tool, running linked tables from MS Access, using ODBC from Excel, etc etc.

PHP appears to be working correctly with all other data types at this stage.

Please let me know if a fix is likely in the near future.  If it's not, I'll need to implement a workaround, or regress to an earlier PHP version.

I tried the link you provided to the Win32 CVS snapshot, but I got a 403 Forbidden response.
 [2003-01-15 03:56 UTC] sniper@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php4-STABLE-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php4-win32-STABLE-latest.zip


There were some mssql fixes committed recently.
 [2003-01-15 19:19 UTC] Brendan at callaghans dot com dot au
And it's just a case of overwriting my current php_mssql.dll with the one in this archive?
 [2003-01-17 21:16 UTC] sniper@php.net
Did you try it or not?

 [2003-01-19 21:07 UTC] Brendan at callaghans dot com dot au
Nope, unfortunately I haven't been able to do anything yet, because my local sysadmin has been difficult to contact, and I need his approval to log on to the server.

In the meantime, I've worked around the problem by changing all my real type columns to decimal.  When I get a chance, I'll test your snapshot and post my results here.

My previous question still stands.  I downloaded the archive you pointed me to, but all you said was "try it".  I'm assuming that you mean to try the php_mssql.dll file.  Am I correct?
 [2003-01-20 00:32 UTC] sniper@php.net
Yes, replace the php_mssql.dll and related dlls from the dlls/ folder.

 [2003-01-21 19:48 UTC] Brendan at callaghans dot com dot au
I've tried replacing the extension file, but there was no change.

You said "related dlls from the dlls/ folder", AFAIK nothing in the dlls/ folder is related to mssql.  So all I did was copy extensions/php_mssql.dll into c:\php, restarted my browser session and observed that I'm still getting the same data from real columns.

If I need to restart IIS to test this properly, tell me.
 [2003-01-21 20:16 UTC] Brendan at callaghans dot com dot au
I just looked up the manual page for MSSQL, and it mentions using the ntwdblib.dll file (which exists in the PHP archive under dlls/).  I've never concerned myself with that file because when I originally installed PHP, the MSSQL connection worked as soon as I copied across php_mssql.dll.

Do I need to replace ntwdblib.dll as well to test your fix?
 [2003-02-12 00:10 UTC] Brendan at callaghans dot com dot au
Okay, I've patched in the php_mssql.dll file from the latest archive, and it looks like we're getting closer.

The numbers coming back are nearly correct.  They are, however, all out by some incredibly small quantity.  Some examples:  The value on the left is what's actually in the cell, the value on the right is what PHP is returning.

0.17  0.170000001788
0.37  0.370000004768
0.185 0.185000002384
0.315 0.314999997616
0.435 0.435000002384
0.485 0.485000014305

19.04    19.0400009155
72.1438  72.1437988281
184.4263 184.426300049

Observations about the data:

It is now deterministic ... meaning that a value of 0.17 always returns 0.170000001788, regardless of which cell it occurs in.  This was not the case with the previous problem.

The discrepancy can be positive or negative, and this doesn't appear to be a function of the data in any discernable way.

The discrepancy does NOT correlate to the data value.  Values of 0.185 and 0.435 both generate a discrepancy of +2.384e-9, and 0.36 & 0.485 both generate +1.4305e-8.  If there is a mathematical pattern here, I can't see it.

NB: Zero always returns correctly, and, in a bizarre exception, so does the value 0.375.  All other values that I've looked at so far have these slight discrepancies.

Okay, scratch that.  I have just discovered something that might prove useful to you.  Integers always come back correctly, and so do numbers where the fractional part is based on a power of 2 denominator.  If that makes any sense.  To put it another way - if the number is 

x / (2^y) 

where x and y are any integers (positive or negative), it will return correctly.  Otherwise, it will not.

I hope this helps you track down whatever's wrong with the extension.
 [2003-02-12 00:17 UTC] Brendan at callaghans dot com dot au
It's just occurred to me that this also explains why 0.185 & 0.435 generate the same discrepancy, as do 0.36 and 0.485.

Check this out

0.435 - 0.185 = 0.25
0.485 - 0.36 = 0.125

0.25 and 0.125 are both members of the x/(2^y) set (1/2^2 and 1/2^3 repsectively) that don't produce discrepancies.  Coincidence?  I think not!
 [2003-02-12 23:07 UTC] fmk@php.net
In MSSQL Server a real is represented by a float when these data are extracted the will be converted to a double (PHP's standard data type for decimal numbers). This can/will cause differences on the last few decimals.

Use the round command in PHP to correct this.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu May 23 03:01:28 2024 UTC