title: Rails MySQL migration, changing text column to decimal
body: <p>I have an items database with a text column where a user would enter a number and it was saved as a string.
  I need to change the column to a decimal, I have it working in postgresql from follwing another answer but don't know how to do the same in MySQL.</p>
  
  <p>This is the error I get when running the migration on a MySQL database;</p>
  
  <blockquote>
    <p>Mysql2::Error: Incorrect decimal value: '' for column '' at row -1:
    ALTER TABLE <code>items</code> CHANGE <code>quantity</code> <code>quantity</code> decimal DEFAULT NULL</p>
  </blockquote>
  
  <p>This is the migration I run, the postgres migration would only run after I converted empty strings to zero, it doesn't run with or without that on MySQL</p>
  
  <pre><code>class ChangeQuantityColumnOnItems &lt; ActiveRecord::Migration
    def up
     if connection.adapter_name.downcase == 'postgresql'
      Item.where(quantity: '').update_all(quantity: 0)
       connection.execute(%q{
         alter table items
         alter column quantity
         type decimal using quantity::decimal
       })
     else
        Item.where(quantity: '').update_all(quantity: 0)
       change_column :items, :quantity, :decimal
     end
    end
  
    def down
     change_column :items, :quantity, :text
    end
  end
  </code></pre>