Packet sequence number wrong

[ghost]

I've been using PyMySQL 0.6.6 for a while now with no issues. However after updating to 0.7.1, I'm getting this error a lot, and PyMySQL has become completely unusable for me because of it.

  File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 146, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 296, in _query
    conn.query(q)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 819, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1001, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1285, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 952, in _read_packet
    (packet_number, self._next_seq_id))
pymysql.err.InternalError: Packet sequence number wrong - got 101 expected 1

The packet numbers vary, but I'm getting this for every query now, no matter how simple.
Things will work as expected for a few minutes after starting my script, but soon after these errors will begin appearing and no queries will work anymore.

I'm not sure if there's something I can do to resolve this in my script (some changes made in newer versions that I need to accommodate for) or if I should just figure out how to install the older version as a temporary solution. As it is now, this is a complete showstopper for me.

[methane]

Please show me your code using PyMySQL.
It may means old PyMySQL works accidentlly.

[ghost]

I can't provide the full code, but I can show you how I'm using PyMySQL:

# single row query
cursor.execute( "SELECT * FROM uploads WHERE ID = '%s'" % fileid )
row = cursor.fetchone()
print( row['Filename'] )

# multiple row query
cursor.execute( "SELECT * FROM uploads WHERE Status < 4" )
result = cursor.fetchall()
for row in result:
    print( row['ID'], row['Filename'] )

# update query
cursor.execute( "UPDATE uploads SET Status = '%s' WHERE ID = '%s'" % ( status, fileid ) )

All other queries in my code are just like these three.

The only weird thing I can think of is maybe my connection code, however this worked fine in 0.6.6:

connection = pymysql.connect( host=config.db_host, port=3306, user=config.db_username, passwd=config.db_password, db=config.db_database )
connection.autocommit( 1 )
cursor = connection.cursor( pymysql.cursors.DictCursor )

[methane]

Is your application single thread?

[ghost]

Ahh, it's multithreaded. Could this be the problem? Do I need one connection per thread?

[methane]

Yes.

[methane]

See https://www.python.org/dev/peps/pep-0249/#threadsafety

PyMySQL's threadsafety is 1.

[ghost]

Oops. My mistake! Didn't realize that. Makes sense.
Funny how the problem didn't present itself in 0.6.6

Thanks for all your help!

[EaconTang]

Thanks for the issue, I finally fix my problem which fucked me these days!

[kadnan]

@EaconTang @dougty Can you please tell how did you fix it?

[ghost]

@kadnan You need separate pymysql connections for each thread

[kadnan]

@dougty I am rather using multiprocessing than multithreading. My code look likes:

with Pool(2) as p:
            result = p.map(parse, links)

here parse() method performs some MYSL INSERT/UPDATE operations where it's getting stuck.

Are you saying I explicitly call pysql.connect() for each process?

[ghost]

@kadnan Yes, you need one pysql.connect() for each process/thread. As far as I know that's the only way to fix it. PyMySQL is not thread safe, so the same connection can't be used across multiple threads.

[ahmedsaalah]

@dougty @methane could you tell me how make pysql.connect() for each process/thread ?

[jkklee]

@dougty @ahmedsaalah I'v faced this problem before, then I created a module which provide connection pools in multi-threads mode. see here
@methane my code maybe ugly, but I'll be appreciate if you can make some suggestions.

[methane]

My suggestion is using existing robust system like SQLAlchemy's engine.

[MyJoiT]

@methane

SQLAlchemy also has this problem in multithread if you use one session,like this:

# base.py
engine = create_engine(
    get_db_str(),
    echo=False,
    pool_recycle=300,
    poolclass=QueuePool,
    pool_size=10,
    max_overflow=10,
    pool_timeout=30,
    listeners=[Reconnect()]
)

Session = sessionmaker(bind=engine)

# user1.py
from .base import Session
session1 = Session()
session1.query

# user2.py
from .base import Session
session2 = Session()
session2.query

I use ab command send 1000 requests to system. System raise that exception after some requests.