Maybe related to #15108

Which DB adapter are you using? Do you know if it's PDO or MySQLI? What's your MySQL version? And do you know if your MySQL user has CREATE TEMPORARY TABLES permission?

This environment is a little bit more complicated. it uses a 3 node Percona XtraDB Cluster based on mysql 5.5. in between a proxysql mysql loadbalancer. The DB user on Percona side have ALL PRIVILEGES on the Matomo DB.
Both PDO and MySQLi is available in php, but don't know which one is used by Matomo then.
Please note that i have no "LOAD DATA INFILE" function available.
Just have seen this error:

Try #1: LOAD DATA INFILE : SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'tracking-live'@'%' (using password: YES)[28000],
Try #2: LOAD DATA LOCAL INFILE : SQLSTATE[HY000]: General error: 2 File '/app/tmp/assets/option-04f391914bdd70361410b14332e3b4b7.csv' not found (Errcode: 2)

This should normally not be the case as LOAD DATA INFILE should not be used and other method need to be used instead. Was something changed in this release regarding this?

You can disable enable_load_data_infile=0 in your config/config.ini.php see https://matomo.org/faq/troubleshooting/faq_194/

I don't think we changed anything related to that. It might be just that the message is now logged. AFAIK Matomo always tries to use load data infile and then falls back to regular insert if it's not available. By setting above config it will know directly it's not going to work...

As for the temporary tables: Do you know if that's maybe an issue with the proxysql mysql loadbalancer?

Thx,
i have now set this value. in The systemCheck it now gives this error message in the LOAD DATA INFILE section:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option
(option_name,option_value)
VALUES ('test_system_check1','1'' at line 1

The error messages when using segments of missing table are still there.
proxysql does not have any issues with temporary tables i would expect.

proxysql does not have any issues with temporary tables i would expect.

Could you maybe double check that? Temporary tables are usually bound to a connection/session. Not sure how the loadbalancer works and whether one connection for example would always go to the same DB?

The option error I cannot reproduce when load data infile is disabled. Are you familiar with PHP? If so, could you check what query it generates in https://github.com/matomo-org/matomo/blob/3.12.0/core/Db/BatchInsert.php#L41 by eg adding var_export($query);exit;

Yes, i am very familiar with PHP,
This is the output of the query.

'INSERT IGNORE INTO option (option_name,option_value) VALUES (?,?)'

I don't know why this is executed this way?

Not sure how the loadbalancer works and whether one connection for example would always go to the same DB?

i have one master db that is used for writes. all other db's are normally only used for reads. its master-master replication, so everything is up2date.

INSERT IGNORE INTO option (option_name,option_value) VALUES (?,?)

That query should work. Not sure why it fails for you. The ? would be replaced with actual value.

i have one master db that is used for writes. all other db's are normally only used for reads. its master-master replication, so everything is up2date.

In Matomo have you configured a reader DB?

In Matomo have you configured a reader DB?

no. proxysql is handling this. matomo have only one normal DB configuration.
Everything worked fine before the upgrade to 3.12.0.
This is the output of $row for the query:

array ( 0 => 'test_system_check1', 1 => '1', )

this error message is just in the System Check
#15115
The issue is something different here.

@mstenz

no. proxysql is handling this. matomo have only one normal DB configuration.

That would likely be the issue. I'd recommend you disable this behaviour and instead use the reader configuration in Matomo https://matomo.org/faq/how-to-install/faq_35746/

Hi,
a reader configuration in Matomo is not required beccause proxysql is handling this. Also there is no direct communication with the Databaseserver possible. My installation is a database cluster that automatically fails over to other nodes if one is unavailable. I don't think this is the issue here as the system was running for more than 2 years now. Just after the update the problem ocoured.

The issue now extended to all pages (not only when Segments are used. means the application is now unusable. Any other suggestion what I can check?

#14919
just found this issue which seems to be similar issue.

a reader configuration in Matomo is not required beccause proxysql is handling this.

@mstenz what I was meaning Matomo won't run well if you use proxysql for handling this. We do support in the application to configure one reader since the latest release see previously pasted link on how to configure it. Matomo will have random issues if you configure this on proxysql or something similar.

@mattab probably need to document this in our FAQ and also our long guide that we don't support MySQL proxies that send read queries to a reader. Instead they need to configure a reader in the Matomo app.

I have fixed this now with configuring proxysql to only use the writer node hostgroup (that contains only one writer) when the user is the Matomo db user.
I can now theoretically use your reader setting with a differnt username forcing proxysql to use a reader, but have not implemented yet.

Still i do not really understand why this issue now is introduced with version 3.12?

It would be related to temporary tables where it would likely create a temporary table on your writer, insert the data on the writer, but then tries to read the data from a reader where the temp table wouldn't exist. Even if it technically worked before, I doubt your Matomo was reporting 100% correct numbers since if there's just a small delay it would potentially create multiple visits/actions for the same visit, etc. Potentially archive data multiple times etc.

Found this commit which introduced the Problem:
7795db3
I will assume users with high traffic and clustered databases will be come very fast with raising issues by using this special mysql feature.

@mstenz we will document it in an FAQ.

As said, Matomo never supported something like proxysql to begin with and likely you would have gotten wrong data on occasion or even regularly. As mentioned in the other issue #15130 (comment) you can disable this behaviour for now using enable_segments_cache but in Matomo 4 this will no longer be possible very likely. Using the reader feature in Matomo should work though.

@mattab would maybe put this into the requirements just so it's clear as users might not search for it etc.

In https://matomo.org/docs/requirements/ could change

We recommend using MySQL 5.7+ or MariaDB for your database.

and add afterwards something like this:

Matomo won't work with a MySQL load balancer unless you can ensure that all query within an established connection is always sent to the same database server.

Now documented in https://matomo.org/docs/requirements/#mysql-user-requirements

Thanks for your feedback @mstenz