Archive.php broken after upgrade to 1.9

After an upgrade to 1.9 and the removal of all the archived reports for reprocessing with the new GeoIP data, archive.php fails, and I can’t understand the origin of the error messages:


General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08'

There is no “piwik_archive_*” table in /var/lib/mysql/piwik. And now the web interface is empty, and says “There is no data for this report”.

I reinstalled piwik 1.9 from a fresh download, keeping the same database. No change. Any idea?


$ sudo -u www-data /usr/bin/php5 /path/to/archive.php --url=https://example.org/piwik/  --force-all-periods > /dev/null
Error in the last Piwik archive.php run: 
2 total errors during this script execution, please investigate and try and fix these errors. First error was: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=7&period=day&date=last52&format=php&token_auth=xxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}'

 Here is the full errors output:

NOTE: if you execute this script at least once per hour (or more often) in a crontab, you may disable 'Browser trigger archiving' in Piwik UI > Settings > General Settings. 
      see doc at: http://piwik.org/docs/setup-auto-archiving/
--force-all-periods was detected: the script will run as if it was its first run, and will trigger archiving for all periods.
---------------------------
INIT
Querying Piwik API at: https://example.org/piwik/index.php
Running as Super User: admin
Notes
- Reports for today will be processed at most every 300 seconds. You can change this value in Piwik UI > Settings > General Settings.
- Reports for the current week/month/year will be refreshed at most every 3600 seconds.
--force-all-periods was detected: we will process websites with visits in the last 7 days 0 hours
Will process 2 websites with new visits since 7 days 0 hours , IDs: 7, 8
---------------------------
START
Starting Piwik reports archiving...
ERROR: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=7&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}'
WARNING: Empty or invalid response '' for website id 7, Time elapsed: 0.185s, skipping
ERROR: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=8&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}'
WARNING: Empty or invalid response '' for website id 8, Time elapsed: 0.172s, skipping
Done archiving!
---------------------------
SUMMARY
Total daily visits archived: 0
Archived today's reports for 0 websites
Archived week/month/year for 0 websites. 
Skipped 2 websites: no new visit since the last script execution
Skipped 0 websites day archiving: existing daily reports are less than 300 seconds old
Skipped 0 websites week/month/year archiving: existing periods reports are less than 3600 seconds old
Total API requests: 0
done: 0/2 0%, 0 v, 0 wtoday, 0 wperiods, 0 req, 358 ms, 2 errors. eg. 'Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=7&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}''
Time elapsed: 0.358s
---------------------------
SCHEDULED TASKS
Starting Scheduled tasks... 
 No task to run
done
---------------------------
SUMMARY OF ERRORS
Error: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=7&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}'
Error: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=8&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}'
2 total errors during this script execution, please investigate and try and fix these errors
ERROR: 2 total errors during this script execution, please investigate and try and fix these errors. First error was: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=7&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}'
PHP Fatal error:  2 total errors during this script execution, please investigate and try and fix these errors. First error was: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=7&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}' in /path/to/archive.php on line 561

Can you try REPAIR the tables using phpmyadmin or similar tool ? maybe a file is corrupt ?

Thanks for your answer. I just did the REPAIR with mysqlcheck. The problem remains:


$ mysqlrepair -u root -p piwik
Enter password: 
piwik.piwik_access                                 OK
piwik.piwik_goal                                   OK
piwik.piwik_log_action                             OK
piwik.piwik_log_conversion                         OK
piwik.piwik_log_conversion_item                    OK
piwik.piwik_log_link_visit_action                  OK
piwik.piwik_log_profiling                          OK
piwik.piwik_log_visit                              OK
piwik.piwik_logger_api_call                        OK
piwik.piwik_logger_error                           OK
piwik.piwik_logger_exception                       OK
piwik.piwik_logger_message                         OK
piwik.piwik_option                                 OK
piwik.piwik_report                                 OK
piwik.piwik_session                                OK
piwik.piwik_site                                   OK
piwik.piwik_site_url                               OK
piwik.piwik_user                                   OK
piwik.piwik_user_dashboard                         OK
piwik.piwik_user_language                          OK


$ sudo -u www-data /usr/bin/php5 /path/to/archive.php --url=https://example.org/piwik/  --force-all-periods > /dev/null
Error in the last Piwik archive.php run: 
2 total errors during this script execution, please investigate and try and fix these errors. First error was: Got invalid response from API request: https://example.org/piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=7&period=day&date=last52&format=php&token_auth=xxxx&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:96:"SQLSTATE[HY000]: General error: 1017 Can't find file: 'piwik_archive_numeric_2012_08' (errno: 2)";}'
...

Just wanted to say thanks to nil for posting this. I was about to remove my archived reports for the exact same purpose, but will hold off until this issue is resolved.

This is not a piwik issue, please search on web “General error: 1017 Can’t find file:” or ask your sysadmin, maybe something is corrupt.

Indeed.

After running mysqlrepair on the mysql base (not the piwik base) and deleting from the mysql commandline some archive tables that were already deleted from the filesystem, the problem was solved. Thanks.