I wish to run PHP code on the host machine which makes a connection to a MySQL (containerized using Docker) database. But I am getting the following error:
Connection failed: MySQL server has gone away
On the host machine(Ubuntu 18.04 LTS) I have the following software installed:
PHP 7.2.10 (Non-Docker)
MySQL 8.0.13 (Docker)
Apache 2.5.29 (Non-Docker)
I am using the following connection string in the program on the host machine:
<?php
$connection=mysqli_connect('127.0.0.1','vernemq','xyzw','some_db');
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
?>
Note: The above program is written in a PHP file which is located in directory /var/www/html
For understanding let's say the host machine has the IP address 172.10.10.15.
When running this same program with a LAMPP server on a different machine (IP address: 172.10.10.16) on the same network as that of the host, I am able to connect to the MySQL server. I am running the following modified program on the different machine:
<?php
$connection=mysqli_connect('172.10.10.15','vernemq','xyzw','some_db');
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
?>
I am even able to connect to the Dockerized MySQL server on the host machine by running the following command on the command prompt of the host machine:
$mysql -h 127.0.0.1 -u vernemq -p
Running the following command from the MySQL prompt returns the following:
mysql> SELECT user, host FROM mysql.user
-> ;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| vernemq | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| vernemq | localhost |
+------------------+-----------+
Any pointer on what could be wrong?
Update 04/01/2019 I noted following things
When I ran the db connection file from the command line, I got the following error.
Getting following error when running the php file from the command prompt
PHP Warning: mysqli_connect(): Unexpected server respose while doing caching_sha2 auth: 109 in /var/www/html/db.php on line 2
PHP Warning: mysqli_connect(): MySQL server has gone away in /var/www/html/db.php on line 2
PHP Warning: mysqli_connect(): (HY000/2006): MySQL server has gone away in /var/www/html/db.php on line 2
Connection failed: MySQL server has gone away
For this I changed the User authentication method from mysql_native to caching_sha2 with following command
ALTER USER 'vernemq' IDENTIFIED WITH caching_sha2_password BY 'vernemq';
And it started working.
But I don't want to use caching_sha2 authentication because it breaks another programs that were working with native_mysql authentication method. What is the way out?
