Re: Problem with MySQL field types

[Scott Gray <sc...@hotwaxmedia.com>]

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

Re: Problem with MySQL field types

[Adrian Crum <ad...@hlmksw.com>]

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.