We build. You grow.

Get best community software here

Start a social network, a fan-site, an education project with oxwall - free opensource community software

Check tables cron job | Forum

Marcus
Marcus Dec 31 '19
Folks could you give me some pointer to create a cron job that will execute all tables mysql ckeck table option every hour. This is to make sure no crashed tables in db. 
Ebenezer Obasi
Ebenezer Obasi Jan 3 '20
Could you tell us what you have done so far? To create a custom cron job, you need to include cron.php in your plugin root.
Marcus
Marcus Jan 3 '20
I'm not sure about the query. How can I tell mysql to execute all table of my db
Ebenezer Obasi
Ebenezer Obasi Jan 3 '20

Try this to get all the tables. Then loop through the tables and perform an SQL query. This method is quite expensive, so you might want to set up a system to save tables that have been processed and pause process for a later cron job.


$dbTables = OW::getDbo()->query("USE `". OW_DB_NAME ."`

    SELECT name FROM sys.tables

");

The Forum post is edited by Ebenezer Obasi Jan 3 '20
Ebenezer Obasi
Ebenezer Obasi Jan 3 '20
So this did it for me,


$dbTables = OW::getDbo()->queryForList("SELECT TABLE_NAME

     FROM INFORMATION_SCHEMA.TABLES

     WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='". OW_DB_NAME ."' 

");


foreach( $dbTables as $table )

{

    //perform query on table

}

The Forum post is edited by Ebenezer Obasi Jan 3 '20
Marcus
Marcus Jan 4 '20
Thanks so much buddy. I thought there was a way to avoid looping through tables....

Anyways I think user table is the one that sometimes stops responding and the only way to get it going again is to manually click on check table  in phpmyadmin. 

So I'm gonna only create cron for that one table. Let see how it goes. 

Thanks again and happy new year
Ebenezer Obasi
Ebenezer Obasi Jan 4 '20
This should list only crashed tables:


$dbTables = OW::getDbo()->queryForList("SHOW TABLE STATUS FROM`". OW_DB_NAME ."`

    WHERE comment LIKE '%crash%';

");


You can as well alter the above example to look like this:


$dbTables = OW::getDbo()->queryForList("SELECT TABLE_NAME

     FROM INFORMATION_SCHEMA.TABLES

     WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='". OW_DB_NAME ."' AND TABLE_COMMENT LIKE '%crash%'

");


In both cases, you will get an empty array if no table has crashed.

The Forum post is edited by Ebenezer Obasi Jan 4 '20
Marcus
Marcus Jan 4 '20
Amazing thanks so much. I'm sure many will benefit from this post of yours 
dave Leader
dave Jun 24 '20