[FIXED] Syntax error when using Prepare Statement MySQL

Issue

I am trying to make a trigger which will create a File with data from the DB and the file would have a different name every time. I'm trying to put the DateTime but a simple increment would be better although, I don't know how since we can't use any variables. It'd be better in the case where two files are created at the same time. The problem with my code is that I get an error when I'm using the Prepare/Execute statements.

I get 'Syntax error near PREPARE s1...' What's wrong?

SET @my_var = 
    CONCAT (
       "SELECT * FROM table_name INTO OUTFILE 'file_path/log-"
       , DATE_FORMAT( NOW(), '%Y%m%d')
       , ".txt'"
    );
PREPARE s1 FROM @my_var;
EXECUTE s1;
DEALLOCATE PREPARE s1;

I'm using MySQL 5.7.26 and PhpMyAdmin.

Solution

I found that it is not possible to do here.

The Current MySQL (5.7) Manual section D.1 Restrictions on Stored Programs states that

• SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).
• Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements .
• Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope.

So you can see that it's not permitted. If someone has an alternative, please comment.

Answered By - Michaeldc