Insert blob into MySQL database only inserts NULL

Question

I try to insert a blob from local disk (Windows 10) to my RDS MySQL database. However my query does not insert the blob. The new row has all other columns, but the blob is NULL.

INSERT INTO table VALUES('c1', LOAD_FILE('C:/testing.7z'));

Here is what I tried:

• I set the permission of the file ro read/write.
• secure_file_priv is set to /tmp ... I dont know what this means. Is this a path or a variable? If it is a variable, I already tried to upload the blob from the paths from user/system variable "TMP" with no success. I am not able to change secure_file_priv because RDS permits changes... If it is a path then from which path do I have to upload the file?
• max_allowed_packet is set to default (1024-1073741824 bytes). My file is 200 kb large.

How can I manage to upload the file?

EDIT: I tried the same on my local database and it worked - but only with files in the directory of select @@secure_file_priv;.

Answer 1

According to the documentation, the file needs to be located in the directory specified in secure_file_priv. So, I suggest you create a directory c:/mysql-uploads, update secure_file_priv to point there and place the file in that directory. I quote the docs:

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

UPDATE

The above solution only applies to when the file is located on the database host. Since you use AWS RDS, you don't have access to the local file system, so you can't copy files to the database host no matter what value is set for secure_file_priv. I'm afraid you'll have to use a client app which will insert the content/blob using client side methods (i.e. JDBC).

You can accomplish this by creating a Connection and a PreparedStatement, use the setBinaryStream to load the file stream into the blob and then call executeUpdate. Full example:

Connection conn = DriverManager.getConnection(url,username,password);
String updateSQL = "INSERT INTO table VALUES(?,?)"
PreparedStatement pstmt = conn.prepareStatement(updateSQL);
File file = new File(filename);
FileInputStream input = new FileInputStream(file);
pstmt.setString(1, "c1");
pstmt.setBinaryStream(2, input);
pstmt.executeUpdate();