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

Answer 2

Did not have luck with the solutions above (My env: Rails 3.0.20, MySQL 5.7.13, Ruby 1.9.3p551). Was able to get around it by overwriting the ActiveRecord::ConnectionAdapters::ColumnDefinition class. See below:

class ActiveRecord::ConnectionAdapters::ColumnDefinition
  def sql_type
    type.to_sym == :primary_key ? 'int(11) auto_increment PRIMARY KEY' : base.type_to_sql(type.to_sym, limit, precision, scale) rescue type
  end 
end

Stored this in config/initializers/column_definition.rb

Answer 3

I had this problem too (mysql 5.7.17 and Rails 4.0.0). I fixed it by adding a file config/initializers/mysql2_adapter.rb

require 'active_record/connection_adapters/mysql2_adapter'
class ActiveRecord::ConnectionAdapters::Mysql2Adapter
  NATIVE_DATABASE_TYPES[:primary_key] = "int(11) auto_increment PRIMARY KEY"
end

and then, in my environment.rb file:

require File.expand_path('../initializers/mysql2_adapter', __FILE__)

Answer 4

Since MySQL 5.7.3 a primary key declared as NULL produces an error:

Columns in a PRIMARY KEY must be NOT NULL, but if declared explicitly as NULL produced no error. Now an error occurs. For example, a statement such as CREATE TABLE t (i INT NULL PRIMARY KEY) is rejected. The same occurs for similar ALTER TABLE statements. (Bug #13995622, Bug #66987, Bug #15967545, Bug #16545198)

But the create_table in your Rails version still wants a DEFAULT or NULL for the PRIMARY KEY. I have solved the issue by updating to a newer rails version.

Answer 5

I solved this problem with a Rails app on JRuby I'm working on by upgrading the mysql-adapter.

I was using the gem activerecord-jdbcmysql-adapter v1.3.14 and upgraded to v1.3.21

Check your jdbc adapter version before monkey patching a solution.