Serverless Computing: MySQL as Function Trigger (Preparation)

In the previous blog an AWS Lambda function is invoked through the API Gateway by a client. How do I invoke (trigger) the same AWS Lambda function by a relational database? This blog sets up MySQL first as preparation, including a database trigger.

Create MySQL RDS Instance

I followed CHAP_GettingStarted.CreatingConnecting.MySQL.html to create an RDS (Relational Database Service) MySQL instance.

In order to connect to it from the MySQL Workbench you need to

• find the connectivity information on the details page. Look for the “Connect” tile to find the connection endpoint and port
• use the master username and password and try to connect. However, the connection must fail as no security group is created yet that allows access (I am correctly getting MySQL error 10060)
• create a second security group and create a rule with custom TCP, port 3306 (in my case) and the IP address from which you are accessing the database (the AWS UI determines that automatically). The rule has to be assigned to the database instance via modification for it to take effect
• alternatively you could create a rule that allows access by all traffic from anywhere. However, I prefer a more restrictive rule, even though I might have to update it when the IP address changes in the system from where I am accessing the MySQL instance

Now logging in from the MySQL Workbench on your laptop using the master user name and master user password is possible – that is going to make development for MySQL easier.

Create Aurora MySQL Instance

Turns out, creating a MySQL RDS Instance was a wasted effort for the goal that I set myself. It is not possible to call a AWS Lamda function from an MySQL RDS instance. Bummer. However, it is possible from Aurora MySQL.

So, I started over and created an Aurora MySQL. Once I had Aurora MySQL setup and running, I could continue with the MySQL user, table, trigger and function definition specification inside Aurora MySQL. I’ll refer to Aurora MySQL as MySQL for short in the following.

Setup MySQL User

For development I setup a user in the database instance as follows:

• CREATE USER ‘cbmysqluser’@’%’ IDENTIFIED BY ‘cbmysqluser’;
• GRANT CREATE ON fib.* TO ‘cbmysqluser’@’%’;

Additional commands are necessary during development to provide additional permissions. Here is the collection I ended up with over time. You might not have to use every single one of those, but anyway. Here is the list of grants

• GRANT DROP ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT SELECT ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT INSERT ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT TRIGGER ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT CREATE ROUTINE ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT ALTER ROUTINE ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT EXECUTE ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT UPDATE ON fib.* TO ‘cbmysqluser’@’%’;
• GRANT DELETE ON fib.* TO ‘cbmysqluser’@’%’;

Here some revocations

• REVOKE CREATE ON *.* FROM ‘cbmysqluser’@’%’;
• REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘cbmysqluser’@’%’;

And some management commands

• SHOW GRANTS FOR ‘cbmysqluser’@’%’;
• FLUSH PRIVILEGES;

Create MySQL Table

Having the user setup, now a table can be created by that user.

The idea is to use the table as the invocation interface. A value is inserted into a column (representing a function parameter), and after the function is executed the result is stored in another column into the same row. Each row therefore has the function parameter value as well as the corresponding return value.

In my case the function is the Fibonacci function. For example, calling it with 0 will return 0. Calling it with 10 will return with 55.

| fib | value |
+-----+-------+
|   0 |     0 |
|  10 |    55 |

Here the table creation statement

CREATE TABLE fibonacci (
  fib INT,
  value INT
);

Create MySQL Trigger

Now moving on to the trigger. I split the trigger into the trigger itself, and a separate function. The rigger, when fired, passes the input parameter to the function and stores the value computed by the function into the table. The trigger is specified as follows

DELIMITER $$
CREATE TRIGGER fibcomp 
  BEFORE INSERT ON fibonacci
  FOR EACH ROW
    BEGIN
      SET NEW.value = fibcomp(NEW.fib);
    END$$
  DELIMITER ;

The function computes the Fibonacci number as follows

DELIMITER $$
CREATE FUNCTION fibcomp (fib INT) 
  RETURNS INT 
BEGIN
  DECLARE f1 INT DEFAULT 0;
  DECLARE f2 INT DEFAULT 1;
  DECLARE sum INT;
  DECLARE i INT DEFAULT 2; 
  
  IF fib <= 0 THEN RETURN f1;
  ELSE
    WHILE i <= fib DO
      SET i = i + 1;
      SET sum = f1 + f2;
      SET f1 = f2;
      SET f2 = sum;
    END WHILE;
    RETURN f2;
  END IF;
END $$
DELIMITER ;

When issuing the following two insert statements, the above table content is the result

INSERT INTO fibonacci VALUES (0, null);
INSERT INTO fibonacci VALUES (10, null);

At this point I have the whole infrastructure available in context of MySQL and the functionality in place. This is the basis for incorporating the AWS Lambda implementation of the function implementing the Fibonacci computation next.

Summary

Once it was clear that Aurora MySQL is required, the setup of a database user, table, trigger and function was easy as this is done within MySQL not using any additional AWS infrastructure. With the ability to connect to the database instance using MySQL Workbench the development environment familiar to me was available, and that’s great.

Go Serverless!

Disclaimer

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

Advertisement