"Kerry Frater" <[EMAIL PROTECTED]> wrote on 02/02/2006 11:21:44
AM:
> I am trying to test some code on the new 5.0 version and am getting
> problems.
>
> I currently have two MYSQL's running on two differing machines. Both
have
> the same schema set up by the my own program. The difference is that
Machine
> 1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a
fresh
> installation of version 5.
>
> My Data insertion code works perfectly well with the Version 5 instance
that
> was upgraded from version 4, but fails on the pure version 5. The
> Administrator interface tells me that the tables are OK and I cannot see
any
> difference between the V4 upgraded to 5 table against the pure V5
install.
>
> The code being used is
> INSERT INTO Invs
>
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
> VatRate)
> VALUES
> ('1234#6','19991016','C','19990731','\"Orig Bill
> £728.50\"',364.68,'A',17.50);
>
> The table columns are set to
> char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2).
The
> table is MyISAM.
>
> There error produced on the pure version 5 installation is:
> "Out of Range value adjusted for column VATRate at row 1"
> and then quits. I have let the installation of MySQL use its defaults
and is
> of charset "latin1" if it is an issue. The above statement does not
error on
> the upgraded version of MySQL but adds the row with no problem.
>
> The set up program is using a function
> with SQLBatch.SQL do
> begin
> Add('CREATE TABLE Invs (');
> Add('InvRef char(12) default NULL,');
> Add('InvDate date,');
> Add('OutgoingRef char(1),');
> Add('OutgoingRef2 date,');
> Add('BillDescription char(40),');
> Add('BillAmount decimal(12,2),');
> Add('VATCode char(1),');
> Add('VATRate decimal(3,2),');
> Add(' index (OutgoingRef2)');
> Add(') TYPE=MyISAM;');
> end;
> SQLBatch.ExecSql;
>
> This seems to work fine. Any ideas on why it works with one and not the
> other?
>
> Kerry
>
In older versions, the range checking for DECIMAL() columns was lax (a
bug). The range checking has been fixed, which may be causing your
problem. For example: in the older versions a DECIMAL(12,3) column could
actually contain a positive number with 10 digits to the left of the
decimal place, instead of the 9 allowed by the definition, because the
extra digit was being allowed to reside in the space set aside for the
sign indicator. By fixing the implementation to actually honor the
definition of the column, a few programs that used that extra, illegal
space started having the exact problems you describe.
Now for the example you provided, you defined VATRate as DECIMAL(3,2)
which meant you wanted that field to have the space for 3 digits with 2 of
them to the right of the decimal place. Your potential range of values for
a field defined this way is from -9.99 to +9.99. You tried to insert the
value 17.50 . With the older, buggy, implementation that would have fit
into a (3,2) because the 1 of the 17 would have fit into the space the +
sign would have occupied if it were displayed. In the fixed
implementation, 17 is too large and will no longer fit.
My suggestion is to resize the field to (4,2) so that you have the range
-99.99 to +99.99 .
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
