Solved Can't connect to mysql database after upgrading to mariadb 10.4.13_3

Sam9978 · Jul 3, 2020

After upgrading to mariadb 10.4.13_3 I'm unable to connect to mysql server via the normal port method. I can connect from the command line and via socket though. Anyone have any idea why that might be?

I've read UPDATING and all the stuff about the new config file locations, etc. and everything seems to be configured correctly but I can not connect via the port method which seems to be the most common method and how all our sites and services are setup.

If I downgrade to 10.4.12 everything works fine again. There are no custom config files anywhere or anything like that. Does anyone have any advice?
Thank you.

acheron · Jul 4, 2020

Is it the same bug ? PR 247747

SirDice · Jul 6, 2020

Sam9978 said:
Anyone have any idea why that might be?

What's bind-address set to? Anything in the error logs?

Sam9978 · Jul 7, 2020

acheron said:
Is it the same bug ? PR 247747

Similar but not quite. My servers start/restart fine and I can connect via command line and via socket but not via port. I have 10 servers and they all do the exact same thing. Everything is fine if I roll back to 10.4.12.

Sam9978 · Jul 7, 2020

SirDice said:
What's bind-address set to? Anything in the error logs?

bind-address is set to 127.0.0.1. All settings have been left at default. Error logs don't show anything other than the server starting up normally. I've included the config files and log snippet below.

I've made sure that there are no random my.cnf files floating around anywhere or other settings in /etc/rc.conf, etc. I've also tried connecting to 127.0.0.1, localhost, and the server IP -- all with and without the port specified as well -- but none work, the only way to connect is via the socket.

For example: I've found that Wordpress will not connect with define('DB_HOST', 'localhost'); (which is the default and has always worked until 10.4.13_3) but will connect to the socket define('DB_HOST', 'localhost:/var/run/mysql/mysql.sock');.

Command-line also works:

Code:

cms:~ # mysql -u website_user -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.4.13-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.

website_user@localhost [(none)]>

Code:

cms:~ # more /usr/local/etc/mysql/my.cnf
#
# 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/

Code:

cms:~ # more /usr/local/etc/mysql/conf.d/server.cnf
# Options specific to server applications, see
# https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#server-option-groups

# Options specific to all server programs
[server]

# Options specific to MariaDB server programs
[server-mariadb]

#
# Options for specific server tools
#

[mysqld]
user                            = mysql
# port                          = 3306 # inherited from /usr/local/etc/mysql/my.cnf
# socket                        = /var/run/mysql/mysql.sock # inherited from /usr/local/etc/mysql/my.cnf
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
net_retry_count                 = 16384
log_error                       = /var/log/mysql/${hostname}.err
# [mysqld] configuration for ZFS
# From https://www.percona.com/resources/technical-presentations/zfs-mysql-percona-technical-webinar
# Create separate datasets for data and logs, eg
# zroot/mysql      compression=on recordsize=128k atime=off
# zroot/mysql/data recordsize=16k
# zroot/mysql/logs
# datadir                       = /var/db/mysql/data
# innodb_log_group_home_dir     = /var/db/mysql/log
# audit_log_file                = /var/db/mysql/log/audit.log
# general_log_file              = /var/db/mysql/log/general.log
# log_bin                       = /var/db/mysql/log/mysql-bin
# relay_log                     = /var/db/mysql/log/relay-log
# slow_query_log_file           = /var/db/mysql/log/slow.log
# innodb_doublewrite            = 0
# innodb_flush_method           = O_DSYNC

# Options read by `mysqld_safe`
# Renamed from [mysqld_safe] starting with MariaDB 10.4.6.
[mariadb_safe]

# Options read my `mariabackup`
[mariabackup]

# Options read by `mysql_upgrade`
# Renamed from [mysql_upgrade] starting with MariaDB 10.4.6.
[mariadb-upgrade]

# Specific options read by the mariabackup SST method
[sst]

# Options read by `mysqlbinlog`
# Renamed from [mysqlbinlog] starting with MariaDB 10.4.6.
[mariadb-binlog]

# Options read by `mysqladmin`
# Renamed from [mysqladmin] starting with MariaDB 10.4.6.
[mariadb-admin]

Code:

cms:~ # more /var/log/mysql/\$\{hostname\}.err
2020-07-07  0:28:36 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-07-07  0:28:36 0 [Note] InnoDB: Uses event mutexes
2020-07-07  0:28:36 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-07-07  0:28:36 0 [Note] InnoDB: Number of pools: 1
2020-07-07  0:28:36 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-07-07  0:28:36 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-07-07  0:28:36 0 [Note] InnoDB: Completed initialization of buffer pool
2020-07-07  0:28:37 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-07-07  0:28:37 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-07-07  0:28:37 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-07-07  0:28:37 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-07-07  0:28:37 0 [Note] InnoDB: Waiting for purge to start
2020-07-07  0:28:37 0 [Note] InnoDB: 10.4.13 started; log sequence number 822068315; transaction id 336156
2020-07-07  0:28:37 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/db/ib_buffer_pool
2020-07-07  0:28:37 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-07-07  0:28:37 0 [Note] Server socket created on IP: '127.0.0.1'.
2020-07-07  0:28:37 0 [Note] InnoDB: Buffer pool(s) load completed at 200707  0:28:37
2020-07-07  0:28:38 0 [Note] Reading of all Master_info entries succeeded
2020-07-07  0:28:38 0 [Note] Added new Master_info '' to hash table
2020-07-07  0:28:38 0 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '10.4.13-MariaDB'  socket: '/var/run/mysql/mysql.sock'  port: 3306  FreeBSD Ports

richardtoohey2 · Jul 7, 2020

Anything else changed on the machines/servers at the same time? I know the old "we didn't change anything else apart from ... ohhh ..." moment from experience! Don't think the case here but worth double-checking. NOTHING else changed? No other upgrades or new policies rolled out or "unrelated" configuration changes?

If you have a spare machine and you do a clean install of Maria DB - the problematic version - does it have the same issue? That might help work out if related to the upgrade process itself or something in the new version.

richardtoohey2 · Jul 7, 2020

Also - what exactly happens - when you say Wordpress doesn't work - what do you see - any error messages from Wordpress? Do you have any snippets of e.g. PHP that show the problem? i.e. a small standalone PHP script that tries to connect and fails one way versus working another way.

EDIT: e.g. save this as con_test.php:

Code:

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_USER', 'website_user');
define('DB_PASS', 'secret');
$dbh=mysqli_connect(DB_HOST,DB_NAME,DB_USER,DB_PASS) or die('MySQLi connection failed!');
?>

And if you run it:

Code:

% php con_test.php
PHP Warning:  mysqli_connect(): (HY000/1045): Access denied for user 'test'@'localhost' (using password: YES) in /usr/home/xyz/con_test.php on line 6
MySQLi connection failed!

(Expecting it to fail on my machine because I've not got that user etc.)

Does sockstat show what you'd expect (this is a MySQL machine but think it will be similar under MariaDB):

Code:

sockstat -4 | grep 3306
mysql    mysqld     1047  13 tcp46  *:3306                *:*

Looks like there's quite a few changes in the area from the PR acheron linked to above so looks like there a few things that might have gone wonky!

SirDice · Jul 7, 2020

If you use localhost a MySQL client will actually connect to the socket, not the IP address. Because the socket moved your PHP MySQL client is probably trying to open it in the wrong location. Hence the connection failure.

SirDice · Jul 7, 2020

I actually had to look this up, but add this to your php.ini to make mysqli use the correct socket path by default:

Code:

mysql.default_socket = /var/run/mysql/mysql.sock
mysqli.default_socket = /var/run/mysql/mysql.sock

Sam9978 · Jul 8, 2020

richardtoohey2 said:
Anything else changed on the machines/servers at the same time? I know the old "we didn't change anything else apart from ... ohhh ..." moment from experience! Don't think the case here but worth double-checking. NOTHING else changed? No other upgrades or new policies rolled out or "unrelated" configuration changes?

Nothing else new that I can think of, mariadb was the only upgraded port at the time and there was nothing else I did to the servers.

richardtoohey2 said:
If you have a spare machine and you do a clean install of Maria DB - the problematic version - does it have the same issue? That might help work out if related to the upgrade process itself or something in the new version.

This is my next step, I just haven't had time but I'm hoping to get to it this weekend.

richardtoohey2 · Jul 8, 2020

How did you do the upgrade?
Does it upgrade the client and server parts (assuming they are split for MariaDB?) Was the MariaDB server restarted?

Wondering if there is anything that would need the client side to be rebuilt/relinked with the newer version but might be dragging red herrings in front of you!

So maybe the server side is actually fine (it looks fine from the logs, doesn't it?) but there's something to do with PHP/the client library that needs upgrading/restarting? Did you restart the webserver (if mod_php) or php_fpm or whatever? Don't think it would be anything to do with those just wondering if the focus is on the server and config when in fact more of a client/caller issue.

Good luck.

Sam9978 · Jul 8, 2020

Thanks for all the help! See below:

richardtoohey2 said:
Also - what exactly happens - when you say Wordpress doesn't work - what do you see - any error messages from Wordpress? Do you have any snippets of e.g. PHP that show the problem? i.e. a small standalone PHP script that tries to connect and fails one way versus working another way.

Wordpress simply says "Error establishing a database connection" nothing else and nothing in the logs. I have 3 Wordpress sites on this server and they are all the same.

richardtoohey2 said:

If I set the host as "localhost" I get:

Code:

PHP Warning:  mysqli_connect(): (HY000/2002): No such file or directory in /tmp/test.php on line 6
MySQLi connection failed!

With nothing in the error log.

If I use the IP or "127.0.0.1" I get:

Code:

PHP Warning:  Packets out of order. Expected 0 received 1. Packet size=67 in /tmp/test.php on line 6
PHP Warning:  mysqli_connect(): MySQL server has gone away in /tmp/test.php on line 6
PHP Warning:  mysqli_connect(): Error while reading greeting packet. PID=99609 in /tmp/test.php on line 6
PHP Warning:  mysqli_connect(): (HY000/2006): MySQL server has gone away in /tmp/test.php on line 6
MySQLi connection failed!

And this in the error log:

Code:

2020-07-07  0:30:37 8 [Warning] Aborted connection 8 to db: 'unconnected' user: 'unauthenticated' host: 'cms' (This connection closed normally without authentication)

I tried the same script on a mariadb 10.4.12 server and it worked fine.

richardtoohey2 said:
Does sockstat show what you'd expect (this is a MySQL machine but think it will be similar under MariaDB):

Code:
sockstat -4 | grep 3306 mysql mysqld 1047 13 tcp46 *:3306 *:*

sockstat -4 | grep 3306 shows:

Code:

88       mysqld     52696 21 tcp4   ip.address:3306  *:*

Running the same on a mariadb 10.4.12 server shows:

Code:

88       mysqld     66339 17 tcp4  *:3306                *:*

richardtoohey2 said:
Looks like there's quite a few changes in the area from the PR acheron linked to above so looks like there a few things that might have gone wonky!

Yes, I noticed that! I think your idea of a fresh install test is a good one and I'm going to do that as soon as I have time.

richardtoohey2 · Jul 8, 2020

That sockstat output is interesting - on 10.4.12 it is bound to ALL IP addresses *:3306.

On 10.4.13 it shows ip.address:3306 - not entirely sure what that means but looks more restrictive than * (all IP addresses) doesn't it?

richardtoohey2 · Jul 8, 2020

And the output from the php script is interesting, too. If you search for that error message you find stuff like this:

Warning: mysqli_connect(): (HY000/2002): No such file or directory

I'm trying to install vanilla forums on my Mac, and for this I just created a database and a user from the MySQL command line: mysql> CREATE DATABASE vanilla; Query OK, 1 row affected (0.00 sec)

stackoverflow.com

Sam9978 · Jul 8, 2020

richardtoohey2 said:
How did you do the upgrade?
Does it upgrade the client and server parts (assuming they are split for MariaDB?) Was the MariaDB server restarted?

Wondering if there is anything that would need the client side to be rebuilt/relinked with the newer version but might be dragging red herrings in front of you!

So maybe the server side is actually fine (it looks fine from the logs, doesn't it?) but there's something to do with PHP/the client library that needs upgrading/restarting? Did you restart the webserver (if mod_php) or php_fpm or whatever? Don't think it would be anything to do with those just wondering if the focus is on the server and config when in fact more of a client/caller issue.

Good luck.

Good ideas.

Yes, there are separate packages for the client and server. I upgraded by running pkg upgrade which is the way I've always done it. I restarted all services and even restarted the server just to make sure. I just checked and there are currently no packages that need to be upgraded

Something odd I just realized is that I have 2 other servers with websites running the CMS MODX Revolution and they are running fine after the mariadb upgrade. I'd totally forgotten about them as they are working and didn't throw any errors.

I compared the installed packages on the Wordpress servers and the MODX Revolution servers and, as far as mariadb, php, apache, and other relevant dependencies go, they are exactly the same versions. Out of interest I installed phpmyadmin on the MODX Revolution server and it does not work, it gives the same errors that the text php script gave which I noted in an above post.

Does that make any sense to you?

Not sure if it will help but here's the database config info from phpmyadmin, wordpress, and modx revolution:

Code:

phpmyadmin:
$cfg['Servers'][$i]['verbose'] = 'test';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['compress'] = true;
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'user';
$cfg['Servers'][$i]['password'] = 'pass';
$cfg['Servers'][$i]['only_db'] = array('database');
$cfg['Servers'][$i]['AllowRoot'] = false;

wordpress:
define('DB_NAME', 'database');
define('DB_USER', 'user');
define('DB_PASSWORD', 'pass');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');
define('DB_COLLATE', '');

modx revolution:
$database_type = 'mysql';
$database_server = 'localhost';
$database_user = 'user';
$database_password = 'pass';
$database_connection_charset = 'latin1';
$dbase = 'database';
$table_prefix = 'modx_';
$database_dsn = 'mysql:host=localhost;dbname=database;charset=latin1';

Sam9978 · Jul 8, 2020

richardtoohey2 said:
That sockstat output is interesting - on 10.4.12 it is bound to ALL IP addresses *:3306.

On 10.4.13 it shows ip.address:3306 - not entirely sure what that means but looks more restrictive than * (all IP addresses) doesn't it?

Yes, I believe that is due to the new changes to 10.4.13_3 namely "bind-address" which wasn't specified previously.

Sam9978 · Jul 8, 2020

SirDice said:
I actually had to look this up, but add this to your php.ini to make mysqli use the correct socket path by default:

Code:
mysql.default_socket = /var/run/mysql/mysql.sock mysqli.default_socket = /var/run/mysql/mysql.sock

This works!

However, that's telling PHP to use the socket and not the port, right? From what I've seen most everything connects via port by default and not socket so the above shouldn't be necessary. If nothing else there would be tons of WordPress users having issues but I haven't seen anyone say anything about it so while this does work there must be a different solution that would allow sites and services to continue to connect via port, right? Or am I misunderstanding something?

Sorry, not meaning to sound ungrateful for the help, just a little confused and frustrated that I can't get this sorted out. I haven't seen other people having issues like this so it must be something unique to my configurations but I can't for the life of me figure it out, everything seems like it should be working.

richardtoohey2 · Jul 8, 2020

What's the sockstat output for the WORKING servers? I might be barking up the wrong tree but it's got to be a question of working out what is different from working versus non-working servers.

I can imagine you are frustrated - it feels like it's really within grasp but not quite there yet.

My theory is that 10.4.12 listens on all IPs - so 127.0.0.1/localhost work. When you go to 10.4.13 you are ending up with something different - so trying to connect via IP isn't working, but connecting via the socket is.

Does sockstat literally show "ip.address:3306"?

richardtoohey2 · Jul 8, 2020

Sam9978 said:
Yes, I believe that is due to the new changes to 10.4.13_3 namely "bind-address" which wasn't specified previously.

MySQL/MariaDB Server: Bind To Multiple IP Address - nixCraft

I have MySQL database system running on FreeBSD. The server has multiple ip address. The mysqld used by many websites hosted on two other CentOS based servers. I would like to bind MySQL sever running on Unix / Linux server to more than one IP address such as 202.54.1.2, 202.54.1.10, and...

www.cyberciti.biz

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
If the address is a “regular” IPv4 address (such as 127.0.0.1), the server accepts TCP/IP connections only for that particular IPv4 address.

Could you try bind-address 0.0.0.0 in your configuration file (and restart MariaDB) to see if that makes any difference?

Hope I'm not leading you in completely the wrong direction ...

EDIT: you said this: Yes, I believe that is due to the new changes to 10.4.13_3 namely "bind-address" which wasn't specified previously. I can't see that in any change logs - are you sure? I don't mean that rudely - just that I can't see anything in the change logs to do with that change; but there appear to have been changes in the FreeBSD port to do with the socket file location.

EDIT2: maybe this bit: https://www.freshports.org/databases/mariadb104-server/

You must merge ... your server configuration with conf.d/server.cnf.

SirDice · Jul 8, 2020

Sam9978 said:
However, that's telling PHP to use the socket and not the port, right? From what I've seen most everything connects via port by default and not socket so the above shouldn't be necessary.

Again, if you connect to localhost, you will always connect to the socket, not using TCP/IP. You will only connect to the TCP port if you use 127.0.0.1. There is a distinct difference.

Sam9978 · Jul 8, 2020

SirDice said:
Again, if you connect to localhost, you will always connect to the socket, not using TCP/IP. You will only connect to the TCP port if you use 127.0.0.1. There is a distinct difference.

I see, so I got it mixed up. Thanks for the clarification.

I found PR 246694 which confirms what you said earlier about explicitly specifying the socket path in php.ini ... I'd somehow missed it before when I looking into all this. That's going to be quite the hassle for people with a lot of mariadb servers.

However, I still don't get why MODX Revolution works when using "localhost" but phpmyadmin and WordPress don't.

Jose · Jul 8, 2020

Sam9978 said:
However, I still don't get why MODX Revolution works when using "localhost" but phpmyadmin and WordPress don't.

Different database drivers. Phpmyadmin and presumably Wordpress use msqli. Modx uses PDO.

Editorial comment: Assuming a Unix domain socket connection when the host is "localhost" is a crock. Unix domain sockets always connect over a filesystem path. There is no host to connect to. Furthermore the string "localhost" is always a name for the TCP loopback address 127.0.0.1. This is worse than nonsense behavior, it's actively harmful and a trap for the unwary.

You've crushed my hopes that PHP was getting better.

Sam9978 · Jul 8, 2020

Jose said:
Different database drivers. Phpmyadmin and presumably Wordpress use msqli. Modx uses PDO.

Makes sense. Thanks for the explanation.

Sam9978 · Jul 8, 2020

I know what I have to do now to fix this. Thank you for all the help and information [USER=48930]richardtoohey2[/USER], [USER=1677]SirDice[/USER], [USER=33252]Jose[/USER], and [USER=15063]acheron[/USER]!

Solved Can't connect to mysql database after upgrading to mariadb 10.4.13_3

Member

Aspiring Daemon

Administrator

Member

Member

Well-Known Member

Well-Known Member

Administrator

Administrator

Member

Well-Known Member

Member

Well-Known Member

Well-Known Member

Member

Member

Member

Well-Known Member

Well-Known Member

Administrator

Member

Aspiring Daemon

Member

Member