pymysql.err.InternalError: Packet sequence number wrong - got 45 expected 0

Question

I am trying to connect to mysql database via pymysql. Everything works when I SSH into the database via terminal. So, that information is correct. However, when I put the same information in using pymysql, I get the following error:

pymysql.err.InternalError: Packet sequence number wrong - got 45 expected 0

pymysql version: 0.9.2

Code (note that I changed the information regarding host, user, password, db, and port for security reasons).

import pymysql.cursors

connection = pymysql.connect(host='myhost',
                             user='userid',
                             password='mypassword',
                             db='my_database',
                             charset='utf8mb4',
                             port=9047,
                             cursorclass=pymysql.cursors.DictCursor)

except Exception as e:
    print('connection failed')
    print(e)

This code was working earlier. I had to upgrade packages for another project and now it quit working. I think this may be the reason.

Has anyone run into this before? If so, how did you solve it?

Thanks!

*I have also tried with different ports. I notice here that they used the port 22 or 3306, but with port 22, I get a timeout error and port 3306, I get an access denied error.

Lysin

Answer 1

Okay, I figured it out.

I just needed to remove

cursorclass=pymysql.cursors.DictCursor

and now it works :)

Thanks everyone!

Lysin

I dont see how changing the cursorclass and removing DictCursor resolved this issue. Are you sure this is related or can you explain? Also, now you not using DictCursor... which most people want / need. – Jansen

pymysql.connect(...., cursorclass=pymysql.cursors.DictCursor) this occasionally causes an error. If you want DictCursor, get it from connection like cursor = conn.cursor(pymysql.cursors.DictCursor) – Colson

Answer 2

I think the reason was that you using multithread to connect to mysql at one time, the mysql server return the wrong response here.

Mulry

Thanks for the response Rocky! Any idea how to fix it? – Lysin

Answer 3

In my case, this error didn't appeared all the time, only under heavy load.

I'm using Python Tornado + tornado-sqlalchemy that manage the sessions automatically ( create, reuse and close )

I.e: I duplicated the same tab on my website that in the backend creates these large queries - * 15. first 8 or 9 tabs worked fine, after that things started to get crazy, and resulted a sequence of errors:

# file: error.log

# One tab received and crashed, which started a chain of other errors
pymysql.err.InterfaceError: (0, '')

# Other tab afterwards received and crashed:
AttributeError: 'NoneType' object has no attribute 'read'

# Other tab afterwards received and crashed:
pymysql.err.InternalError: Packet sequence number wrong - got 102 expected 3

# Other tab received:
sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back. (Background on this error at: https://sqlalche.me/e/14/8s2b)

The behavior:

• Every tab, initiate a new class ( tornado requesthandler ) - and has a different session ( I've logged the session memory value it to the error log )
• I'm using in the Tornado's page class: executor = ThreadPoolExecutor(max_workers=4)
• I'm using lazy load in the relationship(.., lazy='subquery')

What solved this error for me was:

• Removing the lazy load .. because every small error in the "parent" object of the query - causes these "children" to be "orphands" and then I received the "invalid transactions that should be rolledback" first. though, you can also solve it I believe with autocommit=True in some cases, in my specific case I'm using it - but didn't had any affect.
• Increased the max_workers=10

I opened 20 tabs - they all worked fast and not a single crash. Hope it might help others ...

Couperin

Answer 4

I too had the following error when attempting to connect to a mariadb RDS instance: (pymysql.err.InternalError) Packet sequence number wrong - got 1 expected 0

The issue for me was my IP was being blocked by the DB server. I had a failing connection running on a 5 min interval cron job and the DB server blocked me after 100 failed connections.

Unfortunately, the pymysql driver fails to bubble the correct error. Using the official mariadb connector I was able to correctly identify the issue: sqlalchemy.exc.OperationalError: (mariadb.OperationalError) Host 'XXX.XXX.XXX.XXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Once I ran mysqladmin flush-hosts on the DB server everything started to work.

Hopefully I can save a poor soul or 2 from this anguish!

Monoatomic

Answer 5

In my case Error was "(pymysql.err.InternalError) Packet sequence number wrong - got 1 expected 0\n(Background on this error at: http://sqlalche.me/e/14/2j85)"

Problem with Database setting I assigned IP for access same server. But after assigned localhost it is working fine.

Hambletonian