Deleted "log_link_visit_action" table - how to fix?

(fert) #1


in an effort to reduce the size of my database I accidentally deleted the piwik_log_link_visit_action table (it was huge and I thought it could be deleted just like the archives). Now Piwik, rightfully I guess, tells me this:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_link_visit_action.idvisitor' in 'field list'

How can I fix that? I can use PhpMyAdmin and I created a new table with that name and 5 fields but I don’t know much about Databases and here I am stuck.

Is there a way to create this like an empty table so Piwik can still use the data from piwik_log_visit ?

(fert) #2

Update: I’ve been using this as a kind of guide but it won’t work. That might be because it is a very old scheme, several fields seem to have been added since then. Anyone able to help me?

(Matthieu Aubry) #3

Indeed very old, for the latest schema see:

CREATE TABLE {$prefixTables}log_link_visit_action (
											  idlink_va INTEGER(11) NOT NULL AUTO_INCREMENT,
									          idsite int(10) UNSIGNED NOT NULL,
									  		  idvisitor BINARY(8) NOT NULL,
									          server_time DATETIME NOT NULL,
											  idvisit INTEGER(10) UNSIGNED NOT NULL,
											  idaction_url INTEGER(10) UNSIGNED NOT NULL,
											  idaction_url_ref INTEGER(10) UNSIGNED NOT NULL,
											  idaction_name INTEGER(10) UNSIGNED,
											  idaction_name_ref INTEGER(10) UNSIGNED NOT NULL,
											  time_spent_ref_action INTEGER(10) UNSIGNED NOT NULL,
											  custom_var_k1 VARCHAR(200) DEFAULT NULL,
											  custom_var_v1 VARCHAR(200) DEFAULT NULL,
											  custom_var_k2 VARCHAR(200) DEFAULT NULL,
											  custom_var_v2 VARCHAR(200) DEFAULT NULL,
											  custom_var_k3 VARCHAR(200) DEFAULT NULL,
											  custom_var_v3 VARCHAR(200) DEFAULT NULL,
											  custom_var_k4 VARCHAR(200) DEFAULT NULL,
											  custom_var_v4 VARCHAR(200) DEFAULT NULL,
											  custom_var_k5 VARCHAR(200) DEFAULT NULL,
											  custom_var_v5 VARCHAR(200) DEFAULT NULL,
											  PRIMARY KEY(idlink_va),
											  INDEX index_idvisit(idvisit),
									          INDEX index_idsite_servertime ( idsite, server_time )
											)  DEFAULT CHARSET=utf8

(fert) #4

Thanks a lot!