MySQL error for default value of decimal during Rails migrate

Question

I'm setting up my production database in Rails. The development uses SQlite and production is in MySQL.

During the migration I am getting an Invalid default value error for all decimal columns that have a default value. Here is an example.

ActiveRecord::StatementInvalid: Mysql2::Error: Invalid default value for 'retail_markup': CREATE TABLE `defaults` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `company_id` int(11), `styles_number_of_items_per_row` int(11), `created_at` datetime, `updated_at` datetime, `retail_markup` decimal(2,2) DEFAULT '2.0', `commission_rate` smallint DEFAULT 15) ENGINE=InnoDB

The database schema is as follows:

 create_table "defaults", force: true do |t|
    t.integer  "company_id"
    t.integer  "styles_number_of_items_per_row"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.decimal  "retail_markup", precision: 2, scale: 2, default: 2.0
    t.integer  "commission_rate", limit: 2, default: 15
  end

I have had a look around and can no figure out what is going wrong here.

Many thanks in advance.

Answer 1

You cannot have a default value of 2.0 on a decimal field defined with precision 2 and scale 2.

By defining a scale of 2, 2.0 will become 2.00 which requires 3 significant digits to hold it.

If your retail markup has only 1 decimal place, change your definition to:

t.decimal  "retail_markup", precision: 2, scale: 1, default: 2.0

Alternatively if you need to allow 2 decimal places, change your definition to:

t.decimal  "retail_markup", precision: 3, scale: 2, default: 2.00