# Sometimes losing decimal scale on mysql - ok on sqlite

**URL:** <https://www.ruby-forum.com/t/sometimes-losing-decimal-scale-on-mysql-ok-on-sqlite/185275>\
**Category:** Rails\
**Created:** [March 17, 2010, 10:47am UTC](https://www.ruby-forum.com/t/sometimes-losing-decimal-scale-on-mysql-ok-on-sqlite/185275 "2010-03-17T10:47:17Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Owain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/fbc32d/32.png) [@Owain](https://www.ruby-forum.com/u/Owain)\
**Post date:** [March 17, 2010, 10:47am UTC](https://www.ruby-forum.com/t/sometimes-losing-decimal-scale-on-mysql-ok-on-sqlite/185275/1 "2010-03-17T10:47:17Z")

</div>

I posted this yesterday but it was a bit long-winded:  
[http://groups.google.com/group/rubyonrails-talk/browse\_thread/thread/9a6db7467a16911e](http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/9a6db7467a16911e)

Here is a more succinct version.

Migration generates decimal(8,2) column type ok on sqlite but  
decimal(10,0) on mysql. So I lose pence and cents on mysql!

The migration: t.decimal :amount, :precision =\> 8, :scale =\> 2  
sqlite column definition from using .scheme table\_name: “amount”  
decimal(8,2)  
mysql column definition from mysqldump: `amount` decimal(10,0)  
default NULL

I have another table where it works ok on some columns and not on  
others. See OP.

mysql (Ver 5.0.67), sqllite (v 3.6.12), Rails 2.3.5

Anyone have any ideas?

O.

---

<div class="post-metadata">

**Author:** ![Owain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/fbc32d/32.png) [@Owain](https://www.ruby-forum.com/u/Owain)\
**Post date:** [March 17, 2010, 12:18pm UTC](https://www.ruby-forum.com/t/sometimes-losing-decimal-scale-on-mysql-ok-on-sqlite/185275/2 "2010-03-17T12:18:01Z")

</div>

Here are two links that explain that you need to be careful on decimal  
columns when using mysql on the target platform. sqlite3 is a little  
more forgiving.

[http://scottmotte.com/archives/86.html](http://scottmotte.com/archives/86.html)

> **[Problems in Rails between Development and Test Databases - Cory Foy](http://blog.coryfoy.com/2008/06/problems-in-rails-between-development-and-test-databases/)**
>
> One of the things I’ve been getting setup this week on my new laptop is my Ruby environment. I went through the Apple Leopard Rails Tutorial and ran into one big hiccup at the end – my tests wouldn’t pass. I had created an app and ran the following:...

I sorted my problem out by running:

rake RAILS\_ENV=staging db:drop  
rake RAILS\_ENV=staging db:create  
rake RAILS\_ENV=staging db:migrate

I am sure that I did this … but it could have been very late!

So in a nutshell, use a scale and precision on decimal if you intend  
your target dbms to be mysql.

O.

---

<div class="post-metadata">

**Author:** ![Owain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/fbc32d/32.png) [@Owain](https://www.ruby-forum.com/u/Owain)\
**Post date:** [March 17, 2010, 9:54pm UTC](https://www.ruby-forum.com/t/sometimes-losing-decimal-scale-on-mysql-ok-on-sqlite/185275/3 "2010-03-17T21:54:19Z")

</div>

Owain wrote:

> rake RAILS\_ENV=staging db:drop  
> rake RAILS\_ENV=staging db:create  
> rake RAILS\_ENV=staging db:migrate

RichardOnRails wrote:

> That’s easier than the three-stage solution you used, though  
> equivalent in effect I believe.

For what it’s worth the “three-stage solution” can be done with one rake  
task:  
rake RAILS\_ENV=staging db:migrate:reset

However, the change\_column approach seems the safer and cleaner  
solution. This technique keeps the migrations moving forward.

Given that dropping, recreating and migrating fixed your problem this  
leads me to this question, "Was the precision and scale explicitly  
configured in the migration before running the migrations?

---

<div class="post-metadata">

**Author:** ![Owain](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/fbc32d/32.png) [@Owain](https://www.ruby-forum.com/u/Owain)\
**Post date:** [March 17, 2010, 3:45pm UTC](https://www.ruby-forum.com/t/sometimes-losing-decimal-scale-on-mysql-ok-on-sqlite/185275/4 "2010-03-17T15:45:15Z")

</div>

I’m running mysql 5.0.37 rails 2.3.5 on winxp-pro/sp3

I left off precision and scale on my rails app and found my decimal  
places missing in the DB. Did a change of that attribute to (10,2)  
via migration:

change\_column :expenses, :amount, :decimal, :precision =\> 10, :scale  
=\> 2

and dollars-and-cents type data flowed through perfectly.

## That’s easier than the three-stage solution you used, though equivalent in effect I believe.

Richard