CODEWITHSUNDEEP
pythonmysqlamazon-web-servicesaws-lambdaamazon-rds
Jay
Jay

Reputation: 113

AWS Lambda RDS MySQL DB Connection InterfaceError

When I try to connect to AWS RDS (MySQL), most of the time I receive an InterfaceError. When I edit the Lambda code and re-run, it will work fine the first time, but then the same error occurs.

My code:

import sys
import logging
import pymysql
import json
import traceback
rds_host  = "*****.rds.amazonaws.com"
name = "*****"
password = "****"
db_name = "myDB"
logger = logging.getLogger()
logger.setLevel(logging.INFO)
try:
    conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
except:
    logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
    sys.exit()
logger.info("SUCCESS: Connection to RDS mysql instance succeeded")
def handler(event, context):
    sub = event['sub']
    username = event['username']
    givenname = event['givenname']
    isAdmin = event['isAdmin']
    print (sub)
    print (username)
    print (givenname)
    print (isAdmin)
    data = {}

    cur = conn.cursor()
    try:
        cmd = "SELECT AuthState FROM UserInfo WHERE UserName=" + "\'" + username + "\'"
        rowCnt = cur.execute(cmd)
        print (cmd)
    except:
        print("ERROR: DB Query Execution failed.")
        traceback.print_exc()
        data['errorMessage'] = 'Internal server error'
        response = {}
        response['statusCode'] = 500
        response['body'] =  data
        return response
    if rowCnt <= 0:
        print (username)
        data['errorMessage'] = 'No User Name Found'
        response = {}
        response['statusCode'] = 400
        response['body'] =  data
        conn.close()
        return response
    for row in cur:
        print row[0]
        if int(row[0]) == 0:#NOT_AUTHORIZED
            ret = "NOT_AUTHORIZED"
        elif int(row[0]) == 1:#PENDING
             ret = "PENDING"
        elif int(row[0]) == 2:#AUTHORIZED
            ret = "AUTHORIZED"
        else:#BLOCKED
            ret = "BLOCKED"
    data['state'] = ret
    response = {}
    response['statusCode'] = 200
    response['body'] =  data
    conn.close()
    return response

The stacktrace:

Traceback (most recent call last):
  File "/var/task/app.py", line 37, in handler
  File "/var/task/pymysql/connections.py", line 851, in query
    self._execute_command(COMMAND.COM_QUERY, sql)
  File "/var/task/pymysql/connections.py", line 1067, in _execute_command
    raise err.InterfaceError("(0, '')")
InterfaceError: (0, '')

Upvotes: 2

Views: 2929

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 178956

Read Understanding Container Reuse in Lambda.

It was written about Node but is just as accurate for Python.

Your code doesn't run from the top with each invocation. Sometimes it starts with the handler.

Why? It's faster.

How do you know when this will happen? You don't... except for each time you redeploy the function, of course, you'll always get a fresh container on the first invocation, because the old containers would have been abandoned by the redeploy.

If you're going to do your DB connection outside the handler, don't call conn.close(), because on the next invocation of the function, you might find your container is still alive, and the handler is invoked with an already-closed database handle.

You have to write Lambda functions so that they neither fail if a container is reused, nor fail if a container is not reused.

The simpler solution is to open the DB connection inside the handler. The more complex but also more optimal solution (in terms of runtime) is to never close it, so that it can potentially be reused.

Upvotes: 9

Related Questions