Need help regarding the ZM database

[millerajm]

Hello, TLDR is in bold.

I've had some strange issues take place after moving (changed timezones...) I think at this point, I have all timezone settings configured properly, however for a period of time, there was an issue where the database was saving events, but couldn't access them properly.

Long story short, the drive filled up because zmfilter couldn't find the events correctly to delete them.

I am wanting to delete ALL events, so I removed all of the event folders in zoneminder/events

Then I thought zmaudit would simply remove them, but it was unable to find and remove them from the db, so I tried to truncate all event tables manually in mysql (Events, Events-Day, Events-Week, etc.)

The number of events still showed up on the zoneminder web interface, though, so then I tried DROP TABLE Events, thinking that might fully clear it out.

Now of course I get errors that the table zm.Events doesn't exist, I was hoping it would recreate it when it first tried to make an event. Can someone help me out with the Events.frm file from V 1.34.3, so that I can recreate an empty Events table? Or is there a better way to regenerate the database? I have my Monitor and Zone tables backed up, which is all I really care about I think.

The web interface page still shows a large number of events for each camera and a huge file size (much larger than the physical space I have). Is there something else I need to do to reset those?

Thanks for any help, this has taken a couple days of my time trying to figure things out, and any help is appreciated.

[bbunge]

Drop the entire zm database
>mysql -u root -p
Enter password: ********
mysql> DROP DATABASE zm;
\q

then,
Create Zoneminder database in MySQL (Note: this also creates the default Zoneminder user and permissions in MySQL)

mysql -uroot -p < /usr/share/zoneminder/db/zm_create.sql

mysql -uroot -p -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"

mysqladmin -uroot -p reload

[millerajm]

Thank you so much, I wasn't sure how to regenerate the full DB like that.

I've done these things, and then imported my Monitors table and my Zones table, it appears to be working great now.

However, the list of events on the web console has still not been reset. Each monitor shows a large number of events and disk space, which is false. Any idea how I can clean those up? It is showing PB of data all together, lol. I assume they must be somewhere in the Monitor DB since they imported back in.

Thank you so very much, now things are working again to some extent!

[bbunge]

/usr/bin/zmaudit.pl

It is a good idea to run this from a cron job at least once a week. Use to be run from options by default but it sometimes did not finish before the next purge ran.

[iconnor]

Please note that you should not need to run zmaudit unless you need to clean up after a crash. It should not be necessary in general.

As to the parent's issues, you can find table definitions in /usr/share/zoneminder/db/zm_create.sql. Cutnpaste as required.

The event counts exist in their own columns in the monitors table.

[millerajm]

[removed]

[millerajm]

[Posted twice somehow so I removed the text above]

Thanks, I've just run zmaudit, it ran quickly and said Deleted 0 log entries by time. But I looked, and it seems maybe it ran overnight already and took care of it, as the monitor totals appear correct now!

Things were definitely messed up, as I said I think it was a time zone issue (now fixed), which somehow caused it to lose track of some events, and fill up the drive completely (the same drive where the mysql table data is stored) this led to a crash of mysql when trying to shut down, so I had to force it to power off after waiting 30 minutes.

I manually removed the image files, and now by dropping and recreating the database (and importing my backed up monitors and zones) things are running fine again.

Now maybe next weekend I will try to get H.264 going instead of images...

Thanks again for all your help!