Idea baz danych jest prosta co nie oznacza, że podczas pracy jako administrator lub webmaster nie natrafimy na problemy. Czasem ogranicza nas hosting, niewydajny kod aplikacji ale często jest to też nasza nieuwaga. W tym krótkim artykule przedstawię najczęstsze problemy związane z bazami danych (najczęściej MySQL) i proste techniki jak obejść lub rozwiązać problemy.
Problem: Plik SQL jest zbyt duży
Źródłem awarii może być zarówno ograniczenie wielkości pliku przy imporcie a także limit czasu wykonywania w środowisku PHP. Konieczność wykonania importu stosunkowo dużego pliku SQL może być też problematyczne jeżeli z jakichś powodów nie dysponujemy szybkim łączem.
Rozwiązanie:
Jeżeli problemem jest wielkość pliku, wystarczy spakować plik za pomocą zip lub gzip. Aplikacja phpMyAdmin zaakceptuje rozszerzenie sql.zip lub sql.gz i taki import nie powinien być problematyczny. Tak samo możemy poczynić przy imporcie wykonywanym w konsoli (rozpakowując plik SQL przed importem) lub użyć komendy:
unzip -p kopia.sql.zip | mysql -u root -p nazwabazydanych
Jak podzielić duży plik SQL na kilka mniejszych?
Jeżeli problemem jest limit czasu wykonywania i nawet spakowany plik jest stosunkowo duży można go podzielić za pomocą bardzo przydatnej aplikacji SQL Dump Splitter, której autorem jest Philip Lehmann-Böhm: https://www.philiplb.de/sqldumpsplitter3/
Problem: Błędy przy imporcie bazy danych
Parser może zgłaszać problemy związane z indeksami lub po prostu oświadczy nam, że:
MySQL Server has gone away...
Rozwiązanie:
Wszelkie tego typu problemy, które sprawiają wrażenie, że z bazą jest coś nie tak to najczęściej brak dopasowania wersji pomiędzy bazami danych. Problemy takie może powodować import z bazy MySQL 5.6 do 5.7. Wystarczy dostosować wersję MySQL do wersji z jakiej wykonano zrzut bazy danych i import powinien zakończyć się sukcesem.
Problem: nieprawidłowe kodowanie znaków przy zmianie serwera MySQL
To bardzo częsty problem, który może się objawić już przy pierwszych kontaktach z PHP i MySQL. Zakładając że aplikacja PHP a właściwie strona ma poprawnie zadeklarowane kodowanie w znacznikach meta:
<meta charset="utf-8">
lub w samym PHP wysyłamy nagłówek:
<?php header('Content-Type: text/html; charset=utf-8'); ?>
Rozwiązanie:
Zadbaj aby przed importem obie bazy danych miały standardowe kodowanie:
ALTER DATABASE nazwa_bazy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
W instrukcji, która łączy się z bazą użyj następującej instrukcji SET NAMES 'utf8′:
$dbconn = mysqli_connect($dbhost,$dbuser,$dbpass) or die ("Nie można się połączyć z bazą danych");
mysqli_query($dbconn, "SET NAMES 'utf8'");
mysqli_select_db($dbconn, $dbname) or die("Błąd");
Problem: #1044 Access denied for user
Błąd typu CREATE DATABASE 'nazwa_bazy’ IF NOT EXISTS Access denied for user… może się pojawić w przypadku kiedy importujemy plik SQL z baz danych różnych dostawców hostingu.
Rozwiązanie:
Istnieje skuteczne rozwiązanie tego problemu, wystarczy po prostu usunąć lub zakomentować linię CREATE DATABASE a w linii poniżej USE 'nazwa_bazy’ należy wpisać nazwę nowej bazy danych na którą importujemy plik SQL.
Problem: Baza danych szybko rośnie
Duża baza danych może powodować problemy o jakich pisałem w pierwszym punkcie. Czasem warto zapobiegać nadmiernemu rozrostowi bazy danych.
Rozwiązanie:
Zidentyfikuj, które tabele zajmują najwięcej miejsca i spróbuj je odchudzić. Wiele aplikacji przechowuje dane o każdym połączeniu w bazie danych. Logi, dane statyczne czy cache o znacznych rozmiarach często są przechowywane w stosunkowo szybkiej pamięci baz danych. Usuń lub opróżnij tabele lub wyłącz funkcjonalności, które powodują zapis danych, z których i tak nie korzystasz.
Problem: baza danych nie działa lub działa wolno przy dużym ruchu
Coraz większy ruch wymaga coraz większych zasobów sprzętowych niezależnie czy mamy do czynienia z serwerem HTTP czy jednostką/infrastrukturą obsługującą bazę danych. Sukcesem jest co najwyżej liniowy wzrost kosztów w stosunku do obsługiwanego ruchu.
Rozwiązanie:
Serwer MySQL posiada sztywne limity jednoczesnych połączeń na użytkownika aby nie doszło do całkowitego zablokowania jego działania w przypadku nadmiernej ilości zapytań. Zmiana podstawowych parametrów działania bazy możne znacznie poprawić jej wydajność jednak parametry te zawsze powinny być dobierane do możliwości sprzętowych. Jeżeli administrujesz serwerem MySQL zapoznaj się z popularnym skryptem: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
Powolnie działanie może być też przyczyną braku indeksów, tym bardziej jeżeli jest to „autorska” aplikacja a nie gotowe rozwiązanie CMS. Do pewnego momentu możemy optymalizować bazę danych, tym bardziej że wiele problemów ujawnia się w miarę przybywania rekordów. Ostatecznie jednak zawsze będziemy musieli zmierzyć się z problemem skalowania infrastruktury. W czasach niedrogich serwerów VPS i dopracowanych chmur obliczeniowych istnieje wiele rozwiązań, o których możesz przeczytać w osobnym artykule: skalowanie baz danych.
Problem: ERROR 1067 Invalid default value for (nazwa tabeli) np. 'scheduled_date_gmt’
Błąd może występować przy imporcie plików SQL. Jest to spowodowane opcją SQL Mode NO ZERO DATE.
Rozwiązanie:
Na samym początku pliku dodajemy:
SET SQL_MODE='ALLOW_INVALID_DATES';
Problem: #1046 – Nie wybrano żadnej bazy danych
Problem ten może wystąpić przy imporcie bazy SQL w aplikacji phpMyAdmin. Kiedy MySQL zwrócił komunikat:
#1046 - Nie wybrano żadnej bazy danych
oznacza to, że nie kliknęliśmy w bazę danych w panelu, który zazwyczaj wyświetla się po lewej stronie. Jeżeli z jakiegoś powodu nie możemy wybrać bazy danych, lub działamy w konsoli wystarczy dopisać jedną linię do pliku importu: USE nazwa_bazy;
Problem: MySQL has gone away: Error (near „ON” at position XX)
Na błąd MySQL has gone away – error (near „ON” at position 25) często skutecznym rozwiązaniem jest zwiększenie parametru max_allowed_packet. Drugie rozwiązanie to zaznaczenie przy eksporcie opcji:
IF NOT EXISTS (less efficient as indexes will be generated during table creation)
Problem: #1273 – Unknown collation: 'utf8mb4_0900_ai_ci’
Problem Unknown collation w MySQL występuje najczęściej kiedy migrujemy bazę z serwera o nowszej wersji do starszej. Starszy serwer nie obsługuje nowego kodowania (w tym wypadku utf8mb4_0900_ai_ci) i nie można importować takiej bazy danych. Rozwiązaniem jest podmiana kodowania utf8mb4_0900_ai_ci na: utf8_general_ci. Należy pamiętać aby podmienić wszystkie wystąpienia tego ciągu znaków bo moze on występować wielokrotnie w w pliku zrzutu, który mamy zamiar zaimportować do nowego miejsca docelowego.
Odpowiedz lub skomentuj