Problems with external Database

Discussion forum about Anuko Time Tracker
Post Reply
monadnock
Posts: 5
Joined: Thu Mar 18, 2021 6:35 pm

Problems with external Database

Post by monadnock » Thu Mar 18, 2021 6:42 pm

I have install Timetracker on a VM guest and would like to connect to an external MySQL database living on my host. I have setup the database and user on the host per the installation instructions, and entered the DSN as follows

Code: Select all

define('DSN', 'mysql://ttuser:PASSWORD@IP_ADDRESS/timetracker?charset=utf8mb4');
I am getting the

Code: Select all

Error: connection to database failed. MDB2 Error: connect failed
when I visit /dbinstall.php
I have tested the remote database connection using

Code: Select all

$ mysql -h IP_ADDRESS -u ttuser -p
and verified that I can connect and list the databases so mysql is listening and responding.
I am at a loss to discern what else to look into, is there a way to enable logging for more verbose output?

wrc
Posts: 312
Joined: Tue May 25, 2010 8:30 pm

Re: Problems with external Database

Post by wrc » Thu Mar 18, 2021 8:00 pm

What is the other output from dbinstall.php before database connection error?
Version of Time Tracker?
Confirm that you have mysql extension installed, also try using mysqli instead (which is the default).
Are there any special characters used in password such as the @ sign? Try another password without special characters.

monadnock
Posts: 5
Joined: Thu Mar 18, 2021 6:35 pm

Re: Problems with external Database

Post by monadnock » Thu Mar 18, 2021 8:42 pm

Thanks for the quick response.
What is the other output from dbinstall.php before database connection error?
Environment Checks
WEB-INF/templates_c/ directory is writable.
WEB-INF/config.php file exists.
DSN is defined.
PHP version: 5.6.40-47+ubuntu20.04.1+deb.sury.org+1, good enough.
mysqli PHP extension is loaded.
mbstring PHP extension is loaded.
gd PHP extension is loaded.
Error: connection to database failed. MDB2 Error: unknown error
Version of Time Tracker?
1.19.26.5430
Confirm that you have mysql extension installed, also try using mysqli instead (which is the default).
Confirmed, both mysqli and mysql report the same error
Are there any special characters used in password such as the @ sign? Try another password without special characters.
I've changed the mysql password to all lowercase no specials no numbers.
User is 'ttuser'@'%' and again, verified I can auth and use the database with mysql via cli

Code: Select all

mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))  

wrc
Posts: 312
Joined: Tue May 25, 2010 8:30 pm

Re: Problems with external Database

Post by wrc » Thu Mar 18, 2021 10:41 pm

If I am getting it correctly the error is different for mysqli (unknown error vs connect failed). Verify that the port that php uses to connect to database is the same your cli uses.

wrc
Posts: 312
Joined: Tue May 25, 2010 8:30 pm

Re: Problems with external Database

Post by wrc » Thu Mar 18, 2021 11:01 pm

wrc wrote:
Thu Mar 18, 2021 10:41 pm
If I am getting it correctly the error is different for mysqli (unknown error vs connect failed). Verify that the port that php uses to connect to database is the same your cli uses.
If if the port is different, you can try to supply the required port to DSN as so:

Code: Select all

//$dsn = "<driver>://<username>:<password>@<host>:<port>/<database>";
$dsn = "mysqli://ttuser:password@ipaddress:3306/timetracker?charset=utf8mb4";

monadnock
Posts: 5
Joined: Thu Mar 18, 2021 6:35 pm

Re: Problems with external Database

Post by monadnock » Fri Mar 19, 2021 6:44 pm

Afraid I've already tried that.

Code: Select all

mysql -h IP_ADDR --port 3306 -u ttuser -p
works

Code: Select all

define('DSN', 'mysqli://ttuser:PLAINTEXT_PW@IP_ADDR:3306/timetracker?charset=utf8mb4');
Generates the same error if the port is correct ( 3306 ) or intentionally incorrect ( 3333 ).

In _fact_ I can replace the DSN host above with another address and get the same error. Is it possible that this is a permissions problem? I've installed Apache/PHP5.6 and installed Timetracker source to `/var/www/html` with ownership set to my user and the www-data group. WEB-INF is 777 as per installation instructions.

monadnock
Posts: 5
Joined: Thu Mar 18, 2021 6:35 pm

Re: Problems with external Database

Post by monadnock » Fri Mar 19, 2021 6:53 pm

So some of this php stuff is coming back to me, admittedly it has been years since I used it.

I jumped in a shell and executed the following:

Code: Select all

> $mysqli_connection = new MySQLi('IP_ADDR', 'ttuser', 'password', 'timetracker');
PHP Warning:  mysqli::mysqli(): The server requested authentication method unknown to the client [caching_sha2_password] in php shell code on line 1
PHP Warning:  mysqli::mysqli(): (HY000/2054): The server requested authentication method unknown to the client in php shell code on line 1
So looks like this may be a mismatch in versions, perhaps a module on the server I can disable? I'll look into it.

monadnock
Posts: 5
Joined: Thu Mar 18, 2021 6:35 pm

Re: Problems with external Database

Post by monadnock » Fri Mar 19, 2021 7:04 pm

Adding the following to /etc/mysql/my.cnf fixed the problem.

Code: Select all

[mysqld]
default-authentication-plugin=mysql_native_password

Post Reply