Feature #17824
closed
multiple identical database entries on table locations_organizations
Description
We would like to use a galera replication cluster as database backend. Replication for tables without primary key is not supported.
Therefore we created (among others) a composite key on the table locations_organizations. We tried to create a new organization/location assignment and got a a duplicate key error.
After that we undid our change (dropped this primary key), emptied the table locations_organizations, recreated the same organization/location assignment and examined the table: Two identical entries were created.
location_id organization_id
3 5
3 5
Updated by Dominic Cleal over 7 years ago
- Category set to Organizations and Locations
Updated by Karlis Melderis over 7 years ago
I can extend this list of what I get when I tried to enable PostgreSQL BDR for foreman database
Seems like lot of tables are created without primary key.
WARNING: Table public.operatingsystems_ptables has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.architectures_operatingsystems has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.schema_migrations has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.media_operatingsystems has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.foreman_tasks_tasks has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.katello_environment_priors has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.operatingsystems_provisioning_templates has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.dynflow_schema_info has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.dynflow_delayed_plans has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.katello_content_view_filters_repositories has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.features_smart_proxies has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.operatingsystems_puppetclasses has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.locations_organizations has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
Eileen Gray wrote:
We would like to use a galera replication cluster as database backend. Replication for tables without primary key is not supported.
Therefore we created (among others) a composite key on the table locations_organizations. We tried to create a new organization/location assignment and got a a duplicate key error.
After that we undid our change (dropped this primary key), emptied the table locations_organizations, recreated the same organization/location assignment and examined the table: Two identical entries were created.
location_id organization_id
3 5
3 5
Updated by Karlis Melderis over 7 years ago
This is what I added in postgreSQL but couldn't replicate same duplicate issue
ALTER TABLE foreman_tasks_tasks ADD PRIMARY KEY (id);
ALTER TABLE operatingsystems_ptables ADD PRIMARY KEY (ptable_id);
ALTER TABLE architectures_operatingsystems ADD PRIMARY KEY (architecture_id, operatingsystem_id);
ALTER TABLE schema_migrations ADD PRIMARY KEY (version);
ALTER TABLE media_operatingsystems ADD PRIMARY KEY (medium_id, operatingsystem_id);
ALTER TABLE katello_environment_priors ADD PRIMARY KEY (environment_id, prior_id);
ALTER TABLE operatingsystems_provisioning_templates ADD PRIMARY KEY (provisioning_template_id, operatingsystem_id);
ALTER TABLE dynflow_schema_info ADD PRIMARY KEY (version);
ALTER TABLE dynflow_delayed_plans ADD PRIMARY KEY (execution_plan_uuid);
ALTER TABLE katello_content_view_filters_repositories ADD PRIMARY KEY (content_view_filter_id, repository_id);
ALTER TABLE features_smart_proxies ADD PRIMARY KEY (smart_proxy_id,feature_id);
ALTER TABLE operatingsystems_puppetclasses ADD PRIMARY KEY (puppetclass_id, operatingsystem_id);
ALTER TABLE locations_organizations ADD PRIMARY KEY (location_id, organization_id);
Karlis Melderis wrote:
I can extend this list of what I get when I tried to enable PostgreSQL BDR for foreman database
Seems like lot of tables are created without primary key.WARNING: Table public.operatingsystems_ptables has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.architectures_operatingsystems has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.schema_migrations has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.media_operatingsystems has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.foreman_tasks_tasks has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.katello_environment_priors has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.operatingsystems_provisioning_templates has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.dynflow_schema_info has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.dynflow_delayed_plans has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.katello_content_view_filters_repositories has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.features_smart_proxies has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.operatingsystems_puppetclasses has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.
WARNING: Table public.locations_organizations has no PRIMARY KEY
HINT: Tables without a PRIMARY KEY cannot be UPDATEd or DELETEd from, only INSERTed into. Add a PRIMARY KEY.Eileen Gray wrote:
We would like to use a galera replication cluster as database backend. Replication for tables without primary key is not supported.
Therefore we created (among others) a composite key on the table locations_organizations. We tried to create a new organization/location assignment and got a a duplicate key error.
After that we undid our change (dropped this primary key), emptied the table locations_organizations, recreated the same organization/location assignment and examined the table: Two identical entries were created.
location_id organization_id
3 5
3 5
Updated by Eileen Gray over 7 years ago
yes, we also noticed that there are multiple tables without primary key. We assumed a composite key would do no harm, since it would consist of all the columns of the table. The table locations_organizations was the first table to test for problems and we unexpectedly got this duplicate key error when connecting an existing organization and an existing location.
For further investigation I now deleted not only the location/organization connection as above (emptied the locations_organizations table), but also deleted the organization and location. I recreated the organization and location and connected the both. This time no duplicate entries were created.
Sorry, I don't know what went wrong the first time, but thanks for looking into this. I suppose this issue can be rejected as not reproducible.
Updated by Karlis Melderis over 7 years ago
I don't think it's good to close the case.
Everyone would benefit if primary keys will get added to all tables by default.
aka no tweaks to work with galera.
Eileen Gray wrote:
We would like to use a galera replication cluster as database backend. Replication for tables without primary key is not supported.
Therefore we created (among others) a composite key on the table locations_organizations. We tried to create a new organization/location assignment and got a a duplicate key error.
After that we undid our change (dropped this primary key), emptied the table locations_organizations, recreated the same organization/location assignment and examined the table: Two identical entries were created.
location_id organization_id
3 5
3 5