OFBiz › OFBiz - User

Exception occured when I try to add a new person

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

7 messages Options

Leon

Exception occured when I try to add a new person

The exception throwed in backend is something like:
RROR: parsing file: ERROR parsing Entity Xml file: org.ofbiz.entity.GenericEntityException: Error while inserting: [GenericEntity:Person][createdStamp,2011-04-28 11:09:55.29(java.sql.Timestamp)][createdTxStamp,2011-04-28 11:09:55.288(java.sql.Timestamp)][lastUpdatedStamp,2011-04-28 11:09:55.29(java.sql.Timestamp)][lastUpdatedTxStamp,2011-04-28 11:09:55.288(java.sql.Timestamp)][partyId,_NA_(java.lang.String)] (SQL Exception while executing the following:INSERT INTO OFBIZ.PERSON (PARTY_ID, SALUTATION, FIRST_NAME, MIDDLE_NAME, LAST_NAME, PERSONAL_TITLE, SUFFIX, NICKNAME, FIRST_NAME_LOCAL, MIDDLE_NAME_LOCAL, LAST_NAME_LOCAL, OTHER_LOCAL, MEMBER_ID, GENDER, BIRTH_DATE, DECEASED_DATE, HEIGHT, WEIGHT, MOTHERS_MAIDEN_NAME, MARITAL_STATUS, SOCIAL_SECURITY_NUMBER, PASSPORT_NUMBER, PASSPORT_EXPIRE_DATE, TOTAL_YEARS_WORK_EXPERIENCE, COMMENTS, EMPLOYMENT_STATUS_ENUM_ID, RESIDENCE_STATUS_ENUM_ID, OCCUPATION, YEARS_WITH_EMPLOYER, MONTHS_WITH_EMPLOYER, EXISTING_CUSTOMER, CARD_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PERSON_TXCRTS' defined on 'PERSON'.))

----
If I remove the CARD_ID_IDX index (the unique index on Person.cardId), then it works.

btw, in fact, if I use run-install command to re-create the database and import all demo data, ton of such kind "duplicate key value" exception was thrown out.

I test this on ofbiz svn trunk and the database I use is derby 10.7.

I guess maybe this is the defect related to derby 10.7. If that's true, can we downgrade the derby to 10.5 or even ealier?

Jacques Le Roux

Re: Exception occured when I try to add a new person

Administrator

Could you try with Postgres or Derby 10.5?

Jacques

From: "Leon2" <[hidden email]>

> The exception throwed in backend is something like:
> RROR: parsing file: ERROR parsing Entity Xml file:
> org.ofbiz.entity.GenericEntityException: Error while inserting:
> [GenericEntity:Person][createdStamp,2011-04-28
> 11:09:55.29(java.sql.Timestamp)][createdTxStamp,2011-04-28
> 11:09:55.288(java.sql.Timestamp)][lastUpdatedStamp,2011-04-28
> 11:09:55.29(java.sql.Timestamp)][lastUpdatedTxStamp,2011-04-28
> 11:09:55.288(java.sql.Timestamp)][partyId,_NA_(java.lang.String)] (SQL
> Exception while executing the following:INSERT INTO OFBIZ.PERSON (PARTY_ID,
> SALUTATION, FIRST_NAME, MIDDLE_NAME, LAST_NAME, PERSONAL_TITLE, SUFFIX,
> NICKNAME, FIRST_NAME_LOCAL, MIDDLE_NAME_LOCAL, LAST_NAME_LOCAL, OTHER_LOCAL,
> MEMBER_ID, GENDER, BIRTH_DATE, DECEASED_DATE, HEIGHT, WEIGHT,
> MOTHERS_MAIDEN_NAME, MARITAL_STATUS, SOCIAL_SECURITY_NUMBER,
> PASSPORT_NUMBER, PASSPORT_EXPIRE_DATE, TOTAL_YEARS_WORK_EXPERIENCE,
> COMMENTS, EMPLOYMENT_STATUS_ENUM_ID, RESIDENCE_STATUS_ENUM_ID, OCCUPATION,
> YEARS_WITH_EMPLOYER, MONTHS_WITH_EMPLOYER, EXISTING_CUSTOMER, CARD_ID,
> LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP)
> VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (The statement was aborted because it
> would have caused a duplicate key value in a unique or primary key
> constraint or unique index identified by 'PERSON_TXCRTS' defined on
> 'PERSON'.))
>
> ----
> If I remove the CARD_ID_IDX index (the unique index on Person.cardId), then
> it works.
>
> btw, in fact, if I use run-install command to re-create the database and
> import all demo data, ton of such kind "duplicate key value" exception was
> thrown out.
>
> I test this on ofbiz svn trunk and the database I use is derby 10.7.
>
> I guess maybe this is the defect related to derby 10.7. If that's true, can
> we downgrade the derby to 10.5 or even ealier?
>
> --
> View this message in context:
> http://ofbiz.135035.n4.nabble.com/Exception-occured-when-I-try-to-add-a-new-person-tp3494394p3494394.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.

Erwan de FERRIERES

Re: Exception occured when I try to add a new person

In reply to this post by Leon

Le 04/05/2011 05:18, Leon2 a écrit :

to recreate the database, if using derby, you have to make an ant
clean-all which will remove the database. if you are runnnig run-install
2 times, it will then have duplicate values.

>
> I test this on ofbiz svn trunk and the database I use is derby 10.7.
>
> I guess maybe this is the defect related to derby 10.7. If that's true, can
> we downgrade the derby to 10.5 or even ealier?
>
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/Exception-occured-when-I-try-to-add-a-new-person-tp3494394p3494394.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

--
Erwan de FERRIERES
www.nereide.biz

adnane

Re: Exception occured when I try to add a new person

In reply to this post by Leon

I agree with Mr leRoux because there is many problems when you use other data base, i used postgresql and i didn't find this problem, so i you need a help about how to change the database to the prostgresql, i can send you a document wich you will find all the details

Leon

Re: Exception occured when I try to add a new person

In reply to this post by Leon

I found the cause. It's because that Derby treats "null" as a normal value in its unique indice implementation. If you create an unique index, derby will set the unique constraint on whole combination of all index fields no matter the value of one field is null or not.

e.g. table 'test' has two columns (a int not null, b int) with unique index on (a, b), then value combination (1, null) can be inserted into table once and only once.

Workaround: set use-indices-unique="false" explicitly in entityengine.xml for derby database.

to Jacques,

Not test it on Postgres yet, but I think Postgres should be ok since Postgres doesn't allow nullable column in unique indice. That means (1, null) can be inserted to test table twice without unique index violation in previous example.

to adnane,

That's great. I need such detail document. I'm evaluating the feasibility to migrate the mysql database to postgres. The most important reason is Timestamp type of mysql does not support milliseconds. So, thank you in advance ^^. You can send it to 'sun.hao' at 'labwayit' dot 'com'.

Jacques Le Roux

Re: Exception occured when I try to add a new person

Administrator

Yes indeed this is know reason and why we have use-indices-unique="false" by default for Derby datasources
see http://svn.apache.org/viewvc?view=revision&revision=544640

I have completed r544640 by adding an explicit comment about use-indices-unique="false" at r1099727

Jacques

From: "Leon2" <[hidden email]>

>I found the cause. It's because that Derby treats "null" as a normal value in
> its unique indice implementation. If you create an unique index, derby will
> set the unique constraint on whole combination of all index fields no matter
> the value of one field is null or not.
>
> e.g. table 'test' has two columns (a int not null, b int) with unique index
> on (a, b), then value combination (1, null) can be inserted into table once
> and only once.
>
> Workaround: set use-indices-unique="false" explicitly in entityengine.xml
> for derby database.
>
> to Jacques,
>
> Not test it on Postgres yet, but I think Postgres should be ok since
> Postgres doesn't allow nullable column in unique indice. That means (1,
> null) can be inserted to test table twice without unique index violation in
> previous example.
>
>
> to adnane,
>
> That's great. I need such detail document. I'm evaluating the feasibility to
> migrate the mysql database to postgres. The most important reason is
> Timestamp type of mysql does not support milliseconds. So, thank you in
> advance ^^. You can send it to 'sun.hao' at 'labwayit' dot 'com'.
>
>
> --
> View this message in context:
> http://ofbiz.135035.n4.nabble.com/Exception-occured-when-I-try-to-add-a-new-person-tp3494394p3497786.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.

Mike Z

Re: Exception occured when I try to add a new person

In reply to this post by Leon

>Adrian Crum Said: Feb 12
>By the way, the MySql timestamp issue has a workaround in OFBiz. If you change the timestamp field's sql-type to CHAR(30), the
>entity engine will convert timestamps from/to CHAR field types.

Adrian mentioned the above work-around for the mysql timestamp issue.
It probably only applies to trunk/11.04.

On Thu, May 5, 2011 at 1:00 AM, Leon2 <[hidden email]> wrote:
> to adnane,
>
> That's great. I need such detail document. I'm evaluating the feasibility to
> migrate the mysql database to postgres. The most important reason is
> Timestamp type of mysql does not support milliseconds. So, thank you in
> advance ^^. You can send it to 'sun.hao' at 'labwayit' dot 'com'.