Inserting failed queries from errors in piwik tracker

(Pierre) #1

Quick question about re-inserting piwik tracker data that failed to insert in the first place.

Here’s the scenario:

I was upgrading my piwik instance from 2.1.0 -> 3.0.4 and had a couple errors with the database upgrade. I’ve managed to move past all those errors, but there was one I didn’t catch in time. My database shut down while it was upgrading the piwik_log_link_visit_action table and didn’t finish altering it. I tried running the database upgrade again and it said that the database was up to date, so I was under the impression that the table was finished upgrading. When I turned the live tracking back on, I noticed in my php error logs that I had a LOT of errors similar to this:

[11-Jun-2018 16:33:41 UTC] Error in Piwik (tracker): Error query: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘idpageview’ in ‘field list’ In query: INSERT INTO piwik_log_link_visit_action (idvisit, idsite, idvisitor, idaction_url, idaction_url_ref, idaction_name_ref, server_time, idpageview, time_spent_ref_action, idaction_event_action, idaction_event_category) VALUES (?,?,?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => ‘50042867’, 1 => 311, 2 => ‘×L„àÄÔr"’, 3 => ‘7841399’, 4 => 0, 5 => 0, 6 => ‘2018-06-11 16:33:40’, 7 => ‘DwAx5i’, 8 => 0, 9 => ‘6191047’, 10 => ‘6186116’, )

I’ve managed to fix the database table but my question is: Is there a particular way to get this data back into the database?

I’ve tried replicating these queries, and even simplified it by doing a generic "insert into table(…) values(…) but i keep getting an error saying the data for idvisitor is too long for the column.

Any thoughts?

(Peterbo) #2

If you still have the corresponding logfiles, try replaying them: How do I replay the traffic to Piwik and ingest logs of piwik.php requests? - Analytics Platform - Matomo

(Pierre) #3

Thanks for the quick reply. Would replaying the logfiles duplicate the data for the tables that did not have errors while inserting tracking data? Like the log_visit table for instance?

I’m only interested in repopulating the log_link_visit action table since it is the only one that threw errors.

(Peterbo) #4

You should delete the incomplete data in log_visit (if it was written at all) and replay the tracking. Just catching up with the missing data will definitely fail.