`trilogy` can't connect to remote Rails db, but `mysql2` can with identical `database.yml`

[nimmolo]

I'm trying to switch our production Rails 7.1.3 server from mysql2 to trilogy to connect to two dbs:

• our remote mysql db at DB_IP_ADDR on a separate DO droplet
• our local solid_cache mysql db on our webserver DO droplet at PRODUCTION_IP_ADDR

I'm aware of the issue with trilogy not being able to use mysql 8's caching_sha2_password. So I've already altered the db user in mysql to use mysql_native_password, on both the remote mysql server and the mysql cache server:

# on DB_IP_ADDR

mysql> ALTER USER 'us'@'PRODUCTION_IP_ADDR' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> FLUSH PRIVILEGES;

# on PRODUCTION_IP_ADDR

mysql> ALTER USER 'us'@'localhost' IDENTIFIED WITH mysql_native_password BY 'cache_password';
mysql> FLUSH PRIVILEGES;

If I use this in our config/database.yml:

production:
  primary:
    adapter:  trilogy
    database: remote_production_db
    host:     DB_IP_ADDR
    username: us
    password: password
    socket:   /var/run/mysqld/mysqld.sock
    encoding: utf8
  cache:
    adapter:  trilogy
    database: our_solid_cache_db
    host:     127.0.0.1
    username: us
    password: cache_password
    socket:   /var/run/mysqld/mysqld.sock
    endoding: utf8
    migrations_paths: "db/cache/migrate"

the local cache db connects fine with trilogy, but the remote db connection fails with

ActiveRecord::DatabaseConnectionError: There is an issue connecting to your database with your username/password, username: us. (ActiveRecord::DatabaseConnectionError)

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

Caused by:
Trilogy::BaseConnectionError: 1045: Access denied for user 'us'@'localhost' (using password: YES) (Trilogy::BaseConnectionError)

It's not the password: If i switch the database.yml back to adapter: mysql2 and everything else is the same, both dbs connect OK.

One thing I notice is the error message identifies the attempted user connection as 'us'@'localhost' — but the request is not coming from localhost, it's coming from PRODUCTION_IP_ADDR.

Is the socket messing it up? Is there some other thing I need in database.yml so Trilogy to connect to the remote db, or so Rails can be identified a remote user?

[nimmolo]

Whoops. I figured it out. The remote config does not need the socket: it needs only the host IP.