#31668 [Com]: multi_query works exactly every other time

ID: 31668 Comment by: ingmar at hackthenet dot org 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: 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. Previous Comments: ------------------------------------------------------------------------ [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] georg@php.net 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. ------------------------------------------------------------------------ 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