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.94k stars 2.66k forks source link

[Bug] visitEndServerDate greater than a number segment breaks archiving #21571

Open tsteur opened 1 year ago

tsteur commented 1 year ago

What happened?

Customer created a segment visitEndServerDate>3. The segment visitEndServerDate is the dimension "Time in UTC — date (time of last action)" in the segment selector.

The error we get is

piwik.ERROR: Uncaught exception in API: PDOException: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: '9' in /libs/Zend/Db/Statement/Pdo.php:233 Stack trace: #0 /libs/Zend/Db/Statement/Pdo.php(233): PDOStatement->execute() #1 /libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array) #2 /libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #3 /libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT IGNORE I...', Array) #4 /core/Db/Adapter/Pdo/Mysql.php(332): Zend_Db_Adapter_Pdo_Abstract->query('INSERT IGNORE I...', Array) #5 /core/DataAccess/LogAggregator.php(325): Piwik\Db\Adapter\Pdo\Mysql->query('INSERT IGNORE I...', Array) #6 /core/DataAccess/LogAggregator.php(413): Piwik\DataAccess\LogAggregator->createTemporaryTable('logtmpsegment3b...', 'SELECT /* trigg...', Array) #7 /core/DataAccess/LogAggregator.php(355): Piwik\DataAccess\LogAggregator->createSegmentTable() #8 /core/DataAccess/LogAggregator.php(573): Piwik\DataAccess\LogAggregator->generateQuery('count(distinct ...', Array, 'log_visit.visit...', '', '') #9 /core/ArchiveProcessor/PluginsArchiver.php(306): Piwik\DataAccess\LogAggregator->queryVisitsByDimension() #10 /va

What should happen?

No error when this report is being archived or looked at in the UI.

How can this be reproduced?

Possibly the segment needs to be created through the API as I believe through the UI it cannot be configured like this.

The error may show up when selecting the segment in the UI. In any case the error happens when triggering the archiving.

Matomo major version

Matomo 4

Matomo minor or patch Version

15

PHP version

.

Server operating system

.

What browsers are you seeing the problem on?

No response

Computer operating system

.

Relevant log output

No response

Validations

tsteur commented 1 year ago

Getting the same error with visitEndServerDate!=0;visitEndServerDate!=1;visitEndServerDate!=2;visitEndServerDate!=3;visitEndServerDate!=4;visitEndServerDate!=5;visitEndServerDate!=6;visitEndServerDate!=7;visitEndServerDate!=8;visitEndServerDate!=9

piwik.ERROR: Uncaught exception in API: PDOException: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: '0' in libs/Zend/Db/Statement/Pdo.php:233 Stack trace: #0 /var/www/html/libs/Zend/Db/Statement/Pdo.php(233):

tsteur commented 1 year ago

Maybe visitServerHour>9 was meant and checking for invalid values on segment creation would help to avoid these errors.

sgiehl commented 1 year ago

Yes. Guess it would make sense to check the provided values for validity at least before using them in a query