creating a MySQL procedure using a Lambda function in Python

Question

I have a lambda function that runs SQL scripts using Python which works fine until I try to run scripts that create stored procedures. Does anyone know what the issue might be here please? MySQL(5.7), Python(3.9). I've tried various things but nothings working so far. Does anyone have a code example that shows this working please?

Python code

import boto3
import os
import sys
import mysql.connector
import logging

logger = logging.getLogger()
logger.setLevel(logging.INFO)

v_rds_host  = os.environ['rds_endpoint']
v_name = os.environ['rds_user']
v_port = os.environ['rds_port']
v_db_name = "mysql"
v_region = os.environ['aws_region']
v_sql_file = os.environ['sql_file']

file = open('user_create_proc.sql','r')
sql = file.read()
 
logger = logging.getLogger()
logger.setLevel(logging.INFO)
logging.info('Generate database token...')

rds_client = boto3.client('rds')
database_token = rds_client.generate_db_auth_token(
    DBHostname=v_rds_host,
    Port=v_port,
    DBUsername=v_name,
    Region=v_region
)

logging.info('Token successfully obtained. Connecting to database...')

database_connection = mysql.connector.connect(
    host=v_rds_host,
    port=v_port,
    database=v_db_name,
    user=v_name,
    password=database_token
)

logging.info('Connected!!')

cursor = database_connection.cursor(dictionary=True)
try:
 def lambda_handler(event, context):
  for result in cursor.execute(sql, multi=True):
   if result.with_rows:
    print("Rows produced by statement '{}':".format(
       result.statement))
    print(result.fetchall())
   else:
     print("Number of rows affected by statement '{}': {}".format(
       result.statement, result.rowcount))
except:
  cnx.close()**SQL script content**

SQL script

use dba;
DROP PROCEDURE IF EXISTS proc_test;
delimiter //
CREATE PROCEDURE proc_test(in v_val VARCHAR(32))
BEGIN
 select concat(v_val) as test_output;
END//

Logs

Response
{
  "errorMessage": "1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter //\nCREATE PROCEDURE proc_test(in v_val VARCHAR(32))\nBEGIN\n select conc' at line 1",
  "errorType": "ProgrammingError",
  "requestId": "fa185e08-56f8-4e6b-8cfe-08f90f3bd1a0",
  "stackTrace": [
    "  File \"/var/task/run_sql.py\", line 47, in lambda_handler\n    for result in cursor.execute(sql, multi=True):\n",
    "  File \"/opt/python/mysql/connector/cursor.py\", line 501, in _execute_iter\n    result = next(query_iter)\n",
    "  File \"/opt/python/mysql/connector/connection.py\", line 972, in cmd_query_iter\n    yield self._handle_result(self._socket.recv())\n",
    "  File \"/opt/python/mysql/connector/connection.py\", line 730, in _handle_result\n    raise errors.get_exception(packet)\n"
  ]
}

Function Logs
START RequestId: fa185e08-56f8-4e6b-8cfe-08f90f3bd1a0 Version: $LATEST
[INFO]  2022-05-13T10:07:26.253Z        Generate database token...
[INFO]  2022-05-13T10:07:26.267Z        Found credentials in environment variables.
[INFO]  2022-05-13T10:07:26.447Z        Token successfully obtained. Connecting to database...
[INFO]  2022-05-13T10:07:26.615Z        Connected!!
Number of rows affected by statement 'use dba': 0
Number of rows affected by statement 'DROP PROCEDURE IF EXISTS proc_test': 0
[ERROR] ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter //
CREATE PROCEDURE proc_test(in v_val VARCHAR(32))
BEGIN
 select conc' at line 1
Traceback (most recent call last):
  File "/var/task/run_sql.py", line 47, in lambda_handler
    for result in cursor.execute(sql, multi=True):
  File "/opt/python/mysql/connector/cursor.py", line 501, in _execute_iter
    result = next(query_iter)
  File "/opt/python/mysql/connector/connection.py", line 972, in cmd_query_iter
    yield self._handle_result(self._socket.recv())
  File "/opt/python/mysql/connector/connection.py", line 730, in _handle_result
    raise errors.get_exception(packet)END RequestId: fa185e08-56f8-4e6b-8cfe-08f90f3bd1a0
REPORT RequestId: fa185e08-56f8-4e6b-8cfe-08f90f3bd1a0  Duration: 22.80 ms  Billed Duration: 23 ms  Memory Size: 128 MB Max Memory Used: 70 MB  Init Duration: 774.06 ms

Request ID
fa185e08-56f8-4e6b-8cfe-08f90f3bd1a0

Answer 1

Ignore me, being daft the delimiters aren't relevant here so this works for the SQL file.

use dba;
DROP PROCEDURE IF EXISTS proc_test;

CREATE PROCEDURE proc_test(in v_val VARCHAR(32))
BEGIN
 select concat(v_val) as test_output;
END