Xibo CMS 1.8.11 - Templates not visible as a normal user

hhan · September 26, 2018, 1:59pm

Here is a database comparison between a new installation of 1.8.11 (right side) and an update installation (left side).

The following update steps were carried out:
Xibo-cms-1.8.1
Xibo-cms-1.8.2
Xibo-cms-1.8.7
Xibo-cms-1.8.9
Xibo-cms-1.8.10
Xibo-cms-1.8.11

alex · September 26, 2018, 6:27pm

I’ve checked a database here that was upgraded to each point release in 1.8 and those keys are correctly defined there.

It looks to me as if a step was skipped in the 1.8.10 - 1.8.11 upgrade where those would be added. If you still have your backup, I would suggest rolling back to 1.8.10 and performing the upgrade again, ensuring that no steps are skipped over.

Failing that, you could add those keys manually.

hhan · September 27, 2018, 2:56pm

I have the error in all update installations and I have not seen any errors during the update.

All custom installations !

DROP TABLE IF EXISTS permission;
/ *! 40101 SET @saved_cs_client = @@ character_set_client * /;
/ *! 40101 SET character_set_client = utf8 * /;
CREATE TABLE permission (
permissionId int (11) NOT NULL AUTO_INCREMENT,
entityId int (11) NOT NULL,
groupId int (11) NOT NULL,
objectId int (11) NOT NULL,
view tinyint (4) NOT NULL,
edit tinyint (4) NOT NULL,
delete tinyint (4) NOT NULL,
PRIMARY KEY (permissionId),
KEY permission_ibfk_1 (groupId),
KEY permission_ibfk_2 (entityId),
KEY permission_objectId_index (objectId),
CONSTRAINT permission_ibfk_1 FOREIGN KEY (groupId) REFERENCES group (groupID),
CONSTRAINT permission_ibfk_2 FOREIGN KEY (entityId) REFERENCES permissionentity (entityId)
) ENGINE = InnoDB AUTO_INCREMENT = 71 DEFAULT CHARSET = utf8;
/ *! 40101 SET character_set_client = @saved_cs_client * /;

Another example of update order:
Xibo-cms-1.8.1
Xibo-cms-1.8.2
Xibo-cms-1.8.3
Xibo-cms-1.8.4
Xibo-cms-1.8.5
Xibo-cms-1.8.9
Xibo-cms-1.8.10
Xibo-cms-1.8.11

alex · September 27, 2018, 3:32pm

The code that adds it is here:

github.com

xibosignage/xibo-cms/blob/master/lib/Upgrade/FixDatabaseIndexesAndContraints.php#L222


            AND `table_name` = \'lktagdisplaygroup\' AND `index_name` LIKE \'%fk_%\' AND `column_name` = \'displaygroupId\';', [])) {


        // Delete any records which result in a constraint failure (the records would be orphaned anyway)
        $this->store->update('DELETE FROM `lktagdisplaygroup` WHERE displayGroupId NOT IN (SELECT displayGroupId FROM `displaygroup`)', []);


        // Add the constraint
        $this->store->update('ALTER TABLE `lktagdisplaygroup` ADD CONSTRAINT `lktagdisplaygroup_ibfk_1` FOREIGN KEY (`displayGroupId`) REFERENCES `displaygroup` (`displayGroupId`);', []);
    }
}


/**
 * Adds a foreign key for the permissions tables
 */
private function addForeignKeyToPermissions()
{
    if (!$this->store->exists('
        SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE()
            AND `table_name` = \'permission\' AND `index_name` LIKE \'%fk_%\' AND `column_name` = \'groupId\';', [])) {


        // Delete any records which result in a constraint failure (the records would be orphaned anyway)
        $this->store->update('DELETE FROM `permission` WHERE groupId NOT IN (SELECT groupId FROM `group`)', []);

That’s definitely run in the 1.8.10-11 upgrade steps.

If you can send me over your database prior to upgrade I’m happy to try an upgrade here and see if they’re added correctly.

hhan · September 27, 2018, 3:51pm

I just installed cms 1.8.10 and immediately afterwards installed the update to 1.8.11.

Database was still empty.

There were no mistakes.

After the update:

DROP TABLE IF EXISTS permission;
/ *! 40101 SET @saved_cs_client = @@ character_set_client * /;
/ *! 40101 SET character_set_client = utf8 * /;
CREATE TABLE permission (
permissionId int (11) NOT NULL AUTO_INCREMENT,
entityId int (11) NOT NULL,
groupId int (11) NOT NULL,
objectId int (11) NOT NULL,
view tinyint (4) NOT NULL,
edit tinyint (4) NOT NULL,
delete tinyint (4) NOT NULL,
PRIMARY KEY (permissionId),
KEY permission_ibfk_1 (groupId),
KEY permission_ibfk_2 (entityId),
KEY permission_objectId_index (objectId),
CONSTRAINT permission_ibfk_1 FOREIGN KEY (groupId) REFERENCES group (groupID),
CONSTRAINT permission_ibfk_2 FOREIGN KEY (entityId) REFERENCES permissionentity (entityId)
) ENGINE = InnoDB AUTO_INCREMENT = 20 DEFAULT CHARSET = utf8;
/ *! 40101 SET character_set_client = @saved_cs_client * /;

alex · September 28, 2018, 10:31am

The two keys are correctly listed there?

I’m missing the issue in that case I’m afraid.

hhan · September 28, 2018, 1:14pm

416b0aac9c3308a987379d04e3be93c3702d0484_1_690x242

The table structure is different, or I am wrong. ?