MySQL Server Blog

Quero destacar um comando SQL MySQL menos conhecido, analisar como funciona e compreender algumas das decisões que precisam de ser tomadas ao utilizá-lo.

P>Vamos começar com o básico: O Manual de Referência.

Tem muito a dizer sobre CARREGAR DADOS. Mas vamos concentrar-nos no INFICIÁRIO DE CARGA DE DADOS. Em poucas palavras, tem esta sintaxe:

LOAD DATA
br>> INFILE 'file_name'
> br> INTO TABLE tbl_name
...)]
>

ENCLOSED BY 'char']

]


]

...)]
...]

OK, portanto, este é um comando para carregar os dados num ficheiro de texto para uma tabela.

P>Damos-lhe uma tentativa.

P>Damos-lhe a seguinte definição de tabela:
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT);

E o seguinte ficheiro de dados (dados.txt):

1,1
2,2
3,3
4,4

mas … Hmm.

Então este é um comando SQL executado num computador servidor por um computador cliente. Eu sei como criar a tabela. Mas onde ponho o meu ficheiro de dados ?

Voltar ao manual de referência:

Se LOCAL não estiver especificado, o ficheiro deve estar localizado no anfitrião do servidor e é lido directamente pelo servidor.

OK, por isso preciso de colocar data.txt no meu servidor. Vamos tentar:

mysql> create table t1 (a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected (0.05 sec)

mysql> select @@datadir;
+--------------------------------------------+br>| @@datadir |
+--------------------------------------------+
| /home/gkodinov/work/mysql-8.0/bld/datadir/ |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> exit;
>Bye

~/work/mysql-8.0/bld/mysql-test: mkdir /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/

~/work/mysql-8.0/bld/mysql-test: cat > /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/data.txt
1,1
2,2
3,3
4,4
~/work/mysql-8.0/bld/mysql-test: mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-debug Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or itsbr>affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;br>>Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> load data infile 'data/data.txt' into table t1 fields terminated by ',';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Hmm !

mysql> select @@secure_file_priv;
+--------------------+>br>>| @@secure_file_priv |>br>>+--------------------+>br>>| NULL |>br>>+--------------------+br>1 row in set (0.00 sec)

Este valor NULL (que significa “não permitir o acesso a qualquer ficheiro”) impede o servidor de aceder a qualquer ficheiro local no sistema de ficheiros. OK. Isto parece ser um valor por defeito razoável.

Agora, por uma acção contínua, vamos permitir-lhe aceder a ficheiros na subdirectoria de dados do directório de dados adicionando o seguinte:

--secure-file-priv=/home/gkodinov/work/mysql-8.0/bld/datadir/textdata

para mysqld.

Segunda tentativa:

p>mysql> select @@datadir,@@secure_file_priv\G
*************************** 1. row ***************************br>>@@datadir: /home/gkodinov/work/mysql-8.0/bld/datadir/
@@secure_file_priv: /export/home/tmp/gkodinov/work/mysql-8.0/bld/datadir/textdata/
1 row in set (0.00 sec)

mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',';
ERROR 1261 (01000): Row 1 doesn't contain data for all columns

Hmm, parece que já passámos a vedação de acesso ao ficheiro. Mas, como o servidor correctamente notou, temos 3 colunas na tabela e apenas 2 colunas para cada linha em data.txt. Vamos corrigir isso também:

mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | NULL |
| 4 | 4 | NULL |
+---+------+------+
>4 rows in set (0.00 sec)

Perfeito !

Como pode ver, o LOAD DATA INFILE funciona de forma bastante semelhante à importação de dados de texto para uma folha de cálculo do MS Excel. E tem uma configuração padrão bastante razoável para evitar que as pessoas leiam dados aleatórios através do sistema de ficheiros do servidor. Só precisa de ter os seus dados num directório apropriado do computador do servidor e pode importá-los.

Os benefícios de entrar neste problema extra são sobretudo que só tem de analisar uma única declaração INSERIR e depois executá-la tantas vezes quantas as filas.

Até agora tudo bem. Mas … hmmm … tem os seus dados no computador do servidor ? Para que possa importá-los e armazená-los novamente no directório de dados no mesmo computador servidor ?

Isto pode funcionar para pequenos ficheiros. Mas se tiver muitos e muitos dados, então ter duas cópias diferentes deles no mesmo computador pode nem sequer ser possível devido a limitações de espaço.

Mas, como se lembra de anteriormente, há dois computadores envolvidos na execução de um comando SQL: o computador servidor e o computador cliente. E se pudermos utilizar o espaço de armazenamento de ficheiros do último e de alguma forma transmitir os dados para o primeiro através da ligação cliente/servidor (juntamente com o próprio comando SQL) ?

É exactamente isto que o modificador LOCAL opcional faz !

Mas, ei, isto exigiria uma extensão de protocolo de rede: tem muitos dados extra para enviar. Vamos ver como é que isto se parece no fio. Poderíamos, evidentemente, utilizar a “wireshark” ou qualquer outra ferramenta de análise de dados de rede para o fazer. Mas felizmente não temos de.

Os formatos de wire do protocolo cliente/servidor mysql estão documentados na documentação do servidor Doxygen. Tem o seguinte diagrama:

Assim basicamente funciona assim:

1. O cliente envia o COM_QUERY para executar o comando SQL como habitualmente faz.
   • No nosso caso isto é “LOAD DATA LOCAL INFILE datafile/data”.txt”
2. O servidor (em vez de enviar um conjunto de resultados ou um estado) envia um pedido especial (0xFB) juntamente com o nome do ficheiro que o cliente deve ler e enviar
   li> No nosso caso isto é “ficheiro de dados/dados”.txt”
3. O cliente começa a transmitir o conteúdo do ficheiro solicitado pelo servidor seguido de um pacote vazio (comprimento zero) para significar o fim do ficheiro
4. O servidor executa o comando e utiliza os dados enviados e continua como normalmente com qualquer outro comando DML e envia um pacote OK (ou ERR) para significar o resultado da execução.

Bom! Mas … espere …

Porquê que o servidor precisa de enviar um nome de ficheiro para o cliente ler a partir de ? Não forneceu apenas um comando SQL com o mesmo nome de ficheiro em ?

Sim, forneceu. Mas infelizmente (ou não) SQL não é a linguagem mais fácil de analisar. Para saber o nome do ficheiro de que o cliente necessita para ter localmente um analisador completo para o dialecto SQL MySQL que está *em sincronia* com o analisador relevante no servidor. Tricky !

É por isso que o cliente tenta tirar partido da análise feita de qualquer maneira pelo servidor e espera que esta analise a declaração recebida e forneça o bit que o cliente precisa: o nome do ficheiro a ler.

p>OK, vamos mover o nosso ficheiro de dados para o computador cliente e dar uma tentativa a tudo isto:

mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);br>>>ERROR 1148 (42000): The used command is not allowed with this MySQL version

p> O que ?

Esta mensagem de erro não muito amigável diz que o LOAD DATA LOCAL INFILE está desactivado. Mas porquê ?

RefMan to the rescue again (about -local-infile):

Esta variável controla a capacidade LOCAL do lado do servidor para declarações de LOAD DATA. Dependendo da configuração local_infile, o servidor recusa ou permite o carregamento de dados locais por clientes que têm LOCAL activado no lado do cliente.

E o padrão desta variável tem sido OFF desde 8.0.2.

OK, vamos activar isto:

p>mysql> select @@local_infile;

+----------------+br>| @@local_infile |br>+----------------+br>| 1 |
+----------------+
>1 row in set (0.00 sec)

mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);
ERROR 1148 (42000): The used command is not allowed with this MySQL version

Hmm, ainda não funciona ?! O que se passa ?!

p>Remmbrar a explicação da troca de redes acima ? O cliente recebe do servidor um nome de um ficheiro para ler. Assim, o cliente precisa de tomar a sua própria decisão se quiser confiar no servidor e no nome do ficheiro que lhe envia.

Esta situação é explicada muito bem nesta página do manual de referência. Também menciona que o cliente precisa de assinalar que confia no servidor e na ligação ao mesmo, adicionando uma opção extra (ou opção mysql_options()): -local-infile.

Vamos tentar:

~/work/mysql-8.0/bld/datadir: ../runtime_output_directory/mysql --local-infile=1 <your options here>
Welcome to the MySQL monitor. Commands end with ; or \g.br>Your MySQL connection id is 9
>Server version: 8.0.16-debug Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or itsbr>affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;br>>Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);br>Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

Nice ! Finalmente !

Ok, então vamos recapitular o que é necessário para que o INFIL LOCAL DE DADOS CARREGADOS funcione:

1. Terá de dizer ao servidor para permitir a sua utilização pelos seus clientes, especificando -local-infile no servidor.
2. Terá de instruir o seu cliente mysql para o permitir para todas as instruções SQL. Ou geri-lo numa base mais fina (por exemplo, por ligação) se utilizar a API C.
4. E claro que precisa de ter o ficheiro certo e especificar as opções de importação certas.

Eu sei que é uma ordem alta e uma limitação severa de usabilidade. Mas vale definitivamente a pena considerar o tipo de magia que faz para transferir um monte de dados de texto através do fio e importá-los para uma tabela relacional do servidor. Tudo com um único comando.

Obrigado por utilizar o MySQL !