Sometimes losing decimal scale on mysql - ok on sqlite

Rails
1

I posted this yesterday but it was a bit long-winded:
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.

2

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

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.

3

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?

4

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