0

NOTE: This is not a duplicate of What is maximum query size for mysql?. My issue appears to be specific to Doctrine and/or PHP, not MySQL.

My query looks like this:

SELECT COUNT(*) FROM post
WHERE username = :username
AND comment REGEXP '...giant regular expression...'

Note the giant regular expression does NOT include the text :username (which would probably confuse Doctrine).

My PHP is normal Doctrine DQL, something like

$connection->executeQuery($sql, ['username' => 'Frank Sinatra']);

I am getting the PDO DriverException SQLSTATE[HY093]: Invalid parameter number: no parameters were bound. Indeed, I can see from the error output that Doctrine did not attempt to put in the parameters I told it to.

After hours of debugging, I finally figured out this was somehow related to the length of the query itself. If the query string is under around 3,074 characters, it will run just fine. Furthermore, this issue only occurs for prepared statements. A query that's really long (over 3,074 characters) works fine if I don't need to bind any parameters.

To make it even more baffling, I only get this error on my local environment (PHP 7.2), and not production which is still on PHP 5.6. I don't know if PHP is the issue, but I can't think of anything else that would be it. I've ran composer update to get the latest version of dependencies (Doctrine, etc.) for PHP 7.2.

I do not believe it is due to a low value for the MySQL max_allowed_packet option, or anything else in MySQL, because I can run the query manually (putting in the parameter values) in the MySQL console and it works. So, I'm left to blame either Doctrine or PHP itself.

Anyone heard of this issue? Is there some secret setting for max query length in PHP or Doctrine, that would cause Doctrine to get confused in this way?

MusikAnimal
  • 2,286
  • 2
  • 22
  • 28

1 Answers1

1

What does the regex look like and how are you generating it? Does it have a question mark? If so, it's going to expect that you bind parameters for those.

If you're not already, you should be binding the regex value too:

$sql = 'SELECT COUNT(*) FROM post WHERE username = :username AND comment REGEXP :comment_regex;';

$connection->executeQuery($sql, [
    'username'      => $username,
    'comment_regex' => $comment_regex,
]);

Side note: I recommend using ids instead of usernames for foreign keys for performance and practicality (no fun if you have to change the username).

dtbarne
  • 8,110
  • 5
  • 43
  • 49
  • Sorry for the late reply. "If you're not already, you should be binding the regex value too" -- that did it!!! Thing is, this was working fine before (same exact regex), but broke in PHP 7.2 or perhaps some Doctrine update. Binding the regex seems cleaner anyway. Thank you. The post/username/comment thing was just an example. My schema doesn't actually look like this :) Thanks again. – MusikAnimal Sep 16 '18 at 02:25