Can't connect to local MySQL server through socket '/tmp/mysql.sock'

[CyberTiVo]

New install FreeBSD 13.1, ZM 1.36.12 & MySQL 5.7; ZM seems to be working correctly, the cameras display images; I have manually connected to MySQL with -u zmuser -p dozens of times without fail; I have no idea how to fix this and it seems I'm the only person in the world having this problem.

However, I'm getting hundreds of errors connecting to MySQL:
zmwatch[2303].ERR [ZoneMinder::Database:114] [Error reconnecting to db: errstr:Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) error val:]

DBD::mysql::db ping failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) at /usr/local/bin/zmwatch.pl line 88.
DBI connect('database=zm;host=localhost','zmuser',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at /usr/local/lib/perl5/site_perl/ZoneMinder/Database.pm line 110.

zmstats[2314].ERR [ZoneMinder::Database:114] [Error reconnecting to db: errstr:Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) error val:]
06/21/22 18:58:07.319295 zmstats[2314].INF [main:47] [Reconnecting to db]

zmfilter_1[4059]: ERR [Error reconnecting to db: errstr:Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) error val:]

zmc_m3[4218]: ERR [zmc_m3] [Can't run query INSERT INTO `Logs` ( `TimeKey`, `Component`, `ServerId`, `Pid`, `Level`, `Code`, `Message`, `File`, `Line` ) VALUES ( 1655859659.881583, 'zmc_m3', 0, 100680, 0, 'INF', 'Driveway: image_count:7335 - Closing event 234, shutting down', 'zm_monitor.cpp', 3121 ): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)]

zmc_m3[4218]: ERR [zmc_m3] [Can't run query INSERT INTO Monitor_Status (MonitorId,Status,CaptureFPS,AnalysisFPS) VALUES (3, 'Running',0,0) ON DUPLICATE KEY UPDATE Status='Running',CaptureFPS=0,AnalysisFPS=0: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)]

[Magic919]

Is the DB crashing? Looks like it's going away.

[CyberTiVo]

It doesn't look like it; 'mysql-server status' shows the PID remains the same after the server has been up for hours

No surprise, the PurgeWhenFull filter doesn't work

So, the cameras show video and create events to fill up the hard drive; Makes sense the behind the scenes stuff doesn't work

[Magic919]

Interesting. How many connections?

[CyberTiVo]

You mean cameras? 3

[Magic919]

Database.

[CyberTiVo]

'show status' shows 780
show status where variable_name = 'threads_connected'; shows 6
+-----+--------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+--------+-----------+------+---------+------+----------+------------------+
| 7 | zmuser | localhost | zm | Sleep | 9 | | NULL |
| 8 | zmuser | localhost | zm | Sleep | 9 | | NULL |
| 11 | zmuser | localhost | zm | Sleep | 7 | | NULL |
| 12 | zmuser | localhost | zm | Sleep | 59 | | NULL |
| 121 | zmuser | localhost | zm | Sleep | 29 | | NULL |
| 797 | root | localhost | NULL | Query | 0 | starting | show processlist |
+-----+--------+-----------+------+---------+------+----------+------------------+
6 rows in set (0.00 sec)

[Magic919]

I'd just check the ZM UI, tbh. Might not be that though, as it doesn't match the error.

I'm presuming that is your socket location. Can you explicitly connect using socket that when you run the command to test.

[CyberTiVo]

Don't I connect to the socket when I run 'mysql -u' ?

[Magic919]

No idea.

[SkippyDo]

I recently went through a bunch of headaches tracing a DB connection issue via API. I won't elaborate my solution as I doubt it's applicable in your case, but... I am a bit confused as to how all the socket stuff works and I could see plenty of ways in which this could jam up the works for others. Just take this as a muse...

As I'm running Debian things should be similar for you.

In the file /etc/zm/conf.d/01-system-paths.conf I have:

ZM_PATH_SOCKS=/run/zm

That directory and PID and SOCK files are owned by www-data.

There's also socket definitions in the files /etc/mysql/mariadb.cnf, AND /etc/mysql/mariadb.conf.d/50-server.cnf, I have the following in both:

socket = /run/mysqld/mysqld.sock (directory and file are owned by 'mysql')

I suspect that "my.cnf" overrides?

I'll note that I struggle to get the naming correct. I have used "mysql" when "myslqd" is the correct directory and file name. (OR whichever- consistency, though, matters!)