Mysqli error when upgrading to matomo 4 beta

Support & Bugs
1

Hi. I get the following error when I want to open the Matomo surface after upgrading from matomo 3.14 to 4 beta. What/how exactly should I do to solve this issue? Please suggest.

Mysqli prepare error: 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

#matomo4-beta

2

Hi,

This is interesting.

Can you try enabling the SQL logging to see which SQL query your MySQL server complains about:
https://matomo.org/faq/troubleshooting/faq_115/

3

Thanks for caring. Here is my debug log:

DEBUG Piwik\Plugin\Manager[2020-10-13 06:11:11 UTC] [7a606] Loaded plugins: CorePluginsAdmin, CoreAdminHome, CoreHome, WebsiteMeasurable, IntranetMeasurable, Diagnostics, CoreVisualizations, Proxy, API, Widgetize, Transitions, LanguagesManager, Actions, Dashboard, MultiSites, Referrers, UserLanguage, DevicesDetection, Goals, Ecommerce, SEO, Events, UserCountry, GeoIp2, VisitsSummary, VisitFrequency, VisitTime, VisitorInterest, RssWidget, Feedback, Monolog, Login, TwoFactorAuth, UsersManager, SitesManager, Installation, CoreUpdater, CoreConsole, ScheduledReports, UserCountryMap, Live, PrivacyManager, ImageGraph, Annotations, MobileMessaging, Overlay, SegmentEditor, Insights, Morpheus, Contents, BulkTracking, Resolution, DevicePlugins, Heartbeat, Intl, Marketplace, ProfessionalServices, UserId, CustomJsTracker, Tour, PagePerformance, CustomDimensions
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] Uncaught exception: pathto/matomo/libs/Zend/Db/Statement/Mysqli.php(77): Mysqli prepare error: 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
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] Mysqli prepare error: 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
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #0 pathto/matomo/libs/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('SHOW TABLES LIK...')
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #1 pathto/matomo/libs/Zend/Db/Adapter/Mysqli.php(419): Zend_Db_Statement->__construct(Object(Piwik\Db\Adapter\Mysqli), 'SHOW TABLES LIK...')
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #2 pathto/matomo/libs/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('SHOW TABLES LIK...')
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #3 pathto/matomo/core/DbHelper.php(40): Zend_Db_Adapter_Abstract->query('SHOW TABLES LIK...', Array)
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #4 pathto/matomo/plugins/Login/Auth.php(68): Piwik\DbHelper::tableExists('user_token_auth')
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #5 pathto/matomo/core/Access.php(187): Piwik\Plugins\Login\Auth->authenticate()
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #6 pathto/matomo/core/FrontController.php(405): Piwik\Access->reloadAccess(Object(Piwik\Plugins\Login\Auth))
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #7 pathto/matomo/core/dispatch.php(31): Piwik\FrontController->init()
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #8 pathto/matomo/index.php(25): require_once('pathto...')
ERROR Piwik\ExceptionHandler[2020-10-13 06:11:11 UTC] [7a606] #9 {main}
DEBUG Piwik\Profiler[2020-10-13 06:11:11 UTC] [7a606] <hr /><strong>SQL Profiler</strong><hr /><strong>Summary</strong><br/>Executed 5 queries in 0.019 seconds(Average query length: 0.004 seconds)<br />Queries per second: 259.4<br />Longest query length: 0.019 seconds (<code>INSERT INTO matomo_session (id,modified,lifetime,data) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE modified = ?,lifetime = ?,data = ?</code>)
DEBUG Piwik\Profiler[2020-10-13 06:11:11 UTC] [7a606] <hr /><strong>Breakdown by query</strong><br/>Executed <b>1</b> time in <b>18.8ms</b>  <pre>	INSERT INTO matomo_session (id,modified,lifetime,data) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE modified = ?,lifetime = ?,data = ?</pre>Executed <b>1</b> time in <b>0.3ms</b>  <pre>	SELECT data FROM matomo_session WHERE id = ? AND modified + lifetime >= ?</pre>Executed <b>1</b> time in <b>0.1ms</b>  <pre>	SELECT DATABASE()</pre>Executed <b>1</b> time in <b>0.1ms</b>  <pre>	SELECT option_value, option_name FROM `matomo_option` WHERE autoload = 1</pre>Executed <b>1</b> time in <b>0.1ms</b>  <pre>	SELECT option_value FROM `matomo_option` WHERE option_name = ?</pre>
DEBUG Piwik\Profiler[2020-10-13 06:11:11 UTC] [7a606] Total queries = 5 (total sql time = 0.02s)
4

Hi,

I have created a Github issue for this:

5

Hi,

Can you please check if this fixes the issue for you:

6

Sorry for late answer - I couldn’t turn on this before.
Yes, I did the change as suggested and it worked for me.

7

Perfect,

This has been merged and should therefore be included in the next beta release.