Why is an AWS Lambda python call to a MySQL RDS being cached?

Question

Simplifying my program, I have a MySQL RDS DB, and I want to develop a Lambda function to get the last value inserted in a specific column.

I have got the following code in a lambda function, based on this AWS tutorial:

# Connexion to DB outside the handler, per AWS recomendation 
def lambda_handler(event, context):
    with conn.cursor() as cur:
        cur.execute("SELECT column FROM DB.table ORDER BY create_time DESC LIMIT 1;")  
        row = cur.fetchone()
return row[0]

I am using pymysql.

Basically, in the first call (after saving the lambda function, for example) it works as supposed to, and it returns the last value in the table.

However, for any other call during a short interval (some minutes), it continues to return the same value, independent of any DB changes.

Saving or Waiting for some minutes leads to the correct result. Is it possible that I'm unintentionally caching the result?

Solved it with ``conn.autocommit(True)``, after seeing [this question](https://stackoverflow.com/questions/21974169/how-to-disable-query-cache-with-mysql-connector). — NBajanca, May 15 '18 at 22:03

score 3 · Accepted Answer · answered Jul 25 '18 at 10:31

The solution is to use conn.autocommit(True) once or conn.commit() after each Select query.

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! There is also an issue in pymysql but it is closed.

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

The need for a commit on a select is super annoying. – mbunch Aug 15 '18 at 14:18 — mbunch, Aug 15 '18 at 14:18

Why is an AWS Lambda python call to a MySQL RDS being cached?

1 Answers1