1

Can you confirm if this is a bug... I cannot work out if it's MariaDB (10.3.34 or 10.7.3), PHP (mysqlnd), or a mistake I've made:

<?php

  $db = new mysqli('host', 'user', 'pass', 'database');

  // $db->query('SET in_predicate_conversion_threshold=2000;');

  $count = 1000;

  $params = implode(',', array_fill(0, $count, '?'));
  $types = str_repeat('i', $count);
  $values = array_fill(0, $count, 3);

  $statement = $db->prepare('EXPLAIN SELECT id FROM my_table WHERE id IN (' . $params . ')');
  $statement->bind_param($types, ...$values);
  $statement->execute();

  $result = $statement->get_result();

  var_dump($db->error);
  var_dump($statement->error);
  var_dump($statement->errno);
  var_dump($result);

?>

This hangs when it gets to $statement->execute(), and does not show any error message.

If you remove EXPLAIN from the SQL (to make a normal SELECT query), the $statement->get_result() fails (returns false or exception, depending on mysqli_report), and you get the error message "Commands out of sync; you can't run this command now".


The 1,000 parameter limit within IN() seems to be related to in_predicate_conversion_threshold; and you can have multiple IN() operators with 999 parameters each.

2
  • "Commands out of sync; you can't run this command now" refers to the calls before your failing case and/or multi-statement executes and/or stored procs.
    – Rick James
    Commented Mar 14, 2022 at 17:49
  • Use the "general log" to see what the constructed query looks like.
    – Rick James
    Commented Mar 14, 2022 at 17:50

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.