Rpi 4B-8G, 2T SSD, 10 IP cams, 203,000 events-Offline

[pdt5774]

Installed Ubuntu 22.04 using Rpi bootloader on 4B-8G, booting from 125G SSD, running 10 Lorex
IP cameras at 1280x720@2FPS. Current ZM v1.36.33. This was an experimental install around Oct
2022 and currently still running with few to no issues.

Beginning Dec 2022, installed Ubuntu 22.04 using same Rpi bootloader on a 2nd 4B-8G, this time
booting from a 2T SSD, with the same 10 Lorex IP cameras at 1280x720@2FPS. Again, this install
ran with few to no issues since Dec 2, 2022 to Oct 2023 accumulating 203,000 events. PurgeWhenFull
set at 70%, 1.1T Used,649G Available. This install created to see if one year worth of events could
be realized. I almost made it.

What went wrong? On 2nd Rpi booted from 2T SSD, performed usual update only this time, received an error message stating issue with libevent fail to update, refer to libevent docs. Docs gave little resolve info, so did usual apt removed/install. Ended with "libevent-2.1-7/jammy,now 2.1.12-stable-1build3 arm64 [installed]".

Upon reboot and attempt to log into Zoneminder, this message is displayed,

"ZoneMinder Error

Unable to connect to ZM db using dsn mysql:host=localhost;dbname=zm;charset=utf8

SQLSTATE[HY000] [2002] No such file or directory

ZoneMinder will retry connection in 15 seconds."

I'm not sure whether the libvent remove/install played a role however, the libvent is same
version which resides on the other Rpi 4B-8G 125G SSD. The 203,000 events may be an
issue however, I do not believe the PurgeWhenFull set at 70% has been reached causing a large
dump of old events. Quite possibly ram may be an issue, however htop never indicated excessive
use of ram. I reviewed log files but never seen anything that talked to me as definitive clue
what has occurred. Logs can be quite cryptic.

At any rate, I present this situation to the forum with hopes it can be revived. I'm tickled the
little Raspberry Pi has preformed so well dealing with all the event files on this system. I would
like to resume its operation if at all possible. If not, I have a 1T SSD coming that I will
configure in its place while retaining events that exist on the 2T SSD.

Any thoughts?
Thanks for reading...

[iconnor]

sounds like mysql isn't running. Try systemctl status mysql to maybe see why. Try sudo systemctl start mysql to see if you can start it. Check in /var/log/mysql/error.log

[pdt5774]

Hello Connor,
Thanks for your review. Ran your suggested commands. Top shows no mysql processes running, error.log
shows little to no activity, journalctl -u mysql.service a bit more. Just plain journalctl produces
massive errors throughout. Amazed it functioned at all.

Further down, I've included your suggested commands against the
125G SSD rpi4-zm2 system which is same install, just smaller SSD for comparison purpose.

pdt5774@rpi4zm-2tb:~$ sudo systemctl status mysql
[sudo] password for pdt5774:
mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql; generated)
Active: active (exited) since Sun 2023-12-03 20:09:11 PST; 18h ago
Docs: man:systemd-sysv-generator(8)
Process: 735 ExecStart=/etc/init.d/mysql start (code=exited, status=0/SUCCESS)
CPU: 8ms
pdt5774@rpi4zm-2tb:~$

pdt5774@rpi4zm-2tb:~$ sudo systemctl start mysql
pdt5774@rpi4zm-2tb:~$

root@rpi4zm-2tb:/# cd /var/log/mysql
root@rpi4zm-2tb:/var/log/mysql# ls -la
total 36
drwxr-x--- 2 mysql adm 4096 Dec 4 13:51 .
drwxrwxr-x 12 root syslog 4096 Dec 4 13:51 ..
-rw-r----- 1 mysql adm 0 Dec 4 13:51 error.log
-rw-r----- 1 mysql adm 20 Dec 3 00:00 error.log.1.gz
-rw-r----- 1 mysql adm 20 Dec 2 00:00 error.log.2.gz
-rw-r----- 1 mysql adm 20 Dec 1 00:00 error.log.3.gz
-rw-r----- 1 mysql adm 20 Nov 30 00:00 error.log.4.gz
-rw-r----- 1 mysql adm 20 Nov 29 00:00 error.log.5.gz
-rw-r----- 1 mysql adm 20 Nov 28 09:14 error.log.6.gz
-rw-r----- 1 mysql adm 20 Nov 20 00:16 error.log.7.gz
root@rpi4zm-2tb:/var/log/mysql#

pdt5774@rpi4zm-2tb:~$ sudo journalctl -u mysql.service

Jun 23 10:30:38 rpi4zm-2tb systemd[1]: mysql.service: Consumed 1h 15min 57.174s CPU time.
-- Boot 76028bfd98494346bd95aeebed2e0296 --
Jul 02 03:45:04 rpi4zm-2tb systemd[1]: mysql.service: Consumed 4h 1min 43.468s CPU time.
-- Boot ebe6c050899046e6a70269337d04a40d --
Jul 04 12:30:24 rpi4zm-2tb systemd[1]: mysql.service: Consumed 1h 42min 29.565s CPU time.
-- Boot e5168489c7764b7985bfb6098ee6a663 --
Sep 11 19:14:14 rpi4zm-2tb systemd[1]: mysql.service: Consumed 34min 5.837s CPU time.
-- Boot 416fbf6e65be4def8c72e8f4202d2f4b --
Oct 24 21:55:16 rpi4zm-2tb systemd[1]: mysql.service: Consumed 10h 47min 54.627s CPU time.


125G SSD system.....

pdt5774@rpi4-zm2:~$ sudo systemctl status mysql
[sudo] password for pdt5774:
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2023-11-20 10:44:58 PST; 2 weeks 0 days ago
Main PID: 744 (mysqld)
Status: "Server is operational"
Tasks: 57 (limit: 9243)
Memory: 953.4M
CPU: 6h 34min 56.509s
CGroup: /system.slice/mysql.service
└─744 /usr/sbin/mysqld

Nov 20 10:44:54 rpi4-zm2 systemd[1]: Starting MySQL Community Server...
Nov 20 10:44:58 rpi4-zm2 systemd[1]: Started MySQL Community Server.
pdt5774@rpi4-zm2:~$

Top shows abundance of activity....

top - 14:36:41 up 13 days, 9:55, 1 user, load average: 1.41, 1.13, 1.05
Tasks: 162 total, 1 running, 159 sleeping, 2 stopped, 0 zombie
%Cpu(s): 14.9 us, 3.2 sy, 0.0 ni, 81.1 id, 0.1 wa, 0.0 hi, 0.7 si, 0.0 st
MiB Mem : 7807.0 total, 157.4 free, 1908.4 used, 5741.2 buff/cache
MiB Swap: 0.0 total, 0.0 free, 0.0 used. 5539.8 avail Mem

[pdt5774]

I have discovered that rpi4zm-2tb system DOES NOT have '/var/run/mysqld/mysqld.sock' whereas
rpi4-zm2 system DOES have '/var/run/mysqld/mysqld.sock' and is still running fine. Fingers crossed,
knocking on wood. Currently, I'm researching logs files and comparing notes between the two systems
in hopes of discovering what specifically caused the rpi4zm-2tb system to go down so suddenly.
Lots of interesting data to sift through between the two systems. I'm compiling many useful Linux
file system search tools and commands. I'll post any significant ah-ha moments I find.

My 1T SSD drive arrived and is formatted and ready to go just in case. *smile*

[iconnor]

mysql is very easy to corrupt if a system is powered off unexpectedly. Look in /var/log/mysql/error.log it will tell you if it is crashing.

Otherwise, find out why mysql isn't running. Look at systemctl status mysql
Try running sudo systemctl start mysql

[pdt5774]

In /etc/mysql/mysql.conf.d/mysqld.cnf it states...

# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log

In /var/log/mysql/error.log this is apparently so given these files are very small...

root@rpi4zm-2tb:/var/log/mysql# ls -la
total 36
drwxr-x--- 2 mysql adm 4096 Dec 7 00:00 .
drwxrwxr-x 12 root syslog 4096 Dec 7 13:49 ..
-rw-r----- 1 mysql adm 0 Dec 7 00:00 error.log
-rw-r----- 1 mysql adm 20 Dec 6 00:00 error.log.1.gz
-rw-r----- 1 mysql adm 20 Dec 5 00:00 error.log.2.gz
-rw-r----- 1 mysql adm 20 Dec 4 13:51 error.log.3.gz
-rw-r----- 1 mysql adm 20 Dec 3 00:00 error.log.4.gz
-rw-r----- 1 mysql adm 20 Dec 2 00:00 error.log.5.gz
-rw-r----- 1 mysql adm 20 Dec 1 00:00 error.log.6.gz
-rw-r----- 1 mysql adm 20 Nov 30 00:00 error.log.7.gz

As above shows, little to nothing that has been logged to /var/log/mysql/error.log that's beneficial.

pdt5774@rpi4zm-2tb:~$ sudo systemctl start mysql
[sudo] password for pdt5774:
This command gives No response

pdt5774@rpi4zm-2tb:~$ sudo systemctl status mysql
mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql; generated)
Active: active (exited) since Fri 2023-12-08 00:44:05 PST; 2 days ago
Docs: man:systemd-sysv-generator(8)
CPU: 8ms

This command lists mysql as (exited), no indication of it running.


This is a strange Mysql installation I'm discovering. After moving
/var/lib/mysql to /var/lib/mysql.bkp, I was going to attempt to perform a
mysql-server reinstall, but was greeted with "file or directory
not found" error. Using "sudo apt list --installed" command, this is the only information about mysql that is listed...

mysql-client-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-client-core-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-common/jammy,now 5.8+1.0.8 all [installed,automatic]

No mysql-server listed... how has the database even been running, I'm wondering.

Not sure what to think about this. I'm getting a sinking feeling that my rpi4zm-2tb
installation is coming to a dreaded end. *sigh*

I've also been noting the login splash stating:

Welcome to Ubuntu 22.10 (GNU/Linux 5.19.0-1022-raspi aarch64)

Your Ubuntu release is not supported anymore.
For upgrade information, please visit:
http://www.ubuntu.com/releaseendoflife

I don't recall specifically upgrading from 22.04 to 22.10. I also found "unattended upgrades" enabled and quite active.

My other 125G SSD Rpi system is still on 22.04 and not experiencing issues.. yet.

Welcome to Ubuntu 22.04.3 LTS (GNU/Linux 5.15.0-1043-raspi aarch64)

Unattended upgrades has been disabled and removed from both systems.

Back to more digging...

[pdt5774]

I just did the command "sudo apt list --installed" on my pdt5774@rpi4-zm2 system which reveals something
quite interesting....

mysql-client-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-client-core-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-common/jammy,now 5.8+1.0.8 all [installed,automatic]
mysql-server-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-server-core-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-server/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 all [installed]


Back to digging again...

[pdt5774]

Gained a little ground with today's discoveries. When I discovered these three
lines not listed from comparing the two system....

mysql-server-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-server-core-8.0/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 arm64 [installed,automatic]
mysql-server/jammy-updates,jammy-security,now 8.0.35-0ubuntu0.22.04.1 all [installed]

... it was apparent that some process had simply deleted a major function
of Mysql... namely the server! I have a massive chunk of journalctl listing of the
system activities to sift through in hopes of pinpointing the specific cause. For now, mysql server is operational once again...

pdt5774@rpi4zm-2tb:~$ sudo systemctl status mysql
mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Sun 2023-12-10 22:48:48 PST; 20min ago
Process: 724 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited,>
Main PID: 797 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 9237)
Memory: 423.4M
CPU: 3.630s
CGroup: /system.slice/mysql.service
└─797 /usr/sbin/mysqld

Next phase will be troubleshooting Zoneminder

pdt5774@rpi4zm-2tb:~$ sudo systemctl status zoneminder.service
× zoneminder.service - ZoneMinder CCTV recording and surveillance system
Loaded: loaded (/lib/systemd/system/zoneminder.service; enabled; preset: enabled)
Active: failed (Result: exit-code) since Sun 2023-12-10 22:48:48 PST; 25min ago
Process: 849 ExecStart=/usr/bin/zmpkg.pl start (code=exited, status=255/EXCEPTION)
CPU: 277ms

Dec 10 22:48:48 rpi4zm-2tb zmpkg[849]: ERR [Error reconnecting to db: errstr:Access denied for user 'zmuser'@'localhost' (using password: YES) error val:]
Dec 10 22:48:48 rpi4zm-2tb systemd[1]: zoneminder.service: Control process exited, code=exited, status=255/EXCEPTION
Dec 10 22:48:48 rpi4zm-2tb systemd[1]: zoneminder.service: Failed with result 'exit-code'.
Dec 10 22:48:48 rpi4zm-2tb systemd[1]: Failed to start ZoneMinder CCTV recording and surveillance system.

Tomorrow is another day... my mind and eyes are worn out for now.. *smile*

[pdt5774]

Due to the holidays, I decided to give this saga a rest after discovering that when I restarted mysql, the /var/lib/mysql directory disappeared yet again. *sigh* I shut it all down and let it sit. After the holidays, I came back to specifically determine why the var/lib/mysql directory was disappearing. I do not yet know why it originally went away, but using more error chasing, discovered creating a backup directory and moving zm files was a huge mistake! Scattered files terribly! Errors were revealing duplication of files and not where others belonged. Thankfully, I have a smaller sister zoneminder system running where I could review where files belonged originally. So far, mysql now runs as expected and /var/lib/mysql directory does not disappear.

Where am I now? I'm still dealing with these errors:
"Unable to connect to ZM db using dsn mysql:host=localhost;dbname=zm;charset=utf8"
"SQLSTATE[HY000] [1045] Access denied for user 'zmuser'@'localhost' (using password: YES)"

Next, I need to reattach my current /var/lib/mysql/zm directory to mysql database as it does not appear when doing a "mysql show databases;" command. A successful database reattachment may actually resolve the above login errors, guessing here tho. I've reviewed a dozen or so research results that's suggesting reattaching current /var/lib/mysql/zm directory files in mysql may be doable after creating a new DB then once created, deleting new DB and replace it with my current /var/lib/mysql/zm files. To quote Jimmy's World, " What could possible go wrong?" *grin*

[pdt5774]

Been a rough go here the last several weeks. After the holidays, my wife was scheduled for major surgery which has taken center stage. I've not touched rpi4zm-2tb server since last posting. I'm still going to attempt to recover the server by reattaching /var/lib/mysql/zm files. With all that's been going on, I did not want to attempt to recover the server until I could pay close attention to the task at hand. What I want to do is get my series of commands together that I hope will get the job done, and post them here for peer review.

A little discovery during the frigid weeks since the first of January. When I purchased my Raspberry Pi 4Bs with 8g, I opted for the full aluminum cases that serve as a heat sink. These cases do actually remove quite a bit of heat during operation. My rpi4zm-2tb server has one of these aluminum cases. HOWEVER!! I purchased the same aluminum case for my new Rpi 5. One cold evening I was walking by my stack of Pis and noticed my Pi 5 was nearly off the shelf from earlier re-arrangement. When I touched the aluminum case, I got a large snap and electrical shock from static electricity. The static discharge immediately shut down the Pi5. I was taken aback somewhat with this event. It then hit me that static electricity discharge is quite possibly the event that created the sequence of events that took my rpi4zm-2tb server off line. Carpeting and aluminum cased Raspberry Pis is not a good combination. Something else to think about... thought I'd share that little tid-bit of shocking Pi experience. *smile*

[mikb]

When I touched the aluminum case, I got a large snap and electrical shock from static electricity. The static discharge immediately shut down the Pi5.

I would expect that the power supply you are using is a "floating output" (not referenced back to mains earth) and that means the Pi, and any metal bits (e.g. USB/HDMI/Heatsink) that are connected to its local "ground" (0V) are not truly grounded.

One solution would be to earth the case/aluminium/ground. Much like an anti-static strap for personal use, with a 1 Meg resistor to to real ground. That would help slowly bleed charge off the case.

[dougmccrary]

One solution would be to earth the case/aluminium/ground. Much like an anti-static strap for personal use, with a 1 Meg resistor to to real ground. That would help slowly bleed charge off the case.

I was thinking along the same lines, but from the description, I suspect that the static arced across the power rails, so the high resistance ground wouldn't help. Maybe even an actual ground wouldn't...