Recently I am working with Piwik. (Around 200,000 visits per day)
Piwik version: 3.2.0
MySQL version: 5.5.57
PHP version: 5.6.30-0+deb8u1
Table piwik_log_link_visit_action is marked as crashed and should be repaired.
I followed the piwik documentation and the following command solved the problem.
myisamchk -r -q ./piwik_log_link_visit_action --sort_buffer_size=4G
Also, this command worked in another ceash.
myisamchk -r -v -f ./piwik_log_link_visit_action --sort_buffer_size=4G
but this problem happened again next day and again the day after that! Table size is around 50G.
Today, piwik_log_link_visit_action have been crashed again.
It is a MyISAM table and we want to convert it to InnoDB. After recovery can I create an empty table like piwik_log_link_visit_action, then rename it to piwik_log_link_visit_action. Then gradually insert the historical data to the new table? To keep the “idlink_va” field valid I can transfer few records. As following steps:
CREATE TABLE tmp_piwik_log_link_visit_action LIKE piwik_log_link_visit_action;
ALTER TABLE tmp_piwik_log_link_visit_action ENGINE=InnoDB;
INSERT tmp_piwik_log_link_visit_action SELECT * FROM piwik_log_link_visit_action
where idlink_va >= xxxx ; (e.g. last day records)
RENAME TABLE piwik_log_link_visit_action to old_piwik_log_link_visit_action ;
RENAME TABLE tmp_piwik_log_link_visit_action to piwik_log_link_visit_action ;
transfering records from old table to the new one.
Do you have any idea, experiance or suggestion?
Thank you in advance for your kind attention and supports.