Creating tables and problems with primary key in Rails

Question

When I try to run the following code in Rails using Mysql2 as database manager:

rake db:migrate

I obtain the following error:

 rake aborted!
 "Mysql2::Error: All parts of a PRIMARY KEY must be NOT NULL:"

Why do I get this error, if the primary key in a table by default is NOT "null"?

Migration code, however :

class CreateUsers < ActiveRecord::Migration
   def change
    create_table :users do |t|
     t.string "first_name"
     t.timestamps
    end
   end 
end

Answer 1

I had a same problem before, and I solved according to here https://github.com/rails/rails/pull/13247#issuecomment-32425844

With Rails 2.3.5, MySQL version 5.7.9 and mysql gem you need to have this bit as an initializer 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

From the mysql 5.7 breaking change page:

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)

Changes in MySQL 5.7.3 (2013-12-03, Milestone 13)

This issue was fixed 3 months ago. I don't know what Rails version it's going to be part of

monkey patch is here

EDIT: It's been more than a year that this fix was merged into master. So, it must be part of the last version of Rails.

EDIT: Indeed, the commit that fixes this can be found at: https://github.com/yahonda/rails/commit/b6655885ef13cf8d1705dc9b5232846f0207febd , and shows that the fix is included in v4.2.0.beta1, v4.1.6.rc1, v4.1.5, v4.1.4, v4.1.3, v4.1.2, v4.1.2.rc3, v4.1.2.rc2, v4.1.2.rc1, v4.1.1, v4.1.0, v4.1.0.rc2, v4.1.0.rc1, v4.1.0.beta2, v4.1.0.beta1. If upgrading to at least v4.1.0 is an option for you, then it should fix the problem.

Answer 3

I had this issue running a rails 4.0.x app using mysql 5.7.x. I was able to fix it by upgrading to rails 4.2.x and upgrading my gems.

(I'm sure these gemfiles need work, but hopefully they are still helpful)

Old Gemfile

source 'https://rubygems.org'
ruby '2.0.0'

# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '4.0.13'
gem 'mysql2'
# Use unicorn as the app server
# gem 'unicorn'

gem 'sidekiq'
gem 'sinatra'
gem 'whenever'

gem 'thin'
gem 'faye'
gem 'faye-websocket'

#pdf generation
gem 'wicked_pdf'
gem 'wkhtmltopdf-binary'

# Assets
gem 'backbone-on-rails', '~> 0.9.9'
gem 'coffee-rails', '~> 4.0.1'
gem 'coffee-script-source', '=1.8.0'
gem 'ejs'                                   # uses templates for backbone
gem 'jquery-rails'
gem 'jquery-ui-rails'
gem 'neat'
gem 'sass-rails', '~> 4.0.3'
gem 'therubyracer'
gem 'turbolinks'
gem 'uglifier', '>= 1.3.0'

gem 'active_model_serializers', '0.8.3'
gem 'acts_as_list'
gem 'bcrypt-ruby', '~> 3.1.2'
gem 'bootstrap-sass', '~> 3.2.0.0'
gem 'bower-rails'
gem 'carrierwave'
gem 'enumerize'
gem 'embedly'                         # Url to Attachment Processing
gem 'fancybox2-rails', '~> 0.2.4'
gem 'flamegraph'
gem 'fog'                     # s3 storage
gem 'globalize'   # I18n for tagging, etc.
gem 'globalize-accessors'
gem 'i18n-tasks', '~> 0.8.3'
gem 'hirb'
gem 'kaminari'
gem 'nokogiri'
gem 'newrelic_rpm'
gem 'select2-rails'                   # Token Fields
gem 'twilio-ruby'
gem 'rails_autolink'                  # Convert urls to links
gem 'redis', '3.2.1'
gem 'rest-client'
gem 'rmagick'
gem 'rubyzip', :require => 'zip'        # Zip files together
gem 'unf'
gem 'mailgun-ruby', '~>1.0.3', require: 'mailgun'

# Deployments
gem 'mina'

group :doc do
  gem 'sdoc'
end

group :test do
  gem 'rspec-rails', "= 2.14.2"
  gem 'shoulda-matchers', :require => false
  gem 'simplecov', '~> 0.9.2'
  gem 'database_cleaner'
  gem 'codeclimate-test-reporter', require: nil
end

group :development, :test do
  gem 'quiet_assets'
  gem 'factory_girl_rails', "~> 4.0"
  gem 'guard', '>=2.1.0'
  gem 'guard-rspec', '= 4.2.9'
  gem 'spring', '1.3.6'
  gem 'spring-commands-rspec'
  gem 'foreman'
end

New Gemfile

source 'https://rubygems.org'
ruby '2.3.0'

# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '4.2.5'
gem 'mysql2'
gem 'stackprof'
# Use unicorn as the app server
# gem 'unicorn'

gem 'sidekiq'
gem 'sinatra'
gem 'whenever'

gem 'thin'
gem 'faye'
gem 'faye-websocket'

#pdf generation
gem 'wicked_pdf'
gem 'wkhtmltopdf-binary'

# Assets
gem 'backbone-on-rails', '~> 0.9.9'
gem 'coffee-rails', '~> 4.0.1'
gem 'coffee-script-source', '=1.8.0'
gem 'ejs'                                   # uses templates for backbone
gem 'jquery-rails'
gem 'jquery-ui-rails'
gem 'neat'
gem 'sass-rails', '~> 4.0.3'
gem 'therubyracer'
gem 'turbolinks'
gem 'uglifier', '>= 1.3.0'

gem 'active_model_serializers', '0.8.3'
gem 'acts_as_list'
gem 'bcrypt', '~> 3.1.2'
gem 'bootstrap-sass', '~> 3.2.0.0'
gem 'bower-rails'
gem 'carrierwave'
gem 'enumerize'
gem 'embedly'                         # Url to Attachment Processing
gem 'fancybox2-rails', '~> 0.2.4'
gem 'flamegraph'
gem 'fog'                     # s3 storage
gem 'globalize'   # I18n for tagging, etc.
gem 'globalize-accessors'
gem 'i18n-tasks', '~> 0.8.3'
gem 'hirb'
gem 'kaminari'
gem 'nokogiri'
gem 'newrelic_rpm'
gem 'select2-rails'                   # Token Fields
gem 'twilio-ruby'
gem 'rails_autolink'                  # Convert urls to links
gem 'redis', '3.2.1'
gem 'rest-client'
gem 'rmagick'
gem 'rubyzip', :require => 'zip'        # Zip files together
gem 'unf'
gem 'mailgun-ruby', '~>1.0.3', require: 'mailgun'
gem 'rack-cors', :require => 'rack/cors'

# Deployments
gem 'mina'

group :doc do
  gem 'sdoc'
end

group :test do
  gem 'rspec-rails', "= 2.14.2"
  gem 'shoulda-matchers', :require => false
  gem 'simplecov', '~> 0.9.2'
  gem 'database_cleaner'
  gem 'codeclimate-test-reporter', require: nil
end

group :development, :test do
  gem 'quiet_assets'
  gem 'factory_girl_rails', "~> 4.0"
  gem 'guard', '>=2.1.0'
  gem 'guard-rspec', '= 4.2.9'
  gem 'spring', '1.6.1'
  gem 'spring-commands-rspec'
  gem 'foreman'
end