Cope with failure to run MySQL on Rails application14 Feb 2019
When I upgraded MySQL version to 8.0.14 from 5.7, I faced with failure to operate for the database.
mysql_upgrade -u root -p
% echo $OSTYPE darwin18.2.0 % mysql --version mysql Ver 8.0.14 for osx10.14 on x86_64 (Homebrew) % bundle exec rails --version Rails 5.2.2
What was happened?
After uprading MySQL 5.7 to 8.0 with homebrew, run
rake db:migrate for my Rails application, then
LoadError occurred as follows:
% bundle exec rake db:migrate rake aborted! LoadError: dlopen(/Users/akito/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/mysql2-0.5.2/lib/mysql2/mysql2.bundle, 9): Library not loaded: /usr/local/opt/mysql/lib/libmysqlclient.20.dylib Referenced from: /Users/akito/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/mysql2-0.5.2/lib/mysql2/mysql2.bundle Reason: image not found - /Users/akito/.rbenv/versions/2.5.3/lib/ruby/gems/2.5.0/gems/mysql2-0.5.2/lib/mysql2/mysql2.bundle ...
It seemed not to find
Then run command as follow:
% bundle doctor ... The following gems are missing OS dependencies: * mysql2: /usr/local/opt/mysql/lib/libmysqlclient.20.dylib
Fix the problem
libmysqlclient is a client library for MySQL. This library communicates over a network connection.
libmysqlclient20 has been bundled with MySQL 5.7. You can confirm it when MySQL mirror page, for example, MySQL 5.7 mirror page
However, in MySQL 8.0,
libmysql21 has been bundled: MySQL 8.0 mirror page
Both pages above have been hosted in Japan.
By the way, although
libmysqlclient.so is for most UNIX sytems,
.dylib is for macOS. As for the static link library of this is
.a as the extension.
Anyway, I checked
/usr/local/opt/mysql/lib, then I got:
% ll /usr/local/opt/mysql/lib total 37616 drwxr-xr-x 14 akito staff 448 Dec 21 05:31 ./ drwxr-xr-x 17 akito staff 544 Feb 9 13:56 ../ -rw-r--r-- 1 akito staff 5419384 Feb 9 13:56 libmysqlclient.21.dylib -r--r--r-- 1 akito staff 6052712 Dec 21 05:31 libmysqlclient.a lrwxr-xr-x 1 akito staff 23 Dec 21 05:31 libmysqlclient.dylib@ -> libmysqlclient.21.dylib -rw-r--r-- 1 akito staff 565184 Feb 9 13:56 libmysqlharness.1.dylib -r--r--r-- 1 akito staff 1060048 Dec 21 05:31 libmysqlharness.a lrwxr-xr-x 1 akito staff 23 Dec 21 05:31 libmysqlharness.dylib@ -> libmysqlharness.1.dylib -r--r--r-- 1 akito staff 6140608 Feb 9 13:56 libmysqlrouter.1.dylib lrwxr-xr-x 1 akito staff 22 Dec 21 05:31 libmysqlrouter.dylib@ -> libmysqlrouter.1.dylib -r--r--r-- 1 akito staff 9760 Dec 21 05:31 libmysqlservices.a drwxr-xr-x 7 akito staff 224 Dec 21 05:31 mysqlrouter/ drwxr-xr-x 3 akito staff 96 Feb 9 13:56 pkgconfig/ drwxr-xr-x 98 akito staff 3136 Dec 21 05:31 plugin/
It is true that the directory has
libmysqlclient21 but does not have
Hence, I tried to re-install
% gem uninstall mysql2 % bundle // I use it on project root of Rails app.
% bundle doctor ... No issues found with the installed bundle
Error message was gone! Yet, another problem came up.
% bundle exec rake db:migrate rake aborted! ActiveRecord::StatementInvalid: Mysql2::Error: The user specified as a definer ('mysql.infoschema'@'localhost') does not exist: SELECT table_name FROM information_schema.tables WHERE table_schema = database() AND table_name = 'schema_migrations' AND table_type = 'BASE TABLE' /Users/akito/.rbenv/versions/2.5.3/bin/bundle:23:in `load' /Users/akito/.rbenv/versions/2.5.3/bin/bundle:23:in `<main>' Caused by: Mysql2::Error: The user specified as a definer ('mysql.infoschema'@'localhost') does not exist /Users/akito/.rbenv/versions/2.5.3/bin/bundle:23:in `load' /Users/akito/.rbenv/versions/2.5.3/bin/bundle:23:in `<main>' Tasks: TOP => db:migrate (See full trace by running task with --trace)
'mysql.infoschema'@'localhost' is a new user that has added since MySQL 8.0.4.
The release notes of MySQL 8.0.4 said:
If the MySQL root user account was renamed, a query that accessed an INFORMATION_SCHEMA view returned an error stating that the user specified as the definer does not exist. To avoid this error, a new system user account, ‘mysql.infoschema’@’localhost’, is now the DEFINER for INFORMATION_SCHEMA views. (Bug #25185947, Bug #84027)
I upgraded MySQL simply, so user information might be taken over from older MySQL.
Therefore, I ran
% mysql_upgrade -u root -p Enter password: Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK mysql.default_roles OK mysql.engine_cost OK mysql.func OK mysql.general_log OK mysql.global_grants OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.password_history OK mysql.plugin OK mysql.procs_priv OK mysql.proxies_priv OK mysql.role_edges OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Found outdated sys schema version 1.5.1. Upgrading the sys schema. Checking databases. ... suspension points ... sys.sys_config OK Upgrade process completed successfully. Checking if update is needed.
Finally, I could run
- MySQL :: MySQL 8.0 Reference Manual :: 28.7.1 MySQL C API Implementations
- MySQL :: Download Connector/C (libmysqlclient)
- GitHub - brianmario/mysql2: A modern, simple and very fast Mysql library for Ruby - binding to libmysql