{"action":"created","issue":{"url":"https://api.github.com/repos/ci-bonfire/Bonfire/issues/1136","labels_url":"https://api.github.com/repos/ci-bonfire/Bonfire/issues/1136/labels{/name}","comments_url":"https://api.github.com/repos/ci-bonfire/Bonfire/issues/1136/comments","events_url":"https://api.github.com/repos/ci-bonfire/Bonfire/issues/1136/events","html_url":"https://github.com/ci-bonfire/Bonfire/issues/1136","id":92464533,"number":1136,"title":"MySQL explain update fails on older versions","user":{"login":"titaniumtroop","id":13068310,"avatar_url":"https://avatars.githubusercontent.com/u/13068310?v=3","gravatar_id":"","url":"https://api.github.com/users/titaniumtroop","html_url":"https://github.com/titaniumtroop","followers_url":"https://api.github.com/users/titaniumtroop/followers","following_url":"https://api.github.com/users/titaniumtroop/following{/other_user}","gists_url":"https://api.github.com/users/titaniumtroop/gists{/gist_id}","starred_url":"https://api.github.com/users/titaniumtroop/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/titaniumtroop/subscriptions","organizations_url":"https://api.github.com/users/titaniumtroop/orgs","repos_url":"https://api.github.com/users/titaniumtroop/repos","events_url":"https://api.github.com/users/titaniumtroop/events{/privacy}","received_events_url":"https://api.github.com/users/titaniumtroop/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-07-01T19:17:10Z","updated_at":"2015-07-02T13:29:54Z","closed_at":"2015-07-01T23:29:03Z","body":"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:\r\n\r\n<code>$explain = strpos($val, 'SELECT') !== false ? $this->CI->db->query(\"EXPLAIN {$val}\") : null;</code>\r\n\r\nUnfortunately, MySQL 5.0 doesn't permit EXPLAIN UPDATE.\r\n\r\nOne 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:\r\n\r\n<code>//EXPLAIN except for MySQL below 5.6 UPDATE and INSERT statements</code>\r\n<code>$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;</code>\r\n\r\nI submitted this issue to the CI repository as well."},"comment":{"url":"https://api.github.com/repos/ci-bonfire/Bonfire/issues/comments/118033801","html_url":"https://github.com/ci-bonfire/Bonfire/issues/1136#issuecomment-118033801","issue_url":"https://api.github.com/repos/ci-bonfire/Bonfire/issues/1136","id":118033801,"user":{"login":"mwhitneysdsu","id":1537529,"avatar_url":"https://avatars.githubusercontent.com/u/1537529?v=3","gravatar_id":"","url":"https://api.github.com/users/mwhitneysdsu","html_url":"https://github.com/mwhitneysdsu","followers_url":"https://api.github.com/users/mwhitneysdsu/followers","following_url":"https://api.github.com/users/mwhitneysdsu/following{/other_user}","gists_url":"https://api.github.com/users/mwhitneysdsu/gists{/gist_id}","starred_url":"https://api.github.com/users/mwhitneysdsu/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mwhitneysdsu/subscriptions","organizations_url":"https://api.github.com/users/mwhitneysdsu/orgs","repos_url":"https://api.github.com/users/mwhitneysdsu/repos","events_url":"https://api.github.com/users/mwhitneysdsu/events{/privacy}","received_events_url":"https://api.github.com/users/mwhitneysdsu/received_events","type":"User","site_admin":false},"created_at":"2015-07-02T13:29:54Z","updated_at":"2015-07-02T13:29:54Z","body":"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."}}