WP DB Error

[pwaschk]

We are running a Wordpress Blog (always the latest version, now on 5.2.4–de_DE) on PHP 7.3 and MySQL 5.5.62. I installed Matomo for Wordpress 2.0 last week, updated to 2.4 today. The installation seemed to work fine, the tables were created and filled in the database during the installation. There are some visits saved in table matomo_log_visit with a timestamp from last week, no visits from today though.

When I try to look at the statistics in the backend, I get an error: WP DB Error: Access denied for user 'DB-USER'@'%' to database 'DB-NAME' This happened the first time I opened the backend after the installation and happens still after the update. The error is shown on the Dashboard and the General Overview. Both our Wordpress installation and the Matomo plugin are set to German.

Can you please give me some advise on how I could narrow down the problem? Since I'm completely new to Matomo I'm not sure which files to check or which content to expect in the database.

[tsteur]

@pwaschk Did you replace DB-USER and DB-NAME with the actual username/password in your issue description or was it actually using those words? How are you configuring your database in WordPress? Anything special about your database? Are you using any custom plugins for this?

Any chance you could post the output of the system report which you can copy/paste in the beginning of the "Matomo => System Report" page?

[pwaschk]

Thanks @tsteur !

Yes, the error message shows the actual and correct user name and database. The access data for the database was submitted during the Wordpress installation process and are saved in the config file of the Wordpress installation. Wordpress and other plugins show no issues with accessing the database.

The system report is as follows:

# Matomo

* Matomo Plugin Version: 0.2.4
* Config exists and is writable.: Ja ("$ABSPATH/wp-content/uploads/matomo/config/config.ini.php")
* JS Tracker exists and is writable.: Ja ("$ABSPATH/wp-content/uploads/matomo/matomo.js")
* Plugin directories: Yes ([{"pluginsPathAbsolute":"$ABSPATH\/wp-content\/plugins\/matomo\/plugins","webrootDirRelativeToMatomo":"..\/"}])
* Tmp directory writable: Yes ($ABSPATH/wp-content/uploads/matomo/tmp)
* Matomo Version: 3.12.0
* Matomo Blog idSite: 1

## Endpoints

* Matomo JavaScript Tracker URL:  ($site_url/wp-content/uploads/matomo/matomo.js)
* Matomo JavaScript Tracker - WP Rest API:  ($site_url/index.php/wp-json/matomo/v1/hit/)
* Matomo HTTP Tracking API:  ($site_url/wp-content/plugins/matomo/app/matomo.php)
* Matomo HTTP Tracking API - WP Rest API:  ($site_url/index.php/wp-json/matomo/v1/hit/)

## Crons

* Server time: 2019-11-12 07:08:59
* Blog time: 2019-11-12 08:08:59 (Below dates are shown in blog timezone)
* Sync users & sites: Next run: 2019-11-12 08:57:40 (48 Minuten 41s) ( Last started: 2019-11-11 08:58:44 (-23 Stunden 10 Minuten). Last ended: 2019-11-11 08:58:45 (-23 Stunden 10 Minuten). Interval: daily)
* Archive: Next run: 2019-11-12 08:57:40 (48 Minuten 41s) ( Last started: 2019-11-12 08:07:49 (-1 Minuten 10s). Last ended: Unknown. Interval: hourly)
* Update GeoIP DB: Next run: 2019-11-18 08:57:40 (6 Tage 0 Stunden) ( Last started: 2019-11-11 09:00:03 (-23 Stunden 8 Minuten). Last ended: 2019-11-11 09:00:06 (-23 Stunden 8 Minuten). Interval: matomo_weekly)

## Mandatory checks

* PHP-Version >= : ok 
* PDO Erweiterung: ok 
* PDO\MYSQL Erweiterung: ok 
* MYSQLI Erweiterung: ok 
* Weitere erforderliche Anforderungen: ok 
* Erforderliche Funktionen: ok 
* Benötigte PHP Konfiguration (php.ini): ok 
* Verzeichnisse mit Schreibzugriff: ok 
* Verzeichnisse mit Schreibzugriff auf Tag Manager: ok 

## Optional checks

* Tracker-Status: ok 
* Speicherlimit: ok 
* Zeitzone: ok 
* Öffnen einer URL: ok 
* PageSpeed deaktiviert: ok 
* GD > 2.x + Freetype (graphics): ok 
* Andere Erweiterungen: ok 
* Warning Andere Funktionen: warning  (shell_exec<br/>Sie müssen diese eingebaute Funktion aktivierenset_time_limitmailparse_ini_fileglobgzopenmd5_file)
* Dateisystem: ok 
* Archiv Cron: ok 
* Error Letzter erfolgreicher Abschluss der Archivierung: error  (Die Archivierung wurde noch nicht erfolgreich abgeschlossen.<br/><br/>Bitte überprüfen Sie, ob Sie einen Crontab eingerichtet haben, der den <code>core:archive</code> Konsolenbefehl aufruft, und ob Sie einen <code>MAILTO</code> konfiguriert haben, um Fehler per E-Mail zu erhalten, wenn die Archivierung fehlschlägt. Sie können auch versuchen, den Konsolenbefehl auszuführen, um Ihre Berichte manuell zu archivieren: <code>$ABSPATH/wp-content/plugins/matomo/app --matomo-domain=groups.uni-paderborn.de core:archive</code>. <a href="https://matomo.org/docs/setup-auto-archiving/" target="_blank" rel="noreferrer noopener"> Mehr erfahren.</a>)
* Warning Maximale Packetgröße: warning  (Es wird empfohlen die 'max_allowed_packet' Größe in Ihrer MySQL Datenbank auf mindestens 64MB zu erhöhen. Aktuell ist 16MB eingestellt.)
* Standorterkennung: ok 
* Update über HTTPS: ok 
* Warning Schreibbarer JavaScript-Tracker ("/matomo.js"): warning  (Für die Matomo JavaScript-Tracker-Datei "/matomo.js" sind keine Schreibrechte vorhanden , das bedeutet, dass andere Plugins den JavaScript-Tracker nicht erweitern können. In Zukunft könnten sogar einige Kernfunktionen nicht wie erwartet funktionieren.Wir empfehlen, "/matomo.js"beschreibbar zu machen, indem Sie folgenden Befehl ausführen: <br/><code> chmod +w $ABSPATH/wp-content/plugins/matomo/app/matomo.js<br/> chown pwaschk:pwaschk $ABSPATH/wp-content/plugins/matomo/app/matomo.js</code><br />)
* Supports Async Archiving: No

## Matomo Settings

* Track mode: manually
* Track codeposition: footer
* Track api endpoint: default
* Track js endpoint: default
* Last settings update: 1573459125
* Last tracking settings update: 1573459125
* Track search: 1
* Track 404: 1

# WordPress

* Home URL: $site_url
* Site URL: $site_url
* WordPress Version: 5.2.4
* Number of blogs: 1
* Multisite Enabled: No
* Network Enabled: No
* Debug Mode Enabled: No
* Cron Enabled: No
* DB Prefix: ffs_

# WordPress Plugins

* Caldera Forms: 1.8.7
* Content Views: 2.2.0
* Enable Media Replace: 3.3.6
* Google XML Sitemaps: 4.1.0
* iThemes Security: 7.4.1 (Network enabled)
* List category posts: 0.81
* Matomo Analytics & Tag Manager: 0.2.4
* Newsletter: 6.3.7
* Post Grid by PickPlugins: 2.0.41
* Shariff for Wordpress: 1.0.11
* Widget Context: 1.2.0
* WP Maintenance Mode: 2.2.4
* WP Statistics: 12.6.10
* Active Plugins: 12 (better-wp-security/better-wp-security.php, caldera-forms/caldera-core.php, enable-media-replace/enable-media-replace.php, google-sitemap-generator/sitemap.php, list-category-posts/list-category-posts.php, matomo/matomo.php, newsletter/plugin.php, post-grid/post-grid.php, shariff-sharing/shariff-wp.php, widget-context/widget-context.php, wp-maintenance-mode/wp-maintenance-mode.php, wp-statistics/wp-statistics.php)

# Server

* Server Info: Apache
* PHP Version: 7.3.11-1~deb10u1
* MySQL Version: 5.5.62
* Timezone: UTC
* Locale: de_DE
* Memory Limit: 128M (At least 128MB recommended. Depending on your traffic 256MB or more may be needed.)
* Time: 1573542539
* Mysqli Connect: Yes
* Force MySQL over Mysqli: No
* Max Execution Time: 30
* Max Post Size: 100M
* Max Upload Size: 104857600
* Max Input Vars: 1000
* Curl Version: 7.64.0, OpenSSL/1.1.1d

[tsteur]

@pwaschk I'm having bit of a hard time figuring out what that potentially could be. Are you getting this error every time you're wanting to access the statistics in the backend? With backend I suppose you mean when you click on "Matomo Analytics => Reporting" link? Does it work when you go to "Matomo => Summary"? What happens when you click in "System Report => Troubleshooting" on "Archive"?

Sorry about all these questions. Really wanting to get this issue fixed. The system report looks all good. Any chance we can maybe get access to your server to investigate that issue if needed?

[pwaschk]

@pwaschk I'm having bit of a hard time figuring out what that potentially could be. Are you getting this error every time you're wanting to access the statistics in the backend? With backend I suppose you mean when you click on "Matomo Analytics => Reporting" link?

Yes, every time I follow the Reporting link on the Dashboard and Besucher => Übersicht (Visitor Overview).

Does it work when you go to "Matomo => Summary"?

The Summary shows "Es stehen keine Daten für diesen Bericht zur Verfügung." (no data available for the report) in each section.

What happens when you click in "System Report => Troubleshooting" on "Archive"?

The button 'Archive reports'? It mentions an e-mail. I got the mail on Monday but didn't pay much attention to it. My mistake.

Quote from the mail:

Ein Fehler vom Typ E_ERROR wurde in der Zeile 378 der Datei /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/plugins/API/ProcessedReport.php verursacht. Fehlermeldung: Uncaught Exception: API returned an error: WP DB Error: Access denied for user 'zsb-fit-fuers-st'@'%' to database 'zsb-fit-fuers-studium' at Wordpress.php:275  in /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/plugins/API/ProcessedReport.php:378
Stack trace:
#0 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/plugins/API/API.php(348): Piwik\Plugins\API\ProcessedReport->getProcessedReport('1', 'day', 'yesterday', 'Goals', 'get', false, Array, false, false, true, false, false, false, 'bc', false)
#1 [internal function]: Piwik\Plugins\API\API->getProcessedReport('1', 'day', 'yesterday', 'Goals', 'get', false, false, false, false, true, false, false, false, 'bc', false)
#2 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/API/Proxy.php(237): call_user_func_array(Array, Array)
#3 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/Context.php(28): Piwik\API\Proxy->Piwik\API\{closure}()
#4 /upb/gr

Sorry about all these questions. Really wanting to get this issue fixed. The system report looks all good.

I'm really glad you ask! Thank you!

Any chance we can maybe get access to your server to investigate that issue if needed?

I'm afraid that's not possible. The access to the webspace is merged with our employee login. Also we don't have root access and can only install php scripts but not change server setting etc. We have access to the database via phpMyAdmin though.

[tsteur]

@pwaschk do you know if the DB user has access to all the tables? Eg the tables starting with wp_matomo_* if your DB prefix is wp_? Any chance you can check?

[tsteur]

For example using the same DB user in phpMyAdmin can you access/read from these tables?

[pwaschk]

I can access these tables. Some of them contain values. matomo_archive_numeric_2019_11 e.g. has apparently been updated today. The last entry in matomo_log_visit on the other hand is from 2019-11-08.

Btw. I did the update to version 0.2.5 just now. Didn't change anything though.

[tsteur]

Any chance you could check what the value is for DB_HOST in your wp-config.php? You don't have to show the actual value but can replace any IP or host with example value. Just curious about the format to see if there's anything special. Cheers

[tsteur]

Also could you check if you have any information stored in ffs_matomo_log_visit?

[tsteur]

In https://github.com/matomo-org/wp-matomo/issues/78#issuecomment-554256203 you mention a stacktrace with 4 items. Any chance to get the full stack trace?

[tsteur]

Sorry about all the comments. It shouldn't be related to the DB host as WP is actually executing the query so it's bit funny it's not working. Released a new version where I've added bit more information when this error happens (the actual query that is being executed) be good if you could try to go to "System Report => Troubleshooting =>Archive" or if / when you get an email send us more information about the error.

If possible, be still good if you could check if you have any information stored in ffs_matomo_log_visit?

And there's now also a Database section section in the system report (at the end of the page). If you could copy/paste just that information that be great.

[tsteur]

@pwaschk have you maybe had a chance to look recently? Be great if you could let us know.

[pwaschk]

I'm sorry, I couldn't check earlier.

System Report in the Database section:

MySQL Version	5.5.62
Mysqli Connect	Yes
Force MySQL over Mysqli	No
DB Prefix	ffs_
Uses Socket	No
Uses IPv6	No
Required permissions	Error	Missing permissions: CREATE TEMPORARY TABLES, LOCK TABLES. Please check if any of these MySQL permission (grants) are missing and add them if needed. Learn more: https://matomo.org/faq/how-to-install/faq_23484/

While I tried to figure out how to get the stack trace I enabled WP_DEBUG in the wp-config.php. As a result, I can now see the SQL statement causing trouble. CREATE TEMPORARY TABLE ffs_matomo_logtmpsegmente9472018cd222686d055322416a18fc1 (idvisit BIGINT(10) UNSIGNED NOT NULL) And indeed, when I execute the very same statement via PHPMyAdmin I get the same error message. CREATE TABLE on the other hand works. Apparently, we don't have a CREATE TEMPORARY TABLES privilege.

Still no new entries in ffs_matomo_log_visit.

I guess I'll contact the server admin now and ask for CREATE TEMPORARY TABLES permission?

The latest e-mail triggered by visiting "System Report => Troubleshooting =>Archive" returns the following error messages:

Ein Fehler vom Typ E_ERROR wurde in der Zeile 589 der Datei /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/CronArchive.php verursacht. Fehlermeldung: Uncaught Exception: 2 total errors during this script execution, please investigate and try and fix these errors. in /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/CronArchive.php:589
Stack trace:
#0 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/CronArchive.php(582): Piwik\CronArchive->logFatalError('2 total errors ...')
#1 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/CronArchive.php(326): Piwik\CronArchive->end()
#2 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/Access.php(639): Piwik\CronArchive->Piwik\{closure}()
#3 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/app/core/CronArchive.php(327): Piwik\Access::doAsSuperUser(Object(Closure))
#4 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/plugins/matomo/classes/WpMatomo/ScheduledTasks.php(217): Piwik\CronArchive->main()
#5 /upb/groups/zsb-fit-fuers-studium/public/https/wp-content/pl####

We don't have the permission to create cronjobs on our own but would have to ask the server admins for specific cronjobs. I hope this doesn't affect the functionality of the plugin. Unfortunately, we are working on a university's server, which is the reason for the many restrictions.

[tsteur]

I guess I'll contact the server admin now and ask for CREATE TEMPORARY TABLES permission?

Yes it be great to ask them to add this permission. In the system report output it also looks like it would be good to add "LOCK TABLES" permission.

You shouldn't need to set up a cronjob as we're doing this automatically through the WP cron and in this case things should work nicely once the permission has been added.

Thanks for troubleshooting and glad we could find it. I've added checks for this into the system report so we can find out such issues more easily in the future.