How to disable query cache with mysql.connector

Posted on

Question :

How to disable query cache with mysql.connector

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?

Answer #1:

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.

Answered By: Rictrunks

Answer #2:

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 By: NBajanca

Answer #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 By: Lynn Han

Answer #4:

try this,

conn.autocommit(True);

this will auto commit after each of you select query.

Answered By: binbincai

Answer #5:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *