TL;DR: When I try to use flask db migrate with a remote database, I get: MySQLdb._exceptions.OperationalError: (1045, "Access denied for user ''@'localhost' (using password: YES)"). But I'm certain that the username and password are correct, the remote user has the SELECT privilege, and I don't actually have any models declared yet.
I have an SSH tunnel set up for access to a remote database.
$ ssh -L <local_port>:localhost:<remote_internal_port> <mysql_user>@<server> -p <remote_external_port>
I have credentials for a read-only user on the remote MySQL DB (really MariaDB). This is not the database the users are stored in, so I'm using SQLALCHEMY_BINDS to add the second database.
My environment looks like this:
DATABASE_URL=sqlite:////tmp/dev.db
DATABASE_BINDS="remote=mysql+mysqldb://<mysql_user>:<pass>@localhost:<local_port>/<db_name>"
Then in the config file:
from environs import Env
env = Env()
env.read_env()
SQLALCHEMY_DATABASE_URI = env.str("DATABASE_URL") # local
SQLALCHEMY_BINDS = env.dict("DATABASE_BINDS") # remote, etc
I don't presently have any models declared for the remote database at all. I'm planning to use reflection to load them.
The initial run of flask db init --multidb works fine. It creates the migration folder. But when I run flask db migrate -m "Initial DB", I get this:
Traceback (most recent call last):
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2275, in _wrap_pool_connect
return fn()
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 760, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/pool/impl.py", line 238, in _do_get
return self._create_connection()
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 639, in __connect
connection = pool._invoke_creator(self)
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/path/to/project/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 453, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/path/to/project/venv/lib/python3.7/site-packages/MySQLdb/__init__.py", line 84, in Connect
return Connection(*args, **kwargs)
File "/path/to/project/venv/lib/python3.7/site-packages/MySQLdb/connections.py", line 166, in __init__
super(Connection, self).__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (1045, "Access denied for user '<mysql_user>'@'localhost' (using password: YES)")
I've checked and double-checked that the username and password I've declared in the environment are correct. I can successfully use a GUI client with these settings to browse the remote MySQL database via the SSH tunnel. I can use the MySQL CLI client on the remote server to USE and SELECT on the appropriate database.
I'm at a loss for where to look here. I think the SSH tunnel is a red herring, since it's working well with the GUI client, but I don't know how to get more logging to see what the problem really is. This answer implies that the issue might in fact be one of permissions, not with the l/p themselves. Does Flask-Migrate require something other than the SELECT privilege?
