Mysql and money: precision problems with FLOAT(10,2)Master to Slave to Slave Configuration in MySQLfind and...
What is better: yes / no radio, or simple checkbox?
Can a space-faring robot still function over a billion years?
Affine transformation of circular arc in 3D
Can a Mimic (container form) actually hold loot?
I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?
How can I be pwned if I'm not registered on the compromised site?
Infinitive vs Gerund
Should we avoid writing fiction about historical events without extensive research?
Professor forcing me to attend a conference
Where do you go through passport control when transiting through another Schengen airport on your way out of the Schengen area?
Error in TransformedField
Naming Characters after Friends/Family
What is "desert glass" and what does it do to the PCs?
School performs periodic password audits. Is my password compromised?
Questions of the type "What do you think other people would think?"
If nine coins are tossed, what is the probability that the number of heads is even?
Are small insurances worth it
In the world of The Matrix, what is "popping"?
Align equations with text before one of them
Problems with rounding giving too many digits
PTIJ: Aliyot for the deceased
Why is my explanation wrong?
What's the difference between Compensation, Indemnity, and Reparations?
“I had a flat in the centre of town, but I didn’t like living there, so …”
Mysql and money: precision problems with FLOAT(10,2)
Master to Slave to Slave Configuration in MySQLfind and insert row to another table using mysql triggerHow to optimize indexes on MySQL query with various sortsIdentical query, tables, but different EXPLAIN and performanceInserting empty string on Windows MySQL doesn't use default valueHow can I use MySQL variables in subqueries?adding a column with default value to slow in mysq 5.1max allowed packet keep changingError 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)select MAX() from MySQL view (2x INNER JOIN) is slow
I have a table like this:
mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cost | float(10,2) | YES | | NULL | |
+-----------------------+---------------+------+-----+---------+----------------+
I (naively?) thought that would be a good way to store amounts.
I am 3 days from release, and I discovered this:
mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from transactions where id = 14891;
+-------+---------------+
| id | cost |
+-------+---------------+
| 14891 | 162295.98 |
+-------+---------------+
...?!? I entered .99, not .98!
So:
- Why is this doing it?
- How do I get out of this problem?
Would decimal work? I tried with:
mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL;
Query OK, 1120 rows affected, 119 warnings (0.14 sec)
Records: 1120 Duplicates: 0 Warnings: 119
mysql>
However I am getting scary warnings. With "show warnings" I get:
mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1265 | Data truncated for column 'cost' at row 10 |
| Note | 1265 | Data truncated for column 'cost' at row 369 |
| Note | 1265 | Data truncated for column 'cost' at row 371 |
| Note | 1265 | Data truncated for column 'cost' at row 378 |
| Note | 1265 | Data truncated for column 'cost' at row 402 |
| Note | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------
How do I see which one "row 10" is anyway?
KEY QUESTION: How do I get out of this hole safely?
(Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?
mysql
asked 8 mins ago
MercMerc
1011
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have a table like this:
mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cost | float(10,2) | YES | | NULL | |
+-----------------------+---------------+------+-----+---------+----------------+
I (naively?) thought that would be a good way to store amounts.
I am 3 days from release, and I discovered this:
mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from transactions where id = 14891;
+-------+---------------+
| id | cost |
+-------+---------------+
| 14891 | 162295.98 |
+-------+---------------+
...?!? I entered .99, not .98!
So:
- Why is this doing it?
- How do I get out of this problem?
Would decimal work? I tried with:
mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL;
Query OK, 1120 rows affected, 119 warnings (0.14 sec)
Records: 1120 Duplicates: 0 Warnings: 119
mysql>
However I am getting scary warnings. With "show warnings" I get:
mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1265 | Data truncated for column 'cost' at row 10 |
| Note | 1265 | Data truncated for column 'cost' at row 369 |
| Note | 1265 | Data truncated for column 'cost' at row 371 |
| Note | 1265 | Data truncated for column 'cost' at row 378 |
| Note | 1265 | Data truncated for column 'cost' at row 402 |
| Note | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------
How do I see which one "row 10" is anyway?
KEY QUESTION: How do I get out of this hole safely?
(Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?
mysql
asked 8 mins ago
MercMerc
1011
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have a table like this:
mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cost | float(10,2) | YES | | NULL | |
+-----------------------+---------------+------+-----+---------+----------------+
I (naively?) thought that would be a good way to store amounts.
I am 3 days from release, and I discovered this:
mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from transactions where id = 14891;
+-------+---------------+
| id | cost |
+-------+---------------+
| 14891 | 162295.98 |
+-------+---------------+
...?!? I entered .99, not .98!
So:
- Why is this doing it?
- How do I get out of this problem?
Would decimal work? I tried with:
mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL;
Query OK, 1120 rows affected, 119 warnings (0.14 sec)
Records: 1120 Duplicates: 0 Warnings: 119
mysql>
However I am getting scary warnings. With "show warnings" I get:
mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1265 | Data truncated for column 'cost' at row 10 |
| Note | 1265 | Data truncated for column 'cost' at row 369 |
| Note | 1265 | Data truncated for column 'cost' at row 371 |
| Note | 1265 | Data truncated for column 'cost' at row 378 |
| Note | 1265 | Data truncated for column 'cost' at row 402 |
| Note | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------
How do I see which one "row 10" is anyway?
KEY QUESTION: How do I get out of this hole safely?
(Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?
mysql
asked 8 mins ago
MercMerc
1011
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a table like this:
mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cost | float(10,2) | YES | | NULL | |
+-----------------------+---------------+------+-----+---------+----------------+
I (naively?) thought that would be a good way to store amounts.
I am 3 days from release, and I discovered this:
mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from transactions where id = 14891;
+-------+---------------+
| id | cost |
+-------+---------------+
| 14891 | 162295.98 |
+-------+---------------+
...?!? I entered .99, not .98!
So:
- Why is this doing it?
- How do I get out of this problem?
Would decimal work? I tried with:
mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL;
Query OK, 1120 rows affected, 119 warnings (0.14 sec)
Records: 1120 Duplicates: 0 Warnings: 119
mysql>
However I am getting scary warnings. With "show warnings" I get:
mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1265 | Data truncated for column 'cost' at row 10 |
| Note | 1265 | Data truncated for column 'cost' at row 369 |
| Note | 1265 | Data truncated for column 'cost' at row 371 |
| Note | 1265 | Data truncated for column 'cost' at row 378 |
| Note | 1265 | Data truncated for column 'cost' at row 402 |
| Note | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------
How do I see which one "row 10" is anyway?
KEY QUESTION: How do I get out of this hole safely?
(Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?
mysql
mysql
asked 8 mins ago
MercMerc
1011
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
MercMerc
1011
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
MercMerc
1011
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 mins ago
MercMerc
1011
asked 8 mins ago
MercMerc
1011
1011
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Merc is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Merc is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231625%2fmysql-and-money-precision-problems-with-float10-2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Merc is a new contributor. Be nice, and check out our Code of Conduct.
Merc is a new contributor. Be nice, and check out our Code of Conduct.
Merc is a new contributor. Be nice, and check out our Code of Conduct.
Merc is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231625%2fmysql-and-money-precision-problems-with-float10-2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
