MySQL explain update fails on older versions

[titaniumtroop]

I have an UPDATE statement that compares to a value pulled from an aliased table. The Profiler library adds an EXPLAIN statement to any query containing a SELECT statement at line 182:

$explain = strpos($val, 'SELECT') !== false ? $this->CI->db->query("EXPLAIN {$val}") : null;

Unfortunately, MySQL 5.0 doesn't permit EXPLAIN UPDATE.

One fix is to use "select" instead of "SELECT", as the line of code is case-sensitive, but that seems like it is a bug that could be changed at any time. The better solution would be to exclude the explain statement for MySQL versions below 5.6 for INSERT and UPDATE queries, as follows:

//EXPLAIN except for MySQL below 5.6 UPDATE and INSERT statements
$explain = (stripos($val, 'SELECT') !== false && !(stripos($db->dbdriver, 'mysql') !== FALSE && $db->conn_id->server_version < 56000 && preg_match('/UPDATE|INSERT/i', $val))) ? $this->CI->db->query("EXPLAIN {$val}") : null;

I submitted this issue to the CI repository as well.

[mwhitneysdsu]

For better or worse, this is specific to Bonfire, since CI's profiler doesn't add the additional information for queries provided by the explain call. If you get a chance, try the commit referenced above to see if that fixes the issue for you. The differences from the code you included should be minor.

[titaniumtroop]

I tried the commit and it works nicely. Thanks. I closed the CI issue earlier after noticing the Bonfire profiler was different.

[mwhitneysdsu]

Thanks for checking it out. I included some updates to the syntax highlighting for the SQL in the Queries tab in the previous commit, and added one more in the commit above. I'll have one more commit that references this issue ready shortly, for 0.7.5, but the code will be the same as for 0.8.1.

[titaniumtroop]

One more thing: I read a little more about this, and the MySQL limitation extends to DELETE also.

[mwhitneysdsu]

On one hand, I want to say anyone using select and delete in the same SQL statement is asking for trouble. On the other, I'm thinking that it's much easier to add |DELETE into the preg_match() than to defend the idea of not adding it to the code.

[mwhitneysdsu]

Those commits should take care of DELETE in 0.8.1 and 0.7.5.

[titaniumtroop]

If you're that reckless with your code, you should definitely have to "EXPLAIN" yourself. Da dum tsss.