Unable to reduce size of bloated of matomo_archive_blob_2020_01 table. Help?

Support & Bugs
1

I’ve been trying to upgrade our self-hosted system from 3.14 to the latest (was 4.11, but is now 4.12), but keep hitting a DB storage snag when upgrading the DB. (The system is currently “rolled back” to a pre-upgrade backup.)

Long story short, we have 1 table (matomo_archive_blob_2020_01) that the ALTER TABLE statements fail on because it’s 35G due to an old bug :weary: I’m trying to see if I can reduce the size before attempting to upgrade again. Can anyone help?

More details…

Actual Error

 Error trying to execute the migration 'ALTER TABLE `matomo_archive_blob_2020_01` CHANGE `name` `name` VARCHAR(190);'.
    The error was: SQLSTATE[HY000]: General error: 1114 The table 'matomo_archive_blob_2020_01' is full

It seems like we should have enough disk space to handle this; we still have 36G of space available. (Maybe that cuts it too close, or maybe it’s more complicated w/ temp tables.) However, the table doesn’t need to be this large in the first place, so I want to see if I can shrink it first.

Background – Back in 2020, we started having the “known issue” of the matomo_archive_blob_* tables getting unreasonably large (Issue #15930 on Github). 3.14 had a fix for this, so we upgraded not too long after it was launched, and that fixed most of the tables… but for whatever reason, not matomo_archive_blob_2020_01, which has been stuck at 35G ever since. (No other matomo_archive_blob_* table even reaches 1G, let alone 35G.)

image
image747×477 19.3 KB

Here’s what I’ve tried so far (none have reduced the table size in any significant way):

  • ./console --matomo-domain=mamp core:run-scheduled-tasks 'Piwik\Plugins\CoreAdminHome\Tasks.purgeInvalidatedArchives'
  • ./console core:purge-old-archive-data january-2020
  • ./console core:purge-old-archive-data 2020
  • ./console core:invalidate-report-data --dates=2020-01-01,2020-02-01 & then repeat above
  • ./console core:invalidate-report-data --dates=2020-01-01,2021-01-01 & then repeat above
  • ./console database:optimize-archive-tables january-2020
  • ./console database:optimize-archive-tables 2020
  • mysql> OPTIMIZE TABLE matomo_archive_blob_2020_01;

Some of these seemed to make changes (all the table optimizing takes ~30min), but the table size doesn’t seem to change much (if at all).

The only one that displayed any errors was the last one, mysql> OPTIMIZE TABLE matomo_archive_blob_2020_01; which gave me the same error as the ALTER TABLE statement (SQLSTATE[HY000]: General error: 1114 The table 'matomo_archive_blob_2020_01' is full). This makes me wonder if all the purge/optimize console commands are actually failing despite no error message? Is my only hope to find some way to make the OPTIMIZE TABLE command work?

Thank you to anyone who made it through all this!

2

Hi @mikky-cecil
Just an idea… Could you (maybe?):

  1. Create a copy of your DB (and of the Matomo files)
  2. Configure the Matomo copy to this DB copy
  3. Truncate the matomo_archive_blob_2020_01 table
  4. Invalidate data for January 2020
  5. Start data archiving…
  6. If this works, then you may copy the table to the original DB…

But this (and also all what you tried before…) can only work if you still store raw data for January 2020. If these data are already removed from your DB, then I think it won’t be possible… ever!

3

This worked! :tada: I didn’t even think of just truncating the table, forgot that the archive tables can basically rebuild themselves with re-archiving. Thank you so much! :smile: