MySQL load_file() for .zip folders

Question

I have a couple files that I want to store together in a blob type column of a mysql table. So I just put them into a folder and then zipped it. I've never had any trouble storing images, text and pdf files using the load_file() function, but when I try with the .zip folder I get back a NULL value. What am I missing? Thanks!

Answer 1

I have noted the same phenomenon.

It does seem a bit strange indeed and OS related. Here is the result of my investigation (using MARIA DB 10.4, Windows 10 Pro20H2):

In a given folder, C:\zipfolder for ex., I've created a textfile zipdoc.txt with some text content and a zip file containing the textfile.

This gives the folowing load_file output:

select load_file('C:\\zipfolder\\zipdoc.txt');
+----------------------------------------+
| load_file('C:\\zipfolder\\zipdoc.txt') |
+----------------------------------------+
| zipcontent text                        |
+----------------------------------------+

select load_file('C:\\zipfolder\\zipdoc.zip');
+----------------------------------------+
| load_file('C:\\zipfolder\\zipdoc.zip') |
+----------------------------------------+
| NULL                                   |
+----------------------------------------+

Changing the file extension from .zip to .zip_ for ex. fixes the issue:

select load_file('C:\\zipfolder\\zipdoc.zip_');
+---------------------------------------------------------------------------------------------------------------------------------------+
| load_file('C:\\zipfolder\\zipdoc.zip_')                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------+
| PK♥♦¶     FÄLR├SAÏ☼   ☼
   zipdoc.txtzipcontent textPK☺☻¶ ¶     FÄLR├SAÏ☼   ☼
       ☺         zipdoc.txtPK♣♠    ☺ ☺ 8   7      |
+---------------------------------------------------------------------------------------------------------------------------------------+

So, it looks like Windows 10 is blocking the access to .zip files in a more restrictive way than other files.

Giving EVERYONE access to the zip-file allows the load_file function accessing of the original zip-file. After granting the access with the following Powerhell script (adopted from here):

$acl = Get-Acl C:\zipfolder\zipdoc.zip
$AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule("Jeder","Read","Allow")
$acl.SetAccessRule($AccessRule)
$acl | Set-Acl C:\zipfolder\zipdoc.zip

load_file is able to access the zipfile:

select load_file('C:\\zipfolder\\zipdoc.zip');
+---------------------------------------------------------------------------------------------------------------------------------------+
| load_file('C:\\zipfolder\\zipdoc.zip')                                                                                                |
+---------------------------------------------------------------------------------------------------------------------------------------+
| PK♥♦¶     FÄLR├SAÏ☼   ☼
   zipdoc.txtzipcontent textPK☺☻¶ ¶     FÄLR├SAÏ☼   ☼
       ☺         zipdoc.txtPK♣♠    ☺ ☺ 8   7      |
+---------------------------------------------------------------------------------------------------------------------------------------+

So, the solution is to grant EVERYONE access to the zip-files or just changing the extension of the files (It remains a task for admins to find a more restrictive working access level).

Complement: As mentioned by @Álvaro González, the use of an archiving program that sets the appropriate rights is also a solution.