Matomo version: 3.8.1
DB: MariaDB 10.1.38
When the cron job runs, I see this error message in the log:
INFO [2019-03-07 00:50:30] 111882 Scheduler: executing task Piwik\Plugins\PrivacyManager\Tasks.deleteLogData...
WARNING [2019-03-07 00:50:45] 111882 Piwik\Plugins\PrivacyManager\LogDataPurger: LOCK TABLES privilege not granted; skipping unused actions purge
But the user has all rights for the database:
show grants for 'piwik'@'localhost';
GRANT ALL PRIVILEGES ON
piwik.* TO 'piwik'@'localhost'
Do the privileges need to be flushed maybe? In general if MySQL says it doesn't have the permission, then it doesn't. Maybe a different user is used, or there is another user configured that isn't
localhost or so. I don't think it's an issue in Matomo but feel free to reopen if it is.
Locking the matomo tables via the mysql console with the same user will work without any warnings.
Did you check if eg the same user is defined multiple times in MySQL?
Yes it is the same user, and it is only one time defined in the database.
I have the same problems since a few months. It only occurs every few days for me (I guess this particular job is run seldomly).
SHOW GRANTS FOR 'matomo'@'localhost' gives
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `matomo`.* TO 'matomo'@'localhost'
So matomo should be able to lock tables.
Can you execute
SELECT GET_LOCK('foo', 1) with that user?
It locks successful.
I also tried it in two different sessions where it times out correctly after 1 s and returns 0.
MariaDB [matomo]> SELECT GET_LOCK('foo', 1); +--------------------+ | GET_LOCK('foo', 1) | +--------------------+ | 1 | +--------------------+ 1 row in set (0.000 sec)
I suppose eg this works as well ?
LOCK TABLES piwik_user WRITE; UNLOCK TABLES ;
In general this really looks like some MySQL configuration issue. If it says it doesn't have the
LOCK TABLES permission, then the user seems to not have it for some reason. Make sure to execute
FLUSH PRIVILEGE after granting the permission maybe this helps.
Hm, you’re right, seems to work.
MariaDB [matomo]> LOCK TABLES piwik_user WRITE; ERROR 1146 (42S02): Table 'matomo.piwik_user' doesn't exist MariaDB [matomo]> LOCK TABLES matomo_user WRITE; Query OK, 0 rows affected (0.000 sec) MariaDB [matomo]> UNLOCK TABLES; Query OK, 0 rows affected (0.000 sec)
FLUSH PRIVILEGES after testing, I guess it will take a while until this shows up again.
It seems to happen every 5 days only (I run
console core:archive once a day) though for some reason it didn’t happen 5 days ago…
Sorry forgot to mention the prefix might be
matomo_ or any other. If that worked, the user should have had the right privilege. Maybe the privileges were flushed within the last days and that's why it didn't happen? Fingers crossed it might just work. Otherwise not sure if MySQL has some features that prevent the locking somehow. In general the only thing I can imagine and mentioned earlier is when the user exists multiple times in MySQL for different hosts.
On my systems I can say, that FLUSH PRIVILEGES will not be the problem.
The error will happens randomly also after an complete restart of the database.
Today, on time ;), 5 days later the issue turned up again.
WARNING [2019-03-14 23:47:52] 14965 Piwik\Plugins\PrivacyManager\LogDataPurger: LOCK TABLES privilege not granted; skipping unused actions purge
I suggest you maybe ask in the forums: https://forum.matomo.org maybe someone can help there. Or maybe there's something on google about this. Clearly MySQL thinks
LOCK TABLES privilege is not granted.
I'm hit by this issue too. Same MariaDB version.
I don't see how the commit is a fix, though.
The underlying problem seem too be that LOCK TABLES only works when autocommit is disabled. Unfortunately, turning it off breaks PHPBB3 login system, so it's not an option for me. I think the proper solution would be to disable it for the scheduler session.