SOLVED-Recover with old-ish database

[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

[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?

[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;

  creating and new blank one

  Code: Select all

  MariaDB [(none)]>CREATE DATABASE zm;

  importing my backup

  Code: Select all

  Same as before...

  running zmupdate.pl several times

  Code: Select all

  #sudo zmupdate.pl -f

[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.

[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.