migration snippets

How to fix "Mysql::Error: Duplicate entry '2147483647' for key 3: INSERT INTO xxx"

Tagged mysql, numeric, error, gotcha, migration  Languages ruby

2147483647 is the maximum for an integer column in MySQL, so this error probably means you've exceeded this limit somewhere in your code.

Rails automatically detects the best type for your columns, so be sure to specify the correct limit when creating the column with migrations:

# from activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb
        case limit
        when 1; 'tinyint'
        when 2; 'smallint'
        when 3; 'mediumint'
        when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
        when 5..8; 'bigint'
        else raise(ActiveRecordError, "No integer type has byte size #{limit}")
        end

This Rails migration code would create a big integer column:

t.integer :product_id, :null => false, :limit => 8

See the section on Numeric Types in the MySQL documentation for more information.

How to dump Postgres extensions and all schemas with Rails

Tagged lol, migration, rails, wtf  Languages bash, ruby

When running rake db:reset you might get one of the following informative errors:

  function public.gen_random_uuid() does not exist
  db/structure.sql:22: ERROR:  schema "public" already exists

To fix them tell Ruby to dump everything by editing config/application.rb:

module XXX
  class Application < Rails::Application
    config.active_record.dump_schemas = :all

Tested with Rails 6.

References: https://github.com/rails/rails/issues/17157#issuecomment-77400517