3

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?

Improve this question
1
  • Solved it with conn.autocommit(True), after seeing this question. Commented May 15, 2018 at 22:03

1 Answer 1

Reset to default
4

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.

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

1 Comment

The need for a commit on a select is super annoying.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.