Loading Seed and Demo Data into MySQL failing

[John Martin]

Hi,

I downloaded ofbiz from source via svn last week and was able to using
the application with the Derby database.  I'm now working on
integrating it with our other applications and migrating ofBiz to
MySQL.  When I go to run the initialization as described at
http://www.opentaps.org/index.php?option=com_content&task=view&id=37&Itemid=62
I'm getting certain errors.

We are current using mysql 5.0.18-nt.

The majority of the data is loading and I'm able to run the app but
here are my problems:

1. While loading uom_conversion, mySQL is bombing on with a conversion error:

Data truncation: Data truncated for column 'CONVERSION_FACTOR'
the data in the GenericEntity for that column is set to:
[conversionFactor,1.0E-10(java.lang.Double)]
in the database, the table/column is defined as decimal(18,6).

I've tried changing the size of the field to no avail. It seems that
the value is passed to the prepared statement in the exponential
format and mysql is bitch'n.

The field type defined for uom_convers.conversion_factor is:
field name="conversionFactor" type="floating-point"

while the mysql field definition for floating-point is:
field-type-def type="floating-point" sql-type="DECIMAL(18,6)" java-type="Double"

The Mysql docs mention Decimal should map to java type BigDecimal:
http://dev.mysql.com/doc/refman/5.0/en/cj-type-conversions.html

The truncation also occurred with data inserted into
supplier_product.last_price where the value being inserted was 1.4975
(Double)

I've tried using the import data webtool to load
framework/common/data/UnitData.xml and it fails consistently.

Anybody got any suggestions?

2. There are a few tables that try insert data but there are foreign
key constraints that are failing.  Examples are:
- facility, facility_contact and facility_contact_mech_purpose missing
contact_mech foreign key
- product_store_survey_appl missing prod_store foreign key
- product_category_rollup, tax_authority_category missing product_category
- contact_list_party and facility_contact_mech_purpose missing party
"DemoCustCompany".

It seems to me that the order that some of the data might have
dependencies and before I dig into it, I wanted to see if anybody else
has seen this problem?

Thanks,

John

[Si Chen-2]

John,

I've followed those instructinos and loaded successfully with MySQL  
5.0.13 and 5.0.19, so I'm not sure why you're having this problem.  
Are you installing opentaps -0.9.1 or the SVN version of OFBiz?  if  
the latter, can you see if the same problem happens if you used  
opentaps-0.9.1?

Si

On Jul 25, 2006, at 12:41 PM, John Martin wrote:

> Hi,
>
> I downloaded ofbiz from source via svn last week and was able to using
> the application with the Derby database.  I'm now working on
> integrating it with our other applications and migrating ofBiz to
> MySQL.  When I go to run the initialization as described at
> http://www.opentaps.org/index.php?
> option=com_content&task=view&id=37&Itemid=62
> I'm getting certain errors.
>
> We are current using mysql 5.0.18-nt.
>
> The majority of the data is loading and I'm able to run the app but
> here are my problems:
>
> 1. While loading uom_conversion, mySQL is bombing on with a  
> conversion error:
>
> Data truncation: Data truncated for column 'CONVERSION_FACTOR'
> the data in the GenericEntity for that column is set to:
> [conversionFactor,1.0E-10(java.lang.Double)]
> in the database, the table/column is defined as decimal(18,6).
>
> I've tried changing the size of the field to no avail. It seems that
> the value is passed to the prepared statement in the exponential
> format and mysql is bitch'n.
>
> The field type defined for uom_convers.conversion_factor is:
> field name="conversionFactor" type="floating-point"
>
> while the mysql field definition for floating-point is:
> field-type-def type="floating-point" sql-type="DECIMAL(18,6)" java-
> type="Double"
>
> The Mysql docs mention Decimal should map to java type BigDecimal:
> http://dev.mysql.com/doc/refman/5.0/en/cj-type-conversions.html
>
> The truncation also occurred with data inserted into
> supplier_product.last_price where the value being inserted was 1.4975
> (Double)
>
> I've tried using the import data webtool to load
> framework/common/data/UnitData.xml and it fails consistently.
>
> Anybody got any suggestions?
>
> 2. There are a few tables that try insert data but there are foreign
> key constraints that are failing.  Examples are:
> - facility, facility_contact and facility_contact_mech_purpose missing
> contact_mech foreign key
> - product_store_survey_appl missing prod_store foreign key
> - product_category_rollup, tax_authority_category missing  
> product_category
> - contact_list_party and facility_contact_mech_purpose missing party
> "DemoCustCompany".
>
> It seems to me that the order that some of the data might have
> dependencies and before I dig into it, I wanted to see if anybody else
> has seen this problem?
>
> Thanks,
>
> John

[John Martin]

Hi Si,

I just tried a brand new install on a seperate box (my dev machine).
I downloaded the OpenTaps version 424208 as you recommended.  Here is
the configuration:

Win XP
Java SDK 1.4.2_04
MySQL 5.0.22
MySQL JDBC driver 3.1.13

I'm wondering if the problem is with the JDBC driver.  What version
are you using?

I'm going to try again with a different version verion of the driver.

Any other thoughts?

Thanks,

John

Here is the full exception from the logfile:

---- exception report ----------------------------------------------------------
Failure in create operation for entity [UomConversion]:
org.ofbiz.entity.GenericEntityException: Exception while inserting the
following entity:
[GenericEntity:UomConversion][conversionFactor,1.0E-10(java.lang.Double)][createdStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)][uomIdTo,LEN_m(java.lang.String)]
(while inserting:
[GenericEntity:UomConversion][conversionFactor,1.0E-10(java.lang.Double)][createdStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)][uomIdTo,LEN_m(java.lang.String)]
(SQL Exception while executing the following:INSERT INTO
UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
'CONVERSION_FACTOR' at row 1))). Rolling back transaction.
Exception: org.ofbiz.entity.GenericEntityException
Message: Exception while inserting the following entity:
[GenericEntity:UomConversion][conversionFactor,1.0E-10(java.lang.Double)][createdStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)][uomIdTo,LEN_m(java.lang.String)]
(while inserting:
[GenericEntity:UomConversion][conversionFactor,1.0E-10(java.lang.Double)][createdStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)][uomIdTo,LEN_m(java.lang.String)]
(SQL Exception while executing the following:INSERT INTO
UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
'CONVERSION_FACTOR' at row 1)))
---- stack trace ---------------------------------------------------------------
org.ofbiz.entity.GenericEntityException: Exception while inserting the
following entity:
[GenericEntity:UomConversion][conversionFactor,1.0E-10(java.lang.Double)][createdStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)][uomIdTo,LEN_m(java.lang.String)]
(while inserting:
[GenericEntity:UomConversion][conversionFactor,1.0E-10(java.lang.Double)][createdStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)][uomIdTo,LEN_m(java.lang.String)]
(SQL Exception while executing the following:INSERT INTO
UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
'CONVERSION_FACTOR' at row 1)))
org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:130)
org.ofbiz.entity.datasource.GenericHelperDAO.create(GenericHelperDAO.java:75)
org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
org.ofbiz.entity.util.EntitySaxReader.writeValues(EntitySaxReader.java:266)
org.ofbiz.entity.util.EntitySaxReader.endElement(EntitySaxReader.java:397)
javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
org.ofbiz.entity.util.EntityDataLoader.loadData(EntityDataLoader.java:225)
org.ofbiz.entityext.data.EntityDataLoadContainer.start(EntityDataLoadContainer.java:248)
org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:80)
org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
org.ofbiz.base.start.Start.startServer(Start.java:314)
org.ofbiz.base.start.Start.start(Start.java:318)
org.ofbiz.base.start.Start.main(Start.java:404)
org.ofbiz.entity.GenericEntityException: while inserting:
[GenericEntity:UomConversion][conversionFactor,1.0E-10(java.lang.Double)][createdStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)][uomIdTo,LEN_m(java.lang.String)]
(SQL Exception while executing the following:INSERT INTO
UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
'CONVERSION_FACTOR' at row 1))
org.ofbiz.entity.datasource.GenericDAO.singleInsert(GenericDAO.java:185)
org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:127)
org.ofbiz.entity.datasource.GenericHelperDAO.create(GenericHelperDAO.java:75)
org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
org.ofbiz.entity.util.EntitySaxReader.writeValues(EntitySaxReader.java:266)
org.ofbiz.entity.util.EntitySaxReader.endElement(EntitySaxReader.java:397)
javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
org.ofbiz.entity.util.EntityDataLoader.loadData(EntityDataLoader.java:225)
org.ofbiz.entityext.data.EntityDataLoadContainer.start(EntityDataLoadContainer.java:248)
org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:80)
org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
org.ofbiz.base.start.Start.startServer(Start.java:314)
org.ofbiz.base.start.Start.start(Start.java:318)
org.ofbiz.base.start.Start.main(Start.java:404)
org.ofbiz.entity.GenericDataSourceException: SQL Exception while
executing the following:INSERT INTO UOM_CONVERSION (UOM_ID, UOM_ID_TO,
CONVERSION_FACTOR, CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE,
LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Data
truncation: Data truncated for column 'CONVERSION_FACTOR' at row 1)
org.ofbiz.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:439)
org.ofbiz.entity.datasource.GenericDAO.singleInsert(GenericDAO.java:180)
org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:127)
org.ofbiz.entity.datasource.GenericHelperDAO.create(GenericHelperDAO.java:75)
org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
org.ofbiz.entity.util.EntitySaxReader.writeValues(EntitySaxReader.java:266)
org.ofbiz.entity.util.EntitySaxReader.endElement(EntitySaxReader.java:397)
javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
org.ofbiz.entity.util.EntityDataLoader.loadData(EntityDataLoader.java:225)
org.ofbiz.entityext.data.EntityDataLoadContainer.start(EntityDataLoadContainer.java:248)
org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:80)
org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
org.ofbiz.base.start.Start.startServer(Start.java:314)
org.ofbiz.base.start.Start.start(Start.java:318)
org.ofbiz.base.start.Start.main(Start.java:404)
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated
for column 'CONVERSION_FACTOR' at row 1
com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:709)
com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3461)
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1198)
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)
org.ofbiz.minerva.pool.jdbc.PreparedStatementInPool.executeUpdate(PreparedStatementInPool.java:82)
org.ofbiz.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:436)
org.ofbiz.entity.datasource.GenericDAO.singleInsert(GenericDAO.java:180)
org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:127)
org.ofbiz.entity.datasource.GenericHelperDAO.create(GenericHelperDAO.java:75)
org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
org.ofbiz.entity.util.EntitySaxReader.writeValues(EntitySaxReader.java:266)
org.ofbiz.entity.util.EntitySaxReader.endElement(EntitySaxReader.java:397)
javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
javolution.xml.sax.RealtimeParser.parse(Unknown Source)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
org.ofbiz.entity.util.EntityDataLoader.loadData(EntityDataLoader.java:225)
org.ofbiz.entityext.data.EntityDataLoadContainer.start(EntityDataLoadContainer.java:248)
org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:80)
org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
org.ofbiz.base.start.Start.startServer(Start.java:314)
org.ofbiz.base.start.Start.start(Start.java:318)
org.ofbiz.base.start.Start.main(Start.java:404)
--------------------------------------------------------------------------------

1490663[    TransactionUtil.java:276:WARN ]

[Si Chen-2]

John,

opentaps 0.9 comes with this in driver:
mysql-connector-java-3.0.17-ga-bin.jar

and everything worked out of the box.

Si


On Jul 26, 2006, at 12:00 PM, John Martin wrote:

> Hi Si,
>
> I just tried a brand new install on a seperate box (my dev machine).
> I downloaded the OpenTaps version 424208 as you recommended.  Here is
> the configuration:
>
> Win XP
> Java SDK 1.4.2_04
> MySQL 5.0.22
> MySQL JDBC driver 3.1.13
>
> I'm wondering if the problem is with the JDBC driver.  What version
> are you using?
>
> I'm going to try again with a different version verion of the driver.
>
> Any other thoughts?
>
> Thanks,
>
> John
>
> Here is the full exception from the logfile:
>
> ---- exception report  
> ----------------------------------------------------------
> Failure in create operation for entity [UomConversion]:
> org.ofbiz.entity.GenericEntityException: Exception while inserting the
> following entity:
> [GenericEntity:UomConversion][conversionFactor,1.0E-10
> (java.lang.Double)][createdStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)]
> [uomIdTo,LEN_m(java.lang.String)]
> (while inserting:
> [GenericEntity:UomConversion][conversionFactor,1.0E-10
> (java.lang.Double)][createdStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)]
> [uomIdTo,LEN_m(java.lang.String)]
> (SQL Exception while executing the following:INSERT INTO
> UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
> CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
> 'CONVERSION_FACTOR' at row 1))). Rolling back transaction.
> Exception: org.ofbiz.entity.GenericEntityException
> Message: Exception while inserting the following entity:
> [GenericEntity:UomConversion][conversionFactor,1.0E-10
> (java.lang.Double)][createdStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)]
> [uomIdTo,LEN_m(java.lang.String)]
> (while inserting:
> [GenericEntity:UomConversion][conversionFactor,1.0E-10
> (java.lang.Double)][createdStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)]
> [uomIdTo,LEN_m(java.lang.String)]
> (SQL Exception while executing the following:INSERT INTO
> UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
> CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
> 'CONVERSION_FACTOR' at row 1)))
> ---- stack trace  
> ---------------------------------------------------------------
> org.ofbiz.entity.GenericEntityException: Exception while inserting the
> following entity:
> [GenericEntity:UomConversion][conversionFactor,1.0E-10
> (java.lang.Double)][createdStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)]
> [uomIdTo,LEN_m(java.lang.String)]
> (while inserting:
> [GenericEntity:UomConversion][conversionFactor,1.0E-10
> (java.lang.Double)][createdStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)]
> [uomIdTo,LEN_m(java.lang.String)]
> (SQL Exception while executing the following:INSERT INTO
> UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
> CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
> 'CONVERSION_FACTOR' at row 1)))
> org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:130)
> org.ofbiz.entity.datasource.GenericHelperDAO.create
> (GenericHelperDAO.java:75)
> org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
> org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
> org.ofbiz.entity.util.EntitySaxReader.writeValues
> (EntitySaxReader.java:266)
> org.ofbiz.entity.util.EntitySaxReader.endElement
> (EntitySaxReader.java:397)
> javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
> javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
> org.ofbiz.entity.util.EntityDataLoader.loadData
> (EntityDataLoader.java:225)
> org.ofbiz.entityext.data.EntityDataLoadContainer.start
> (EntityDataLoadContainer.java:248)
> org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:
> 80)
> org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
> org.ofbiz.base.start.Start.startServer(Start.java:314)
> org.ofbiz.base.start.Start.start(Start.java:318)
> org.ofbiz.base.start.Start.main(Start.java:404)
> org.ofbiz.entity.GenericEntityException: while inserting:
> [GenericEntity:UomConversion][conversionFactor,1.0E-10
> (java.lang.Double)][createdStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][createdTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][lastUpdatedStamp,2006-07-26
> 14:37:42.132(java.sql.Timestamp)][lastUpdatedTxStamp,2006-07-26
> 14:37:41.851(java.sql.Timestamp)][uomId,LEN_A(java.lang.String)]
> [uomIdTo,LEN_m(java.lang.String)]
> (SQL Exception while executing the following:INSERT INTO
> UOM_CONVERSION (UOM_ID, UOM_ID_TO, CONVERSION_FACTOR,
> CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?) (Data truncation: Data truncated for column
> 'CONVERSION_FACTOR' at row 1))
> org.ofbiz.entity.datasource.GenericDAO.singleInsert(GenericDAO.java:
> 185)
> org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:127)
> org.ofbiz.entity.datasource.GenericHelperDAO.create
> (GenericHelperDAO.java:75)
> org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
> org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
> org.ofbiz.entity.util.EntitySaxReader.writeValues
> (EntitySaxReader.java:266)
> org.ofbiz.entity.util.EntitySaxReader.endElement
> (EntitySaxReader.java:397)
> javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
> javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
> org.ofbiz.entity.util.EntityDataLoader.loadData
> (EntityDataLoader.java:225)
> org.ofbiz.entityext.data.EntityDataLoadContainer.start
> (EntityDataLoadContainer.java:248)
> org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:
> 80)
> org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
> org.ofbiz.base.start.Start.startServer(Start.java:314)
> org.ofbiz.base.start.Start.start(Start.java:318)
> org.ofbiz.base.start.Start.main(Start.java:404)
> org.ofbiz.entity.GenericDataSourceException: SQL Exception while
> executing the following:INSERT INTO UOM_CONVERSION (UOM_ID, UOM_ID_TO,
> CONVERSION_FACTOR, CUSTOM_METHOD_ID, DECIMAL_SCALE, ROUNDING_MODE,
> LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
> CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Data
> truncation: Data truncated for column 'CONVERSION_FACTOR' at row 1)
> org.ofbiz.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:
> 439)
> org.ofbiz.entity.datasource.GenericDAO.singleInsert(GenericDAO.java:
> 180)
> org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:127)
> org.ofbiz.entity.datasource.GenericHelperDAO.create
> (GenericHelperDAO.java:75)
> org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
> org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
> org.ofbiz.entity.util.EntitySaxReader.writeValues
> (EntitySaxReader.java:266)
> org.ofbiz.entity.util.EntitySaxReader.endElement
> (EntitySaxReader.java:397)
> javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
> javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
> org.ofbiz.entity.util.EntityDataLoader.loadData
> (EntityDataLoader.java:225)
> org.ofbiz.entityext.data.EntityDataLoadContainer.start
> (EntityDataLoadContainer.java:248)
> org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:
> 80)
> org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
> org.ofbiz.base.start.Start.startServer(Start.java:314)
> org.ofbiz.base.start.Start.start(Start.java:318)
> org.ofbiz.base.start.Start.main(Start.java:404)
> com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated
> for column 'CONVERSION_FACTOR' at row 1
> com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings
> (SQLError.java:709)
> com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:
> 3461)
> com.mysql.jdbc.ServerPreparedStatement.serverExecute
> (ServerPreparedStatement.java:1198)
> com.mysql.jdbc.ServerPreparedStatement.executeInternal
> (ServerPreparedStatement.java:677)
> com.mysql.jdbc.PreparedStatement.executeUpdate
> (PreparedStatement.java:1357)
> com.mysql.jdbc.PreparedStatement.executeUpdate
> (PreparedStatement.java:1274)
> com.mysql.jdbc.PreparedStatement.executeUpdate
> (PreparedStatement.java:1259)
> org.ofbiz.minerva.pool.jdbc.PreparedStatementInPool.executeUpdate
> (PreparedStatementInPool.java:82)
> org.ofbiz.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:
> 436)
> org.ofbiz.entity.datasource.GenericDAO.singleInsert(GenericDAO.java:
> 180)
> org.ofbiz.entity.datasource.GenericDAO.insert(GenericDAO.java:127)
> org.ofbiz.entity.datasource.GenericHelperDAO.create
> (GenericHelperDAO.java:75)
> org.ofbiz.entity.GenericDelegator.create(GenericDelegator.java:570)
> org.ofbiz.entity.GenericDelegator.storeAll(GenericDelegator.java:1109)
> org.ofbiz.entity.util.EntitySaxReader.writeValues
> (EntitySaxReader.java:266)
> org.ofbiz.entity.util.EntitySaxReader.endElement
> (EntitySaxReader.java:397)
> javolution.xml.sax.RealtimeParser.processElement(Unknown Source)
> javolution.xml.sax.RealtimeParser.parseContent(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> javolution.xml.sax.RealtimeParser.parse(Unknown Source)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:245)
> org.ofbiz.entity.util.EntitySaxReader.parse(EntitySaxReader.java:205)
> org.ofbiz.entity.util.EntityDataLoader.loadData
> (EntityDataLoader.java:225)
> org.ofbiz.entityext.data.EntityDataLoadContainer.start
> (EntityDataLoadContainer.java:248)
> org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:
> 80)
> org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
> org.ofbiz.base.start.Start.startServer(Start.java:314)
> org.ofbiz.base.start.Start.start(Start.java:318)
> org.ofbiz.base.start.Start.main(Start.java:404)
> ----------------------------------------------------------------------
> ----------
>
> 1490663[    TransactionUtil.java:276:WARN ]

[John Martin]

Hi Si,

Guess what, the problem is with the JDBC driver.  I downgraded from
the 3.1.13 Connector/J 3.1 to the 3.0.17 as you mentioned and
everything worked fine.

The correct driver is available at:
http://dev.mysql.com/downloads/connector/j/3.0.html

Thanks for your help.

John

On 7/26/06, Si Chen <[hidden email]> wrote:

> John,
>
> I've followed those instructinos and loaded successfully with MySQL
> 5.0.13 and 5.0.19, so I'm not sure why you're having this problem.
> Are you installing opentaps -0.9.1 or the SVN version of OFBiz?  if
> the latter, can you see if the same problem happens if you used
> opentaps-0.9.1?
>
> Si
>
> On Jul 25, 2006, at 12:41 PM, John Martin wrote:
>
> > Hi,
> >
> > I downloaded ofbiz from source via svn last week and was able to using
> > the application with the Derby database.  I'm now working on
> > integrating it with our other applications and migrating ofBiz to
> > MySQL.  When I go to run the initialization as described at
> > http://www.opentaps.org/index.php?
> > option=com_content&task=view&id=37&Itemid=62
> > I'm getting certain errors.
> >
> > We are current using mysql 5.0.18-nt.
> >
> > The majority of the data is loading and I'm able to run the app but
> > here are my problems:
> >
> > 1. While loading uom_conversion, mySQL is bombing on with a
> > conversion error:
> >
> > Data truncation: Data truncated for column 'CONVERSION_FACTOR'
> > the data in the GenericEntity for that column is set to:
> > [conversionFactor,1.0E-10(java.lang.Double)]
> > in the database, the table/column is defined as decimal(18,6).
> >
> > I've tried changing the size of the field to no avail. It seems that
> > the value is passed to the prepared statement in the exponential
> > format and mysql is bitch'n.
> >
> > The field type defined for uom_convers.conversion_factor is:
> > field name="conversionFactor" type="floating-point"
> >
> > while the mysql field definition for floating-point is:
> > field-type-def type="floating-point" sql-type="DECIMAL(18,6)" java-
> > type="Double"
> >
> > The Mysql docs mention Decimal should map to java type BigDecimal:
> > http://dev.mysql.com/doc/refman/5.0/en/cj-type-conversions.html
> >
> > The truncation also occurred with data inserted into
> > supplier_product.last_price where the value being inserted was 1.4975
> > (Double)
> >
> > I've tried using the import data webtool to load
> > framework/common/data/UnitData.xml and it fails consistently.
> >
> > Anybody got any suggestions?
> >
> > 2. There are a few tables that try insert data but there are foreign
> > key constraints that are failing.  Examples are:
> > - facility, facility_contact and facility_contact_mech_purpose missing
> > contact_mech foreign key
> > - product_store_survey_appl missing prod_store foreign key
> > - product_category_rollup, tax_authority_category missing
> > product_category
> > - contact_list_party and facility_contact_mech_purpose missing party
> > "DemoCustCompany".
> >
> > It seems to me that the order that some of the data might have
> > dependencies and before I dig into it, I wanted to see if anybody else
> > has seen this problem?
> >
> > Thanks,
> >
> > John
>
>

[Walter Vaughan]

John Martin wrote:
> Hi Si,
>
> Guess what, the problem is with the JDBC driver.  I downgraded from
> the 3.1.13 Connector/J 3.1 to the 3.0.17 as you mentioned and
> everything worked fine.

Uh, I am using that driver just fine... (In fact sitting here at OSCON just
recreated your scenario to make sure I'm not speaking from foggy memory).

What ALWAYS seemed to bite me when I started installing ofBiz is is having a
path the the JDK with a space in a directory name in it.

I'm using Sun's AppServer JDK on a XP laptop with 512M memory...
C:\ofbiz>echo %JAVA_HOME%
C:\Sun\AppServer\jdk

and MySQL 5.0.22-community-nt edition

"%JAVA_HOME%\bin\java" -Xms256M -Xmx512M -jar ofbiz.jar to get things going

post hoc ergo prompter hoc

YMMV
--
Walter

[John Martin]

Hey Walter,

My JAVA_HOME=C:\j2sdk1.4.2_12.  So I don't have that problem.  The
application runs pretty well.  It is only one data type that causes
problems when loading the data.  That really indicates a problem with
the driver.

In resolving it, I just replaced the 3.1.x JDBC driver with the 3.0.x,
restarted the server and was able to do the import of the
uom_conversion table without any problems.

Are you sure you're using the 3.1.x mySQL JDBC drivers ?  Which
version precisely are you using?  I would like to report this problem
to mysql.com.

Thanks,

John

On 7/26/06, Walter Vaughan <[hidden email]> wrote:

> John Martin wrote:
> > Hi Si,
> >
> > Guess what, the problem is with the JDBC driver.  I downgraded from
> > the 3.1.13 Connector/J 3.1 to the 3.0.17 as you mentioned and
> > everything worked fine.
>
> Uh, I am using that driver just fine... (In fact sitting here at OSCON just
> recreated your scenario to make sure I'm not speaking from foggy memory).
>
> What ALWAYS seemed to bite me when I started installing ofBiz is is having a
> path the the JDK with a space in a directory name in it.
>
> I'm using Sun's AppServer JDK on a XP laptop with 512M memory...
> C:\ofbiz>echo %JAVA_HOME%
> C:\Sun\AppServer\jdk
>
> and MySQL 5.0.22-community-nt edition
>
> "%JAVA_HOME%\bin\java" -Xms256M -Xmx512M -jar ofbiz.jar to get things going
>
> post hoc ergo prompter hoc
>
> YMMV
> --
> Walter
>

[John Martin]

FYI - I just tried the next to latest version of the mysql drivers
(3.1.12) and everything worked fine.  So the problem has something to
do with the latest release (3.1.13).

John

[Walter Vaughan]

John Martin wrote:

> Hey Walter,
>
> My JAVA_HOME=C:\j2sdk1.4.2_12.  So I don't have that problem.  The
> application runs pretty well.  It is only one data type that causes
> problems when loading the data.  That really indicates a problem with
> the driver.
>
> In resolving it, I just replaced the 3.1.x JDBC driver with the 3.0.x,
> restarted the server and was able to do the import of the
> uom_conversion table without any problems.
>
> Are you sure you're using the 3.1.x mySQL JDBC drivers ?  Which
> version precisely are you using?  

C:\Program Files\Support Tools>%JAVA_HOME%/bin/java -version
java version "1.5.0_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05)
Java HotSpot(TM) Client VM (build 1.5.0_06-b05, mixed mode)

mysql-connector-java-3.1.13-bin.jar
placed in C:\ofbiz\framework\entity\lib\jdbc

and C:\ofbiz\framework\entity\config\entityengine.xml
contains...
<delegator name="default" entity-model-reader="main" entity-group-reader="main"
entity-eca-reader="main" distributed-cache-clear-enabled="false">
         <group-map group-name="org.ofbiz" datasource-name="localmysql"/>
         <group-map group-name="org.ofbiz.odbc" datasource-name="localderbyodbc"/>
     </delegator>

as well as the correct info for communicating with mysql in that file....

I would like to report this problem

> to mysql.com.
>
> Thanks,
>
> John
>
> On 7/26/06, Walter Vaughan <[hidden email]> wrote:
>
>> John Martin wrote:
>> > Hi Si,
>> >
>> > Guess what, the problem is with the JDBC driver.  I downgraded from
>> > the 3.1.13 Connector/J 3.1 to the 3.0.17 as you mentioned and
>> > everything worked fine.
>>
>> Uh, I am using that driver just fine... (In fact sitting here at OSCON
>> just
>> recreated your scenario to make sure I'm not speaking from foggy memory).
>>
>> What ALWAYS seemed to bite me when I started installing ofBiz is is
>> having a
>> path the the JDK with a space in a directory name in it.
>>
>> I'm using Sun's AppServer JDK on a XP laptop with 512M memory...
>> C:\ofbiz>echo %JAVA_HOME%
>> C:\Sun\AppServer\jdk
>>
>> and MySQL 5.0.22-community-nt edition
>>
>> "%JAVA_HOME%\bin\java" -Xms256M -Xmx512M -jar ofbiz.jar to get things
>> going
>>
>> post hoc ergo prompter hoc
>>
>> YMMV
>> --
>> Walter
>>
>
>

[John Martin]

I see that you are running the 1.5 JVM.  I wonder if the problem is a
conflict with the 3.1.13 JDBC driver with the 1.4 JVM?

It was my understanding that you should run ofBiz with 1.4 due to some
problems with the 1.5 JVM.  Apparently you are not having any problems
with that version?

Thanks again,

John

[cjhowe]

When David made the change to Geronimo, it seems to
have cleared up any issues.  At the same time, there
shouldn't be any benefit in using Java 5 at the moment
with OFBiz.  None of the code (AFAIK) takes advantage
of the improvements in JVM.

--- John Martin <[hidden email]> wrote:

> I see that you are running the 1.5 JVM.  I wonder if
> the problem is a
> conflict with the 3.1.13 JDBC driver with the 1.4
> JVM?
>
> It was my understanding that you should run ofBiz
> with 1.4 due to some
> problems with the 1.5 JVM.  Apparently you are not
> having any problems
> with that version?
>
> Thanks again,
>
> John
>

[Jacques Le Roux]

From: "Chris Howe" <[hidden email]>
> When David made the change to Geronimo, it seems to
> have cleared up any issues.  At the same time, there
> shouldn't be any benefit in using Java 5 at the moment
> with OFBiz.  None of the code (AFAIK) takes advantage
> of the improvements in JVM.

I confirm, this was related to JOTM, now with Geronimo the JDK 1.5 would be normally OK (I did not yet try)

Jacques

>
> --- John Martin <[hidden email]> wrote:
>
> > I see that you are running the 1.5 JVM.  I wonder if
> > the problem is a
> > conflict with the 3.1.13 JDBC driver with the 1.4
> > JVM?
> >
> > It was my understanding that you should run ofBiz
> > with 1.4 due to some
> > problems with the 1.5 JVM.  Apparently you are not
> > having any problems
> > with that version?
> >
> > Thanks again,
> >
> > John
> >

[Walter Vaughan]

Chris Howe wrote:

> When David made the change to Geronimo, it seems to
> have cleared up any issues.  At the same time, there
> shouldn't be any benefit in using Java 5 at the moment
> with OFBiz.  None of the code (AFAIK) takes advantage
> of the improvements in JVM.

Yesterday when I was talking to one of the OpenSolaris tech support guys at
OSCON, he was going on and on about how great Java6 will be since "dtrace" will
run natively inside Java6. When it's released, we should be able to see inside
our ofBiz apps and see exactly where time is being wasted. That, and they are
seeing up to 7 times improvement in proformance on their "Niagra" chipset vs
Java 5 with identical code.

FWIW I'm going to goof around with OpenSolaris live CD they gave me.