Upgrading to 6.7 I get MySQL error: de.hybris.platform.jalo.JaloSystemException: org.springframework.dao.DuplicateKeyException: query; SQL []; Duplicate entry '8796158853642' for key 'PRIMARY';

[felipemonteiro_carvalho]

Hello,

I am upgrading a project from 6.3 to 6.7. Till 6.6 everything is fine. But when I try to upgrade 6.3->6.7 I get some very strange MySQL errors. Any ideas?

 ERROR [update-bsnDeIndex-cronJob::de.hybris.platform.servicelayer.internal.jalo.ServicelayerJob] (update-bsnDeIndex-cronJob) [Job] Caught throwable org.springframework.dao.DuplicateKeyException: query; SQL []; Duplicate entry '8796158853642' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '8796158853642' for key 'PRIMARY'
 de.hybris.platform.jalo.JaloSystemException: org.springframework.dao.DuplicateKeyException: query; SQL []; Duplicate entry '8796158853642' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '8796158853642' for key 'PRIMARY'[HY--1]
     at de.hybris.platform.persistence.GenericItemEJBImpl.createGenericItem(GenericItemEJBImpl.java:125)
     at de.hybris.platform.jalo.GenericItem.createItem(GenericItem.java:87)
     at de.hybris.platform.cronjob.jalo.GeneratedCronJobHistory.createItem(GeneratedCronJobHistory.java:109)
     at de.hybris.platform.jalo.Item.newInstanceInternal(Item.java:4136)
     at de.hybris.platform.jalo.Item.access$6(Item.java:4077)
     at de.hybris.platform.jalo.Item$19.execute(Item.java:4055)
     at de.hybris.platform.tx.Transaction.execute(Transaction.java:1192)
     at de.hybris.platform.tx.Transaction.execute(Transaction.java:1157)
     at de.hybris.platform.jalo.Item.newInstance(Item.java:4050)
     at de.hybris.platform.jalo.type.ComposedType.newInstance(ComposedType.java:1337)
     at de.hybris.platform.jalo.type.ComposedType.newInstance(ComposedType.java:1257)
     at de.hybris.platform.cronjob.jalo.CronJob.instantiateCronJobHistory(CronJob.java:2835)
     at de.hybris.platform.cronjob.jalo.CronJob.createCronJobHistory(CronJob.java:2815)
     at de.hybris.platform.cronjob.jalo.Job.execute(Job.java:1389)
     at de.hybris.platform.cronjob.jalo.Job.performImpl(Job.java:814)
     at de.hybris.platform.cronjob.jalo.Job.access$1(Job.java:767)
     at de.hybris.platform.cronjob.jalo.Job$JobRunable.run(Job.java:686)
     at de.hybris.platform.util.threadpool.PoolableThread.internalRun(PoolableThread.java:208)
     at de.hybris.platform.core.threadregistry.RegistrableThread.run(RegistrableThread.java:134)
 Caused by: de.hybris.platform.persistence.hjmp.HJMPException: org.springframework.dao.DuplicateKeyException: query; SQL []; Duplicate entry '8796158853642' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '8796158853642' for key 'PRIMARY'
     at de.hybris.platform.persistence.GenericBMPBean$GenericItemEntityState.createEntity(GenericBMPBean.java:1842)
     at de.hybris.platform.persistence.GenericBMPBean.ejbCreate(GenericBMPBean.java:399)
     at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:498)
     at de.hybris.platform.util.Utilities.callMethod(Utilities.java:1087)
     at de.hybris.platform.util.Utilities.callMethod(Utilities.java:1077)
     at de.hybris.platform.persistence.framework.HomeInvocationHandler.invoke(HomeInvocationHandler.java:60)
     at com.sun.proxy.$Proxy116.create(Unknown Source)
     at de.hybris.platform.persistence.GenericItemEJBImpl.createGenericItem(GenericItemEJBImpl.java:116)
     ... 18 more
 Caused by: org.springframework.dao.DuplicateKeyException: query; SQL []; Duplicate entry '8796158853642' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '8796158853642' for key 'PRIMARY'
     at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
     at de.hybris.platform.jdbcwrapper.DataSourceImpl.translateToDataAccessException(DataSourceImpl.java:1030)
     ... 28 more
 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '8796158853642' for key 'PRIMARY'
     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
     at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
     at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
     at com.mysql.jdbc.Util.getInstance(Util.java:386)
     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
     at de.hybris.platform.jdbcwrapper.PreparedStatementImpl.executeUpdate(PreparedStatementImpl.java:313)
     at de.hybris.platform.persistence.GenericBMPBean$GenericItemEntityState.createEntity(GenericBMPBean.java:1817)
     ... 27 more

[mpern · Accepted solution]

For whatever reason, it looks like you have a clash in your PKs for the type CronJobHistory

If you don't use the history in any business logic, I would just TRUNCATE the table to have a fresh start.

[felipemonteiro_carvalho]

Ok, now it seams to be better! I also had to clear another table...

 mysql> delete from CronJobHistories;
 Query OK, 880 rows affected (0.01 sec)

[felipemonteiro_carvalho]

Thanks, its a good idea, but I did:

mysql> DELETE FROM CVSyncCronJobHistories; Query OK, 23 rows affected (0.01 sec)

And then confirmed in HAC that now I have 0 rows there:

SELECT * FROM {CatalogVersionSyncCronJobHistory}

And restarted hybris but nothing changed.

Furthermore, previously I checked the error messages Duplicate entry '8796224356874' for key 'PRIMARY'; and those entry Numbers were not in any of the 23 rows in that table!

Suposedly if its duplicate it should be in the PK column... I am trying to figure out which SQL exactly it tries to run ...

[felipemonteiro_carvalho]

I found some info. Debugging GenericBMPBean.class decompiled line 1414 throw new HJMPException(this.getPool().getDataSource().translateToDataAccessException(var13), this.getDeployment());

I get: typeInfoMap.itemTableName=cvsynccronjobhistories code=CatalogVersionSyncCronJobHistory

So maybe some module needs updating? But which module would it be? Not sure since the jar involved is coreserver.jar ....