Flush old data from ibd files

[BiloxiGeek]

Got 1.30.3 working, I've switched the database to use innodb_file_per_table. Right now I have no events showing up, the filesystem is empty for all the monitors. But when I look at the files in /var/lib/mysql/zm/ I see that Frames.ibd and Logs.ibd are 3 and 5 GB in size and Stats.ibd is about 240MB. Is there a recommended method to flushing the old data from at least Frames.ibd. There shouldn't be any frames to track since there are no events currently saved.

[iconnor]

I would tend to want to find out what is in there. You say that there are no events, so there should be no frames...

so if you open a mysql shell...

What does

SELECT * from Frames;

give you?

If you don't care about the contents, then

DELETE FROM Frames;

Should clear it out.

I'd be interested in the out put of zmaudit...

[bbunge]

From what I've read it is not uncommon for .idb files to stay large even when "empty." Reclaim space by running OPTIMIZE TABLE under MySQL

[BiloxiGeek]

I see year old frames showing up. I did a little googling later yesterday and I'm currently deleting old frames a little at a time while zoneminder is shut down. When I started I had over 30 million rows in the Frames table. Once I get it cleaned out I'll run the optimize process and see what happens. If that goes well I'm gonna probably do the same on the Logs and Stats tables.

iconnor: Maybe I caused this myself. On occasion I'll forget to check things in ZM and end up with a couple or three days of events. Deleting them via the web UI can be a bit tedious. So one method I use is to shut down ZM, Run a find for jpg files in the events subdirectory and rm them all. Then I run zmaudit I get a bunch of warnings that it couldn't find frames (or maybe images, can't quite recall the exact wording) and it then deletes the event. Does that not also delete related rows in the Frames table? There is a single SQL error message when it finishes but I can't recall what it says right off the top of my head.

[BiloxiGeek]

Got all the row in Frames deleted, ran the optimize table Frames and now Frames.ibd is about 147kB. Much better! Starting the same procedure on Logs now.

[bbunge]

Got all the row in Frames deleted, ran the optimize table Frames and now Frames.ibd is about 147kB. Much better! Starting the same procedure on Logs now.

Much easier to set a filter to clean out the old events. Or, run a filter manually...

[BiloxiGeek]

Just started up ZM, forced an alarm on one monitor just to have one event to play with. I see the appropriate entries in the Events and Frames tables. Then I deleted the event. The Events table now has no rows, but the Frames table still shows all the Frames.

Stopped ZM, ran zmaudit.pl and it deleted the events and gives this error:

Code: Select all

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 at /usr/bin/zmaudit.pl line 384.

Still have 740 rows in Frames with no associated Event.

[BiloxiGeek]

Deleted 5 events using the web UI. Checking from mysql I see no events but 909 frames. Ran zmaudit and checked mysql again, same state. Events are deleted but frames are not. The frames themselves are deleted from the filesystem, just the reference in the Frames table remains.

Code: Select all

[root@zone zm]# zmaudit.pl 
Argument "" isn't numeric in numeric gt (>) at /usr/bin/zmaudit.pl line 262.
Filesystem event '208/3' does not exist in database, deleting
Filesystem event '203/6' does not exist in database, deleting
Filesystem event '203/4' does not exist in database, deleting
Filesystem event '103/7' does not exist in database, deleting
Filesystem event '103/5' does not exist in database, deleting
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 at /usr/bin/zmaudit.pl line 384.

[root@zone zm]# zmaudit.pl 
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 at /usr/bin/zmaudit.pl line 384.