Zmień limit dla „Zbyt duży rozmiar wiersza MySQL”

Jak mogę zmienić limit

Zbyt duży rozmiar wiersza (> 8126). Pomocna ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSEDmoże być zmiana niektórych kolumn na TEKST lub BLOB lub użycie . W aktualnym formacie wierszaBLOB prefiks 768 bajtów jest przechowywany w linii.

Stół:

id  int(11) No       
name    text    No       
date    date    No       
time    time    No       
schedule    int(11) No       
category    int(11) No       
top_a   varchar(255)    No       
top_b   varchar(255)    No       
top_c   varchar(255)    No       
top_d   varchar(255)    No       
top_e   varchar(255)    No       
top_f   varchar(255)    No       
top_g   varchar(255)    No       
top_h   varchar(255)    No       
top_i   varchar(255)    No       
top_j   varchar(255)    No       
top_title_a varchar(255)    No       
top_title_b varchar(255)    No       
top_title_c varchar(255)    No       
top_title_d varchar(255)    No       
top_title_e varchar(255)    No       
top_title_f varchar(255)    No       
top_title_g varchar(255)    No       
top_title_h varchar(255)    No       
top_title_i varchar(255)    No       
top_title_j varchar(255)    No       
top_desc_a  text    No       
top_desc_b  text    No       
top_desc_c  text    No       
top_desc_d  text    No       
top_desc_e  text    No       
top_desc_f  text    No       
top_desc_g  text    No       
top_desc_h  text    No       
top_desc_i  text    No       
top_desc_j  text    No       
status  int(11) No       
admin_id    int(11) No 

Pytanie zostało również zadane w przypadku awarii serwera.

Możesz rzucić okiem na ten artykuł, który wyjaśnia wiele na temat rozmiarów wierszy MySQL. Ważne jest, aby pamiętać, że nawet jeśli używasz pól TEXT lub BLOB, rozmiar wiersza może nadal przekraczać 8K (limit dla InnoDB), ponieważ przechowuje pierwsze 768 bajtów dla każdego pola wbudowanego na stronie.

Najprostszym sposobem rozwiązania tego problemu jest użycie formatu pliku Barracuda z InnoDB. To w zasadzie całkowicie eliminuje problem, przechowując tylko 20-bajtowy wskaźnik do danych tekstowych, zamiast przechowywać pierwsze 768 bajtów.

Metoda, która sprawdziła się w PO to:

1. Dodaj poniższe do my.cnfpliku w [mysqld]sekcji.

   innodb_file_per_table=1
   innodb_file_format = Barracuda

2. ALTERstół do użycia ROW_FORMAT=COMPRESSED.

   ALTER TABLE nombre_tabla
       ENGINE=InnoDB
       ROW_FORMAT=COMPRESSED 
       KEY_BLOCK_SIZE=8;

Istnieje możliwość, że powyższe nadal nie rozwiąże Twoich problemów. Jest to znany (i zweryfikowany) błąd związany z silnikiem InnoDB , a tymczasowym rozwiązaniem na razie jest powrót do silnika MyISAM jako tymczasowego magazynu. Więc w twoim my.cnfpliku:

internal_tmp_disk_storage_engine=MyISAM

Niedawno natknąłem się na ten problem i rozwiązałem go w inny sposób. Jeśli używasz MySQL w wersji 5.6.20, w systemie jest znany błąd. Widzieć dokumentację MySQL

Ważne Ze względu na błąd nr 69477 ponowne zapisywanie w dzienniku dużych, przechowywanych na zewnątrz pól BLOB może nadpisać ostatni punkt kontrolny. Aby rozwiązać ten błąd, poprawka wprowadzona w MySQL 5.6.20 ogranicza rozmiar zapisu BLOB powtórzeń dziennika do 10% rozmiaru pliku dziennika powtórzeń. W wyniku tego ograniczenia innodb_log_file_size należy ustawić na wartość większą niż 10-krotność największego rozmiaru danych BLOB znalezionych w wierszach twoich tabel plus długość innych pól o zmiennej długości (pola typu VARCHAR, VARBINARY i TEXT).

W mojej sytuacji tabela blobów powodująca problemy wynosiła około 16 MB. Tak więc sposób, w jaki to rozwiązałem, polegał na dodaniu linii do my.cnf, która zapewniła, że ​​mam co najmniej 10 razy tyle, a potem:

innodb_log_file_size = 256M

Ustaw następujące elementy w pliku my.cnf i zrestartuj serwer mysql.

innodb_strict_mode=0

Jeśli możesz zmienić SILNIK i użyć MyISAM zamiast InnoDB, to powinno pomóc:

ENGINE=MyISAM

Istnieją dwa zastrzeżenia dotyczące MyISAM (prawdopodobnie więcej):

1. Nie możesz używać transakcji.
2. Nie możesz używać ograniczeń klucza obcego.

Chciałbym podzielić się świetną odpowiedzią, może być pomocna. Kredyty Bill Karwin patrz tutaj /dba/6598/innodb-create-table-error-row-size-too-large

Różnią się one w zależności od formatu pliku InnoDB. Obecnie istnieją 2 formaty o nazwie Antelope i Barracuda.

Centralny plik obszaru tabel (ibdata1) jest zawsze w formacie Antelope. Jeśli używasz pliku na tabelę, możesz ustawić poszczególne pliki w formacie Barracuda, ustawiając innodb_file_format = Barracuda w my.cnf.

Podstawowe punkty:

1. Jedna strona danych InnoDB o rozmiarze 16 KB musi zawierać co najmniej dwa wiersze danych. Dodatkowo każda strona ma nagłówek i stopkę zawierającą sumy kontrolne stron, numer kolejny dziennika i tak dalej. W tym miejscu uzyskujesz limit nieco mniejszy niż 8 KB na wiersz.

2. Typy danych o stałym rozmiarze, takie jak INTEGER, DATE, FLOAT, CHAR, są przechowywane na tej podstawowej stronie danych i wliczają się do limitu rozmiaru wiersza.

3. Typy danych o zmiennej wielkości, takie jak VARCHAR, TEXT, BLOB, są przechowywane na przepełnionych stronach, więc nie wliczają się w pełni do limitu rozmiaru wiersza. W Antelope do 768 bajtów takich kolumn jest przechowywanych na głównej stronie danych, oprócz tego, że są one przechowywane na stronie przepełnienia. Barracuda obsługuje dynamiczny format wierszy, więc może przechowywać tylko 20-bajtowy wskaźnik na głównej stronie danych.

4. Typy danych o zmiennej wielkości są również poprzedzone 1 lub większą liczbą bajtów w celu zakodowania długości. Format wiersza InnoDB ma również tablicę przesunięć pól. Więc istnieje wewnętrzna struktura mniej więcej udokumentowana na ich wiki.

Barracuda obsługuje również ROW_FORMAT = COMPRESSED, aby zwiększyć wydajność przechowywania danych o przepełnieniu.

Muszę też dodać, że nigdy nie widziałem dobrze zaprojektowanej tabeli przekraczającej limit rozmiaru wiersza. To silny „zapach kodu”, że naruszasz warunek grup powtarzających się w pierwszej normalnej formie.

Miałem ten sam problem, to rozwiązało to za mnie:

ALTER TABLE `my_table` ROW_FORMAT=DYNAMIC;

Z dokumentacji MYSQL :

Format wiersza DYNAMICZNY zachowuje efektywność przechowywania całego wiersza w węźle indeksu, jeśli pasuje (podobnie jak formaty COMPACT i REDUNDANT), ale ten nowy format pozwala uniknąć problemu wypełniania węzłów B-drzewa dużą liczbą bajtów danych długie kolumny. Format DYNAMICZNY opiera się na założeniu, że jeśli część długiej wartości danych jest przechowywana poza stroną, zazwyczaj najbardziej efektywne jest przechowywanie całej wartości poza stroną. W przypadku formatu DYNAMICZNEGO krótsze kolumny prawdopodobnie pozostaną w węźle B-drzewa, minimalizując liczbę przepełnionych stron potrzebnych dla danego wiersza.

Po spędzeniu wielu godzin znalazłem rozwiązanie: po prostu uruchom następujący SQL w swoim administratorze MySQL, aby przekonwertować tabelę na MyISAM:

USE db_name;
ALTER TABLE table_name ENGINE=MYISAM;

Napotkałem ten problem, gdy próbowałem przywrócić kopię zapasową bazy danych mysql z innego serwera. Rozwiązaniem dla mnie było dodanie pewnych ustawień do my.conf (jak w pytaniach powyżej) i dodatkowo zmiana pliku kopii zapasowej sql:

Krok 1: dodaj lub edytuj następujące wiersze w my.conf:

innodb_page_size=32K
innodb_file_format=Barracuda
innodb_file_per_table=1

Krok 2 dodaj ROW_FORMAT = DYNAMIC do tabeli create instrukcji w pliku kopii zapasowej sql dla tabeli, która powoduje ten błąd:

DROP TABLE IF EXISTS `problematic_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `problematic_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC;

ważna zmiana powyżej to ROW_FORMAT = DYNAMIC;(to nie było zawarte w oryginalnym pliku kopii zapasowej sql)

źródło, które pomogło mi rozwiązać ten problem: MariaDB i InnoDB MySQL Row size too large

Pozostałe odpowiedzi dotyczą zadanego pytania. Zajmę się podstawową przyczyną: słaby projekt schematu.

Nie rozkładaj tablicy między kolumnami. Tutaj masz 3 * 10 kolumn, które powinny zostać zamienione na 10 rzędów po 3 kolumny w nowej tabeli (plus iditp.)

Twój Main stół miałby tylko

id  int(11) No       
name    text    No       
date    date    No       
time    time    No       
schedule    int(11) No       
category    int(11) No       
status  int(11) No       
admin_id    int(11) No 

Twój dodatkowy stół (Top ) miałby

id  int(11) No          -- for joining to Main
seq TINYINT UNSIGNED    -- containing 1..10
img   varchar(255)    No       
title varchar(255)    No       
desc  text    No    
PRIMARY KEY(id, seq)    -- so you can easily find the 10 top_titles

Byłoby 10 (lub mniej? Lub więcej?) Wierszy w Top dla każdego id.

Eliminuje to pierwotny problem i oczyszcza schemat. (Nie jest to „normalizacja”, o której mowa w niektórych komentarzach).

Czy nie przełączyć się na MyISAM; to odchodzi.
Nie martw się ROW_FORMAT.

Będziesz musiał zmienić kod, aby wykonać JOINi obsługiwać wiele wierszy zamiast wielu kolumn.

Używam MySQL 5.6 na AWS RDS. Zaktualizowałem następujące w grupie parametrów.

innodb_file_per_table=1
innodb_file_format = Barracuda

Musiałem zrestartować instancję DB, aby zmiany grupy parametrów zaczęły obowiązywać.

Ponadto ROW_FORMAT = COMPRESSED nie był obsługiwany. Użyłem DYNAMIC, jak poniżej i działało dobrze.

ALTER TABLE nombre_tabla ENGINE=InnoDB ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8

Maksymalny rozmiar wiersza dla tabeli InnoDB, który dotyczy danych przechowywanych lokalnie na stronie bazy danych, to nieco mniej niż połowa strony dla 4 KB, 8 KB, 16 KB i 32 KB

Dla stron 16kb (domyślnie) możemy obliczyć:

Slightly less than half a page 8126 / Number of bytes to threshold for overflow 767 = 10.59 fields of 767 bytes maximum

Zasadniczo możesz zmaksymalizować wiersz za pomocą:

• 11 pól varchar> 767 znaków (latin1 = 1 bajt na znak) lub
• 11 pól varchar> 255 znaków (utf-8 na mysql = 3 bajty na znak).

Pamiętaj, że nastąpi przepełnienie do strony przepełnienia tylko wtedy, gdy pole ma> 767 bajtów. Jeśli jest zbyt wiele pól o wielkości 767 bajtów, nastąpi błąd (przekraczający maksymalny rozmiar_wiersza). Nie jest to typowe dla latin1, ale bardzo możliwe z utf-8, jeśli programiści nie są ostrożni.

W tym przypadku myślę, że możesz podnieść innodb_page_size do 32kb.

w my.cnf:

innodb_page_size=32K

Bibliografia:

• https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#row-size-limits
• https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434
• https://mariadb.com/kb/en/library/innodb-compact-row-format/#overflow-pages-with-the-compact-row-format
• Ustawienie rozmiaru strony Innodb

Napotkałem też ten sam problem. Rozwiązuję problem, wykonując następujący sql:

ALTER ${table} ROW_FORMAT=COMPRESSED;

Ale myślę, że powinieneś wiedzieć o Row Storage .
Istnieją dwa rodzaje kolumn: kolumny o zmiennej długości (takie jak typy VARCHAR, VARBINARY oraz BLOB i TEXT) oraz kolumny o stałej długości . Są przechowywane na różnych typach stron.

Kolumny o zmiennej długości stanowią wyjątek od tej reguły. Kolumny, takie jak BLOB i VARCHAR, które są zbyt długie, aby zmieścić się na stronie B-drzewa, są przechowywane na osobno przydzielonych stronach dysku zwanych stronami przepełnienia. Takie kolumny nazywamy kolumnami poza stroną. Wartości tych kolumn są przechowywane na listach przepełnionych stron, do których prowadzą pojedyncze łącza, a każda taka kolumna ma własną listę zawierającą jedną lub więcej przepełnionych stron. W niektórych przypadkach cała lub przedrostek wartości długiej kolumny jest przechowywany w B-drzewie, aby uniknąć marnowania pamięci i wyeliminowania potrzeby czytania oddzielnej strony.

i kiedy celem ustawienia ROW_FORMAT jest

Kiedy tabela jest tworzona z ROW_FORMAT = DYNAMIC lub ROW_FORMAT = COMPRESSED, InnoDB może przechowywać wartości kolumn o długich zmiennych długościach (dla typów VARCHAR, VARBINARY oraz BLOB i TEXT) całkowicie poza stroną, z rekordem indeksu klastrowanego zawierającym tylko 20- wskaźnik bajtów do strony przepełnienia.

Chcesz dowiedzieć się więcej o formatach wierszy DYNAMICZNYCH i SPRĘŻONYCH

Jeśli tak się stanie w przypadku SELECT z wieloma kolumnami, przyczyną może być to, że mysql tworzy tymczasową tabelę. Jeśli ta tabela jest zbyt duża, aby zmieścić się w pamięci, użyje domyślnego formatu tabeli tymczasowej, czyli InnoDB, do przechowywania jej na dysku. W takim przypadku obowiązują ograniczenia rozmiaru InnoDB.

Masz wtedy 4 opcje:

1. zmień limit rozmiaru wiersza innodb, jak podano w innym poście, co wymaga ponownej inicjalizacji serwera.
2. zmień zapytanie, aby zawierało mniej kolumn lub unikaj powodowania tworzenia tymczasowej tabeli (np. usuwając klauzule order by i limit).
3. zmiana max_heap_table_size na dużą, aby wynik zmieścił się w pamięci i nie trzeba go zapisywać na dysku.

4. zmień domyślny format tabeli tymczasowej na MYISAM, oto co zrobiłem. Zmiana w my.cnf:

   internal_tmp_disk_storage_engine=MYISAM

Uruchom ponownie mysql, zapytanie działa.

Oto prosta wskazówka dla wszystkich zainteresowanych:

Po aktualizacji z Debiana 9 do Debiana 10 z 10.3.17-MariaDB, mam kilka błędów w bazach danych Joomla:

[Ostrzeżenie] InnoDB: Nie można dodać pola fieldw tabeli database.tableponieważ po dodaniu rozmiar wiersza wynosi 8742, co jest większe niż maksymalny dozwolony rozmiar (8126) dla rekordu na stronie liścia indeksu.

Na wszelki wypadek ustawiłem innodb_default_row_format = DYNAMIC w /etc/mysql/mariadb.conf.d/50-server.cnf (i tak było to domyślne)

Następnie użyłem phpmyadmin do uruchomienia „Optymalizuj tabelę” dla wszystkich tabel w bazie danych Joomla. Myślę, że odtworzenie tabeli wykonane przez phpmyadmin pomogło w tym procesie. Jeśli masz zainstalowany phpmyadmin, wystarczy kilka kliknięć.