Bug #24993: migration broken on nightly mysql - Foreman

Bug #24993

migration broken on nightly mysql

Added by Ohad Levy over 4 years ago. Updated over 3 years ago.

Status:

Closed

Priority:

Normal

Assignee:

Tomer Brisker

Category:

DB migrations

Target version:

1.20.3

Difficulty:

Triaged:

Bugzilla link:

Pull request:

https://github.com/theforeman/foreman/pull/6862

Fixed in Releases:

1.20.3, 1.21.4, 1.22.1, 1.23.0

Found in Releases:

1.20.0

Red Hat JIRA:

Description

Mysql2::Error: Duplicate column name 'type': ALTER TABLE `tokens` ADD `type` varchar(255) DEFAULT 'Token::Build' NOT NULL
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:578:in `block in log'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:569:in `log'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:581:in `add_column'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:871:in `block in method_missing'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:840:in `block in say_with_time'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:840:in `say_with_time'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:860:in `method_missing'
/home/foreman/git/foreman/db/migrate/20180613100703_add_type_to_token.rb:7:in `up'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:817:in `exec_migration'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:798:in `block (2 levels) in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:797:in `block in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:414:in `with_connection'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:796:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:977:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1292:in `block in execute_migration_in_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1345:in `ddl_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1291:in `execute_migration_in_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1263:in `block in migrate_without_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1262:in `each'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1262:in `migrate_without_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1210:in `block in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1363:in `with_advisory_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1210:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1036:in `up'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1011:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/tasks/database_tasks.rb:172:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/railties/databases.rake:60:in `block (2 levels) in <top (required)>'
/home/foreman/gems/gems/rake-12.3.1/exe/rake:27:in `<top (required)>'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:74:in `load'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:74:in `kernel_load'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:28:in `run'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:424:in `exec'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/invocation.rb:126:in `invoke_command'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor.rb:387:in `dispatch'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:27:in `dispatch'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/base.rb:466:in `start'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:18:in `start'
/home/foreman/gems/gems/bundler-1.16.5/exe/bundle:30:in `block in <top (required)>'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/friendly_errors.rb:124:in `with_friendly_errors'
/home/foreman/gems/gems/bundler-1.16.5/exe/bundle:22:in `<top (required)>'
/opt/rh/rh-ruby24/root/usr/bin/bundle:22:in `load'
/opt/rh/rh-ruby24/root/usr/bin/bundle:22:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Duplicate column name 'type': ALTER TABLE `tokens` ADD `type` varchar(255) DEFAULT 'Token::Build' NOT NULL
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:578:in `block in log'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:569:in `log'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:581:in `add_column'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:871:in `block in method_missing'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:840:in `block in say_with_time'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:840:in `say_with_time'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:860:in `method_missing'
/home/foreman/git/foreman/db/migrate/20180613100703_add_type_to_token.rb:7:in `up'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:817:in `exec_migration'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:798:in `block (2 levels) in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:797:in `block in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:414:in `with_connection'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:796:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:977:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1292:in `block in execute_migration_in_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1345:in `ddl_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1291:in `execute_migration_in_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1263:in `block in migrate_without_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1262:in `each'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1262:in `migrate_without_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1210:in `block in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1363:in `with_advisory_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1210:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1036:in `up'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1011:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/tasks/database_tasks.rb:172:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/railties/databases.rake:60:in `block (2 levels) in <top (required)>'
/home/foreman/gems/gems/rake-12.3.1/exe/rake:27:in `<top (required)>'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:74:in `load'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:74:in `kernel_load'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:28:in `run'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:424:in `exec'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/invocation.rb:126:in `invoke_command'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor.rb:387:in `dispatch'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:27:in `dispatch'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/base.rb:466:in `start'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:18:in `start'
/home/foreman/gems/gems/bundler-1.16.5/exe/bundle:30:in `block in <top (required)>'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/friendly_errors.rb:124:in `with_friendly_errors'
/home/foreman/gems/gems/bundler-1.16.5/exe/bundle:22:in `<top (required)>'
/opt/rh/rh-ruby24/root/usr/bin/bundle:22:in `load'
/opt/rh/rh-ruby24/root/usr/bin/bundle:22:in `<main>'

Caused by:
Mysql2::Error: Duplicate column name 'type'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/home/foreman/gems/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:579:in `block (2 levels) in log'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:578:in `block in log'
/home/foreman/gems/gems/activesupport-5.2.1/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:569:in `log'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:581:in `add_column'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:871:in `block in method_missing'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:840:in `block in say_with_time'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:840:in `say_with_time'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:860:in `method_missing'
/home/foreman/git/foreman/db/migrate/20180613100703_add_type_to_token.rb:7:in `up'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:817:in `exec_migration'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:798:in `block (2 levels) in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:797:in `block in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:414:in `with_connection'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:796:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:977:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1292:in `block in execute_migration_in_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1345:in `ddl_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1291:in `execute_migration_in_transaction'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1263:in `block in migrate_without_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1262:in `each'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1262:in `migrate_without_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1210:in `block in migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1363:in `with_advisory_lock'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1210:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1036:in `up'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/migration.rb:1011:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/tasks/database_tasks.rb:172:in `migrate'
/home/foreman/gems/gems/activerecord-5.2.1/lib/active_record/railties/databases.rake:60:in `block (2 levels) in <top (required)>'
/home/foreman/gems/gems/rake-12.3.1/exe/rake:27:in `<top (required)>'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:74:in `load'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:74:in `kernel_load'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli/exec.rb:28:in `run'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:424:in `exec'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/invocation.rb:126:in `invoke_command'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor.rb:387:in `dispatch'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:27:in `dispatch'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/vendor/thor/lib/thor/base.rb:466:in `start'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/cli.rb:18:in `start'
/home/foreman/gems/gems/bundler-1.16.5/exe/bundle:30:in `block in <top (required)>'
/home/foreman/gems/gems/bundler-1.16.5/lib/bundler/friendly_errors.rb:124:in `with_friendly_errors'
/home/foreman/gems/gems/bundler-1.16.5/exe/bundle:22:in `<top (required)>'
/opt/rh/rh-ruby24/root/usr/bin/bundle:22:in `load'
/opt/rh/rh-ruby24/root/usr/bin/bundle:22:in `<main>'

Related issues

Associated revisions

Revision 8e4b0853 (diff)
Added by Tomer Brisker over 3 years ago

Fixes #24993 - Modify token value to text

MySQL can't index fields longer than 767 bytes so we need to limit the
index length. This is causing upgrade failures on MySQL, and users
attempting to rerun migrations will fail on trying to recreate the
existing type index, as there is no transaction support in MySQL for
index creation so it isn't rolled back when the first migration fails.

History

#1 Updated by Anonymous over 4 years ago

Related to Feature #23210: PuppetCA: Token Based Autosigning added

#2 Updated by Anonymous over 4 years ago

https://dev.mysql.com/doc/refman/5.5/en/keywords.html -> "TYPE" seems to be a reserved word, but I'm unsure if that's really the cause.

#3 Updated by Julian Todt over 4 years ago

I don't think the name `type` is the problem as there are already various tables using it as a name (eg. trends, host_status, taxonomies).
The message would let me to suspect that the table doesn't look like I expected when running the migration, but I think you would need to test this manually.
I currently don't have a mysql setup around and as I'm currently on vacation anyway, it would be nice if someone else could take a look, thank you.

#4 Updated by Ohad Levy over 4 years ago

also, i skipped the earlier error

db/migrate/20180613100703_add_type_to_token.rb
class AddTypeToToken < ActiveRecord::Migration[5.1]
     remove_index :tokens, :host_id if index_exists? :tokens, :host_id # was unique
     add_index :tokens, :host_id
     add_foreign_key :tokens, :hosts, :name => "tokens_host_id_fk" unless foreign_key_exists?(:tokens, { :name => "tokens_host_id_fk" })
-    add_column :tokens, :type, :string, default: 'Token::Build', null: false, index: true
+    add_column :tokens, :type, :string, default: 'Token::Build', null: false, index: true  unless column_exists? :tokens, :type
     change_column :tokens, :value, :string, limit: 900
   end

but then the following error observed

ActiveRecord::StatementInvalid: Mysql2::Error: Specified key was too long; max key length is 767 bytes: ALTER TABLE `tokens` CHANGE `value` `value` varchar(900) DEFAULT NULL

#5 Updated by Julian Todt over 4 years ago

Okay, but this error is from the next line where we change the string limit for the token (as the new tokens are significantly longer), and the error makes kind of sense as mysql has a maximum vor `varchar(255)` and we want a `varchar(900)`. The length of the generated token depends on the certname (=> hostname or uuid), as hostnames can be up to 255 chars long, tokens can be longer than that, meaning we would have to use a TEXT field instead of a VARCHAR for the value of the token on mysql. Could you try this and see if that works? (Still not sure why the `unless column_exists?` fixes the former problem as the column should not exist...)

#6 Updated by Julian Todt over 4 years ago

Length of the varchar-field also doesn't seem to be the issue as noticed in a bug report in IRC today.

schlitzer:

12:21
hey, i just treid to upgrade to foreman 1.20: and got this error while running foreman-rake db:migrate
12:21
Mysql2::Error: Duplicate column name 'type': ALTER TABLE `tokens` ADD `type` varchar(255) DEFAULT 'Token::Build' NOT NULL
13:00
i think i solved the issue by running the following sql on my database: "ALTER TABLE tokens DROP COLUMN type;"
13:01
after this the db::migrate was successful, but then "foreman-rake db:seed" failed, because there where duplicate entries
13:02
is guess the problem is that i have a redundant foreman setup, with 3 foreman hosts, using the same database, i guess while running yum update, all of then where running db::migrate and db::seed at the same time, and something got messed up
14:54
Jjuliantodt, you are right, i also had to manually issue this sql: ALTER TABLE `tokens` CHANGE `value` `value` varchar(900) DEFAULT NULL, followed by issuing the drop statement a second time

juliantodt (@juliantodt:matrix.org)

15:04
schlitzer: You're running mysql, right? Because as far as I understood 255 is the biggest possible length for varchar in mysql and we would have had to use text there to fix it. I definitely need to take another look at this.

schlitzer

15:06
yes, mysql
15:06
or mariadb, not sure... i am not the admin of the mysql stuff
15:08
just inspected the table structure, and it is varchar(900) utf8_unicode_ci
15:08
10.0.35-MariaDB
15:08
so not a mysql
S
15:09
but i guess no one is really using a real mysql anymore, afaik all distros switched to mariadb

#7 Updated by Tomer Brisker almost 4 years ago

Category set to DB migrations
Found in Releases 1.20.0 added
Found in Releases deleted (~~Nightly~~)

#8 Updated by The Foreman Bot almost 4 years ago

Assignee set to Tomer Brisker
Status changed from New to Ready For Testing
Pull request https://github.com/theforeman/foreman/pull/6862 added

#9 Updated by Tomer Brisker over 3 years ago

Target version set to 1.20.3

#10 Updated by Lukas Zapletal over 3 years ago

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.

So the proper fix is to make the value shorter so it fits - do we really need it 900 characters long? Why this was added? Looks like 30face9 introduced it, I can't tell why Puppet CA has such a long tokens.

#11 Updated by Lukas Zapletal over 3 years ago

Fixed in Releases 1.23.0 added

#12 Updated by Tomer Brisker over 3 years ago

Fixed in Releases 1.20.3, 1.21.4, 1.22.1 added

#13 Updated by Tomer Brisker over 3 years ago

Status changed from Ready For Testing to Closed

Applied in changeset 8e4b08538945afe6aaef5e22b39b53777af767bc.

Also available in: Atom PDF

Project

General

Profile

Issues

Custom queries