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
– Georg Richter, Commented Feb 5, 2023 at 18:56
Execute SHOW GRANTS instead of your SELECT and show the output. — Akina
– Akina, Commented Feb 5, 2023 at 19:03
Thank you very much. Edited questions with SHOW GRANTS and mysql version — Sourcerer
– Sourcerer, Commented Feb 5, 2023 at 19:11
SHOW VARIABLES LIKE "secure_file_priv"; can share the output of this — Ashok
– Ashok, Commented Feb 5, 2023 at 19:18
Does this answer your question? How should I resolve --secure-file-priv in MySQL? — user1191247
– user1191247, Commented Feb 5, 2023 at 19:19

Sourcerer · Accepted Answer · 2023-02-05 19:18:43Z

1

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;

answered Feb 5, 2023 at 19:18

Sourcerer

2,2081 gold badge25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot save mysql longblob into file using sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related