Error 500 after SQLSTATE errors missing a matomo.matomo_logtmpsegment table

[oscarmparedes]

We are seeing a 500 application error in logs right after this kind of SQLSTATE event in logs

[Tue Oct 25 10:17:38.542583 2022] [php:notice] [pid 65] [client XXXX] [XXXX] Error in Matomo: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'matomo.matomo_logtmpsegmentc2345b9867f0aa5d2556f6b8339d1f31' doesn't exist - in plugin Goals., referer: https://XXXX/index.php?module=CoreHome&action=index&idSite=1&period=day&date=yesterday
This has happened after a db migration to another cluster, and the db user didn't have the right permissions to create temporary tables. We added that permissions and restarted the application but still seeing the errors above.

Expected Behavior

Application should work normally, since the tables look to be temporary.

Current Behavior

Application is not usable after this error.

Steps to Reproduce (for Bugs)

1. Delete a temporary table, or migrate db to another instance without migrating temporary tables.
2. start the application and use it.

Your Environment

• Matomo Version: 4.12.1
• PHP Version: PHP/8.0.24
• Server Operating System: Debian GNU/Linux 11
• Additionally installed plugins:

• Browser: Firefox latest version
• Operating System: Mac, windows

[bx80]

Hi @oscarmparedes, thanks for reaching out.

If the database server has been restarted after adding user permissions then any references to previous temporary tables should have been reset.

Are you using the enable_segments_subquery_cache config setting? If so then you might need to run ./console cache:clear.

If you are using background archiving via a cronjob then it's also possible you could have a process still running in the background trying to access the old tables.