Hi,

I am using Liferay 6.0.6.
I am getting the error "Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A' in my Liferay application.

When i checked the DB i found that Unique Non-clustered index is given for 'companyId' and 'emailAddress' columns in liferay "User_" table.
can anyone help me why unique index is given to those columns and does it affects any table or application if we delete unique index(by changing it to Non-Unique).
Please let us know when will the data regarding users be inserted in to User_ table from ldap.


Thanks in advance.
Ram

I really would discourage deleting that index. If you think about it it really makes sense. A can generally be identified by his/her email address. You only want one account per user per Portal instance. that's why there is the index companyId/email. The error obviously arises because you try to insert a email address which is already in the database. If you are trying to import users via ldap make sure you only import users, which are not already present.

Hope this helps.

Regards Jan

Thanks Jan for reply.It is helpful to me.

so you mean that this error is coming while importing all the users from ldap and trying to insert into liferay DB.
Please help me how to import the users via ldap which are not present in liferay only.

my ldap configuration in portal-ext.properties is :
ldap.import.enabled=true
ldap.import.interval=60
ldap.import.on.startup=true

But i am getting that error frequently and while deployment of my portlets also.see the below log

08:01:02,694 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:01:02,694 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:01:02,773 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:01:59,726 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:02:02,148 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:09:40,025 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:09:43,722 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:15:03,861 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:15:03,899 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:15:06,554 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.

Please help me why this is coming frequently and how to fix this.

Is there any possibility of getting this error if the user(who is not present in ldap) first time tries to login to the application?


Thanks in advance
Ram A

Hi,

ldap setting in my portal-ext.properties were given as follows.

ldap.import.enabled=true
ldap.import.interval=20
ldap.import.on.startup=true

Is anybody facing this type of issue.

Generally Liferay will import the users in 3 senarios
1.When user login to the application using ldap credentials then that logged in user details will be imported.
2.mass import on startup,
3.mass import on interval .

Is it compulsory to import all the users in all these senarios.
I think importing the user when he logged in and massimport on startup is enough.

Please help me to resolve this.


Thanks in advance
Ram A

Ram,

you are right, generally the user's get imported in all the above three scenarios.
But in one of my project, I have the same requirement as you have currently. But for that I have created a cron expression which use to run once a year and I think it's acceptable.
It can be done by two ways as per my knowledge :-

First Way

In order to block the last scenario "3.mass import on interval", I have done following thing.
1) LDAP Startup Action which extends simple action.
2) put the below code in it.



Checked any entry exist in the Scheduler if yes then delete it and create the new entry with the my custom cronexpression.

3) put it's entry in the portal-ext.properties application.startup.events=com.liferay.portal.events.AppStartupAction,com.amit.portal.events.LdapStartupAction

And your work is done.

Drawback
It will get called on every time server Startup.

Second Way
Create the liferay-portlet-ext.xml and modify below entry (instead of Simple , try cron expression on it)



NOTE :- I am not sure whether this is the best ways to do it or not. But it was few of them.

Let us know if any one done better way then this.

Thanks & Regards,
Amit Doshi

RAM A :


In LdapStartupAction file and it's entry in the portal-ext.properties, I already mentioned all the things for it in my previous post.



If you delete the entry from portal-ext.properties then it will take the default entry of portal.properties, by default it is ldap.import.interval=10



If exists then update the data and if not then insert the Data in Liferay DB.
Please refer the class PortalLDAPImporterImpl.java, it will solve your all question answers.

Thanks & REgards,
Amit Doshi

Thanks Amit for reply.

Actually say one batch application(or say whatsup monitor page of another application) is triggering our liferay application for every 1sec with screenname 'userA'.

Then Liferay is trying to insert that record in our Liferay User_ table as it does not present in DB.
At that time it is giving the below error.

08:01:02,694 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:01:02,694 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
08:01:02,773 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.

what i am thinking is Liferay trying to insert the record for 'userA' in User_ table with companyid 'C1', and emailAddress 'e1' from ldap.
And liferay User_ table may contain the record for user 'xxxx' with company id 'C1' and email address 'e1'. As it is having unique index on 'companyid' and 'emailAddress' it is giving the above mentioned error.

how can we find the email address of user from ldap ?

This error we are getting in PROD env not local env .Am i missing anything in local? can anyone suggest me.
Please help me to solve this issue.

Thanks in advance
Ram A

Hi,

I am getting this error in my Liferay application console log.

java.sql.BatchUpdateException: Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
at com.liferay.portal.dao.orm.hibernate.SessionImpl.flush(SessionImpl.java:115)
at com.liferay.portal.kernel.dao.orm.ClassLoaderSession.flush(ClassLoaderSession.java:176)
at com.liferay.portal.service.persistence.BatchSessionImpl.update(BatchSessionImpl.java:95)
at com.liferay.portal.service.persistence.BatchSessionUtil.update(BatchSessionUtil.java:49)
at com.liferay.portal.service.persistence.UserPersistenceImpl.updateImpl(UserPersistenceImpl.java:546)
at com.liferay.portal.service.persistence.UserPersistenceImpl.updateImpl(UserPersistenceImpl.java:1)
at com.liferay.portal.service.persistence.impl.BasePersistenceImpl.update(BasePersistenceImpl.java:254)
at com.liferay.portal.service.persistence.impl.BasePersistenceImpl.update(BasePersistenceImpl.java:274)
at com.liferay.portal.service.impl.MyUserLocalServiceImpl.addUser(MyUserLocalServiceImpl.java:422)
at sun.reflect.GeneratedMethodAccessor983.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at com.liferay.portal.service.UserLocalServiceUtil.addUser(UserLocalServiceUtil.java:291)
at com.liferay.portal.security.ldap.MyPortalLDAPImporterImpl.addUser(MyPortalLDAPImporterImpl.java:336)
at com.liferay.portal.security.ldap.MyPortalLDAPImporterImpl.importUser(MyPortalLDAPImporterImpl.java:674)
at com.liferay.portal.security.ldap.MyPortalLDAPImporterImpl.importFromLDAPByUser(MyPortalLDAPImporterImpl.java:497)
at com.liferay.portal.security.ldap.MyPortalLDAPImporterImpl.importFromLDAP(MyPortalLDAPImporterImpl.java:179)
at com.liferay.portal.security.ldap.PortalLDAPImporterImpl.importFromLDAP(PortalLDAPImporterImpl.java:41)
at com.liferay.portal.security.ldap.MyPortalLDAPImporterImpl.importFromLDAP(MyPortalLDAPImporterImpl.java:85)
at com.liferay.portal.security.ldap.PortalLDAPImporterUtil.importFromLDAP(PortalLDAPImporterUtil.java:30)
at com.liferay.portlet.admin.messaging.LDAPImportMessageListener.doReceive(LDAPImportMessageListener.java:27)
at com.liferay.portal.kernel.messaging.BaseMessageListener.receive(BaseMessageListener.java:25)
at sun.reflect.GeneratedMethodAccessor830.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.liferay.portal.kernel.bean.ClassLoaderBeanHandler.invoke(ClassLoaderBeanHandler.java:54)
at $Proxy238.receive(Unknown Source)
at com.liferay.portal.kernel.scheduler.messaging.SchedulerEventMessageListenerWrapper.receive(SchedulerEventMessageListenerWrapper.java:73)
at com.liferay.portal.kernel.messaging.InvokerMessageListener.receive(InvokerMessageListener.java:63)
at com.liferay.portal.kernel.messaging.ParallelDestination$1.run(ParallelDestination.java:63)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Is this error is coming while adding the user in Liferay table User_ after importing the user from ldap(if user doesnot exist).?
If user doesnot exist in ldap does it add the user in ldap and then imports that user to liferay? otherwise directly adds it in Liferay table ?
Please let me know whether the table User_ exists in the ldap with the same structrure as it is in Liferay?

Please help me solve this issue.


Thanks and Regards
Ram A

It's indexing issue try to do re-indexing from the Control panel - > Plugin Installation - press the re-index button for the user to re-index.


If you have an export functionality configured for ldap in liferay then it will export to ldap.



I think you don't have much knowledge for ldap. you required first to learn LDAP.

Thanks & Regards,
Amit Doshi

Hi Amit,

Thanks for reply.

yes i am new to Liferay and ldap .

10:31:45,637 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
10:31:53,964 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
10:33:26,601 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
10:34:48,579 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
10:34:48,607 ERROR [JDBCExceptionReporter:234] Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.

when i debug it i got the error like this :

12:47:50,047 DEBUG [JDBCExceptionReporter:225] Could not execute JDBC batch update [insert into User_ (uuid_, companyId, createDate, modifiedDate, defaultUser, contactId, password_, passwordEncrypted, passwordReset, passwordModifiedDate, digest, reminderQueryQuestion, reminderQueryAnswer, graceLoginCount, screenName, emailAddress, facebookId, openId, portraitId, languageId, timeZoneId, greeting, comments, firstName, middleName, lastName, jobTitle, loginDate, loginIP, lastLoginDate, lastLoginIP, lastFailedLoginDate, failedLoginAttempts, lockout, lockoutDate, agreedToTermsOfUse, active_, userId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
java.sql.BatchUpdateException: Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_615E9F7A'.
you mentioned the reindexing is the solution for the above issue.

It's indexing issue try to do re-indexing from the Control panel - > Plugin Installation - press the re-index button for the user to re-index.

for this i go to ControlPanel >> Plugins installation >> portlet plugins >> Users

i would like to know why this error is coming and what is this Reindex funnctionality and how it will help to me to solve the issue.

can you please help me .



Thanks in advance
Ram A

Hi,

This is resolved by adding the below property in portal-ext.properties file.

ldap.import.method=group.

I think before adding this property it is trying to import the all the users from ldap (eventhough some users are not related my application) .Because of some users have the duplicate email address with the other person it is giving the error while adding that user in liferay.

After adding this property it is importing the users from the required groups based on filtering .


Thanks all for your help in this issue.

Regards
Ram A