Problem with SQL table after migrating from Matomo 3 to Matomo 4 #17762

BRVN01 · 2021-07-13T14:40:49Z

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 · 2021-07-13T17:46:49Z

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 · 2021-07-13T18:30:36Z

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 · 2021-07-13T18:33:37Z

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 · 2021-07-13T18:57:05Z

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 · 2021-07-13T19:41:13Z

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 · 2021-07-13T20:07:23Z

@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 · 2021-07-14T12:10:25Z

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 · 2021-07-14T20:07:22Z

Hi @BRVN01, thanks for the extra information, looks like there's a bug which I've fixed here: #17771 . Can you see if that fixes the issue for you?

BRVN01 · 2021-07-15T12:46:19Z

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.

BRVN01 added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Jul 13, 2021

diosmosis mentioned this issue Jul 14, 2021

If table prefix is not specified, the database abilities diagnostic can fail #17771

Merged

10 tasks

sgiehl closed this as completed in #17771 Jul 15, 2021

mattab added the not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. label Jul 28, 2021

tsteur removed the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Sep 12, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Problem with SQL table after migrating from Matomo 3 to Matomo 4 #17762

Problem with SQL table after migrating from Matomo 3 to Matomo 4 #17762

BRVN01 commented Jul 13, 2021

diosmosis commented Jul 13, 2021

BRVN01 commented Jul 13, 2021

BRVN01 commented Jul 13, 2021

diosmosis commented Jul 13, 2021

BRVN01 commented Jul 13, 2021

diosmosis commented Jul 13, 2021

BRVN01 commented Jul 14, 2021

diosmosis commented Jul 14, 2021

BRVN01 commented Jul 15, 2021

Navigation Menu

Problem with SQL table after migrating from Matomo 3 to Matomo 4 #17762

Problem with SQL table after migrating from Matomo 3 to Matomo 4 #17762

Comments

BRVN01 commented Jul 13, 2021

Current Behavior

Table OPTION

diosmosis commented Jul 13, 2021

BRVN01 commented Jul 13, 2021

BRVN01 commented Jul 13, 2021

diosmosis commented Jul 13, 2021

BRVN01 commented Jul 13, 2021

diosmosis commented Jul 13, 2021

BRVN01 commented Jul 14, 2021

diosmosis commented Jul 14, 2021

BRVN01 commented Jul 15, 2021