Skip to content
This repository has been archived by the owner on Jan 15, 2019. It is now read-only.

[dev.icinga.com #3870] (Oracle) Unique key missing for nsm_user_preference #1058

Closed
icinga-migration opened this issue Mar 17, 2013 · 8 comments

Comments

@icinga-migration
Copy link

This issue has been migrated from Redmine: https://dev.icinga.com/issues/3870

Created by mfrosch on 2013-03-17 10:29:22 +00:00

Assignee: Tommi
Status: Resolved (closed on 2013-03-25 13:51:22 +00:00)
Target Version: 1.8.3
Last Update: 2013-03-25 13:51:22 +00:00 (in Redmine)

Icinga Version: 1.8.4
Icinga Web Version: 1.8.2
IDO Version: 1.8.4
OS Version: Debian/wheezy
DB Type: Oracle
DB Version: 11.2
Browser Version: ---

A unique key on the table nsm_user_preference is missing connecting the fields (upref_user_id, upref_key)

This is a deferred issue for Oracle - which we can't provide a quick fix for 1.8.3

@tommi: Could you please update the oracle schemas for this:
https://git.icinga.org/?p=icinga-web.git;a=commit;h=aa24c311284e85a5fd4eba19ab3562afd34eea82

Attachments

  • test_3870.sql Tommi - 2013-03-18 20:07:37 +00:00 - Test SQL script
  • test_3870_1.log Tommi - 2013-03-18 20:07:37 +00:00 - failed removing of duplicates based on upref_modified
  • test_3870_2.log Tommi - 2013-03-18 20:07:37 +00:00 - version using upref_id looks OK

Changesets

2013-03-17 19:51:09 +00:00 by Tommi 354ffde

Oracle: add new index on nsm_user_preferences and delete duplicates #3870
Refs #3870 #3868

2013-03-17 20:13:59 +00:00 by Tommi 139a247

Oracle: add new unique index on nsm_user_preferences to oracle.sql #3870
Refs #3870 #3868

2013-03-18 11:19:38 +00:00 by Tommi f155ebb

Oracle: bump db_version to 1.8.3 in oracle.sql #3870
Refs #3870 #3868

2013-03-18 19:46:45 +00:00 by Tommi 817a7587796343cbad0bd0a462f974e0e6b30ee3

Oracle: nsm_user_preference use upref_id instead of upref_modified for duplicate check #3870
Refs #3870 #3868

2013-03-25 13:50:27 +00:00 by Tommi c2e3183

Oracle: add new unique index on nsm_user_preferences to oracle.sql #3870
Refs #3870 #3868

2013-03-25 13:50:27 +00:00 by Tommi 1a237b3

Oracle: add new index on nsm_user_preferences and delete duplicates #3870
Refs #3870 #3868

2013-03-25 13:50:28 +00:00 by Tommi 88cfa27

Oracle: nsm_user_preference use upref_id instead of upref_modified for duplicate check #3870
Refs #3870 #3868

2013-03-25 13:50:28 +00:00 by Tommi 1f6681c

Oracle: bump db_version to 1.8.3 in oracle.sql #3870
Refs #3870 #3868

2013-03-25 15:17:30 +00:00 by Tommi e56097a

Oracle: nsm_user_preference use upref_id instead of upref_modified for duplicate check #3870
Refs #3870 #3868

Relations:

@icinga-migration
Copy link
Author

Updated by Tommi on 2013-03-17 14:14:20 +00:00

  • DB Type changed from UNKNOWN to Oracle
  • DB Version changed from --- to 11

Pls provide an export of this table containing duplicates to remove for tests . SQL script with inserts exported from mysql is sufficient.

@icinga-migration
Copy link
Author

Updated by Tommi on 2013-03-17 20:38:33 +00:00

  • Status changed from Assigned to Feedback
  • Done % changed from 0 to 100

new v1.8.3 update and changed creation script committed in branch tdressler/issue_3870. only syntactical tested. pls check esp. deduplication correctness

@icinga-migration
Copy link
Author

Updated by mfrosch on 2013-03-18 12:33:43 +00:00

  • Target Version changed from 1.9 to 1.8.3

Hey Tommi,
thanks for quick response.

Here is a snippet to create a duplicated var:

-- call this multiple times
INSERT INTO nsm_user_preference (upref_user_id, upref_key, upref_val) VALUES
(1, 'org.icinga.ext.appstate', '{}');

Could you make a quick test? I'd like to include it in 1.8.3 ;-)

I'm lacking a Oracle environment as of now.

Cheers
Markus

@icinga-migration
Copy link
Author

Updated by mfrosch on 2013-03-18 12:33:52 +00:00

  • Status changed from Feedback to Assigned

@icinga-migration
Copy link
Author

Updated by Tommi on 2013-03-18 20:07:37 +00:00

  • File added test_3870.sql
  • File added test_3870_1.log
  • File added test_3870_2.log
  • Status changed from Assigned to Feedback

Test done as suggested. This opens a problem with your duplication check logic. if the modified time is the same (which i have when inserting the rows in a short time) double rows are left in the table. see test_3870_1.log. I changed the logic to use the upref_id, which is based on a sequence and the last value wins. This looks fine,see test_3870_2.sql. but i am not sure: if an existing entry can be modified after insert, than this logic is wrong too.

@icinga-migration
Copy link
Author

Updated by mfrosch on 2013-03-18 20:17:01 +00:00

You are right!

I'll think over it tomorrow...

The other update scripts have to be fixed as well, at least they should be as clean as possible!

@icinga-migration
Copy link
Author

Updated by mfrosch on 2013-03-25 13:47:59 +00:00

  • Status changed from Feedback to Assigned

From my thoughts the switch to upref_id should be sufficent.

We are trying to cleanup the data as best as possible, I can't think of a better way to approach this.

The only field that really causes that problem is the appstate, and it should be good enough like this.

@icinga-migration
Copy link
Author

Updated by mfrosch on 2013-03-25 13:51:22 +00:00

  • Status changed from Assigned to Resolved

Merged into r1.8

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests

1 participant