MySQLi syntax error "AND idarchive < ?' at line 3" #16978
Description
I upgraded Matomo to the first beta of 4.1.0 and sadly as well migrated to PHP8.0, so I'm not quite sure which one of both is responsible for the error messages I get now:
ERROR | Piwik\ExceptionHandler | 2020-12-17 23:16:12 UTC | bd4c8 | AND idarchive < ?' at line 3
ERROR | Piwik\ExceptionHandler | 2020-12-17 23:16:12 UTC | bd4c8 | Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND idarchive >= ?
ERROR | Piwik\ExceptionHandler | 2020-12-17 23:16:12 UTC | bd4c8 | AND idarchive < ?' at line 3
ERROR | Piwik\ExceptionHandler | 2020-12-17 23:16:12 UTC | bd4c8 | Uncaught exception: /var/www/matomo/libs/Zend/Db/Statement/Mysqli.php(77): Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND idarchive >= ?
Copied from logs viewer, hence first entry is the most recent one.
Apache2 reports it as well:
Dec 18 21:20:59 dietpi.com apache2[2133626]: [proxy_fcgi:error] [pid 2133626:tid 140713454954240] [client 162.158.202.249:19486] AH01071: Got error 'PHP message: Error in Matomo: Mysqli prepare error: You have an
error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND idarchive >= ? AND idarchive < ?' at line 3', referer: https://dietpi.com/
matomo/index.php?module=PrivacyManager&action=privacySettings&idSite=1&period=range&date=last7&limit=100
It happens when accessing Administration > Privacy > Anonymize data.
But it seems to have a larger rats tail, as since then as well archiving data does not work anymore and reports are not generated. Running the archive cron job also shows:
INFO [2020-12-18 21:07:02] 3374105 SUMMARY
INFO [2020-12-18 21:07:02] 3374105 Processed 0 archives.
INFO [2020-12-18 21:07:02] 3374105 Total API requests: 0
EDIT: That part has been fixed with #16968, hence is not related to the syntax error.
It is likely those functions: https://github.com/matomo-org/matomo/blob/4.x-dev/plugins/PrivacyManager/ReportsPurger.php#L259-L284
I guess one of the variables that should replace the ? is not set correctly, but I do not 100% understand what happens there 😉.