By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

SELECT returns old/cached data (Lambda python code calling MySQL)

0

Hi All,

I have two lambda functions written in Python - each triggered by separate AWS IoT events. Function 1 = Return a RecordSet where DateShip column IS NULL using SELECT in a MySQL table. Function 2 = UPDATE DateShip column with a 'Date & Time' in a single row with UniqueID in MySQL table.

Operation Steps.

  1. Function 1 called. Lambda Select function queries RDS MySQL for table that contains NULLs. For example this may return 10 rows.
  2. Function 2 called. Lambda Update function updates a NULL value with a 'Date & Time' value. This is confirmed with a DBeaver client connection to the same RDS MySQL instance (i.e. SELECT * ) and now returns 9 rows containing NULLs (i.e. one row has been UPDATEd with 'Date & Time' in DateShip column).
  3. Function 1 called again. Lambda Select function queries RDS MySQL for table that contains NULLs. This still returns the same data as the original call in step 1 - i.e. 10 rows are returned, not 9 as expected.

After some time (maybe 30s, or 5mins) - repeat calls to Function 1 (Select NULLs) finally returns the expected data (9 rows).

I receive no errors in my trace data logs. I can call the Lamba function from the TEST environment or real (via AWS IoT event - MQTT message) and the result is the same.

It is if the MySQL SELECT is getting cached/old data from the RDS connection rather than really querying the MySQL table for the latest data. Has anyone else seen this issue? I would be grateful for any assistance - as I've spent days trying to understand what I've done wrong.

Kind regards

Topics
ServerlessComputeMigration & ModernizationAnalyticsDatabaseInternet of Things (IoT)
Tags
AWS LambdaAmazon Relational Database ServiceAWS IoT EventsMySQL
Language
English
bakjus
asked 7 months ago275 views
1 Answer
1
Accepted Answer

You did not include your code here, but could it be that you are running your query outside the handler code? If you do, you should note that yje code outside the handler is invoked only once during the initialization stage of your execution environment.n If this is the case, move your query inside the handler and run it for each invocation.

profile pictureAWS
EXPERT
Uri
answered 7 months ago
  • bakjus
    7 months ago

    Thank you Uri Perfect. Yes - you're absolutely correct. I was connecting to the DB outside of the event handler, even though the query was inside the event handler. I've moved the connect inside and it's working now. I apologise - I think I did read this in the help, but clearly it didn't "go in".

  • Uri EXPERT
    7 months ago

    Actually, creating the connection outside the handler is recommend best practice usually. I think in your case there is something cached on the connection object itself. You can create the connection for every request, or maybe there is some mechanism on the connection itself to refresh it.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content