Error upgrading to 2.9 (1034 Incorrect key file for table..)

Support & Bugs
1

Hello,

I am quite new to Piwik, and was asked to upgrade our company’s installation to the latest version. We were running Piwik 2.2.0, hosted in a private server of ours. I got these errors after running the php command on command line

[X] Error during plugin updates:

    * /CERN_WWW/piwik-dev/core/Columns/Updater.php:

Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it

    The update completed successfuly, however there were issues during the process. Please read the above descriptions for details. For further help:
* Check the [ Piwik FAQ ] which explains most common errors during update.

* Ask your system administrator - they may be able to help you with the error which is most likely related to y                                                                                                                          our server or MySQL setup.

When I go to our Piwik page, I am shown this message:

Database Upgrade Required
Your Piwik database is out-of-date, and must be upgraded before you can continue.
The following dimensions will be updated: log_visit.user_id, log_link_visit_action.idaction_content_interaction, log_link_visit_action.idaction_content_name, log_link_visit_action.idaction_content_piece, log_link_visit_action.idaction_content_target.

If I try to run the required commands manually in MySQL, I get the same error (“Incorrect key file for table …”)

Any ideas of how to solve or work around this issue, please?

Thank you very much!

2

Hi there, can you try REPAIR your mysql tables? maybe some of them are corrupted.

3

see faq I get the error: Table piwik_log_x is marked as crashed and last (automatic?) repair failed. - Analytics Platform - Matomo

4

Thank you for your reply. Unfortunately repair does not seem available for this table, is InnoDB (“The storage engine for the table doesn’t support repair”).

I have asked the DBAs to restore a previous version of our databases from backup, and I will try again.

5

I got the same error (The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
) when I retried upgrading from 2.2 to 2.9.1, using PHP command line. Again, I cannot repair it.

A database upgrade is required. Execute update? (y/N) y

Starting the database upgrade process now. This may take a while, so please be patient.

*** Update ***

Database Upgrade Required

Your Piwik database is out-of-date, and must be upgraded before you can continue.

Piwik database will be upgraded from version 2.2.0 to the new version 2.9.1.
The following dimensions will be updated: log_visit.user_id, log_link_visit_action.idaction_content_interaction, log_link_visit_action.idaction_content_name, log_link_visit_action.idaction_content_piece, log_link_visit_action.idaction_content_target.

The database upgrade process may take a while, so please be patient.

[X] Error during plugin updates:

    * /CERN_WWW/piwik-dev/core/Columns/Updater.php:

Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it
* /CERN_WWW/piwik-dev/core/Columns/Updater.php:
Error trying to execute the query 'ALTER TABLE piwik_log_visit ADD COLUMN `use
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table
t

    The update completed successfuly, however there were issues during the p
	iptions for details. For further help:

* Check the [ Piwik FAQ ] which explains most common errors during update.

* Ask your system administrator - they may be able to help you with the erro
our server or MySQL setup.

Piwik has been successfully updated!

Even though it says it has been successfully upgraded, when I access my Piwik page it says I have to upgrade it. Repeating the upgrade results in the same error.

6

Ok I’ve updated the FAQ with better instructions:

This may not work, in which case use the following solution:

cd /var/lib/mysql/your_piwik_database
myisamchk -r -v -f ./your_table_repair

Your tables should now be repaired and Piwik up and running!

does it work with these commands?

7

I don’t have access to the MySQL server, it is a external service. Is there a similar command I could run from a MySQL client?

8

I have tried upgrading version by version (i.e. from 2.2 to 2.3, from 2.3 to 2.4, etc…). It all worked fine until I upgraded from 2.6.1 to 2.7, when I got the same error as before (“Error trying to execute the query ‘ALTER TABLE piwik_log_visit ADD COLUMN user_id VARCHAR(200) NULL’.
The error was: SQLSTATE[HY000]: General error: 1034 Incorrect key file for table ‘piwik_log_visit’; try to repair it”).

I checked and our ‘piwik_log_visit’ table has 21,960,422 rows (7359.91 MB data), is that too much for the upgrade?

9

It’s not too much for the upgrade, it should complete. PLease try to repair the table or ask your sysadmin to run the command

10

Hello Matt,

In the end the problem was caused by lack of memory in the database server hosting our Piwik database, it could not handle an ‘alter’ on a 27 million rows table, crashed in the process. The database then was not corrupted, transaction was never finished and was rolled back automatically.

Our DBAs had to make a special setup for the upgrade, moving our temp folder to a dedicated partition while the migration lasted.

By the way the DBAs were quite surprised that all visits were logged to the same table, instead of having smaller monthly tables. Is there any way to do this? if not, please take it as a recommendation for the future :wink:

Thanks a lot for the help

11

Hi Jager,

By the way the DBAs were quite surprised that all visits were logged to the same table, instead of having smaller monthly tables. Is there any way to do this? if not, please take it as a recommendation for the future winking smiley

Thanks for the suggestion! I’ve created an issue at: https://github.com/piwik/piwik/issues/6848 - feel free to comment there