Hi @YamasakiRhys, sorry you're experiencing this issue. Do you know which columns are missing in the table that are also in the query (the error message does not say which columns it can't find)? Can you also describe how you updated your Matomo? Did you follow the manual update process or use the one click update feature?

Hi @diosmosis. Thank you for your response. We did a manual update at first for Matomo and were receiving an issue that sql wasn't completing this query because it was missing columns. It doesn't specifically state which columns are missing but from our table structure compared to the select statement, it seems that there are quite a few missing.
mysql> DESCRIBE matomo_qa.matomo_log_visit;
+------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+----------------+
| idvisit | bigint(10) unsigned | NO | PRI | NULL | auto_increment |
| idsite | int(10) unsigned | NO | MUL | NULL | |
| idvisitor | binary(8) | NO | | NULL | |
| visit_last_action_time | datetime | NO | | NULL | |
| config_id | binary(8) | NO | | NULL | |
| location_ip | varbinary(16) | NO | | NULL | |
| last_idlink_va | bigint(20) unsigned | YES | | NULL | |
| custom_dimension_1 | varchar(255) | YES | | NULL | |
| custom_dimension_2 | varchar(255) | YES | | NULL | |
| custom_dimension_3 | varchar(255) | YES | | NULL | |
| custom_dimension_4 | varchar(255) | YES | | NULL | |
| custom_dimension_5 | varchar(255) | YES | | NULL | |
+------------------------+---------------------+------+-----+---------+----------------+

This is our table structure as it is currently in our new db instance.

After having received that error we completely removed all Matomo files and created a new db instance and did a fresh install of 4.2.1 to see if that would resolve our issue but we're still receiving the same sql issue that we saw when we upgraded.
Is there some cache that needs to be cleared that we had missed? Thank you for your time

@YamasakiRhys It looks like no dimensions that aren't listed in the initial table definitions are added except CustomDimensions. I would say the mysql user doesn't have the privilege to alter tables, but in that case the custom_dimension_* columns wouldn't be added. Can you run the following query and post the results:

SELECT FROM `option` WHERE option_name LIKE 'version_log_%';

?

Hi @diosmosis.

We added the missing permissions but still seem to be getting an error.
Here is the result of what we received when running the query you suggested:

mysql> SELECT * FROM matomo_option WHERE option_name LIKE 'version_log_%';
+------------------------------------------------------------+------------------------------------------------------------------------------+----------+
| option_name | option_value | autoload |
+------------------------------------------------------------+------------------------------------------------------------------------------+----------+
| version_Login | 4.2.1 | 1 |
| version_log_conversion.revenue | float default NULL | 1 |
| version_log_link_visit_action.idaction_content_interaction | INTEGER(10) UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.idaction_content_name | INTEGER(10) UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.idaction_content_piece | INTEGER(10) UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.idaction_content_target | INTEGER(10) UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.idaction_event_action | INTEGER(10) UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.idaction_event_category | INTEGER(10) UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.idaction_name | INTEGER(10) UNSIGNED | 1 |
| version_log_link_visit_action.idaction_product_cat | INT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.idaction_product_cat2 | INT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.idaction_product_cat3 | INT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.idaction_product_cat4 | INT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.idaction_product_cat5 | INT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.idaction_product_name | INT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.idaction_product_sku | INT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.idaction_url | INTEGER(10) UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.idpageview | CHAR(6) NULL DEFAULT NULL | 1 |
| version_log_link_visit_action.interaction_position | SMALLINT UNSIGNED DEFAULT NULL | 1 |
| version_log_link_visit_action.product_price | DOUBLE NULL | 1 |
| version_log_link_visit_action.search_cat | VARCHAR(200) NULL | 1 |
| version_log_link_visit_action.search_count | INTEGER(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.server_time | DATETIME NOT NULL | 1 |
| version_log_link_visit_action.time_dom_completion | MEDIUMINT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.time_dom_processing | MEDIUMINT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.time_network | MEDIUMINT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.time_on_load | MEDIUMINT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.time_server | MEDIUMINT(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.time_spent_ref_action | INTEGER(10) UNSIGNED NULL | 1 |
| version_log_link_visit_action.time_transfer | MEDIUMINT(10) UNSIGNED NULL | 1 |
| version_log_visit.config_browser_engine | VARCHAR(10) NULL | 1 |
| version_log_visit.config_browser_name | VARCHAR(40) NULL1 | 1 |
| version_log_visit.config_browser_version | VARCHAR(20) NULL | 1 |
| version_log_visit.config_client_type | TINYINT( 1 ) NULL DEFAULT NULL1 | 1 |
| version_log_visit.config_cookie | TINYINT(1) NULL | 1 |
| version_log_visit.config_device_brand | VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL1 | 1 |
| version_log_visit.config_device_model | VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL1 | 1 |
| version_log_visit.config_device_type | TINYINT( 100 ) NULL DEFAULT NULL1 | 1 |
| version_log_visit.config_director | TINYINT(1) NULL | 1 |
| version_log_visit.config_flash | TINYINT(1) NULL | 1 |
| version_log_visit.config_gears | TINYINT(1) NULL | 1 |
| version_log_visit.config_java | TINYINT(1) NULL | 1 |
| version_log_visit.config_os | CHAR(3) NULL | 1 |
| version_log_visit.config_os_version | VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL | 1 |
| version_log_visit.config_pdf | TINYINT(1) NULL | 1 |
| version_log_visit.config_quicktime | TINYINT(1) NULL | 1 |
| version_log_visit.config_realplayer | TINYINT(1) NULL | 1 |
| version_log_visit.config_resolution | VARCHAR(18) NULL | 1 |
| version_log_visit.config_silverlight | TINYINT(1) NULL | 1 |
| version_log_visit.config_windowsmedia | TINYINT(1) NULL | 1 |
| version_log_visit.location_browser_lang | VARCHAR(20) NULL | 1 |
| version_log_visit.location_city | varchar(255) DEFAULT NULL1 | 1 |
| version_log_visit.location_country | CHAR(3) NULL1 | 1 |
| version_log_visit.location_latitude | decimal(9, 6) DEFAULT NULL1 | 1 |
| version_log_visit.location_longitude | decimal(9, 6) DEFAULT NULL1 | 1 |
| version_log_visit.location_region | char(3) DEFAULT NULL1 | 1 |
| version_log_visit.profilable | TINYINT(1) NULL | 1 |
| version_log_visit.referer_keyword | VARCHAR(255) NULL1 | 1 |
| version_log_visit.referer_name | VARCHAR(255) NULL1 | 1 |
| version_log_visit.referer_type | TINYINT(1) UNSIGNED NULL1 | 1 |
| version_log_visit.referer_url | VARCHAR(1500) NULL | 1 |
| version_log_visit.user_id | VARCHAR(200) NULL | 1 |
| version_log_visit.visitor_count_visits | INT(11) UNSIGNED NOT NULL DEFAULT 01 | 1 |
| version_log_visit.visitor_days_since_last | SMALLINT(5) UNSIGNED NULL | 1 |
| version_log_visit.visitor_localtime | TIME NULL | 1 |
| version_log_visit.visitor_returning | TINYINT(1) NULL1 | 1 |
| version_log_visit.visitor_seconds_since_first | INT(11) UNSIGNED NULL1 | 1 |
| version_log_visit.visitor_seconds_since_last | INT(11) UNSIGNED NULL | 1 |
| version_log_visit.visitor_seconds_since_order | INT(11) UNSIGNED NULL1 | 1 |
| version_log_visit.visit_entry_idaction_name | INTEGER(10) UNSIGNED NULL | 1 |
| version_log_visit.visit_entry_idaction_url | INTEGER(11) UNSIGNED NULL DEFAULT NULL | 1 |
| version_log_visit.visit_exit_idaction_name | INTEGER(10) UNSIGNED NULL | 1 |
| version_log_visit.visit_exit_idaction_url | INTEGER(10) UNSIGNED NULL DEFAULT 0 | 1 |
| version_log_visit.visit_first_action_time | DATETIME NOT NULL | 1 |
| version_log_visit.visit_goal_buyer | TINYINT(1) NULL | 1 |
| version_log_visit.visit_goal_converted | TINYINT(1) NULL | 1 |
| version_log_visit.visit_total_actions | INT(11) UNSIGNED NULL | 1 |
| version_log_visit.visit_total_events | INT(11) UNSIGNED NULL | 1 |
| version_log_visit.visit_total_interactions | MEDIUMINT UNSIGNED DEFAULT 0 | 1 |
| version_log_visit.visit_total_searches | SMALLINT(5) UNSIGNED NULL | 1 |
| version_log_visit.visit_total_time | INT(11) UNSIGNED NOT NULL | 1 |
+------------------------------------------------------------+------------------------------------------------------------------------------+----------+
81 rows in set (0.00 sec)

We also ran these commands to grant permission:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON matomo_qa.* TO 'matomo'@'localhost';
GRANT FILE ON . TO 'matomo'@'localhost';

@YamasakiRhys can you try to run this SQL:

DELETE FROM `matomo_option` WHERE name LIKE 'version\_log\_%';

? Then update Matomo again (via the update command or via the one click updater). Matomo will try to add the columns again. (Note: if you're doing this on the new install it should work unless the problems is not w/ the mysql user privileges. If you're doing this on the upgraded install, you may need to set version_core to the 3.x version to run the updates again with the required privileges (though if you are missing as many columns there that would also mean you lost a lot of data, and I'd recommend redoing the update w/ a backup).)

@diosmosis
That worked! Matomo updated the tables with the appropriate columns that were missing and everything is working now.
Thank you for your help with this! We couldn't figure out what was wrong so your help was great! Closing this thread now.