kiarash13581979
asked on
MySQL is rounding out and trunking the number
Hi Every one,
I have mySQL server running on a VM, its been working fine, and I have over 200,000 record saved on it, until couple of days ago that one of the user entered $ amount of 33448.67 on the application, but on the MySQL it got saved 33448.70 ( instead of .67 got saved as .70) I tried to edit it in the MySQL but it did a same thing, is jut trunking it up. I removed one digit from the beginning decimal digits and it got saved fine!!
I looked at the field parameters, the Data type of this field is "Float" I tried to change it to double but it wouldn't change and I got an error that Field " is not valid!
I tried to change the Width and decimals and still I got a error message too.
I have to say that I'm using Webmin interface to connect to the Mysql
any suggestion?
I have mySQL server running on a VM, its been working fine, and I have over 200,000 record saved on it, until couple of days ago that one of the user entered $ amount of 33448.67 on the application, but on the MySQL it got saved 33448.70 ( instead of .67 got saved as .70) I tried to edit it in the MySQL but it did a same thing, is jut trunking it up. I removed one digit from the beginning decimal digits and it got saved fine!!
I looked at the field parameters, the Data type of this field is "Float" I tried to change it to double but it wouldn't change and I got an error that Field " is not valid!
I tried to change the Width and decimals and still I got a error message too.
I have to say that I'm using Webmin interface to connect to the Mysql
any suggestion?
MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001
You should use the field type Numeric (P, S) or Decimal (P, S)
(P, S) means P is the precision and S is the scale sometimes stated as P is the width and S is the decimal places to the right of period. For example: Numeric (10, 2) would have a 10 digit precision and 2 as scale or simply call it 10 is the width and 2 decimal places.
You should use the field type Numeric (P, S) or Decimal (P, S)
(P, S) means P is the precision and S is the scale sometimes stated as P is the width and S is the decimal places to the right of period. For example: Numeric (10, 2) would have a 10 digit precision and 2 as scale or simply call it 10 is the width and 2 decimal places.
ASKER
if I change my current fata field from "Float" to Numeric(10, 2) what will happen to my current data that are in the Database? I just don't want any changes to the current data. and I just want to make sure that I don't loose or change any of them.
ASKER
I have tried to change but I get the error message bellow from Webin tool that use:
Failed to save field : SQL alter table `items` modify `amount` float(10,2) null default none failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'none' at line 1
Failed to save field : SQL alter table `items` modify `amount` float(10,2) null default none failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'none' at line 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Failed to save field : '' is not a valid field size