Deleted Logs.frm ... D'OH!!!

[bs27975]

So, I did a dumb thing, deleted Logs.frm

And mondorestore isn't happy with my mondoarchive backup of the night before, or the night before that. Which is to say ... I might not have a backup.

I all but never have to do anything in mysql, so I'm bumbling about here, guidance appreciated.

On the thought that maybe I could just recreate the table (hoping that zm logs are only backwards looking, and if all is sufficiently ok now, it's only logging going forwards that may matter to me), I thought to cut out the logs table creation mysql from /usr/share/zoneminder/db/zm_create.sql, but I'm not getting anywhere.

Logging in to mysql with the userid and password from /etc/zm/zm.conf,
# mysql -u {user} -p,
mysql> use zm
mysql> CREATE TABLE `Logs` ( ...
ERROR 1142 (42000): CREATE command denied to user 'zmuser'@'localhost' for table 'Logs'
{sigh}

So I try:
# mysql -u root -p
mysql> use zm
mysql> CREATE TABLE `Logs` ( ...
ERROR 1017 (HY000): Can't find file: './zm/Logs.frm' (errno: 13)
mysql> show tables;
- shows Logs, and I already know the file isn't there, so I suppose I need to drop the table first. Taking my cue from zm_create.sql ...

mysql> DROP TABLE IF EXISTS `Logs`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `Logs` ( ...
ERROR 1017 (HY000): Can't find file: './zm/Logs.frm' (errno: 13)

mysql> show tables;
- does show Logs as being present.

Anyone know how I make mysql forget about the Logs.frm file so I can recreate it?n


For that matter, is doing so and recreating the table likely to let me get on with my day, with a reasonably happy zm?

TIA.

[bs27975]

At one point I tried copying Logs.frm from another system, without success. Likely my inexperience with mysql. At one point got Error 13 which appears to be permissions. chown mysql:mysql fixed that, but didn't fix the problem.

It did, however, likely allow me to delete the table. And likely thus then allowed me to recreate a blank one. At which point mysql got happy, and thus zoneminder.

mysqlcheck -uroot -p --all-databases --auto-repair

- was useful as a sanity check.

At some point I did: show tables;

- and I noticed that Logs was no longer present.

So executing:
CREATE TABLE `Logs` ( `TimeKey` decimal(16,6) NOT NULL, `Component` varchar(32) NOT NULL, `Pid` smallint(6) DEFAULT NULL, `Level` tinyint(3) NOT NULL, `Code` char(3) NOT NULL, `Message` varchar(255) NOT NULL, `File` varchar(255) DEFAULT NULL, `Line` smallint(5) unsigned DEFAULT NULL, KEY `TimeKey` (`TimeKey`) ) ENGINE=MyISAM;


Other useful bits (if only to demonstrate I was headed in the right direction):

# mysql -uroot -p
(Note: don't include '- ' below.)
-help
- status
- show databases;
- use zm
- SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';
- show table Zones
- select * from Zones;
- DROP TABLE IF EXISTS `Logs`;

[jtech]

So it sounds like the restored version of Logs.frm or a copy from a different system caused the file permissions or ownership to change, locking mysql out from being able to delete (and hence then re-create the table).

If you want to delete the contents of a mysql table you should do it from within the Mysql cli itself.. not the filesystem!

Glad you got it sorted, just a note did you re-create using ENGINE=MyISAM?

I thought at some point Zoneminder moved to InnoDB, but maybe I'm wrong.