- Newest
- Most votes
- Most comments
I spent a day trying to figure this out so hopefully this helps someone out there...
DELIMITER is a feature of the client, not the mysql server. The RDS Query Editor is a client but it does not support changing the delimiter so attempting to run a multiline script won't work since the first time it sees a semicolon it will interpret that as the end of the command and fail with a syntax error. It also does not support BEGIN and END commands used when creating multiline functions and procedures.
So, how do you create something like a stored procedure that has multiple statements and semicolons in it? You must create it as an .sql file and send it using the Data API from either a Lambda function or the CLI.
First, create your script in a .sql file without any DELIMITER commands or alternate delimiters.
For example: function.sql
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END
Then, run the script using the CLI like this:
cat function.sql | xargs -0 aws rds-data execute-statement \
--resource-arn arn:aws:rds:eu-west-1:xxx:cluster:cluster-name \
--secret-arn arn:aws:secretsmanager:eu-west-1:xxx:secret:secret-name-xxx \
--database "database_name" \
--sql
Alternatively, you can create a Lambda function that reads the file and uses rds_client.execute_statement() to send the script to the server via the Data API. But again, do NOT use the DELIMITER statement. The server sees the BEGIN and END lines and acts accordingly without the need to change the delimiter.
Relevant content
- asked 2 years ago
- Accepted Answerasked 2 years ago
- asked a month ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 7 months ago
- AWS OFFICIALUpdated 2 months ago