search

miguelgrinberg / microblog

The microblogging application developed in my Flask Mega-Tutorial series. This version maps to the 2024 Edition of the tutorial.
http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-i-hello-world
MIT License
4.56k stars 1.65k forks source link

DB Packet sequence number wrong - DB Migration #212

Closed superteece closed 4 years ago

superteece commented 4 years ago

My DB works fine after Chapter 17 so long as mysql is on localhost. I'm trying to use a separate VM as the SQL server by changing the address in the .env file but any attempt to flask db migrate or flask db upgrade results in sqlalchemy.exc.InternalError: (pymysql.err.InternalError) Packet sequence number wrong - got 1 expected 0

Research is pointing to an issue with pymysql connection pooling but finding a solution is proving difficult.

Any pointers?

miguelgrinberg commented 4 years ago

Are you using your own code, or is this with this tutorial's code?

superteece commented 4 years ago

It’s the tutorial code, changing the .env DATABASE_URL entry to the FQDN or IP address of a MySQL db served on another VM. A remote db instead of localhost.

superteece commented 4 years ago

I just double checked. After cloning a fresh copy, checking out ch16, making a fresh db on both localhost and the remote host as per ch17, creating the .env as per ch17:

flask db migrate & flask db upgrade succeed when .env has localhost as DATABASE_URL

flask db migrate & flask db upgrade fail for the same error as in the original post when .env has the remote host as DATABASE_URL.

The VMs are CENTOS 8. The databases are on MariaDB.

It certainly seems to line up with issues known to stem from multi-threading I just don't understand the suggested fixes and came here hoping I wasn't the first ask about it.

superteece commented 4 years ago

Stack trace, if it helps: https://gist.github.com/superteece/fad2469cdb05ce93303766299ba01adf

miguelgrinberg commented 4 years ago

Can you connect to the database using a different client, from the same host where you are running the Python app?

superteece commented 4 years ago

Ahh yes, thank you. Simplified troubleshooting is best troubleshooting. I had not granted a user with remote access to the DB. I've now done that and the connection works from the Flask app. Thank you for the quick and patient responses.

Summary:

Attempting to connect from PyMySQL (via flask-sqlalchemy // sqlalchemy) to a remote hosted MariaDB with a SQL user who is NOT granted remote access results in the error sqlalchemy.exc.InternalError: (pymysql.err.InternalError) Packet sequence number wrong - got 1 expected 0

Granting the user remote access like so: GRANT ALL ON microblog.* TO 'microblog'@'<remote_ip>' IDENTIFIED BY '<password>'; immediately clears the issue.