Solved

MySQL is rounding out and trunking the number

Posted on 2009-06-28
7
434 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:kiarash13581979
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 1

Author Comment

by:kiarash13581979
ID: 24733487
This is the error message that I get when tried to change it from "Float" to "Double":
Failed to save field : '' is not a valid field size
0
 
LVL 3

Expert Comment

by:tcsaddul9
ID: 24734248
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.
0
 
LVL 1

Author Comment

by:kiarash13581979
ID: 24734390
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.
0
 
LVL 1

Author Comment

by:kiarash13581979
ID: 24739362
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
0
 
LVL 3

Accepted Solution

by:
tcsaddul9 earned 500 total points
ID: 24745191
There is an error in the 'DEFAULT' clause of your statement. Please try this:

alter table `items` modify `amount` numeric(10,2) null default '0.00'

In my apps, I make sure that null is not used, so I use something like this:

alter table `items` modify `amount` numeric(10,2) NOT null default '0.00'



0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question