Cannot save mysql longblob into file using sql

Question

First of all, I ask for your patience, because has to be a very simple question, but I'm not able to find it.

I have created a simple database to store emails. For this database I have created a simple user with FILE privilege.

CREATE USER 'email'@'%' IDENTIFIED BY 'mypassword';
CREATE DATABASE email CHARACTER SET 'UTF8MB4'; 
GRANT ALL PRIVILEGES ON email.* TO 'email'@'%';
GRANT FILE ON *.* TO 'email'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'email'@'%';

In this database, I have an Email table, with a LONGBLOB field

CREATE TABLE `Email` (
    `Id` char(36) COLLATE ascii_general_ci NOT NULL,
    `RawContent` longblob NULL
    CONSTRAINT `PK_Email` PRIMARY KEY (`Id`)
) CHARACTER SET=utf8mb4 ROW_FORMAT=COMPRESSED;

And now, I try to get the content (the content is correctly loaded, and I'm able to get it by code). I try the following SQL

SELECT 
    Email.RawContent INTO DUMPFILE '/tmp/test.eml'
FROM
    Email;

But I get always the error

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> SHOW GRANTS;
+----------------------------------------------------------+
| Grants for email@localhost                               |
+----------------------------------------------------------+
| GRANT FILE ON *.* TO `email`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `email`.* TO `email`@`localhost` |
+----------------------------------------------------------+
2 rows in set (0,00 sec)

I have already test restarting the mysql server with sudo systemctl restart mysql

I'm running mysql 8.0.32 on Ubuntu 22.04.

mysql --version
mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

Please provide the version of MySQL server and result/output for SHOW GRANTS after user email connected to MySQL. — Georg Richter, Feb 05 '23 at 18:56
Execute SHOW GRANTS instead of your SELECT and show the output. — Akina, Feb 05 '23 at 19:03
Thank you very much. Edited questions with SHOW GRANTS and mysql version — Sourcerer, Feb 05 '23 at 19:11
SHOW VARIABLES LIKE "secure_file_priv"; can share the output of this — Ashok, Feb 05 '23 at 19:18
Does this answer your question? [How should I resolve --secure-file-priv in MySQL?](https://stackoverflow.com/questions/32737478/how-should-i-resolve-secure-file-priv-in-mysql) — user1191247, Feb 05 '23 at 19:19

score 1 · Answer 1 · answered Feb 05 '23 at 19:18

Thanks for the comments. Found

First, checked the secure-file location with

SHOW VARIABLES LIKE "secure_file_priv";

'secure_file_priv', '/var/lib/mysql-files/'

Modified output to this directory

SELECT 
    Email.RawContent INTO DUMPFILE '/var/lib/mysql-files/test.eml'
FROM
    Email;

Cannot save mysql longblob into file using sql

1 Answers1