MySQLdb seems to have a problem finding updated data in the tables.
If I connect to my database the normal way and retrieve data using a stored procedure, it works fine the first time. However, if I later try to collect the data again from the same stored procedure, it provides me with the original data from the first procedure call, even if the data has since been updated.
The presumption is that MySQLdb holds a cache on the client system; is this true? If so, is it possible to turn off the cache? Or force a refresh of the cache?
I know it's not on the database side since if i call the procedure directly it always gives the correct result.
Please help.
bradley
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You presume incorrectly: There is no cache in MySQLdb. Are you using transactions? Are you committing with db.commit()? MySQLdb has auto-commit off by default, whereas the command line client has it on by default.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
thanks that fixed it. i was unaware that a simple stored procedure would be interpreted as a transaction without explicitly stating 'START TRANSACTION' ...
Nevertheless, this is unexpected behaviour since the procedure does not update the database itself, it only retrieves data, and returns it to the client (effectively providing one of 3 'SELECT' statements.
If the database is updated from a secondary source, the procedure should return different results (it does from a terminal client), however using MySQLdb in python it returns the previous (non-updated) results. Which suggests that either the server is not providing the client with the latest data, or the client is caching results for reuse later. If MySQLdb doesn't specifically have a cache, then maybe Python is caching it even after the function has been destroyed?
Anyway, I'm back on track. thanks for your help
-Bradley
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
SELECT will start a new transaction. START TRANSACTION is only important if auto-commit is ON (it disables it until COMMIT or ROLLBACK). If you aren't doing a COMMIT or ROLLBACK between queries, then you are still in the same transaction so the SELECTs will always see the same data. This is all on the MySQL server-side and is normal SQL behavior.
MySQLdb seems to have a problem finding updated data in the tables.
If I connect to my database the normal way and retrieve data using a stored procedure, it works fine the first time. However, if I later try to collect the data again from the same stored procedure, it provides me with the original data from the first procedure call, even if the data has since been updated.
The presumption is that MySQLdb holds a cache on the client system; is this true? If so, is it possible to turn off the cache? Or force a refresh of the cache?
I know it's not on the database side since if i call the procedure directly it always gives the correct result.
Please help.
You presume incorrectly: There is no cache in MySQLdb. Are you using transactions? Are you committing with db.commit()? MySQLdb has auto-commit off by default, whereas the command line client has it on by default.
thanks that fixed it. i was unaware that a simple stored procedure would be interpreted as a transaction without explicitly stating 'START TRANSACTION' ...
Nevertheless, this is unexpected behaviour since the procedure does not update the database itself, it only retrieves data, and returns it to the client (effectively providing one of 3 'SELECT' statements.
If the database is updated from a secondary source, the procedure should return different results (it does from a terminal client), however using MySQLdb in python it returns the previous (non-updated) results. Which suggests that either the server is not providing the client with the latest data, or the client is caching results for reuse later. If MySQLdb doesn't specifically have a cache, then maybe Python is caching it even after the function has been destroyed?
Anyway, I'm back on track. thanks for your help
-Bradley
SELECT will start a new transaction. START TRANSACTION is only important if auto-commit is ON (it disables it until COMMIT or ROLLBACK). If you aren't doing a COMMIT or ROLLBACK between queries, then you are still in the same transaction so the SELECTs will always see the same data. This is all on the MySQL server-side and is normal SQL behavior.
http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html
Note that the default isolation level is REPEATABLE READ, which seems to describe what you're seeing...
Ah!
so my to learn...
so few brain cells.
thx again.
- bradley