Flask-Migrate unable to connect to MySQL in a Docker container but the Flask app works fine

[blackdwarf]

So, I'm kind of hitting my head against the wall on this, would appreciate if anyone could help me understand what I'm doing wrong.

My code exists on my machine, in a folder. It is running Flask, uses Flask-SQLAlchemy, and Flask-Migrate. The MySQL Server is running in a Docker container. The container itself is stock from the Docker repo. The port 3306 is forwarded and visible from the host (where the code runs). I can connect using DBeaver.

The connection string is:

SQLALCHEMY_DATABASE_URI = 'mysql://root:<passwd>@127.0.0.1/db_name'

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

Now, the funny part is that when I run the server using the standards flask run --debugger, everything...works! There is nothing thrown, the app works, the data shows.

My understanding is that Flask-Migrate uses Alembic below the covers and Alembic should be using SQLAlchemy, right, so this should "Just Work".

Reading the docs and the issues here, I wasn't able to find this particular setup, so posting in case someone has this setup and knows what I'm doing wrong.

[miguelgrinberg]

Are you using 127.0.0.1 inside the docker container? That IP address does not map to your host computer inside the container, it maps to the container itself. In general you can use host.docker.internal as hostname to reference the host from inside a container.

[blackdwarf]

Hey @miguelgrinberg , thanks for replying. The situation is actually opposite, the Flask app is on the host, while MySQL is in container. That's why I'm using 127.0.0.1 from the app (and other clients).

The funny thing is that I tried to repro this with just using SQLAlchemy and Alembic directly, without Flask, Flask-Migrate, or Flask-SQLAlchemy, using a minimal model and creating a revision with --autogenerate. That worked. The Flask development server works, as in, the app shows the data from the containerized DB. I even started up IPython and used mysqlclient directly to connect to the containerized DB and it worked like a charm.

Confusion on my part is absolute at this point. 🤣

[miguelgrinberg]

My guess is that this is caused by how you have defined your users in the MySQL DB. Note how your error message shows the user as root@172.17.0.1, which maps to the IP address of the host from inside the container. Your user access should allow connections from this IP address in addition to localhost.

[blackdwarf]

Well, I feel stupid now...The solution was right in front of me, but I didn't actually read the source all the way. This commit or, rather, what it fixed, was bugging me. I generated my migrations folder way back, some years back, and the env.py file was old, which means it didn't have this new code that properly get the DB URI.

And, no, I didn't compare the code. I figured this out because I sprinkled a lot of debug statements all over the place and finally found this SO comment that made me change env.py and it worked. Then I went to write this response and saw the commit. 😢

Is there a way to regenerate the env.py file? I guess I could just move the migrations out, nuke the folder, re-run flask db init and then move the migrations back in, but is there a better way?

[miguelgrinberg]

Alembic does not provide a way to refresh the env.py or alembic.ini files. I have debated whether to implement something myself on this, but ended up not doing it. The simplest solution is to rename migrations to migrations-old, create a new repository, and then move all your scripts in migrations-old/versions to the new migrations/versions.

[blackdwarf]

I was going to ask if I can make a PR, but you're right, there is nothing much I could do except orchestrate the same approach you mentioned. However, you ok with me making a PR to the README with this information (e.g. "if you have an old version")?

[miguelgrinberg]

This should go in the documentation, not in the readme.

[blackdwarf]

Fair enough, you ok with me making a PR for that?

[blackdwarf]

Just to make sure people can find this, I've solved this by just taking env.py contents from the release I am running and copy-pasted it into the env.py file in my project.

Another way would be what Miguel suggests which is to move the migrations out somewhere, regenerate the migrations scaffold and then move the migration files back in. This will also generate an up-to-date env.py.