PHP lying to me about results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

PHP lying to me about results

Post by pickle »

~Everah and I have been trying to figure this thing out for almost 2 hours & both our minds are blown.

Basically, I've got class function that sends a query to MySQL via my database abstraction class, which uses the OOP mysqli_* functions. MySQL receives the query (as I can see it in the query log) but doesn't execute it. MySQL returns that all things worked fine - no error. When checking mysqli->affected_rows, it returns the expected number of 1 - even though no rows were actually updated.

If I copy & paste the query from the query log into MySQL via CLI, it executes just fine.
If I build a new file & use my database abstraction layer to execute the query, it executes just fine.
If I build a new file & use the raw mysqli_* functions, it executes just fine.

For some reason, it only fails when being run from that particular class function.

I'm leaning towards this being a problem with MySQL or the client library, but any insight would be greatly appreciated, as I've got absolutely no clue what's wrong.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: PHP lying to me about results

Post by Benjamin »

Besides the fact that no rows are being updated, (I'm assuming this is an update query?), what makes you say it's not being executed? Are you absolutely sure there are no subtle differences in the query, especially in a WHERE clause? Are there any MySQL reserved keywords in the query? Can you post the actual query?

EDIT: Try breaking the query by putting jibberish in it, does it throw an error then?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: PHP lying to me about results

Post by pickle »

- Yes this is an update query. I know nothing's being changed because I'm checking the database directly through the CLI & the row isn't updated.
- The query is:

Code: Select all

UPDATE
      `response`
SET
      `number` = 89
WHERE
      `response_id` = 172405
- Breaking the query does generate an error
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: PHP lying to me about results

Post by Benjamin »

I've seen this happen before, I can't for the life of me remember what the problem was. I think putting single quotes around the integers will fix it, I just can't remember why. I'll keep thinking about this.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP lying to me about results

Post by Eran »

did you check what the query looks like before it is ran against the native db functions?
Try using FirePHP to check exactly what is ran against the database
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: PHP lying to me about results

Post by pickle »

- Putting quotes around the numbers didn't help.
- When I was doing the testing to see if it was my DAL or native functions, I was using a hardcoded query - the same one for all testing & the same one that isn't working.
- I know exactly what the database is receiving, as I'm looking at the daemon's query log.

Thanks for the advice though.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP lying to me about results

Post by Eran »

I'd still suggest to var_dump the query just before it is executed using native functions. Wrap it <pre> tags as well. You might have hidden characters (line breaks etc) that are not shown in the query log
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: PHP lying to me about results

Post by pickle »

That doesn't show anything surprising.

This is the code that builds the query:

Code: Select all

$query = <<<SQL
UPDATE
      `response`
SET
      `number` = '$number'
WHERE
      `response_id` = '$this->response_id'
SQL;
Not a lot of opportunity for extra characters to be inserted.

I should mention (and likely should have earlier), that other queries in this function don't get executed either. In my testing, I put a second update query at the very head of this function - it wasn't executed either. It showed up in the logs just fine, but it wasn't executed. This leads me to believe that it's a problem with MySQL or at very least the connection between PHP & MySQL. Creating a new database connection in this function & using that connection to execute the queries didn't solve anything either.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: PHP lying to me about results

Post by Weirdan »

hmm... are those queries a part of transaction by any chance? (it could be so without you knowing it, for example when autocommit mode is off for connection or server, or previous transaction on this connection not closed properly). Try issuing 'commit' query after your query.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: PHP lying to me about results

Post by pickle »

I don't use transactions anywhere in this entire application. Or even this server for that matter. Running 'COMMIT' didn't do anything, and 'autocommit' is set to 1.

Is there a way to "scrub" the connection - sort of reset it or revert it to it's initial state without making a new connection?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP lying to me about results

Post by Eran »

Is it possible to see some code?
Also, dump the mysql connection itself, see what it contains
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: PHP lying to me about results

Post by pickle »

The results of var_dump-ing the mysqli object from the DAL inside the function that's causing problems:

Code: Select all

object(mysqli)#5(0){
}
The results of var_dump-ing the mysqli object from the file I'm viewing

Code: Select all

object(DBi)#4 (5) {
  ["mysqli"]=>
  object(mysqli)#5 (0) {
  }
  ["result"]=>
  bool(false)
  ["error:protected"]=>
  bool(false)
  ["errnum:protected"]=>
  bool(false)
  ["query_count"]=>
  int(6)
}
It's a singleton variable, so it's the same object. Does this indicate that somehow the mysqli object inside my DAL is being unset?

Here's the function I keep referring to:

Code: Select all

 function _updateResponseNumber($number)
{
    if(!$this->_checkResponseNumber($number,$this->response_id))
        return FALSE;
 
    $query = <<<SQL
UPDATE
    `response`
SET
    `number` = '$number'
WHERE
    `response_id` = '$this->response_id'
SQL;
 
    if(!$result = $this->DB->execute($query,'updating response number'))
    {
        $this->error = 'Unable to update respnose number due to a database error: "'.$this->DB->getError().'"';
        return FALSE;
    }
    else
    {
        return TRUE;
    }
}
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP lying to me about results

Post by Eran »

This is how mysqli object looks (empty). It's possible the connection is lost - try pinging it to see if it is connected and if it will reconnect (try querying again after the ping). As a last resort you can dump debug info into the mysql log using dump_debug_info() - http://www.php.net/manual/en/mysqli.dump-debug-info.php
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: PHP lying to me about results

Post by pickle »

The ping ran successfully - but no change.

Here's the result of dumping the debug info:

Code: Select all

Status information:
 
Current dir: /var/lib/mysql/
Running threads: 1  Stack size: 131072
Current locks:
lock: 0xc02fb8:
lock: 0xc00aa8:
lock: 0xbfe148:
lock: 0xbfb998:
lock: 0xbf9658:
lock: 0xbf6318:
lock: 0xbf1618:
lock: 0xbee5b8:
lock: 0xbec1b8:
lock: 0xbe9708:
lock: 0xbe68c8:
lock: 0xbe3f98:
lock: 0xbe19c8:
lock: 0xbdf4c8:
lock: 0xbdd058:
lock: 0xbdaea8:
lock: 0xbd9378:
lock: 0xbd7c48:
lock: 0xbd6528:
lock: 0xbd4ed8:
lock: 0xbd2268:
lock: 0xbcfc08:
lock: 0xbcb4d8:
lock: 0xbc8278:
lock: 0xbc50c8:
lock: 0xbb1698:
lock: 0xbbfe58:
lock: 0xbbd308:
lock: 0xbbaf28:
lock: 0xbb81f8:
lock: 0xbb45b8:
lock: 0xb8b3c8:
lock: 0xb855a8:
lock: 0xb80628:
lock: 0xb71d18:
lock: 0xb6dd68:
lock: 0xb67d48:
 
Key caches:
default
Buffer_size:      33554432
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:            66
not flushed:             0
w_requests:             42
writes:                 29
r_requests:           2828
reads:                  66
 
 
handler status:
read_key:          892
read_next:         131
read_rnd           532
read_first:          6
write:          117641
delete               0
update:             32
 
Table status:
Opened tables:         42
Open tables:           37
Open files:            77
Open streams:           0
 
Alarm status:
Active alarms:   0
Max used alarms: 2
Next alarm time: 0
 
I'm not a DBA enough to know for sure if any of that is significant - but isn't that a high number of locks? Especially since I don't lock tables on this page?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP lying to me about results

Post by Eran »

This information is for the entire database, so it's not completely unreasonable. I don't see anything unusual so I'm out of options.. maybe Vlad has an idea
Post Reply