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!
2 Answers
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.
-
Great research! The key point seems to be what tool you use to create the ZIP archive. 7-Zip sets access for the user that creates the archive but Windows Zip Folders sets it to
NT AUTHORITY\SYSTEM. Commented Feb 13, 2021 at 12:56 -
@IDK The problem seems to be solved, but from my own experience I would advise against storing large amounts of file content in the database as the performance, especially of MySQL InnoDB tables, decreases significantly as the table size increases. Instead, I recommend storing the files in a file system and only storing the relative path in the database, possibly with some metadata such as size and file time. Commented Feb 14, 2021 at 15:07
-
This is strange... your
@@secure_file_privilegeis set to empty string and allows file operations with any folder, and at the same time the account which is used for MySQL service execution may have restricted privileges in the filesystem? This looks like incorrect/illogical MySQL and OS settings.– AkinaCommented Feb 15, 2021 at 18:20 -
1@Akina There is nothing special in my MySQL / OS configuration. The problematic part is the behavior of the Explorers zip functionality which sets too restrictive access level for the archives created. Files created by (all) other archivers / applications are accessible to the MySQL service, regardless of the
secure_file_privilegesetting. Commented Feb 15, 2021 at 20:42
I cannot reproduce the problem. See console output:
mysql> CREATE TABLE test (val BLOB);
Query OK, 0 rows affected (0.29 sec)
mysql> INSERT INTO test SELECT LOAD_FILE('C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\test.sql');
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test SELECT LOAD_FILE('C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\test.zip');
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT LENGTH(val) FROM test;
+-------------+
| LENGTH(val) |
+-------------+
| 5603 |
| 17725 |
+-------------+
2 rows in set (0.00 sec)
INSERTand tell us what application is doing the work (PHP? Java? VB? other?)secure_file_privilegeandmax_allowed_packetvariables, the size of the file to be loaded, and ensure that your account have FILE privilege.