Database Migration from 3.13.6 -> 4.0.0-b2: "Unknown column 'log_conversion.revenue'" #16546

I upgraded a 3.13.6 DEV instance to v4 via Git as I needed to update plugins I develop and also see how an upgrade would go. It seems all the required database migrations did not run when I executed core:update (unfortunately I didn't keep a copy of the output of the command. But it ran successfully to completion).
When run now I get

./console core:update                                               
Everything is already up to date.

I am now seeing the following errors in the interface and logs with DEBUG log enabled

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_conversion.revenue' in 'field list' - in plugin Goals
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_link_visit_action.product_price' in 'field list' - in plugin Goals

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_link_visit_action.idaction_product_sku' in 'where clause' - in plugin Goals

DEBUG QueuedTracking[2020-10-07 14:28:01 UTC] [88e71] Got exception: Error query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'config_client_type' in 'field list' In query: SELECT visit_last_action_time, visit_first_action_time, idvisitor, idvisit, user_id, visit_exit_idaction_url, visit_exit_idaction_name, visitor_returning, visitor_seconds_since_first, visitor_seconds_since_order, visitor_count_visits, visit_goal_buyer, location_country, location_region, location_city, location_latitude, location_longitude, referer_name, referer_keyword, referer_type, idsite, profilable, visit_entry_idaction_url, visit_total_actions, visit_total_interactions, visit_total_searches, referer_url, config_browser_name, config_client_type, config_device_brand, config_device_model, config_device_type, visit_total_events, visit_total_time, location_ip, location_browser_lang, last_idlink_va, custom_dimension_1, custom_dimension_2, custom_dimension_3, custom_dimension_4, custom_dimension_5, custom_var_k1, custom_var_v1, custom_var_k2, custom_var_v2, custom_var_k3, custom_var_v3, custom_var_k4, custom_var_v4, custom_var_k5, custom_var_v5  FROM piwik_log_visit FORCE INDEX (index_idsite_idvisitor)  WHERE idsite = ? AND visit_last_action_time <= ? AND idvisitor = ?

Also also was missing columns (last_idlink_va) for the CustomDimensions plugins which I did not have installed pre v4.0 but I was able to resolve those errors by running ./console plugins:uninstall CustomDimensions then git checkout plugins/CustomDimensions then ./console plugins:activate CustomDimensions which ran a migration check and fixed those issue.

Is there a way to force a migration check or some other way to best solve this? I see some of the other core plugins have no uninstall method so I can't go that route.

Thank you

Hi,

I also noticed the log_link_visit_action.product_price error in my upgrade. In my case it was (probably) caused by the fact that I had disabled the ecommerce plugin before the upgrade, but probably had it enabled before. Enabling it after the update caused Matomo to show the database update screen again for the plugin and fix the issue.

Maybe this is something that needs to be handled by the upgrade.

Updates through git can always be bit tricky under circumstances. @jbrule do you maybe remember if you were also running something like git submodule update --init to init all new submodules and update all the other submodules? This could eg explain the issue with custom dimensions.

As for the ecommerce issue: Do you remember if ecommerce plugin was maybe disabled?

It might not 100% be an update issue but more a problem that Matomo doesn't work now when the ecommerce plugin is disabled.

Alternatively we could also move the dimension to the corehome plugin or so.

Yes I did git submodule init. I did run into an issue where all the plugins
disabled. I'm just wondering the best way to correct it at this point.

On Wed, Oct 7, 2020, 2:53 PM Thomas Steur <notifications@github.com> wrote:

Updates through git can always be bit tricky under circumstances. @jbrule
https://github.com/jbrule do you maybe remember if you were also
running something like git submodule update --init to init all new
submodules and update all the other submodules? This could eg explain the
issue with custom dimensions.

As for the ecommerce issue: Do you remember if ecommerce plugin was maybe
disabled?

It might not 100% be an update issue but more a problem that Matomo
doesn't work now when the ecommerce plugin is disabled.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/matomo-org/matomo/issues/16546#issuecomment-705158700,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAF26ZVCM2IKDJSBPTLQXBTSJTBLPANCNFSM4SHQNJUQ
.

I assume those errors are occurring again when archiving is running. Seems the Goal Archiver always tries to archive the ecommerce items, even if the ecommerce plugin is disabled and the reports are actually not needed... Will issue a PR to change that.

@jbrule Regarding the config_client_type error. Did you disable the DevicesDetection plugin aswell? Wondering why the dimension should actually be used if the plugin is disabled or why it should be missing when its activated...

The next beta also includes some update fixes from https://github.com/matomo-org/matomo/pull/16490 maybe the dimensions weren't correctly updated.

@jbrule if you could let us know if the DevicesDetection plugin is disabled as well that be great so we can decide whether to close the issue or if maybe more work is needed.

@jbrule to fix your issue below are some SQL statements you can run for any possibly missing column (just pick the ones for the columns that are missing). If you have a table prefix configured in Matomo, such as matomo_ or piwik_ then you will need to prefix the table names like log_visit turns into matomo_log_visit. I would let you know which ones you need to add but I'm not sure which ones are missing. Sorry for all the trouble.

ALTER TABLE log_link_visit_action ADD COLUMN search_cat VARCHAR(200) NULL;
ALTER TABLE log_link_visit_action ADD COLUMN search_count INTEGER(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN idaction_product_cat INT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN idaction_product_cat2 INT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN idaction_product_cat3 INT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN idaction_product_cat4 INT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN idaction_product_cat5 INT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN idaction_product_name INT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN product_price DOUBLE NULL;
ALTER TABLE log_link_visit_action ADD COLUMN idaction_product_sku INT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN time_dom_completion MEDIUMINT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN time_dom_processing MEDIUMINT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN time_network MEDIUMINT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN time_on_load MEDIUMINT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN time_server MEDIUMINT(10) UNSIGNED NULL;
ALTER TABLE log_link_visit_action ADD COLUMN time_transfer MEDIUMINT(10) UNSIGNED NULL;
ALTER TABLE log_visit ADD COLUMN profilable TINYINT(1) NULL;
ALTER TABLE log_visit ADD COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL;
ALTER TABLE log_visit ADD COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL;
ALTER TABLE log_visit ADD COLUMN visitor_seconds_since_last INT(11) UNSIGNED NULL;
ALTER TABLE log_visit ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL;
ALTER TABLE log_conversion ADD COLUMN revenue DOUBLE NULL DEFAULT NULL;
ALTER TABLE log_conversion ADD COLUMN revenue_discount DOUBLE NULL DEFAULT NULL;
ALTER TABLE log_conversion ADD COLUMN revenue_shipping DOUBLE NULL DEFAULT NULL;
ALTER TABLE log_conversion ADD COLUMN revenue_subtotal DOUBLE NULL DEFAULT NULL;
ALTER TABLE log_conversion ADD COLUMN revenue_tax DOUBLE NULL DEFAULT NULL;
ALTER TABLE log_conversion ADD COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL;
ALTER TABLE log_conversion ADD COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL;
ALTER TABLE log_conversion ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL;

@sgiehl re devices detection I'm thinking in this case the dimensions were likely ignored eg because they weren't displayed or otherwise ignored because of a bug maybe (which we would have fixed now).

Now I get

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_conversion.config_browser_name' in 'field list' - in plugin DevicesDetection

I have a DB snapshot from before 4.x migration so I am going to import into another DB. Point 4.x at it and see what happens.

Sorry about that @jbrule you will also need to execute

ALTER TABLE config_browser_name ADD COLUMN VARCHAR(40) NULL;

I didn't realise that column changed too.

I'll close this issue for now as it looks like this is fixed by now regarding the dimension updates.

@jbrule if there are still schema changes missing or so be great to let us know.

ALTER TABLE config_browser_name ADD COLUMN VARCHAR(40) NULL;

Do you mean?

 ALTER TABLE log_conversion ADD COLUMN config_browser_name VARCHAR(40) NULL;

I updated to 4.0.0 today and I was missing exactly this column. I can't remember exactly which version I had before.

Yes that's what I meant @alex1702 👍

I can confirm that. This issue happened also for my environment.

Version transition: Matomo database will be upgraded from version 3.14.1 to the new version 4.0.5.

Problem:

Error: Got invalid response from API request: "result":"error","message":"Mysqli prepare error: Unknown column 'log_conversion.config_browser_name'

Uncaught exception: /opt/piwik/core/CronArchive.php(600)

Solution:

ALTER TABLE piwik_log_conversion ADD COLUMN config_browser_name VARCHAR(40) NULL;

I have a similar problem:

Upgrade from 3 to 4.1 over console: No stats on matomo anymore...

Hi @maltsero seems something went wrong there during the update. Any chance you can make modifications to the database by running a MySQL query?

If that's the case, can you execute:

ALTER TABLE matomo_log_visit ADD COLUMN config_client_type TINYINT( 1 ) NULL DEFAULT NULL;

You might need to replace the matomo_ in matomo_log_visit with your table prefix. So this could be eg just log_visit or piwik_log_visit for example.