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, Feb 23 '14 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, Feb 23 '14 at 20:53
@user3051668 Does the code that inserts/updates the db call .commit() ? — nos, Feb 23 '14 at 20:58

kadir_cakir · Answer 1 · 2020-12-10T11:14:57.200

34

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 '20 at 11:14

answered Feb 23 '14 at 21:03

kadir_cakir

1,013
1
16
22

1

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. – Mike Fisher Sep 28 '20 at 03:09

score 15 · Answer 2 · answered Jul 25 '18 at 10:34

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.

score 3 · Answer 3 · answered Aug 31 '17 at 06:25

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 '17 at 06:25

Lynn Han

443
6
8

1

Is that supposed to be an answer to the question? – GhostCat Aug 31 '17 at 06:33
Maybe,hope to have a better answer – Lynn Han Aug 31 '17 at 06:50
1

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. – GhostCat Aug 31 '17 at 06:55

score 2 · Answer 4 · edited Jun 05 '15 at 07:23

2

try this,

conn.autocommit(True);

this will auto commit after each of you select query.

edited Jun 05 '15 at 07:23

ANjaNA

1,404
2
16
29

answered Jun 05 '15 at 06:33

binbincai

31
2

4

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. – s3n0 Jun 13 '18 at 12:48

score 0 · Answer 5 · answered Feb 23 '14 at 20:42

0

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 '14 at 20:42

No, it's not failing. Because I'm checking on HeidiSQL. – kadir_cakir Feb 23 '14 at 20:46

How to disable query cache with mysql.connector

5 Answers5

Linked

Related