ID: 31668
User updated by: exaton at free dot fr
Reported By: exaton at free dot fr
-Status: Feedback
+Status: Open
Bug Type: MySQLi related
Operating System: WinXP
PHP Version: 5CVS-2005-01-23 (dev)
New Comment:
I'm afraid I can no longer help out here ; I needed to upgrade the DB
layer my comany's web application framework fast, and have therefore
switched to PostgreSQL in the meantime.
camka at email dot ee, I leave this in your hands...
Previous Comments:
------------------------------------------------------------------------
[2005-02-11 16:15:00] [EMAIL PROTECTED]
Does this problem still occur when you disable MySQL's
query cache?
------------------------------------------------------------------------
[2005-02-11 10:41:27] camka at email dot ee
I'm facing the same problem on Linux environment.
Looking forward to get the bug fixed.
------------------------------------------------------------------------
[2005-01-23 16:39:31] 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-01-23 14:39:33] 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 14:26:43] 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.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=31668&edit=1
