@BRVN01 opened this Issue on July 13th 2021

Hello,
I don't know how this happened, but I see a problem after the upgrade from Matomo 3 to Matomo 4, the upgrade from Ubuntu 14.4 to Ubuntu 20.04 was performed as well.

Current Behavior

The GUI reports that there is an error in the SQL syntax:

Erro: Mysqli prepare error: 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 (?,?)' at line 1

I don't see the same reports when I run diagnostics via './console diagnostics: run --all', just from the GUI.

Apparently I have no problem, everything works fine, but I don't like this report. I can see that there is a table called 'option' in the Matomo database, but when I list the table structure, I can see the same error shown by the GUI, the same error happened when I list the data in this table too, is this table important? I didn't find her in the documentation.

Table OPTION

mysql> desc option;
ERROR 1064 (42000): 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' at line 1

mysql> select * from option;
ERROR 1064 (42000): 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' at line 1

I don't know if this table is the cause of the problem, but it seems to have some relationship, could someone help me with this error?

Thank you so much.

@diosmosis commented on July 13th 2021 Member

Hi @BRVN01, there should be backticks around option, for example:

SELECT * FROM `option`;

If the error occurs in Matomo, can you find the stack trace? (Adding define('PIWIK_PRINT_ERROR_BACKTRACE', 1); to /path/to/matomo/bootstrap.php should allow it to get printed out.)

@BRVN01 commented on July 13th 2021

This file (/path/to/matomo/bootstrap.php) does not exist, should i create it?

About the option table, other tables don't need backticks, thanks for that. The value in this tables option is a little strange, I can see something like this:

mysql> SELECT * FROM `option`;
+-------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This table looks broken, the value is not normal, can you tell me if it's like that?
I saw some important data in it, so I can't just remove it, but that doesn't affect the matomo app negatively, right?

@BRVN01 commented on July 13th 2021

It seems that mysql cannot create the output as it should be, everything is a little broken.

The desc command works fine:

mysql> desc `option`;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| option_name  | varchar(191) | NO   | PRI | NULL    |       |
| option_value | longtext     | NO   |     | NULL    |       |
| autoload     | tinyint      | NO   | MUL | 1       |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0,00 sec)
@diosmosis commented on July 13th 2021 Member

This file (/path/to/matomo/bootstrap.php) does not exist, should i create it?

Yes, the file will be read if it's there, it's one of the places for custom matomo config (like the define() that sets the backtrace). You can delete the file after or just remove the define().

This table looks broken, the value is not normal, can you tell me if it's like that?

That does looks strange, I'm not sure what could cause that. Can you try the following queries:

SELECT COUNT(*) FROM `option`
SHOW CREATE TABLE `option`
SHOW TABLE STATUS FROM `option`
@BRVN01 commented on July 13th 2021

Yes, the file will be read if it's there, it's one of the places for custom matomo config (like the define() that sets the backtrace). You can delete the file after or just remove the define().

Done, but the results are pretty much the same:

./console diagnostics:run --all
define('PIWIK_PRINT_ERROR_BACKTRACE', 1);
INFO [2021-07-13 19:20:40] 338260  Unable to test if mod_pagespeed is enabled: the request to http://unknown/./console?module=Installation&action=getEmptyPageForSystemCheck failed
PHP version >= 7.2.5: OK 7.4.3
PDO extension: OK 
PDO\MYSQL extension: OK 
MYSQLI extension: OK 
Other required extensions:
    - OK zlib
    - OK SPL
    - OK iconv
    - OK json
    - OK mbstring
    - OK Reflection
    - OK filter
    - OK hash
    - OK session
Required functions:
    - OK debug_backtrace
    - OK eval
    - OK hash
    - OK gzcompress
    - OK gzuncompress
    - OK pack
Required PHP configuration (php.ini):
    - OK session.auto_start = 0
    - OK max_execution_time = 0 OR >= 30
Directories with write access:
    - OK /var/www/html/tmp
    - OK /var/www/html/tmp/assets
    - OK /var/www/html/tmp/cache
    - OK /var/www/html/tmp/climulti
    - OK /var/www/html/tmp/latest
    - OK /var/www/html/tmp/logs
    - OK /var/www/html/tmp/sessions
    - OK /var/www/html/tmp/tcpdf
    - OK /var/www/html/tmp/templates_c
Required Private Directories: OK All private directories are inaccessible from the internet.
File integrity: OK 
Tracker status: OK 
Memory limit: OK There is no memory limit set
Time zone: OK 
Open URL: OK curl
PageSpeed disabled: OK 
GD > 2.x + Freetype (graphics): OK 
Other extensions:
    - OK json
    - OK libxml
    - OK dom
    - OK SimpleXML
    - OK openssl
Other functions:
    - OK shell_exec
    - OK set_time_limit
    - OK mail
    - OK parse_ini_file
    - OK glob
    - OK gzopen
    - OK md5_file
Filesystem: OK 
Setup Cron - Managing processes via CLI: OK Ok
Last Successful Archiving Completion: OK The archiving process completed successfully 00:12:16 ago.
Database abilities:
    - OK UTF8mb4 charset
    - OK LOAD DATA INFILE
    - OK CREATE TEMPORARY TABLES
    - OK Changing transaction isolation level
Max Packet Size: OK 
Forced SSL Connection: OK 
Geolocation: WARNING The default location provider guesses a visitor's country based on the language they use. This is not very accurate, so we recommend installing and using a geolocation database.
Update over HTTPS: OK 
Writable JavaScript Tracker ("/matomo.js" & "/piwik.js"): OK 
Matomo Version: INFORMATIONAL 4.3.1
Matomo Update History: INFORMATIONAL 4.3.1,3.14.0,
Matomo Install Version: INFORMATIONAL Unknown - pre 3.8.
Latest Available Version: INFORMATIONAL 4.3.1
Is Git Deployment: INFORMATIONAL 0
PHP_OS: INFORMATIONAL Linux
PHP_BINARY: INFORMATIONAL /usr/bin/php7.4
PHP SAPI: INFORMATIONAL cli
Timezone Version: INFORMATIONAL 0.system
PHP Timezone: INFORMATIONAL UTC
PHP Time: INFORMATIONAL 1626204040
PHP Datetime: INFORMATIONAL 2021-07-13 19:20:40
PHP INI max_execution_time: INFORMATIONAL 0
PHP INI post_max_size: INFORMATIONAL 8M
PHP INI max_input_vars: INFORMATIONAL 1000
PHP INI zlib.output_compression: INFORMATIONAL 
Curl Version: INFORMATIONAL 7.68.0, OpenSSL/1.1.1f
Suhosin Installed: INFORMATIONAL 0
DB Prefix: INFORMATIONAL 
DB Charset: INFORMATIONAL utf8mb4
DB Adapter: INFORMATIONAL MYSQLI
MySQL Version: INFORMATIONAL 8.0.25-0ubuntu0.20.04.1
Num Tables: INFORMATIONAL 344
Browser Segment Archiving Enabled: INFORMATIONAL 1
Development Mode Enabled: INFORMATIONAL 0
Internet Enabled: INFORMATIONAL 1
Multi Server Environment: INFORMATIONAL 0
Auto Update Enabled: INFORMATIONAL 1
Custom User Path: INFORMATIONAL 0
Custom Include Path: INFORMATIONAL 0
Release Channel: INFORMATIONAL latest_stable
Plugins Activated: INFORMATIONAL API, Actions, Annotations, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CustomDimensions, CustomJsTracker, CustomVariables, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, GeoIp2, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, LoginLdap 4.3.0, MarketingCampaignsReporting 4.0.4, 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: INFORMATIONAL DBStats, Login, MobileAppMeasurable, Provider, TagManager
Plugins Invalid: INFORMATIONAL 
Had visits in last 1 day: INFORMATIONAL 1
Had visits in last 3 days: INFORMATIONAL 1
Had visits in last 5 days: INFORMATIONAL 1
Archive Time Last Started: INFORMATIONAL 1626203101
Archive Time Last Finished: INFORMATIONAL 1626203304
Browser Language: INFORMATIONAL en-us
Total Invalidation Count: INFORMATIONAL 10
In Progress Invalidation Count: INFORMATIONAL 0
Scheduled Invalidation Count: INFORMATIONAL 10
Earliest invalidation ts_started: INFORMATIONAL 
Latest invalidation ts_started: INFORMATIONAL 
Earliest invalidation ts_invalidated: INFORMATIONAL 2021-07-12 23:05:04
Latest invalidation ts_invalidated: INFORMATIONAL 2021-07-12 23:08:18
Number of segment invalidations: INFORMATIONAL 0
Number of plugin invalidations: INFORMATIONAL 10
List of plugins being invalidated: INFORMATIONAL VisitFrequency
Anonymize Referrer: INFORMATIONAL 
Do Not Track enabled: INFORMATIONAL 0
1 warnings detected

The sql command worked fine, less the last one, my db is called 'piwik':

mysql> SELECT COUNT(*) FROM `option`
    -> ;
+----------+
| COUNT(*) |
+----------+
|      481 |
+----------+
1 row in set (0,00 sec)

mysql> SHOW CREATE TABLE `option`;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                  |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| option | CREATE TABLE `option` (
  `option_name` varchar(191) NOT NULL,
  `option_value` longtext NOT NULL,
  `autoload` tinyint NOT NULL DEFAULT '1',
  PRIMARY KEY (`option_name`),
  KEY `autoload` (`autoload`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

# Result of SHOW TABLE STATUS FROM `option`:
+--------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name                           | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+--------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| option                         | InnoDB |      10 | Dynamic    |      465 |            739 |      344064 |               0 |        49152 |         0 |           NULL | 2021-07-12 18:04:51 | 2021-07-13 16:22:04 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |

Follow the GUI report:

#### Database capabilities:
  ✔ UTF8mb4 charset ⚠ Warning: LOAD DATA INFILE<br/>Using LOAD DATA INFILE will considerably speed up Matomo's 
archiving speed. To make it available for Matomo, try to update your PHP and MySQL, and confirm that your database user has 
the FILE privilege.<br/>If your Matomo server tracks sites with high traffic (eg more than 100,000 pages per month), we 
recommend that you try to solve this problem.<br/><strong>Error:</strong> Mysqli prepare error: You have an error in your SQL 
syntax; check the manual that matches your MySQL server version for the right syntax to use near 'option<br/> 
(option_name,option_value)<br/> VALUES (?,?)' at line 1<br/>Troubleshooting: FAQ on matomo.org 
✔ CREATE TEMPORARY TABLES 
✔ Changing transaction level isolation

During the migration tests I couldn't see this error, I thought it was related to the MySQL server, but now I think it's related to Matomo, I don't know anymore.

@diosmosis commented on July 13th 2021 Member

@BRVN01 can you make sure your bootstrap.php file looks like this:

<?php
define('PIWIK_PRINT_ERROR_BACKTRACE', 1);

Then trigger the original error you were seeing again. There should be a stack trace.

@BRVN01 commented on July 14th 2021

Hello,

The bootstrap.php file is now correct:

# cat bootstrap.php
<?php
define('PIWIK_PRINT_ERROR_BACKTRACE', 1);

But I got the same error, in the cli and gui:

INFO [2021-07-14 11:38:15] 494563  Unable to test if mod_pagespeed is enabled: the request to http://unknown/./console?module=Installation&action=getEmptyPageForSystemCheck failed
PHP version >= 7.2.5: OK 7.4.3
PDO extension: OK 
PDO\MYSQL extension: OK 
MYSQLI extension: OK 
Other required extensions:
    - OK zlib
    - OK SPL
    - OK iconv
    - OK json
    - OK mbstring
    - OK Reflection
    - OK filter
    - OK hash
    - OK session
Required functions:
    - OK debug_backtrace
    - OK eval
    - OK hash
    - OK gzcompress
    - OK gzuncompress
    - OK pack
Required PHP configuration (php.ini):
    - OK session.auto_start = 0
    - OK max_execution_time = 0 OR >= 30
Directories with write access:
    - OK /var/www/html/tmp
    - OK /var/www/html/tmp/assets
    - OK /var/www/html/tmp/cache
    - OK /var/www/html/tmp/climulti
    - OK /var/www/html/tmp/latest
    - OK /var/www/html/tmp/logs
    - OK /var/www/html/tmp/sessions
    - OK /var/www/html/tmp/tcpdf
    - OK /var/www/html/tmp/templates_c
Required Private Directories: OK All private directories are inaccessible from the internet.
File integrity: OK 
Tracker status: OK 
Memory limit: OK There is no memory limit set
Time zone: OK 
Open URL: OK curl
PageSpeed disabled: OK 
GD > 2.x + Freetype (graphics): OK 
Other extensions:
    - OK json
    - OK libxml
    - OK dom
    - OK SimpleXML
    - OK openssl
Other functions:
    - OK shell_exec
    - OK set_time_limit
    - OK mail
    - OK parse_ini_file
    - OK glob
    - OK gzopen
    - OK md5_file
Filesystem: OK 
Setup Cron - Managing processes via CLI: OK Ok
Last Successful Archiving Completion: OK The archiving process completed successfully 16:29:51 ago.
Database abilities:
    - OK UTF8mb4 charset
    - OK LOAD DATA INFILE
    - OK CREATE TEMPORARY TABLES
    - OK Changing transaction isolation level
Max Packet Size: OK 
Forced SSL Connection: OK 
Geolocation: WARNING The default location provider guesses a visitor's country based on the language they use. This is not very accurate, so we recommend installing and using a geolocation database.
Update over HTTPS: OK 
Writable JavaScript Tracker ("/matomo.js" & "/piwik.js"): OK 
Matomo Version: INFORMATIONAL 4.3.1
Matomo Update History: INFORMATIONAL 4.3.1,3.14.0,
Matomo Install Version: INFORMATIONAL Unknown - pre 3.8.
Latest Available Version: INFORMATIONAL 4.3.1
Is Git Deployment: INFORMATIONAL 0
PHP_OS: INFORMATIONAL Linux
PHP_BINARY: INFORMATIONAL /usr/bin/php7.4
PHP SAPI: INFORMATIONAL cli
Timezone Version: INFORMATIONAL 0.system
PHP Timezone: INFORMATIONAL UTC
PHP Time: INFORMATIONAL 1626262696
PHP Datetime: INFORMATIONAL 2021-07-14 11:38:16
PHP INI max_execution_time: INFORMATIONAL 0
PHP INI post_max_size: INFORMATIONAL 8M
PHP INI max_input_vars: INFORMATIONAL 1000
PHP INI zlib.output_compression: INFORMATIONAL 
Curl Version: INFORMATIONAL 7.68.0, OpenSSL/1.1.1f
Suhosin Installed: INFORMATIONAL 0
DB Prefix: INFORMATIONAL 
DB Charset: INFORMATIONAL utf8mb4
DB Adapter: INFORMATIONAL MYSQLI
MySQL Version: INFORMATIONAL 8.0.25-0ubuntu0.20.04.1
Num Tables: INFORMATIONAL 344
Browser Segment Archiving Enabled: INFORMATIONAL 1
Development Mode Enabled: INFORMATIONAL 0
Internet Enabled: INFORMATIONAL 1
Multi Server Environment: INFORMATIONAL 0
Auto Update Enabled: INFORMATIONAL 1
Custom User Path: INFORMATIONAL 0
Custom Include Path: INFORMATIONAL 0
Release Channel: INFORMATIONAL latest_stable
Plugins Activated: INFORMATIONAL API, Actions, Annotations, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CustomDimensions, CustomJsTracker, CustomVariables, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, GeoIp2, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, LoginLdap 4.3.0, MarketingCampaignsReporting 4.0.4, 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: INFORMATIONAL DBStats, Login, MobileAppMeasurable, Provider, TagManager
Plugins Invalid: INFORMATIONAL 
Had visits in last 1 day: INFORMATIONAL 1
Had visits in last 3 days: INFORMATIONAL 1
Had visits in last 5 days: INFORMATIONAL 1
Archive Time Last Started: INFORMATIONAL 1626260702
Archive Time Last Finished: INFORMATIONAL 1626203304
Browser Language: INFORMATIONAL en-us
Total Invalidation Count: INFORMATIONAL 87
In Progress Invalidation Count: INFORMATIONAL 0
Scheduled Invalidation Count: INFORMATIONAL 87
Earliest invalidation ts_started: INFORMATIONAL 
Latest invalidation ts_started: INFORMATIONAL 
Earliest invalidation ts_invalidated: INFORMATIONAL 2021-07-12 23:05:04
Latest invalidation ts_invalidated: INFORMATIONAL 2021-07-14 11:06:26
Number of segment invalidations: INFORMATIONAL 0
Number of plugin invalidations: INFORMATIONAL 10
List of plugins being invalidated: INFORMATIONAL VisitFrequency
Anonymize Referrer: INFORMATIONAL 
Do Not Track enabled: INFORMATIONAL 0
1 warnings detected

In the GUI:

#### Database abilities:
 ✔ UTF8mb4 charset 
⚠ Warning: LOAD DATA INFILE<br/>Using LOAD DATA INFILE will greatly speed Matomo's archiving process up. To make it 
available to Matomo, try updating your PHP & MySQL software and make sure your database user has the FILE privilege.<br/>If 
your Matomo server tracks high traffic websites (eg. > 100,000 pages per month), we recommend trying to fix this problem.
<br/><strong>Error:</strong> Mysqli prepare error: 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<br/> (option_name,option_value)<br/> VALUES (?,?)' at line 1<br/>Troubleshooting: FAQ on matomo.org 
✔ CREATE TEMPORARY TABLES 
✔ Changing transaction isolation level

The error appears to be the same error described in the link below. The OS and Matomo version are different though.
https://github.com/matomo-org/matomo/issues/12577

@diosmosis commented on July 14th 2021 Member

Hi @BRVN01, thanks for the extra information, looks like there's a bug which I've fixed here: https://github.com/matomo-org/matomo/pull/17771 . Can you see if that fixes the issue for you?

@BRVN01 commented on July 15th 2021

Hello,
Thanks for your help, you helped me a lot.

Just to record for those who have this problem, after editing these files, you need to change the file size in bytes and the md5 hash in the config/manifest.inc.php file.

This Issue was closed on July 15th 2021
Powered by GitHub Issue Mirror