MySQL / MariaDB sock failure - troubleshooting

[Janusz]

Hi All,

I've recently relocated the FreeBSD machine to a new location, new network, etc. I've upgraded the packages shortly after confirming physical connectivity was OK and Nginx instance threw HTTP 500. I started investigating and noticed problems with the MariaDB installation:

# mysqladmin -u root -p status
Enter password: 
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysql/mysql.sock' exists!

I then tried issuing the upgrade command to see whether there's anything different being output:

/var/db/mysql # mysql_upgrade
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)
FATAL ERROR: Upgrade failed

Starting the server fails. Domain logs show the following:

Version: '10.4.13-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD Ports
2020-06-12 19:05:12 0 [Note] InnoDB: Buffer pool(s) load completed at 200612 19:05:12
2020-06-12 20:33:02 0 [Note] /usr/local/libexec/mysqld (initiated by: unknown): Normal shutdown
2020-06-12 20:33:02 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-06-12 20:33:02 0 [Note] InnoDB: FTS optimize thread exiting.
2020-06-12 20:33:02 0 [Note] InnoDB: Starting shutdown...
2020-06-12 20:33:02 0 [Note] InnoDB: Dumping buffer pool(s) to /var/db/mysql/ib_buffer_pool
2020-06-12 20:33:02 0 [Note] InnoDB: Instance 0, restricted to 2003 pages due to innodb_buf_pool_dump_pct=25
2020-06-12 20:33:02 0 [Note] InnoDB: Buffer pool(s) dump completed at 200612 20:33:02
2020-06-12 20:33:03 0 [Note] InnoDB: Shutdown completed; log sequence number 3062311769; transaction id 14546297
2020-06-12 20:33:03 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-06-12 20:33:03 0 [Note] /usr/local/libexec/mysqld: Shutdown complete

Unsure how to progress this. Can I rollback the upgrade somehow? How can I troubleshoot this further?

Thanks in advance!
Janusz

 

[jmos]

Whatever "Domain logs" are … how exactly have you tried to start your database? "service mysql-server (one)start"? Your first two outputs are telling me that your database simply isn't up, the output of the third I've never seen (or remember?), but to me it looks like it has been running till a shutdown.

 

[joneum@]

Have you read UPDATING?

 

[Janusz]

jmos - I've tried to start is precisely as per you say. The output is as follows:

# service mysql-server start
Starting mysql.

Nothing more and the server fails to get up.

What I meant by 'domain logs' is the <whateverdomain.com>.err files in /var/db/mysql.

joneum@ - I have but a while ago. Appreciate reading it for each pkg and per update case is the way to go but here we are. I've done some digging on the net to try and find revert info but hitting SQL rollback only.

 

[jmos]

# service mysql-server start
Starting mysql.

Nothing more and the server fails to get up.

To me that looks like it is up and running. How did you check afterwards that it isn't? "ps aux | grep sql" can tell more.

Again: Your first two outputs are exactly what you're getting if MariaDB isn't started yet. Your third output is exactly what you're getting when it's been up & running from 19:05 till 20:33. And the last one now tells me that you've started it now.

 

[anlashok]

Check your config files, you appear to have two different socket settings
trying to run mysqladmin:

error: 'Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysql/mysql.sock' exists!

but your domain logs show:

Version: '10.4.13-MariaDB' socket: '/tmp/mysql.sock' port: 3306 FreeBSD Ports 2020-06-12 19:05:12 0 [Note] InnoDB: Buffer pool(s) load completed at 200612 19:05:12

probably because of this

20200526:
  AFFECTS: users of databases/mariadb104-client, databases/mariadb104-server
  AUTHOR: brnrd@FreeBSD.org

  The ports now add sample configuration files to /usr/local/etc/mysql. You
  must merge your client configation with the conf.d/client.cnf and your
  server configuration with conf.d/server.cnf.

 

[Janusz]

jmos - ps and grep is the combo I used to see if the process was live. I'm positive it was down.

anlashok - thanks for spelling it out for me! I did investigate as per the note and I got it to work. I'm a bit puzzled though...

(1) - I removed the following bit from /usr/local/etc/mysql/my.cnf - that took care of the duplicate socket binding attempt

[client-server]
port = 3306
socket = /var/run/mysql/mysql.sock

(2) I checked /usr/local/etc/mysql/conf.d/client.cnf and /usr/local/etc/mysql/conf.d/server.cnf and in both cases the port and socket are not being set up from within those files

[mysqld]
user = mysql
# port = 3306 # set in /usr/local/etc/mysql/my.cnf
# socket = /var/run/mysql/mysql.sock # set in /usr/local/etc/mysql/my.cnf

So what's driving the port and socket?