0

I have a nice function called lnk() that I have been using for a very long time that connects to MySQL. Recently I decided to add to it the mysqli_ping call to check the connection and reconnect if need be as I was experiencing issues with a daemon (running 24/7) that kept losing connection to MySQL after long periods of inactivity.

After implementation I started having problems with multi-queries. Connection to MySQL is lost at the moment the ping is sent if it is sent after mysqli_multi_query unless mysqli_store_result comes before the ping.

$mq = mysqli_multi_query(lnk(), "SHOW PROCESSLIST;");
mysqli_next_result(lnk());
$result = mysqli_store_result(lnk());
while($row = mysqli_fetch_row($result)) {
  print_r($row);
}

Any ideas why this is happening?

Please do not suggest to add a variable to the library to ignore connection check in those cases. I have already thought of that and it's not good enough. I need to know exactly why is this happening and what the ping does to my connection in this case.

transilvlad
  • 13,974
  • 13
  • 45
  • 80
  • You probably need to re-use the same `$link` across the various calls. If the connection is reset between `mysqli_multi_query` and the others, `lnk()` will give you a new link ... which in turn breaks `mysqli_next_result`. – EthanB Aug 31 '12 at 16:14
  • The break happens at the ping not at the next_result. – transilvlad Aug 31 '12 at 16:19
  • Regardless of that, I think you should probably re-use the same link value in the various functions. – EthanB Aug 31 '12 at 16:21
  • As I mentioned, I added am option to bypass the ping in the function. This means that the function simply returns the connection exactly as you mentioned. I do not need a solution to make it work. I already have it as I mentioned. I need an answer why this is happening. What is the ping really doing? – transilvlad Aug 31 '12 at 16:27

1 Answers1

1

Under the covers:

... which appears to delete your results.

This means you should only reconnect once (before calling multi_query). Something like this:

$link = lnk(); //once
$mq = mysqli_multi_query($link, "SHOW PROCESSLIST;");
mysqli_next_result($link);
$result = mysqli_store_result($link);
while($row = mysqli_fetch_row($result)) {
  print_r($row);
}
EthanB
  • 4,239
  • 1
  • 28
  • 46
  • Did you read the italic text? I have a parameter I pass to by-pass the connection verification and it works just fine. The ping is only called in other cases that are not multi-query. My problem is not that I don't know how to make the code work. My problem is why is the ping doing this in case of a multi-query? Is the ping actually something other then a ping? Like a particular command? – transilvlad Aug 31 '12 at 17:28
  • Sorry, I assumed by "parameter" that it defaulted to "do the ping". (Still, it doesn't hurt to explicitly re-use the same link). The docs say that `ping` might auto-reconnect for you--though I don't think that's happening here, because your multi-query is successful (right?). IIRC, you can't have multiple statements open at the same time in PDO. It possible `ping` does something like a `SELECT 1` under the covers, which would probably cause a multi-query to blow up. – EthanB Aug 31 '12 at 17:42
  • I presume the same, but does anybody know what? Or an alternative way to check the connection without sending a command? I do not use the ping to reconnect but to check the connection in a infinite controlled loop that does some operations every set interval. – transilvlad Aug 31 '12 at 19:46
  • [mysqli_ping](https://github.com/php/php-src/blob/master/ext/mysqli/mysqli_api.c#L1698) depends on [mysql_ping](https://github.com/php/php-src/blob/master/ext/mysql/php_mysql.c#L2661) depends on [PHPMY_UNBUFFERED_QUERY_CHECK](https://github.com/php/php-src/blob/master/ext/mysql/php_mysql.c#L369) ... – EthanB Aug 31 '12 at 20:07
  • I don't see anything eggregious like a `SELECT 1`, but it looks like it's throwing away any active results: https://github.com/php/php-src/blob/master/ext/mysql/php_mysql.c#L381 – EthanB Aug 31 '12 at 20:10
  • Edit your answer with the info in your last comment and I will accept your answer. – transilvlad Aug 31 '12 at 22:12