result does not match database data

[Sunfox]

How to reproduce:

from mysql.connector.connection import MySQLConnection

db = MySQLConnection(
    unix_socket = "/var/run/mysqld/mysqld.sock",
    user = "user",
    password = "pass",
    database = "database"
)

sql = """
    SELECT p.*
    FROM `cw_product_part` p
    WHERE p.`product_id` = 253353 AND p.`deleted` = 0
    ORDER BY p.`position`
"""

cur = db.cursor()
cur.execute(sql)
for row in cur:
    # gives 2 rows
    print row
cur.close()

print "\n\n
import time
time.sleep(20)
# now in phpmyadmin i change `deleted` = 1 on one of two rows

# do same query once again
cur = db.cursor()
cur.execute(sql)
for row in cur:
    # gives same 2 rows, should give only one
    print row
cur.close()

python -c "import mysql.connector as db; print db.__version__"
(0, 3, 2, 'devel', 292)

python --version
Python 2.6.5

mysql -NB -e "SELECT VERSION()"
5.5.17-55-log
(percona server)

uname -a
Linux sunfox 2.6.32-36-server #79-Ubuntu SMP Tue Nov 8 22:44:38 UTC 2011 x86_64 GNU/Linux

[Geert JM Vanderkelen (geertjmvdk)]

Please check:
1) You are using MySQL 5.5, so default storage engine is InnoDB, means that it is transactional
2) and if so, make sure you do COMMIT when you are updating data.

[Sunfox (sunfox)]

Hello

1) I think storage engine not depends, but yes, I am using mysql 5.5 and InnoDB
2) I do not update table in the same application. If I do, second query result is ok. if another application does any sort of update anywhere in the database, myconnpy returns old (cached) results until I close connection and reconnect

I used simple console script I posted to avoid influence of my application, so it's not bug in application, but in myconnpy. Plus, if I use MySQLdb instead myconnpy, everything is ok.

[Geert JM Vanderkelen (geertjmvdk)]

Thanks for the extra clarifications, and yes, I just reproduced.

[Geert JM Vanderkelen (geertjmvdk)]

After some more testing and wondering if anything is broken in the code, I'm positive there is nothing wrong.

* Storage Engine: if you would use MyISAM, you will see proper result (but don't switch to MyISAM of course!)
* Transaction Isolation: if you have REPEATABLE-READ (which is default), the behavior you see is correct.
* Need COMMIT: If you going to reuse a cursor and want the newest information, you need to COMMIT, cause everything is a transaction. You can set auto_commit=True in the connection options of course.

Illustrated using some code, storage engine InnoDB and REPEATABLE-READ transaction isolation:

  cnx = mysql.connector.connect(database='test')
  cursor.execute("SELECT id,c1 FROM t1")
  # Transaction started
  # Do an update on the data in other connection (and COMMIT)
  cursor.execute("SELECT id,c1 FROM t1")
  # SELECT same data, but we are still in the same transaction
  # Same data will be returned, although data is updated

Solution:
 cnx = mysql.connector.connect(database='test')
  cursor.execute("SELECT id,c1 FROM t1")
  cnx.commit()
  # Do an update on the data in other connection (and COMMIT)
  cursor.execute("SELECT id,c1 FROM t1")
  cnx.commit()
  # Data shows now the updated data

Please let us know if that explains what you experience.

[Sunfox (sunfox)]

Hello, if You look closely on my first code, you see that I am opening new cursor on second query. Of cource I am not doing commit after read transaction, but every query is isolated in own cursor which is closed after query. This works perfectly with MySQLdb, but not with myconnpy.

[Geert JM Vanderkelen (geertjmvdk)]

Yes, but a session (or connection) can have multiple cursors, and all can be used part of the same transaction. Closing a cursor does not mean the transaction gets committed or rolled back. For example, you can have a cursor reading the data (probably buffering the result), and another cursor for updating data.

MySQLdb does not turn off autocommit, AFAIK.

[Sunfox (sunfox)]

You are right. It's because autocommit. I changed myconnpy to MySQLdb in hurry to solve my problem and didn't noticed this. When I disable autocommit in MySQLdb, it acts same way. Thanks a lot for Your help and keep Your great work.