Foren

[Resolved] Can not insert duplicate key row in object 'User_' with unique

Ram A, geändert vor 11 Jahren.

[Resolved] Can not insert duplicate key row in object 'User_' with unique

Junior Member Beiträge: 76 Beitrittsdatum: 16.01.13 Neueste Beiträge
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
thumbnail
Jan Geißler, geändert vor 11 Jahren.

RE: Can't insert duplicate key row in object 'User_' with unique index 'IX_

Liferay Master Beiträge: 735 Beitrittsdatum: 05.07.11 Neueste Beiträge
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
Ram A, geändert vor 11 Jahren.

Can not insert duplicate key row in object 'User_' with unique index 'IX_6

Junior Member Beiträge: 76 Beitrittsdatum: 16.01.13 Neueste Beiträge
Jan Geißler:
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
Ram A, geändert vor 11 Jahren.

Can not insert duplicate key row in object 'User_' with unique index 'IX_61

Junior Member Beiträge: 76 Beitrittsdatum: 16.01.13 Neueste Beiträge
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
thumbnail
Amit Doshi, geändert vor 11 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

Liferay Master Beiträge: 550 Beitrittsdatum: 29.12.10 Neueste Beiträge
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.


final SchedulerEntry SCHEDULER_ENTRY = new SchedulerEntryImpl();
if (SchedulerEngineUtil.getScheduledJobs(LDAPImportMessageListener.class.getName(),StorageType.MEMORY_CLUSTERED).size() > 0)
				{
					SchedulerEngineUtil.delete(LDAPImportMessageListener.class.getName(), LDAPImportMessageListener.class.getName(),StorageType.MEMORY_CLUSTERED);

				}
				//LdapSchedulerUtility._log.warn("After Deleting Entry for LDAP " + SchedulerEngineUtil.getScheduledJobs(LDAPImportMessageListener.class.getName()).size());
				SCHEDULER_ENTRY.setDescription(LDAPConstants.DESCRIPTION);
				SCHEDULER_ENTRY.setTriggerType(TriggerType.CRON);
				SCHEDULER_ENTRY.setEventListenerClass(LDAPImportMessageListener.class.getName());
				SCHEDULER_ENTRY.setTriggerValue(cronExpression);
				SchedulerEngineUtil.schedule(SCHEDULER_ENTRY,StorageType.MEMORY_CLUSTERED, null, -1);


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)


<scheduler-entry>
			<scheduler-event-listener-class>com.liferay.portlet.admin.messaging.LDAPImportMessageListener</scheduler-event-listener-class>
			<trigger>
				<simple>
					<property-key>ldap.import.interval</property-key>
					<time-unit>minute</time-unit>
				</simple>
			</trigger>
		</scheduler-entry>



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
thumbnail
Amit Doshi, geändert vor 11 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

Liferay Master Beiträge: 550 Beitrittsdatum: 29.12.10 Neueste Beiträge
RAM A :
In first approach you are creating Schedular Entry.Where we have to create that one ?


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


What will happen if we delete ldap.import.interval=20 from portal-ext.properties?
Does liferay takes any default value as interval duration to import the users.


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


While importing the users from ldap Liferay is updating the DB right?
Does it update the liferay DB or insert the imported users into Liferay DB ?

if it updates the Liferay User_ DB ,is it bulk update or updates user records one by one?


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
Ram A, geändert vor 11 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

Junior Member Beiträge: 76 Beitrittsdatum: 16.01.13 Neueste Beiträge
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
Ram A, geändert vor 11 Jahren.

Can not insert duplicate key row in object 'User_' with unique index 'Ix615

Junior Member Beiträge: 76 Beitrittsdatum: 16.01.13 Neueste Beiträge
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
thumbnail
Amit Doshi, geändert vor 11 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

Liferay Master Beiträge: 550 Beitrittsdatum: 29.12.10 Neueste Beiträge

Is this error is coming while adding the user in Liferay table User_ after importing the user from ldap(if user doesnot exist).?


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 user doesnot exist in ldap does it add the user in ldap and then imports that user to liferay?

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


Please let me know whether the table User_ exists in the ldap with the same structrure as it is in Liferay?


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

Thanks & Regards,
Amit Doshi
Ram A, geändert vor 11 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

Junior Member Beiträge: 76 Beitrittsdatum: 16.01.13 Neueste Beiträge
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
Ram A, geändert vor 11 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

Junior Member Beiträge: 76 Beitrittsdatum: 16.01.13 Neueste Beiträge
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
thumbnail
Sean Gildea, geändert vor 9 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

New Member Beiträge: 20 Beitrittsdatum: 15.12.12 Neueste Beiträge
I'm also encountering the same error when deploying a custom portlet through the sitemap.json inside my custom themes resources-importer.

I thought it was an instancing problem with my portlets inside sitemap.json but after setting <instanceable>true</instanceable> inside liferay-portlet.xml it still doesn't fix the problem. Here is a snippet of my custom portlet code inside sitemap.json

{
"publicPages": [
{
"friendlyURL": "/home",
"layoutTemplateId": "seventyfive",
"columns": [
[{ "portletId": "MainContent_WAR_MainContentportlet_INSTANCE_${groupId}",
"portletPreferences": { "showLocationAddress": "home" }}
],
[{ "portletId": "TwitterFeed_WAR_TwitterFeedportlet_INSTANCE_${groupId}" }]
],
"name": "Home",
"title": "Advantage Resourcing | Recruitment Services | Staffing Agency",
"type": "url",
"layouts" : [
thumbnail
Sean Gildea, geändert vor 9 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

New Member Beiträge: 20 Beitrittsdatum: 15.12.12 Neueste Beiträge
I figured out the solution. Instead of specifying the ${group_id} for a custom portlet, for each page instance, you must have a unique identifier.

For example for the associates page, I just append associates after "INSTANCE" and after the employers page, "employers", this way I never hit the duplicate key exception with regards to importing the sitemap.json.

Again, none of this is documented by liferay. They give you a very simple example and you have to figure this stuff out on your own ( gun to head ... )


{
"friendlyURL": "/associates/home",
"layoutTemplateId": "seventyfive",
"columns": [
[{ "portletId": "73" },
{ "portletId": "MainContent_WAR_MainContentportlet_INSTANCE_associates",
"portletPreferences": { "showLocationAddress": "associate" }}
],
["test.html"]
],
"name": "Associates"



Here's another example:


{
"publicPages": [
{
"friendlyURL": "/home",
"layoutTemplateId": "seventyfive",
"columns": [
[{ "portletId": "MainContent_WAR_MainContentportlet_INSTANCE_home",
"portletPreferences": { "showLocationAddress": "home" }}
],
[{ "portletId": "TwitterFeed_WAR_TwitterFeedportlet_INSTANCE_home" }]
],
"name": "Home",
"title": "Company name withheld",
"type": "url",
triveni yadaw, geändert vor 8 Jahren.

RE: Can not insert duplicate key row in object 'User_' with unique index 'I

New Member Beitrag: 1 Beitrittsdatum: 12.01.16 Neueste Beiträge
i have one problem that when i inserting the value in user_ table first time it is inserted but after that no other records are inserting.can any one solve this problem. it gerenrates the error like Cannot insert duplicate key row in object 'dbo.User_' with unique index 'IX_5ADBE171'.