@mwalliczek opened this Issue on April 3rd 2021

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<a href='/0'>#0</a>: *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<a href='/0'>#0</a>: *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

@diosmosis commented on April 6th 2021 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 commented on April 7th 2021

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<a href='/0'>#0</a>: *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 commented on April 7th 2021 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.

@mwalliczek commented on April 8th 2021

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

@diosmosis commented on April 8th 2021 Member

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

This Issue was closed on April 8th 2021
Powered by GitHub Issue Mirror