3

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;.

Bobin
  • 278
  • 5
  • 15
  • Can you show us your table structure? – Jalil May 07 '19 at 11:44
  • 1
    Do you want to import a file from the server running MySQL? Which is the "local disk"? – Nico Haase May 07 '19 at 11:46
  • Possible duplicate of [MySQL LOAD\_FILE() loads null values](https://stackoverflow.com/questions/18069054/mysql-load-file-loads-null-values) – Mike Doe May 07 '19 at 11:47
  • The file is on my physical disk, the server running MySQL is in the Amazon cloud – Bobin May 07 '19 at 11:59
  • So you thought that if you tell MySQL, which is AWS Aurora, to load file from `C:/testing.7z` that it'll somehow magically access your disk? You do realize that file paths are relative to the server where query executes at? [Here's the documentation](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_load-file) and here's the excerpt: *"To use this function, the file must be located on the server host"* – Mjh May 07 '19 at 12:10
  • Ohhh... I see. Thanks for your comment. But how is it possible to upload a local file? – Bobin May 07 '19 at 12:25

3 Answers3

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();
Tasos P.
  • 3,994
  • 2
  • 21
  • 41
  • 1
    If the current value of `secure_file_priv` is `/tmp`, then it will be difficult to even create the directory you've mentioned – Nico Haase May 07 '19 at 11:47
  • I cannot change the secure_file_priv. – Bobin May 07 '19 at 11:47
  • You are both right, I updated my answer. Apologies for not paying attention to AWS RDS... – Tasos P. May 07 '19 at 11:51
  • I just tried JDBC with the same result. (Actually I first tried it with jdbc and then with MySQL) – Bobin May 07 '19 at 11:56
  • You should use `setBinaryStream` in a `PreparedStatement` and then `executeUpdate`. Please refer to this [full example](http://www.mysqltutorial.org/mysql-jdbc-blob) – Tasos P. May 07 '19 at 13:32
0

You're inserting NULL because file has to be relative to server host, not the computer where the query was issued at.

Queries are text, they aren't functions so you can't execute a query that reads a file from your computer and transfers it to the server.

You can read the file using one of languages you're familiar with and transfer the contents of the file.

Documentation for LOAD_FILE: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_load-file

Important excerpt:

To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege.

Mjh
  • 2,904
  • 1
  • 17
  • 16
0

You could accomplish the equivalent by loading the blob into an S3 bucket and store the reference location of the s3 file in your table.

David Pinhasik
  • 1
  • 1