Is it possible to invoke or trigger an AWS lambda through remote MYSQL database?

Question

While Inserting a data in remote MYSQL database table (hosted in local machine) it should also get inserted in RDS MYSQL database table through lambda:

The Requirement I have:

1. I have a local database in my PC.
2. I access it through Dbeaver by connection to it using remote access.
3. Now I've created a RDS MYSQL database(with the same schema of my local database)in AWS.

4. Where I have lambda as a bridge to connect these two databases.

   What I tried:

   • I tried the usual way we use to connect RDS to RDS.
   • Where I created a trigger, then a stored procedure with LAMBDA_ARN in my local database to call the lambda once a data is inserted in the local table.
   • Where the lambda will insert the same data in the RDS MYSQL table.

For Example, what I tried was:

I've a table in my local database like this:

And I've created a database in RDS MYSQL with the same schema.

I've added a Trigger in my local database like this:

 CREATE TRIGGER local_to_rds AFTER INSERT ON mark2 FOR EACH ROW
BEGIN
  SELECT  NEW.sno, QUOTE(NEW.name), IFNULL(NEW.geoloc,0), NEW.ranging INTO @sno, @name, @geoloc, @ranging;
  CALL  local_to_rds_Procedure(@sno, @name, @geoloc, @ranging);
end

A procedure in my local database like this:

CREATE PROCEDURE local_to_rds_procedure (IN sno int, IN name varchar(50), IN geoloc int, IN ranging int) LANGUAGE sql begin
CALL mysql.lambda_async('arn:aws:lambda:us-west-2:919585521528:function:local_to_rds',
CONCAT('{ "sno" : "', sno,
'", "name" : "', name,
'", "geoloc" : "', geoloc,
'", "ranging" : "', ranging,'"}'));
END

Now I've created a lambda for connecting this local database to the RDS database, also for some other purpose like notifying me if I get any error, creating log table automatically on every inserting (or) update (or) delete in my table, etc... Which I don't have any problem.

The Issue I face here is with the connection, after creating the Trigger and procedure for invoking the lambda I get SQL Error [1305] [42000]: PROCEDURE my.sql.lambda_async does not exist

I tried browsing for solution but I couldn't find a proper solution for it.

This is the view for the process I'm trying:

LOCAL_DATABASE --> LAMBDA --> AWS_RDS

The part I'm stuck is invoking the lambda while inserting a data in the local database table... Also I've no idea what kind of errors I'll be facing if I pass through this.

Thanks in advance...

Answer 1

Where are you seeing that lambda_async should be available in your local MySQL server? I'm only seeing that feature as available in the MySQL version of AWS Aurora.

For what you are trying to do, you need to expose the Lambda function to the world via API Gateway, and then make an HTTPS call to that API Gateway endpoint from your MySQL procedure.