Plugins » Katello

On performing a fresh installation of CentOS 7, and a fresh installation of Katello, it is impossible to complete the installation successfully with MySQL as an unmanaged backend database.

The DB Migration scripts can not be applied.

There are a few main areas of issue that I have worked out:
- ActiveRecord, when creating new tables with t.references assumes the data type to be BIGINT. When a foreign key is subsequently added, it frequently references a column of type INT, and MySQL rejects the constraint.
- When creating multi-column indexes, a few times an index is made which spans 5+ columns of type VARCHAR. This exceeds MySQL's limit on index width in MySQL 5.7 which is 3072 bytes. ( Characters are multiplied by 4x with utf8mb4 )

There are a few one-off issues:
- When applying 20170222131211_change_pool_columns_to_dates.rb, Ruby hits "SystemStackError: stack level too deep" for some reason.
- I got past by executing ALTER TABLE katello_pools MODIFY start_date TIMESTAMP, MODIFY end_date TIMESTAMP;

- When applying 20161028153131_sub_facet_user_index_not_uniq.rb, Drop + Add index fails due to foreign key.
    - Workaround:

ALTER TABLE katello_subscription_facets DROP FOREIGN KEY `fk_rails_ee1659f1ac`;
ALTER TABLE katello_subscription_facets DROP KEY `index_katello_subscription_facets_on_user_id`;
ALTER TABLE katello_subscription_facets ADD KEY `index_katello_subscription_facets_on_user_id` (`user_id`);
ALTER TABLE katello_subscription_facets ADD FOREIGN KEY `fk_rails_ee1659f1ac`(`user_id`) REFERENCES `users`(`id`);
INSERT INTO schema_migrations VALUES ( 20161028153131 );

- Initial error was mysterious, the latest migration applied was 20131014225132, inspecting the next migration 20131016124255_add_foreign_keys_engine.rb, it appears that everything was applied EXCEPT the last 2 foreign keys, which I could manually add?

ALTER TABLE katello_user_notices ADD FOREIGN KEY `user_notices_notice_id_fk` (`notice_id`) REFERENCES `katello_notices` (`id`);
ALTER TABLE katello_user_notices ADD FOREIGN KEY `user_notices_user_id_fk` (`user_id`) REFERENCES `users` (`id`);
INSERT INTO schema_migrations VALUES ( 20131016124255 );

I'm attaching the full log of my efforts.

I gave up on 20180920214134_create_repository_root.rb after having spent 5-6 hours hand-fixing each error. Chasing down FK dependency chains is such a nightmare. This one bailed in the middle of a no-rollback, huge migration. So I'd either have to hand-remove/undo what had already been applied so I could re-run the migration, or hand-apply the remainder of it and skip the migration. Didn't want to do either one.

Honestly, my recommendation at this point would be either:
A: Cut a new db schema release for 1.21 using MySQL backend. Installs from 1.21 forward will import the current working MySQL schema and use mysql-friendly migrations for future releases.
B: Go back and patch/fix all the migration scripts to be mysql-friendly.
C: Drop support for MySQL as a backend database provider.

Perhaps we could use a SQL conversion tool to dump the schema from a working postgres installation into mysql-friendly syntax/structure.

As an aside, I could not find a configuration option during install to specify the mysql socket if setup to a non-default location. I had to symlink the socket to where Foreman was looking ( the compiled default ).

Final thoughts, You cannot install multiple foreman databases in the same MySQL instance. At first I attempted to install Katello along side my older 1.12 Foreman installation, using the same MySQL instance on the same database server, using different database names. However, Foreign Keys in MySQL must have globally unique names - since all the Katello schema specifies the names of the Foreign Key's, it makes it impossible to have multiple schema's in the same mysql instance. Perhaps a feature request to prefix all database objects with a specified prefix. This would at least alleviate the unique naming constraints of MySQL's foreign keys, and maybe allow multiple concurrent Foreman/Katello installations on the same MySQL Instance.

Attached is my notes during the trial installation.