Quantcast

Database Maintenance at Logon Fails Regularly

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Database Maintenance at Logon Fails Regularly

baliboss
Dear, All webERP Developers and Phil Daintree

In our webERP System Configuration (version 4.10.1), we set the Perform Database Maintenance At Logon to Monthly and there is no problem for more than 3 years now.

But now when the first user login every month and performing the database maintenance, the process seems like stuck and loading for hours, no error messages, it's just loading. It's happen for several months now. We thought it's stuck so we decide to change the config in phpmyadmin and set DB_Maintenance to '0' (never)  and cancel the process, clear the browser cache and webERP running as usual.

What we want to know is:
1. What's the script actually do? which DB table affected by this script?
2. Is the script really necessary to run in webERP? Is there's any problem if we set it to '0' (never)?
3. Is it possible that this issues happens because our database is growing larger and larger each months? If it's really the issues, so how we manage to perform the database maintenance properly?

Thank You,

Best Regards,
----------------
Ade / baliboss
Baliboss
Bali Open Source Solution
www.baliboss.com
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database Maintenance at Logon Fails Regularly

gsavix
Hi. IMHO you could use:

1. What's the script actually do? which DB table affected by this script?

Problem is new version of databases (constraints (primary key, foreign key, indexes)) that goto live production without tests.

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

First answer is no you do not have any problem if you do not clean your database; but if your provider limit size of your data base space or your resources (memory + network) are rare this leave you to more slow weberp environment. So if you use share dreamhost, hostgator or other like these providers keep cool. I use weberp for more than 6 years in small, medium and high volume and never we have issues with this.

Make a phpmyadmin export (with zip or gzip mode) and after clear all records of table AUDITTRAIL. For detailed database weberp 4.11.2 please see




3. Is it possible that this issues happens because our database is growing
larger and larger each months? If it's really the issues, so how we manage
to perform the database maintenance properly?

Make a phpmyadmin export (with zip or gzip mode) and after clear all records of table AUDITTRAIL.
For detailed database weberp 4.11.2 please see [1]

Look for this code fragment session.inc or (login.php) search at [2]

******************** fragment end ***********************************

/*If the Code $Version - held in ConnectDB.inc is > than the Database VersionNumber held in config table then do upgrades */
if (strcmp($Version,$_SESSION['VersionNumber'])>0 AND (basename($_SERVER['SCRIPT_NAME'])!='UpgradeDatabase.php')) {
	header('Location: UpgradeDatabase.php');
}
if(isset($_SESSION['DB_Maintenance'])){ 
	if ($_SESSION['DB_Maintenance']>0)  { //run the DB maintenance script
		if (DateDiff(Date($_SESSION['DefaultDateFormat']),
				ConvertSQLDate($_SESSION['DB_Maintenance_LastRun'])
				,'d')	>= 	$_SESSION['DB_Maintenance']){

			/*Do the DB maintenance routing for the DB_type selected */
			DB_Maintenance($db);
			$_SESSION['DB_Maintenance_LastRun'] = Date('Y-m-d');
		}
	}
}
//purge the audit trail if necessary
if (isset($_SESSION['MonthsAuditTrail'])){ 
	 $sql = "DELETE FROM audittrail
			WHERE  transactiondate <= '" . Date('Y-m-d', mktime(0,0,0, Date('m')-$_SESSION['MonthsAuditTrail'])) . "'";
	$ErrMsg = _('There was a problem deleting expired audit-trail history');
	$result = DB_query($sql,$db);
}

******************** fragment end ***********************************
Regards.

2015-10-19 4:26 GMT-02:00 baliboss <[hidden email]>:
Dear, All webERP Developers and Phil Daintree

In our webERP System Configuration (version 4.10.1), we set the Perform
Database Maintenance At Logon to Monthly and there is no problem for more
than 3 years now.

But now when the first user login every month and performing the database
maintenance, the process seems like stuck and loading for hours, no error
messages, it's just loading. It's happen for several months now. We thought
it's stuck so we decide to change the config in phpmyadmin and set
DB_Maintenance to '0' (never)  and cancel the process, clear the browser
cache and webERP running as usual.

What we want to know is:
1. What's the script actually do? which DB table affected by this script?
2. Is the script really necessary to run in webERP? Is there's any problem
if we set it to '0' (never)?
3. Is it possible that this issues happens because our database is growing
larger and larger each months? If it's really the issues, so how we manage
to perform the database maintenance properly?

Thank You,

Best Regards,
----------------
Ade / baliboss



-----
Baliboss
Bali Open Source Solution
www.baliboss.com
--
View this message in context: http://weberp-accounting.1478800.n4.nabble.com/Database-Maintenance-at-Logon-Fails-Regularly-tp4658349.html
Sent from the web-ERP-developers mailing list archive at Nabble.com.

------------------------------------------------------------------------------
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers



--
gilberto dos santos alves
+55(11)9-8646-5049
sao paulo - sp - brasil





------------------------------------------------------------------------------

_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database Maintenance at Logon Fails Regularly

phildaintree
In reply to this post by baliboss
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

------------------------------------------------------------------------------
_______________________________________________
Web-erp-developers mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/web-erp-developers
If anyone is wondering about the persistently nasty comments made by Tim Schofield and wants the full story please see: http://timschofield.blogspot.com/ Hell hath no fury like a woman (or Tim) scorned
Loading...