Solved mariadb not seeing eye to eye with 127.0.0.1 and localhost

Understudy

Active Member

Reaction score: 5
Messages: 133

Hi All,

Lets get the basics out of the way. FBSD 12.2, MariaDB 10.5.10 server and client, along with php 7.4.

The big issue appears to be that when trying to access the database for the coppermine gallery that it wants to use 127.0.0.1 solely instead of localhost. This does not bode well for coppermine and phpMyAdmin.

Lets take a look at the basics
/usr/local/etc/mysql/my.cnf
Code:
#
# This group is read both by the client and the server
# use it for options that affect everything, see
# https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#option-groups
#
[client-server]
port    = 3306
socket  = /var/run/mysql/mysql.sock

#
# include *.cnf from the config directory
#
!includedir /usr/local/etc/mysql/conf.d/

# bind address for localhost
[mysqld]
skip-networking
bind-address = 127.0.0.1
port     = 3306
socket  = /var/run/mysql/mysql.sock
ps -ax | grep mysql
Code:
19303  -  Is        0:00.01 /bin/sh /usr/local/bin/mariadbd-safe --defaults-extra-file=/usr/local/etc/mysql/my.cnf --user=m
19448  -  I         0:00.38 /usr/local/libexec/mariadbd --defaults-extra-file=/usr/local/etc/mysql/my.cnf --basedir=/usr/lo
19570  0  S+        0:00.00 grep mysql

netstat -ln | grep mysql
Code:
fffff8043838e100 stream      0      0 fffff804c604f1e0                0                0                0 /var/run/mysql/mysql.sock
So lets see where things go wrong

When trying to do the install of coppermine one of the steps asks you to connect to the database. When I try to here is what I see
Code:
The following errors were encountered and need to be corrected first:
Could not create a MySQLi connection, please check the MySQLi details entered
Database error: No such file or directory

So I check on the cli of the server:
Code:
root@nexus:~ # mysql -u photouser -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.5.10-MariaDB FreeBSD Ports

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

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

photouser@localhost [(none)]> use photodb;
Database changed
photouser@localhost [photodb]> show tables;
+----------------------+
| Tables_in_photodb    |
+----------------------+
| cpg143_albums        |
| cpg143_banned        |
| cpg143_bridge        |
| cpg143_categories    |
| cpg143_comments      |
| cpg143_config        |
| cpg143_dict          |
| cpg143_ecards        |
| cpg143_exif          |
| cpg143_favpics       |
| cpg143_filetypes     |
| cpg143_hit_stats     |
| cpg143_pictures      |
| cpg143_plugins       |
| cpg143_sessions      |
| cpg143_temp_data     |
| cpg143_usergroups    |
| cpg143_users         |
| cpg143_vote_stats    |
| cpg143_votes         |
| cpg15x_albums        |
| cpg15x_banned        |
| cpg15x_bridge        |
| cpg15x_categories    |
| cpg15x_categorymap   |
| cpg15x_comments      |
| cpg15x_config        |
| cpg15x_dict          |
| cpg15x_ecards        |
| cpg15x_exif          |
| cpg15x_favpics       |
| cpg15x_filetypes     |
| cpg15x_hit_stats     |
| cpg15x_languages     |
| cpg15x_pictures      |
| cpg15x_plugins       |
| cpg15x_sessions      |
| cpg15x_temp_messages |
| cpg15x_usergroups    |
| cpg15x_users         |
| cpg15x_vote_stats    |
| cpg15x_votes         |
| cpg16x_albums        |
| cpg16x_banned        |
| cpg16x_bridge        |
| cpg16x_categories    |
| cpg16x_categorymap   |
| cpg16x_comments      |
| cpg16x_config        |
| cpg16x_dict          |
| cpg16x_ecards        |
| cpg16x_exif          |
| cpg16x_favpics       |
| cpg16x_filetypes     |
| cpg16x_hit_stats     |
| cpg16x_languages     |
| cpg16x_pictures      |
| cpg16x_plugins       |
| cpg16x_sessions      |
| cpg16x_temp_messages |
| cpg16x_usergroups    |
| cpg16x_users         |
| cpg16x_vote_stats    |
| cpg16x_votes         |
| pics_albums          |
| pics_banned          |
| pics_bridge          |
| pics_categories      |
| pics_categorymap     |
| pics_comments        |
| pics_config          |
| pics_dict            |
| pics_ecards          |
| pics_exif            |
| pics_favpics         |
| pics_filetypes       |
| pics_hit_stats       |
| pics_languages       |
| pics_pictures        |
| pics_plugins         |
| pics_sessions        |
| pics_temp_messages   |
| pics_usergroups      |
| pics_users           |
| pics_vote_stats      |
| pics_votes           |
+----------------------+
86 rows in set (0.001 sec)

photouser@localhost [photodb]>
So I know from the cli I can see the DB and tables

Well let me check a different way:
Code:
root@nexus:~ # mysql -h 127.0.0.1 -P 3306 -u photouser -p
Enter password:
ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (36)
root@nexus:~ #
Lets try it another way:
Code:
root@nexus:~ # mysql -h localhost -P 3306 -u photouser -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.5.10-MariaDB FreeBSD Ports

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

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

photouser@localhost [(none)]>
Well that worked.

When I try to connect via phpMyAdmin I get:
Code:
mysqli_real_connect(): (HY000/2002): No such file or directory
but the cli shows it is there.

How do I get the localhost recognized?
 

Vull

Aspiring Daemon

Reaction score: 564
Messages: 859

I don't have your answer, but have noticed that in /etc/hosts localhost is defined twice, and the ipv4 address is on the 2nd line. This might or might not be related to your problem, depending perhaps on how your system resolves host names.
Code:
::1            localhost localhost.my.domain
127.0.0.1        localhost localhost.my.domain
 

Alain De Vos

Son of Beastie

Reaction score: 869
Messages: 2,826

Like i said before ,localhost is sockets in mysql language. Use 127.0.0.1 and issue can be closed. Mariadb just works fine with IP-address "127.0.0.1".
 

dbdemon

Member

Reaction score: 27
Messages: 46

I noticed you have skip_networking enabled. I get exactly the same error if I enable that:
$ mariadb -uhello -p -h127.0.0.1 -P3306
Enter password:
ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (36)

So I'd recommend turning that off / removing that line (and then restart mysql-server) if you must connect on 127.0.0.1.
 
OP
Understudy

Understudy

Active Member

Reaction score: 5
Messages: 133

I don't have your answer, but have noticed that in /etc/hosts localhost is defined twice, and the ipv4 address is on the 2nd line. This might or might not be related to your problem, depending perhaps on how your system resolves host names.
Code:
::1            localhost localhost.my.domain
127.0.0.1        localhost localhost.my.domain
My /etc/hosts

# $FreeBSD: releng/12.2/lib/libc/net/hosts 338729 2018-09-17 18:56:47Z brd $
#
# Host Database
#
# This file should contain the addresses and aliases for local hosts that
# share this file. Replace 'my.domain' below with the domainname of your
# machine.
#
# In the presence of the domain name service or NIS, this file may
# not be consulted at all; see /etc/nsswitch.conf for the resolution order.
#
#
::1 localhost localhost.my.domain
127.0.0.1 localhost localhost.my.domain


So I suspect that is not the issue
 
OP
Understudy

Understudy

Active Member

Reaction score: 5
Messages: 133

I noticed you have skip_networking enabled. I get exactly the same error if I enable that:


So I'd recommend turning that off / removing that line (and then restart mysql-server) if you must connect on 127.0.0.1.

root@nexus# mysql -h 127.0.0.1 -P 3306 -u photouser -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.10-MariaDB FreeBSD Ports

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

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

photouser@127.0.0.1 [(none)]>


root@nexus# mysql -h localhost -P 3306 -u photouser -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 105
Server version: 10.5.10-MariaDB FreeBSD Ports

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

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

photouser@localhost [(none)]>


Okay so doing the comment out of #skip-networking helped with the localhost 127.0.0.1 issue on cli access. However it did not change anything for coppermine or phpmyadmin access.
 

Vull

Aspiring Daemon

Reaction score: 564
Messages: 859

My /etc/hosts

# $FreeBSD: releng/12.2/lib/libc/net/hosts 338729 2018-09-17 18:56:47Z brd $
#
# Host Database
#
# This file should contain the addresses and aliases for local hosts that
# share this file. Replace 'my.domain' below with the domainname of your
# machine.
#
# In the presence of the domain name service or NIS, this file may
# not be consulted at all; see /etc/nsswitch.conf for the resolution order.
#
#
::1 localhost localhost.my.domain
127.0.0.1 localhost localhost.my.domain


So I suspect that is not the issue
It's the same. Some time back I started routinely changing it to:
Code:
::1                     localhost.my.domain
127.0.0.1               localhost
... and it corrected some problems I was having, the exact details of which I don't remember, with Apache 2.4, php 7, and pure-ftpd, all of which needed localhost to resolve as 127.0.0.1, but which seemed to be getting ::1 instead.
 

Alain De Vos

Son of Beastie

Reaction score: 869
Messages: 2,826

Mariadb does not fail to resolve localhost. Because localhost in mysql language is sockets. So there is nothing to resolve.
Does everything works fine using "127.0.0.1" ?
 
OP
Understudy

Understudy

Active Member

Reaction score: 5
Messages: 133

Mariadb does not fail to resolve localhost. Because localhost in mysql language is sockets. So there is nothing to resolve.
Does everything works fine using "127.0.0.1" ?
No, it took a lot to figure this one out.
 

richardtoohey2

Aspiring Daemon

Reaction score: 345
Messages: 673

::1 is for IPv6 isn't it?

This issue (localhost/127.0.0.1 and PHP access to MySQL/MariaDB) is naggingly familiar but I can't remember the details. Isn't it more to do with the PHP configuration or the MySQL client? I think you are very focussed on the server (MariaDB) side but think it might be more on the client side. What environment will PHP be running under (php-fpm, mod_apache, etc?) Any chroot going on (i.e. can it find /etc/resolv.conf - if it can't it (PHP) won't be able resolve localhost)?

Does a PHP CLI connection script work (i.e. outside of the web server environment)?
 
OP
Understudy

Understudy

Active Member

Reaction score: 5
Messages: 133

It is nice when you can bring things to a close. Solution time.

With the help of a tech from Manchester England, this forum, a lot of google-fu, we came up with an answer.
This issue is with PHP. In my case I am using PHP74. We ended up editing a file called /usr/local/etc/php/ext-20-mysqli.ini.
We added these two lines to it.
Code:
pdo_mysql.default_socket="/var/run/mysql/mysql.sock"
mysqli.default_socket="/var/run/mysql/mysql.sock"
Restarted things and this appears to have made a solution. I am not sure how severe an issue this is but I am probably going to submit a bug report I am just not sure who gets it. I will probably start with php.

Thank you to everyone who helped out.
 

Alain De Vos

Son of Beastie

Reaction score: 869
Messages: 2,826

I use "127.0.0.1" because otherwise you have to know the exact location of the socket.
But you found it, "/var/run/mysql/mysql.sock"
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 13,106
Messages: 39,696

I use "127.0.0.1" because otherwise you have to know the exact location of the socket.
But you found it, "/var/run/mysql/mysql.sock"
Configure the socket correctly for the server and/or client in my.cnf.

Code:
[client]
# port                  = 3306 # inherited from /usr/local/etc/mysql/my.cnf
# socket                = /var/run/mysql/mysql.sock # inherited from /usr/local/etc/mysql/my.cnf
Code:
[client-server]
port    = 3306
socket  = /var/run/mysql/mysql.sock
 
Top