Sometimes losing decimal scale on mysql - ok on sqlite

rubyonrails-talk
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

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.

4

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?