Broadleaf Commerce Discussion Forums

hello please follow up my exception i am getting on my server


[DEBUG] 12:30:53 SQL - update SEQUENCE_GENERATOR set ID_VAL=? where ID_VAL=? and ID_NAME=?
[DEBUG] 12:30:53 SQL - insert into BLC_SKU (ACTIVE_END_DATE, ACTIVE_START_DATE, AVAILABLE_FLAG, CURRENCY_CODE, DEFAULT_PRODUCT_ID, DESCRIPTION, CONTAINER_SHAPE, DEPTH, DIMENSION_UNIT_OF_MEASURE, GIRTH, HEIGHT, CONTAINER_SIZE, WIDTH, DISCOUNTABLE_FLAG, FULFILLMENT_TYPE, INVENTORY_TYPE, IS_MACHINE_SORTABLE, LONG_DESCRIPTION, NAME, RETAIL_PRICE, SALE_PRICE, TAX_CODE, TAXABLE_FLAG, WEIGHT, WEIGHT_UNIT_OF_MEASURE, SKU_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[TRACE] 12:30:53 BasicBinder - binding parameter [1] as [TIMESTAMP] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [2] as [TIMESTAMP] - Sat Mar 01 00:00:00 CET 2014
[TRACE] 12:30:53 BasicBinder - binding parameter [3] as [CHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [4] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [5] as [BIGINT] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [6] as [VARCHAR] - mohittest870987dfaksdjf desc
[TRACE] 12:30:53 BasicBinder - binding parameter [7] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [8] as [NUMERIC] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [9] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [10] as [NUMERIC] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [11] as [NUMERIC] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [12] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [13] as [NUMERIC] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [14] as [CHAR] - Y
[TRACE] 12:30:53 BasicBinder - binding parameter [15] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [16] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [17] as [BOOLEAN] - true
[TRACE] 12:30:53 BasicBinder - binding parameter [19] as [VARCHAR] - mohittest870987dfaksdjf
[TRACE] 12:30:53 BasicBinder - binding parameter [20] as [NUMERIC] - 300
[TRACE] 12:30:53 BasicBinder - binding parameter [21] as [NUMERIC] - 20
[TRACE] 12:30:53 BasicBinder - binding parameter [22] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [23] as [CHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [24] as [NUMERIC] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [25] as [VARCHAR] - <null>
[TRACE] 12:30:53 BasicBinder - binding parameter [26] as [BIGINT] - 753659
[DEBUG] 12:30:53 SqlExceptionHelper - Duplicate entry '753659' for key 'PRIMARY' [n/a]
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '753659' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)


this exceptions occurred after batch product insert ETL task runs on my system. In batch update task we insert products and take ids from SEQUENCE_GENERATOR and assign it to new product and then update SEQUENCE_GENERATOR and at end of import i do maxid + 2 and update SEQUENCE_GENERATOR accordingly.


now following query result explain status of my sku table and seq. gen table after batch import

mysql> select max(SKU_ID) from BLC_SKU;
+-------------+
| max(SKU_ID) |
+-------------+
| 753708 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from SEQUENCE_GENERATOR where ID_NAME = 'SkuImpl';
+---------+--------+
| ID_NAME | ID_VAL |
+---------+--------+
| SkuImpl | 753759 |
+---------+--------+
1 row in set (0.00 sec)

so admin application trying to insert record with 753659 even seq. gen have entry of 753759 and then duplicate key exception occurred.

to fix it temporary i execute following query and restart admin server.

UPDATE SEQUENCE_GENERATOR SET ID_VAL = (SELECT (PRODUCT_ID + 500) FROM BLC_PRODUCT ORDER BY PRODUCT_ID DESC LIMIT 1) WHERE ID_NAME = 'ProductImpl';
UPDATE SEQUENCE_GENERATOR SET ID_VAL = (SELECT (SKU_ID + 500) FROM BLC_SKU ORDER BY SKU_ID DESC LIMIT 1) WHERE ID_NAME = 'SkuImpl';
UPDATE SEQUENCE_GENERATOR SET ID_VAL = (SELECT (STATIC_ASSET_ID + 500) FROM BLC_STATIC_ASSET ORDER BY STATIC_ASSET_ID DESC LIMIT 1) WHERE ID_NAME = 'StaticAssetImpl';
UPDATE SEQUENCE_GENERATOR SET ID_VAL = (SELECT (ID + 500) FROM BLC_PRODUCT_INGREDIENT ORDER BY ID DESC LIMIT 1) WHERE ID_NAME = 'ProductIngredientImpl';
UPDATE SEQUENCE_GENERATOR SET ID_VAL = (SELECT (MEDIA_ID + 500) FROM BLC_MEDIA ORDER BY MEDIA_ID DESC LIMIT 1) WHERE ID_NAME = 'MediaImpl';
UPDATE SEQUENCE_GENERATOR SET ID_VAL = (SELECT (SKU_ATTR_ID + 500) FROM BLC_SKU_ATTRIBUTE ORDER BY SKU_ATTR_ID DESC LIMIT 1) WHERE ID_NAME = 'SkuAttributeImpl';


but question is what would be permanent solution ? is it bug from blc ?

See https://github.com/BroadleafCommerce/Br ... issues/168 for the full discussion on this. Specifically this comment that talks about how to change the behavior: https://github.com/BroadleafCommerce/Br ... t-28219459

In my opinion this should be the default behavior of Broadleaf with sequences and I think it is a mistake for Hibernate to operate in the way that it does. Opened an issue to change it at https://github.com/BroadleafCommerce/Br ... issues/786.