@SystemZ opened this Issue on September 30th 2019

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:
https://github.com/matomo-org/matomo/blob/54fa6853b67c52220c42ad1e632a695ae757192c/core/Db.php#L509

EDIT:
I found this pull request merged to future v3.12.0, can this change solve this error?
https://github.com/matomo-org/matomo/pull/14788

@tsteur commented on September 30th 2019 Member

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

@SystemZ commented on September 30th 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 <a href='/0'>#0</a> /var/www/html/piwik/core/ArchiveProcessor/Loader.php(127): Piwik\ArchiveProcessor\PluginsArchiver-&gt;callAggregateAllPlugins('11', '0', false) <a href='/1'>#1</a> /var/www/html/piwik/core/ArchiveProcessor/Loader.php(77): Piwik\ArchiveProcessor\Loader-&gt;prepareAllPluginsArchive('11', '0') <a href='/2'>#2</a> /var/www/html/piwik/core/ArchiveProcessor/Loader.php(63): Piwik\ArchiveProcessor\Loader-&gt;prepareArchiveImpl('VisitsSummary') <a href='/3'>#3</a> /var/www/html/piwik/core/Context.php(75): Piwik\ArchiveProcessor\Loader-&gt;Piwik\ArchiveProcessor\{closure}() <a href='/4'>#4</a> /var/www/html/piwik/core/ArchiveProcessor/Loader.php(64): Piwik\Context::changeIdSite(18, Object(Closure)) <a href='/5'>#5</a> /var/www/html/piwik/core/Archive.php(858): Piwik\ArchiveProcessor\Loader-&gt;prepareArchive('VisitsSummary') <a href='/6'>#6</a> /var/www/html/piwik/core/Archive.php(668): Piwik\Archive-&gt;prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) <a href='/7'>#7</a> /var/www/html/piwik/core/Archive.php(620): Piwik\Archive-&gt;cacheArchiveIdsAfterLaunching(Array, Array) <a href='/8'>#8</a> /var/www/html/piwik/core/Archive.php(549): Piwik\Archive-&gt;getArchiveIds(Array) <a href='/9'>#9</a> /var/www/html/piwik/core/Archive.php(303): Piwik\Archive-&gt;get(Array, 'numeric') <a href='/10'>#10</a> /var/www/html/piwik/plugins/VisitsSummary/API.php(36): Piwik\Archive-&gt;getDataTableFromNumeric(Array) <a href='/11'>#11</a> [internal function]: Piwik\Plugins\VisitsSummary\API-&gt;get('18', 'day', 'last4', false, Array) <a href='/12'>#12</a> /var/www/html/piwik/core/API/Proxy.php(237): call_user_func_array(Array, Array) <a href='/13'>#13</a> /var/www/html/piwik/core/Context.php(28): Piwik\API\Proxy-&gt;Piwik\API\{closure}() <a href='/14'>#14</a> /var/www/html/piwik/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) <a href='/15'>#15</a> /var/www/html/piwik/plugins/API/API.php(435): Piwik\API\Proxy-&gt;call('\\Piwik\\Plugins\\...', 'get', Array) <a href='/16'>#16</a> [internal function]: Piwik\Plugins\API\API-&gt;get('18', 'day', 'last4', false, Array) <a href='/17'>#17</a> /var/www/html/piwik/core/API/Proxy.php(237): call_user_func_array(Array, Array) <a href='/18'>#18</a> /var/www/html/piwik/core/Context.php(28): Piwik\API\Proxy-&gt;Piwik\API\{closure}() <a href='/19'>#19</a> /var/www/html/piwik/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) <a href='/20'>#20</a> /var/www/html/piwik/core/API/Request.php(265): Piwik\API\Proxy-&gt;call('\\Piwik\\Plugins\\...', 'get', Array) <a href='/21'>#21</a> /var/www/html/piwik/plugins/API/Controller.php(41): Piwik\API\Request-&gt;process() <a href='/22'>#22</a> [internal function]: Piwik\Plugins\API\Controller-&gt;index() <a href='/23'>#23</a> /var/www/html/piwik/core/FrontController.php(589): call_user_func_array(Array, Array) <a href='/24'>#24</a> /var/www/html/piwik/core/FrontController.php(165): Piwik\FrontController-&gt;doDispatch('API', false, Array) <a href='/25'>#25</a> /var/www/html/piwik/core/dispatch.php(34): Piwik\FrontController-&gt;dispatch() <a href='/26'>#26</a> /var/www/html/piwik/index.php(29): require_once('/var/www/html/p...') <a href='/27'>#27</a> /var/www/html/piwik/core/CliMulti/RequestCommand.php(79): require_once('/var/www/html/p...') <a href='/28'>#28</a> /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Piwik\CliMulti\RequestCommand-&gt;execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/29'>#29</a> /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Symfony\Component\Console\Command\Command-&gt;run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/30'>#30</a> /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application-&gt;doRunCommand(Object(Piwik\CliMulti\RequestCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/31'>#31</a> [internal function]: Symfony\Component\Console\Application-&gt;doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/32'>#32</a> /var/www/html/piwik/core/Console.php(103): call_user_func(Array, Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/33'>#33</a> /var/www/html/piwik/core/Access.php(629): Piwik\Console-&gt;Piwik\{closure}() <a href='/34'>#34</a> /var/www/html/piwik/core/Console.php(104): Piwik\Access::doAsSuperUser(Object(Closure)) <a href='/35'>#35</a> /var/www/html/piwik/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Piwik\Console-&gt;doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)) <a href='/36'>#36</a> /var/www/html/piwik/console(29): Symfony\Component\Console\Application-&gt;run() <a href='/37'>#37</a> {main}, caused by: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'matomo.piwik_logtmpsegment2f31d32034994e0af461ae08a3bf0c91' doesn't exist <a href='/0'>#0</a> /var/www/html/piwik/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo-&gt;_execute(Array) <a href='/1'>#1</a> /var/www/html/piwik/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement-&gt;execute(Array) <a href='/2'>#2</a> /var/www/html/piwik/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract-&gt;query('/* trigger = Cr...', Array) <a href='/3'>#3</a> /var/www/html/piwik/core/Db/Adapter/Pdo/Mysql.php(309): Zend_Db_Adapter_Pdo_Abstract-&gt;query('/* trigger = Cr...', Array) <a href='/4'>#4</a> /var/www/html/piwik/core/DataAccess/LogAggregator.php(548): Piwik\Db\Adapter\Pdo\Mysql-&gt;query('/* trigger = Cr...', Array) <a href='/5'>#5</a> /var/www/html/piwik/core/ArchiveProcessor/PluginsArchiver.php(284): Piwik\DataAccess\LogAggregator-&gt;queryVisitsByDimension() <a href='/6'>#6</a> /var/www/html/piwik/core/ArchiveProcessor/PluginsArchiver.php(106): Piwik\ArchiveProcessor\PluginsArchiver-&gt;aggregateDayVisitsMetrics() <a href='/7'>#7</a> /var/www/html/piwik/core/ArchiveProcessor/Loader.php(102): Piwik\ArchiveProcessor\PluginsArchiver-&gt;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 commented on September 30th 2019 Member

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 commented on September 30th 2019

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 commented on September 30th 2019 Member

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

@SystemZ commented on September 30th 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 commented on September 30th 2019 Member

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

@SystemZ commented on September 30th 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 commented on September 30th 2019 Member

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 commented on September 30th 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: https://github.com/sysown/proxysql/issues/939

This Issue was closed on September 30th 2019
Powered by GitHub Issue Mirror