@tuxmaster5000 opened this Issue on March 7th 2019

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'

@tsteur commented on March 7th 2019 Member

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.

@tuxmaster5000 commented on March 7th 2019

Locking the matomo tables via the mysql console with the same user will work without any warnings.

@tsteur commented on March 7th 2019 Member

Did you check if eg the same user is defined multiple times in MySQL?

@tuxmaster5000 commented on March 7th 2019

Yes it is the same user, and it is only one time defined in the database.

@Flakebi commented on March 10th 2019 Contributor

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.

@tsteur commented on March 10th 2019 Member

Can you execute SELECT GET_LOCK('foo', 1) with that user?

@Flakebi commented on March 10th 2019 Contributor

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)
@tsteur commented on March 10th 2019 Member

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.

@Flakebi commented on March 10th 2019 Contributor

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)

I ran 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…

@tsteur commented on March 10th 2019 Member

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.

@tuxmaster5000 commented on March 10th 2019

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.

@Flakebi commented on March 15th 2019 Contributor

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
@tsteur commented on March 16th 2019 Member

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.

@burner1024 commented on May 15th 2019

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.

This Issue was closed on March 7th 2019
Powered by GitHub Issue Mirror