[SOLVED] HELP: PHP upgrade broke my code!

[steveg1701]

Hi, I hope one of you gurus out there can help me. I have a web

application that had been running fine before I upgraded my server,

now it fails on the following snippet of code:

 

function NextOrder($OrderID) {

ConnectDatabase();

$MyQuery = "SELECT OrderID FROM `Orders` WHERE (OrderID > $OrderID)";

$request = mysql_query($MyQuery);

list($Answer) = mysql_fetch_row($request);

echo "DEBUG: Query is ""$MyQuery"",next is ""$Answer""";

return $Answer;

 

}

 

When this code is interpreted on the old server it outputs this:

 

DEBUG: Query is "SELECT OrderID FROM `Orders` WHERE (OrderID > 20081437)", next is "20081438"

 

but on the new server

DEBUG: Query is "SELECT OrderID FROM `Orders` WHERE (OrderID > 20081437)", next is ""

 

Not surprisingly, I have a companion routine that looks essentially

the same except for the < operator replacing > and sort order

descending - it fails in the same way. if however, for the purpose of

testing I replace the < with a = then the code works as expected;

 

Did I find a bug in PHP/MySQL somewhere or am I doing something wrong

that I was getting away with before and now getting my hand slapped by

smarter software? ;-)

 

The environment that this worked under is

 

PHP 5.2.0

SuSE 10.2 x586 32 bit

MySQL 5.0.26-12

PHP5-MySQL 5.2.0-10

 

Environment it failed under

 

PHP 5.2.4

SuSE 10.3 x86 64 bit

MySQL 5.0.45-22 x86 64 bit

PHP5-MySQL 5.2.4-10 x86 64 bit

 

Thanks in advance for any light you can shed on this!

[pocobueno1388]

First try this:

 

<?php

function NextOrder($OrderID) {
   ConnectDatabase();
   $MyQuery = "SELECT OrderID FROM `Orders` WHERE (OrderID > $OrderID)";
   $request = mysql_query($MyQuery);
   list($Answer) = mysql_fetch_row($request);
   echo "DEBUG: Query is '$MyQuery', next is '$Answer'";
   return $Answer;
}

?>

 

If that doesn't work, try this:

 

<?php

function NextOrder($OrderID) {
   ConnectDatabase();
   $MyQuery = "SELECT OrderID FROM `Orders` WHERE (OrderID > $OrderID)";
   $request = mysql_query($MyQuery);
   $row = mysql_fetch_assoc($request);
   $Answer = $row['OrderID'];
   echo "DEBUG: Query is '$MyQuery', next is '$Answer'";
   return $Answer;
}

?>

 

 

[steveg1701]

Thanks for the quick reply!!

 

I tried both your suggestions (though I didn't see any difference between my original code and yours besides the missing ; on the query);

 

Both yielded the same results.

 

Any more ideas? ???

[btherl]

Can you try this:

 

$request = mysql_query($MyQuery) or die("Error in $MyQuery: " . mysql_error());

[steveg1701]

OK, that gave me a little bit to go on but what does this error mean?

 

Error in SELECT OrderID FROM `Orders` WHERE (OrderID > 20086774): MySQL server has gone away

 

FWIW the SQL server is on the same box as the http server and is addressed as localhost

[AndyB]

OK, that gave me a little bit to go on but what does this error mean?

 

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

[mattclements]

best thing to try is to look if the PHP <-> MySQL connection works correctly on other scripts... this maybe a server based error...

 

Please advise,

Matthew

[steveg1701]

OK, thanks for the help so far, now we're getting somewhere!

 

I decided to put debug code in both of the companion routines, as follows:

 

<?php

 

function PrevOrder($OrderID) {

  ConnectDatabase();

  $MyQuery = "SELECT OrderID FROM `Orders` WHERE (OrderID < $OrderID) order by OrderID desc";

  $request = mysql_query($MyQuery);

  list($Answer) = mysql_fetch_row($request);

  echo "DEBUG: Query is '$MyQuery', next is '$Answer'<br>";

  echo "Prev Error code: " . mysql_error() . "<br>";

  return $Answer;

}

 

function NextOrder($OrderID) {

  ConnectDatabase();

  $MyQuery = "SELECT OrderID FROM `Orders` WHERE (OrderID > $OrderID)";

  $request = mysql_query($MyQuery);

  list($Answer) = mysql_fetch_row($request);

  echo "DEBUG: Query is '$MyQuery', next is '$Answer'<br>";

  echo "Next Error code: " . mysql_error() . "<br>";

  return $Answer;

}

 

?>

 

and this is what I got

 

DEBUG: Query is 'SELECT OrderID FROM `Orders` WHERE (OrderID < 20086776) order by OrderID desc', next is ''

Prev Error code: Lost connection to MySQL server during query

DEBUG: Query is 'SELECT OrderID FROM `Orders` WHERE (OrderID > 20086776)', next is ''

Next Error code: MySQL server has gone away

 

 

So I changed the order of the calls to the routines and behold:

 

DEBUG: Query is 'SELECT OrderID FROM `Orders` WHERE (OrderID > 20086776)', next is '20086779'

Next Error code:

DEBUG: Query is 'SELECT OrderID FROM `Orders` WHERE (OrderID < 20086776) order by OrderID desc', next is ''

Prev Error code: Lost connection to MySQL server during query

 

Now NextOrder works but PrevOrder still doesn't

 

If I paste the SQL statement into MySQL Query Browser it gives me the same error.  So it appears that the problem is in MySQL itself.  Interestingly enough, I set up an identical environment on another computer, with the exception that it was 32 bit - and everything WORKED!

 

A bit more investigation yields that the SQL will work if I change the DESC to ASC or if I drop the WHERE condition.

 

As Lewis Carroll wrote, "curiouser and curiouser"

 

Any thoughts?

[btherl]

Could it be data corruption causing the query to crash?  You can try the query on an identical table (Same structure, indexes and content) in the same environment and see if that works.  If it does, then replacing the old table with that new one should fix it.

 

But if that does work, I would worry about how it got corrupted in the first place.

[steveg1701]

I really don't think it's data corruption.  In addition to the Orders table I have a Customers table and another pair of analagous routines to find the next and previous records, I get an identical failure there.

[steveg1701]

If I replace the query "SELECT OrderID FROM `Orders` WHERE (OrderID > 20086774)"

with "SELECT OrderID,Status FROM `Orders` WHERE (OrderID > 20086774)" then it returns the expected records.  I created a table with an identical structure and populated it with a few records and tried the query on it and had the same issue, this really leads me to believe that I may have actually found a bug in MySQL!

I suspected as much earlier since it happened only on the 64 bit version but this seems to confirm it.  Any thoughts?

[btherl]

I didn't notice you were running a 64 bit mysql there .. yes I would believe that there may be bugs in the 64 bit version, even some serious ones.  When Mysql (and most other mature software packages) were written, ints were 32 bits and that was that.