Real Time Visits Error with 4.9 update #19129

ericimprint · 2022-04-21T18:21:42Z

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:

/index.php?module=CoreHome&action=index&idSite=1&period=day&date=today#?period=day&date=today&category=Dashboard_Dashboard&subcategory=1
/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:

bx80 · 2022-04-22T04:59:45Z

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.

ericimprint · 2022-04-22T15:13:31Z

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.

sgiehl · 2022-04-25T13:53:17Z

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

ericimprint · 2022-04-25T16:28:23Z

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

sgiehl · 2022-04-29T08:37:26Z

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

ericimprint · 2022-04-29T15:01:56Z

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]

sgiehl · 2022-04-29T15:21:05Z

@ericimprint Would you mind sharing your system check results?

ericimprint · 2022-04-29T15:24:34Z

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

ericimprint · 2022-05-16T21:09:29Z

Still present with 4.10

ericimprint · 2022-07-14T19:37:17Z

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.

sgiehl · 2022-07-15T08:36:38Z

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.

sgiehl · 2022-11-09T16:20:17Z

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

ericimprint added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Apr 21, 2022

sgiehl mentioned this issue Jun 27, 2022

PHP 8.1 error with Mysqli "The number of variables must match the number of parameters in the prepared statement" #19413

Closed

sgiehl closed this as completed Nov 9, 2022

sgiehl added worksforme The issue cannot be reproduced and things work as intended. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Nov 9, 2022

AdamSGit mentioned this issue Jun 16, 2023

Realtime visits error (see #19129) #20906

Closed

Real Time Visits Error with 4.9 update #19129

Real Time Visits Error with 4.9 update #19129

Comments

ericimprint commented Apr 21, 2022

Expected Behavior

Current Behavior

Possible Solution

Steps to Reproduce (for Bugs)

Context

Your Environment

Mandatory checks

PHP version >= 7.2.5:

PDO extension:

PDO\MYSQL extension:

MYSQLI extension:

Other required extensions:

Required functions:

Required PHP configuration (php.ini):

Directories with write access:

Optional checks

Required Private Directories:

Recommended Private Directories:

File integrity:

64-bit PHP Binary:

Tracker status:

Memory limit:

Time zone:

Open URL:

PageSpeed is turned off:

GD > 2.x + FreeType (graphics):

Other extensions:

Other functions:

Filesystem:

Set up Cron - Managing processes via CLI:

Last Successful Archiving Completion:

Database abilities:

Max Packet Size:

Forced SSL Connection:

Geolocation:

Update over HTTPS:

Writable JavaScript Tracker ("/matomo.js" & "/piwik.js"):

Informational results

Matomo Version:

Matomo Update History:

Matomo Install Version:

Latest Available Version:

Is Git Deployment:

PHP_OS:

PHP_BINARY:

PHP SAPI:

Timezone Version:

PHP Timezone:

PHP Time:

PHP Datetime:

PHP INI max_execution_time:

PHP INI post_max_size:

PHP INI max_input_vars:

PHP INI zlib.output_compression:

Curl Version:

Suhosin Installed:

DB Prefix:

DB Charset:

DB Adapter:

MySQL Version:

Num Tables:

Browser Segment Archiving Enabled:

Development Mode Enabled:

Internet Enabled:

Multi Server Environment:

Auto Update Enabled:

Custom User Path:

Custom Include Path:

Release Channel:

Plugins Activated:

Plugins Deactivated:

Plugins Invalid:

Server Info:

Had visits in last 1 day:

Had visits in last 3 days:

Had visits in last 5 days:

ericimprint commented Apr 25, 2022 •

edited