Problem:
I want to upgrade foreman 1.19.1 to 1.20.2 using yum/rpms on CentOS 7.
DB migration task:
foreman-rake db:migrate
fails with error
Mysql2::Error: Duplicate column name ‘type’: ALTER TABLE tokens ADD type varchar(255) DEFAULT ‘Token::Build’ NOT NULL
The complete error message/stack trace is more or less identical to Bug #24993: migration broken on nightly mysql - Foreman
Expected outcome:
DB migration task succeeds.
Foreman and Proxy versions:
1.19.1
If you need more details (like config files) please ask. The setup is pretty much standard. (foreman with mysql/mariadb on dedictated server).
We have yet to identify what caused the creation of the duplicate column in some cases. Do you have any plugins installed? Are you running some sort of HA setup or DB duplication or something like that? Is there any data in the type column of that table?
If all else fails, you should be able to drop the column manually and rerun the migrations.
Do you have any plugins installed?
I can find the following plugins via gui: /about#plugins
foreman_memcache
foreman_templates
puppetdb_foreman
Are you running some sort of HA setup or DB duplication or something like that ?
Yes, somehow: I dont have a HA proxy or simliar that is handling all request in the first place. I am just running bascially 3 instances to distribute the load a little bit. So one is used as GUI, another one to collect reports from puppet and the last is used as ENC. All instances are configured to acces the DB. As far as I understand (and I think it turned out to be indeed necessary) I am using memcached so somehow sync all 3 instances.
Is there any data in the type column of that table?
MariaDB [foreman]> select * from tokens;
±----±-------------------------------------±--------------------±--------±-------------+
| id | value | expires | host_id | type |
±----±-------------------------------------±--------------------±--------±-------------+
| 161 | xx | 2017-12-18 02:07:32 | 36 | Token::Build |
±----±-------------------------------------±--------------------±--------±-------------+
It’s strange that this only occurs with mysql… is it possible that you ran the migrations from more than one instance at the same time? In any case, I opened a PR at https://github.com/theforeman/foreman/pull/6862 to try and fix this issue once and for all.
Since you’re a MySQL user, we are considering dropping support for it, so it would be get your feedback on RFC: Dropping support for MySQL as well 
yes that is possible, … and as far as I understand this happens just as part of the upgrade (post installation step for rpm installations) procedure. At least in the manual ( Step 3 - Post-upgrade steps) it is menitioned:
Step 3 (A) - Database migration and cleanup
The database should be migrated already, but you can make sure by executing the migration script again, it should produce no errors or output:
By the way I tried to migrate to postgres. But I did encounter issues with permissions and TRIGGERS. I guess I have to kindly ask for help in this matter in another post … ?
is it possible that you ran the migrations from more than one instance at the same time?
If I now understand you right you really mean at the same time… so that both or more instances trying to do the DB migration at the same time: I am not sure, possiblity exists.
For the record, here is my analysis: I believe that type colum presence is not the root cause (what would create it anyway), I think the culprit is length 900 which is more than what MySQL allows according the documentation and the error that Ohad reported: Mysql2::Error: Specified key was too long; max key length is 767 bytes: ALTER TABLE tokens CHANGE value value varchar(900) DEFAULT NULL
What we all do when migration fails? We run it again! My idea is that the type column which is not expected is consequence of the unfinished migration - it creates the column, then it fails to change its length and Rails stops. Next time when it tries to perform the migration the column already exists. Maybe Rails is using RDBM transactions to ensure migration integrity but MySQL does not support that (silently skips which is “the golden standard” in MySQL world). Edit: Looks like MySQL really does not support DDL transaction rollback: https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html
Anyway, the real cause seems to be a thing which I had no idea about: prefix limit : 767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It’s 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes. It turns out that MySQL creates indexes for string columns only from the leading part of column values - that’s the prefix limit.
The PR is close to be merged.
The PR is close to be merged.
What does it mean ? Will there a patch for 1.20.2 be available soon ?
Is there something what I can do else ?
The PR was merged and Tomer backported this fix into 1.20-1.22 so yes it will be released with the next updates.
I just noted another issue, that is occuring while creation/cloning/rebuilding of host: The task is failing with below error. I think this has to do with the upgrade to 1.19.1. Do you think it is worth to try to rename the column ‘type’ as suggested ? If yes, could you advice which table it is about ?
ActiveRecord::SubclassNotFound
The single-table inheritance mechanism failed to locate the subclass: ‘Token::Build’. This error is raised because the column ‘type’ is reserved for storing the class in case of inheritance. Please rename this column if you didn’t intend it to be used for storing the inheritance class or overwrite Token.inheritance_column to use another column for that information.
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/inheritance.rb:196:in `rescue in find_sti_class’
…
This is on 1.19.1? iirc build token as a subclass was only introduced in 1.20, so not sure how that got into your db.
Could you paste the full stack trace please? that may give us some hint to what’s going on.
Thanks for your reply. It could even be, that this is the result of my failed attempt to migrate to 1.20… (at least the DB)
However, what is for sure: the foreman application is running as 1.19.1.
Here is full stack trace:
https://pastebin.com/eZDv3akE
That does indeed looks like outcome of a failed upgrade. with 1.20.3 out including the fix for the migration that failed, it’s possible the easiest way to fix it would be to actually finish that upgrade.
The other option would be to try and remove the type column from the token table and remove any tokens currently in it.
ok, I will give 1.20.3 a try