php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #31668 multi_query works exactly every other time
Submitted: 2005-01-23 14:26 UTC Modified: 2005-04-27 14:14 UTC
Votes:11
Avg. Score:5.0 ± 0.0
Reproduced:10 of 10 (100.0%)
Same Version:7 (70.0%)
Same OS:1 (10.0%)
From: exaton at free dot fr Assigned: andrey (profile)
Status: Closed Package: MySQLi related
PHP Version: 5CVS-2005-04-26 OS: WinXP
Private report: No CVE-ID: None
 [2005-01-23 14:26 UTC] exaton at free dot fr
Description:
------------
Using PHP 5.0.4-dev WinXP snapshot from Jan 23 2005 10:14:07, running Apache 2.0.52 and MySQL 4.1.9 .

I've seen other bugs possibly relating to this issue, e.g. http://bugs.php.net/bug.php?id=29272 or http://bugs.php.net/bug.php?id=28860 , but I think I have something more precise to describe. The querying works half the time anyway, so it's not an evident MySQL error.

I find that when I run a MySQLi multi_query, the first time the results are perfectly correct, then on browser reload the multi-query fails with an SQL error pertaining to the query concatenation. Reload again, fine. Reload again, same error. Etc.

I'm doing this : (table 'faq' is simply an AUTO_INCREMENT 'indx' field, then 'question' and 'answer' TEXT fields) :

$SQL = new mysqli(/*...connection information...*/);

$SQL -> multi_query('
  SELECT indx FROM faq;
  SELECT question, answer FROM faq
');

do {
  $res = $SQL -> store_result();
	
  if ($SQL -> errno == 0)
  {
    while ($arr = $res -> fetch_assoc())
      print_r($arr);
    $res -> free();
  }
  else
    echo $SQL -> error; // (*)
}
while ($SQL -> next_result());

$SQL -> close();

So, a first time, I get a bunch of arrays from the first resultset :
Array
(
    [indx] => 1
)
followed by the same number of arrays from the second resultset :
Array
(
    [question] => This is a question
    [answer] => This is an answer
)
And that is fine.

Then, browser reload, and I get no arrays at all but a _single_ error instead, which I show with line (*) :

"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 '; SELECT question, answer FROM faq' at line 1 "

Browser reload, back to my nice arrays. Browser reload again, error message. It is _very_ rare that a same situation should occur twice in a row instead of the alternation.

I've tried concatenating the queries onto a single line, even with no space at all behind the semicolon. All the same.

So, despite my running MySQL 4.1.9, is this mysqli_multi_query() related, or is something up with the database server ?

Thanks.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-01-23 14:39 UTC] exaton at free dot fr
OK, realised that of course the $SQL -> error is set (when it is set) right after multi_query() is called. So that should be :

if ($SQL -> multi_query(...))
{
   do { ... } while (...);
}
else
  echo $SQL -> error; // (*)

But it works out to exactly the same thing. I understand I would notice a difference only if I were looking out for SQL errors in intermediate queries sent by multi_query(), making those errors accessible with next_result() calls. Whatever -- N/A here as far as I can see.
 [2005-01-23 16:39 UTC] exaton at free dot fr
Hilarity continues :

First off I copy-pasted a second iteration of the code above right after it, and I constate as expected that the first run through the code does not work, whereas the second one does (alternation).

The FUN thing is, that if I continue my copy-pasting until I have n copies of that code block in succession, then the first one still fails 9 times out of 10, with the same error, and then all subsequent blocks work correctly.

Next up, I decided to give up on resultsets and do other things in my multi queries ; consider the following block of code :

if (!$SQL -> multi_query('
  INSERT faq SET question = "q1", answer = "a1";
  INSERT faq SET question = "q2", answer = "a2";
  INSERT faq SET question = "q3", answer = "a3"
'))
  echo 'ERR i : #'.$SQL -> errno.' : '.$SQL -> error.'<br />'; (*)

The i on line (*) is hard-coded to correspond to the i-th block in a new copy-paste sequence. Indeed, the minimum number of times you want to copy-paste that block of code is twice, for a total a three blocks. Then :

The first block fails with the same specious concatenation problem as before :

"ERR 1 : #1064 : 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 '; INSERT faq SET question = "q2", answer = "a2"; INSERT faq SET question =' at line 1"

Then the second block executes correctly, no error reported.

Then the third block manifestedly executes correctly (I do end up with TWO sets of [q1,a1 ; q2,a2 ; q3,a3] in my table), BUT multi_query() must return FALSE because I get :

"ERR 3 : #2013 : Lost connection to MySQL server during query"

And THEN, if I try to do anything else SQL-related, like for example a simple :

$SQL -> query('DELETE FROM faq WHERE question = "q1"');

Then I log a very hilarious error for that query (which does not get executed) :

"#2006 : MySQL server has gone away"

I love that.

OK, so I thought, let's look for a simple way of stopping a FIRST multi_query from failing. Well all it takes is a very simple query to "give the mysqli connection confidence" as I see it. If I add :

$SQL -> query('SELECT 1');

just after $SQL = new mysqli(...), then not only does that query not fail, but there is no longer the problem of the first multi_query failing :

- in my initial description @ 2:26pm CET, the multi_query() always works, never mind how many browser reloads.

- in this new situation with the INSERT multi_queries, the situation is TRANSLATED by 1 :

  - The first multi_query executes correctly
  - The second one executes correctly but I get the 'connection lost during query' error
  - The third one fails, because 'MySQL has gone away'.

In the place of the third multi_query, a simple query() also fails in the same manner, and so does a prepare(). $SQL -> host_info gave me correct info, however, so the $SQL object still appears to be partially valid, it just can't query the server anymore.

---------------------------------------------
---------------------------------------------

RECAP :

* A MySQLi connection appears to need to execute a simple query() before it will reliably execute multi_queries. If multi_queries are asked for immediately, the first one of them will fail.

* Whereas this problem did not appear with SELECT multi_queries, INSERT multi_queries present the following effect : the first one is fine, the second one works but makes the MySQLi object lose it's ability to further query the database server.

I hope you have as much fun solving this as I appear to be having researching it. Good luck and thank you again.
 [2005-02-11 16:15 UTC] georg@php.net
Does this problem still occur when you disable MySQL's 
query cache? 
 [2005-02-22 12:09 UTC] camka at email dot ee
setting query_cache_type to OFF didn't change anything. still the same error.
 [2005-04-27 11:57 UTC] andrey@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5-STABLE-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.0-win32-latest.zip

Produces an error in CLI with :
<?php
$SQL = new mysqli("localhost", "andrey", "", "test");

$res = $SQL->multi_query('
  SELECT indx FROM faq;
  SELECT question, answer FROM faq
');
var_dump($res, $SQL -> error, __LINE__);
do {
  $res = $SQL->store_result();
  var_dump($SQL -> error, __LINE__);
	
  if ($SQL->errno == 0) {
    while ($arr = $res->fetch_assoc())
      print_r($arr);
    $res->free();
  }
} while ($SQL->next_result());
var_dump($SQL -> error, __LINE__);

$SQL->close();
var_dump($SQL->error, __LINE__);

$SQL2 = new mysqli("localhost", "andrey", "", "test");

$res = $SQL2->multi_query('
  SELECT indx FROM faq;
  SELECT question, answer FROM faq
');
var_dump($res, $SQL2->error, __LINE__);
do {
  $res2 = $SQL2->store_result();
  var_dump($SQL2->error, __LINE__);
	
  if ($SQL2->errno == 0) {
    while ($arr = $res2->fetch_assoc())
      print_r($arr);
    $res2->free();
  }
} while ($SQL2->next_result());
var_dump($SQL2->error, __LINE__);

$SQL->close();
var_dump($SQL2->error, __LINE__);

?>
 [2005-04-27 14:14 UTC] andrey@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

fixed :)
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 07:01:29 2024 UTC