Problem with MySQL field types

[Adrian Crum]

I'm running MySQL through the field tests I introduced recently and it
is failing due to a bad fieldtypemysql.xml file. The file defines the
floating-point type like this:

<field-type-def type="floating-point" sql-type="DECIMAL(18,6)"
java-type="Double"><validate method="isSignedDouble"/></field-type-def>

When a floating point number is stored in that type of field, an
exception is thrown due to data truncation.

The correct entry is:

<field-type-def type="floating-point" sql-type="DOUBLE"
java-type="Double"><validate method="isSignedDouble"/></field-type-def>

but changing it in the trunk is going to cause problems for anyone using
MySQL.

Any thoughts on what we should do?

-Adrian

[Adam Heath-2]

Adrian Crum wrote:

> I'm running MySQL through the field tests I introduced recently and it
> is failing due to a bad fieldtypemysql.xml file. The file defines the
> floating-point type like this:
>
> <field-type-def type="floating-point" sql-type="DECIMAL(18,6)"
> java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>
> When a floating point number is stored in that type of field, an
> exception is thrown due to data truncation.
>
> The correct entry is:
>
> <field-type-def type="floating-point" sql-type="DOUBLE"
> java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>
> but changing it in the trunk is going to cause problems for anyone using
> MySQL.
>
> Any thoughts on what we should do?
>
> -Adrian

ofbiz doesn't really support upgrades.

[Adam Heath-2]

Adam Heath wrote:

> Adrian Crum wrote:
>> I'm running MySQL through the field tests I introduced recently and it
>> is failing due to a bad fieldtypemysql.xml file. The file defines the
>> floating-point type like this:
>>
>> <field-type-def type="floating-point" sql-type="DECIMAL(18,6)"
>> java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>
>> When a floating point number is stored in that type of field, an
>> exception is thrown due to data truncation.
>>
>> The correct entry is:
>>
>> <field-type-def type="floating-point" sql-type="DOUBLE"
>> java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>
>> but changing it in the trunk is going to cause problems for anyone using
>> MySQL.
>>
>> Any thoughts on what we should do?
>>
>> -Adrian
>
> ofbiz doesn't really support upgrades.

+in place.

[Scott Gray-2]

On 1/07/2010, at 7:58 AM, Adrian Crum wrote:

> I'm running MySQL through the field tests I introduced recently and it is failing due to a bad fieldtypemysql.xml file. The file defines the floating-point type like this:
>
> <field-type-def type="floating-point" sql-type="DECIMAL(18,6)" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>
> When a floating point number is stored in that type of field, an exception is thrown due to data truncation.
>
> The correct entry is:
>
> <field-type-def type="floating-point" sql-type="DOUBLE" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>
> but changing it in the trunk is going to cause problems for anyone using MySQL.
>
> Any thoughts on what we should do?
>
> -Adrian

What problems will it cause other than a warning at startup?  If that is the only issue then I don't think it's anything to worry about, manual column type changes are regularly required of people upgrading.

Regards
Scott

[Adrian Crum]

On 6/30/2010 2:30 PM, Scott Gray wrote:

> On 1/07/2010, at 7:58 AM, Adrian Crum wrote:
>
>> I'm running MySQL through the field tests I introduced recently and it is failing due to a bad fieldtypemysql.xml file. The file defines the floating-point type like this:
>>
>> <field-type-def type="floating-point" sql-type="DECIMAL(18,6)" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>
>> When a floating point number is stored in that type of field, an exception is thrown due to data truncation.
>>
>> The correct entry is:
>>
>> <field-type-def type="floating-point" sql-type="DOUBLE" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>
>> but changing it in the trunk is going to cause problems for anyone using MySQL.
>>
>> Any thoughts on what we should do?
>>
>> -Adrian
>
> What problems will it cause other than a warning at startup?  If that is the only issue then I don't think it's anything to worry about, manual column type changes are regularly required of people upgrading.
>
> Regards
> Scott

It's the same type of scenario as a PK change - it will require changing
an existing database, possible data export/import, etc.

At first I was surprised no one has mentioned it before, but now I see
MySQL fails other tests as well, due to Timestamp precision (or lack
thereof).

Anyways, I believe the SQL type should be corrected, I was just hesitant
to do it and I needed some feedback.

-Adrian

[Scott Gray-2]

On 1/07/2010, at 9:35 AM, Adrian Crum wrote:

> On 6/30/2010 2:30 PM, Scott Gray wrote:
>> On 1/07/2010, at 7:58 AM, Adrian Crum wrote:
>>
>>> I'm running MySQL through the field tests I introduced recently and it is failing due to a bad fieldtypemysql.xml file. The file defines the floating-point type like this:
>>>
>>> <field-type-def type="floating-point" sql-type="DECIMAL(18,6)" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>>
>>> When a floating point number is stored in that type of field, an exception is thrown due to data truncation.
>>>
>>> The correct entry is:
>>>
>>> <field-type-def type="floating-point" sql-type="DOUBLE" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>>
>>> but changing it in the trunk is going to cause problems for anyone using MySQL.
>>>
>>> Any thoughts on what we should do?
>>>
>>> -Adrian
>>
>> What problems will it cause other than a warning at startup?  If that is the only issue then I don't think it's anything to worry about, manual column type changes are regularly required of people upgrading.
>>
>> Regards
>> Scott
>
> It's the same type of scenario as a PK change - it will require changing an existing database, possible data export/import, etc.

PK changes are a bit more involved because data actually needs to be moved, in this case though OFBiz should continue to function as it was.  The main reason OFBiz doesn't automatically take care of column type changes is because of the risk of data loss so we force users to do it themselves.  IMO if it's reported at startup and doesn't cause any harm then we should just do it (and we have, many times before).

> At first I was surprised no one has mentioned it before, but now I see MySQL fails other tests as well, due to Timestamp precision (or lack thereof).
>
> Anyways, I believe the SQL type should be corrected, I was just hesitant to do it and I needed some feedback.

+1

[BJ Freeman]

the postgresql has a new one for the newer versions.

=========================
BJ Freeman
http://bjfreeman.elance.com
Strategic Power Office with Supplier Automation  <http://www.businessesnetwork.com/automation/viewforum.php?f=52>
Specialtymarket.com  <http://www.specialtymarket.com/>

Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man
Linkedin
<http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro>


Adrian Crum sent the following on 6/30/2010 12:58 PM:

> I'm running MySQL through the field tests I introduced recently and it
> is failing due to a bad fieldtypemysql.xml file. The file defines the
> floating-point type like this:
>
> <field-type-def type="floating-point" sql-type="DECIMAL(18,6)"
> java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>
> When a floating point number is stored in that type of field, an
> exception is thrown due to data truncation.
>
> The correct entry is:
>
> <field-type-def type="floating-point" sql-type="DOUBLE"
> java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>
> but changing it in the trunk is going to cause problems for anyone using
> MySQL.
>
> Any thoughts on what we should do?
>
> -Adrian
>

[Adrian Crum]

On 6/30/2010 3:05 PM, Scott Gray wrote:

> On 1/07/2010, at 9:35 AM, Adrian Crum wrote:
>
>> On 6/30/2010 2:30 PM, Scott Gray wrote:
>>> On 1/07/2010, at 7:58 AM, Adrian Crum wrote:
>>>
>>>> I'm running MySQL through the field tests I introduced recently and it is failing due to a bad fieldtypemysql.xml file. The file defines the floating-point type like this:
>>>>
>>>> <field-type-def type="floating-point" sql-type="DECIMAL(18,6)" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>>>
>>>> When a floating point number is stored in that type of field, an exception is thrown due to data truncation.
>>>>
>>>> The correct entry is:
>>>>
>>>> <field-type-def type="floating-point" sql-type="DOUBLE" java-type="Double"><validate method="isSignedDouble"/></field-type-def>
>>>>
>>>> but changing it in the trunk is going to cause problems for anyone using MySQL.
>>>>
>>>> Any thoughts on what we should do?
>>>>
>>>> -Adrian
>>>
>>> What problems will it cause other than a warning at startup?  If that is the only issue then I don't think it's anything to worry about, manual column type changes are regularly required of people upgrading.
>>>
>>> Regards
>>> Scott
>>
>> It's the same type of scenario as a PK change - it will require changing an existing database, possible data export/import, etc.
>
> PK changes are a bit more involved because data actually needs to be moved, in this case though OFBiz should continue to function as it was.  The main reason OFBiz doesn't automatically take care of column type changes is because of the risk of data loss so we force users to do it themselves.  IMO if it's reported at startup and doesn't cause any harm then we should just do it (and we have, many times before).

I hadn't thought of that - thanks for pointing it out.