Hi. For a long time and until today i was using a MySQL query with no issues with the wpdatatables plugin. This morning when we tried to open the report the table was empty. I opened the wpdatatable query and it was giving an error that said we had to use

SET SQL_BIG_SELECTS=1  before the query because the MAX_JOIN_SIZE limitation. 

I tried to add the 

SET SQL_BIG_SELECTS=1 but wpdatatables wasn't accepting it always giving a sintax error.

A few mins ago i tried to connect wpdatatables to an slave db i have externally and i checked a couple of reports working correctly. I went back and selected the local wordpress database. Tried the report that wasn't working and suddenly started working correctly no errors about the MAX_JOIN_SIZE or SET SQL_BIG_SELECTS=1. 

Is there any possibility that the plugin reset something that suddenly the report started working? I checked with Pagely (our server hosting provider) and the MAX_JOIN_SIZE is set to super big value already.

Any advice will be appreciated.

Thanks

Francisco Cabrera

Hi Miljko. 

After having again issues today with the report and the server limiting the query due to the max_join_size the Pagely support agent Oscar Gomez wrote a plugin to fix the issue. It could be nice for people that have the same issue to create an option within the query admin screen where you can include this on any individual query.

I tried to add the SET SQL_BIG_SELECT=1; in the query but wpdatatable plugin didn't let me saying that there was a sintax error in the query. Thats when the support team developed this function. 

/*
 Plugin Name: wpdatatables Tweaks
 Plugin URI: https://pagely.com
 Description: Enables SQL_BIG_SELECTS for wpdatatables queries.
 Author: pagely.com
 Version: 0.0.1
 Author URI: https://pagely.com
 */

add_filter(
    "wpdatatables_filter_mysql_query",
    function ($query) {
        global $wpdb;
        $wpdb->query("SET SQL_BIG_SELECTS=1");
        // error_log("[Debug] Filter triggered");
        return $query;
    }
);