After upgrading matomo from 3.13 to 4.5, can't process archive #18281

After upgrading matomo from 3.13 to 4.5 then can't process archive. The log stays on this page for three days without moving (FYI, I have 36w+ domains on my server), any idea?

INFO [2021-11-08 08:53:46] 30799 ---------------------------
INFO [2021-11-08 08:53:46] 30799 NOTES
INFO [2021-11-08 08:53:46] 30799 - Async process archiving supported, using CliMulti.
INFO [2021-11-08 08:53:46] 30799 - Reports for today will be processed at most every 3600 seconds. You can change this value in Matomo UI > Settings > General Settings.
INFO [2021-11-08 08:53:46] 30799 - Archiving was last executed without error 349 days 0 hours ago
INFO [2021-11-08 08:53:47] 30799 - Will process all 360546 websites
INFO [2021-11-08 08:53:47] 30799 ---------------------------
INFO [2021-11-08 08:53:47] 30799 START
INFO [2021-11-08 08:53:47] 30799 Starting Matomo reports archiving...
DEBUG [2021-11-08 08:53:47] 30799 Applying queued rearchiving...

• Matomo Version:4.5.0
• PHP Version:7.4.21
• Server Operating System:Centos7

Hi @MyIcecream. could you check how many entries are in the table archive_invalidations? And also if the is an entry in the options table with the name ReArchiveList and maybe how big that option_value is.

Hi, Thanks for replying: Do you mean these?
mysql> select count() from matomo_archive_invalidations;
+----------+
| count() |
+----------+
| 40187578 |
+----------+
mysql> select max(length(option_value)) from matomo_option;
+---------------------------+
| max(length(option_value)) |
+---------------------------+
| 5033211 |
+---------------------------+

Seems like there are quite too many invalidation records. Might be caused be the high number of websites.
@diosmosis do you think it would be safe to clean the table and maybe the option and restart the archiving?

If I empty this table, will I lose data? such as all domains， just reset the archive?

I guess cleaning the table matomo_archive_invalidations, might only cause that some archives aren't triggered correctly. But that can be fixed by invalidating the archives manually later if needed.

What should I do？

Stop any archiving processes. Afterwards try running a TRUNCATE TABLE matomo_archive_invalidations and then trigger the archiving again.

This seems similar to this issue: https://github.com/matomo-org/matomo/issues/17474 caused by updating from 3.X to one of the later 4.X's that had upgrade fixes for certain bugs in earlier 4.X releases. Truncating archive_invalidations and clearing ReArchiveList may solve the issue.

SELECT LENGTH(option_value) FROM matomo_option WHERE option_name = 'ReArchiveList';
+----------------------+
| LENGTH(option_value) |
+----------------------+
| 117 |
+----------------------+
SELECT option_value FROM matomo_option WHERE option_name = 'ReArchiveList';
+-----------------------------------------------------------------------------------------------------------------------+
| option_value |
+-----------------------------------------------------------------------------------------------------------------------+
| a:1:{i:0;s:99:"{"idSites":"all","pluginName":"VisitFrequency","report":null,"startDate":1606089600,"segment":null}";} |
+-----------------------------------------------------------------------------------------------------------------------+

Do you mean I can only execute these sql?
mysql> DELETE FROM matomo_option WHERE option_name = 'ReArchiveList';
mysql> TRUNCATE TABLE matomo_archive_invalidations;

@MyIcecream yes, those two queries look right.

It worked, thank you all @diosmosis @sgiehl

Glad to hear it worked! I'll close the issue.

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/matomo-on-premise-scaling/45262/4