result does not match database data

Bug #920026 reported by Sunfox on 2012-01-22
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Connector/Python
Invalid
Undecided
Unassigned

Bug Description

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

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.

Changed in myconnpy:
status: New → Incomplete
Sunfox (sunfox) wrote :

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.

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

Changed in myconnpy:
importance: Undecided → Critical
assignee: nobody → Geert JM Vanderkelen (geertjmvdk)
status: Incomplete → In Progress
Changed in myconnpy:
importance: Critical → Undecided

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) wrote :

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.

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) wrote :

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.

Changed in myconnpy:
status: In Progress → Invalid
assignee: Geert JM Vanderkelen (geertjmvdk) → nobody
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers