Syntax between mySQL versions

Question

I've got two environments using the same bit of code. The two environments aren't matched up perfectly (nothing I can do about that immediately). It works fine in one but fails in the other, despite the fact the same data is in both tables. I've tried messing around with the formatting some, generally created more errors than I solved. One is running 5.0.95 mysql and the other is running a 5.5.29. What's the syntax difference between the two that I'm missing, and how can I make it complaint with both?

// Code I can easily edit for reasons starts here
$sql = <<< SQL
SELECT WIDGET FROM TABLE WHERE FOO=:foo AND BAR=binary(:bar);
SQL;
// Code I can easily edit for reasons ends here
// Code I can't reliably mess with for reasons starts here
$db = pdo_dbopen();
$pdo = $db->prepare( $sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY) );
$pdo->bindValue(":foo", "kitten");
$pdo->bindValue(":bar", "hugs");

print_r($pdo); // First Print
$pdo->execute();
$my_info = $pdo->fetch(PDO::FETCH_ASSOC);
print_r($my_info); // Second Print

I can't put the print_r on the other server, but on the non-functioning one (at First Point), shows...

PDOStatement Object([queryString]=> SELECT WIDGET FROM TABLE WHERE FOO=:foo AND BAR=binary(:bar);)

For the second print print, I get just an empty result, despite confirming the value is there in phpmyadmin.

I feel maybe this should be populated with the bind values, and I don't know why it's not in one version but it is in the other?

It's the 5.5.29 that's failing, and not really getting any kind of error message. — lilHar, Mar 02 '16 at 23:58
Well, I"m technically getting an array. ([0]=>00000[1]=>[2]=>) But looking it up, it really doesn't mean much. Which means "success" technically, but it's not returning the appropriate values from the DB. — lilHar, Mar 03 '16 at 00:31
`:foo` and `:FOO` are not the same ;) Perhaps one of the versions is a bit ore flexible. But either way, cases should match! Also, you're missing `$result = $pdo->fetchAll();` — Alex Tartan, Mar 03 '16 at 07:51
Good catch, Alex. That's not the problem with my actual code, but it is a problem with the code I posted here. (I have a habit of typing out server variables upper_case and it carried over). I'll fix it on here. I do have a fetch in there, but it came later. I'll add it. — lilHar, Mar 03 '16 at 16:51
You might want to check https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html it contains changes that affect upgrades, known issues and incompatible changes. Maybe you will find something that will point you in the right direction, give you a clue, solve the problem. Other than that I would suggest upgrading the 5.0 one asap, I think it's not even supported anymore. — , Mar 03 '16 at 18:30

score 0 · Accepted Answer · edited May 23 '17 at 11:50

Finally found a solution to my issue (thanks to everyone who helped). The solution was actually quite odd...

First, I had to get deeper into error checking. I had to turn on logging for the actual evaluated queries.

(See here: How to enable MySQL Query Log? - Alexandre Marcondes's answer in particular... seemed to help to actually set the values while in the databases themselves in phpadmin and not the home SQL... could be placebo effect though making me think that).

Checking the databases, one was evaluating the query as

SELECT WIDGET FROM TABLE WHERE FOO="kitten" AND BAR=binary("hugs");

The other was evaluating the query as

SELECT WIDGET FROM TABLE WHERE FOO=\"kitten\" AND BAR=binary(\"hugs\");

Switching out double-quotes for single quotes for setting the variables in the query fixed the problem... weird, but problem is now fixed. Apparently there was a weird setting buried in Apache/PHP/mySQL that was causing it.

Syntax between mySQL versions

1 Answers1