Real Time Visits Error with 4.9 update #19129

Expected Behavior

The Visits in Real-time dashboard widget, Visits in Real-time page and Real Time Visitor Count dashboard widget are all failing.

Current Behavior

There is an error on the page of:

Oops… there was a problem during the request. Maybe the server had a temporary issue, or maybe you requested a report with too much data. Please try again. If this error occurs repeatedly please contact your Matomo administrator for assistance.

There is a error in the PHP error log of:

Error in Matomo: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Possible Solution

Steps to Reproduce (for Bugs)

Error on the following URLs:

1. /index.php?module=CoreHome&action=index&idSite=1&period=day&date=today#?period=day&date=today&category=Dashboard_Dashboard&subcategory=1
2. /index.php?module=CoreHome&action=index&idSite=1&period=day&date=today#?idSite=1&period=day&date=today&category=General_Visitors&subcategory=General_RealTime

Context

Your Environment

Click to view System Check ### Mandatory checks #### PHP version >= 7.2.5: ✔ 8.0.18 #### PDO extension: ✔ #### PDO\MYSQL extension: ✔ #### MYSQLI extension: ✔ #### Other required extensions: ✔ zlib ✔ json ✔ filter ✔ hash ✔ session #### Required functions: ✔ debug_backtrace ✔ eval ✔ hash ✔ gzcompress ✔ gzuncompress ✔ pack #### Required PHP configuration (php.ini): ✔ session.auto_start = 0 ✔ max_execution_time = 0 OR = -1 OR >= 30 #### Directories with write access: ✔ $DOC_ROOT/tmp ✔ $DOC_ROOT/tmp/assets ✔ $DOC_ROOT/tmp/cache ✔ $DOC_ROOT/tmp/climulti ✔ $DOC_ROOT/tmp/latest ✔ $DOC_ROOT/tmp/logs ✔ $DOC_ROOT/tmp/sessions ✔ $DOC_ROOT/tmp/tcpdf ✔ $DOC_ROOT/tmp/templates_c ### Optional checks #### Required Private Directories: ✔ All private directories are inaccessible from the internet. #### Recommended Private Directories: ✔ All private directories are inaccessible from the internet. #### File integrity: ⚠ Warning: File integrity check failed and reported some errors. You should fix this issue and then refresh this page until it shows no error.

Files were found in your Matomo, but we didn't expect them.
--> Please delete these files to prevent errors. #### 64-bit PHP Binary: ✔ #### Tracker status: ✔ #### Memory limit: ✔ 128M #### Time zone: ✔ #### Open URL: ✔ curl #### PageSpeed is turned off: ✔ #### GD > 2.x + FreeType (graphics): ✔ #### Other extensions: ✔ json ✔ libxml ✔ dom ✔ SimpleXML ✔ openssl #### Other functions: ✔ shell_exec ✔ set_time_limit ✔ mail ✔ parse_ini_file ✔ glob ✔ gzopen ✔ md5_file #### Filesystem: ✔ #### Set up Cron - Managing processes via CLI: not supported (optional) (Reasons: Unknown) learn more #### Last Successful Archiving Completion: ✔ The archiving process completed successfully 00:04:14 ago. #### Database abilities: ✔ UTF8mb4 charset ✔ LOAD DATA INFILE ✔ CREATE TEMPORARY TABLES ✔ Changing transaction isolation level #### Max Packet Size: ✔ #### Forced SSL Connection: ✔ #### Geolocation: ✔ geoip2php (continent_code, continent_name, country_code, country_name, region_code, region_name, city_name, postal_code, lat, long) #### Update over HTTPS: ✔ #### Writable JavaScript Tracker ("/matomo.js" & "/piwik.js"): ✔ ### Informational results #### Matomo Version: 4.9.0 #### Matomo Update History: 4.7.0,4.6.2,4.6.1,4.5.0,4.4.1,4.3.1 #### Matomo Install Version: Unknown - pre 3.8. #### Latest Available Version: 4.9.0 #### Is Git Deployment: 0 #### PHP_OS: Linux #### PHP_BINARY: /opt/cpanel/ea-php80/root/usr/bin/php-cgi #### PHP SAPI: cgi-fcgi #### Timezone Version: 0.system #### PHP Timezone: UTC #### PHP Time: 1650565096 #### PHP Datetime: 2022-04-21 18:18:16 #### PHP INI max_execution_time: 30 #### PHP INI post_max_size: 8M #### PHP INI max_input_vars: 1000 #### PHP INI zlib.output_compression: #### Curl Version: 7.81.0, OpenSSL/1.1.1n #### Suhosin Installed: 0 #### DB Prefix: CCpiwik_ #### DB Charset: utf8mb4 #### DB Adapter: PDO\MYSQL #### MySQL Version: 10.2.43-MariaDB #### Num Tables: 333 #### Browser Segment Archiving Enabled: 1 #### Development Mode Enabled: 0 #### Internet Enabled: 1 #### Multi Server Environment: 0 #### Auto Update Enabled: 1 #### Custom User Path: 0 #### Custom Include Path: 0 #### Release Channel: latest_stable #### Plugins Activated: API, Actions, Annotations, BulkTracking, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CoreVue, CustomDimensions, CustomJsTracker, DBStats, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Events, GeoIp2, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, Login, Marketplace, Monolog, Morpheus, Overlay, PagePerformance, ProfessionalServices, Proxy, Referrers, Resolution, SEO, ScheduledReports, SegmentEditor, SitesManager, Tour, Transitions, TwoFactorAuth, UserCountry, UserId, UserLanguage, UsersManager, VisitFrequency, VisitTime, VisitorInterest, VisitsSummary, WebsiteMeasurable #### Plugins Deactivated: Contents, CustomVariables 4.0.1, Ecommerce, Feedback, MobileAppMeasurable, MobileMessaging, MultiSites, PrivacyManager, Provider 4.0.3, QueuedTracking 4.0.2, RssWidget, TagManager, UserCountryMap, Widgetize #### Plugins Invalid: #### Server Info: Apache #### Had visits in last 1 day: 1 #### Had visits in last 3 days: 1 #### Had visits in last 5 days: 1 #### Archive Time Last Started: 1650564841 #### Archive Time Last Finished: 1650564841 #### User Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Safari/537.36 #### Browser Language: en-us,en #### Total Invalidation Count: 0 #### In Progress Invalidation Count: 0 #### Scheduled Invalidation Count: 0 #### Earliest invalidation ts_started: #### Latest invalidation ts_started: #### Earliest invalidation ts_invalidated: #### Latest invalidation ts_invalidated: #### Number of segment invalidations: 0 #### Number of plugin invalidations: 0 #### List of plugins being invalidated:

Hi @ericimprint, thanks for contacting us with this issue. I've not been able to recreate it locally so it might be specific to your installation/upgrade. From the error message it seems like the database query to get real time visit data isn't being generated correctly.

One possibility is that there are existing data table options set which are somehow affecting the query generation.

If possible, could you provide the results of the following query?

SELECT * FROM option WHERE option_name LIKE 'viewDataTableParameters_root_Live.%';

This will show any customizations for the real time visitor data tables. Resetting these options might resolve the issue.

Thanks for replying. That query (with my table prefix) returned no results. I have cleared the tmp folder as well with no change. I am happy to send a database dump or anything else if it will help.

@ericimprint Does the log file contain some more details in which file the error/exception raises?

The php error log didn't show anything else. Is there other logging I can turn on to help?
I did a clean install in another directory with a new database and fresh data from another site, and got the exact same error. So it is a server issue, not a historical setting or data issue. I updated the directory to use PHP 8.1 and PHP 7.4 and got the same error.

Click to view System Check ### Mandatory checks #### PHP version >= 7.2.5: ✔ 8.1.5 #### PDO extension: ✔ #### PDO\MYSQL extension: ✔ #### MYSQLI extension: ✔ #### Other required extensions: ✔ zlib ✔ json ✔ filter ✔ hash ✔ session #### Required functions: ✔ debug_backtrace ✔ eval ✔ hash ✔ gzcompress ✔ gzuncompress ✔ pack #### Required PHP configuration (php.ini): ✔ session.auto_start = 0 ✔ max_execution_time = 0 OR = -1 OR >= 30 #### Directories with write access: ✔ $DOC_ROOT/tmp ✔ $DOC_ROOT/tmp/assets ✔ $DOC_ROOT/tmp/cache ✔ $DOC_ROOT/tmp/climulti ✔ $DOC_ROOT/tmp/latest ✔ $DOC_ROOT/tmp/logs ✔ $DOC_ROOT/tmp/sessions ✔ $DOC_ROOT/tmp/tcpdf ✔ $DOC_ROOT/tmp/templates_c ### Optional checks #### Required Private Directories: ✔ All private directories are inaccessible from the internet. #### Recommended Private Directories: ✔ All private directories are inaccessible from the internet. #### File integrity: ✔ #### 64-bit PHP Binary: ✔ #### Tracker status: ✔ #### Memory limit: ✔ 256M #### Time zone: ✔ #### Open URL: ✔ curl #### PageSpeed is turned off: ✔ #### GD > 2.x + FreeType (graphics): ✔ #### Other extensions: ✔ json ✔ libxml ✔ dom ✔ SimpleXML ✔ openssl #### Other functions: ✔ shell_exec ✔ set_time_limit ✔ mail ✔ parse_ini_file ✔ glob ✔ gzopen ✔ md5_file #### Filesystem: ✔ #### Set up Cron (faster report-loading): ⚠ Warning: For optimal performance and a speedy Matomo, it is highly recommended to set up a crontab to automatically archive your reports, and to disable browser triggering in the Matomo settings. Learn more. #### Set up Cron - Managing processes via CLI: not supported (optional) (Reasons: Unknown) learn more #### Database abilities: ✔ UTF8mb4 charset ✔ LOAD DATA INFILE ✔ CREATE TEMPORARY TABLES ✔ Changing transaction isolation level #### Max Packet Size: ✔ #### Forced SSL Connection: ⚠ Warning: We recommend using Matomo over secure SSL connections only. To prevent insecure access over http, add force_ssl = 1 to the General section in your Matomo config/config.ini.php file. #### Geolocation: ✔ geoip2php (continent_code, continent_name, country_code, country_name, region_code, region_name, city_name, postal_code, lat, long) #### Update over HTTPS: ✔ #### Writable JavaScript Tracker ("/matomo.js"): ✔ ### Informational results #### Matomo Version: 4.9.0 #### Matomo Update History: 4.9.0, #### Matomo Install Version: 4.9.0 #### Latest Available Version: 4.9.0 #### Is Git Deployment: 0 #### PHP_OS: Linux #### PHP_BINARY: /opt/cpanel/ea-php81/root/usr/bin/php-cgi #### PHP SAPI: cgi-fcgi #### Timezone Version: 0.system #### PHP Timezone: UTC #### PHP Time: 1650904232 #### PHP Datetime: 2022-04-25 16:30:32 #### PHP INI max_execution_time: 30 #### PHP INI post_max_size: 8M #### PHP INI max_input_vars: 1000 #### PHP INI zlib.output_compression: #### Curl Version: 7.81.0, OpenSSL/1.1.1n #### Suhosin Installed: 0 #### DB Prefix: matomo_ #### DB Charset: utf8mb4 #### DB Adapter: PDO\MYSQL #### MySQL Version: 10.2.43-MariaDB #### Num Tables: 34 #### Browser Segment Archiving Enabled: 1 #### Development Mode Enabled: 0 #### Internet Enabled: 1 #### Multi Server Environment: 0 #### Auto Update Enabled: 1 #### Custom User Path: 0 #### Custom Include Path: 0 #### Release Channel: latest_stable #### Plugins Activated: API, Actions, Annotations, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CoreVue, CustomDimensions, CustomJsTracker, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, GeoIp2, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, Login, Marketplace, MobileMessaging, Monolog, Morpheus, MultiSites, Overlay, PagePerformance, PrivacyManager, ProfessionalServices, Proxy, Referrers, Resolution, RssWidget, SEO, ScheduledReports, SegmentEditor, SitesManager, Tour, Transitions, TwoFactorAuth, UserCountry, UserCountryMap, UserId, UserLanguage, UsersManager, VisitFrequency, VisitTime, VisitorInterest, VisitsSummary, WebsiteMeasurable, Widgetize #### Plugins Deactivated: DBStats, MobileAppMeasurable, TagManager #### Plugins Invalid: #### Server Info: Apache #### Had visits in last 1 day: 1 #### Had visits in last 3 days: 1 #### Had visits in last 5 days: 1 #### Archive Time Last Started: 0 #### Archive Time Last Finished: 0 #### User Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Safari/537.36 #### Browser Language: en-us,en #### Total Invalidation Count: 0 #### In Progress Invalidation Count: 0 #### Scheduled Invalidation Count: 0 #### Earliest invalidation ts_started: #### Latest invalidation ts_started: #### Earliest invalidation ts_invalidated: #### Latest invalidation ts_invalidated: #### Number of segment invalidations: 0 #### Number of plugin invalidations: 0 #### List of plugins being invalidated: #### Anonymize Referrer: #### Do Not Track enabled: 0

@ericimprint sorry for the delay. Did you also check the matomo log file in tmp/logs/matomo.log ?

4.9.1 didn't change anything.

@sgiehl I turned on logging and got:

ERROR Live[2022-04-29 14:59:13 UTC] [b504c] Uncaught exception in API: /home/imprint/stats.imprintitems.com/matomo/libs/Zend/Db/Statement/Pdo.php(236): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens [Query: ?date=yesterday&module=Live&action=widget&disableLink=0&widget=1&idSite=1&period=day, CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-04-29 14:59:13 UTC] [b504c] Uncaught exception: /home/imprint/stats.imprintitems.com/matomo/libs/Zend/Db/Statement/Pdo.php(236): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens [Query: ?date=yesterday&module=Live&action=widget&disableLink=0&widget=1&idSite=1&period=day, CLI mode: 0]

@ericimprint Would you mind sharing your system check results?

@sgiehl I have 2 sets of system check results above (old install and fresh install on the same system).

I switched to the MYSQLi adaptor and got the same problem. Error:

ERROR Live[2022-04-29 15:20:26 UTC] [8b581] Uncaught exception in API: /home/imprint/stats.imprintitems.com/matomo/libs/Zend/Db/Statement/Mysqli.php(70): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 [Query: ?date=yesterday&module=Live&action=widget&disableLink=0&widget=1&idSite=1&period=day, CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-04-29 15:20:26 UTC] [8b581] Uncaught exception: /home/imprint/stats.imprintitems.com/matomo/libs/Zend/Db/Statement/Mysqli.php(70): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 [Query: ?date=yesterday&module=Live&action=widget&disableLink=0&widget=1&idSite=1&period=day, CLI mode: 0]

Still present with 4.10

So I got back to testing this more and changing my PHP version back to 7.4.30 solved the problem. 8.0.20 and 8.1.7 both have errors above. I thought I had tested 7.4.30 before. Sorry.

Hi @ericimprint. Interesting that it works with PHP 7.4. But guess without the exact query it will be hard to further investigate and fix this issue. If you are still able to reproduce on PHP 8 you could try to enabled sql profiling (enable_sql_profiler) or sql logging (log_sql_queries) in config and see if you are able to look up the query in log afterwards.

Closing this issue for now. @ericimprint I hope this issue won't occur again with the latest releases. But if you spot this issue again later, don't hesitate to open another report. Cheers