Bug 1434336
| Summary: | traceback when showing list of activation keys: db schema issue | ||
|---|---|---|---|
| Product: | Red Hat Satellite 5 | Reporter: | Jan Hutař <jhutar> |
| Component: | Server | Assignee: | Jan Dobes <jdobes> |
| Status: | CLOSED CURRENTRELEASE | QA Contact: | Lukáš Hellebrandt <lhellebr> |
| Severity: | medium | Docs Contact: | |
| Priority: | unspecified | ||
| Version: | 570 | CC: | dyordano, lhellebr, tkasparek, tlestach |
| Target Milestone: | --- | ||
| Target Release: | --- | ||
| Hardware: | Unspecified | ||
| OS: | Unspecified | ||
| Whiteboard: | |||
| Fixed In Version: | spacewalk-schema-2.5.1-43 satellite-schema-5.8.0.25-1 | Doc Type: | If docs needed, set a value |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2017-06-21 12:07:46 UTC | Type: | Bug |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
| Bug Depends On: | |||
| Bug Blocks: | 1340444 | ||
WebUI ISE reported as bug 1434339 This can be fixed by setting multiple unique indexes on PostgreSQL, each index will be limited by where clauses on nullable columns. Another and bigger problem is to upgrade and clean existing rhnset table which can already contain duplicate records. first piece of the fix: f7aa18b9078f1816372e8026412498eae0bce059 upgrade on postgresql: e6dd4b91befcaba99d70ec9b450142066b49d007 fixing upgrade on PostgreSQL 8.4, spacewalk master: 444685cea65c39574812765805e1e386885fb4cf Verified with spacewalk-schema-2.5.1-48 and satellite-schema-5.8.0.31-1 with ManagedDB. It is no more possible to have duplicate rows in rhnschema: spacesatschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); INSERT 0 1 spacesatschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); ERROR: duplicate key value violates unique constraint "rhn_set_user_label_elem_unq" DETAIL: Key (user_id, label, element)=(1, activation_keys, 123) already exists. |
Description of problem: It is possible to get into the state when showing list of activation keys produces ISE. This issue is about schema flaw on PostgreSQL DB backend. Note the issue was discovered by pstudeni and investigated by jdobes. Version-Release number of selected component (if applicable): satellite-schema-5.7.0.27-1.el6sat.noarch (but others are affected as well) How reproducible: We were not able to reproduce it besides one case Steps to Reproduce: 1. Unreliable reproducer: a. Delete lots (hundreds) of activation keys via spacecmd: # spacecmd -s ... -u ... -p ... activationkey_list \ | grep "^1-" \ | xargs spacecmd -s ... -u ... -p ... activationkey_delete -y b. During it is deleting, refresh Systems -> Activation keys multiple times 2. Reliable, but not real-world reproducer: a. To see the issue, lets break directly the DB: rhnschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); rhnschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); rhnschema=# INSERT INTO rhnset (user_id, label, element) VALUES (1, 'activation_keys', 123); b. Show duplicate rows: rhnschema=# select user_id, label, element, count(*) from rhnset group by user_id, label, element having count(*) > 1; user_id | label | element | count ---------+-----------------+---------+------- 1 | activation_keys | 123 | 3 (1 row) c. Check ISE on Systems -> Activation Keys d. Fix it: rhnschema=# DELETE FROM rhnset WHERE user_id = 1 AND label = 'activation_keys' AND element = 123; DELETE 3 Actual results: Problem is that rhnset have unique constraint from multiple columns and some of them ("element_two" and "element_three") does not have "not null" configured: rhnschema=# \d rhnset Table "public.rhnset" Column | Type | Modifiers ---------------+-----------------------+----------- user_id | numeric | not null label | character varying(32) | not null element | numeric | not null element_two | numeric | element_three | numeric | Indexes: "rhn_set_user_label_elem_unq" UNIQUE CONSTRAINT, btree (user_id, label, element, element_two, element_three) Check constraints: "vn_rhnset_label" CHECK (label::text <> ''::text) Foreign-key constraints: "rhn_set_user_fk" FOREIGN KEY (user_id) REFERENCES web_contact(id) ON DELETE CASCADE Expected results: It should not be possible to insert duplicate rows into the table.