Solved mariadb not seeing eye to eye with 127.0.0.1 and localhost

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?
 
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
 
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.
 
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
 
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.
 
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.
 
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" ?
 
::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)?
 
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.
 
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"
 
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
 
May I ask why this is an issue with Mariadb and not Mysql? Should I have just left the socket be at /tmp/mysql.sock?
 
Because the maintainer changed the scripts and default configuration files (the MariaDB and MySQL ports have different maintainers).

Besides that, /var/run/ makes more sense for these types of files than /tmp/.
 
Hmm, thanks
You know, today I ran pkg upgrade, then freebsd-update, and it happened again.
I went from 13.0 to 13.1.
Turns out it the two lines are now gone. (The file must have been re-written by pkg upgrade?)

I do agree that it does make more sense to place it in /var/run, but then I need to remember this every time I run pkg upgrade, correct?
 
I also use MariaDB on FreeBSD and I have a problem with the configuration.
bind-address = 127.0.0.1 but MariaDB/MySQL is listening on all interfaces. Output of sockstat:
mysql mariadbd 1153 17 tcp4 *:3306 *:*

When I enable skip-networking, the problem is that not even localhost (127.0.0.1) is served.

I want MariaDB/MySQL to be reachable "only" over 127.0.0.1 via TCP.

Any solutions?
 
Because the maintainer changed the scripts and default configuration files (the MariaDB and MySQL ports have different maintainers).

Besides that, /var/run/ makes more sense for these types of files than /tmp/.
I still am not clear on what the solution is as the thread says solved and clearly it is not.
 
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.
this appears to be a short term hack which does not resolve the underlying issue, it is not a solution. Updating php will clearly wipe this and leave us back where we started, mariadb not responding to localhost or 127.0.0.1
 
mariadb not responding to localhost or 127.0.0.1
Hm… I'm using MariaDB since years on FreeBSD, and it always responded on localhost and 127.0.0.1 as long it is configured to do so (also with PHP without touching /usr/local/etc/php/ext-20-mysqli.ini). Have you done a GRANT USAGE ON… / GRANT ALL PRIVILEGES ON… for your database user? Your user must be allowed to access that host ("%" is a wildcard). Check SELECT `user`, `host` FROM `mysql`.`user`;. Also check SHOW variables LIKE '%bind%';.
 
Hm… I'm using MariaDB since years on FreeBSD, and it always responded on localhost and 127.0.0.1 as long it is configured to do so (also with PHP without touching /usr/local/etc/php/ext-20-mysqli.ini). Have you done a GRANT USAGE ON… / GRANT ALL PRIVILEGES ON… for your database user? Your user must be allowed to access that host ("%" is a wildcard). Check SELECT `user`, `host` FROM `mysql`.`user`;. Also check SHOW variables LIKE '%bind%';.
bind_address | 127.0.0.1

My issue is that Mariadb >103 does not work out of the box with other ports yet Mysql 8x seems to do so. If I am able to get php to work by hardcoding the socket then the issue does not reside with the privs for database user access. There is absolutely zero documentation provided updating or not regarding this undocumented configuration to get mariadb to work on FreeBSD. When the current maintainer took over mariadb there were changes and yet a blanket response working as intended.

Where are the docs for FreeBSD MariaDB perhaps the troubleshooting section at https://wiki.freebsd.org/MariaDB

This indicates I need to hard code other 3rd party ported software configs (if possible) to force MariaDB to be accessible locally?
 
Back
Top