WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Is there a way to store all running SQL queries in a file (5 posts)

  1. riversatile
    Member
    Posted 5 months ago #

    Hi all,

    Is there a way to store all running SQL queries in a file (knowing that I don't have access to SQL server log files) ?

    I already tried to use WP_DEBUG, WP_DEBUG_LOG and SAVEQUERIES described here : http://codex.wordpress.org/Debugging_in_WordPress
    but it's not what I'm looking for. I not only want to display the SQL queries of the pages that are loaded on my screen, but gather ALL SQL queries of all the users on my website !

    I need to know why I get the message on SQL server (MariaDB) :
    User has exceeded the 'max_questions' resource (current value: 50000)
    (per hours)
    At this moment, if I refresh WordPress Dashboard, I'm redirected to my-server.com/wp-admin/install.php for a new install of WordPress (My database in unreachable).

    Well ! I know my host limits the number of SQL queries to 50000 per hours, but I want to know why I reach to 50000 queries.

    I know that print_r($wpdb->queries); is the begining of the solution, but I never found a way to store queries in a file.
    Can you help me to customize the following code to store queries in a file on my web server ?

    # Enable SAVEQUERIES
    define('SAVEQUERIES', true);
    
    # Display queries
    if (SAVEQUERIES) {
    echo "<!--\n";
    print_r($wpdb->queries);
    echo "\n-->\n";
    }
  2. riversatile
    Member
    Posted 5 months ago #

    UP please !

    Finaly, I found the solution by myself after more than 30 different websites !
    If this may help to some. The following code allows your WordPress to store [The page load Date/Time, number of SQL queries on that page] in separated rows !

    <?php
        // Custom Time Zone
        date_default_timezone_set('Europe/Paris');
        // Define the file name and location in the variable $log_file
        // Below, the path correspond to http://your-site/queries.txt
        $log_file = ABSPATH. "queries.txt";
        // Store the Date/Time of the page load in the variable $Now
        $Now = date("Y-m-d H:i:s");
        // Build the stored informations in the variable $Qdata
        $Qdata = $Now . "," . get_num_queries();
        // Save date of the variable $Qdata in the file $log_file
        file_put_contents($log_file, PHP_EOL . $Qdata , FILE_APPEND);
    ?>

    Now, I just have to wait some hours, and I should be able to see why and how my site exceeds more than 50000 querie an hour.
    We can also include Host IP address in the rows if we want to know if ban a suspicious IP address is necessary to limit queries.

  3. riversatile
    Member
    Posted 5 months ago #

    And if you want to store in a file SQL queries that you run on the WordPress Dashboard, put this code into your functions.php in your Theme folder :
    (This will not showing number of SQL queries in the footer. This just place the script in the Admin footer)

    /*
    #######################################
    Store in a file SQL queries I run via my WordPress Dashboard
    #######################################
    */
    add_action('admin_footer', 'my_admin_footer_function');
    function my_admin_footer_function() {
        // Custom Time Zone
        date_default_timezone_set('Europe/Paris');
        // Define the file name and location in the variable $log_file
        // Below, the path correspond to http://your-site/dashboard-queries.txt
        $log_file = ABSPATH. "dashboard-queries.txt";
        // Store the Date/Time of the page load in the variable $Now
        $Now = date("Y-m-d H:i:s");
        // Build the stored informations in the variable $Qdata
        $Qdata = $Now . "," . get_num_queries();
        // Save date of the variable $Qdata in the file $log_file
        file_put_contents($log_file, PHP_EOL . $Qdata , FILE_APPEND);
    }
  4. riversatile
    Member
    Posted 4 months ago #

    Hi,

    Here is an update of the previous script above.
    Changes :

    • Added the remote server/host IP address
    • Added the requested URL (path)
    /*
    #######################################
    Store in a file SQL queries I run via my WordPress Dashboard
    v1.2
    #######################################
    */
    add_action('admin_footer', 'my_admin_footer_function');
    function my_admin_footer_function() {
      // Custom Time Zone
      date_default_timezone_set('Europe/Paris');
      // Define the file name and location in the variable $log_file
      // Below, the path correspond to http://your-site/dashboard-queries.txt
      $log_file = ABSPATH. "dashboard-queries.txt";
      // Store the Date/Time of the page load in the variable $Now
      $Now = date("Y-m-d H:i:s");
      // Store the IP address of the remote server/host in the variable $Ip
      $Ip = $_SERVER['REMOTE_ADDR'];
      // Store the requested URL (path) in the variable $RequestedUrl
      $RequestedUrl = $_SERVER['REQUEST_URI'];
      // Build the stored informations in the variable $Qdata
      $Qdata = $Now . "," . $Ip . "," . $RequestedUrl . "," . get_num_queries();
      // Save data of the variable $Qdata in the file $log_file
      file_put_contents($log_file, PHP_EOL . $Qdata , FILE_APPEND);
    }
  5. Pam Blizzard
    Member
    Posted 3 months ago #

    Thank you for this! I wish I could buy you some coffee and pie, because I know how it is to search for an answer. Know that you have helped another very tired and weary WP'er :)

Reply

You must log in to post.

About this Topic