MySQL 5.7.9, Rails 3.2, mysql2 0.3.20

Question

I'm having issues after upgrading to MySQL 5.7.9 (installed via brew on OSX 10.11 El Capitan): all my legacy apps based on Rails 3.2.* stopped working.

Note that I will use a new application as an example, but I need old applications with old database working, not new ones.

$ rvm use 2.1.5
$ rails _3.2.22_ new r32-mysql2-test -d mysql
$ rails generate model Product name:string description:text
      invoke  active_record
/Users/user/.rvm/gems/ruby-2.1.5@global/gems/bundler-1.7.6/lib/bundler/rubygems_integration.rb:266:in `block in replace_gem': Please install the mysql2 adapter: `gem install activerecord-mysql2-adapter` (can't activate mysql2 (~> 0.3.10), already activated mysql2-0.4.1. Make sure all dependencies are added to Gemfile.) (LoadError)

mysql2 0.4.1 will not work, so let's add gem 'mysql2', '~> 0.3.20' to Gemfile

$ rails generate model Product name:string description:text
      invoke  active_record
      create    db/migrate/20151117104219_create_products.rb
      create    app/models/product.rb
      invoke    test_unit
      create      test/unit/product_test.rb
      create      test/fixtures/products.yml

$ rake db:create db:migrate
==  CreateProducts: migrating =================================================
-- create_table(:products)
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 `products` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `name` varchar(255), `description` text, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `block in execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activesupport-3.2.22/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/mysql2_adapter.rb:213:in `execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/schema_statements.rb:170:in `create_table'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:434:in `create_table'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:466:in `block in method_missing'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:438:in `block in say_with_time'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:438:in `say_with_time'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:458:in `method_missing'
/Users/user/dev/r32-mysql2-test/db/migrate/20151117104219_create_products.rb:3:in `change'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:407:in `block (2 levels) in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:407:in `block in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/connection_pool.rb:129:in `with_connection'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:389:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:528:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:720:in `block (2 levels) in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:777:in `call'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:777:in `ddl_transaction'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:719:in `block in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:700:in `each'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:700:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:570:in `up'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:551:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/railties/databases.rake:193:in `block (2 levels) in <top (required)>'
/Users/user/.rvm/gems/ruby-2.1.5/bin/ruby_executable_hooks:15:in `eval'
/Users/user/.rvm/gems/ruby-2.1.5/bin/ruby_executable_hooks:15:in `<main>'
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 `products` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `name` varchar(255), `description` text, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `block in execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activesupport-3.2.22/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/mysql2_adapter.rb:213:in `execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/schema_statements.rb:170:in `create_table'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:434:in `create_table'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:466:in `block in method_missing'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:438:in `block in say_with_time'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:438:in `say_with_time'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:458:in `method_missing'
/Users/user/dev/r32-mysql2-test/db/migrate/20151117104219_create_products.rb:3:in `change'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:407:in `block (2 levels) in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:407:in `block in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/connection_pool.rb:129:in `with_connection'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:389:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:528:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:720:in `block (2 levels) in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:777:in `call'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:777:in `ddl_transaction'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:719:in `block in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:700:in `each'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:700:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:570:in `up'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:551:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/railties/databases.rake:193:in `block (2 levels) in <top (required)>'
/Users/user/.rvm/gems/ruby-2.1.5/bin/ruby_executable_hooks:15:in `eval'
/Users/user/.rvm/gems/ruby-2.1.5/bin/ruby_executable_hooks:15:in `<main>'
Mysql2::Error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `block in execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activesupport-3.2.22/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/mysql2_adapter.rb:213:in `execute'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/schema_statements.rb:170:in `create_table'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:434:in `create_table'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:466:in `block in method_missing'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:438:in `block in say_with_time'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:438:in `say_with_time'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:458:in `method_missing'
/Users/user/dev/r32-mysql2-test/db/migrate/20151117104219_create_products.rb:3:in `change'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:407:in `block (2 levels) in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:407:in `block in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/connection_adapters/abstract/connection_pool.rb:129:in `with_connection'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:389:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:528:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:720:in `block (2 levels) in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:777:in `call'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:777:in `ddl_transaction'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:719:in `block in migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:700:in `each'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:700:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:570:in `up'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/migration.rb:551:in `migrate'
/Users/user/.rvm/gems/ruby-2.1.5/gems/activerecord-3.2.22/lib/active_record/railties/databases.rake:193:in `block (2 levels) in <top (required)>'
/Users/user/.rvm/gems/ruby-2.1.5/bin/ruby_executable_hooks:15:in `eval'
/Users/user/.rvm/gems/ruby-2.1.5/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Is there anything I can do to support legacy databases without switching back and forward to MySQL 5.6?

TIA

Answer 1

This was fixed for Rails two years ago. I'm not sure which versions of Rails have the fix. There are a few different ways to fix it in older versions of Rails here: https://github.com/rails/rails/pull/13247#issuecomment-158787912

Here is one of those solutions:

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

I guess in Rails 2 you should put that in environment.rb instead of an initializer.

For me, at least one rake task still fails with that fix, and I had to do the patch or require the patch right in the rake task itself. I read that rake tasks that do not depend on environment don't load initializers or environment.rb, so maybe that's why. Maybe this is just specific to this code base though...

Hopefully one of those solutions works, but if not then another possible temporary workaround is to monkey-patch ActiveRecord to fix that constant (in lib/active_record/connection_adapters/abstract_mysql_adapter.rb).

If you use the activerecord-mysql2-adapter gem then you may need to fix it too. (Look in activerecord-mysql2-adapter-{GEM VERSION}/lib/active_record/connection_adapters/mysql2_adapter.rb). Although, I'm having other problems with that gem, and it may not even be needed.