About 2 weeks ago I updated piwik (to 2.4), I used the one click button, and it worked. Never the less, there was an note that I needed to update may database. I followed the instructions and it looked like all was good.
Today MySQL would not start. :X The server must have sicled through a reboot. All other websites where down as well, not just piwik.
I found this in the error log:
2014-07-10 14:29:37 5768 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/slave_worker_info uses space ID: 5 at filepath: .\mysql\slave_worker_info.ibd. Cannot open tablespace piwik/piwik_access which uses space ID: 5 at filepath: .\piwik\piwik_access.ibd
InnoDB: Error: could not open single-table tablespace file .\piwik\piwik_access.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
I made a backup of the databases. (not using MySQL but copy folder method)
then I added this line to my my.ini file (note, it is NOT called my.cnf as suggested ablove.)
[mysqld]
innodb_force_recovery = 1
After trying a start of MySQL, MySQL finally started. Now i found this in the error log:
2014-07-10 14:29:37 5768 [Note] InnoDB: innodb_force_recovery was set to 1. Continuing crash recovery even though we cannot access the .ibd file of this table.
2014-07-10 14:29:37 5768 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/slave_master_info uses space ID: 4 at filepath: .\mysql\slave_master_info.ibd. Cannot open tablespace piwik/piwik_user which uses space ID: 4 at filepath: .\piwik\piwik_user.ibd
InnoDB: Error: could not open single-table tablespace file .\piwik\piwik_user.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
2014-07-10 14:29:37 5768 [Note] InnoDB: innodb_force_recovery was set to 1. Continuing crash recovery even though we cannot access the .ibd file of this table.
2014-07-10 14:29:37 5768 [Note] InnoDB: Restoring possible half-written data pages
2014-07-10 14:29:37 5768 [Note] InnoDB: from the doublewrite buffer…
2014-07-10 14:29:38 1500 InnoDB: Error: table 'piwik/piwik_access’
InnoDB: in InnoDB data dictionary has tablespace id 5,
InnoDB: but the tablespace with that id has name mysql/slave_worker_info.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: MySQL :: MySQL 5.6 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
2014-07-10 14:29:38 1500 InnoDB: Error: table 'piwik/piwik_user’
InnoDB: in InnoDB data dictionary has tablespace id 4,
InnoDB: but the tablespace with that id has name mysql/slave_master_info.
InnoDB: Have you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: MySQL :: MySQL 5.6 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
2014-07-10 14:29:38 5768 [Note] InnoDB: 128 rollback segment(s) are active.
2014-07-10 14:29:38 5768 [Note] InnoDB: Waiting for purge to start
2014-07-10 14:29:38 5768 [Note] InnoDB: 5.6.11 started; log sequence number 1861199971
2014-07-10 14:29:38 5768 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
and Piwik is down.
Checking with phpMyAdmin on the Piwik data I found this: #1146 - Table ‘piwik.piwik_access’ doesn’t exist
What would I need to do to get piwik working again, and without loosing all my piwik data? And how can I prevent this happening again? This data base restructuring broght me down two times now. I can not afford MySQL hanging and being down.
I found the following also in the error log, a bit lower:
29:38 5768 [Note] Server socket created on IP: ‘127.0.0.1’.
2014-07-10 14:29:38 5768 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See MySQL :: MySQL 5.6 Reference Manual :: 14.21 InnoDB Troubleshooting for how you can resolve the problem.
2014-07-10 14:29:38 5768 [Warning] Info table is not ready to be used. Table ‘mysql.slave_master_info’ cannot be opened.
2014-07-10 14:29:38 5768 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See MySQL :: MySQL 5.6 Reference Manual :: 14.21 InnoDB Troubleshooting for how you can resolve the problem.
2014-07-10 14:29:38 5768 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See MySQL :: MySQL 5.6 Reference Manual :: 14.21 InnoDB Troubleshooting for how you can resolve the problem.
2014-07-10 14:29:38 5768 [Warning] Info table is not ready to be used. Table ‘mysql.slave_relay_log_info’ cannot be opened.
2014-07-10 14:29:38 5768 [Note] Event Scheduler: Loaded 0 events
Not too sure what to make of that …
Did that update mess up my tables?
Suggestions are very welcome! Thanks. 