cursor.execute(); cursor.fetchall() returns empty tuple for first two calls on newly dropped/recreated/populated table #399

mikeyhew · 2015-12-10T16:34:30Z

This may be similar to #390, I don't know enough about MySQL to be able to tell.

import pymysql
from .. import config

connection = pymysql.connect(
        host=       config.MYSQL_HOST,
        user=       config.MYSQL_USER,
        password=   config.MYSQL_PASSWORD,
        db=         config.TEST_DB_NAME,
        charset=    'utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
        )
c = connection.cursor()
connection.cursor().execute("""
drop table if exists tweets;
create table tweets (
    id int primary key auto_increment,
    handle varchar(10),
    text varchar(100)
);
insert into tweets (handle, text) values
('yolo123', 'whatup'),
('user11',  'wazzzap');
""")

connection.commit()

for i in range(0,10):
    c.execute("""
        select * from tweets
    """)
    print(c.fetchall())

The output is as follows:

()
()
[{'id': 1, 'handle': 'yolo123', 'text': 'whatup'}, {'id': 2, 'handle': 'user11', 'text': 'wazzzap'}]
[{'id': 1, 'handle': 'yolo123', 'text': 'whatup'}, {'id': 2, 'handle': 'user11', 'text': 'wazzzap'}]
[{'id': 1, 'handle': 'yolo123', 'text': 'whatup'}, {'id': 2, 'handle': 'user11', 'text': 'wazzzap'}]

Interestingly, even if you add a time.sleep(1) at the end of the for loop, it still returns an empty tuple for the first two c.execute(); c.fetchall() cycles.
If you edit the for loop to the following,

for i in range(5):
    c.execute("""
        select * from tweets
    """)
    for j in range(5):
        print(c.fetchall())

It prints out 5 empty tuples for the first two times c.execute is called.

Version info:
MySQL server version: 5.5.44-0ubuntu0.14.04.1

>>> pymysql.__version__
   '0.6.7.None'

The text was updated successfully, but these errors were encountered:

methane · 2016-01-03T01:53:21Z

You haven't closed cursor used for create and insert into tweet table.
While it's wrong usage, I'll try to raise Exception for such case.

fixes PyMySQL#399

mikeyhew · 2016-01-04T06:47:05Z

Changed the code to only use one cursor. Now it's still returning an empty tuple for the first query after connection.commit, even if that query doesn't use the tweets table. This is true regardless of whether the connection is autocommit.

c = connection.cursor()
c.execute("""
    select * from tweets
""")
print("query 1:")
print(c.fetchall())

c.execute("""
drop table if exists tweets;
create table tweets (
    id int primary key auto_increment,
    handle varchar(10),
    text varchar(100)
);
insert into tweets (handle, text) values
('yolo123', 'whatup'),
('user11',  'wazzzap');
""")
print('result of drop/create/insert')
print(c.fetchall())
connection.commit()

c.execute("""
    select 1
""")
print("query after commit:")
print(c.fetchall())

c.execute("""
    select 1
""")
print("2nd query after commit:")
print(c.fetchall())

outputs:

query 1:
[{'id': 1, 'handle': 'yolo123', 'text': 'whatup'}, {'id': 2, 'handle': 'user11', 'text': 'wazzzap'}]
result of drop/create/insert
()
query after commit:
() # should be [{'1':1}]
2nd query after commit:
[{'1': 1}]

mikeyhew · 2016-01-04T06:54:18Z

Interestingly, removing the statement connection.commit makes everything work as expected. Maybe there's something going wonky in there?

import pymysql
import config

connection = pymysql.connect(
        host=       config.MYSQL_HOST,
        user=       config.MYSQL_USER,
        password=   config.MYSQL_PASSWORD,
        db=         config.TEST_DB_NAME,
        charset=    'utf8mb4'
        )

c = connection.cursor()
c.execute("""
    select * from tweets
""")
print("query 1:")
print(c.fetchall())

c.execute("""
    drop table if exists tweets;
    create table tweets (
        id int primary key auto_increment,
        handle varchar(10),
        text varchar(100)
    );
    insert into tweets (handle, text) values
    ('yolo123', 'whatup'),
    ('user11',  'wazzzap');
""")
print('result of drop/create/insert')
print(c.fetchall())

#connection.commit()

c.execute("""
    select * from tweets
""")
print("query after commit:")
print(c.fetchall())

c.execute("""
    select * from tweets
""")
print("2nd query after commit:")
print(c.fetchall())

query 1:
()
result of drop/create/insert
()
query after commit:
((1, 'yolo123', 'whatup'), (2, 'user11', 'wazzzap'))
2nd query after commit:
((1, 'yolo123', 'whatup'), (2, 'user11', 'wazzzap'))

methane · 2016-01-04T07:59:14Z

I'm sorry. #403 was not perfect. It doesn't handle conn.commit() case.

    drop table if exists tweets;
    create table tweets (
        id int primary key auto_increment,
        handle varchar(10),
        text varchar(100)
    );
    insert into tweets (handle, text) values
    ('yolo123', 'whatup'),
    ('user11',  'wazzzap');

This query has 4 statements. All of 4 statements don't returns rows..
MySQL returns "OK packet" for statements having no rows.

receive queue: [OK (has next), OK (has next), OK (has next), OK]

In this case, PyMySQL only read first "OK packet".

receive queue: [OK (has next), OK (has next), OK]

You should call cursor.nextset() until it returns None to read remaining three "OK packet".
cursor.close() and cursor.execute() do it internally.

MySQL also returns "OK packet" for commit.
In your case, conn.commit() read one of three remaining "OK packet".

receive queue: [OK (has next), OK, OK]

c.execute("""
    select 1
""")

Since cursor think "has next", it fetches results until receive result not "has next" before send the query.

receive queue: [OK]

Then, cursor sends the query and MySQL returns "resultset" for it.

receive queue: [OK, resultset(1)]

Then, cursor fetches "OK" and it think "this query has not resultset".

print("query after commit:")
print(c.fetchall())  # ==> None

c.execute("""
    select * from tweets
""")

Since the result cursor having is not "has next", cursor sends second query and MySQL returns resultset for it.

receive queue: [resultset(1), resultset(2)]

Then cursor reads resultset(1).

print("2nd query after commit:")
print(c.fetchall())   # => show result for previous query.

This is what happens in your case.

methane added a commit to methane/PyMySQL that referenced this issue Jan 3, 2016

Raise ProgrammingError when previous multi result not fetched.

27e0da3

fixes PyMySQL#399

methane mentioned this issue Jan 3, 2016

Raise ProgrammingError when previous multi result not fetched. #403

Merged

methane closed this as completed in #403 Jan 3, 2016

methane reopened this Jan 4, 2016

methane added a commit to methane/PyMySQL that referenced this issue Jan 4, 2016

Add test to reproduce PyMySQL#399

abcb5f9

methane mentioned this issue Jan 4, 2016

Calling connection's method during multiple result breaks command sync #406

Merged

methane closed this as completed in #406 Jan 5, 2016

github-actions bot locked as resolved and limited conversation to collaborators Aug 5, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

cursor.execute(); cursor.fetchall() returns empty tuple for first two calls on newly dropped/recreated/populated table #399

cursor.execute(); cursor.fetchall() returns empty tuple for first two calls on newly dropped/recreated/populated table #399

mikeyhew commented Dec 10, 2015

methane commented Jan 3, 2016

Uh oh!

mikeyhew commented Jan 4, 2016

Uh oh!

mikeyhew commented Jan 4, 2016

Uh oh!

methane commented Jan 4, 2016

Uh oh!

Uh oh!

cursor.execute(); cursor.fetchall() returns empty tuple for first two calls on newly dropped/recreated/populated table #399

cursor.execute(); cursor.fetchall() returns empty tuple for first two calls on newly dropped/recreated/populated table #399

Comments

mikeyhew commented Dec 10, 2015

methane commented Jan 3, 2016

Uh oh!

mikeyhew commented Jan 4, 2016

Uh oh!

mikeyhew commented Jan 4, 2016

Uh oh!

methane commented Jan 4, 2016

Uh oh!