Page 1 of 1
SOLVED-Recover with old-ish database
Posted: Tue Dec 20, 2022 5:37 am
by Pedulla
Hi All,
Ubuntu Server 22, LEMP stack, ZM 1.36.32.
Installed the system fine and made sure it was running before I tried to restore an earlier zm database:
Code: Select all
sudo mysql -u root -p zm < ./zmdb.sql
Enter password:
ERROR 1005 (HY000) at line 230: Can't create table `zm`.`Events` (errno: 150 "Foreign key constraint is incorrectly formed")
Now ZM won't start because the database is hosed. So I dropped ZM and recreated with zm_create.sql:
Code: Select all
sudo mysql -u root -p < /usr/share/zoneminder/db/zm_create.sql
So now what?
The backup db is only a year old (1.34 at the earliest), but has the config for ~18 monitors.
What do I need to do to restore this DB?
Thanks
Re: Recover with old-ish database
Posted: Tue Dec 20, 2022 6:07 am
by Pedulla
For reference, here's the .Events table section from zmdb.sql
Code: Select all
DROP TABLE IF EXISTS `Events`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Events` (
`Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`MonitorId` int(10) unsigned NOT NULL DEFAULT '0',
`Name` varchar(64) NOT NULL DEFAULT '',
`Cause` varchar(32) NOT NULL DEFAULT '',
`StartTime` datetime DEFAULT NULL,
`EndTime` datetime DEFAULT NULL,
`Width` smallint(5) unsigned NOT NULL DEFAULT '0',
`Height` smallint(5) unsigned NOT NULL DEFAULT '0',
`Length` decimal(10,2) NOT NULL DEFAULT '0.00',
`Frames` int(10) unsigned DEFAULT NULL,
`AlarmFrames` int(10) unsigned DEFAULT NULL,
`TotScore` int(10) unsigned NOT NULL DEFAULT '0',
`AvgScore` smallint(5) unsigned DEFAULT '0',
`MaxScore` smallint(5) unsigned DEFAULT '0',
`Archived` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Videoed` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Uploaded` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Emailed` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Messaged` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Executed` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Notes` text,
PRIMARY KEY (`Id`,`MonitorId`),
KEY `MonitorId` (`MonitorId`),
KEY `StartTime` (`StartTime`),
KEY `Frames` (`Frames`),
KEY `Archived` (`Archived`)
) ENGINE=InnoDB AUTO_INCREMENT=1443597 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `Events`
--
LOCK TABLES `Events` WRITE;
/*!40000 ALTER TABLE `Events` DISABLE KEYS */;
/*!40000 ALTER TABLE `Events` ENABLE KEYS */;
UNLOCK TABLES;
Is there a problem with Name and Cause, maybe?
SOLVED - Re: Recover with old-ish database
Posted: Tue Dec 20, 2022 6:38 am
by Pedulla
Okay, I didn't actually solve the mysql error, but I did get the system back up and running with the recovered database.
It required:
- dropping the zm db
Code: Select all
MariaDB [(none)]>DROP DATABASE zm;
Code: Select all
MariaDB [(none)]>CREATE DATABASE zm;
Re: SOLVED-Recover with old-ish database
Posted: Tue Dec 20, 2022 8:08 am
by Magic919
Generally zmupdate.pl without the -f does this job. The -f is worth running afterwards, as it'll freshen up the config in the DB.
Re: SOLVED-Recover with old-ish database
Posted: Wed Dec 21, 2022 12:00 am
by Pedulla
Yeah, that's all I've needed in the past.
I lost track of the number of times I ran zmupdate.pl with and without the -f.
It's like it was progressing through the DB till it was all straightened out.