无法使用SQL将MySQL的longblob保存到文件。

huangapple go评论51阅读模式
英文:

Cannot save mysql longblob into file using sql

问题

I'll provide the translated code and relevant information:

首先,我要求您的耐心,因为这应该是一个非常简单的问题,但我无法找到它。

我已经创建了一个用于存储电子邮件的简单数据库。为此数据库,我创建了一个具有FILE特权的简单用户。

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'@'%';

在这个数据库中,我有一个名为Email的表,其中包含一个LONGBLOB字段。

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;

现在,我尝试获取内容(内容已正确加载,我能够通过代码获取它)。我尝试以下SQL语句。

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

但我总是收到以下错误:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

我已经尝试重新启动MySQL服务器,使用以下命令:sudo systemctl restart mysql

我正在运行Ubuntu 22.04上的MySQL 8.0.32。

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

注意:请注意,由于您不希望进行翻译以外的回答或解释,我仅提供了代码的翻译版本。如果您需要任何进一步的帮助或解释,请随时提出。

英文:

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))

答案1

得分: 1

感谢您的评论。找到了

首先,使用以下命令检查 secure-file 目录位置:

SHOW VARIABLES LIKE "secure_file_priv";

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

然后将输出修改为此目录:

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

英文:

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;

huangapple
  • 本文由 发表于 2023年2月6日 02:45:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75354680.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定