Matomo 4 - Missing field config_browser_name in table log_conversion #17181

I still have the problem that the column config_browser_name is not added by console core:update. I had to add it manualy. Otherwise console core:archive produces errors. (Matomo 4.1.1)

"Mysqli prepare error: Unknown column 'log_conversion.config_browser_name' in 'field list' - in plugin DevicesDetection

Related: #16868

What version were you updating from? After adding the column were you able to update successfully?

I made an update from version 3.13.3 to 4.1.1. I also had to add
ALTER TABLE piwik_log_visit ADD COLUMN config_client_type tinyint(11) NULL;
For debuging i installed a second new matomo in a different database. I can see some differences:

For example

• referer_name is varchar(70) in my old piwik/matomo-installation and we have referer_name varchar (255) in the new installation.
• referer_url is mediumtext and in the new installation it is varchar(1500)

After adding config_client_type tinyint and log_conversion.config_browser_name matomo works as i can see. I tried to redo the database update with
UPDATE 'piwik_option' SET option_value = "3.13.3" WHERE option_name = "version_core";
The updater had no errors.

@datango did you have the config_client_type and config_browser_name columns in your tables before yodatubg?

I found a problem with

ALTER TABLE piwik_log_visit MODIFY COLUMN visitor_count_visits INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN visit_total_interactions MEDIUMINT UNSIGNED DEFAULT 0, MODIFY COLUMN referer_name VARCHAR(255) NULL, MODIFY COLUMN referer_url VARCHAR(1500) NULL, MODIFY COLUMN config_browser_name VARCHAR(40) NULL, ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL, ADD COLUMN visitor_seconds_since_last INT(11) UNSIGNED NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL;
ERROR 1406 (22001): Data too long for column 'referer_url' at row 2364

Before update:
referer_url | text | YES | | NULL |
Update wants: MODIFY COLUMN referer_url VARCHAR(1500) NULL

SELECT @@SESSION.sql_mode;
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I removed "STRICT_TRANS_TABLES":
SET @@session.sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

ALTER TABLE piwik_log_visit MODIFY COLUMN visitor_count_visits INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN visit_total_interactions MEDIUMINT UNSIGNED DEFAULT 0, MODIFY COLUMN referer_name VARCHAR(255) NULL, MODIFY COLUMN referer_url VARCHAR(1500) NULL, MODIFY COLUMN config_browser_name VARCHAR(40) NULL, ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL, ADD COLUMN visitor_seconds_since_last INT(11) UNSIGNED NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL;
Query OK, 19974716 rows affected, 92 warnings (4 min 33.597 sec)
Records: 19974716 Duplicates: 0 Warnings: 92

92 Rows have a length of field referer_url longer then 1500;

MariaDB [matomo]> select referer_url from piwik_log_visit where CHAR_LENGTH(referer_url) > 1500;
92 Urls found

I am trying a new update right now. I come back with the result.

I think the update script should fail on errors like "ERROR 1406 (22001): Data too long for column 'referer_url' at row 2364".
And the SQL-Mode should be checked and/or set.

The sql is still not working cause of "dublicate modify COLUMN location_region char(3)" parts in this two rows:

Executing ALTER TABLE piwik_log_visit MODIFY COLUMN profilable TINYINT(1) NULL, MODIFY COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_count_visits INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN visit_total_interactions MEDIUMINT UNSIGNED DEFAULT 0, MODIFY COLUMN referer_name VARCHAR(255) NULL, MODIFY COLUMN referer_url VARCHAR(1500) NULL, MODIFY COLUMN config_browser_name VARCHAR(40) NULL, ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL, MODIFY COLUMN visitor_seconds_since_last INT(11) UNSIGNED NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL;... Done. [416 / 418]

Executing ALTER TABLE piwik_log_conversion MODIFY COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_count_visits INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN referer_name VARCHAR(255) NULL, ADD COLUMN config_browser_name VARCHAR(40) NULL, ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL;... Done. [417 / 418]

I called these sql commands after the updater manualy to get the error message:

MariaDB [matomo]> ALTER TABLE piwik_log_visit MODIFY COLUMN profilable TINYINT(1) NULL, MODIFY COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_count_visits INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN visit_total_interactions MEDIUMINT UNSIGNED DEFAULT 0, MODIFY COLUMN referer_name VARCHAR(255) NULL, MODIFY COLUMN referer_url VARCHAR(1500) NULL, MODIFY COLUMN config_browser_name VARCHAR(40) NULL, ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL, MODIFY COLUMN visitor_seconds_since_last INT(11) UNSIGNED NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL;
ERROR 1054 (42S22): Unknown column 'location_region' in 'piwik_log_visit'

MariaDB [matomo]> ALTER TABLE piwik_log_conversion MODIFY COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL, MODIFY COLUMN visitor_count_visits INT(11) UNSIGNED NOT NULL DEFAULT 0, MODIFY COLUMN referer_name VARCHAR(255) NULL, ADD COLUMN config_browser_name VARCHAR(40) NULL, ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL, MODIFY COLUMN location_region char(3) DEFAULT NULL;
ERROR 1054 (42S22): Unknown column 'location_region' in 'piwik_log_conversion'

Hi @datango how do did you update the files for Matomo 4? Did you use our one-click UI update or did you update the files manually? If you updated manually, did you remove all old files?

Any chance you have maybe some plugins disabled like the UserCountry plugin? (If you don't know how to find out you can go to Matomo Admin -> Plugins and check for deactivated plugins or you can check your config/config.ini.php if you see a line saying Plugins[] = "UserCountry")

It looks like for some reason the region column is missing. You could try to add it manually like this:

ALTER TABLE piwik_log_visit ADD COLUMN location_region char(3) DEFAULT NULL;
ALTER TABLE piwik_log_conversion ADD COLUMN location_region char(3) DEFAULT NULL

Hi @tsteur! I updated matomo manually (on a test system) like i do it since many years:

• Downloading and extracting the sources to the existing matomo folder
• running the core:update cli
• Checking the system status - removing files which are obsolete

Now i changed it to

• moving the existing folder to matomo-old
• extracting the matomo.zip to new/clean folder matomo
• copy the config/config.ini.php and needed plugins to the new folder
• running the core:update cli
• Checking the system status

Did i missed something important?

After that the update worked as expected. The tables have the needed structure.

BUT these problems remain:

1. The core:update cli does not stop, if there are errors. It always displayed that everything is fine. I think the cli should stop on every sql problem cause i dont want a message at the end of the script that before 5 hours there was a problem.
2. There is no real live check if the database is ok/has the right structure. That would be nice.
3. The sql_mode must be checked in the updater and there should be a matomo config for this - overriding the system default to a matomo compatible one (I removed "STRICT_TRANS_TABLES": see above).

What do you think?

Downloading and extracting the sources to the existing matomo folder, running the core:update cli, Checking the system status - removing files which are obsolete

This would have been potentially not quite correct and could have caused issues in the past unless you ran core:update again after removing the files as it might have caused to keep some DB columns that shouldn't been kept or so. Although likely it was fine to do it this way actually.

The new flow sounds definitely good and I understand everything works now?

Regarding the update error: When there is an error during the update it would stop if the error is in Matomo core (which most updates are). If there is an error as part of a plugin then it would continue but disable that particular plugin. I can see though that we should maybe also stop the update when there is an issue with a plugin that is bundled with core. That could be good indeed. I've created https://github.com/matomo-org/matomo/pull/17213

For the real live check I created this issue: https://github.com/matomo-org/matomo/issues/17211 be indeed great to have but also very time consuming and difficult to implement unfortunately. It could help though avoid various issues.

The sql_mode must be checked in the updater and there should be a matomo config for this - overriding the system default to a matomo compatible one (I removed "STRICT_TRANS_TABLES": see above).

Matomo sets the needed SQL mode (see https://github.com/matomo-org/matomo/blob/4.1.1/core/Db.php#L36 ) already every time we connect to the database. I don't know if it's maybe possible for MySQL to configure that it can't be overwritten?

If I understand things correctly then things are working again and we can close this issue?

Also if the sql_mode can not be forced to be set by matomo - it should be checked by matomo update script, that it is correctly set.

Yes, update works now. Thx!

Minor side note that may be of interest for others: I had the same error after automatically updating via bitnami/matomo docker image. For me it solves the issue by disabling and re-activating the mentioned "DevicesDetection" plugin from the UI.

Also if the sql_mode can not be forced to be set by matomo - it should be checked by matomo update script, that it is correctly set.

From what I see it should always be possible to set the sql_mode actually. Be great to let us know if it's possible to prevent this somehow. I'll close this for now but happy to follow up should there are ways to prevent changing the sql mode in a session. Thanks