Malformed packet Problem with Mysql 5.7

[BrunoKruz]

Hello guys,
I have an api that was made over python 3.5 with SQLAlchemy==1.2.10
My api connects to a RDS Aurora with mysql engine 5.7.

In the api i have a get method that when executed return some lines from my
database. When i run the api local pointing out
to rds every think works fine, but when the i try to execute the same get
method deployed into Amazon Ecs, i get the follow error.

sqlalchemy.exc.OperationalError

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2027,
'Malformed packet') [SQL: 'SELECT compra.compra_id AS compra_compra_id,
compra.cnpj_cliente AS compra_cnpj_cliente, compra.nome_cliente AS
compra_nome_cliente, compra.cnpj_agencia AS compra_cnpj_agencia,
compra.nome_agencia AS compra_nome_agencia, compra.contato_atendimento AS
compra_contato_atendimento, compra.assistente AS compra_assistente,
compra.player AS compra_player, compra.cliente_direto AS
compra_cliente_direto, compra.status AS compra_status, compra.tipo AS
compra_tipo, compra.dt_criacao AS compra_dt_criacao, compra.dt_atualizacao
AS compra_dt_atualizacao \nFROM compra \n LIMIT %s, %s'] [parameters: (0,
50)] (Background on this error at: http://sqlalche.me/e/e3q8)

Link to the api:
https://globoexpress.cluster.stg.negocios.tvglobo.com.br/compras/api/v1/Order/

[methane]

It is unlikely bug and here is not user forum.
Unless you can create reproducible example, bug will be in your application. Don't ask here.

Maybe, you used same connection from multiple process or thread. But I'm not sure and I'm not your free tech support.

[vpogrebi]

Same issue haunted my team for a while, and there's very little useful information about it on the web. We spent a lot of time troubleshooting the issue - and FINALLY found solution that solved it (at least - for our team)!

We found that Django sets "charset" option to "utf8" for database connections by default. During troubleshooting we used two separate database connection objects: one created for us by Django, and another - created manually using direct _mysql.connect() command. When we executed same exact query using both connection objects - the one created by Django resulted in "django.db.utils.OperationalError: (2027, 'Malformed packet')" (which is exactly what we were getting in our API), but the second connection (manual) - worked without any issues. Further comparison of the two connection objects (we actually had to use Python debugger "pdb" and set breakpoint within django.db.backends.mysql.base.py for that) - revealed that Django creates connection by passing "charset":"utf8", while manual _mysql connection - uses "latin1". As soon as we added "charset":"latin1" to DATABASES["default"]["OPTIONS"] within our settings.py - this error disappeared.

To summarize, solution (for us) was to explicitly set "charset":"latin1" within "OPTIONS" config section of DATABASES configuration - for every database alias. I can not say for sure that this will work for everyone who experiences this error - but it certainly works for us.

I suspect that any MySQL connection that uses "utf8" charset - would exhibit this behavior (in our case Django is just a middleman); setting charset to "latin1" - should resolve this issue.

[methane]

UTF-8 is the recommended charset. So using latin1 is not a solution.

[vpogrebi]

Thank you @methane,

Turned out my previous solution - does not address the issue (at least - does not address its "root cause"). In fact, "charset" update - cause more issues than it solved (caused many side-effects).

After more investigation, it turned out that the issue (in our case) was really caused by dependency misconfiguration... When we upgraded to Django 3.1.5, our project's requirements.txt was set to use "django-mysql" v3.11.1 and "mysqlclient" v2.0.3. Based on "django-mysql" documentation (https://django-mysql.readthedocs.io/en/latest/installation.html), this package requires mysqlclient 1.3 or 1.4 - while we were using v2.0.3.

After downgrading to the latest version in the 1.4 track (1.4.6) - things came back to normal, no more "Malformed packet" errors.

To summarize, this issue seems to be an indicator of dependency misconfiguration (where MySQL client library is incompatible with the server side). Solution - is to bring dependencies "in synch", to have the properly configured.

[methane]

Malformed packet is happen when MySQL server sent packet that client library (not this Python library. libmysqlclient or libmariadb) can not understand it.

So right solution is:

• Upgrade your MySQL, and client library.
• If your server is MySQL not MariaDB, use libmysqlclient instead of libmariadb too.
• Report the issue to MySQL or MariaDB.

[neverho0d]

My five cents to the discussion. My server is Percona 5.7 and after migration my code from Python 2 to 3 I found some queries failed with the "Malformed packet" error. I left the charset as it is - utf-8, checked all dependences - they also were okay.
I noticed that not all queries are failed - some work okay, but some not.
My solution is to remove all newlines ("\n") from the failed SQL.
It looks weird anyway and I suspect something wrong with underlying libraries, so I hope my findings will help to catch the real bug.
As I see OP has newlines in his SQL too. ;)

[ghost]

https://www.wireshark.org/docs/wsug_html_chunked/AppMessages.html
You could (if it’s TCP) enable reassembly for TCP and the specific dissector (if possible) in the Edit|Preferences menu.
As long as you use wireshark or tshark to capture the packets.