@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.

Powered by GitHub Issue Mirror