5

In my magento 2 website there is a table named customer_visitor which has more than 15,47,354 entries . This would have been surely making the website slow , i did not care about that untill the website completly stopped working and this error was thrown to my face .

Lock wait timeout exceeded; try restarting transaction, query was: INSERT INTO `cw_customer_visitor` (`customer_id`, `session_id`, `last_visit_at`) VALUES (?, ?, ?)

I restarted the mysql server and the error was gone , but i am afraid it would keep on increasing and i would be faced with the same problem again .

Have anyone deleted this table ? Can anyone tell me is it safe to delete this table , if someone answer , please also tell me which log tables are harmless to delete , that would have been causing problems to the system

Thanks

2 Answers 2

8

its absolutely fine to truncate both these tables, as you most likely use Google Analytics to track the site visitors more efficiently. No harm in emptying these tables or disable the logging feature itself.

customer_visitor

customer_log

you can use the SQL below to truncate these tables

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE customer_log;
TRUNCATE customer_visitor;
SET FOREIGN_KEY_CHECKS=1;

you an also use this module to prevent logging the site visitor information into these tables

In addition to this you can also truncate these tables periodically. Remember clearing the below tables will clean up the reports of recently viewed products by every customer, products compared by every customer. So it makes sense you can maintain these data for some time and clean this up bi-weekly / monthly etc based on how do you want to maintain your data.

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `report_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `report_compared_product_index`;
SET FOREIGN_KEY_CHECKS=1;
1
  • Thanks for the information and the module link @Haijerome , i would try this in development and then i would truncate these tables on the live website . Nov 15, 2019 at 5:35
2

I have truncate the customer_visitor table in the local environment, it doesn't affect any functionality in Magento. You can simply truncate the table manually or set up cron job to truncate the table old data for every 15 days. It would be good solution rather than completely deleting a table.

These tables are used in below modules in Magento.

  • Magento_Catalog
  • Magento_Customer
  • Magento_Reports
1
  • Thanks for the information Nov 15, 2019 at 5:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.