ID: 31668
Comment by: camka at email dot ee
Reported By: exaton at free dot fr
Status: Open
Bug Type: MySQLi related
Operating System: WinXP
PHP Version: 5CVS-2005-01-23 (dev)
New Comment:
I used exaton at free dot fr 's code to reproduce the problem. And I
succeeded.
The following code works just as it was described above, giving the
"You have an error in your SQL syntax" error or workind just fine.
<?php
$SQL = new mysqli(/* ... */);
$SQL -> multi_query('SELECT 1;SELECT 2');
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();
?>
setting SQL_NO_CACHE directive didn't do the thing as ingmar at
hackthenet dot org suggested. The query_cache_size server variable
value is 0.
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
and finally: explicite calling the query method before multy_query
$SQL->query('select 343'); causes the "syntax error" error not to
occure anymore.
i'll try to set query_cache_type to OFF value and share the results
soon.
Previous Comments:
------------------------------------------------------------------------
[2005-02-12 13:25:06] ingmar at hackthenet dot org
hello,
i had a very similar problem.
The MySQL-Server hung up (SHOW PROCESSLIST says process is in the
sleep-state) every time I refreshed the page where $db->multi_query()
was called.
But I found a workaround at least for SELECT-Queries. Simply use
SELECT SQL_NO_CACHE * FROM tbl
instead of
SELECT * FROM tbl.
------------------------------------------------------------------------
[2005-02-11 19:11:33] exaton at free dot fr
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...
------------------------------------------------------------------------
[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.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/31668
--
Edit this bug report at http://bugs.php.net/?id=31668&edit=1
