How to disable query cache with mysql.connector

Question

I'm connecting mysql on my Kivy application.

import mysql.connector
con = mysql.connector.Connect(host='XXX', port=XXX, user='XXX', password='XXX', database='XXX')
cur = con.cursor()
db = cur.execute("""select SELECT SQL_NO_CACHE * from abc""")
data = cur.fetchall()
print (data)

After inserting or deleting on table abc from another connection; i call the same query on python; but data is not updating.

I add the query "SET SESSION query_cache_type = OFF;" before select query, but it didn't work. Someone said "select NOW() ..." query is not cachable but it didn't work again. What should I do?

I checked it now. It says "transaction-isolation = REPEATABLE-READ". Is it normal? — kadir_cakir
– kadir_cakir, Commented Feb 23, 2014 at 20:43
I've tested "... LOCK IN SHARE MODE" query, but when i call this, I can not insert or update from another connection. I want to insert or update from another connection and I want to call select query again on Python with NEW results. — kadir_cakir
– kadir_cakir, Commented Feb 23, 2014 at 20:53
@user3051668 Does the code that inserts/updates the db call .commit() ? — nos
– nos, Commented Feb 23, 2014 at 20:58

kadir_cakir · Accepted Answer · 2020-12-10 11:14:57Z

37

I solved this by adding the code after fetchall()

con.commit()

Calling the same select query without doing a commit, won't update the results.

edited Dec 10, 2020 at 11:14

answered Feb 23, 2014 at 21:03

kadir_cakir

1,0932 gold badges16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Fisher Over a year ago

Why was this answer so hard to find? I'm thankful for it. I thought I was going crazy that after updating a row in my table and running a select query again, I was still getting the old values.

NBajanca · Accepted Answer · 2018-07-25 10:34:39Z

17

The solution is to use:

Once:
```
con.autocommit(True)
```
Or, after each select query:
```
con.commit()
```

With this option, there will be a commit after each select query. Otherwise, subsequent selects will render the same result.

This error seems to be Bug #42197 related to Query cache and auto-commit in MySQL. The status is won't fix!

In a few months, this should be irrelevant because MySQL 8.0 is dropping Query Cache.

answered Jul 25, 2018 at 10:34

NBajanca

3,7803 gold badges30 silver badges54 bronze badges

Comments

ANjaNA · Accepted Answer · 2015-06-05 07:23:36Z

3

try this,

conn.autocommit(True);

this will auto commit after each of you select query.

edited Jun 5, 2015 at 7:23

ANjaNA

1,4322 gold badges16 silver badges31 bronze badges

answered Jun 5, 2015 at 6:33

binbincai

412 bronze badges

1 Comment

s3n0 Over a year ago

In my case db.autocommit(True) did not work. I use mysql-connector-python-2.0.4. I had to use another entry: db.autocommit = True and this has helped.

Lynn Han · Accepted Answer · 2017-08-31 06:25:51Z

3

I encounterd the same problem that has been solved and used the above method.

conn.commit()

and I found that different DBMS has different behavior,not all DBMS exist in the connection cache

answered Aug 31, 2017 at 6:25

Lynn Han

5036 silver badges8 bronze badges

3 Comments

GhostCat Over a year ago

Is that supposed to be an answer to the question?

Lynn Han Over a year ago

Maybe,hope to have a better answer

GhostCat Over a year ago

A) do you mean below instead of above? B) no, so far your answer is hard to comprehend, and really not very helpful. "Not all DBMS exist in cache" ... as said: not helpful.

user149341 · Accepted Answer · 2014-02-23 20:42:38Z

-1

The MySQL query cache is flushed when tables are modified, so it wouldn't have that effect. It's impossible to say without seeing the rest of your code, but it's most likely that your INSERT / DELETE query is failing to run.

answered Feb 23, 2014 at 20:42

user149341

1 Comment

kadir_cakir Over a year ago

No, it's not failing. Because I'm checking on HeidiSQL.

Collectives™ on Stack Overflow

How to disable query cache with mysql.connector

5 Answers 5

1 Comment

Comments

1 Comment

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related