Audit Trigger, Insert Blob As LongText

Question

I'm trying to create an audit trigger, inserting blob data as longtext datatype.

When I use select I see the content of the image, but when I insert using the same format it shows up as blank.

Works: data display the raw content of the image

select cast(my_image as char) as data from MyTable

Doesn't Work: data column appears blank (0byte)

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

Update: Sep. 12 I tried to put it through a function hoping by explicitly specify the type then it will take, but no luck there either

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

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

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

Right now the only thing that give me some hope is Hex(blob) but when I UNHEX() the data is not readable.

Answer 1

You may try the following:

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

If your SELECT query works correctly, this solution should work.

Hope this will help.