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
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 withUPDATE '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:
Now i changed it to
Did i missed something important?
After that the update worked as expected. The tables have the needed structure.
BUT these problems remain:
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