Hardcoded roleIds in 01.UserRole.99.xml causes exception

[onitake]

In RPM/SOURCES/01.UserRole.99.template.xml, there is a hardcoded roleIds=2 field for the "Template Partner" user.

When the kaltura-db-noprompt-config.sh installation script runs, it will fail when no record with such an id exists in the kaltura.user_role table:

2017-07-17 16:35:56 [0.000524] [127.0.0.1] [1451323540] [268] [API] [KalturaStatement->execute] DEBUG: /* <REDACTED>[1451323540][propel] */ SELECT user_role.ID, user_role.STR_ID, user_role.NAME, user_role.DESCRIPTION, user_role.PARTNER_ID, user_role.STATUS, user_role.PERMISSION_NAMES, user_role.TAGS, user_role.CREATED_AT, user_role.UPDATED_AT, user_role.CUSTOM_DATA, user_role.SYSTEM_NAME FROM `user_role` WHERE user_role.ID='2' AND user_role.STATUS<>'3'
2017-07-17 16:35:56 [0.000569] [127.0.0.1] [1451323540] [269] [API] [KalturaStatement->execute] DEBUG: Sql took - 0.00041604042053223 seconds
2017-07-17 16:35:56 [0.001091] [127.0.0.1] [1451323540] [270] [API] [kCoreException->__construct] ERR: exception 'kPermissionException' with message 'A user role with ID [2] does not exist' in /opt/kaltura/app/alpha/lib/model/UserRolePeer.php:56

Querying the table reveals that all previous roles are created correctly, but the id field does not increment in a predictable manner. For this reason, it doesn't seem to be a good idea to assume anything about the value of this id. If the desired UserRole is the "Manager" created from the same file, it may be possible to use a back-reference similar to the one in 01.UserRole.-2.template.xml: {1:result:id}.

Perhaps this is what was intended here?

[jessp01]

Hello @onitake,

The roleId should be hardcoded, that's not the problem. Please drop the existing half baked DB using: # /opt/kaltura/bin/kaltura-drop-db.sh then rerun /opt/kaltura/bin/kaltura-db-config.sh and paste the full output here. /opt/kaltura/bin/kaltura-db-config.sh calls the following DB population scripts:

php $APP_DIR/deployment/base/scripts/installPlugins.php
php $APP_DIR/deployment/base/scripts/insertDefaults.php
php $APP_DIR/deployment/base/scripts/insertPermissions.php
php $APP_DIR/deployment/base/scripts/insertContent.php

The output for each is logged into the following log files, accordingly:

/opt/kaltura/log/installPlugins.log
/opt/kaltura/log/insertDefaults.log
/opt/kaltura/log/insertPermissions.log
/opt/kaltura/log/insertContent.log

If one of these fails, there no point in moving forward until the issue is resolved. Please check the log for the failing script to understand the root cause. Should you need help with that, please paste the full request made and the response it received from the server.

[onitake]

This log is from a fresh install with a completely clean and empty database. When I hit the error for the first time, I assumed it was caused by an inconsistent setup, but after extensive debugging (and two reinstallations from scratch, including the DB servers), I have to conclude this is not the case.

The installation scripts incorrectly assume that UserRoles are, when inserted into the database in a certain order, receive id primary keys with certain exact values. I conclude this is addressed in 01.UserRole.-2.template.xml by back-referencing the UserRole created in the same multirequest, but not in 01.UserRole.99.template.xml, where a record with id "2" is assumed to be present in the user_role database.

If the "Template Partner" user should reference an exact UserRole, then there needs to be a way to either reliably set the primary key of UserRoles during creation, or there needs to be some other way to reference a certain UserRole. It is wrong to assume that the database's auto-increment logic works in a certain way. The user.add API documentation mentions a roleNames attribute, but it is read-only. Why not make it writeable (with the corresponding logic, of course) and use that instead?

[jessp01]

@onitake, please provide the info I asked for in my last reply and also state the MySQL version you are using so that I can help you further. You are correct, the deployment script does rely on very specific IDs assigned to many records in many tables, not only user roles but also for partners and other things, while I agree that could have been done differently, that's not about to change. If you're setting either auto_increment_increment or auto_increment_offset, you should not, since it would make the deployment fail. This was also discussed here: https://forum.kaltura.org/t/error-in-insertcontent-php-solved/3481/4

[onitake]

The exact server version we use is MariaDB 5.5.52, and the reason why the auto_increment step is not 1 is because we are using master-master database replication. The general recommendation to do this (for both MySQL and MariaDB) is to set auto_increment_increment and auto_increment_offset based on each server's id, so each master generates a different series of auto_increment ids.

In the case of simple master-slave replication, this can be avoided altogether, but for master-master replication it doesn't look like auto_increment synchronisation is addressed by the database engine.

Please excuse me that I didn't provide this detail right away, I only found today it was actually configured like this.

There are a few other ways how this problem could be addressed:

1. Set the auto_increment_increment to 1 and the offset to 0 (or 1) during bootstrapping, then change it after initial records have been inserted into the database
2. Not use the API, and instead insert a database dump directly into the database (with hard-coded ids)
3. Break up replication, bootstrap using default settings, dump, insert into the other site, then enable replication again.

I would like to avoid 3., because it is a very complicated and error-prone procedure. Option 1. is slightly less dangerous and may work if access from a single site can be guaranteed (this is already the case for our setup). 2. is clearly be the best choice here, as the exact bootstrap table layout can be guaranteed, but it would need manual changes to the installation scripts.

I should also note that the replication setup was created by a Kaltura engineer and we receive support from Kaltura for it. But, as it seems that it is not reproducible using the provided installation scripts, we have to find an alternate solution - or the installation scripts need to be changed.

Concerning the full log files: I'd like to avoid posting them here without anonymising them first, and the reason why the problem occurs is already clear. If you really need access to them, I can attach them to the ticket I opened on the customer support portal instead.

[etameran]

Option 1 seems like the easiest solution as this is only for the setup step .

Eran

Sent from my iPhone

On 19 Jul 2017, at 18:06, Gregor Riepl notifications@github.com<mailto:notifications@github.com> wrote:

The exact server version we use is MariaDB 5.5.52, and the reason why the auto_increment step is not 1 is because we are using master-master database replication. The general recommendation to do this (for both MySQL and MariaDB) is to set auto_increment_increment and auto_increment_offset based on each server's id, so each master generates a different series of auto_increment ids.

In the case of simple master-slave replication, this can be avoided altogether, but for master-master replication it doesn't look like auto_increment synchronisation is addressed by the database engine.

Please excuse me that I didn't provide this detail right away, I only found today it was actually configured like this.

There are a few other ways how this problem could be addressed:

1. Set the auto_increment_increment to 1 and the offset to 0 (or 1) during bootstrapping, then change it after initial records have been inserted into the database
2. Not use the API, and instead insert a database dump directly into the database (with hard-coded ids)
3. Break up replication, bootstrap using default settings, dump, insert into the other site, then enable replication again.

I would like to avoid 3., because it is a very complicated and error-prone procedure. Option 1. is slightly less dangerous and may work if access from a single site can be guaranteed (this is already the case for our setup). 2. is clearly be the best choice here, as the exact bootstrap table layout can be guaranteed, but it would need manual changes to the installation scripts.

I should also note that the replication setup was created by a Kaltura engineer ( @DBezemerhttps://github.com/dbezemer ) and we receive support from Kaltura for it. But, as it seems that it is not reproducible using the provided installation scripts, we have to find an alternate solution - or the installation scripts need to be changed.

Concerning the full log files: I'd like to avoid posting them here without anonymising them first, and the reason why the problem occurs is already clear. If you really need access to them, I can attach them to the ticket I opened on the customer support portal instead.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/kaltura/platform-install-packages/issues/594#issuecomment-316417105, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ABtRXVgxs3onNbZwgZEPIwR4OMtY_Hm5ks5sPhtQgaJpZM4OaLV_.

[jessp01]

Hi @onitake,

I agree with @etameran, the first option you stated is the best course of action. As for the logs, at the moment I do not require them because we know what the issue is. Once you drop the auto_increment_increment and auto_increment_offset directives, run /opt/kaltura/bin/kaltura-drop-db.sh to drop the half baked DB and then the DB population phase should complete successfully. Should you have additional issues, I will need to see the logs. You can send to our support team, they'll forward to me.