Upgraded from 3.x to 4.2.1: Not receiving data due to SQL error

[YamasakiRhys]

Having an issue after upgrading from 3.x that is using a query to grab columns that are not existent with 4.x db.
Receiving an error with a query with a select statement in the db causing a sql error.

Expected Behavior

Should query expected columns within the table.

Current Behavior

Getting a error with select query:
ERROR Piwik\Tracker\Handler[2021-04-07 18:16:19 UTC] [e58b4] Exception: /opt/rh/httpd24/root/var/www/html/matomo/core/Tracker/Db/Pdo/Mysql.php(238): Error query: SQLSTATE[42S22]: Column not found: 1054 Unknown error 1054 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 FROM matomo_log_visit FORCE INDEX (index_idsite_idvisitor) WHERE idsite = ? AND visit_last_action_time <= ? AND idvisitor = ?
ERROR Piwik\Tracker\Handler[2021-04-07 18:16:19 UTC] [e58b4] ORDER BY visit_last_action_time DESC
ERROR Piwik\Tracker\Handler[2021-04-07 18:16:19 UTC] [e58b4] LIMIT 1 Parameters: array (
ERROR Piwik\Tracker\Handler[2021-04-07 18:16:19 UTC] [e58b4] 0 => 1,
ERROR Piwik\Tracker\Handler[2021-04-07 18:16:19 UTC] [e58b4] 1 => '2021-04-07 18:46:19',
ERROR Piwik\Tracker\Handler[2021-04-07 18:16:19 UTC] [e58b4] 2 => '�ÍR>‡
•£',
ERROR Piwik\Tracker\Handler[2021-04-07 18:16:19 UTC] [e58b4] )

When using the tracker on our site, matomo.php is failing and we're receiving this error in the logs.

ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #0 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker/Db/Pdo/Mysql.php(197): Piwik\Tracker\Db\Pdo\Mysql->query('SELECT visit_la...', Array)
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #1 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker/Model.php(458): Piwik\Tracker\Db\Pdo\Mysql->fetch('SELECT visit_la...', Array)
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #2 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker/Model.php(440): Piwik\Tracker\Model->fetchVisitor('SELECT visit_la...', 'FROM dbuser_log...', 'idsite = ? AND ...', Array)
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #3 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker/Model.php(401): Piwik\Tracker\Model->findVisitorByVisitorId('\x0F\xCDR>\x87\r\x95\xA3', 'SELECT visit_la...', 'FROM dbuser_log...', 'idsite = ? AND ...', Array)
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #4 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker/VisitorRecognizer.php(109): Piwik\Tracker\Model->findVisitor(1, ':\xA4mQ\x1F\x8B\xFD', '\x0F\xCDR>\x87\r\x95\xA3', false, Array, false, true, '2021-04-07 17:4...', '2021-04-07 18:4...')
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #5 /opt/rh/httpd24/root/var/www/html/dbuser/plugins/CoreHome/Tracker/VisitRequestProcessor.php(118): Piwik\Tracker\VisitorRecognizer->findKnownVisitor(':\xA4mQ\x1F\x8B\xFD', Object(Piwik\Tracker\Visit\VisitProperties), Object(Piwik\Tracker\Request))
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #6 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker/Visit.php(163): Piwik\Plugins\CoreHome\Tracker\VisitRequestProcessor->processRequestParams(Object(Piwik\Tracker\Visit\VisitProperties), Object(Piwik\Tracker\Request))
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #7 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker.php(160): Piwik\Tracker\Visit->handle()
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #8 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker/Handler.php(55): Piwik\Tracker->trackRequest(Object(Piwik\Tracker\Request))
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #9 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker.php(140): Piwik\Tracker\Handler->process(Object(Piwik\Tracker), Object(Piwik\Tracker\RequestSet))
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #10 /opt/rh/httpd24/root/var/www/html/dbuser/core/Tracker.php(115): Piwik\Tracker->track(Object(Piwik\Tracker\Handler), Object(Piwik\Tracker\RequestSet))
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #11 /opt/rh/httpd24/root/var/www/html/dbuser/piwik.php(73): Piwik\Tracker->main(Object(Piwik\Tracker\Handler), Object(Piwik\Tracker\RequestSet))
ERROR Piwik\Tracker\Handler[2021-04-07 18:17:15 UTC] [172f7] #12 /opt/rh/httpd24/root/var/www/html/dbuser/dbuser.php(13): include('/opt/rh/httpd24...')

This isn't allowing us to receive any data since this is stopping the process.

Originally we upgraded and used a db instance with that upgrade, but were receiving this error.
Current DB is a new instance when we tried to do a fresh install of 4.2.1

Your Environment

• Matomo Version: 4.2.1
• PHP Version: 7.3.20
• Server Operating System: Linux
• Additionally installed plugins:
  API, Actions, Annotations, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CustomDimensions, CustomJsTracker, CustomVariables, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, Login, Monolog, Morpheus, MultiSites, Overlay, PagePerformance, PrivacyManager, ProfessionalServices, Proxy, Referrers, Resolution, RssWidget, SEO, ScheduledReports, SegmentEditor, SitesManager, TagManager, Tour, Transitions, TwoFactorAuth, UserCountry, UserCountryMap, UserId, UserLanguage, UsersManager, VisitFrequency, VisitTime, VisitorInterest, VisitsSummary, WebsiteMeasurable, Widgetize,

Installed but deactivated:
DBStats, GeoIp2, Marketplace, MobileAppMeasurable, MobileMessaging, Provider

[diosmosis]

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?

[YamasakiRhys]

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

[diosmosis]

@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_%';

?

[YamasakiRhys]

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';

[diosmosis]

@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).)

[YamasakiRhys]

@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.