[dev.icinga.com #2228] Please remove UNIQUE INDEX on user_email

[icinga-migration]

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

Created by tgelf on 2011-12-28 13:53:01 +00:00

Assignee: mhein
Status: Resolved (closed on 2012-02-29 10:55:25 +00:00)
Target Version: 1.6.2
Last Update: 2012-02-29 10:55:25 +00:00 (in Redmine)

---

There may be people using more than just one username not willing to create a new free mail account for each of them.

Changesets

2012-01-26 16:31:18 +00:00 by mhein 65edfd3

* removed unique index on email from nsm_user (fixes #2228)

---

Relations:

• relates #2378
• duplicates #1993

[icinga-migration]

Updated by ossmon on 2011-12-28 14:39:51 +00:00

FYI, i have already opened an issue for this problem (see #1993)

[icinga-migration]

Updated by mhein on 2012-01-20 10:55:04 +00:00

• Target Version set to 1.7

[icinga-migration]

Updated by mhein on 2012-01-20 10:56:01 +00:00

• Status changed from New to Closed

Using #2228. Thanks.

[icinga-migration]

Updated by mhein on 2012-01-20 10:56:31 +00:00

• Status changed from Closed to New

[icinga-migration]

Updated by mhein on 2012-01-26 16:22:34 +00:00

• Assigned to set to mhein

[icinga-migration]

Updated by mhein on 2012-01-26 16:31:46 +00:00

• Status changed from New to Resolved
• Done % changed from 0 to 100

Applied in changeset 65edfd3.

[icinga-migration]

Updated by mhein on 2012-02-14 16:40:39 +00:00

• Target Version changed from 1.7 to 1.6.2

[icinga-migration]

Updated by mfriedrich on 2012-02-28 14:00:02 +00:00

• Category set to Database & Queries
• Status changed from Resolved to Assigned
• Priority changed from Normal to Urgent

this is NOT fixed in r1.6 or master.

1. the upgrade scripts which ship the fix are wrongly named mysql_v1-6_to_v1-7.sql, should be 1.6.2 instead of 1.7 - mysql, pgsql, oracle

2. the schema creation sqls still contain the unique constraint

mysql

CREATE TABLE nsm_user (user_id INT AUTO_INCREMENT, user_account INT DEFAULT 0 NOT NULL, user_name VARCHAR(127) NOT NULL, user_lastname VARCHAR(40) NOT NULL, user_firstname VARCHAR(40) NOT NULL, user_password VARCHAR(64) NOT NULL, user_salt VARCHAR(64) NOT NULL, user_authsrc VARCHAR(45) DEFAULT 'internal' NOT NULL, user_authid VARCHAR(127), user_authkey VARCHAR(64), user_email VARCHAR(254) NOT NULL, user_disabled TINYINT DEFAULT '1' NOT NULL, user_created DATETIME NOT NULL, user_modified DATETIME NOT NULL, UNIQUE INDEX user_name_unique_idx (user_name), UNIQUE INDEX user_email_unique_idx (user_email), INDEX user_search_idx (user_name, user_authsrc, user_authid, user_disabled), PRIMARY KEY(user_id)) ENGINE = INNODB;
CREATE TABLE nsm_user_preference (upref_id INT AUTO_INCREMENT, upref_user_id INT NOT NULL, upref_val VARCHAR(100), upref_longval LONGTEXT, upref_key VARCHAR(50) NOT NULL, upref_created DATETIME NOT NULL, upref_modified DATETIME NOT NULL, INDEX upref_search_key_idx_idx (upref_key), INDEX principal_role_id_ix_idx (upref_user_id), PRIMARY KEY(upref_id)) ENGINE = INNODB;

pgsql

CREATE UNIQUE INDEX user_email_unique ON nsm_user (user_email);

oracle

CREATE TABLE nsm_user (user_id NUMBER(10), user_account NUMBER(10) DEFAULT 0 NOT NULL, user_name VARCHAR2(127) NOT NULL, user_lastname VARCHAR2(40) NOT NULL, user_firstname VARCHAR2(40) NOT NULL, user_password VARCHAR2(64) NOT NULL, user_salt VARCHAR2(64) NOT NULL, user_authsrc VARCHAR2(45) DEFAULT 'internal' NOT NULL, user_authid VARCHAR2(127), user_authkey VARCHAR2(64), user_email VARCHAR2(254) NOT NULL, user_disabled NUMBER(3) DEFAULT 1 NOT NULL, user_created DATE NOT NULL, user_modified DATE NOT NULL, PRIMARY KEY(user_id), CONSTRAINT user_unique UNIQUE (user_name), CONSTRAINT user_email_unique UNIQUE (user_email))

please fix accordingly!

[icinga-migration]

Updated by mfriedrich on 2012-02-29 09:30:15 +00:00

recent commit misses the pgsql script.

diff --git a/etc/schema/pgsql.sql b/etc/schema/pgsql.sql
index fbecff0..443be5b 100644
--- a/etc/schema/pgsql.sql
+++ b/etc/schema/pgsql.sql
@@ -25,7 +25,6 @@ CREATE UNIQUE INDEX cc_uid_UNIQUE ON cronk_category (cc_uid);
 CREATE INDEX pt_target_id_ix ON nsm_principal_target (pt_target_id);
 CREATE INDEX pt_principal_id_ix ON nsm_principal_target (pt_principal_id);
 CREATE UNIQUE INDEX user_name_unique ON nsm_user (user_name);
-CREATE UNIQUE INDEX user_email_unique ON nsm_user (user_email);
 CREATE INDEX user_search ON nsm_user (user_name, user_authsrc, user_authid, user_disabled);
 CREATE INDEX upref_search_key_idx ON nsm_user_preference (upref_key);
 CREATE INDEX principal_role_id_ix ON nsm_user_preference (upref_user_id);

[icinga-migration]

Updated by mhein on 2012-02-29 10:55:25 +00:00

• Status changed from Assigned to Resolved

Yes, got it. Next push the comes in.