SQLSTATE[42S02]: Base table or view not found: 1932 Table 'piwik.piwik_archive_numeric_2016_01' doesn't exist in engine

Hi, I have got this error, and unlike the other posts in the forum, this hasn’t happened during update or install. The only widget that displays anything meaningful is the “Visitors in Real-time”, the rest of the statistics is replaced with error message. Would there be anyway to recover the statistics? I am using the version 2.15.0, php 7.0.3, mariadb-10.1.11-1, apache-2.4.18-1 under Archlinux.

Please check this table directly in the DB (if it exists and what’s the status). You can also delete it and run the archiving process which should recreate archive tables. The good information is that no data has been lost. :slight_smile:

For these tables, I get things like this

MariaDB [piwik]> check table piwik_archive_numeric_2015_01;
| Table | Op | Msg_type | Msg_text |
| piwik.piwik_archive_numeric_2015_01 | check | Warning | Tablespace is missing for table ‘piwik/piwik_archive_numeric_2015_01’ |
| piwik.piwik_archive_numeric_2015_01 | check | Error | Table ‘piwik.piwik_archive_numeric_2015_01’ doesn’t exist in engine |
| piwik.piwik_archive_numeric_2015_01 | check | status | Operation failed |
3 rows in set (0.00 sec)

MariaDB [piwik]> check table piwik_archive_numeric_2016_01;
| Table | Op | Msg_type | Msg_text |
| piwik.piwik_archive_numeric_2016_01 | check | Error | Table ‘piwik.piwik_archive_numeric_2016_01’ doesn’t exist in engine |
| piwik.piwik_archive_numeric_2016_01 | check | status | Operation failed |
2 rows in set (0.00 sec)

So you are saying that I can simply do the drop all tables piwik.piwik_archive* and run archiving process from piwik browser?

Drop only damaged tables. Before doing it, make sure that you have all the raw data for the given month. It’s recommended to run the archiving process from the command line interface, but going with the browser archiving is also OK if you don’t have a high traffic website.

Thank you very much, after dropping the tables and removing ibd files, things are fine now.

However, for knowledge’s sake, how did this happen in the first place? I used to run older versions of piwik on
other machines with different OS (older) with different machines (not necessary older) with older versions of php,
mysql, and appache, but I don’t seem to remember having encountered this problem. So presumably one of the things I changed is causing the problem, but which one?

Actually I have just found that the data for the visits between the January third and February 17th are gone. Knowing that I have the mysql binary log between these period, I have a backup of the db as of January 4th,
but there is only one binary logfile covering the period from January 3rd (before the backup of the db) to February 18th, (presumably after the db corruption), would there be a way to recover the data?

@Editions_Brandon could you check log_visit and log_link_visit_tables for this date-range? Remember to use idsite and visit_last_action_time or server_time while executing a select query. After you confirm that there’s no data for this period, the only way will be to recover it from the backup. It’s also possible to recover the data from server access log files (maybe it’s even easier than using binlogs).

The data seems to be gone from these tables as well. The documentation on log analysis is not very clear if there is a way to the data imported from log with java tracker data, would there be any? As the number of visits we have
had is not so big, I don’t mind copying table entries by hand, if there is detailed documentation of the structure of piwik database. (We migrated our server in January, so if I use access log from the new server, I don’t get the data froom last year, and in any case I prefer to continue to track visitors with javascripts).

Did you migrate the Piwik server or the app server? Or maybe both? As I mentioned, the easiest way to go is replaying the requests to piwik.php resource on your Piwik machine.

Could you elaborate on that?

We migrated both the server for the website and the piwik.

I think I misunderstood when you mentioned the server access log files, I thought you meant the use of
log analysis tools.

So if I understand you correctly, (and if I leave out the problem of finding access log from the ancient server),
what I should do is something like

grep piwik.php /var/log/httpd/access_log

to extract all GET request for piwik.php, feed the result into gawk or something to single out the relevant part, and use curl or wget to “replay” the transactions?

Another point: won’t there be problems related to visitor-id reassigned? That is, since piwik “forgot” all visits during certain period, it may have assigned a visitor-id to later visitors an ids that had been assigned during that period?

There’s no need to do this manually. See this article: How do I replay the traffic to Piwik and ingest logs of piwik.php requests? - Analytics Platform - Matomo

OK, thanks. Unfortunately it has turned out that somehow the default system of log rotation of apache under archlinux (I didn’t bother to change the configuration :frowning: ) overwrites old logs… So I will try binary log. Anyhow I have come across two other problems, and I will open new threads for them. Thank you very much for your help so far.