Im encountering syntax error 1064 on my MySql stored procedure

Issue

Hi rephrasing my question earlier I added the full statement below.codes should retain data from past 7 days.Thank you.

CREATE PROCEDURE logs_user_aht_delete(
IN logs_database VARCHAR(25) CHARACTER SET utf,

IN logs_data_retention_db INT(10) )

BEGIN

SET @v = concat(‘DELETE FROM ',logs_database,'.user_aht ua WHERE
IFNULL(ua.completed_at, ua.inserted_at) < (CURRENT_DATE – 7)- INTERVAL ‘,logs_data_retention_db ,’ DAY);’);

PREPARE stm FROM @v;

EXECUTE stm;

DEALLOCATE PREPARE stm;

END$$

Solution

To debug this select @v in the procedure commenting out the prepare,exec,deallocate and examine the result.

Two thing are syntactically incorrect 1) the table alias where you are part way to a multi-table delete 2) you are providing the days to be deleted as a parameter and also hard coding it in the build of @v

I suggest you change to

SET @v = concat('DELETE ua FROM ',logs_database,'.user_aht ua
WHERE IFNULL(ua.completed_at, ua.inserted_at) < (CURRENT_DATE - INTERVAL ',logs_data_retention_db ,' DAY);');

Answered By – P.Salmon

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published