1375029 – db:migrate failure during upgrade from 3.2 to 4.1

Bug 1375029 - db:migrate failure during upgrade from 3.2 to 4.1

Summary: db:migrate failure during upgrade from 3.2 to 4.1

Keywords:
Status:	CLOSED CURRENTRELEASE
Alias:	None
Product:	Red Hat CloudForms Management Engine
Classification:	Red Hat
Component:	Appliance
Sub Component:
Version:	5.6.0
Hardware:	x86_64
OS:	Linux
Priority:	high
Severity:	high
Target Milestone:	GA
Target Release:	5.7.0
Assignee:	Nick Carboni
QA Contact:	luke couzens
Docs Contact:
URL:
Whiteboard:	upgrade:migration
Duplicates (1):	1367127 (view as bug list)
Depends On:
Blocks:	1377418
TreeView+	depends on / blocked

Reported:	2016-09-12 00:14 UTC by Colin Arnott
Modified:	2019-12-16 06:42 UTC (History)
CC List:	11 users (show)
Fixed In Version:	5.7.0.2
Doc Type:	If docs needed, set a value
Doc Text:
Clone Of:
Clones:	1377418 (view as bug list)
Environment:
Last Closed:	2017-01-11 19:55:55 UTC
Category:	---
Cloudforms Team:	---
Target Upstream Version:
Embargoed:

Attachments	(Terms of Use)

Description Colin Arnott 2016-09-12 00:14:10 UTC

Description of problem:
What problem/issue/behavior are you having trouble with?  What do you expect to see?

After running the DB migrate task as part of upgrading from 3.2 to 4.1 I have got an error PG::UniqueViolation: ERROR:  could not create unique index "miq_roles_features_pkey"

Version-Release number of selected component (if applicable):
cf-3.2
cf-4.1

How reproducible:
needs this database

Actual results:
see additional info

Expected results:
success

Additional info:
# bin/rake db:migrate
Full error: 
== 20160106214719 AddCompositePrimaryKeysToJoinTables: migrating ==============
-- execute("ALTER TABLE cloud_tenants_vms ADD PRIMARY KEY (cloud_tenant_id, vm_id)")
   -> 0.0040s
-- execute("ALTER TABLE conditions_miq_policies ADD PRIMARY KEY (miq_policy_id, condition_id)")
   -> 0.0059s
-- execute("ALTER TABLE configuration_locations_configuration_profiles ADD PRIMARY KEY (configuration_location_id, configuration_profile_id)")
   -> 0.0040s
-- execute("ALTER TABLE configuration_organizations_configuration_profiles ADD PRIMARY KEY (configuration_organization_id, configuration_profile_id)")
   -> 0.0037s
-- execute("ALTER TABLE configuration_profiles_configuration_tags ADD PRIMARY KEY (configuration_profile_id, configuration_tag_id)")
   -> 0.0036s
-- execute("ALTER TABLE configuration_tags_configured_systems ADD PRIMARY KEY (configured_system_id, configuration_tag_id)")
   -> 0.0038s
-- execute("ALTER TABLE container_groups_container_services ADD PRIMARY KEY (container_service_id, container_group_id)")
   -> 0.0056s
-- execute("ALTER TABLE customization_scripts_operating_system_flavors ADD PRIMARY KEY (customization_script_id, operating_system_flavor_id)")
   -> 0.0048s
-- execute("ALTER TABLE direct_configuration_profiles_configuration_tags ADD PRIMARY KEY (configuration_profile_id, configuration_tag_id)")
   -> 0.0033s
-- execute("ALTER TABLE direct_configuration_tags_configured_systems ADD PRIMARY KEY (configured_system_id, configuration_tag_id)")
   -> 0.0032s
-- execute("ALTER TABLE key_pairs_vms ADD PRIMARY KEY (authentication_id, vm_id)")
   -> 0.0033s
-- execute("ALTER TABLE miq_groups_users ADD PRIMARY KEY (miq_group_id, user_id)")
   -> 0.0040s
-- execute("ALTER TABLE miq_roles_features ADD PRIMARY KEY (miq_user_role_id, miq_product_feature_id)")
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UniqueViolation: ERROR:  could not create unique index "miq_roles_features_pkey"
DETAIL:  Key (miq_user_role_id, miq_product_feature_id)=(1000000000021, 1000000000059) is duplicated.
: ALTER TABLE miq_roles_features ADD PRIMARY KEY (miq_user_role_id, miq_product_feature_id)/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activesupport/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:845:in `block in method_missing'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:814:in `block in say_with_time'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:814:in `say_with_time'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:834:in `method_missing'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:24:in `block in up'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:23:in `each'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:23:in `up'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:791:in `exec_migration'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:772:in `block (2 levels) in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:771:in `block in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:770:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration/compatibility.rb:121:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:950:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1211:in `block in execute_migration_in_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1279:in `block in ddl_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/transactions.rb:211:in `transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1279:in `ddl_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1210:in `execute_migration_in_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1183:in `block in migrate_without_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1182:in `each'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1182:in `migrate_without_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1131:in `block in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1298:in `with_advisory_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1131:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1005:in `up'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:983:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  could not create unique index "miq_roles_features_pkey"
DETAIL:  Key (miq_user_role_id, miq_product_feature_id)=(1000000000021, 1000000000059) is duplicated.
: ALTER TABLE miq_roles_features ADD PRIMARY KEY (miq_user_role_id, miq_product_feature_id)
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activesupport/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:845:in `block in method_missing'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:814:in `block in say_with_time'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:814:in `say_with_time'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:834:in `method_missing'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:24:in `block in up'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:23:in `each'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:23:in `up'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:791:in `exec_migration'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:772:in `block (2 levels) in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:771:in `block in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:770:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration/compatibility.rb:121:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:950:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1211:in `block in execute_migration_in_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1279:in `block in ddl_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/transactions.rb:211:in `transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1279:in `ddl_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1210:in `execute_migration_in_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1183:in `block in migrate_without_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1182:in `each'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1182:in `migrate_without_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1131:in `block in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1298:in `with_advisory_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1131:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1005:in `up'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:983:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
PG::UniqueViolation: ERROR:  could not create unique index "miq_roles_features_pkey"
DETAIL:  Key (miq_user_role_id, miq_product_feature_id)=(1000000000021, 1000000000059) is duplicated.
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `block in execute'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:566:in `block in log'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activesupport/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:560:in `log'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb:97:in `execute'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:845:in `block in method_missing'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:814:in `block in say_with_time'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:814:in `say_with_time'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:834:in `method_missing'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:24:in `block in up'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:23:in `each'
/var/www/miq/vmdb/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb:23:in `up'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:791:in `exec_migration'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:772:in `block (2 levels) in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:771:in `block in migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:770:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration/compatibility.rb:121:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:950:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1211:in `block in execute_migration_in_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1279:in `block in ddl_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/transactions.rb:211:in `transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1279:in `ddl_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1210:in `execute_migration_in_transaction'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1183:in `block in migrate_without_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1182:in `each'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1182:in `migrate_without_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1131:in `block in migrate'
/opt/rh/cfme-gemFaiaset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1298:in `with_advisory_lock'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1131:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:1005:in `up'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/migration.rb:983:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/tasks/database_tasks.rb:161:in `migrate'
/opt/rh/cfme-gemset/bundler/gems/rails-3d9d4f56c1ee/activerecord/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'

Comment 4 tachoi 2016-09-12 10:23:19 UTC

Was able to recreated the issue in lab appBiance with customer provided db dump.
With some internal research(Case:01684595,bug: 1368192) now I am able to import customer db dump after truncating some tables.

psql -U root -d vmdb_production -c 'truncate miq_roles_features;'
psql -U root -d vmdb_production -c 'truncate time_profiles;'

Just want to get a confirmation from engineering to see if this is safe to proceed 

Taeho

Comment 6 Nick Carboni 2016-09-12 13:59:15 UTC

In nearly all cases it is not okay to just truncate tables.

In this case specifically that data will not be recreated and would be lost, corrupting the link between the miq_user_roles and miq_product_features table.

Is there an environment with the issue recreated that I can look at?

Comment 7 Nick Carboni 2016-09-12 18:18:49 UTC

The following three queries should fix the issue:

-- create a temp table of the duplicates
CREATE TEMP TABLE duplicate_miq_roles_features AS (
  SELECT miq_user_role_id, miq_product_feature_id
  FROM miq_roles_features
  GROUP BY miq_user_role_id, miq_product_feature_id
  HAVING COUNT(*) > 1
);

-- delete the duplicate rows
DELETE FROM miq_roles_features mrf
WHERE EXISTS(
  SELECT 1
  FROM duplicate_miq_roles_features dups
  WHERE
    dups.miq_user_role_id = mrf.miq_user_role_id
      AND
    dups.miq_product_feature_id = mrf.miq_product_feature_id
);  

-- insert one copy of each of the duplicate sets
INSERT INTO miq_roles_features(
  SELECT * FROM duplicate_miq_roles_features
);

Comment 8 Nick Carboni 2016-09-12 18:32:49 UTC

As a part of this fix I will look into removing the migration that adds composite keys to the join tables, as those will be removed in a later migration.

Initial migration to add composite keys: https://github.com/ManageIQ/manageiq/blob/master/db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb

Migration that removes composite keys: https://github.com/ManageIQ/manageiq/blob/master/db/migrate/20160406195810_add_id_primary_key_to_join_tables.rb

Because both changes (adding the migrations) are contained in the same set of releases it should be safe to remove the offsetting migrations.

Comment 9 CFME Bot 2016-09-12 19:26:15 UTC

https://github.com/ManageIQ/manageiq/pull/11197

Comment 10 CFME Bot 2016-09-14 18:55:55 UTC

New commit detected on ManageIQ/manageiq/master:
https://github.com/ManageIQ/manageiq/commit/3ed51d47b6231eb328aea5a423cc5ff9f35b98e8

commit 3ed51d47b6231eb328aea5a423cc5ff9f35b98e8
Author:     Nick Carboni <ncarboni>
AuthorDate: Mon Sep 12 15:12:01 2016 -0400
Commit:     Nick Carboni <ncarboni>
CommitDate: Mon Sep 12 15:15:55 2016 -0400

    Remove composite primary key migration logic with no net effect
    
    Originally, composite primary keys were added to join tables
    so that pglogical replication would work properly.
    
    This change was reverted and id primary keys were added in a following
    migration.
    
    Adding composite primary keys is now failing some migrations in
    live environments because rows in join tables seem to have duplicates
    in some cases. Removing this logic will allow those migrations to go
    much more smoothly.
    
    https://bugzilla.redhat.com/show_bug.cgi?id=1375029

 ...19_add_composite_primary_keys_to_join_tables.rb | 33 ----------------------
 ...0406195810_add_id_primary_key_to_join_tables.rb | 29 -------------------
 ...95810_add_id_primary_key_to_join_tables_spec.rb | 10 -------
 3 files changed, 72 deletions(-)
 delete mode 100644 db/migrate/20160106214719_add_composite_primary_keys_to_join_tables.rb

Comment 13 Nick Carboni 2016-09-21 13:43:01 UTC

*** Bug 1367127 has been marked as a duplicate of this bug. ***

Note You need to log in before you can comment on or make changes to this bug.