Flask-Migrate Access denied for user 'root'@'10.x.x.x' (using password: YES)

Question

I'm working on Flask-Migrate with mysql.

And I received error message like this:

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1045, "Access denied for user 'root'@'10.x.x.x' (using password: YES)")

mysql is live with docker, I try to connect with following commands and it works fine:

mysql -u root -h {mysql_host} -P {mysql_port} -p

Plus it works very fine with sqlalchemy like this:

class DatabaseClient():
    def __init__(self):
        self.__settings__ = settings
    

        alchemy_string = 'mysql+mysqldb://root:{password}@{mysql_host}:{mysql_port}/main?charset=utf8mb4'
        self.__engine__ = sqlalchemy.create_engine(alchemy_string, echo=False)
        self.__conn__ = self.__engine__.connect()

        self.session = self.__conn__
    
    def get(self, query: str) -> pandas.DataFrame:
        result = self.__conn__.execute(sqlalchemy.text(query))
        
        return pandas.DataFrame(result.fetchall())


    def execute(self, query: str):
        return self.__conn__.execute(sqlalchemy.text(query))

    def commit(self):
        self.__conn__.commit()


client = DatabaseClient()
query_str = f'''
    select * from contents
'''
df_result = client.get(query_str)
print(df_result)

del client

But, when I tried to use Flask-Migrate for ORM features, it respond with the error message. Here's the code:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

import sys
sys.path.append('../../')

from apps._common.database_client import DatabaseSettings, DatabaseType
from database.pio_d1.model import *

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqldb://root:{password}@{mysql_host):{mysql_port}/main?charset=utf8mb4'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['DEBUG'] = True

print(app.config)

db = SQLAlchemy(app)
migrate = Migrate(app, db)

flask db init
flask db migrate

and here's error codes:

  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3293, in raw_connection
    return self.pool.connect()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1268, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 283, in _do_get
    return self._create_connection()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    with util.safe_reraise():
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/home/dev/database/venv/lib/python3.10/site-packages/MySQLdb/__init__.py", line 123, in Connect
    return Connection(*args, **kwargs)
  File "/home/dev/database/venv/lib/python3.10/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
MySQLdb.OperationalError: (1045, "Access denied for user 'root'@'10.x.x.x' (using password: YES)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/dev/database/venv/bin/flask", line 8, in <module>
    sys.exit(main())
  File "/home/dev/database/venv/lib/python3.10/site-packages/flask/cli.py", line 1063, in main
    cli.main()
  File "/home/dev/database/venv/lib/python3.10/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/home/dev/database/venv/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/dev/database/venv/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/dev/database/venv/lib/python3.10/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/dev/database/venv/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/dev/database/venv/lib/python3.10/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/dev/database/venv/lib/python3.10/site-packages/flask/cli.py", line 357, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/home/dev/database/venv/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/dev/database/venv/lib/python3.10/site-packages/flask_migrate/cli.py", line 91, in migrate
    _migrate(directory, message, sql, head, splice, branch_label, version_path,
  File "/home/dev/database/venv/lib/python3.10/site-packages/flask_migrate/__init__.py", line 96, in wrapped
    f(*args, **kwargs)
  File "/home/dev/database/venv/lib/python3.10/site-packages/flask_migrate/__init__.py", line 210, in migrate
    command.revision(config, message, autogenerate=True, sql=sql,
  File "/home/dev/database/venv/lib/python3.10/site-packages/alembic/command.py", line 236, in revision
    script_directory.run_env()
  File "/home/dev/database/venv/lib/python3.10/site-packages/alembic/script/base.py", line 582, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/dev/database/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/dev/database/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/dev/database/database/pio_d1/migrations/env.py", line 96, in <module>
    run_migrations_online()
  File "/home/dev/database/database/pio_d1/migrations/env.py", line 81, in run_migrations_online
    with connectable.connect() as connection:
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3269, in connect
    return self._connection_cls(self)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2431, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3293, in raw_connection
    return self.pool.connect()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1268, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 283, in _do_get
    return self._create_connection()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    with util.safe_reraise():
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/dev/database/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/home/dev/database/venv/lib/python3.10/site-packages/MySQLdb/__init__.py", line 123, in Connect
    return Connection(*args, **kwargs)
  File "/home/dev/database/venv/lib/python3.10/site-packages/MySQLdb/connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1045, "Access denied for user 'root'@'10.x.x.x' (using password: YES)")
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Any insights or suggestions are very welcomed!

Many thanks

JK

Answer 1

Look at the 10.x.x.x address that the error complains about. Then make sure the root user is configured in your MySQL server with access from that URL. Chances are you only have the user set up to access from localhost.