Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

After fresh installation of 4.2.1 some columns like "visit_first_action_time" are missing #17420

Closed
mwalliczek opened this issue Apr 3, 2021 · 5 comments
Closed

After fresh installation of 4.2.1 some columns like "visit_first_action_time" are missing #17420

mwalliczek opened this issue Apr 3, 2021 · 5 comments
Labels
answered For when a question was asked and we referred to forum or answered it. Likely a server config issue Indicates that the issue is not in Matomo and a fix likely requires a server configuration change.

Comments

@mwalliczek
Copy link

I just installed matomo 4.2.1 at my server, connected to an empty database, added some sites to be analyzed - but when I try to import some data from the logfiles, I get an Error 500 from the server and the logfile says "PHP message: Error in Matomo (tracker): Error query: preparing query failed: Unknown column 'visit_first_action_time'"

Expected Behavior

The data is imported correctly.

Current Behavior

Command:

python /home/matomo/misc/log-analytics/import_logs.py --enable-reverse-dns --enable-http-errors --enable-http-redirects --enable-bots --url=https://example.com/matomo --idsite=1 /var/log/nginx/access.log-20210328
0 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
Parsing log /var/log/nginx/access.log-20210328...
2021-04-03 10:07:02,054: [INFO] Error when connecting to Matomo: HTTP Error 500: Internal Server Error

logfile:

2021/04/03 09:53:57 [warn] 5709#0: *5422011 a client request body is buffered to a temporary file /var/lib/nginx/tmp/client/0000000712, client: 1.2.3.4, server: example.com, request: "POST /matomo/piwik.php HTTP/1.1", host: "example.com"
2021/04/03 09:53:57 [error] 5709#0: *5422011 FastCGI sent in stderr: "PHP message: Error in Matomo (tracker): Error query: preparing query failed: Unknown column 'visit_first_action_time' in 'field list' : 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  FROM matomo_log_visit WHERE visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite = ? AND config_id = ?                 ORDER BY visit_last_action_time" while reading response header from upstream, client: 1.2.3.4, server: example.com, request: "POST /matomo/piwik.php HTTP/1.1", upstream: "fastcgi://unix:/usr/lib64/php7.4/fpm/matomo/socket:", host: "example.com"

Possible Solution

I tryed adding the column, but than I get an error about a different missing column (user_id).

Your Environment

Mandatory checks

PHP-Version >= 7.2.5: ✔ 7.4.15

PDO Erweiterung: ✔

PDO\MYSQL Erweiterung: ✔

MYSQLI Erweiterung: ✔

Weitere erforderliche Anforderungen: ✔ zlib ✔ SPL ✔ iconv ✔ json ✔ mbstring ✔ Reflection

Erforderliche Funktionen: ✔ debug_backtrace ✔ eval ✔ hash ✔ gzcompress ✔ gzuncompress ✔ pack

Benötigte PHP Konfiguration (php.ini): ✔ session.auto_start = 0 ✔ max_execution_time = 0 OR >= 30

Verzeichnisse mit Schreibzugriff: ✔ $DOC_ROOT/tmp ✔ $DOC_ROOT/tmp/assets ✔ $DOC_ROOT/tmp/cache ✔ $DOC_ROOT/tmp/climulti ✔ $DOC_ROOT/tmp/latest ✔ $DOC_ROOT/tmp/logs ✔ $DOC_ROOT/tmp/sessions ✔ $DOC_ROOT/tmp/tcpdf ✔ $DOC_ROOT/tmp/templates_c

Optional checks

Dateiintegrität: ✔

Tracker-Status: ✔

Speicherlimit: ✔ 512M

Zeitzone: ✔

Öffnen einer URL: ✔ curl

PageSpeed deaktiviert: ✔

GD > 2.x + Freetype (graphics): ✔

Andere Erweiterungen: ✔ json ✔ libxml ✔ dom ✔ SimpleXML

Andere Funktionen: ✔ shell_exec ✔ set_time_limit ✔ mail ✔ parse_ini_file ✔ glob ✔ gzopen ✔ md5_file

Dateisystem: ✔

Cron einrichten (schneller ladende Berichte): ⚠ Warning: Für eine optimale Leistung und ein schnelles Matomo wird dringend empfohlen, einen Crontab einzurichten, um Ihre Berichte automatisch zu archivieren und das Auslösen durch den Browser in den Matomo-Einstellungen zu deaktivieren. Erfahren Sie mehr.

Cron einrichten - Prozesse via CLI steuern: nicht unterstützt (optional)

Datenbankfähigkeiten: ✔ UTF8mb4 charset ⚠ Warning: LOAD DATA INFILE
Die Benutzung von LOAD DATA INFILE erhöht die Geschwindigkeit des Matomo Archivierungsprozesses erheblich. Um dies für Matomo verfügbar zu machen, setzen Sie bitte eine neuere PHP & MySQL Software ein und stellen Sie sicher, dass der gewählte Datenbankbenutzer das FILE Recht besitzt.
Falls Ihr Matomo Server Websites mit erhöhtem Verkehrsaufkommen (z.B.: > 100 000 Seiten pro Monat) verarbeiten muss, empfehlen wir dieses Problem zu beheben. ✔ CREATE TEMPORARY TABLES ✔ Changing transaction isolation level

Maximale Packetgröße: ✔

Erzwungene SSL Verbindung: ⚠ Warning: Wir empfehlen, Matomo ausschliesslich über sichere SSL Verbindungen zu verwenden. Um unsicheren Zugriff über http zu verhindern, fügen Sie force_ssl = 1 dem Bereich General in Ihrer Matomo config/config.ini.php Datei hinzu.

Standorterkennung: ⚠ Warning: Die voreingestellte Standorterkennung versucht das Herkunftsland des Besuchers anhand dessen verwendeter Sprache zu erkennen. Dies ist nicht sehr genau, daher wird empfohlen Geolocation-Datenbank zu installieren und zu nutzen.

Update über HTTPS: ✔

Schreibbarer JavaScript-Tracker ("/matomo.js"): ✔

Informational results

Matomo Version: 4.2.1

Matomo Update History: 4.2.1,

Matomo Install Version: 4.2.1

Latest Available Version: 4.2.1

Is Git Deployment: 0

PHP_OS: Linux

PHP_BINARY: /usr/lib64/php7.4/bin/php-fpm

PHP SAPI: fpm-fcgi

Timezone Version: 2020.4

PHP Timezone: UTC

PHP Time: 1617437516

PHP Datetime: 2021-04-03 08:11:56

PHP INI max_execution_time: 30

PHP INI post_max_size: 8M

PHP INI max_input_vars: 1000

PHP INI zlib.output_compression:

Curl Version: 7.74.0, OpenSSL/1.1.1j

Suhosin Installed: 0

DB Prefix: matomo_

DB Charset: utf8mb4

DB Adapter: MYSQLI

MySQL Version: 10.4.17-MariaDB-log

Num Tables: 31

Browser Segment Archiving Enabled: 1

Development Mode Enabled: 0

Internet Enabled: 1

Multi Server Environment: 0

Auto Update Enabled: 1

Custom User Path: 0

Custom Include Path: 0

Release Channel: latest_stable

Plugins Activated: API, Actions, Annotations, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CustomDimensions, CustomJsTracker, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, GeoIp2, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, Login, Marketplace, MobileMessaging, Monolog, Morpheus, MultiSites, Overlay, PagePerformance, PrivacyManager, ProfessionalServices, Proxy, Referrers, Resolution, RssWidget, SEO, ScheduledReports, SegmentEditor, SitesManager, Tour, Transitions, TwoFactorAuth, UserCountry, UserCountryMap, UserId, UserLanguage, UsersManager, VisitFrequency, VisitTime, VisitorInterest, VisitsSummary, WebsiteMeasurable, Widgetize

Plugins Deactivated: CustomVariables, DBStats, MobileAppMeasurable, Provider, TagManager

Plugins Invalid:

Server Info: nginx/1.18.0

Had visits in last 1 day: 0

Had visits in last 3 days: 0

Had visits in last 5 days: 0

Archive Time Last Started: 0

Archive Time Last Finished: 0

Num invalidations: 0 queued, 0 in progress

User Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 Safari/537.36

Browser Language: de-de,de,en-us,en

Anonymize Referrer:

Do Not Track enabled: 1
@mwalliczek mwalliczek added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Apr 3, 2021
@Findus23 Findus23 mentioned this issue Apr 3, 2021
Merged
10 tasks
@diosmosis
Copy link
Member

Hi @mwalliczek, sorry you're experiencing this issue, it seems like matomo or php isn't able to create columns itself. To try and recreate the columns yourself through matomo, you could run the following SQL:

delete from matomo_option where option_name like 'version\_log\_%';

Then visit Matomo and the auto updater will try to recreate the columns. If this doesn't work, then php or matomo isn't able to create columns for some reason (and errors are maybe silenced).

@mwalliczek
Copy link
Author

mwalliczek commented Apr 7, 2021 •
edited

Hi @diosmosis, thanks for your help. You are right: I forgot to add "Alter_priv" to the matomo mysql user. Unfortunately there was no error displayed or logged.
So I run this SQL script, Matomo started the auto update - but there is still an error when trying to import:
2021/04/07 07:32:00 [error] 5709#0: *5578453 FastCGI sent in stderr: "PHP message: Error in Matomo (tracker): Error query: preparing query failed: Unknown column 'last_idlink_va' in 'field list' : 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 FROM matomo_log_visit WHERE visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite = ? AND config_id = ? ORDER BY visit_last_action_time DESC"

@diosmosis
Copy link
Member

@mwalliczek that column comes from a plugin. It might be easier to just do a clean install of matomo w/ the mysql user that has the correct privileges. Otherwise something like DELETE FROM matomo_option WHERE option_name LIKE 'version_' AND name <> 'version_core'; might do the trick.

@diosmosis diosmosis added Likely a server config issue Indicates that the issue is not in Matomo and a fix likely requires a server configuration change. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Apr 7, 2021
@diosmosis diosmosis mentioned this issue Apr 7, 2021
Open
@mwalliczek
Copy link
Author

Ok, after clean installation everything is working fine. Thanks for your help.

@diosmosis
Copy link
Member

Glad it's working for you, I'll close this.

@sgiehl sgiehl added the answered For when a question was asked and we referred to forum or answered it. label Apr 28, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
answered For when a question was asked and we referred to forum or answered it. Likely a server config issue Indicates that the issue is not in Matomo and a fix likely requires a server configuration change.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@diosmosis @sgiehl @mwalliczek