Is there a way to store all running SQL queries in a file

  • Resolved Riversatile

    (@riversatile)


    10 years, 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";
}
Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter Riversatile

    (@riversatile)

    10 years, 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.

    Thread Starter Riversatile

    (@riversatile)

    10 years, 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);
}
    Thread Starter Riversatile

    (@riversatile)

    10 years, 5 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);
}
    Pam Blizzard

    (@pdblizzard)

    10 years, 4 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 🙂

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Is there a way to store all running SQL queries in a file’ is closed to new replies.