英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论