{"action":"created","issue":{"url":"https://api.github.com/repos/apache/ambari/issues/975","repository_url":"https://api.github.com/repos/apache/ambari","labels_url":"https://api.github.com/repos/apache/ambari/issues/975/labels{/name}","comments_url":"https://api.github.com/repos/apache/ambari/issues/975/comments","events_url":"https://api.github.com/repos/apache/ambari/issues/975/events","html_url":"https://github.com/apache/ambari/pull/975","id":313508400,"number":975,"title":"AMBARI-23552. Switch to using Surrogate PK in Ambari DB tables, wherever applicable.","user":{"login":"swapanshridhar","id":1879146,"avatar_url":"https://avatars3.githubusercontent.com/u/1879146?v=4","gravatar_id":"","url":"https://api.github.com/users/swapanshridhar","html_url":"https://github.com/swapanshridhar","followers_url":"https://api.github.com/users/swapanshridhar/followers","following_url":"https://api.github.com/users/swapanshridhar/following{/other_user}","gists_url":"https://api.github.com/users/swapanshridhar/gists{/gist_id}","starred_url":"https://api.github.com/users/swapanshridhar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/swapanshridhar/subscriptions","organizations_url":"https://api.github.com/users/swapanshridhar/orgs","repos_url":"https://api.github.com/users/swapanshridhar/repos","events_url":"https://api.github.com/users/swapanshridhar/events{/privacy}","received_events_url":"https://api.github.com/users/swapanshridhar/received_events","type":"User","site_admin":false},"labels":[{"id":808826919,"url":"https://api.github.com/repos/apache/ambari/labels/JPA","name":"JPA","color":"f9229c","default":false},{"id":867223182,"url":"https://api.github.com/repos/apache/ambari/labels/SQL","name":"SQL","color":"3c40ad","default":false}],"state":"open","locked":false,"assignee":{"login":"swapanshridhar","id":1879146,"avatar_url":"https://avatars3.githubusercontent.com/u/1879146?v=4","gravatar_id":"","url":"https://api.github.com/users/swapanshridhar","html_url":"https://github.com/swapanshridhar","followers_url":"https://api.github.com/users/swapanshridhar/followers","following_url":"https://api.github.com/users/swapanshridhar/following{/other_user}","gists_url":"https://api.github.com/users/swapanshridhar/gists{/gist_id}","starred_url":"https://api.github.com/users/swapanshridhar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/swapanshridhar/subscriptions","organizations_url":"https://api.github.com/users/swapanshridhar/orgs","repos_url":"https://api.github.com/users/swapanshridhar/repos","events_url":"https://api.github.com/users/swapanshridhar/events{/privacy}","received_events_url":"https://api.github.com/users/swapanshridhar/received_events","type":"User","site_admin":false},"assignees":[{"login":"swapanshridhar","id":1879146,"avatar_url":"https://avatars3.githubusercontent.com/u/1879146?v=4","gravatar_id":"","url":"https://api.github.com/users/swapanshridhar","html_url":"https://github.com/swapanshridhar","followers_url":"https://api.github.com/users/swapanshridhar/followers","following_url":"https://api.github.com/users/swapanshridhar/following{/other_user}","gists_url":"https://api.github.com/users/swapanshridhar/gists{/gist_id}","starred_url":"https://api.github.com/users/swapanshridhar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/swapanshridhar/subscriptions","organizations_url":"https://api.github.com/users/swapanshridhar/orgs","repos_url":"https://api.github.com/users/swapanshridhar/repos","events_url":"https://api.github.com/users/swapanshridhar/events{/privacy}","received_events_url":"https://api.github.com/users/swapanshridhar/received_events","type":"User","site_admin":false}],"milestone":null,"comments":4,"created_at":"2018-04-11T22:21:13Z","updated_at":"2018-04-14T00:38:46Z","closed_at":null,"author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/apache/ambari/pulls/975","html_url":"https://github.com/apache/ambari/pull/975","diff_url":"https://github.com/apache/ambari/pull/975.diff","patch_url":"https://github.com/apache/ambari/pull/975.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\n- # Problem Statement:\r\n\r\nThe {{clusterservices}} table was given a new surrogate, auto-incrementing primary key:\r\n\r\n\r\n```\r\n@Entity\r\n@TableGenerator(name = \"service_id_generator\",\r\n  table = \"ambari_sequences\", pkColumnName = \"sequence_name\", valueColumnName = \"sequence_value\"\r\n  , pkColumnValue = \"service_id_seq\"\r\n  , initialValue = 1\r\n)\r\n```\r\n\r\n\r\nHowever, the table doesn't use this for its PK. Instead, it combines it with 2 other columns. This would allow a single Service Group to be a part of 2 clusters and still be considered unique (which is incorrect). Compound PKs also present a problem in slower cloud-based databases as they can cause table locks on read which lead to deadlocks in the database:\r\n\r\n\r\n```\r\nCREATE TABLE clusterservices (\r\n  id BIGINT NOT NULL,\r\n  service_name VARCHAR(255) NOT NULL,\r\n  service_type VARCHAR(255) NOT NULL,\r\n  cluster_id BIGINT NOT NULL,\r\n  service_group_id BIGINT NOT NULL,\r\n  service_enabled INTEGER NOT NULL,\r\n  CONSTRAINT PK_clusterservices PRIMARY KEY (id, service_group_id, cluster_id),\r\n  CONSTRAINT UQ_service_id UNIQUE (id),\r\n  CONSTRAINT FK_clusterservices_cluster_id FOREIGN KEY (service_group_id, cluster_id) REFERENCES servicegroups (id, cluster_id));\r\n\r\n```\r\n\r\nBy not using the surrogate PK, we also cause other tables, like {{serviceconfig}} to have to create compound FKs as well:\r\n\r\n\r\n`  CONSTRAINT FK_serviceconfig_clstr_svc FOREIGN KEY (service_id, service_group_id, cluster_id) REFERENCES clusterservices (id, service_group_id, cluster_id),`\r\n\r\n\r\nThis should just be a single FK to the surrogate ID.\r\n\r\nSame for some other other tables, too, like {{servicegroups}}:\r\n\r\n\r\n```\r\nCREATE TABLE servicegroups (\r\n  id BIGINT NOT NULL,\r\n  service_group_name VARCHAR(255) NOT NULL,\r\n  cluster_id BIGINT NOT NULL,\r\n  CONSTRAINT PK_servicegroups PRIMARY KEY (id, cluster_id),\r\n  CONSTRAINT FK_servicegroups_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id));\r\n\r\n```\r\n\r\nIt uses a surrogate auto-incrementing ID, but it's PK is a compound.\r\n\r\n\r\n- # Fix: \r\n\r\nUpdate code to used Surrogate for PK, and updated the relevant foreign references to them from other tables.\r\n\r\n\r\n## How was this patch tested?\r\n\r\n- Fixed few UTs\r\n- Ran UTs corresponding to the changes.\r\n- Tested on cluster.\r\n"},"comment":{"url":"https://api.github.com/repos/apache/ambari/issues/comments/381290755","html_url":"https://github.com/apache/ambari/pull/975#issuecomment-381290755","issue_url":"https://api.github.com/repos/apache/ambari/issues/975","id":381290755,"user":{"login":"asfgit","id":1341245,"avatar_url":"https://avatars1.githubusercontent.com/u/1341245?v=4","gravatar_id":"","url":"https://api.github.com/users/asfgit","html_url":"https://github.com/asfgit","followers_url":"https://api.github.com/users/asfgit/followers","following_url":"https://api.github.com/users/asfgit/following{/other_user}","gists_url":"https://api.github.com/users/asfgit/gists{/gist_id}","starred_url":"https://api.github.com/users/asfgit/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/asfgit/subscriptions","organizations_url":"https://api.github.com/users/asfgit/orgs","repos_url":"https://api.github.com/users/asfgit/repos","events_url":"https://api.github.com/users/asfgit/events{/privacy}","received_events_url":"https://api.github.com/users/asfgit/received_events","type":"User","site_admin":false},"created_at":"2018-04-14T00:38:46Z","updated_at":"2018-04-14T00:38:46Z","author_association":"NONE","body":"\nRefer to this link for build results (access rights to CI server needed): \nhttps://builds.apache.org/job/Ambari-Github-PullRequest-Builder/1854/\nTest FAILed.\nTest FAILured.\n"}}
{"actor":{"display_login":"asfgit"}}