Blog MySQL Server

Chciałbym zwrócić uwagę na mniej znane polecenie MySQL SQL, przeanalizować jak działa i zrozumieć niektóre decyzje, które należy podjąć przy jego użyciu.

Zacznijmy od podstaw: Podręcznik referencyjny.

Ma on wiele do powiedzenia na temat LOAD DATA. My jednak skupimy się na LOAD DATA INFILE. W skrócie ma on taką składnię:

LOAD DATA

INFILE 'file_name'

INTO TABLE tbl_name
...)]


ENCLOSED BY 'char']

]


]

...)]
...]

OK, jest to więc polecenie wczytania danych z pliku tekstowego do tabeli.

Spróbujmy.

Użyjmy następującej definicji tabeli:
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT);

I następującego pliku danych (data.txt):

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

Ale … Hmm.

Jest to więc polecenie SQL wykonywane na komputerze serwera przez komputer klienta. Wiem, jak utworzyć tabelę. Ale gdzie mam umieścić mój plik z danymi?

Powrót do podręcznika:

Jeśli LOCAL nie jest określony, plik musi znajdować się na serwerze i jest odczytywany bezpośrednio przez serwer.

OK, więc muszę umieścić data.txt na moim serwerze. Spróbujmy to zrobić:

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

mysql> select @@datadir;
+--------------------------------------------+
| @@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 its
affiliates. Other names may be trademarks of their respective
owners.

owners.

owners.

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

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

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

mysql> use test;
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;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)

Ta wartość NULL (oznaczająca „nie zezwalaj na dostęp do plików”) uniemożliwia serwerowi dostęp do dowolnego pliku lokalnego w systemie plików. OK. Wydaje się to rozsądną wartością domyślną.

Teraz, dla dobra dalszego działania, umożliwmy mu dostęp do plików w podkatalogu data katalogu data, dodając następujące elementy:

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

do mysqld.

Druga próba:

mysql> select @@datadir,@@secure_file_priv\G
*************************** 1. row ***************************
@@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, wygląda na to, że mamy już za sobą ogrodzenie dostępu do plików. Ale, jak zauważył serwer, mamy 3 kolumny w tabeli i tylko 2 kolumny dla każdego wiersza w data.txt. To też naprawmy:

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

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)

Doskonale !

Jak widać LOAD DATA INFILE działa całkiem podobnie do importowania danych tekstowych do arkusza MS Excel. I ma całkiem sensowną domyślną konfigurację, aby zapobiec czytaniu przypadkowych danych przez ludzi w systemie plików serwera. Wystarczy mieć swoje dane w odpowiednim katalogu na serwerze i można je importować.

Korzyści z tego, że trzeba się natrudzić, to przede wszystkim to, że trzeba wykonać tylko jedną instrukcję INSERT, a następnie wykonać ją tyle razy, ile jest wierszy.

Jak na razie wszystko jest w porządku. Ale … hmmm … masz swoje dane na komputerze serwera? Abyś mógł je zaimportować i ponownie przechowywać w katalogu danych na tym samym komputerze serwera?

To może działać dla małych plików. Ale jeśli masz dużo i dużo danych, to posiadanie dwóch różnych kopii na tym samym komputerze może nie być możliwe ze względu na ograniczenia przestrzeni.

Ale, jak pamiętasz, są dwa komputery zaangażowane w wykonywanie poleceń SQL: komputer serwera i komputer klienta. A co jeśli możemy użyć przestrzeni dyskowej tego drugiego i w jakiś sposób przesłać dane do tego pierwszego poprzez połączenie klient/serwer (razem z samym poleceniem SQL).

To jest dokładnie to, co robi opcjonalny modyfikator LOCAL !

Ale hej, wymagałoby to rozszerzenia protokołu sieciowego: masz dużo dodatkowych danych do przesłania. Zobaczmy, jak to wygląda na kablu. Moglibyśmy oczywiście użyć do tego celu wiresharka lub innego narzędzia do analizy danych sieciowych. Ale na szczęście nie musimy tego robić.

Formaty przewodowe protokołu klient/serwer mysql są udokumentowane w dokumentacji Doxygen serwera. Ma ona następujący schemat:

Podstawowo działa to tak:

1. Klient wysyła COM_QUERY, aby wykonać polecenie SQL, tak jak to zwykle robi.
   • W naszym przypadku jest to „LOAD DATA LOCAL INFILE datafile/data.txt”
2. Serwer (zamiast wysyłać zestaw wyników lub status) wysyła specjalne żądanie (0xFB) wraz z nazwą pliku, który klient ma odczytać i wysłać
   • W naszym przypadku jest to „datafile/data.txt”
3. Klient zaczyna przesyłać strumieniowo zawartość pliku żądanego przez serwer, po czym następuje pusty (o zerowej długości) pakiet oznaczający koniec pliku
4. Serwer wykonuje polecenie i używa przesłanych danych i kontynuuje jak w przypadku każdego innego polecenia DML i wysyła pakiet OK (lub ERR) oznaczający wynik wykonania.

Dobrze ! Ale … czekaj …

Dlaczego serwer musi wysyłać nazwę pliku do klienta, aby ten mógł z niego czytać ? Czy nie mógł po prostu dostarczyć polecenia SQL z tą samą nazwą pliku, którą podał ?

Tak, mógł. Ale niestety (lub nie) SQL nie jest najłatwiejszym językiem do parsowania. Aby poznać nazwę pliku, klient musi lokalnie posiadać pełnowartościowy parser dla dialektu MySQL SQL, który jest *zsynchronizowany* z odpowiednim parserem na serwerze. Podchwytliwe !

Dlatego klient próbuje skorzystać z parsowania wykonanego przez serwer i oczekuje, że będzie on parsował otrzymaną deklarację i poda bit, którego klient potrzebuje: nazwę pliku do odczytania.

Dobra, przenieśmy nasz plik z danymi na komputer klienta i spróbujmy tego wszystkiego:

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

Co ?

Ten niezbyt przyjazny komunikat o błędzie mówi, że LOAD DATA LOCAL INFILE jest wyłączone. Ale dlaczego ?

RefMan znowu na ratunek (o -local-infile):

Ta zmienna kontroluje możliwości LOCAL po stronie serwera dla instrukcji LOAD DATA. W zależności od ustawienia local_infile, serwer odmawia lub zezwala na lokalne ładowanie danych przez klientów, którzy mają włączone LOCAL po stronie klienta.

A domyślnie ta zmienna jest wyłączona od 8.0.2.

OK, włączmy to:

mysql> select @@local_infile;

+----------------+
| @@local_infile |
+----------------+
| 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, nadal nie działa ?! Co jest nie tak ?

Pamiętasz powyższe wyjaśnienie wymiany sieci ? Klient otrzymuje od serwera nazwę pliku, z którego ma odczytać dane. Tak więc klient musi podjąć swoją własną decyzję, czy chce zaufać serwerowi i nazwie pliku, którą do niego wysyła.

Ta sytuacja jest wyjaśniona bardzo dobrze na tej stronie podręcznika referencyjnego. Wspomina ona również, że klient musi zasygnalizować, że ufa serwerowi i połączeniu z nim poprzez dodanie jednej dodatkowej opcji (lub opcji mysql_options()): -local-infile.

Spróbujmy tego:

~/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.
Your MySQL connection id is 9
Server version: 8.0.16-debug Source distribution

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 its
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;
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);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

Miło ! Nareszcie !

Ok, więc podsumujmy co trzeba zrobić, aby LOAD DATA LOCAL INFILE działało:

1. Musisz powiedzieć serwerowi, aby włączył to dla swoich klientów poprzez określenie -local-infile na serwerze.
2. Musisz poinstruować swojego klienta mysql, aby włączył to dla wszystkich poleceń SQL. Lub zarządzać nim na bardziej szczegółowej podstawie (np. na połączenie), jeśli używasz C API.
3. I oczywiście musisz mieć właściwy plik i określić właściwe opcje importu.

Wiem, że jest to wysokie zamówienie i poważne ograniczenie użyteczności. Ale zdecydowanie warto to zrobić, biorąc pod uwagę magię, jaką jest przesyłanie danych tekstowych przez kabel i importowanie ich do tabeli relacyjnej na serwerze. Wszystko za pomocą jednego polecenia.

Dziękuję za używanie MySQL’a !