Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Scheduler fails with "LOCK TABLES privilege not granted" #14167

Closed
tuxmaster5000 opened this issue Mar 7, 2019 · 14 comments
Closed

Scheduler fails with "LOCK TABLES privilege not granted" #14167

tuxmaster5000 opened this issue Mar 7, 2019 · 14 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Milestone

Comments

@tuxmaster5000
Copy link

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
Copy link
Member

tsteur commented Mar 7, 2019

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.

@tsteur tsteur closed this as completed Mar 7, 2019
@tsteur tsteur added the answered For when a question was asked and we referred to forum or answered it. label Mar 7, 2019
@tuxmaster5000
Copy link
Author

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

@tsteur
Copy link
Member

tsteur commented Mar 7, 2019

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

@tuxmaster5000
Copy link
Author

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

@Flakebi
Copy link
Contributor

Flakebi commented Mar 10, 2019

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
Copy link
Member

tsteur commented Mar 10, 2019

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

@Flakebi
Copy link
Contributor

Flakebi commented Mar 10, 2019

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
Copy link
Member

tsteur commented Mar 10, 2019

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
Copy link
Contributor

Flakebi commented Mar 10, 2019

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
Copy link
Member

tsteur commented Mar 10, 2019

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
Copy link
Author

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
Copy link
Contributor

Flakebi commented Mar 15, 2019

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
Copy link
Member

tsteur commented Mar 16, 2019

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.

@tsteur tsteur added Bug For errors / faults / flaws / inconsistencies etc. and removed answered For when a question was asked and we referred to forum or answered it. labels Mar 24, 2019
@tsteur tsteur added this to the 3.10.0 milestone Mar 24, 2019
@burner1024
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Projects
None yet
Development

No branches or pull requests

4 participants