MariaDB and ZM issues

Forum for questions and support relating to the 1.28.x releases only.
Locked
lpallard
Posts: 87
Joined: Fri Mar 05, 2010 10:59 pm

MariaDB and ZM issues

Post by lpallard »

We have a centos 6.6 VM running zm 1.28.0 from the zmrepo packages and installed using the instructions provided by the devs of this project. All seems to have gone well. Now we have noticed that the application (ZM) is not performing as intended. Event playback is severely screwed up (dropped frames, events overlapping, choppy playback, etc) and the most worrisome problem is the web interface locks up when we try to launch the logs window from the ZM web interface. The web interface immediately crawls to a halt (config/logs/monitor/etc windows wont load, the main ZM interface is unresponsive, live playback is lost, etc).

We searched for Apache errors, couldnt find any. dmesg also didnt yield anything out of the ordinary. Finally /var/log/messages exhibits traditional zoneminder related errors such as:

[code]Jan 11 13:24:17 centos-zm zmpkg[1398]: INF [Command: start]
Jan 11 13:24:17 centos-zm zmdc[1423]: INF [Server starting at 15/01/11 13:24:17]
Jan 11 13:24:20 centos-zm zmdc[1423]: INF ['zmc -m 1' starting at 15/01/11 13:24:20, pid = 1448]
Jan 11 13:24:20 centos-zm zmdc[1448]: INF ['zmc -m 1' started at 15/01/11 13:24:20]
Jan 11 13:24:20 centos-zm zmdc[1423]: INF ['zmf -m 1' starting at 15/01/11 13:24:20, pid = 1453]
Jan 11 13:24:20 centos-zm zmdc[1453]: INF ['zmf -m 1' started at 15/01/11 13:24:20]
Jan 11 13:24:20 centos-zm zmdc[1423]: INF ['zma -m 1' starting at 15/01/11 13:24:20, pid = 1458]
Jan 11 13:24:20 centos-zm zmdc[1458]: INF ['zma -m 1' started at 15/01/11 13:24:20]
Jan 11 13:24:20 centos-zm zmdc[1423]: INF ['zmfilter.pl' starting at 15/01/11 13:24:20, pid = 1463]
Jan 11 13:24:20 centos-zm zmdc[1463]: INF ['zmfilter.pl' started at 15/01/11 13:24:20]
Jan 11 13:24:21 centos-zm zmdc[1423]: INF ['zmaudit.pl -c' starting at 15/01/11 13:24:21, pid = 1472]
Jan 11 13:24:21 centos-zm zmdc[1472]: INF ['zmaudit.pl -c' started at 15/01/11 13:24:21]
Jan 11 13:24:21 centos-zm zma_m1[1458]: ERR [Got empty memory map file size 0, is the zmc process for this monitor running?]
Jan 11 13:24:21 centos-zm zmdc[1423]: ERR ['zma -m 1' exited abnormally, exit status 255]
Jan 11 13:24:21 centos-zm zmdc[1423]: INF [Starting pending process, zma -m 1]
Jan 11 13:24:21 centos-zm zmdc[1423]: INF ['zma -m 1' starting at 15/01/11 13:24:21, pid = 1478]
Jan 11 13:24:21 centos-zm zmf[1453]: WAR [Shared data not initialised by capture daemon, some query functions may not be available or produce invalid results]
Jan 11 13:24:21 centos-zm zmdc[1478]: INF ['zma -m 1' started at 15/01/11 13:24:21]
Jan 11 13:24:21 centos-zm zmdc[1423]: ERR ['zmc -m 1' exited abnormally, exit status 255]
Jan 11 13:24:21 centos-zm zmdc[1423]: INF [Starting pending process, zmc -m 1]
Jan 11 13:24:21 centos-zm zmdc[1423]: INF ['zmc -m 1' starting at 15/01/11 13:24:21, pid = 1479]
Jan 11 13:24:21 centos-zm zmdc[1479]: INF ['zmc -m 1' started at 15/01/11 13:24:21]
Jan 11 13:24:21 centos-zm zma_m1[1478]: ERR [Shared data not initialised by capture daemon]
Jan 11 13:24:21 centos-zm zmdc[1423]: ERR ['zma -m 1' exited abnormally, exit status 255]
Jan 11 13:24:21 centos-zm zmdc[1423]: INF ['zmwatch.pl' starting at 15/01/11 13:24:21, pid = 1482]
Jan 11 13:24:21 centos-zm zmdc[1482]: INF ['zmwatch.pl' started at 15/01/11 13:24:21]
Jan 11 13:24:21 centos-zm zmfilter[1463]: INF [Scanning for events]
Jan 11 13:24:21 centos-zm zmwatch[1482]: INF [Watchdog starting]
Jan 11 13:24:21 centos-zm zmwatch[1482]: INF [Watchdog pausing for 30 seconds]
Jan 11 13:24:22 centos-zm zmc_m1[1479]: INF [Starting Capture]
Jan 11 13:24:22 centos-zm zmc_m1[1479]: INF [Priming capture from ##############################################]
Jan 11 13:24:26 centos-zm zmdc[1423]: INF [Starting pending process, zma -m 1]
Jan 11 13:24:26 centos-zm zmdc[1423]: INF ['zma -m 1' starting at 15/01/11 13:24:26, pid = 1511]
Jan 11 13:24:26 centos-zm zmdc[1511]: INF ['zma -m 1' started at 15/01/11 13:24:26]
Jan 11 13:24:26 centos-zm zma_m1[1511]: INF [In mode 3/1, warming up]
Jan 11 13:24:26 centos-zm zmf[1453]: INF [Frame server socket open, awaiting images]
Jan 11 13:24:56 centos-zm zmc_m1[1479]: INF [Monitor-1: 1000 - Capturing at 29.41 fps]
Jan 11 13:24:59 centos-zm zma_m1[1511]: INF [Monitor-1: 1000 - Processing at 30.30 fps]
Jan 11 13:25:30 centos-zm zmc_m1[1479]: INF [Monitor-1: 2000 - Capturing at 29.41 fps]
Jan 11 13:25:32 centos-zm zma_m1[1511]: INF [Monitor-1: 2000 - Processing at 30.30 fps]
Jan 11 13:26:03 centos-zm zmc_m1[1479]: INF [Monitor-1: 3000 - Capturing at 30.30 fps]
Jan 11 13:26:05 centos-zm zma_m1[1511]: INF [Monitor-1: 3000 - Processing at 30.30 fps]
Jan 11 13:26:37 centos-zm zmc_m1[1479]: INF [Monitor-1: 4000 - Capturing at 29.41 fps][/code]

The ZM machine also has plenty of free RAM and disk space, and /dev/shm is around 15-20% full

[code][root@centos-zm ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/mapper/vg_centoszm-lv_root
ext4 8.3G 2.0G 5.9G 25% /
tmpfs tmpfs 1.5G 235M 1.2G 17% /dev/shm
/dev/vda1 ext4 477M 47M 405M 11% /boot
freenas:/mnt/zpool/storage/centos-zm-data
nfs 3.0T 1.4T 1.7T 46% /mnt/data
[/code]

[code][root@centos-zm ~]# free -m
total used free shared buffers cached
Mem: 2887 554 2332 234 11 309
-/+ buffers/cache: 233 2653
Swap: 1023 0 1023[/code]

Now this was from the zoneminder side..

On the remote SQL database server we also see a lot of serious issues. The MariaDB log is totally flooded by "aborted connection" error messages caused by the zoneminder VM.

[code][...]
150111 12:54:19 [Warning] Aborted connection 46101 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 12:54:19 [Warning] Aborted connection 46100 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:09:28 [Warning] Aborted connection 46533 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:10:51 [Warning] Aborted connection 47029 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:24:21 [Warning] Aborted connection 48863 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:24:21 [Warning] Aborted connection 48861 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:24:21 [Warning] Aborted connection 48862 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:24:21 [Warning] Aborted connection 48860 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:24:21 [Warning] Aborted connection 48872 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:24:21 [Warning] Aborted connection 48873 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:29:57 [Warning] Aborted connection 48914 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
150111 13:29:57 [Warning] Aborted connection 48915 to db: 'zm' user: 'zoneminder' host: 'centos-zm' (Unknown error)
[/code]

Since all other (9) VM's using the remote DM server are functioning perfectly well (now well over 18 months), we concluded that something in zoneminder must cause unnecessary connections to the DB server and must not close them (which would explain the "aborted connection" errors and the numerous connections in "TIME_WAIT" status on the DB server).

netstat on the DB server (most of these connections are from the ZM machine)
[code]36249.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36250.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36253.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36255.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36281.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36291.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36292.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36308.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36387.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36392.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36393.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36394.............................. : ESTABLISHED:00001 TIME_WAIT:00000
36395.............................. : ESTABLISHED:00001 TIME_WAIT:00000
42651.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44079.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44080.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44090.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44092.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44093.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44095.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44096.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44097.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44098.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44100.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44102.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44103.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44109.............................. : ESTABLISHED:00001 TIME_WAIT:00000
44121.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50321.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50424.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50527.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50528.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50529.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50587.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50588.............................. : ESTABLISHED:00001 TIME_WAIT:00000
50589.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54445.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54447.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54449.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54458.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54459.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54465.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54466.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54467.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54530.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54534.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54535.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54536.............................. : ESTABLISHED:00001 TIME_WAIT:00000
54537.............................. : ESTABLISHED:00001 TIME_WAIT:00000[/code]

Prior versions of zoneminder (series 1.26 & 1.27) did not yield to these issues so we believe something has changed in ZM in the way it handles connections to MySQL/MariaDB. If true, this could explain the lockups and performance issues we have with the ZM application.

Right now the only way to make ZM responsive is to restart the ZM service or Apache.

Anybody who has come up to the same type or a similar issue in the past please let me know what you did to fix this. In the meantime I will do more testing and isolate the faulty component.

Thanks
User avatar
iconnor
Posts: 2880
Joined: Fri Oct 29, 2010 1:43 am
Location: Toronto
Contact:

Re: MariaDB and ZM issues

Post by iconnor »

The main change in 1.28 was switching from the legacy mysql library to the PDO. Perhaps that is causing the issue.

That being said, with mysql I am not seeing this behaviour.

I find that using the db logging will being zm to a crawl. Disable it.
lpallard
Posts: 87
Joined: Fri Mar 05, 2010 10:59 pm

Re: MariaDB and ZM issues

Post by lpallard »

[quote]The main change in 1.28 was switching from the legacy mysql library to the PDO. Perhaps that is causing the issue. [/quote]

I am sure that I am not the only one to use MariaDB as the backend. Others see this kind of behavior?

YOu find that the db logging brings ZM to a crawl? How do you disable this?
lpallard
Posts: 87
Joined: Fri Mar 05, 2010 10:59 pm

Re: MariaDB and ZM issues

Post by lpallard »

Ok I managed to dig deeper into this issue:

1. The webinterface crawling to a halt was due to the Logs table being extremely big with more than 108 million entries (20.5GB). I truncated the table and now everything is fine. I also changed the log levels to "Warning" for all four log facilities. I am not sure what has changed in that regards since 1.26.x because I ran this release for more than 2 years and I never had database issues....

2. There is really something sketchy in ZM's code in regards to the "Aborted connections". Each time I launch the live monitor and close the window (using the "close" shortcut at the top right corner and NOT the window's X) I get 3 or 4 "aborted connection" entries in MariaDB's log. I strongly suspect that mysql_close() is not being used in Zoneminder's code or somehow doesnt work on my Centos 6.5 machine....?????

Playing with these log functions I came to realize that the "info" setting is more of a "debug" setting than anything else. Why would I want to know every 10 seconds that the framework is processing images at XX.XX fps?? I suppose this is responsible for the 108million+ entries in the Logs table!! I would recommend renaming "Debug" to "Debug+" and "Info" to "Debug" but that another story.

Im still searching for a clean way to stop the aborted connections causing not only Mariadb's logs to be polluted with thes error messages but also causes buffer problems with connections that MariaDB has to kill after wait_timeout has expired which is messy at best.

EDIT: found a lot of these entries in zmdc.log and I wonder if they wouldnt be related (in the sense that something is crashing and causing the aborted connections to mariadb):

01/11/2015 20:19:48.177525 zmdc[3696].ERR ['zmc -m 1' exited abnormally, exit status 255]
01/11/2015 20:19:48.522082 zmdc[3696].ERR ['zma -m 1' exited abnormally, exit status 255]
01/11/15 20:39:19.472617 zmdc[3947].ERR ['zmc -m 1' exited abnormally, exit status 255]
01/11/15 20:39:19.807556 zmdc[3947].ERR ['zma -m 1' exited abnormally, exit status 255]
01/11/15 20:39:19.894322 zmdc[3947].ERR ['zma -m 1' exited abnormally, exit status 255
User avatar
knight-of-ni
Posts: 2404
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: MariaDB and ZM issues

Post by knight-of-ni »

The instructions for zmrepo are designed around a local installation of mysql 5.1, not mariadb (and not a newer version of mysql).

Why are you using mariadb?
That combination of distro + database has not been tested so I'm not surprised you are running into issues.
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/
lpallard
Posts: 87
Joined: Fri Mar 05, 2010 10:59 pm

Re: MariaDB and ZM issues

Post by lpallard »

There are actually several issues:

Firstly:

In my experience ZM was running "decently" on a Centos 6.5 install. This time I had to change some stuff and reinstalled centos 6.5 (because ZM doesnt not support centos 7 yet) then upgraded the distro immediately to 6.6 via yum. I would have expected ZM to run fine on centos 6.6 (=6.5) which it doesnt. Numerous performance and crash issues with:

[code]zmf 5383 ERR Only read 127960 bytes of 534834 /builddir/build/BUILD/ZoneMinder-1.28.0/src/zmf.cpp 267
zma_m1 18460 ERR Incomplete frame write: 128000 of 534874 bytes written /builddir/build/BUILD/ZoneMinder-1.28.0/src/zm_event.cpp
WAR Last image read from shared memory 4 seconds ago, zma may have gone away
WAR Buffer overrun at index 6, image 4306266, slow down capture, speed up analysis or increase ring buffer size
WAR Socket closed at remote end
...[/code]

I suspect on a centos 6.5 UN-UPDATED to 6.6 ZM should work but I am reluctant to run an out-of-date distro for so long... After all 6.5 was released on Dec 2013.... Centos 7 is already 6 months old now....

Secondly:

I am using MariaDB instead of MySQL because I use a dedicated database server (VM) (running Centos 7) that several other network services are using as well. MariaDB being advertised as a drop-in replacement for MySQL I thought I could configure ZM to use it transperently, apparently I cant.

Why I use Mariadb? Because this is what ships with Centos 7 now and probably will ship with future versions of Centos.

The combination of Centos 6.6 - ZM 1.28 - Centos 7 with MariaDB is not great but except installing Mysql on the local ZM machine this is the only way I had. Centos 8 will probably be released before ZM supports centos 7.

Out of 10+ frameworks and applications that initially were connecting to Mysql and now to mariadb, ZM is the only one having issues like that. Thats why I posted here because I thought it deserved some attention. After all, ZM wil have to support mariadb some time soon if it wants to run on centos 7+.
User avatar
knight-of-ni
Posts: 2404
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: MariaDB and ZM issues

Post by knight-of-ni »

This isn't a question of whether or not zoneminder works with Mariadb. It does.
This is a question of whether or not you want a stable, working system.

The zoneminder rpm's for CentOS 6 in zmrepo are built around a specific set of dependencies. One of them being mysql 5.1. You can't implement a major upgrade to that and then expect the system as a whole to have the same level of stability.
If you do this, you become the kid who upgraded his car with after market components and then experiences troubles because of that (had to learn the hard way myself). Is the manufacturer responsible for this? No, of course not. Can it be a fun learning experience? It sure can, but it really sucks when you are depending on that vehicle a.k.a. production environment.

You've just hit on why "stable" and "lastest and greatest" are mutually exclusive.

Before we go any further, ask yourself what you are trying to accomplish. Are you looking to achieve something that is stable and runs well in a production environment, or are you looking for something with the latest and greatest software?

Either scenario is acceptable, but that depends on your environment.

I'm running multiple instances of CentOS 6.6 with zoneminder, both real and virtual, and they all run well right out of the box. The difference between me and you is that you have tainted your system with third party rpm's. The guys at CentOS can sing a song much better than I can as to why you should not do this. CentOS 6.x is supported until 2020 and that includes bug fixes. Hence there is no reason to upgrade core components of CentOS for zoneminder.

You can choose to accept this or not, but it really is the root cause of your problems. Since we have had numerous discussions in the forum, I feel I am getting to know you and what your tendencies are. You like to tinker with stuff and tend to deviate from the instructions. That's not necessarily a bad thing, but it can get you into trouble.

On the other hand, if you really want the newest software packages then you ought to stay away from CentOS. CentOS, by design, will never have the latest and greatest, even after a new version has been released. You may want to look into Fedora instead.

TL;DR
If you want something that works well, then my advice is to create a CentOS 6 environment without replacing any of its core packages. The rpm's in zmrepo have been tested and are known to work only with the rpm's that CentOS ships with.

If you want the latest and greatest packages then you might want to consider Fedora. The Fedora rpm's in zmrepo have been tested and are known to work with the rpm's that Fedora ships with.
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/
lpallard
Posts: 87
Joined: Fri Mar 05, 2010 10:59 pm

Re: MariaDB and ZM issues

Post by lpallard »

Hey knnniggett

I fully understand your POV. You are right when you say that I like to fiddle with installs but normally when I reach a point where everything runs fine and stable, I leave it alone. This is not the case with ZM because I could never get it to work well with my current hardware which I find totally absurd considering the horsepower I have... I am now suspecting either hardware issues or incompatible camera..

Anyways I think this thread was ill-born because I mixed two fundamental issues: the OS on which I run ZM and the DB version it connects to.. If you agree with me that connecting to a remote DB instead of a local one is fine then lets continue on the foundation that there are two different topics at hand.

OS ZM runs into:

I fully agree with you when you say zmrepo (hence your packages of ZM) only runs on centos 6.X. This is why I installed 6.5 and then updated to 6.6... Later on who knows maybe the centos folks will push updates bringing 6.6 to 6.7, etc.. After all like you said, Centos 6.X is supported until 2020.. That shouldnt stop ZM from working. Mine never really worked well at first and I would like to get to the bottom of that with your (or other folk's) help.

The DB it connects to:

I think you may have misunderstood me on this one. I didnt upgrade mysql to mariadb on the centos VM ZM is installed on, but I was trying to have ZM work with a *remote* centos 7 VM running mariadb. Now seeing that ZM currently doesnt support > mysql 5.1, I abandonned the remote connection strategy and setup mysql on the local centos 6.6 machine that ZM is installed on. So far so good, no more "abandoned connections" errors, etc.... If you guys decide to ever support mariadb in the future, I will simply undo the local DB server and reconfig to use the remote mariadb server.

"mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1"

To answer your question about wether I want stability or bleeding edge features, I want stability. This topic arose because I was led to believe that mariadb was as a complete transparent drop-in replacement for mysql which clearly is not, at least from the point of view of zoneminder's code!

I hope this will help clear up the fog..

Now you may consider my setup and fully compliant with the Wiki instructions and the requirements of ZM 1.28.0 (the latest packages in zmrepo).
Locked