@mstenz opened this Issue on November 5th 2019

I receive a lot of these error messages when using Segments.
These type of tables does not exist (and I do not remember any similar one of them existed before). Here one example error message:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'tracking-live.logtmpsegment3e15c1e6dea7400cbf21c6af991d51b1' doesn't exist - in plugin Goals

I don't have a cron setup, just normal browser archiving.
Any Idea what might be wrong?

@tsteur commented on November 5th 2019 Member

Maybe related to https://github.com/matomo-org/matomo/issues/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?

@mstenz commented on November 5th 2019

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?

@tsteur commented on November 5th 2019 Member

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?

@mstenz commented on November 5th 2019

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.

@tsteur commented on November 5th 2019 Member

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;

@mstenz commented on November 5th 2019

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.

@tsteur commented on November 5th 2019 Member

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?

@mstenz commented on November 5th 2019

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', )

@mstenz commented on November 5th 2019

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

@tsteur commented on November 5th 2019 Member

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

@mstenz commented on November 6th 2019

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.

@mstenz commented on November 6th 2019

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?

@mstenz commented on November 6th 2019

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

@tsteur commented on November 6th 2019 Member

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.

@mstenz commented on November 6th 2019

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?

@tsteur commented on November 6th 2019 Member

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.

@mstenz commented on November 7th 2019

Found this commit which introduced the Problem:
https://github.com/matomo-org/matomo/pull/14761/commits/7795db37be3dd75d0a26e617ffc4ed3c2af83587
I will assume users with high traffic and clustered databases will be come very fast with raising issues by using this special mysql feature.

@tsteur commented on November 7th 2019 Member

@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 https://github.com/matomo-org/matomo/issues/15130#issuecomment-551252343 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.

Powered by GitHub Issue Mirror