Need to recreate log table and stat table in Xibo Database v2.3.8

[Gerard_Billebault]

Hello
A manipulation error caused the deletion of the ‘log’ table and the ‘stat’ table of a xibo v2.3.8 instance (Ubuntu on Docker).
The succession of errors does not allow me to find my backups anymore.
The only chance to recover is to recreate these 2 tables.
I need the :
CREATE TABLE log etc …
and
CREATE TABLE stat etc …
Thanks for your help.
(I think of Alex …)

[alex]

You would need to do a fresh install in a new directory of version 2.3.8, and then you can run

SHOW CREATE TABLE `log`;
SHOW CREATE TABLE `stat`;

and then run those commands on your live database.

[Gerard_Billebault]

Hello,

I used this method even before you replied to me. But I used instead the backup file of an instance which is not used at home and I was able to recover the CREATE command of my 2 tables.
(/opt/xibo/shared/backup/db/latest.sql.gz)

If it can be useful to anyone here is for a V2.3.8 instance:

CREATE TABLE `log` (
  `logId` int(11) NOT NULL AUTO_INCREMENT,
  `runNo` varchar(10) NOT NULL,
  `logDate` datetime NOT NULL,
  `channel` varchar(20) NOT NULL,
  `type` varchar(254) NOT NULL,
  `page` varchar(50) NOT NULL,
  `function` varchar(50) DEFAULT NULL,
  `message` longtext NOT NULL,
  `userId` int(11) NOT NULL DEFAULT '0',
  `displayId` int(11) DEFAULT NULL,
  PRIMARY KEY (`logId`),
  KEY `logDate` (`logDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `stat` (
  `statId` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(20) NOT NULL,
  `statDate` int(11) NOT NULL,
  `scheduleId` int(11) NOT NULL,
  `displayId` int(11) NOT NULL,
  `campaignId` int(11) DEFAULT NULL,
  `layoutId` int(11) DEFAULT NULL,
  `mediaId` int(11) DEFAULT NULL,
  `widgetId` int(11) DEFAULT NULL,
  `start` int(11) NOT NULL,
  `end` int(11) NOT NULL,
  `tag` varchar(254) DEFAULT NULL,
  `duration` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  `engagements` text,
  PRIMARY KEY (`statId`),
  KEY `statDate` (`statDate`),
  KEY `displayId` (`displayId`,`end`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[alex]

Thanks for that. To anyone else reading this who wants the structure for a specific version, please use the method I give above and don’t attempt to shortcut by using these, as the structure does change between versions.

It’s really important you use the correct structure for your specific CMS version, and not the ones from 2.3.8 if you’re running something else!

[Gerard_Billebault]

Hello Alex
Yes, this clarification is perfectly necessary.
I would add that it is strongly advised not to touch the structure of the MySql database of Xibo.
I had to do this DROP/CREATE operation as a last resort after having exhausted all the restoration solutions we had.
We were lucky this time to reboot, but this action is really not recommended.
Thanks Alex