'Incorrect user or password' when logging in. PHP 'Connection refused'

[BOkay]

I appear to have a php connection issue to my database on my Expedition instance.

When trying to upgrade Expedition to the latest version I get the following error.

Preparing to unpack .../expedition-beta_1.1.70_amd64.deb ...
Unpacking expedition-beta (1.1.70) over (1.1.70) ...
Setting up expedition-beta (1.1.70) ...
PHP Warning:  mysqli::__construct(): (HY000/2002): Connection refused in /var/www/html/libs/database.php on line 22
PHP Fatal error:  Database connection failed: Connection refused in /var/www/html/libs/database.php on line 24
Checking for old projects and Devices what are not Encrypted
PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000] [2002] Connection refused in /var/www/html/libs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:43
Stack trace:
#0 /var/www/html/libs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(43): PDO->__construct('mysql:host=127....', 'root', 'paloalto', Array)
#1 /var/www/html/libs/vendor/illuminate/database/Connectors/Connector.php(64): Doctrine\DBAL\Driver\PDOConnection->__construct('mysql:host=127....', 'root', 'paloalto', Array)
#2 /var/www/html/libs/vendor/illuminate/database/Connectors/Connector.php(43): Illuminate\Database\Connectors\Connector->createPdoConnection('mysql:host=127....', 'root', 'paloalto', Array)
#3 /var/www/html/libs/vendor/illuminate/database/Connectors/MySqlConnector.php(24): Illuminate\Database\Connectors\Connector->createConnection('mysql:host=127....', Array, Array)
#4 /var/www/html/libs/vendor/illuminate/database/Connectors/ConnectionFactory.php(183): Illuminate\Database\Connectors\MySqlConnector->connect(Array)
#5 [i in /var/www/html/libs/vendor/illuminate/database/Connection.php on line 664

Any thoughts on how to resolve the issue?

[BOkay]

l also get the following

pane@PAISASPANORA001:~$ tail /var/log/apache2/error.log
[Mon Jun 15 08:27:17.423133 2020] [:error] [pid 1330] [client 10.199.105.198:50747] PHP Warning: mysqli::__construct(): (HY000/2002): Connection refused in /var/www/html/libs/database.php on line 22, referer: https://198.84.35.11/
[Mon Jun 15 08:27:17.423157 2020] [:error] [pid 1330] [client 10.199.105.198:50747] PHP Fatal error: Database connection failed: Connection refused in /var/www/html/libs/database.php on line 24, referer: https://198.84.35.11/
[Mon Jun 15 08:27:17.505744 2020] [:error] [pid 1587] [client 10.199.105.198:50750] PHP Warning: mysqli::__construct(): (HY000/2002): Connection refused in /var/www/html/libs/database.php on line 22, referer: https://198.84.35.11/
[Mon Jun 15 08:27:17.505766 2020] [:error] [pid 1587] [client 10.199.105.198:50750] PHP Fatal error: Database connection failed: Connection refused in /var/www/html/libs/database.php on line 24, referer: https://198.84.35.11/
[Mon Jun 15 08:27:17.515943 2020] [:error] [pid 1330] [client 10.199.105.198:50752] PHP Warning: mysqli::__construct(): (HY000/2002): Connection refused in /var/www/html/libs/database.php on line 22, referer: https://198.84.35.11/
[Mon Jun 15 08:27:17.515963 2020] [:error] [pid 1330] [client 10.199.105.198:50752] PHP Fatal error: Database connection failed: Connection refused in /var/www/html/libs/database.php on line 24, referer: https://198.84.35.11/
[Mon Jun 15 08:27:17.522211 2020] [:error] [pid 1328] [client 10.199.105.198:50753] PHP Warning: mysqli::__construct(): (HY000/2002): Connection refused in /var/www/html/libs/database.php on line 22, referer: https://198.84.35.11/
[Mon Jun 15 08:27:17.522231 2020] [:error] [pid 1328] [client 10.199.105.198:50753] PHP Fatal error: Database connection failed: Connection refused in /var/www/html/libs/database.php on line 24, referer: https://198.84.35.11/
[Mon Jun 15 08:28:39.027273 2020] [:error] [pid 1330] [client 10.199.105.198:28156] PHP Warning: mysqli::__construct(): (HY000/2002): Connection refused in /var/www/html/libs/database.php on line 22, referer: https://198.84.35.11/
[Mon Jun 15 08:28:39.027314 2020] [:error] [pid 1330] [client 10.199.105.198:28156] PHP Fatal error: Database connection failed: Connection refused in /var/www/html/libs/database.php on line 24, referer: https://198.84.35.11/

[lychiang]

Hello @BOkay 

Could you please try below steps :

1. In Setting-> M. Learning-> Server information-> Expedition ML address, trying to set the Expedition IP to 127.0.0.1 , this will fall back to Expedition server IP and click “Save”. This step will re-initiate the database connections. (If Expedition is behind a different IP or NATed IP , you would need to put the real IP as ML IP not the NATed IP ) , check the IP using ifconfig.  (Most cases, this step should fix your Database connection issue, if not, continue to step 2)

2. Go to #more /home/userSpace/userDefinitions.php review the parameters in the files look like below:

define ('DBServer' , '127.0.0.1');

define ('DBUser',  'root');

define ('DBPass', 'paloalto');

define ('DBName' ,'project_schema');

define ('PARSER_max_execution_time','10000');

define ('PARSER_max_execution_memory','1G');

define ('DBSQL_LOG_BIN', 0);

3. Issue SQL commands to check which IPs were being allowed. 

#mysql -uroot -ppaloalto

expedition@expedition-documentation:~$ mysql -uroot -ppaloalto

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 319026

Server version: 10.1.44-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select host,user from mysql.user;

+--------------------------+------------------+

| host                     | user             |

+--------------------------+------------------+

| 10.8.200.34              | root             |

| 127.0.0.1                | root             |

| ::1                      | root             |

| expedition-documentation | root             |

| localhost                | debian-sys-maint |

| localhost                | root             |

+--------------------------+------------------+

6 rows in set (0.00 sec)

Verified Expedition IP and User Root is showing up as above

4. Check that /etc/mysql/my.cnf is correctly setup not bind to 127.0.0.1. There is a health check in the dashboard that checks this as well. 

Make sure below line is commented out:

# The following values assume you have at least 32M ram

/127.0.0.1

...skipping

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

#bind-address = 127.0.0.1

[BOkay]

Thanks. Here are the results.

1) I can't login via GUI

2) My parameters match

3) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

4) My parameters are correct, the proper things are commented out.

[lychiang]

Hi @BOkay 
have you ran “sudo apt-get upgrade” by mistake ? We have seen user can’t login to GUI after did a “sudo apt-get upgrade “ 

[BOkay]

Nope, I did sudo apt-get update and sudo apt-get install expedition-beta.  

I used command line history and just verified now.

[lychiang]

Hello BOkay,

The error could be that the MySQL is not listening to ports. Do a

netstat -pnuat
And check if you have any line with port 3306.
Also, which IP are defined to be listening. Normally it should state all ipv6 (it includes ipv4)

Below are possible reasons of this database connection error:

a) the MySQL service is blocked to only listen to socket connections,
b) the MySQL went uninstalled
c) the MySQL is there, but firewall is blocking connections to MySQL
d) that they upgraded MySQL and it only allows root MySQL connections from system root user (we connect using www-data)

[BOkay · Accepted solution]

I resolved the issue by doing the following:

I had to remove the tc.log file as it became corrupt.

sudo rm /var/lib/mysql/tc.log

I then reinstalled expedition

sudo apt-get install --reinstall expedition-beta

[lychiang]

@BOkay Thank you for sharing the solution!