Bug

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.