Estou tentando ler um arquivo csv no meu banco de dados SQL, mas não consigo fazer isso por causa de problemas de segurança com o secure_file_priv. Li muitas postagens que me disseram para editar my.cnf para permitir que uma pasta seja acessada pelo SQL. Atualmente, meu my.cnf se parece com isso:
[mysqld]
secure_file_priv = /private/var/lib/mysql-files
pid-file = /usr/local/mysql/data/mysqld.local.pid
Mas, quando eu corro
SHOW VARIABLES LIKE 'secure_file_priv';
Eu ainda recebo um valor NULL. Eu também reinstalei o mysql e reiniciei meu banco de dados muitas vezes. Enquanto minhas alterações no arquivo my.cnf são salvas, não há efeito na saída.
Também fiquei pensando se isso tinha algo a ver com não especificar um arquivo de configuração, mas quando eu especificava um caminho para meu arquivo my.cnf, meu servidor parava automaticamente depois que eu o iniciava.
EDIT: Adicionando conteúdo do log de erros aqui sudo tail -f /usr/local/mysql/data/mysqld.local.err
:
2024-09-21T20:38:24.709702Z 0 [System] [MY-015016] [Server] MySQL Server - end.
2024-09-21T20:38:29.338249Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-09-21T20:38:29.487803Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.2) starting as process 3726
2024-09-21T20:38:29.490033Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2024-09-21T20:38:29.495667Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-21T20:38:29.575501Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-21T20:38:29.700945Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-21T20:38:29.700966Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-21T20:38:29.712909Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.4.2' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
2024-09-21T20:38:29.712908Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
^C
% sudo tail -f /usr/local/mysql/data/mysqld.local.err
2024-09-21T20:38:24.709702Z 0 [System] [MY-015016] [Server] MySQL Server - end.
2024-09-21T20:38:29.338249Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-09-21T20:38:29.487803Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.2) starting as process 3726
2024-09-21T20:38:29.490033Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2024-09-21T20:38:29.495667Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-21T20:38:29.575501Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-21T20:38:29.700945Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-21T20:38:29.700966Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-21T20:38:29.712909Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.4.2' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.
2024-09-21T20:38:29.712908Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
Pelo que posso perceber, não estou vendo nenhum erro sendo registrado. Mas, quando executo o seguinte script SQL, recebo um erro.
LOAD DATA INFILE '/private/var/lib/mysql-files/table.csv'
INTO TABLE Patients
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
ERRO:
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
use aspas simples ao redor do caminho