MySQLからJawsDBへの接続が行えるようにしたい

[CarbazochromeT]

Herokuからアプリ自体のデプロイは成功したのですが、productionの方のデータベースへのマイグレーションを行うことができず、行き詰まってしまっている状況です。 どなたがご助言をお願いいたします。

試したこと

HerokuからMySQLへ接続ができなかったため一度Homebrew経由でインストールしたMySQLをこれらのコマンドを使用してアンインストール、MySQLをインストールいたしました。

brew doctor
mysqldump -u root -p -x --all-databases > dump.sql\n
mysql.server stop
brew uninstall mysql \n
sudo rm -rf /usr/local/var/mysql/
sudo rm -rf /usr/local/var/mysql
 sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*
sudo rm /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo rm -rf /usr/local/Cellar/mysql*
sudo rm -rf /usr/local/bin/mysql*
sudo rm -rf /usr/local/var/mysql*
sudo rm -rf /usr/local/var/mysql*
sudo rm -rf /usr/local/share/mysql*
sudo rm -rf /usr/local/opt/mysql*
sudo rm -rf /etc/my.cnf
 2488  sudo rm -rf /opt/homebrew/var/mysql
 2489  sudo rm -rf /opt/homebrew/etc/my.conf
 2490  sudo rm -rf /opt/homebrew/etc/my.conf.default

$ mysql          
zsh: command not found: mysql

$ which mysql    
mysql not found

$ brew list                                    
==> Formulae
abseil                  libevent                node-build
anyenv                  libfido2                nodenv
aom                     libgcrypt               openexr
autoconf                libgpg-error            openjpeg
brotli                  libheif                 openldap
c-ares                  libiconv                openssl@1.1
ca-certificates         libidn                  openssl@3
cairo                   libidn2                 pango
curl                    liblqr                  pcre
fontconfig              libnghttp2              pcre2
freetype                libomp                  peco
fribidi                 libpng                  pixman
gd                      libraw                  pkg-config
gdk-pixbuf              librsvg                 postgresql@14
gettext                 libssh2                 protobuf
ghostscript             libtiff                 protobuf@21
giflib                  libtool                 rbenv
glib                    libunistring            readline
graphite2               libuv                   rtmpdump
graphviz                libvmaf                 ruby-build
gts                     libx11                  shared-mime-info
harfbuzz                libxau                  sqlite
heroku                  libxcb                  tree
highway                 libxdmcp                webp
icu4c                   libxext                 wget
imagemagick             libxml2                 x265
imagemagick@6           libxrender              xorgproto
imath                   libxslt                 xz
jasper                  libyaml                 yarn
jbig2dec                little-cms2             zlib
jpeg-turbo              lz4                     zsh
jpeg-xl                 lzo                     zsh-autosuggestions
jsoncpp                 m4                      zsh-completions
krb5                    mysql-client            zsh-syntax-highlighting
libavif                 ncurses                 zstd
libcbor                 netpbm
libde265                node

上記のコマンドでMySQLがいないことを確認し、再度インストールを行い、ローカルへ接続いたしました。

$ brew install mysql@8.1
Running `brew update --auto-update`...
==> Auto-updated Homebrew!
Updated 2 taps (homebrew/core and homebrew/cask).
==> New Formulae
gossip                    libdicom                  opentofu
karmadactl                mentat                    wormhole-william
==> New Casks
songkong                               spacedrive

You have 17 outdated formulae installed.

==> Downloading https://ghcr.io/v2/homebrew/core/mysql/manifests/8.1.0
Already downloaded: /Users/flare_home/Library/Caches/Homebrew/downloads/92c3b013bf98bd060e2779550b33750f6fb219f18d529b393b551ff8245504c4--mysql-8.1.0.bottle_manifest.json
==> Fetching mysql
==> Downloading https://ghcr.io/v2/homebrew/core/mysql/blobs/sha256:2635ebe10
Already downloaded: /Users/flare_home/Library/Caches/Homebrew/downloads/f852745c89c56ecde7a55c7b499ebb81910ab9eec82d53e0227d6a85952c6c4e--mysql--8.1.0.arm64_ventura.bottle.tar.gz
==> Pouring mysql--8.1.0.arm64_ventura.bottle.tar.gz
==> /opt/homebrew/Cellar/mysql/8.1.0/bin/mysqld --initialize-insecure --user=
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -u root

To restart mysql after an upgrade:
  brew services restart mysql
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql
==> Summary
🍺  /opt/homebrew/Cellar/mysql/8.1.0: 325 files, 308.4MB
==> Running `brew cleanup mysql`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).

$ mysql.server start
Starting MySQL
 SUCCESS! 

$ 2023-10-12T21:40:00.6NZ mysqld_safe A mysqld process already exists
mysql -u root     
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> nc -z -w1 -v 127.0.0.1 3306
    -> exit
exit
^C
mysql> exit
Bye

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [6:40:49] C:130
$ nc -z -w1 -v 127.0.0.1 3306
Connection to 127.0.0.1 port 3306 [tcp/mysql] succeeded!

$ mysql -u root -h 127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

$ mysqld --help --verbose | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf 
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default

https://qiita.com/akiko-pusu/items/aef52b723da2cb5dc596

ローカルからのMySQLへの接続は完了したのですが、heroku経由でマイグレーションを行おうとするとやはりMysql2::Error: Table 'qw7n2t3o8f78mknx.your_table_name' doesn't existというエラーが発生してしまいます。 どなたかご助言をお願いいたします。

$ heroku run rake db:migrate
Running rake db:migrate on ⬢ care-drug-searcher... up, run.4095 (Basic)
============= WARNING FROM mysql2 =============
The options :user, :pass, :hostname, :dbname, :db, and :sock are deprecated and will be removed at some point in the future.
Instead, please use :username, :password, :host, :port, :database, :socket, :flags for the options.
============= END WARNING FROM mysql2 =========
============= WARNING FROM mysql2 =============
The options :user, :pass, :hostname, :dbname, :db, and :sock are deprecated and will be removed at some point in the future.
Instead, please use :username, :password, :host, :port, :database, :socket, :flags for the options.
============= END WARNING FROM mysql2 =========
I, [2023-10-12T21:57:54.922054 #2]  INFO -- : Migrating to ChangePrimaryKey (20230909001430)
== 20230909001430 ChangePrimaryKey: migrating =================================
-- execute("ALTER TABLE your_table_name DROP PRIMARY KEY, ADD PRIMARY KEY (new_primary_key_column);")
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'qw7n2t3o8f78mknx.your_table_name' doesn't exist
/app/vendor/bundle/ruby/3.1.0/gems/mysql2-0.5.5/lib/mysql2/client.rb:151:in `_query'

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: 5

development:
  adapter: mysql2
  encoding: utf8
  pool: 5
  port: 3306
  database: drug_app5_development
  username: root
  password:　＜非公開＞
  host: localhost
  socket: /tmp/mysql.sock

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: drug_app5_test

# As with config/credentials.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password or a full connection URL as an environment
# variable when you boot the app. For example:
#
#   DATABASE_URL="mysql2://myuser:mypass@localhost/somedatabase"
#
# If the connection URL is provided in the special DATABASE_URL environment
# variable, Rails will automatically merge its configuration values on top of
# the values provided in this file. Alternatively, you can specify a connection
# URL environment variable explicitly:
#
#   production:
#     url: <%= ENV["MY_APP_DATABASE_URL"] %>
#
# Read https://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full overview on how database connection configuration can be specified.
#

production:
  <<: *default
  hostname: <%= ENV['DB_HOSTNAME'] %>
  username: <%= ENV['DB_USERNAME'] %>
  database: <%= ENV['DB_NAME'] %>
  password: <%= ENV['DB_PASSWORD'] %>
  url: <%= ENV['DATABASE_URL'] %>

app.json
    "addons": [
    {
        "plan": "jawsdb:kitefin",
        "as": "JAWSDB",
        "options": {
          "version": "8.1"
        }
    }
]

heroku configのデータはどなたか閲覧次第消します。

$ heroku config                         
=== care-drug-searcher Config Vars

DATABASE_URL:             mysql2://ttbj0x1rhci4syen:qsitgephovj3wz6g@j8oay8teq9xaycnm.cbetxkdyhwsb.us-east-1.rds.amazonaws.com:3306/qw7n2t3o8f78mknx
DB_HOSTNAME:              j8oay8teq9xaycnm.cbetxkdyhwsb.us-east-1.rds.amazonaws.com
DB_NAME:                  qw7n2t3o8f78mknx
DB_PASSWORD:              qsitgephovj3wz6g
DB_USERNAME:              ttbj0x1rhci4syen
JAWSDB_ROSE_URL:          mysql://o28wpnz5qly9ms0s:xrlt01kbz0qrufzf@d6rii63wp64rsfb5.cbetxkdyhwsb.us-east-1.rds.amazonaws.com:3306/u8f1rcvbezfahq4z
JAWSDB_URL:               mysql://ttbj0x1rhci4syen:qsitgephovj3wz6g@j8oay8teq9xaycnm.cbetxkdyhwsb.us-east-1.rds.amazonaws.com:3306/qw7n2t3o8f78mknx
LANG:                     en_US.UTF-8
RACK_ENV:                 production
RAILS_ENV:                production
RAILS_LOG_TO_STDOUT:      enabled
RAILS_SERVE_STATIC_FILES: enabled
SECRET_KEY_BASE:          584d14cb745bb14cb4a181e278a990325bbc5c1402413370f68298b7b9ae4e0c318cfac2b724b6d6cbfe9804b61144420b99ae9d17dda2d45dac0d17dc75ce1d

$ heroku info  
=== care-drug-searcher
Addons:         jawsdb:kitefin
                jawsdb:kitefin
Auto Cert Mgmt: false
Dynos:          web: 1
Git URL:        https://git.heroku.com/care-drug-searcher.git
Owner:          flare.marionette@gmail.com
Region:         us
Repo Size:      103 MB
Slug Size:      127 MB
Stack:          heroku-22
Web URL:        https://care-drug-searcher-6709cdce3a56.herokuapp.com/

なお、DATABASE URLに関しましてはmysql2を指定しています。 https://qiita.com/shizimi_50/items/3f0ca56649dc8f968267

heroku config:set DATABASE_URL=mysql2://ttbj0x1rhci4syen:qsitgephovj3wz6g@j8oay8teq9xaycnm.cbetxkdyhwsb.us-east-1.rds.amazonaws.com:3306/qw7n2t3o8f78mknx?reconnect=true

上記のコマンドを利用しています。

[kenchasonakai]

開発環境でrails db:createやrails db:migrateなどのコマンドは正常に動作していますか？

[CarbazochromeT]

開発環境でのマイグレーションは行えておりません。 developmentの方のデータベースが消えてしまったみたいです。

$ rails db:create     
There is an issue connecting to your database with your username/password, username: root.

Please check your database configuration to ensure the username/password are valid.
Couldn't create 'drug_app5_development' database. Please check your configuration.
rails aborted!
ActiveRecord::DatabaseConnectionError: There is an issue connecting to your database with your username/password, username: root.

Please check your database configuration to ensure the username/password are valid.

Caused by:
Mysql2::Error::ConnectionError: Access denied for user 'root'@'localhost' (using password: YES)

Tasks: TOP => db:create
(See full trace by running task with --trace)

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [12:40:43] C:1
$ rails db:migrate
rails aborted!
ActiveRecord::DatabaseConnectionError: There is an issue connecting to your database with your username/password, username: root.

Please check your database configuration to ensure the username/password are valid.

Caused by:
Mysql2::Error::ConnectionError: Access denied for user 'root'@'localhost' (using password: YES)

Tasks: TOP => db:migrate
(See full trace by running task with --trace)

ホスト名を指定するとMySQLにログインできるようです。

$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [12:45:23] C:1
$ mysql -u root -h 127.0.0.1           
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[Tsuchiya2]

そもそもですが、ローカルのmysqlのrootに対してパスワードを設定していますか。設定していたら、その設定とdatabase.yml内のpasswordが一致しているか確認とかしてみてください。

---

余談ですが、Herokuはmysql以外でもpostgresqlでもデプロイできるので、そちらを検討するのもありかと思います。（インフラ入門のアプリケーションのデプロイ辺りが参考になるかと思います）

[CarbazochromeT]

すみません、MySQLWorkbenchの方でパスワードを設定し直しましたらrootの方にログインできました。

$ mysql -u root -p          
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.1.0 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
exit
^C
mysql> exit
Bye

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [13:59:33] 
$ rails db:migrate                     
rails aborted!
ActiveRecord::NoDatabaseError: We could not find your database: drug_app5_development. Which can be found in the database configuration file located at config/database.yml.

To resolve this issue:

- Did you create the database for this app, or delete it? You may need to create your database.
- Has the database name changed? Check your database.yml config has the correct database name.

To create your database, run:

        bin/rails db:create

Caused by:
Mysql2::Error: Unknown database 'drug_app5_development'

Tasks: TOP => db:migrate
(See full trace by running task with --trace)

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [13:59:58] C:127
$ rails db:create 
Created database 'drug_app5_development'
There is an issue connecting to your database with your username/password, username: .

Please check your database configuration to ensure the username/password are valid.
Couldn't create 'drug_app5_test' database. Please check your configuration.
rails aborted!
ActiveRecord::DatabaseConnectionError: There is an issue connecting to your database with your username/password, username: .

Please check your database configuration to ensure the username/password are valid.

Caused by:
Mysql2::Error::ConnectionError: Access denied for user 'root'@'localhost' (using password: NO)

Tasks: TOP => db:create
(See full trace by running task with --trace)

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [14:00:07] C:1
$ rails db:create
Database 'drug_app5_development' already exists
There is an issue connecting to your database with your username/password, username: .

Please check your database configuration to ensure the username/password are valid.
Couldn't create 'drug_app5_test' database. Please check your configuration.
rails aborted!
ActiveRecord::DatabaseConnectionError: There is an issue connecting to your database with your username/password, username: .

Please check your database configuration to ensure the username/password are valid.

Caused by:
Mysql2::Error::ConnectionError: Access denied for user 'root'@'localhost' (using password: NO)

Tasks: TOP => db:create
(See full trace by running task with --trace)

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [14:02:01] C:1
$ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

Rootでの設定を変えた後、再起動したらサーバーが立ち上がらなくなってしまいました。

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [14:06:57] C:1
$  sudo mysql.server restart
Password:
Shutting down MySQL
... ERROR! The server quit without updating PID file (/opt/homebrew/var/mysql/flarenoMacbook-Air.local.pid).
 ERROR! Failed to stop running server, so refusing to try to start.

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [14:07:04] C:1
$  sudo vi /etc/my.cnf

# anago @ flarenoMacbook-Air in ~/workspace/runteq/応用編/drug_app5 on git:deploy x [14:07:40] 
$ sudo mysql.server restart            
Shutting down MySQL
. SUCCESS! 
Starting MySQL
. ERROR! The server quit without updating PID file (/opt/homebrew/var/mysql/flarenoMacbook-Air.local.pid).

データベースに関してはもう少しMySQLでなんとか頑張ってみたいと思います。

現在MySQLのエラーログを確認したらこのような内容になっておりました。

2023-10-13T05:28:19.981653Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2023-10-13T05:28:20.133292Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-fi
le-priv: Current value does not restrict location of generated files. Consider setting it to a vali
d, non-empty path.
2023-10-13T05:28:20.133340Z 0 [System] [MY-010116] [Server] /opt/homebrew/opt/mysql/bin/mysqld (mys
qld 8.1.0) starting as process 97179
2023-10-13T05:28:20.135332Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 becau
se file system for /opt/homebrew/var/mysql/ is case insensitive
2023-10-13T05:28:20.142136Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-10-13T05:28:20.214538Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-10-13T05:28:20.306832Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-10-13T05:28:20.306858Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to suppor
t TLS. Encrypted connections are now supported for this channel.
2023-10-13T05:28:20.308062Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file:
 Location '/opt/homebrew/var/mysql' in the path is accessible to all OS users. Consider choosing a 
different directory.
2023-10-13T05:28:20.309806Z 0 [ERROR] [MY-000067] [Server] unknown variable 'validate-password=OFF'
.
2023-10-13T05:28:20.309924Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-13T05:28:21.391003Z 0 [System] [MY-010910] [Server] /opt/homebrew/opt/mysql/bin/mysqld: Shu
:

下記のエラーメッセージが特に気になっています。

[Server] Insecure configuration for --secure-fi
le-priv: Current value does not restrict location of generated files. Consider setting it to a vali
d, non-empty path.
[Server] Setting lower_case_table_names=2 becau
se file system for /opt/homebrew/var/mysql/ is case insensitive
[Server] Insecure configuration for --pid-file:
 Location '/opt/homebrew/var/mysql' in the path is accessible to all OS users. Consider choosing a 
different directory.