@khromov opened this Issue on November 6th 2019

Seems to be some issue with MySQL:

The error is:

SQLSTATE[HY000]: General error: 1665 Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT

Full error:

INFO [2019-11-06 17:09:51] 14902  - pre-processing all visits
ERROR [2019-11-06 17:09:52] 14902  Got invalid response from API request: ?module=API&method=API.get&idSite=11&period=day&date=last5&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:14893:"SQLSTATE[HY000]: General error: 1665 Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. <a href='/0'>#0</a> /var/www/xxx.khromov.se/htdocs/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo-&gt;_execute(Array) <a href='/1'>#1</a> /var/www/xxx.khromov.se/htdocs/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement-&gt;execute(Array) <a href='/2'>#2</a> /var/www/xxx.khromov.se/htdocs/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract-&gt;query('INSERT INTO piw...', Array
...
@khromov commented on November 6th 2019

Resolved by setting binlog-format=mixed in /etc/mysql/my.cnf, although I feel like Matomo should warn users about this somehow.

@tsteur commented on November 6th 2019 Member

@khromov just to understand things better: any particular reason you were using statement and not for example row?

@mattab wonder if we should recommend row (in case we haven't done this yet on an FAQ).

In general: Matomo should already catch the error and workaround this for statement in https://github.com/matomo-org/matomo/blob/3.12.0/core/Db/TransactionLevel.php#L53-L62 (we even have a comment re catching this error statement). @khromov is there any chance you could send us the full stack trace?

@manusfreedom commented on November 7th 2019

I think we got the problem, because our Matomo is connected to a MariaDB Galera Cluster and the binlog_format is ROW and we can't change:
https://mariadb.com/kb/en/library/mariadb-galera-cluster-known-limitations/

@manusfreedom commented on November 7th 2019

INFO [2019-11-07 15:16:48] 123809 Error: Got invalid response from API request: ?module=API&method=API.get&idSite=12&period=day&date=last12&format=php&trigger=archivephp. Response was 'Thu Nov 7 16:16:46 2019 (124058): Warning opcache.file_cache must be a full path of accessible directory. a:2:{s:6:"result";s:5:"error";s:7:"message";s:13138:"Mysqli prepare error: MySQL server has gone away - in plugin Goals <a href='/0'>#0</a> /home/mysite.com/public_html/core/ArchiveProcessor/Loader.php(127): Piwik\ArchiveProcessor\PluginsArchiver-&gt;callAggregateAllPlugins(455, '0', false) <a href='/1'>#1</a> /home/mysite.com/public_html/core/ArchiveProcessor/Loader.php(77): Piwik\ArchiveProcessor\Loader-&gt;prepareAllPluginsArchive(455, '0') <a href='/2'>#2</a> /home/mysite.com/public_html/core/ArchiveProcessor/Loader.php(63): Piwik\ArchiveProcessor\Loader-&gt;prepareArchiveImpl('VisitsSummary') <a href='/3'>#3</a> /home/mysite.com/public_html/core/Context.php(75): Piwik\ArchiveProcessor\Loader-&gt;Piwik\ArchiveProcessor\{closure}() <a href='/4'>#4</a> /home/mysite.com/public_html/core/ArchiveProcessor/Loader.php(64): Piwik\Context::changeIdSite(12, Object(Closure)) <a href='/5'>#5</a> /home/mysite.com/public_html/core/Archive.php(867): Piwik\ArchiveProcessor\Loader-&gt;prepareArchive('VisitsSummary') <a href='/6'>#6</a> /home/mysite.com/public_html/core/Archive.php(677): Piwik\Archive-&gt;prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) <a href='/7'>#7</a> /home/mysite.com/public_html/core/Archive.php(620): Piwik\Archive-&gt;cacheArchiveIdsAfterLaunching(Array, Array) <a href='/8'>#8</a> /home/mysite.com/public_html/core/Archive.php(549): Piwik\Archive-&gt;getArchiveIds(Array) <a href='/9'>#9</a> /home/mysite.com/public_html/core/Archive.php(303): Piwik\Archive-&gt;get(Array, 'numeric') <a href='/10'>#10</a> /home/mysite.com/public_html/plugins/VisitsSummary/API.php(36): Piwik\Archive-&gt;getDataTableFromNumeric(Array) <a href='/11'>#11</a> [internal function]: Piwik\Plugins\VisitsSummary\API-&gt;get('12', 'day', 'last12', false, Array) <a href='/12'>#12</a> /home/mysite.com/public_html/core/API/Proxy.php(237): call_user_func_array(Array, Array) <a href='/13'>#13</a> /home/mysite.com/public_html/core/Context.php(28): Piwik\API\Proxy-&gt;Piwik\API\{closure}() <a href='/14'>#14</a> /home/mysite.com/public_html/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) <a href='/15'>#15</a> /home/mysite.com/public_html/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('12', 'day', 'last12', false, Array) <a href='/17'>#17</a> /home/mysite.com/public_html/core/API/Proxy.php(237): call_user_func_array(Array, Array) <a href='/18'>#18</a> /home/mysite.com/public_html/core/Context.php(28): Piwik\API\Proxy-&gt;Piwik\API\{closure}() <a href='/19'>#19</a> /home/mysite.com/public_html/core/API/Proxy.php(328): Piwik\Context::executeWithQueryParameters(Array, Object(Closure)) <a href='/20'>#20</a> /home/mysite.com/public_html/core/API/Request.php(265): Piwik\API\Proxy-&gt;call('\\Piwik\\Plugins\\...', 'get', Array) <a href='/21'>#21</a> /home/mysite.com/public_html/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> /home/mysite.com/public_html/core/FrontController.php(589): call_user_func_array(Array, Array) <a href='/24'>#24</a> /home/mysite.com/public_html/core/FrontController.php(165): Piwik\FrontController-&gt;doDispatch('API', false, Array) <a href='/25'>#25</a> /home/mysite.com/public_html/core/dispatch.php(34): Piwik\FrontController-&gt;dispatch() <a href='/26'>#26</a> /home/mysite.com/public_html/index.php(29): require_once('/home/mysite.co...') <a href='/27'>#27</a> /home/mysite.com/public_html/core/CliMulti/RequestCommand.php(79): require_once('/home/mysite.co...') <a href='/28'>#28</a> /home/mysite.com/public_html/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> /home/mysite.com/public_html/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> /home/mysite.com/public_html/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> /home/mysite.com/public_html/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> /home/mysite.com/public_html/core/Access.php(639): Piwik\Console-&gt;Piwik\{closure}() <a href='/34'>#34</a> /home/mysite.com/public_html/core/Console.php(104): Piwik\Access::doAsSuperUser(Object(Closure)) <a href='/35'>#35</a> /home/mysite.com/public_html/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> /home/mysite.com/public_html/console(29): Symfony\Component\Console\Application-&gt;run() <a href='/37'>#37</a> {main}, caused by: Mysqli prepare error: MySQL server has gone away <a href='/0'>#0</a> /home/mysite.com/public_html/libs/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli-&gt;_prepare('CREATE TEMPORAR...') <a href='/1'>#1</a> /home/mysite.com/public_html/libs/Zend/Db/Adapter/Mysqli.php(417): Zend_Db_Statement-&gt;__construct(Object(Piwik\Db\Adapter\Mysqli), 'CREATE TEMPORAR...') <a href='/2'>#2</a> /home/mysite.com/public_html/lib INFO [2019-11-07 15:16:48] 123809 Error: Empty or invalid response '' for website id 12, Time elapsed: 1.905s, skipping

@tsteur commented on November 7th 2019 Member

@manusfreedom MySQL server has gone away seems like a different issue? https://matomo.org/faq/troubleshooting/faq_183/

@manusfreedom commented on November 7th 2019

@tsteur The problem comes with 3.12.0 update and nothing has change on our infrastructure and all https://matomo.org/faq/troubleshooting/faq_183/ is already respected.

@manens commented on December 22nd 2019

Same issue here with Galera, 3.13 do not work 3.11 is ok.

@mattab commented on January 21st 2020 Member

Can you please create a new issue with the full error message you're getting?

There are a few different error messages in this issue already (which seem not related to Matomo?) so will be better to tackle them separately. Thanks!

This Issue was closed on January 21st 2020
Powered by GitHub Issue Mirror