Okay
  Public Ticket #904527
Query Error
Closed

Comments

  •  2
    primalforce started the conversation

    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

  • [deleted] replied

    Hi Primalforce,

    Plugin just tries to execute every query that you input. We do not reset anything on our side. Maybe this was same temporary problem with server. Please inspect server error logs for more information and if you find anything suspicious get back to me and we will find a solution

  •   primalforce replied privately
  • [deleted] replied

    Hi Primalforce,

    For query like this we usually suggesting our users to create MySQL VIEW and use that as data source. Creating it in phpMyAdmin or other database management software is relatively easy process. WIth VIEW you shouldn't have this problem and some other problems that could occur with query like this should be omitted. 


  •  2
    primalforce replied

    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;
        }
    );
  • [deleted] replied

    Hi Primalforce,

    Thank you very much for sharing this with us.

    We will investigate this problem and come with solution for it although I still think that creating a VIEW from your query is more optimized solution.


  •   primalforce replied privately