Running your code serverless in Lambda is great: it doesn’t require resource provisioning, managing a server and you pay as you go. Is it perfect? Not quite. Among the other issues natural to any serverless implementation there is one with… drivers, of course. With traditional implementations drivers are installed across the entire system, which is no problem as they are centrally registered and referenced. With serverless deployment, however, the references are broken which inevitably leads to some noticeable pain in the neck. Something I have experienced firsthand not so long ago while trying to deploy Python Lambda with MySQL ODBC connection (using pyodbc). 

To save anyone who may be pursuing a similar task from the pain I had to endure, I’m posting a step-by-step guide on how to handle such issue here. Whether you use a framework (serverless) or manually deploy lambda this guide should be applicable. For this walk through I picked MySQL ODBC 8 driver but it can be adjusted to other DB providers supporting ODBC connection.

Walk Through:

1. for clean environment we start docker with an image that AWS Lambda uses:

·         we’ll use –rm to remove the container once we exit it (we’ll have to save our package before this happens)

·         lambda image with applicable build lambci/lambda:build-python3.7

·         setting up environment variables with -e; /var/task is a working directory for AWS Lambda

·         name the container MySQL_ODBC

docker run -it --rm --name MySQL_ODBC --entrypoint bash -e ODBCINI=/var/task -e ODBCSYSINI=/var/task lambci/lambda:build-python3.7 

2. Getting UNIX ODBC driver (that’s regardless of the DB brand we want to connect to), unpacking and installing it

curl ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz -O
tar xvzf unixODBC-2.3.5.tar.gz
cd unixODBC-2.3.5
./configure --sysconfdir=/var/task --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --prefix=/home
make install
cd ..
mv /home/* .
mv unixODBC-2.3.5 unixODBC-2.3.5.tar.gz /tmp/

3. Setting up MySQL ODBC Driver (this will change depending on what DB brand you’re trying to communicate with)

·         getting MySQL ODBC 8.0 driver from MySQL website; -L is needed to follow redirect

curl -L
https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.18-linux-glibc2.12-x86-64bit.tar.gz --output /etc/yum/mysql-connector-odbc-8.0.18-linux-glibc2.12-x86-64bit.tar.gz

·         unpacking outside of working directory

tar xvzf /etc/yum/mysql-connector-odbc-8.0.18-linux-glibc2.12-x86-64bit.tar.gz -C /etc/yum/

·         copying content of lib dir to lib dir in our working directory

cp -r /etc/yum/mysql-connector-odbc-8.0.18-linux-glibc2.12-x86-64bit/lib/. ./lib/

4. Before we can install PYODBC library for our python script we need to install the developer version of UNIX ODBC driver in the docker container

yum -y install unixODBC-devel

5. Installing PYODBC via pip

pip install pyodbc -t .

6. Now we need to create the config files (change values according to DB brand you’re trying to connect)

cat <<EOF > odbcinst.ini
[MySQL]
Description=MySQL ODBC Driver 8
Driver=/var/task/lib/libmyodbc8w.so
UsageCount=1
EOF
 
cat <<EOF > odbc.ini
[MySQL]
Driver = MySQL
Description = MySQL ODBC Driver 8
Trace = No
EOF

 

7. Let’s test it! You should see “Connected!!!” in return

Cat <<EOF > function.py
import pyodbc
driver = '{MySQL}'
server = '[SERVER ENDPOINT]'
database = '[DATABASE NAME]'
port = 3306
user = '[DATABASE USERNAME]'
password = '[DATABASE PASSWORD]'
def test_pyodbc():
   print(pyodbc.drivers())
   print('Attempting Connection...')
   conn = pyodbc.connect(f"DRIVER={{MySQL}};SERVER={server};DATABASE={database};UID={user};PWD={password}")
   print('Connected!!!')
if __name__ == "__main__":
   test_pyodbc()
EOF
 
python function.py

8. Finally, we need to package our necessary files, which happen to all be in the working directory

·         using -r9 to compress recursively with max compression

·         saving package to a parent folder (/var)

zip -r9 ../pyodbc_package.zip .

9. From outside of the docker run the following to copy our resulting zip to local machine

·         put destination path instead of [DEST] or . to copy package to current directory on local machine

docker cp MySQL_ODBC:/var/pyodbc_package.zip /[DEST]/

10. Now you can exit from MySQL_ODBC container and check it is no longer showing up running

·         run from within the container

exit

·         check the status of the container by running following from local machine

docker ps -a 

Add the files from pyodbc_package.zip to the folder with your application to have it added to the deployment package. In case you use a framework (e.g. serverless) do not add PYODBC library to requirements to avoid additional installation (which will cause failure upon deployment)