MySQL prepare error: Table '.\piwik\piwik_archive_numeric_2013 failed

Support & Bugs
1

Hi all,

I am seeing MySQL “prepare error: Table '.\piwik\piwik_archive_numeric_2013 failed” message on my widget and when i went to check myPHPAdmin I noticed that 4 tables had crashed and are not repairable:

piwik_archive_numeric_2013_11
piwik_archive_numeric_2013_10
piwik_archive_blob_2013_11
piwik_archive_blob_2013_10

May I know what are these tables for and what might be the causes that i can look at.

Thanks All ! =)

2

if you still have your logs (if you do not use LOG DELETE) then you can safely delete these tables and they will be re-processed from the logs

3

Hi Matt,

Thanks for replying.

I tried dropping the table, and i received an error which prevented me from doing so. However due to this action, the table was “repaired” and no longer corrupted or locked. Hence I repeated the process for all the crashed tables and it works.

I am not sure why is this happening, and shall continue to track this problem. In the mean time, I hope someone can shred some light on this =) cause i am very curious If this problem is caused by Piwik or MySQL

4

That could be an issue of encoding. Your database, for example, is setup using collation swedish-latin-1 and the data trying to be store is utf-8-general-ci, or something.

Check to see that the data and the tables are on the same page.

The Innodb engine gets confused, because it doesn’t know how to store it, nor get rid of it. Since Innodb databases usually lock tables while writing, it sticks. Try a BFH; works for me sometimes. ::o Short of that, you could try converting the database to use the ISAM engine, if it is not already.

Hope that helps! :wink:

Sven2157

5

Hi Sven,

Thanks for the reply, my db collation is utf-8-general-ci, which is the same as the site i m tracking and my engine type is MYISAM. So dun think its the case =(

The problem still persist once a while but i noticed that only the monthly archives are having problems, i.e. piwik_archive_numeric_2013_11
piwik_archive_numeric_2013_10. Recently I am tracking via year scope, which generated the table piwik_archive_numeric_2013_01 which so far doesn’t crash together with the monthly archives.

6

Please try with latest beta Index of /

7

test

8

Hi

Sorry for the late reply, it seems that every morning i faced the same problem when users start using the website; the tables will be “in use” state and i will get “Table '.\piwik\piwik_archive_numeric_2013_xx failed” message at the dashboard. I will then attempt to drop the tables, which will solve the problem for the day.

I tried emptying the tables but it only resolve “piwik_archive_blob_2013_10” permanently, currently “piwik_archive_numeric_2013_11”,
“piwik_archive_numeric_2013_10”, and “piwik_archive_blob_2013_11” still face the same problem everyday~

Shall go try the new beta build~

9

What is the exact error message( s )? How are you determining that the table is locked, and thus causing the issue? See if this helps: http://www.mysqlperformancetuning.com/how-to-reduce-table_locks_waited-in-mysql-myisam

*** EDIT ***
This may help as well:
http://dba.stackexchange.com/questions/21075/way-to-prevent-queries-from-waiting-for-table-level-lock

Sven2157