2

I am currently using AWS Lambda (Python 3.6) to talk to a MySQL database. I also have Slack commands triggering the queries to the database. On occasion, I have noticed that I can change things directly through MySQL Workbench and then trigger a query through Slack which returns old values. I currently connect to MySQL outside of the python handler like this:

BOT_TOKEN   = os.environ["BOT_TOKEN"]
ASSET_TABLE = os.environ["ASSET_TABLE"]
REGION_NAME = os.getenv('REGION_NAME', 'us-east-2')
DB_NAME     = os.environ["DB_NAME"]
DB_PASSWORD = os.environ["DB_PASSWORD"]
DB_DATABASE = os.environ["DB_DATABASE"]
RDS_HOST    = os.environ["RDS_HOST"]
port        = os.environ["port"]

try:
    conn = pymysql.connect(RDS_HOST, user=DB_NAME, passwd=DB_PASSWORD, db=DB_DATABASE, connect_timeout=5, cursorclass=pymysql.cursors.DictCursor)
    cursor = conn.cursor()
except:
    sys.exit()

The MySQL connection is done outside of any definition at the very top of my program. When Slack sends a command, I call another definition that then queries MySQL. This works okay sometimes, but other times can send my old data that has not updated. The whole layout is like this:

imports SQL connections SQL query definitions handler definition

I tried moving the MySQL connection portion inside of the handler, but then the SQL query definitions do not recognize my cursor (out of scope, I guess).

So my question is, how do I handle this MySQL connection? Is it best to keep the MySQL connection outside of any definitions? Should I open and close the connection each time? Why is my data stale? Will Lambda ALWAYS run the entire routine or can it try to split the load between servers (I swear I read somewhere that I cannot rely on Lambda to always read my entire routine; sometimes it just reads the handler)?

I'm pretty new to all this, so any suggestions are much appreciated. Thanks!

Rest of the code if it helps:

################################################################################################################################################################################################################
# Slack Lambda handler.
################################################################################################################################################################################################################

################################################################################################################################################################################################################
# IMPORTS
###############
import sys
import os
import pymysql
import urllib
import math
################################################################################################################################################################################################################


################################################################################################################################################################################################################
# Grab data from AWS environment.
###############
BOT_TOKEN   = os.environ["BOT_TOKEN"]
ASSET_TABLE = os.environ["ASSET_TABLE"]
REGION_NAME = os.getenv('REGION_NAME', 'us-east-2')
DB_NAME     = os.environ["DB_NAME"]
DB_PASSWORD = os.environ["DB_PASSWORD"]
DB_DATABASE = os.environ["DB_DATABASE"]
RDS_HOST    = os.environ["RDS_HOST"]
port        = os.environ["port"]
################################################################################################################################################################################################################


################################################################################################################################################################################################################
# Attempt SQL connection.
###############
try:
    conn = pymysql.connect(RDS_HOST, user=DB_NAME, passwd=DB_PASSWORD, db=DB_DATABASE, connect_timeout=5, cursorclass=pymysql.cursors.DictCursor)
    cursor = conn.cursor()
except:
    sys.exit()
################################################################################################################################################################################################################

# Define the URL of the targeted Slack API resource.
SLACK_URL = "https://slack.com/api/chat.postMessage"

################################################################################################################################################################################################################
# Function Definitions.
###############

def get_userExistance(user):
    statement = f"SELECT 1 FROM slackDB.users WHERE userID LIKE '%{user}%' LIMIT 1"
    cursor.execute(statement, args=None)
    userExists  =  cursor.fetchone()
    return userExists

def set_User(user):
    statement = f"INSERT INTO `slackDB`.`users` (`userID`) VALUES ('{user}');"
    cursor.execute(statement, args=None)
    conn.commit()
    return

################################################################################################################################################################################################################

################################################################################################################################################################################################################
# Slack command interactions.
###############
def lambda_handler(data, context):

    # Slack challenge answer.
    if "challenge" in data:
        return data["challenge"]

    # Grab the Slack channel data.
    slack_event    = data['event']
    slack_userID   = slack_event['user']
    slack_text     = slack_event['text']
    channel_id     = slack_event['channel']
    slack_reply    = ""

    # Check sql connection.
    try:
        conn = pymysql.connect(RDS_HOST, user=DB_NAME, passwd=DB_PASSWORD, db=DB_DATABASE, connect_timeout=5, cursorclass=pymysql.cursors.DictCursor)
        cursor = conn.cursor()
    except pymysql.OperationalError:
        connected = 0
    else:
        connected = 1

    # Ignore bot messages.
    if "bot_id" in slack_event:
        slack_reply = ""
    else:
        # Start data sift.
        if slack_text.startswith("!addme"):
            if get_userExistance(slack_userID):
                slack_reply = f"User {slack_userID} already exists"
            else:
                slack_reply = f"Adding user {slack_userID}"
                set_user(slack_userID)

        # We need to send back three pieces of information:
        data = urllib.parse.urlencode(
            (
                ("token", BOT_TOKEN),
                ("channel", channel_id),
                ("text", slack_reply)
            )
        )
        data = data.encode("ascii")

        # Construct the HTTP request that will be sent to the Slack API.
        request = urllib.request.Request(
            SLACK_URL, 
            data=data, 
            method="POST"
        )
        # Add a header mentioning that the text is URL-encoded.
        request.add_header(
            "Content-Type", 
            "application/x-www-form-urlencoded"
        )

        # Fire off the request!
        urllib.request.urlopen(request).read()

    # Everything went fine.
    return "200 OK"
################################################################################################################################################################################################################

1 Answer 1

2

All of the code outside the lambda handler is only run once per container. All code inside the handler is run every time the lambda is invoked.

A lambda container lasts for between 10 and 30 minutes depending on usage. A new lambda invocation may or may not run on an already running container.

It's possible you are invoking a lambda in a container that is over 5 minutes old where your connection has timed out.

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

4 Comments

So the best course of action would be to run the MySQL connect inside the handler and somehow pass it to my other definitions? Or two check if the MySQL connection is open inside the handler and if not, reconnect?
Not overly familiar with pymysql, but in similar circumstances I would check the connection at the top of the handler code and re-establish the connection if lost. You may have to close and open the cursor for this to work.
I added a try/except (updated code to show where), but I still have an issue where I can update a value in MySQL Workbench and it is not seen in a query. I can save Lambda (guessing the containers reinitiates) and the new value is pulled correctly. I can also have slack use a set query and that seems to update. But why is it sometimes the data does not match the database?
Nevermind, I started reading some similar questions and it seems that adding autocommit=True to my connection code solved this. Thanks for the help on the other stuff!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.