Audit trigger, insert blob as longText

Question

I am trying to create an audit trigger by inserting BLOB data as longtatatype.

When I use select, I see the contents of the image, but when I paste it in the same format, it appears as empty.

Work : data displays the original content of the image

select cast(my_image as char) as data from MyTable 

Does not work : the data column is displayed empty (0 bytes)

 insert into MyAuditTable (data) values ( cast(NEW.my_image as char) ) 

Update: September 12th I tried to execute the function, hoping that I would explicitly indicate the type that it would accept, but no luck there too

 CREATE FUNCTION `BLOB2TXT`(dablob LONGBLOB) RETURNS LONGTEXT NO SQL DETERMINISTIC BEGIN RETURN CAST(dablob AS CHAR CHARACTER SET utf8); END 

I posted my project on github if someone wants to take a look or want to use what I have. You are always welcome :)

https://github.com/hotmit/mysql-sp-audit

Currently, the only thing that gives me some hope is Hex (blob), but when I unhex () the data is not readable.

Answer 1

You can try the following:

 INSERT INTO zaudit_meta(audit_id, col_name) SELECT 0 ,CAST(my_image AS CHAR) 

If your SELECT query is working correctly, this solution should work.

Hope this helps.