Making ZoneMinder crash safer

[mastertheknife]

Hi,

I'm using ZM 1.24.2, i have one camera that is constantly recording.
At the moment i have no UPS and everytime there was a power outage the computer automatically restarted and 90% of the time ZM won't function properly because of corrupt database tables (Usually the Frames table)

MyISAM is the tables type used by the ZM database. MyISAM is very fast, but old and has a very low chance of surviving a crash during a write operation, and has no automatic crash recovery.
The tables with most write activity are Stats, Frames and Events.
The other tables are modified usually only when configuration changes (by user).
A general rule is to use MyISAM for tables that are 95% read and 5% write, all tables except Stats, Frames and Events meet this description.
InnoDB is a newer table type, ACID compliant, supports transitions, has an automatic crash recovery and most importantly, has a much higher chance of surviving a crash during a write operation.

I changed my Stats,Frames and Events tables to use InnoDB.
I did this by shutting down ZM, dumping the database into a SQL file called backup.
Did another database dump, this time to a SQL file called innodb. I then deleted all tables in the database and opened the innodb.sql file and changed the TYPE=MyISAM to TYPE=InnoDB in the CREATE TABLE statement for the tables Stats, Frames and Events.
Then i loaded this SQL file and fired up ZM and everything works great, had few power outages since but ZM always recovered like nothing happened.

InnoDB is by default compiled into MySQL in most distros, and InnoDB automatic crash recovery is enabled by default, but it won't hurt to make sure.

17th Sept 2010 EDIT:
There is an easier way to do it if you have phpmyadmin:
1) Backup your database by exporting it to a sql file and downloading it, or by making a copy of that database.
2) Switch the Events, Frames and Stats tables engine from MyISAM to InnoDB (from phpmyadmin of course), see the picture at this link: http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM

mastertheknife.

[JBL]

even tho i got my system running again i have noticed now it holds far less
events than it did before the problem

i am a noob to both zm and linux i know a little

what you are talking about sounds just like my problem
it seems that my hd is getting smaller the total number of events on hd is far less than before crashes using same purge filter

if i could get a more detailed discrption of how to do this fix
with the path to where thees files and folders are
what files or folders i create or delete
and a clear path to where i create or delete said files or folders

i have killed my ZM 3 times before trying to tweek a few things
had to format and reinstall not fun i loose all my little tweeks that i got to fly
each time the process gets a little longer

thankyou for your help greatly appreciated

jeff

[pdanilchik]

I have a fairly powerfully equipped ZM 1.24.2 box set up running on Ubuntu 10.04 LTS Server with a UPS and 4GB of ram. The server seems to come to a complete stop about once per week and I find evidence of mysql errors in the logs although thorough database checks find nothing wrong.

After looking at a lot of logs I have come to the conclusion that mysql is the weak link in the ZoneMinder setup and the likely culprit. Is there any hope of making mysql not able to bring down a machine. At this point I'd be satisfied with a way to make mysql simply fail but not take the server completely down.

Would the suggested conversion to InnoDB possibly help me? If so I'd also appreciate some clear directions.

Thanks for any suggestions or help with my problem.

[mastertheknife]

I have a fairly powerfully equipped ZM 1.24.2 box set up running on Ubuntu 10.04 LTS Server with a UPS and 4GB of ram. The server seems to come to a complete stop about once per week and I find evidence of mysql errors in the logs although thorough database checks find nothing wrong.

After looking at a lot of logs I have come to the conclusion that mysql is the weak link in the ZoneMinder setup and the likely culprit. Is there any hope of making mysql not able to bring down a machine. At this point I'd be satisfied with a way to make mysql simply fail but not take the server completely down.

Would the suggested conversion to InnoDB possibly help me? If so I'd also appreciate some clear directions.

Thanks for any suggestions or help with my problem.

Definitely.
If after a crash you see many events with duration of 0.0 seconds, you are suffering from the same problem i did. Power failures that cause corrupt tables.
I switched my Frames, Stats and Events tables to InnoDB and ever since, i never had to repair a table or mess with MySQL. InnoDB is a little slower, but a lot safer during write operations and can recover automaticly from crashes, making the whole crash invisible to ZM.

There is an easier way to do it if you have phpmyadmin:
1) Backup your database by exporting it to a sql file and downloading it, or by making a copy of that database.
2) Switch the Events, Frames and Stats tables engine from MyISAM to InnoDB (from phpmyadmin)

I hope this helps,
mastertheknife.

[pdanilchik]

Thanks for that. I will likely switch over to InnoDB but I am not sure that is the problem that I'm having.

First off I have a UPS so except for my initial testing, I haven't had the server shut down abruptly. The UPS software does an orderly shutdown per the installed software (apcupsd).

Second, when I checked the database, using both command line methods, one that requires the db to be stopped, the other not... everything checks out fine (used: myisamchk and mysqlcheck). Is it possible that when I get phpmyadmin installed and running that it will show a different result?

I do have suspicions that it happens when the sun flickers through the trees and causes a lot of rapid events to be stored. The times that there was failure it was during such rapid fire events being generated. Maybe there is a good way to limit the even generation rate??.

My guess is that the InnoDB change won't fix my problem as the server simply locks up completely, not just ZM. I cannot SSH nor get the web page to come up and the system logs show NO activity during this time. I do appreciate the suggestion and will be sure to let you know if it does help.

Thank you kindly, -P

[pdanilchik]

One additional question: What if I wanted to create a new database for ZM from scratch? What would be the process to do something like that and hopefully create it as a InnoDB from the get-go?

There is nothing precious in the existing DB so I thought that it might be useful for others to know, as well, how one goes about wiping the old one and replacing it with a freshly created DB.

Thanks in advance, -P

[pdanilchik]

By the instruction to "Switch the Events, Frames and Stats tables engine..." from phpmyadmin, I assume that it is necessary to do a Query:

ALTER TABLE `Events` ENGINE=InnoDB;
ALTER TABLE `Frames` ENGINE=InnoDB;
ALTER TABLE `Stats` ENGINE=InnoDB;

Is that so?... because I looked forever for a way to do it via the web-gui and there didn't seem to be a way. To allow modifications, logging into phpmyadmin as 'root' was required, btw. I mention that to benefit others like me who are groping around in the dark.

Now I would ask why only these three tables? Why not them all??

[mastertheknife]

By the instruction to "Switch the Events, Frames and Stats tables engine..." from phpmyadmin, I assume that it is necessary to do a Query:

ALTER TABLE `Events` ENGINE=InnoDB;
ALTER TABLE `Frames` ENGINE=InnoDB;
ALTER TABLE `Stats` ENGINE=InnoDB;

Is that so?... because I looked forever for a way to do it via the web-gui and there didn't seem to be a way. To allow modifications, logging into phpmyadmin as 'root' was required, btw. I mention that to benefit others like me who are groping around in the dark.

Now I would ask why only these three tables? Why not them all??

I recall seeing an option to change the table type, but it was a long time ago, maybe its not really there but it doesn't really matter, because it probably executes the same query.
Why only those tables? you can change them all, i just didn't see a point to change them all, because MyISAM is faster and i only needed the advantages of InnoDB (which come at cost, speed) for those specific tables, because those are being written to constantly, while the other tables are only being written to during configuration change and its less likely to have a power failure doing a configuration change.

EDIT: The link below has a picture with how to change the table type to InnoDB, but i haven't tested this. Also, it includes some information about MyISAM, InnoDB and the differences.
http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM

mastertheknife

[mastertheknife]

One additional question: What if I wanted to create a new database for ZM from scratch? What would be the process to do something like that and hopefully create it as a InnoDB from the get-go?

There is nothing precious in the existing DB so I thought that it might be useful for others to know, as well, how one goes about wiping the old one and replacing it with a freshly created DB.

Thanks in advance, -P

Here are instructions on how to populate a newly created database for ZM: http://www.zoneminder.com/wiki/index.ph ... rom_Source

[Flav]

You can add on a startup script this commande :

Code: Select all

service zoneminder stop
mysqlcheck --all-databases
service zoneminder start

[Alan87i]

If possible could someone post a script as mentioned above that works.
Also Would it not be possible to setup a script to auto check the data base and fix errors, then setup a filter in ZM to execute this script say every week , or a cron job.

[Flav]

If possible could someone post a script as mentioned above that works.
Also Would it not be possible to setup a script to auto check the data base and fix errors, then setup a filter in ZM to execute this script say every week , or a cron job.

Put this code in file /root/safedatabase

Code: Select all

service zoneminder stop
mysqlcheck --all-databases
service zoneminder start 

enter a cronjob in /etc/contab

Code: Select all

0 8 *** root /root/safedatabase