MySQL 5.7.9, Rails 3.2, mysql2 0.3.20

Question

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

Please note that I will use the new application as an example, but I need old applications with an old workstation, 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 add the gem 'mysql2', '~> 0.3.20' to the 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 migrating to MySQL 5.6?

TIA

Answer 1

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

Here is one such solution:

With Rails 2.3.5, MySQL version 5.7.9 and mysql gem you need to have this bit as an initializer in the config / Initializers / abstract_mysql_adapter.rb:

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

For mysql2, this 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 think in Rails 2 you should put this in environment.rb instead of an initializer.

For me, at least one rake task still does not work with this fix, and I had to make a patch or request a fix right in the rake command itself. I read that rake tasks that are environment independent do not load initializers or environment.rb, which is why this is probably why. Perhaps this is just typical of this code base, though ...

We hope that one of these solutions works, but if not, then another possible temporary workaround is to use ActiveRecord for the monkey patch to fix this constant (in lib/active_record/connection_adapters/abstract_mysql_adapter.rb ).

If you are using the activerecord-mysql2-adapter hard drive, you may also need to fix it. (See activerecord-mysql2-adapter-{GEM VERSION}/lib/active_record/connection_adapters/mysql2_adapter.rb ). Although, I have other problems with this stone, and it may not even be necessary.