PHP lying to me about results
Moderator: General Moderators
PHP lying to me about results
~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.
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.
Re: PHP lying to me about results
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?
EDIT: Try breaking the query by putting jibberish in it, does it throw an error then?
Re: PHP lying to me about results
- 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:
- Breaking the query does generate an error
- The query is:
Code: Select all
UPDATE
`response`
SET
`number` = 89
WHERE
`response_id` = 172405Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: PHP lying to me about results
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.
Re: PHP lying to me about results
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
Try using FirePHP to check exactly what is ran against the database
Re: PHP lying to me about results
- 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.
- 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.
Re: PHP lying to me about results
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
Re: PHP lying to me about results
That doesn't show anything surprising.
This is the code that builds the query:
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.
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;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.
Re: PHP lying to me about results
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.
Re: PHP lying to me about results
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?
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.
Re: PHP lying to me about results
Is it possible to see some code?
Also, dump the mysql connection itself, see what it contains
Also, dump the mysql connection itself, see what it contains
Re: PHP lying to me about results
The results of var_dump-ing the mysqli object from the DAL inside the function that's causing problems:
The results of var_dump-ing the mysqli object from the file I'm viewing
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
object(mysqli)#5(0){
}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)
}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.
Re: PHP lying to me about results
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
Re: PHP lying to me about results
The ping ran successfully - but no change.
Here's the result of dumping the debug info:
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?
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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: PHP lying to me about results
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