CODEWITHSUNDEEP
mysql
WonderLand
WonderLand

Reputation: 5674

Run a query foreach databases (mysql)

I'm looking for a straight way to run a query on all databases hosted on my mysql server.

I have a bunch of Magento installations and I want to truncate all Magento log table on all databases:

I think it something very easy to accomplish in mysql but I cannot find a straight answer/solution.

*UPDATE *
According to @Ollie Jones it is not possible to do it without a STORE PROCEDURE or a server side language ( PHP or whatever )

UPDATE 1
I choose to follow the PHP approach (@samitha) for 2 reasons:

  1. STORE PROCEDURE looks more complicated
  2. Query on 'information_schema' table is very slow ( at least if you have many DB/TABLES)

Upvotes: 9

Views: 22446

Answers (4)

O. Jones
O. Jones

Reputation: 108696

  SELECT DISTINCT SCHEMA_NAME AS `database`
    FROM information_schema.SCHEMATA
   WHERE  SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql')
   ORDER BY SCHEMA_NAME

gets you a list of all the non-MYSQL databases on your system.

  SELECT TABLE_SCHEMA AS `database`,
         TABLE_NAME AS `table`
    FROM information_schema.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
   ORDER BY TABLE_SCHEMA, TABLE_NAME

gets you a list of all the actual tables (excluding SYSTEM VIEWs like the TABLES table, and user-defined views) in all the databases.

Then, you should implement logic in your program to ensure that, for each database, it really is a Magento database before you truncate certain tables. Otherwise, you might become a despised person among your co-workers. :-)

Edit

Here's a stored procedure.

You need to edit it to do exactly what you need it to do; in particular, it counts rows rather than truncating tables, and it doesn't contain the correct list of log tables. (It would be irresponsible for me to publish such a wildly destructive stored procedure; you should edit it yourself to do the destructive part.)

DELIMITER $$
DROP PROCEDURE IF EXISTS `zap_magento_logs`$$

CREATE PROCEDURE `zap_magento_logs`()
BEGIN

    -- declare variables for database and table names
    DECLARE dbname VARCHAR(128) DEFAULT '';
    DECLARE tbname VARCHAR(128) DEFAULT '';

    DECLARE done INTEGER DEFAULT 0;

    -- declare cursor for list of log tables
    DECLARE log_table_list CURSOR FOR 
      SELECT TABLE_SCHEMA AS `database`,
             TABLE_NAME AS `table`
        FROM `information_schema`.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE'
         AND TABLE_NAME IN 
         (
            'log_customer',
        'log_visitor',
        'log_visitor_info',
        'log_url',
        'log_url_info',
        'log_quote'
         )
       ORDER BY TABLE_SCHEMA, TABLE_NAME;

    -- declare NOT FOUND handler
        DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET done = 1;

    OPEN log_table_list;

    log_table: LOOP

        FETCH log_table_list INTO dbname, tbname;

        IF done = 1 THEN
        LEAVE log_table;
        END IF;

        -- create an appropriate text string for a DDL or other SQL statement
        SET @s = CONCAT('SELECT COUNT(*) AS num FROM  ',dbname,'.',tbname);
        PREPARE stmt FROM @s;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt; 
    END LOOP    log_table;
    CLOSE log_table_list;

END$$

DELIMITER ;

You run this by issuing the SQL command

  CALL zap_magento_logs();

Upvotes: 15

Ryan Shillington
Ryan Shillington

Reputation: 25107

I didn't feel like writing code to solve this so I found a different solution. I wrote SQL that generates the SQL that I need. So I saved the following to a file called createSomeSQL.sql:

SET sql_mode='PIPES_AS_CONCAT';

select 
'truncate table ' || dbs.database || '.someLogTable;'
 as ''
from  (SELECT DISTINCT SCHEMA_NAME AS `database`
         FROM information_schema.SCHEMATA
        WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'test')
        ORDER BY SCHEMA_NAME) as dbs;

You could replace the SQL in line 4 with anything you want. Then I ran this command to generate the SQL that I need:

mysql -u root -p < createSomeSQL.sql > sqlToExecute.sql

Replace "root" with your username, of course. Now the file sqlToExecute.sql contains a script you can run to execute that SQL against all your databases.

Upvotes: 7

underscore
underscore

Reputation: 6887

A PHP approach would be:

$tables = array(
    'log_customer',
    'log_visitor',
    'log_visitor_info',
    'log_url',
    'log_url_info',
    'log_quote',
    'report_viewed_product_index',
    'report_compared_product_index',
    'report_event',
    'catalog_compare_item',
);

$dbh = new PDO('mysql:host=localhost;', 'USERNAME', 'PASSWORD', array(
    PDO::ATTR_PERSISTENT => true
));


$sql = $dbh->query('SHOW DATABASES');
$getAllDbs = $sql->fetchALL(PDO::FETCH_ASSOC);

foreach ($getAllDbs as $DB) {  

        foreach ($tables as $table) {
            $dbh->query('TRUNCATE TABLE ' . $DB['Database'] . '.' . $table);

    };
};

Upvotes: 6

user2629998
user2629998

Reputation:

Try the following (very basic, no error handling, may not work at all, I've not tested this):

$db = mysqli_connect(); // your database connection

$tables = ["log_customer", "log_visitor", "log_visitor_info"]; // array with all the tables

foreach ($tables as $table) {
    mysqli_query($db, "TRUNCATE TABLE `".$table."`"); // executes query for each element in the array
}

Upvotes: 1

Related Questions