Resolved the problem with character set and collation in ofbiz

[rvujji]

Hi All,

Recently I started working with Ofbiz, I faced few problem and founded some solution for them.
Few days back I faced problem with character set & collation while running load-exseed ant script to load required data to newly created tables.

Here is the log...

Failure in create operation for entity [Geo]: org.ofbiz.entity.GenericEntityException: Error while inserting: [GenericEntity:Geo][abbreviation,?(java.lang.String)][createdStamp,2016-01-21 18:58:33.226(java.sql.Timestamp)][createdTxStamp,2016-01-21 18:58:33.213(java.sql.Timestamp)][geoCode,11(java.lang.String)][geoId,CN-11(java.lang.String)][geoName,??(java.lang.String)][geoTypeId,MUNICIPALITY(java.lang.String)][lastUpdatedStamp,2016-01-21 18:58:33.226(java.sql.Timestamp)][lastUpdatedTxStamp,2016-01-21 18:58:33.213(java.sql.Timestamp)][wellKnownText,Beijing(java.lang.String)] (SQL Exception while executing the following:INSERT INTO GEO (GEO_ID, GEO_TYPE_ID, GEO_NAME, GEO_CODE, GEO_SEC_CODE, ABBREVIATION, WELL_KNOWN_TEXT, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xE5\x8C\x97\xE4\xBA\xAC' for column 'GEO_NAME' at row 1)). Rolling back transaction.
     [java] 2016-01-21 18:58:33,243 |main                 |TransactionUtil               |W| Calling transaction setRollbackOnly; this stack trace shows where this is happening:
     [java] java.lang.Exception: Failure in create operation for entity [Geo]: org.ofbiz.entity.GenericEntityException: Error while inserting: [GenericEntity:Geo][abbreviation,?(java.lang.String)][createdStamp,2016-01-21 18:58:33.226(java.sql.Timestamp)][createdTxStamp,2016-01-21 18:58:33.213(java.sql.Timestamp)][geoCode,11(java.lang.String)][geoId,CN-11(java.lang.String)][geoName,??(java.lang.String)][geoTypeId,MUNICIPALITY(java.lang.String)][lastUpdatedStamp,2016-01-21 18:58:33.226(java.sql.Timestamp)][lastUpdatedTxStamp,2016-01-21 18:58:33.213(java.sql.Timestamp)][wellKnownText,Beijing(java.lang.String)] (SQL Exception while executing the following:INSERT INTO GEO (GEO_ID, GEO_TYPE_ID, GEO_NAME, GEO_CODE, GEO_SEC_CODE, ABBREVIATION, WELL_KNOWN_TEXT, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xE5\x8C\x97\xE4\xBA\xAC' for column 'GEO_NAME' at row 1)). Rolling back transaction.
     [java] at org.ofbiz.entity.transaction.TransactionUtil.setRollbackOnly(TransactionUtil.java:360) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entity.transaction.TransactionUtil.rollback(TransactionUtil.java:301) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:925) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1447) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entity.util.EntitySaxReader.writeValues(EntitySaxReader.java:329) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:299) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:248) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entity.util.EntityDataLoader.loadData(EntityDataLoader.java:257) [ofbiz-entity.jar:?]
     [java] at org.ofbiz.entityext.data.EntityDataLoadContainer.loadContainer(EntityDataLoadContainer.java:490) [ofbiz-entityext.jar:?]
     [java] at org.ofbiz.entityext.data.EntityDataLoadContainer.start(EntityDataLoadContainer.java:245) [ofbiz-entityext.jar:?]
     [java] at org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:237) [ofbiz-base.jar:?]
     [java] at org.ofbiz.base.start.Start.startStartLoaders(Start.java:343) [ofbiz.jar:?]
     [java] at org.ofbiz.base.start.Start.start(Start.java:369) [ofbiz.jar:?]
     [java] at org.ofbiz.base.start.Start.main(Start.java:136) [ofbiz.jar:?]

then, I did below configuration
First change entityengine.xml to utf8 and create/alter database with utf8 by running below queries,
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
If still you are facing same problem then try to alter the table with utf8 by running below queries,
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

If the table contains any ID column then we will get error msg,so in that case just avoid special characters of ID column in demo data xml's.

Now its working fine for me.