[AMBARI-19164] Add PK to hostcomponentdesiredstate Table To Support FK Relationships

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 2.5.0
• Component/s: ambari-server
• Assignee: Sebastian Toader
• Reporter: Sebastian Toader
• Created: 11/Dec/16 22:40
• Updated: 15/Dec/16 17:47
• Resolved: 15/Dec/16 16:31

Description

The hostcomponentdesiredstate table currently uses a compound PK based off of the cluster ID, service name, host id and component name. There are several problems with this approach:

• Primary Keys should be data that's not part of the business logic of the system and not subject to be changed potentially (as strings are).
• Other tables referencing the hostcomponentdesiredstate table would now need knowledge of cluster/service/component/host in order to make the correct FK association. This leads to extra data being tracked as well as data duplication.
• Some databases, such as SQL Server, have problems with the indexing of compound PKs and may lead to deadlocks when querying and updating concurrently.
  This table needs to be changed so that it uses a simple PK for referencing. FK relationships as they exist today can still be maintained as long as a UNIQUE constraint is placed on the table. We should:
• Add a UNIQUE constraint to the former PK columns
• Add an INDEX to the former PK columns

Issue Links

• links to: ReviewBoard