[gatein-issues] [JBoss JIRA] (GTNPORTAL-3489) [MembershipDAOImpl]ORA-00001: unique constraint (IDM.SYS_C0015551) violated

[ https://issues.jboss.org/browse/GTNPORTAL-3489?page=com.atlassian.jira.pl... ] Ahmed Zaoui commented on GTNPORTAL-3489: ---------------------------------------- some analysis because the problem seems to be more complicated than the issue itself: By activating the show sql log properties in hibernate test configuration file for oracle dialect: {code:xml} <description>Default Hibernate Service</description> <property name="hibernate.hbm2ddl.auto" value="create-drop"/> <property name="hibernate.show_sql" value="true"/> <property name="hibernate.use_sql_comments" value="true"/> {code} You will notice that this insert sql statement which caused unique constraint violated error: {code:sql} into jbid_io_rel (FROM_IDENTITY, NAME, TO_IDENTITY, REL_TYPE, ID) values (?, ?, ?, ?, ?) {code} {code:noformat} 18.06.2014 14:12:17 *ERROR* [main] SqlExceptionHelper: ORA-00001: unique constraint (GERAP.SYS_C0045636) violated (SqlExceptionHelper.java, line 144) Hibernate: /* insert org.picketlink.idm.impl.model.hibernate.HibernateIdentityObjectRelationship */ insert into jbid_io_rel (FROM_IDENTITY, NAME, TO_IDENTITY, REL_TYPE, ID) values (?, ?, ?, ?, ?) 18.06.2014 14:12:17 *WARN * [main] SqlExceptionHelper: SQL Error: 1, SQLState: 23000 (SqlExceptionHelper.java, line 143) 18.06.2014 14:12:17 *ERROR* [main] SqlExceptionHelper: ORA-00001: unique constraint (GERAP.SYS_C0045636) violated (SqlExceptionHelper.java, line 144) 18.06.2014 14:12:17 *ERROR* [main] PicketLinkIDMOrganizationServiceImpl: ORA-00001: unique constraint (GERAP.SYS_C0045636) violated {code} Normal behaviour and the reason is clear (an already column exist with the same composite unique key). to understand more the issue you should check the sql script to create jbid_io_rel table : generated script from oracle: {code:sql} Hibernate: create table jbid_io_rel ( ID number(19,0) not null, FROM_IDENTITY number(19,0) not null, NAME number(19,0), TO_IDENTITY number(19,0) not null, REL_TYPE number(19,0) not null, primary key (ID), unique (FROM_IDENTITY, NAME, TO_IDENTITY, REL_TYPE) ) {code} Show create table from mysql: {code:sql} jbid_io_rel | CREATE TABLE `jbid_io_rel` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `FROM_IDENTITY` bigint(20) NOT NULL, `NAME` bigint(20) DEFAULT NULL, `TO_IDENTITY` bigint(20) NOT NULL, `REL_TYPE` bigint(20) NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `FROM_IDENTITY` (`FROM_IDENTITY`,`NAME`,`TO_IDENTITY`,`REL_TYPE`), KEY `FKE9BC4F6C2D9E6CE8` (`REL_TYPE`), KEY `FKE9BC4F6C4EF6CBA4` (`NAME`), KEY `FKE9BC4F6CE5991E18` (`TO_IDENTITY`), KEY `FKE9BC4F6C89DEF9C9` (`FROM_IDENTITY`), CONSTRAINT `FKE9BC4F6C89DEF9C9` FOREIGN KEY (`FROM_IDENTITY`) REFERENCES `jbid_io` (`ID`), CONSTRAINT `FKE9BC4F6C2D9E6CE8` FOREIGN KEY (`REL_TYPE`) REFERENCES `jbid_io_rel_type` (`ID`), CONSTRAINT `FKE9BC4F6C4EF6CBA4` FOREIGN KEY (`NAME`) REFERENCES `jbid_io_rel_name` (`ID`), CONSTRAINT `FKE9BC4F6CE5991E18` FOREIGN KEY (`TO_IDENTITY`) REFERENCES `jbid_io` (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=latin1 | {code} So our constraint is : {code:sql} UNIQUE KEY `FROM_IDENTITY` (`FROM_IDENTITY`,`NAME`,`TO_IDENTITY`,`REL_TYPE`) {code} In fact what is considerate here as bug with oracle is the expected behaviour to keep the consistent state of the database, we add only a test case to ovoid this situation at java service layer before rejected from DB returning now to our test case that will try to insert the same association ( the same composite key (`FROM_IDENTITY`,`NAME`,`TO_IDENTITY`,`REL_TYPE`)) This is the result at database layer after running the attached test case: *WITH ORACLE* {noformat} +----+---------------+------+-------------+----------+ | ID | FROM_IDENTITY | NAME | TO_IDENTITY | REL_TYPE | +----+---------------+------+-------------+----------+ | 12 | 11 | NULL | 9 | 1 | | 21 | 9 | NULL | 14 | 1 | | 22 | 9 | 13 | 14 | 1 | +----+---------------+------+-------------+----------+ {noformat} With mysql : {noformat} +----+---------------+------+-------------+----------+ | ID | FROM_IDENTITY | NAME | TO_IDENTITY | REL_TYPE | +----+---------------+------+-------------+----------+ | 2 | 1 | NULL | 3 | 1 | | 73 | 1 | NULL | 39 | 1 | | 3 | 1 | 1 | 3 | 2 | | 74 | 1 | 1 | 39 | 2 | | 1 | 2 | NULL | 1 | 1 | | 72 | 2 | NULL | 38 | 1 | | 75 | 38 | NULL | 39 | 1 | | 76 | 38 | NULL | 39 | 1 | | 77 | 38 | 1 | 39 | 2 | +----+---------------+------+-------------+----------+ {noformat} As you can see there is no duplicated composite key with oracle as the constraint was respected However the same constraint was rejected with mysql and a duplicated composite entry is inserted (38,null,39,1) : {noformat} | 75 | 38 | NULL | 39 | 1 | | 76 | 38 | NULL | 39 | 1 | {noformat} *So one question begs to be answered: why this unique constraint is not respected with mysql and HSQL DB* *The response is :MySQL ignore null values on unique constraints* -- This message was sent by Atlassian JIRA (v6.2.6#6264)