Changed remote MySQL db - log in issues

Support & Bugs
1

I recently changed web host where my remote SQL db is located for Matomo (from Hostgator to Hostinger). The Matomo installation has remained in the same place, in ‘stats’ subdirectory, in a subfolder, on my domain. I exported the contents of the db before shutting down my Hostgator account, created a new db at Hostinger, and imported the data, set up the relevant user and password and access levels, and edited the config.ini.php file on my Matomo installation to reflect the new db location and log in details, but the error message I get when starting Matomo index.php file is:

An error occurred. Cannot connect to the database:
SQLSTATE[28000] [1045] Access denied for user ‘u99999999_user’@‘81.x.x.x’ (using password: YES)

I have set up the remote db to accept logins from any host, to eliminate that possible error as the IP address in the Matomo log in error page had the last block of numbers changing every time I tried. I have changed the db password several times to varying lengths etc. (as Hostinger had some issues with this elsewhere with no forewarning!) and mirroring this in the config.ini.php file. Hostinger have advised me that the defaul port for accessing their SQL server is 3306, which as I understand is the default port used by Matomo. I tried including the port number in the config.ini.php file but it didn’t make any difference.

The start of the config.ini.php file with a few security edits is this:

; <?php exit; ?> DO NOT REMOVE THIS LINE
; file automatically generated or modified by Matomo; you can manually override the default values in global.ini.php by redefining them in this file.
[database]
host = "185.28.20.x"
username = "99999999_user"
password = "password"
dbname = "88888888_domain"
tables_prefix = "piwik_"

[General]
proxy_client_headers[] = "HTTP_X_FORWARDED_FOR"
proxy_host_headers[] = "HTTP_X_FORWARDED_HOST"
salt = "edit123blablabla"
trusted_hosts[] = "stats.domain.co.uk"
trusted_hosts[] = "www.domain.co.uk"

Is there any point trying to completely reinstall Matomo and letting the app web interface set up the MySQL db connection rather than just doing it from the config file? Any ideas where I might potentially be going wrong please? TIA.

2

Hi,

I unfortunately don’t have time to look into this closer, but this might help:

3

Hi Lukas, thanks for the help.

I tried setting:

enable_segments_cache = 0

I also tried editing the user permissions and everything was enabled for that user, including the create temporary tables option. I tried repairing the database. Didn’t work. I tried creating a new empty database on the same server and edited the log in details in the matomo config file to see if would at least connected but it didn’t work either, I was not able to log in. I tried created another database in another Hostinger account and importing my old matomo data and then editing the matomo config file to connect to that database instead, but still no luck. Remote access is enabled to all incoming hosts for all the databases I created. Next stop I will try a completely new install and then set up the database access details that way.

4

Reinstalling Matomo, on the database set up page, entering the database details inc. the former ‘piwik_’ tables_prefix not the suggested ‘matomo_’, and clicking submit, I get the error message:

‘Error while trying to connect to the database server: SQLSTATE[28000] [1045] Access denied for user ‘u25044xxxx_dbusername’@’[my IP address]’ (using password: YES)’

I also tried setting up Matomo as PDO and MySQLi but got the same error with both.