Re: [WebERP-developers] Database Maintenance at Logon Fails Regularly

[Phil D. <ph...@lo...>]

Hi Ade,

 >What we want to know is:
 >1. What's the script actually do? which DB table affected by this script?

It runs the function DB_Maintenance() - in includes/ConnectDB_mysqlx.inc
the code in the ConnectDB_mysqli.inc does this:

function DB_Maintenance(){

     prnMsg(_('The system has just run the regular database 
administration and optimisation routine.'),'info');

     $TablesResult = DB_query("SHOW TABLES");
     while ($myrow = DB_fetch_row($TablesResult)){
         $Result = DB_query('OPTIMIZE TABLE ' . $myrow[0]);
     }

     $Result = DB_query("UPDATE config
                 SET confvalue='" . Date('Y-m-d') . "'
                 WHERE confname='DB_Maintenance_LastRun'");
}

so runs OPTIMIZE TABLE on all tables

https://dev.mysql.com/doc/refman/5.1/en/optimize-table.html


 >2. Is the script really necessary to run in webERP? Is there's any 
problem if we set it to '0' (never)?

The effect is minimal I think as indexes do not need to be rebuilt often.

 >3. Is it possible that this issues happens because our database is 
growing larger and larger each months?

Yes the bigger the DB the longer the OPTIMIZE TABLE will take.

 >If it's really the issues, so how we manage to perform the database 
maintenance properly?

Probably could run the OPTIMIZE TABLE from phpMyAdmin or from mysql 
command line more efficiently.

Phil