CODEWITHSUNDEEP
mysqlruby-on-railssqliterakedbmigrate
hey-ashy
hey-ashy

Reputation: 23

MySQL error for default value of decimal during Rails migrate

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.

Upvotes: 2

Views: 1234

Answers (1)

user740584
user740584

Reputation:

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

Upvotes: 1

Related Questions