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

Jump to bottom

Percona XtraDB Cluster prohibits use of GET_LOCK #14919

Closed
SystemZ opened this issue Sep 30, 2019 · 10 comments
Closed

Percona XtraDB Cluster prohibits use of GET_LOCK #14919

SystemZ opened this issue Sep 30, 2019 · 10 comments
Labels
answered For when a question was asked and we referred to forum or answered it.
Milestone
3.12.0

Comments

@SystemZ
Copy link

SystemZ commented Sep 30, 2019 •
edited

Hi,

I moved my Matomo instance recently from standard MariaDB master/slave setup to PXC multi master.

Running web interface seems fine but archiving in cron task doesn't work, this is a part of error:

Error: Got invalid response from API request: ?module=API&method=API.get&idSite=18&period=day&date=last4&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:18680:"SQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use of GET_LOCK with pxc_strict_mode = ENFORCING - in plugin Goals
...

Is there any workaround or chance to skip table locks for cron?
AFAIK changing pxc_strict_mode to something other than ENFORCING is a bad idea and that can break data in DB.
Maybe some optional flag for core:archive task like --skip-locks with some word of warning ?
I'm not sure if in Matomo code there is a ability to totally skip lock and data can be still OK.

This comment kinda suggests that Matomo should work without locks but it's not working for me:

matomo/core/Db.php

Line 509 in 54fa685

* **NOTE:** Piwik does not require the `LOCK TABLES` privilege to be available. Piwik

EDIT:
I found this pull request merged to future v3.12.0, can this change solve this error?
#14788
@tsteur
Copy link
Member

tsteur commented Sep 30, 2019

Can you try the latest beta? This particular get_lock might be removed.

@SystemZ
Copy link
Author

SystemZ commented Sep 30, 2019

Sure :)
I'm trying with 3.12.0-b4 and there is still error but different, unrelated to lock:

INFO [2019-09-30 01:33:11] 795  Error: Empty or invalid response '' for website id 17, Time elapsed: 1.083s, skipping
INFO [2019-09-30 01:33:11] 795  Error: Got invalid response from API request: ?module=API&method=API.get&idSite=18&period=day&date=last4&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:18024:"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'matomo.piwik_logtmpsegment2f31d32034994e0af461ae08a3bf0c91' doesn't exist - in plugin Goals #0 /var/www/html/piwik/core/ArchiveProcessor/Loader.php(127): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateAllPlugins('11', '0', false) #1 /var/www/html/piwik/core/ArchiveProcessor/Loader.php(77): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive('11', '0') #2 /var/www/html/piwik/core/ArchiveProcessor/Loader.php(63): Piwik\ArchiveProcessor\Loader->prepareArchiveImpl('VisitsSummary') #3 /var/www/html/piwik/core/Context.php(75): Piwik\ArchiveProcessor\Loader->Piwik\ArchiveProcessor\{closure}() #4 /var/www/html/piwik/core/ArchiveProcessor/Loader.php(64): Piwik\Context::changeIdSite(18, Object(Closure)) #5 /var/www/html/piwik/core/Archive.php(858): Piwik\ArchiveProcessor\Loader->prepareArchive('VisitsSummary') #6 /var/www/html/piwik/core/Archive.php(668): Piwik\Archive->prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) #7 /var/www/html/piwik/core/Archive.php(620): Piwik\Archive->cacheArchiveIdsAfterLaunching(Array, Array) #8 /var/www/html/piwik/core/Archive.php(549): Piwik\Archive->getArchiveIds(Array) #9 /var/www/html/piwik/core/Archive.php(303): Piwik\Archive->get(Array, 'numeric') #10 /var/www/html/piwik/plugins/VisitsSummary/API.php(36): Piwik\Archive->getDataTableFromNumeric(Array) #11 [internal function]: Piwik\Plugins\VisitsSummary\API->get('18', 'day', 'last4', false, Array) #12 /var/www/html/piwik/core/API/Proxy.php(237): call_user_func_array(Array, Array) #13 /var/www/html/piwik/core/Context.php(28): Piwik\API\Proxy->Piwik\API\{closure}() #14 /var/www/html/piwik/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) #15 /var/www/html/piwik/plugins/API/API.php(435): Piwik\API\Proxy->call('\\Piwik\\Plugins\\...', 'get', Array) #16 [internal function]: Piwik\Plugins\API\API->get('18', 'day', 'last4', false, Array) #17 /var/www/html/piwik/core/API/Proxy.php(237): call_user_func_array(Array, Array) #18 /var/www/html/piwik/core/Context.php(28): Piwik\API\Proxy->Piwik\API\{closure}() #19 /var/www/html/piwik/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) #20 /var/www/html/piwik/core/API/Request.php(265): Piwik\API\Proxy->call('\\Piwik\\Plugins\\...', 'get', Array) #21 /var/www/html/piwik/plugins/API/Controller.php(41): Piwik\API\Request->process() #22 [internal function]: Piwik\Plugins\API\Controller->index() #23 /var/www/html/piwik/core/FrontController.php(589): call_user_func_array(Array, Array) #24 /var/www/html/piwik/core/FrontController.php(165): Piwik\FrontController->doDispatch('API', false, Array) #25 /var/www/html/piwik/core/dispatch.php(34): Piwik\FrontController->dispatch() #26 /var/www/html/piwik/index.php(29): require_once('/var/www/html/p...') #27 /var/www/html/piwik/core/CliMulti/RequestCommand.php(79): require_once('/var/www/html/p...') #28 /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Piwik\CliMulti\RequestCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #29 /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #30 /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application->doRunCommand(Object(Piwik\CliMulti\RequestCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #31 [internal function]: Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #32 /var/www/html/piwik/core/Console.php(103): call_user_func(Array, Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #33 /var/www/html/piwik/core/Access.php(629): Piwik\Console->Piwik\{closure}() #34 /var/www/html/piwik/core/Console.php(104): Piwik\Access::doAsSuperUser(Object(Closure)) #35 /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Piwik\Console->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) #36 /var/www/html/piwik/console(29): Symfony\Component\Console\Application->run() #37 {main}, caused by: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'matomo.piwik_logtmpsegment2f31d32034994e0af461ae08a3bf0c91' doesn't exist #0 /var/www/html/piwik/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array) #1 /var/www/html/piwik/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #2 /var/www/html/piwik/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('/* trigger = Cr...', Array) #3 /var/www/html/piwik/core/Db/Adapter/Pdo/Mysql.php(309): Zend_Db_Adapter_Pdo_Abstract->query('/* trigger = Cr...', Array) #4 /var/www/html/piwik/core/DataAccess/LogAggregator.php(548): Piwik\Db\Adapter\Pdo\Mysql->query('/* trigger = Cr...', Array) #5 /var/www/html/piwik/core/ArchiveProcessor/PluginsArchiver.php(284): Piwik\DataAccess\LogAggregator->queryVisitsByDimension() #6 /var/www/html/piwik/core/ArchiveProcessor/PluginsArchiver.php(106): Piwik\ArchiveProcessor\PluginsArchiver->aggregateDayVisitsMetrics() #7 /var/www/html/piwik/core/ArchiveProcessor/Loader.php(102): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateCoreMetrics()
INFO [2019-09-30 01:33:11] 795  Error: Empty or invalid response '' for website id 18, Time elapsed: 0.888s, skipping
ERROR [2019-09-30 01:33:11] 795  18 total errors during this script execution, please investigate and try and fix these errors.

@tsteur
Copy link
Member

tsteur commented Sep 30, 2019

Do you have a DB reader configured? Do you use any plugins besides any plugins from Matomo/InnoCraft? Are they all up to date?

@SystemZ
Copy link
Author

SystemZ commented Sep 30, 2019 •
edited

I use only plugins included with Matomo, I didn't installed any external ones yet, in settings I see Core (59) plugins, no 3rd party.
I'm only using [database] section, no dedicated reader/writer.
I have default config from zip package besides this:

multi_server_environment = 1
session_save_handler = dbtable

I run multiple Matomo Docker containers on k8s

@tsteur
Copy link
Member

tsteur commented Sep 30, 2019

Percona XtraDB Cluster allows you to create temporary tables right? I suppose it would have triggered a different error if not.

@SystemZ
Copy link
Author

SystemZ commented Sep 30, 2019

I see this available engines in PMA, I use default settings for PXC:
image

I can test it on my setup just to be sure.
I just don't know what SQL code should I execute to test this in 100%.

@tsteur
Copy link
Member

tsteur commented Sep 30, 2019

something like create temporary table foobar (idvisit BIGINT(10) UNSIGNED NOT NULL)

@SystemZ
Copy link
Author

SystemZ commented Sep 30, 2019

Yep, it's ok, no error.

IIRC creation of tmp table and operating on it should be inside one transaction, right? Outside transaction it will disappear. Maybe it's the case here? I had really long break for writing SQL and real code recently so not sure :)

@tsteur
Copy link
Member

tsteur commented Sep 30, 2019

The table should exist until the DB connection is interrupt / closed (the session is closed) or until we delete the table. I'm not sure if XtraDB Cluster would behave differently here for some reason. We haven't had any issues with this so far otherwise.

@SystemZ
Copy link
Author

SystemZ commented Sep 30, 2019

Patch with archiving without LOCK works correctly :)
I managed to solve issue by using direct connection to DB instead of connection to ProxySQL.

It's possible that ProxySQL is not handling correctly SQL queries with temp tables that Matomo is currently using. Related: sysown/proxysql#939

@SystemZ SystemZ closed this as completed Sep 30, 2019
@tsteur tsteur added the answered For when a question was asked and we referred to forum or answered it. label Sep 30, 2019
@mattab mattab added this to the 3.12.0 milestone Oct 27, 2019
@mstenz mstenz mentioned this issue Nov 6, 2019
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Milestone
3.12.0
Development

No branches or pull requests
3 participants
@tsteur @mattab @SystemZ