AWS Lambda RDS MySQL DB Connection InterfaceError

Question

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, '')

Michael - sqlbot · Accepted Answer · 2017-04-12 04:12:07Z

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.

Thanks @Michael. Moved the open DB connection inside handler and it works fine now.

Collectives™ on Stack Overflow

AWS Lambda RDS MySQL DB Connection InterfaceError

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related