Database access denied or synax error after newest update

I got a mail “Neue Matomo-Version 3.13.0 ist verfügbar!” and started auto-update. Now I get for all widgets this error:

SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'abc'@'localhost' to database 'matomo' - in plugin Goals

Reason is maybe call index.php?module=CoreHome&action=index&idSite=1&period=range&date=last30 response with http-status 500

Is here a way to “repair” a installation or start update process again?

I have tried
# /opt/plesk/php/7.3/bin/php console core:update
but it said “Everything is already up to date.”

Hi,

This seems to be the same issue as reported here, but honestly I have no idea why. Maybe your MySQL error log has more information as to why MySQL rejects this query:

You could also try to enable query logging to find the exact SQL query that triggers the error:
https://matomo.org/faq/troubleshooting/faq_115/

Hi Lucas,

as I finde out while covering database to new version or in archive.php the correctly server address is Not taken. -‘dbxxxx’@‘%’- right should be

  • dbxxxx’@‘localhost’-

I downgraded to 3.11 and all is working fine!

So there should be a bug taking the right database host name……

cheers

Ralf

that’s very strange! If you can create a github issue at: http://www.github.com/matomo-org/matomo/issues we can see if other people experience this.

For future reference:

I have manual take a update and copied/replaced all files, except config-file and repaired all tables in database. Error still exists.
I can login, I can change matomo settings and save them, remove and add widgets, so I can access successful the database, but widgets and stats are broken.

Damn, I have the same issue then… my console:archive call stops with:
INFO [2019-11-30 21:18:19] 1312 Error: Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=day&date=last2&format=php&trigger=archivephp. Response was ‘a:2:{s:6:“result”;s:5:“error”;s:7:“message”;s:241:"SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user ‘user’@’%’ to database ‘blabla’, caused by: SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user ‘user’@’%’ to database ‘blabla’";}’

Any updates on this?

Does the error message really says @’%’ or is % replaced by your hostname?
it should say your hostname here…

In my case it was “localhost”.

Could you try replace localhost by 127.0.0.1 in your config/config.ini.php under [database] section?

I get with 127.0.0.1

An error occurred
Cannot connect to the database:
SQLSTATE[HY000] [2002] Connection refused

change back to localhost I can login and see error message in widgets again.

That’s strange, would you be able to share with us the URL, username, password to your Matomo, by email at hello@matomo.org ? Thanks

It is % here which is fine as for mySQL the user is accepted from any host. I have dozens of PERL scripts running like this on this machine with users configured like that.

In the config.ini.php the database is mentioned with 127.0.0.1 since the beginning and a different port than 3306. But as all the rest is working just only the archiving not i suspect that something must be wrong in the script doing that and not in my config.

One thing that changed in 3.12.0 was that now the mysql user requires the CREATE TEMPORARY TABLES permission. Can you check that your matomo db user has this permission?

1 Like

And that is the solution for me. Thank you very much! :wink:

1 Like

For me too… I might suggest to add a proper error message to the script because creating temp tables is not a default right and I can imagine that a lot with restrictive rights will stumble over this issue not knowing what to do.

1 Like

Thanks for the updates, great to hear.

Created an issue to improve this in Matomo: When DB user doesn't have the 'CREATE TEMPORARY TABLE' grant, display a clear error message · Issue #15237 · matomo-org/matomo · GitHub

for those having the issue, see the command to grant the user the correct DB permission in this FAQ: How do I create a new database and database user in MySQL? FAQ - Analytics Platform - Matomo