search

matomo-org / matomo

Empowering People Ethically with the leading open source alternative to Google Analytics that gives you full control over your data. Matomo lets you easily collect data from websites & apps and visualise this data and extract insights. Privacy is built-in. Liberating Web Analytics. Star us on Github? +1. And we love Pull Requests!
https://matomo.org/
GNU General Public License v3.0
19.93k stars 2.66k forks source link

[Bug] `database_reader` parameter not respected in Matomo configuration #22769

Closed reeslo closed 6 days ago

reeslo commented 1 week ago

What happened?

When using the database_reader configuration in Matomo to separate read and write operations (e.g., in a high-availability setup with a master and multiple read replicas), INSERT operations are still observed on the reader database. This behavior contradicts the expected functionality of directing only read operations to the reader.

What should happen?

The database_reader configuration should ensure that only read operations are executed on the specified reader database(s). Write operations such as INSERT, UPDATE, and DELETE should be strictly directed to the master database.

image

How can this be reproduced?

  1. Configure Matomo with a master database and a database_reader.
  2. Observe database queries, especially during high-traffic or archival tasks.
  3. Note any INSERT or write operations occurring on the reader database.

Matomo version

5.1.2

PHP version

8.1

Server operating system

Centos7

What browsers are you seeing the problem on?

No response

Computer operating system

No response

Relevant log output

ERROR API[2024-11-18 09:48:42 UTC] [34595] caused by: SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'db_matomo_ro'@'%' to database 'db_matomo'
ERROR API[2024-11-18 09:48:42 UTC] [34595] #0 /home/matomo/stable/libs/Zend/Db/Statement/Pdo.php(233): PDOStatement->execute()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #1 /home/matomo/stable/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #2 /home/matomo/stable/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #3 /home/matomo/stable/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #4 /home/matomo/stable/core/Db/Adapter/Pdo/Mysql.php(336): Zend_Db_Adapter_Pdo_Abstract->query()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #5 /home/matomo/stable/core/DataAccess/LogAggregator.php(293): Piwik\Db\Adapter\Pdo\Mysql->query()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #6 /home/matomo/stable/core/DataAccess/LogAggregator.php(421): Piwik\DataAccess\LogAggregator->createTemporaryTable()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #7 /home/matomo/stable/core/DataAccess/LogAggregator.php(356): Piwik\DataAccess\LogAggregator->createSegmentTable()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #8 /home/matomo/stable/core/DataAccess/LogAggregator.php(660): Piwik\DataAccess\LogAggregator->generateQuery()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #9 /home/matomo/stable/core/DataAccess/LogAggregator.php(603): Piwik\DataAccess\LogAggregator->getQueryByDimensionSql()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #10 /home/matomo/stable/core/ArchiveProcessor.php(619): Piwik\DataAccess\LogAggregator->queryVisitsByDimension()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #11 /home/matomo/stable/core/ArchiveProcessor.php(556): Piwik\ArchiveProcessor->computeNbUniques()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #12 /home/matomo/stable/core/ArchiveProcessor.php(714): Piwik\ArchiveProcessor->enrichWithUniqueVisitorsMetric()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #13 /home/matomo/stable/core/ArchiveProcessor.php(284): Piwik\ArchiveProcessor->getAggregatedNumericMetrics()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #14 /home/matomo/stable/core/ArchiveProcessor/PluginsArchiver.php(337): Piwik\ArchiveProcessor->aggregateNumericMetrics()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #15 /home/matomo/stable/core/ArchiveProcessor/PluginsArchiver.php(109): Piwik\ArchiveProcessor\PluginsArchiver->aggregateMultipleVisitsMetrics()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #16 /home/matomo/stable/core/ArchiveProcessor/Loader.php(311): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateCoreMetrics()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #17 /home/matomo/stable/core/ArchiveProcessor/Loader.php(186): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #18 /home/matomo/stable/core/ArchiveProcessor/Loader.php(165): Piwik\ArchiveProcessor\Loader->insertArchiveData()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #19 /home/matomo/stable/core/ArchiveProcessor/Loader.php(105): Piwik\ArchiveProcessor\Loader->prepareArchiveImpl()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #20 /home/matomo/stable/core/Context.php(76): Piwik\ArchiveProcessor\Loader->Piwik\ArchiveProcessor\{closure}()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #21 /home/matomo/stable/core/ArchiveProcessor/Loader.php(109): Piwik\Context::changeIdSite()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #22 /home/matomo/stable/plugins/CoreAdminHome/API.php(306): Piwik\ArchiveProcessor\Loader->prepareArchive()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #23 [internal function]: Piwik\Plugins\CoreAdminHome\API->archiveReports()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #24 /home/matomo/stable/core/API/Proxy.php(255): call_user_func_array()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #25 /home/matomo/stable/core/Context.php(29): Piwik\API\Proxy->Piwik\API\{closure}()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #26 /home/matomo/stable/core/API/Proxy.php(346): Piwik\Context::executeWithQueryParameters()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #27 /home/matomo/stable/core/API/Request.php(274): Piwik\API\Proxy->call()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #28 /home/matomo/stable/plugins/API/Controller.php(46): Piwik\API\Request->process()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #29 [internal function]: Piwik\Plugins\API\Controller->index()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #30 /home/matomo/stable/core/FrontController.php(645): call_user_func_array()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #31 /home/matomo/stable/core/FrontController.php(169): Piwik\FrontController->doDispatch()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #32 /home/matomo/stable/core/dispatch.php(33): Piwik\FrontController->dispatch()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #33 /home/matomo/stable/index.php(25): require_once('...')
ERROR API[2024-11-18 09:48:42 UTC] [34595] #34 /home/matomo/stable/core/CliMulti/RequestCommand.php(76): require_once('...')
ERROR API[2024-11-18 09:48:42 UTC] [34595] #35 /home/matomo/stable/core/Plugin/ConsoleCommand.php(109): Piwik\CliMulti\RequestCommand->doExecute()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #36 /home/matomo/stable/vendor/symfony/console/Command/Command.php(298): Piwik\Plugin\ConsoleCommand->execute()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #37 /home/matomo/stable/core/Plugin/ConsoleCommand.php(124): Symfony\Component\Console\Command\Command->run()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #38 /home/matomo/stable/vendor/symfony/console/Application.php(1040): Piwik\Plugin\ConsoleCommand->run()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #39 /home/matomo/stable/vendor/symfony/console/Application.php(301): Symfony\Component\Console\Application->doRunCommand()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #40 /home/matomo/stable/core/Console.php(113): Symfony\Component\Console\Application->doRun()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #41 [internal function]: Piwik\Console->originDoRun()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #42 /home/matomo/stable/core/Console.php(152): call_user_func()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #43 /home/matomo/stable/core/Access.php(672): Piwik\Console->Piwik\{closure}()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #44 /home/matomo/stable/core/Console.php(153): Piwik\Access::doAsSuperUser()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #45 /home/matomo/stable/core/Console.php(92): Piwik\Console->doRunImpl()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #46 /home/matomo/stable/vendor/symfony/console/Application.php(171): Piwik\Console->doRun()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #47 /home/matomo/stable/console(32): Symfony\Component\Console\Application->run()
ERROR API[2024-11-18 09:48:42 UTC] [34595] #48 {main} [Query: ?module=API&method=CoreAdminHome.archiveReports&idSite=1&period=month&date=2024-11-01&format=json&segment=eventAction%3D%40Signed&trigger=archivephp&pid=792633b47464bb1d2c763f92a92e96662de25e7d59a2d77933f0d3b791cb109f7292afd7027e6c4af2c9436039ca1cc8b61b1&runid=51931, CLI mode: 1]
ERROR API[2024-11-18 09:48:42 UTC] [34599] Uncaught exception in API: /home/matomo/stable/libs/Zend/Db/Statement/Pdo.php(236): SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'db_matomo_ro'@'%' to database 'db_matomo'

Validations

sgiehl commented 1 week ago

@reeslo This is expected behavior and can't be solved differently. The logtmpsegment* tables are TEMPORARY tables, that are created during archiving, to speed up various queries, while archiving a certain segment. As temporary tables are bound to the connection it would not be possible to create the table on the writer, but use it one the reader, without persisting the table.

reeslo commented 6 days ago

Thanks for you reply I will discuss this with my web hosting provider

sgiehl commented 6 days ago

@reeslo I'll mark this as won't fix for now. If you have any further questions or suggestions around this topic, feel free to comment here nevertheless.