mysqli_multi_query doesn't work reliable with mysql conditional comment queries

Question

I am having an issue with mysql conditional comment queries where errors are reported with no syntax error. It works in the case that at least one of the queries actually executes with a conditional.

I am using php 5.6.24 and mysql 5.5.52-cll

Example 1 (Success):

<?php
$conn = mysqli_connect("127.0.0.1", "aaatex_phppos", "phppos", "aaatex_phppos2");

$test1 = "
/*!40000 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('supports_full_text', '0') */;
/*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('supports_full_text', '1') */;";

mysqli_multi_query($conn,$test1);
print_r(mysqli_error_list($conn));
?>

supports_full_text value is 0 as expected.

Example 2 (Failure):

<?php
$conn = mysqli_connect("127.0.0.1", "aaatex_phppos", "phppos", "aaatex_phppos2");

$test2 = "
/*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('test', '0') */;
/*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('test', '1') */;";

mysqli_multi_query($conn,$test2);
print_r(mysqli_error_list($conn));

Errors received:

Array
(
    [0] => Array
        (
            [errno] => 1064
            [sqlstate] => 42000
            [error] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
    /*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('test', '1' at line 1
        )

)

Example 3 (Failure; but appears like success (see below message):

<?php
$conn = mysqli_connect("127.0.0.1", "aaatex_phppos", "phppos", "aaatex_phppos2");

$test3 = "
/*!40000 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('test', '0') */;
/*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('test', '0') */;
/*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('test', '1') */;";

mysqli_multi_query($conn,$test3);
print_r(mysqli_error_list($conn));

test value is 0 as expected.

Is this a bug in php or something I am doing wrong?

EDIT:

NOTE: I found that when the query fails it STOPS processing the rest of the file. So example 3 still has errors in the 2nd and 3rd queries; I just didn't catch all errors. The 40000 query works; but anything that does NOT run for current mysql version fails as a syntax error.

Answer 1

You have a misunderstanding here. Your mysql version is 5.5.52. That means the results you are getting are correct.

When you say /*!40000 ... */ in your query, you are saying that this query should only get executed in mysql versions higher than 4.0.0. Similarly, /*!50604 ... */ means the mysql version should be higher than 5.6.04 for this query to get executed. Remember, these numbers relate to mysql version. Not to php version.

In the first test of yours, first query gets executed fine since your mysql version is larger than 4.0.0. But the second query gets skipped since your mysql version is lower than 5.6.04. This is what happens in the other two tests as well.

But I'm unable to explain why you are getting syntax errors like,

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
    /*!50604 REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('test', '1' at line 1

in the 2nd test. May be the query you put here is not the actual query you executed. Can you check on that? As I also did all those tests(I have mysql 5.5 as well and php 5.6), I didn't come across any error. I only saw the queries with higher version requirements not being executed.

For additional reading refer this article. Hope my answer helped you.

Update

By looking at other answers as well, seems like you are facing a rare bug. Try updating your mysql version. If the issue still persists, it can be a bug with mysql API.

Answer 2

Yes, looks like it's a bug in multi_query(). It seems this function doesn't like misplaced semicolons.

$mysqli->multi_query(";SELECT 1;");

will give you the same syntax error. As well as SELECT 1;;SELECT 2; query.

Which makes whatever conditional comment evaluated to false produce an extra semicolon which results in a syntax error.

Update.
It turns that it's not mysqli but mysql API is being very picky about semicolons: this problem can be reproduced in PDO as well. Looks like I'll file a bug on mysql tracker.

Answer 3

Multi-statement is a dangerous tool; avoid it.

Anyway, do you really need it? You can do multiple rows in a single statement:

REPLACE INTO `phppos_app_config` (`key`, `value`)
     VALUES
         ('test', '0'),
         ('test', '1')

BTW, REPLACE is an old command; INSERT ... ON DUPLICATE KEY UPDATE ... has mostly replaced it. Consider that instead.

Or maybe you could use simply INSERT IGNORE ....

Please explain your purpose of using /*!50604 ... */. It mostly exists to give some form of backward compatibility in tools; it is rarely useful in a production environment.