Thank you for your review, I really appreciate your feedback.
Where is the SQL error displayed? It is a little odd, because the plugin checks what version of MySQL you are using and should then use the correct syntax accordingly.
MySQL < 5.5: SET OPTION SQL_BIG_SELECTS = 1
MySQL >= 5.5: SET SQL_BIG_SELECTS = 1
Hi Joachim,
I noticed in the error logs when I was debugging some other stuff. I don’t have the logs any more but it’s easy to reproduce in a mysql console. The The issue is that the possible syntaxes changed twice: once from 5.4 to 5.5 and again from 5.5 to 5.6.
mysql 5.5:
mysql> set option SQL_BIG_SELECTS = 1;
Query OK, 0 rows affected (0.00 sec)
mysql 5.6:
mysql> SET OPTION SQL_BIG_SELECTS = 1;
ERROR 1064 (42000): 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 'OPTION SQL_BIG_SELECTS = 1' at line 1
The reason is that the SET OPTION syntax is deprecated (but functional) in MySQL5.5, but removed in 5.6. (see first sentence here).
In MySQL <= 5.5.2, the scope for SQL_BIG_SELECTS is SESSION. In MySQL >= 5.5.3, it is SESSION or GLOBAL (see here).
So lib/wp-content-aware-engine/core.php:344 should (IMHO) be changed to read something like:
$stmt = version_compare($wpdb->db_version(), '5.5', '>=')
? 'SET SESSION SQL_BIG_SELECTS = 1'
: 'SET SQL_BIG_SELECTS = 1'
;
$wpdb->query($stmt);
Hi Joachim. It looks like my last reply was dropped; I apologize if this is a double post.
The SET OPTION syntax that you’re using for >= 5.5 is incorrect. It was deprecated (but functional) in 5.5 (see here, second sentence) and removed in 5.6. Thus, in 5.6:
mysql> SET OPTION SQL_BIG_SELECTS = 1;
ERROR 1064 (42000): 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 'OPTION SQL_BIG_SELECTS = 1' at line 1
mysql> SET SESSION SQL_BIG_SELECTS = 1;
Query OK, 0 rows affected (0.00 sec)
The variable isn’t a user variable, so it’s scope has to be specified. It’s a SESSION variable, so the correct syntax is SET SESSION SQL_BIG_SELECTS = 1.
It’s a very small change: just change the word ‘OPTION’ to ‘SESSION’ in lib/wp-content-aware-engine/core.php:344.
Thanks for the links to the docs. Does it mean that removing OPTION would work for all versions [5.0;5.4] (current WordPress minimum requirement)?
I still don’t see why you get an error with OPTION now though, as the current code is:
$wpdb->query('SET'.(version_compare($wpdb->db_version(), '5.5', '>=') ? '' : ' OPTION').' SQL_BIG_SELECTS = 1');
Omitting SESSION would default to session scope as I read it, but it could be a good idea to include it for readability.
Hi Joachim,
Looks like it, yeah. This is what I get for 5.6.25:
mysql> SET SQL_BIG_SELECTS = 1;
Query OK, 0 rows affected (0.00 sec)
And for 5.5.45:
mysql> SET SQL_BIG_SELECTS = 1;
Query OK, 0 rows affected (0.00 sec)
So, I guess you could just remove the ternary statement. Not sure how that would behave in other versions, though; I assume it’s there to fix some bug somewhere.
This should be resolved in version 3.2.
