Mysql2::Error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Question

I have created a demo application in rails 3.2.9 and ruby versiion 2.0.0 . After scaffolding Blog model I am trying to migrate it, but having following issue.

# rake db:migrate

== CreateBlogs: migrating ====================================================

-- create_table(:blogs) 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 blogs (id int(11) DEFAULT NULL auto_increment PRIMARY KEY, title varchar(255), description text, created_at datetime NOT NULL, updated_at datetime NOT NULL) ENGINE=InnoDB /usr/local/rvm/gems/ruby-2.0.0-p648@demo-app/gems/activerecord-3.2.9/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in query' /usr/local/rvm/gems/ruby-2.0.0-p648@demo-app/gems/activerecord-3.2.9/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:inblock in execute' /usr/local/rvm/gems/ruby-2.0.0-p648@demo-app/gems/activerecord-

....

....

....

-3.2.9/lib/active_record/migration.rb:551:in migrate' /usr/local/rvm/gems/ruby-2.0.0-p648@demo-app/gems/activerecord-3.2.9/lib/active_record/railties/databases.rake:179:inblock (2 levels) in ' /usr/local/rvm/gems/ruby-2.0.0-p648@demo-app/gems/rake-11.3.0/exe/rake:27:in `' Tasks: TOP => db:migrate (See full trace by running task with --trace)

Answer 1

Add this initializer to fix the problem:

config/initializers/abstract_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

Answer 2

I had this problem too (mysql 0.3.21 and Rails 3.2.22.5). I fixed it by adding a file in my application config/initializers/mysql2_adapter.rb (as there was no such file before) with the below content:

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

Then, in my environment.rb file, I've added below content:

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

Answer 3

This error occurred when not null is defined with primary key column.

Even if you defined any composite key, then those column definition does not contains the not null criteria.

Search for those column, remove the not null criteria, this is one of the solution to remove the error.

Answer 4

In this line id int(11) DEFAULT NULL auto_increment PRIMARY KEY you are creating a primary key (which can never be null), with the default value NULL. In your migration file (or schema.rb) you should make sure the DEFAULT NULL part is removed.