When we install Magento several tables are created, among them, the navigation log tables.

This log is different from the system log, which we discussed here in the blog under Check system log. The difference is that the browsing log records the passage of your customers and visitors through the site.

Yes. From the moment you put the store on the air, all pages visited are recorded in the log with the information of the type of visitor, user id logged, url visited, date/time of access, browser type and operating system , ip, etc.

And what’s the problem?

Because this navigation log is continuous, problems can occur when you do a product import, update data in tables, sometimes even a back button (back) from the client browser. Generating an integrity error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry…

That is, the system tries to record a record where data already exists.

Another problem would be precisely the size of your database that never stops growing. For every day (since launch) all the navigation information of the users are stored.

Obviously this information is important to investigate a problem or attempt to intrude, etc. But once you do a backup of this information this history can be deleted, thus reducing the volume of data in your tables and the size of your next backup.

Advanced log settings

To avoid this type of problem and even data overload, it is interesting that you enable the system log cleanup schedule. To access this:

System > Configuration > ADVANCED > System [Log Cleaning]

Let’s look at our configuration options:

Save Log, Days – Indicates the minimum amount of days the information should be stored in the tables. Any information above that period no longer needs to be stored – probably because you’ve already performed a backup.

Enable Log Clean – By default it is marked “No“, change to “Yes” if you want to clear the tables automatically.

Start time – You can enter hour, minute, and second. The important thing here is to inform a low-flow visitor time because the process consumes server resources.

Tip! Try to follow the first processes to find the average duration time – so you can better measure the scheduling time.

Frequency – How often will you perform the cleaning. This may match your backup’s frequency. Initially you can start with monthly backups, and with the increase in data volume, and the financial importance of them, you increase the frequency for weeks or days.

Configuring log cleanup

The last 3 (three) fields refer to the error email. Whenever there is a failure in scheduled data cleaning, an alert will be triggered for the informed email, using the referrals and informed templates on the system.

Emergency solution

Sometimes the error can prevent us from accessing the system. And our only alternative is to “attack” the database. So when the system has a problem with the log tables that can not be resolved by backend, we should use a DBMS to execute the script cleanup:

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `log_customer`;
TRUNCATE `log_quote`;
TRUNCATE `log_summary`;
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;
TRUNCATE `log_visitor_online`;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
ALTER TABLE `log_summary` AUTO_INCREMENT=1;
ALTER TABLE `log_url` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_online` AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1;

Basically all log tables are less log_summary_type. We used the truncate command to zero the logging log records, thus eliminating the duplicate key problem.

The script works on all versions of Magento 1.x.

Success!