FastAPI-MySql-SqlAlchemy Intermittent Broken Pipe Error
Hi everyone
I keep getting BrokenPipeError: [Errno 32] Broken pipe error from MySQL using SqlAlchemy. It usually happens right when logging in or refreshing tokens. Meaning.. right at the first use after not using for a while. I am the only user plus a few test users so the API service is not continuously used. Did anybody see this? What could be the reason. Could it be a time out issue with MySql?
Solution:Jump to solution
are you connecting to the database via the private network? if not, definitely do that
26 Replies
Project ID:
dba6cd6e-5ea1-494c-b77f-81377e24fbdeare you getting this error from the mysql service or your fastapi service?
Sorry.. Project Id: dba6cd6e-5ea1-494c-b77f-81377e24fbde
I think from SqlAlchemy
Copying one by one takes long so I took a screenshot
Sql Alchemy says:...
OperationalError
Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.
This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.
The link at the end of the screenshot
https://sqlalche.me/e/20/e3q8
It usually happens, after first login if I have not used for a while. Or... again after not using right at the time of refreshing JWT tokens
Solution
are you connecting to the database via the private network? if not, definitely do that
No.
Should I?
I guess I should
yes absolutely
ok. Will try that. I would have to change the connection string for that.
yep!
I am sure there is an example but If I cannot find it, I'll check out the docs
what is the variable you use for the database url?
SQLALCHEMY_DATABASE_URL
what is the name of your mysql service
and is the mysql service in the same project as the fastapi service?
MySQL-9_Bm
Yes, they are in the same project
SQLALCHEMY_DATABASE_URL=${{MySQL-9_Bm.MYSQL_PRIVATE_URL}}
as easy as thatGreat.. Will do.. Thanks a lot for this
OK. I kept everything the same on the SqlAlchemy connection string, except the URL. For the URL I just used the MySQL service name MySQL-9_Bm and not even the port number since it was suggested in the network settins of MySQL... Everything worked.
so what is
SQLALCHEMY_DATABASE_URL set to now
sans the passwordSo, If I don't get the broken pipe error again, this means it was because I was using the Newtork URL
mysql+pymysql://user_name:my_password@mysql-9bm/database_Name
That is an example SqlAlchemy URL for pymysql driver
that works too, until you change the internal address and forget to update that url
Yeah, I don't know why I did not setup the link internally. Network URL does not make sense when I have the chance to use internal. Thanks again Brody
no problem!
Yes, it did not happen. No broken pipe errors all day. I would have already gotten a few. So this fixed it 🙂 Thanks
happy to hear that!