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 I’m trying to see if I can reduce the size before attempting to upgrade again. Can anyone help?
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.)
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!