PHP script cannot connect to the database

Operating system: Ubuntu
OS version: 20.04

What do i have to check in virtualmin settings to be sure that php scripts on virtual server can get access to mysql.
Now it looks like i can connect to mysql locally, open database etc, but php script does not have the connection with the same user name and password.

I did a simple test - mysqli_connect returns
errno: 2002
error: No such file or directory

So I think I’ve broken the mysql config but cannot understand where

You’ll need to post your PHP for the connection and query (redacted, of course) for anyone to provide meaningful advice on this problem.

Or else you can read this and see if anything jumps out at you.

Richard

The code is very simple just for testing

$dbc = mysqli_connect ('localhost', '$username', '$password');
if ($dbc){
echo "Connection to Server opened";
}
else{
echo "There's no connection to server". PHP_EOL;;
echo "errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "error: " . mysqli_connect_error() . PHP_EOL;

and it returns There’s no connection to server errno: 2002 error: No such file or directory

That is why i think i have some issues in the server settings

As a disclaimer, I know almost nothing about Ubuntu. That being said…

That’s a socket error. Does the site in question happen to have its own php.ini?

Richard

try this one

<?php
$servername   = "localhost";
$database = "database";
$username = "user";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}
  echo "Connected successfully";
?>

yes there is one created by virtualmin as i understand. I changed nothing there

Result is the same: Connection failed: No such file or directory

I think you have to know - I had to delete mysql 8 and installed 5.6
I think I could not restore all needed settings… but i don’t know where to look…

I see, problem is there. cannot you restore backup?

yes I can restore :slight_smile: but i need 5.6… :slight_smile: it is a long story

I had an old laravel app. Without support already.
It cannot work on mysql 8 because of ‘only_full_group_by’ issue.

First i tried to change sql_mode and delete ‘only_full_group_by’ from there. It looked nice and i can successfully execute the query with the problem in the console and the server shows me the updated sql_mode without ‘only_full_group_by’ but the application still saw ‘only_full_group_by’ somewhere and was not able to work… i spent hours to find the reason…

So the downgrade to 5.6 was my idea how to solve my problem… but… :slight_smile:
I think i missed some settings in virtualmin or somewhere else but i don’t know how to find it

I see. well I do not know how did you install your sql server but on ubuntu it should be simple like this:

sudo apt update
sudo apt install mysql-server

Then run:

sudo mysql_secure_installation

and follow the screen and use common sense - its just few questions like password for sql root user etc… you should be fine.

also you should update that app instead downgrade sql server… eh

Yes it works for the last mysql version but for old 5.5 i had to go a bit different way Install MySQL 5.6.xx on Ubuntu 18.04 & Ubuntu 20.04 · GitHub

it was installed successfully except this socket issue

oh it is almost impossible… I am not too good to update all sql queries there to comply with ‘only_full_group_by’

If you could tell me how to 100% disable ‘only_full_group_by’ in virtualmin+mysql8 I would be very grateful

well you realise that virtualmin is one thing and sql server is another thing. Also if the installation was successful you should not have any issues.

Anyway I think if you cannot update the app but you need it to run for whatever reason, do not run it on production server as that could be quiet dangerous. Have totally separate server for outdated things somewhere on local network or something so you can stay safe.

anyway :slight_smile:

you can remove ONLY_FULL_GROUP_BY from mysql console like this:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Or to just disable it:

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

This will remove this mode from MySQL without affecting the others.

You may need to edit your /etc/my.cnf file as well to ensure this change holds. Look for the line that says sql_mode= and remove only_full_group_by, then restart MySQL.

Conversely, if this mode is disabled and you want to enable it, just follow these steps in reverse. The MySQL command will be:

mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

You’ll also want to add in or uncomment only_full_group_by to /etc/my.cnf and restart MySQL.

This should work on sql8 too. I hope that somehow make sense to you and helps :wink:

1 Like

Dear unborn,
thank you for your advices and your time.

I think I can try again to follow this recommendations even yesterday i did the same without the result. I don’t know why but the application thought that the mysql was still in only_full_group_by mode but server shown that it is not true…
That is why I thought that virtualmin maybe create any special reality for my application :slight_smile: :slight_smile:

Thank you. I will let know here about the result

did you restart sql server after your changes? that is quiet important.

many times… it was really a kind of magic…

well, try it when you have a chance even on sql 8.x and on sql 5.x and let me know, but I think, that is the max from my side as this should work. Good luck man.

I think I found the solution for my application. :slight_smile:
So I return to mysql 8 and disable only_full_group_by.

Thanks to everybody who tried to help me. :hugs:

PS bdw the reason with the php no connection issue was in wrong settings for socket. The connection works for 127.0.0.1. But I don’t know where was my mistake because I found another solution for the initial issue and had no reason for any other investigation.

1 Like