Extended database commands yields "Mysql has gone away" errors

[eluser]jonnyjon[/eluser]
I needed to use multi-query with MySQLi but it doesn't appear to be supported in CI for some reason. So I use this syntax:

$this->db->conn_id->multi_query($query);

Which works ... but if I try to access to database after that query for anything I get an "MySQL has gone away error" error.

Anyone know how to work around this?

[eluser]Pascal Kriete[/eluser]
I've never done this, or encountered it, but custom function calls may solve your problem.

[eluser]AgentPhoenix[/eluser]
That's my favorite MySQL error. Haha!

[eluser]jonnyjon[/eluser]
OK, this is solved (sorta).

You can indeed you custom function calls which makes the syntax a little nicer but has no effect on the error.

The problem is that when you do a mysqli multi_query the connect remains open unless you "do something with the results". Even if you are not expecting any results. Weird.

Here's my fix/workaround.

$query = 'DELETE from table1; DELETE from table2';
$this->db->conn_id->multi_query($query);
while($this->db->conn_id->next_result()) {if ($result instanceof mysqli_result) {$result->free();}};

The CI custom function way would look like this:

$query = 'DELETE from table1; DELETE from table2';
$this->db->call_function('multi_query', $this->db->conn_id, $query);
while($this->db->conn_id->next_result()) {if ($result instanceof mysqli_result) {$result->free();}};

[eluser]Sam Dark[/eluser]
It's due the query is takes really huge amount of memory to execute.

Find max_allowed_packet in your mysql config and change it to 8M, 16M or so.