Serverless Computing: AWS Lambda Function Invocation from Aurora MySQL – Second Part of a Journey

Today the journey of invoking a AWS Lambda function and storing its result into a table is completed. Some more issues had to be overcome.

Recap

The previous blog left off with the realization that in MySQL 5.6 there is no native facility to parse JSON objects as returned from the AWS Lambda invocation.

Recursive Descent Parser

To keep things simple, I implemented a recursive descent parser in MySQL (not 100% complete, only the part needed for this blog – still missing functionality to be implemented later as needed). The parser also extracts a property being sought for at the same time.

The signature is as follows:

CREATE PROCEDURE json_extract_property
  (IN  json_document TEXT,
   IN property_name VARCHAR(32),
   OUT property_value VARCHAR(32),
   OUT property_type VARCHAR(6))

Fundamentally, given a JSON document and a property name, return the property value and JSON type if the property exists. Otherwise the value is SQL NULL; this allows for testing if a value was found or not.

Any error encountered is raised as a SQL exception according to the MySQL approach. For example, if an incorrect character is found in the JSON document, the following exception is raised:

SET message = CONCAT('Not a JSON document or JSON array: found incorrect "', 
                     current_char, '" at position: ', next_char_index - 1);
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = message,
MYSQL_ERRNO = 1001;

Current_char contains the character in question at this point of parser execution, and next_char_index points at the next character to be examined.

MySQL Table for Fibonacci

The functionality is as follows. The client inserts a number (into a table) for which the Fibonacci number should be computed. The table is defined as follows:

CREATE TABLE fib.fibonacci (
    fib INT,
    value INT,
    code CHAR(5),
    msg TEXT
);

The table has four columns, one holding the number for which the Fibonacci number should be computed for, one holds the Fibonacci number, and two columns for error handling – one holding the error code, and one the error message.

The client inserts a row as follows:

INSERT INTO fib.fibonacci VALUES (5, null, null, null);

This then causes a trigger to be executed that computes the Fibonacci number via AWS Lambda invocation and inserts the resulting value (or error message). The trigger is explained next.

MySQL Trigger Computing Fibonacci

The trigger is defined as follows:

DELIMITER $$
CREATE TRIGGER fibcomp BEFORE INSERT
ON fibonacci
FOR EACH ROW
BEGIN
DECLARE function_result_message TEXT;
DECLARE output_value_as_string VARCHAR(32);
DECLARE property_type VARCHAR(6);
DECLARE code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
        GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
        SET NEW.code = code;
        SET NEW.msg = msg;
        END;

SET function_result_message = lambda_sync('arn:aws:lambda:us-west-2:<acct>:function:fibonacci',
                                          CONCAT('{"fib": ', NEW.fib, '}'));
CALL json_extract_property(function_result_message, 'output', output_value_as_string, property_type);
IF (output_value_as_string IS NULL)
THEN
    SET NEW.code = "-1";
    SET NEW.msg = 'No output value received';
ELSE
    SET NEW.value = output_value_as_string;
END IF;
END$$
DELIMITER ;

The trigger invokes the recursive descent parser. If a result is found, it is added to the row the user is inserting. If no value is found, code and error message are set. If there is any error being raised, this is recorded in the row as well.

After the above insert, the result is as follows:

| fib | value | code | msg  |
+-----+-------+------+------+
| 5   | 5     | null | null |

Issues Encountered

This second part of the journey was not without issues, either. Here a short summary:

The AWS RDS web user interface sometimes does not work. When I made a change to my MySQL instance, the change was not recognized by the UI and I could not apply the change.

The resolution to this is using the REST endpoints in order to modify the MySQL instance.

A bigger “issue” is that the function “lambda_sync” provided by AWS is formatting (pretty printing) the resulting JSON document before returning it adding ‘0A’ for pretty printing. It took me a while to realize that.

The resolution to this problem was to add the following to the parser as a first execution:

SET normalized_json_document = REPLACE(REPLACE(json_document, '\r', ''), '\n', '');

This ensures that any formatting characters added by lambda_sync are removed before parsing starts.

Note on Engineering with MySQL

In order to be able to efficiently work while implementing the recursive descent parser I installed MySQL locally not using the hosted version. This was very convenient and a route to consider.

Summary

In summary, I was able to implement the AWS Lambda function in such a way that a single function can be invoked from MySQL as well as via the AWS Gateway providing the same result structure.

In principle, the trigger mechanism allows the invocation of functions as well as their result processing – even though there is no support infrastructure from that provided by AWS. I assume this will get addressed over time by the appropriate upgrades of MySQL as well as making the lambda_sync function available.

Go Serverless!

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

 

Advertisement