PRIMARY KEY issue with creating tables in Rails using rake db:migrate command with mysql

Question

My version of rails is 4.0.0, my version of mysql is Ver 14.14 Distrib 5.7.9, for Win64 (x86_64). I am operating of an older version of rails as I was getting some clashes with the mysql as per my previous question Here. (check Kalelc's approved answer for my recourse)

upon running

rake db:migrate 

I get the following error

==  CreateUsers: migrating ====================================================
-- create_table(:users)
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead: CREATE TABLE `users` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `first_name` varchar(25), `last_name` varchar(50), `email` varchar(255) DEFAULT '' NOT NULL, `password` varchar(40), `created_at` datetime, `updated_at` datetime) ENGINE=InnoDBC:/Users/Lizanne/Documents/Code/Sites/simple_cms/db/migrate/20151116154434_create_users.rb:3:in `up'
C:in `migrate'
ActiveRecord::StatementInvalid: Mysql2::Error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead: CREATE TABLE `users` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `first_name` varchar(25), `last_name` varchar(50), `email` varchar(255) DEFAULT '' NOT NULL, `password` varchar(40), `created_at` datetime, `updated_at` datetime) ENGINE=InnoDB
C:/Users/Lizanne/Documents/Code/Sites/simple_cms/db/migrate/20151116154434_create_users.rb:3:in `up'
C:in `migrate'
Mysql2::Error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
C:/Users/Lizanne/Documents/Code/Sites/simple_cms/db/migrate/20151116154434_create_users.rb:3:in `up'
C:in `migrate'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

I do not set any values to NULL in my code, here is the code

Class CreateUsers < ActiveRecord::Migration

  def up
    create_table :users do |t| 
      t.column "first_name", :string, :limit => 25 
      t.string "last_name", :limit => 50
      t.string "email", :default => "", :null => false 
      t.string "password", :limit => 40
      t.timestamps
    end
  end

  def down
    drop_table :users
  end
end

This code is exactly as shown in the tutorials I am following. I have also investigated other similar issues here on stack overflow and have followed the advice given. I have tried the monkey patch as suggested

# lib/patches/abastract_mysql_adapter.rb
class ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter
  NATIVE_DATABASE_TYPES[:primary_key] = "int(11) auto_increment PRIMARY KEY"
end

I inserted this file into a folder I have created called patches inside the lib of my simple_cms application. I have saved down the file as "abstract_mysql_adapter.rb" as suggested in the same monkey patch. I have updated my environment.rb of the simple_cms application with the following

require File.expand_path('../../lib/patches/abstract_mysql_adapter.rb', __FILE__)

if I then run a rake db:migrate command

rake aborted!
LoadError: cannot load such file -- C:/Users/Lizanne/Documents/Code/Sites/simple_cms/lib/patches/abstract_mysql_adapter.rb
C:/Users/Lizanne/Documents/Code/Sites/simple_cms/config/environment.rb:3:in `<top (required)>'
Tasks: TOP => db:migrate => environment
(See full trace by running task with --trace)

C:/Users/Lizanne/Documents/Code/Sites/simple_cms/lib/patches/abstract_mysql_adapter.rb is most definitely the path to the monkey patch. Have I put the patch in the wrong place? What am I doing wrong here, scratching my head over this one? Apologies if this is obvious to some but I'm returning to coding after a very long abstinence and I cant get my head around the issue. Many thanks in advance for you help :)

Answer 1

I too recently faced same issue.

MySQL 5.7 no longer supports null default values for the primary key.

By overriding the Native default for primary keys in MySql you can resolve your issue.

In config/initializers/abstract_mysql_adapter.rb:

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  NATIVE_DATABASE_TYPES[:primary_key] = "int(11) auto_increment PRIMARY KEY"
end

For mysql2 it should be config/initializers/abstract_mysql2_adapter.rb:

class ActiveRecord::ConnectionAdapters::Mysql2Adapter
  NATIVE_DATABASE_TYPES[:primary_key] = "int(11) auto_increment PRIMARY KEY"
end