Предел изменения для слишком большого размера строки Mysql

Ответ 1

Вопрос был задан и на сервере.

Возможно, вы захотите взглянуть на эту статью, которая многое объясняет о размерах строк MySQL. Важно отметить, что даже если вы используете поля TEXT или BLOB, размер вашей строки все равно может превышать 8 КБ (предел для InnoDB), поскольку он хранит первые 768 байт для каждого встроенного поля на странице.

Самый простой способ исправить это - использовать формат файла Barracuda с InnoDB. Это в основном полностью избавляет от проблемы, сохраняя только 20-байтовый указатель на текстовые данные вместо того, чтобы сохранять первые 768 байт.

Метод, который работал для OP там был:

1. Добавьте следующее в файл my.cnf разделе [mysqld].

   innodb_file_per_table=1
   innodb_file_format = Barracuda
   

2. ALTER таблицы использовать ROW_FORMAT=COMPRESSED.

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

Существует вероятность того, что вышеуказанное все еще не решит ваши проблемы. Это известная (и проверенная) ошибка в механизме InnoDB, и на данный момент временное исправление - откат к механизму MyISAM в качестве временного хранилища. Итак, в вашем файле my.cnf:

internal_tmp_disk_storage_engine=MyISAM

Ответ 2

Недавно я столкнулся с этой проблемой и решил ее по-другому. Если вы используете MySQL версии 5.6.20, в системе существует известная ошибка. См. Документы MySQL

Важно Из-за ошибки # 69477, журнал повторных записей для больших, сохраненных во внешнем блоке полей BLOB может переписать наиболее последний контрольно-пропускной пункт. Чтобы устранить эту ошибку, патч, введенный в MySQL 5.6.20, ограничивает размер повтора log BLOB записывает 10% от размера файла журнала повтора. В результате этого предела innodb_log_file_size должно быть установлено в значение, превышающее в 10 раз наибольший размер данных BLOB, найденный в строках вашего таблицы плюс длина других полей переменной длины (поля VARCHAR, VARBINARY и TEXT).

В моей ситуации оскорбительная таблица blob составляла около 16 МБ. Таким образом, способ, которым я решил это, заключался в добавлении строки в my.cnf, которая обеспечила бы, что у меня было как минимум 10x это количество, а затем несколько:

innodb_log_file_size = 256M

Ответ 3

Если вы можете переключить ДВИГАТЕЛЬ и использовать MyISAM вместо InnoDB, это должно помочь:

ENGINE=MyISAM

В MyISAM есть два оговорки (возможно, больше):

• Вы не можете использовать транзакции.
• Вы не можете использовать ограничения внешнего ключа.

Ответ 4

Установите следующее в файле my.cnf и перезапустите сервер MySQL.

innodb_strict_mode = 0

Ответ 5

Я хотел бы поделиться удивительным ответом, это может быть полезно. Кредиты Билл Карвин см. Здесь https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

Они варьируются в зависимости от формата файла InnoDB. В настоящее время существует 2 формата, называемые Antelope и Barracuda. ​​

Центральный файл табличного пространства (ibdata1) всегда находится в формате Antelope. Если вы используете файл за таблицей, вы можете сделать отдельные файлы использующими формат Barracuda, установив innodb_file_format = Barracuda в my.cnf.

Основные пункты:

• Одна страница 16KB данных InnoDB должна содержать не менее двух строк данных. Плюс каждая страница имеет заголовок и нижний колонтитул, содержащий контрольные суммы страниц и порядковый номер журнала и т.д. Это то, где вы получаете свой лимит чуть меньше 8 КБ на строку.

• На этой первичной странице данных хранятся фиксированные типы данных типа INTEGER, DATE, FLOAT, CHAR и рассчитываются в соответствии с лимитом размера строки.

• Типы данных с переменным размером, такие как VARCHAR, TEXT, BLOB, хранятся на страницах переполнения, поэтому они не учитывают полностью ограничение по размеру строки. В Antelope до 768 байт таких столбцов хранятся на первичной странице данных в дополнение к хранению на странице переполнения. Barracuda поддерживает формат динамической строки, поэтому он может хранить только 20-байтовый указатель на первичной странице данных.

• Типы данных с переменным размером также имеют префикс с 1 или более байтами для кодирования длины. И формат строки InnoDB также имеет массив смещений полей. Таким образом, внутренняя структура более или менее документирована в их вики.

Barracuda также поддерживает ROW_FORMAT = COMPRESSED, чтобы получить дополнительную эффективность хранения для данных переполнения.

Мне также нужно прокомментировать, что я никогда не видел, чтобы хорошо спроектированная таблица превышала предел размера строки. Это сильный "запах кода", что вы нарушаете условие повторяющихся групп первой нормальной формы.

Ответ 6

Потратив часы, я нашел решение: просто запустите следующий SQL в вашем администраторе MySQL, чтобы преобразовать таблицу в MyISAM:

USE db_name;
ALTER TABLE table_name ENGINE=MYISAM;

Ответ 7

Я столкнулся с этой проблемой, когда пытался восстановить резервную копию базы данных mysql с другого сервера. Для меня эта проблема была решена путем добавления определенных настроек в my.conf (как в приведенных выше вопросах) и дополнительного изменения файла резервной копии sql:

Шаг 1: добавьте или отредактируйте следующие строки в my.conf:

innodb_page_size=32K
innodb_file_format=Barracuda
innodb_file_per_table=1

Шаг 2 добавьте ROW_FORMAT = DYNAMIC в оператор создания таблицы в файле резервной копии sql для таблицы, которая вызывает эту ошибку:

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;

важное изменение, приведенное выше, - ROW_FORMAT = DYNAMIC; (это не было включено в файл резервной копии оригинального sql)

источник, который помог мне решить эту проблему: слишком большой размер MariaDB и InnoDB MySQL Row

Ответ 8

У меня была та же самая проблема, это решило это для меня:

ALTER TABLE 'my_table' ROW_FORMAT=DYNAMIC;

Из документации MYSQL:

Формат строки DYNAMIC поддерживает эффективность хранения всей строки в узле индекса, если он подходит (как это делают форматы COMPACT и REDUNDANT), но этот новый формат устраняет проблему заполнения узлов B-дерева большим количеством байтов данных длинные колонны. Формат DYNAMIC основан на идее, что если часть длинного значения данных хранится вне страницы, обычно наиболее эффективно хранить все значения вне страницы. В формате DYNAMIC более короткие столбцы, вероятно, останутся в узле B-дерева, минимизируя количество страниц переполнения, необходимых для любой данной строки.

Ответ 9

Другие ответы отвечают на заданный вопрос. Я остановлюсь на основной причине: плохой дизайн схемы.

Не массируйте массив по столбцам. Здесь у вас есть 3 * 10 столбцов, которые должны быть превращены в 10 строк из 3 столбцов в новой таблице (плюс id и т.д.)

В таблице Main будет только

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 

Ваша дополнительная таблица (Top) имела бы

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

В Top для каждого id будет 10 (или меньше?) строк.

Это устраняет вашу оригинальную проблему и очищает схему. (Это не "нормализация", как обсуждалось в некоторых комментариях.)

Не переключайтесь на MyISAM; он уходит.
Не беспокойтесь о ROW_FORMAT.

Вам нужно будет изменить свой код, чтобы сделать JOIN, и обрабатывать несколько строк вместо нескольких столбцов.

Ответ 10

Я также столкнулся с той же проблемой. Я решаю проблему, выполнив следующий sql:

ALTER ${table} ROW_FORMAT=COMPRESSED;

Но я думаю, что вы должны знать о Row Storage.
Существует два типа столбцов: столбца переменной длины (типа VARCHAR, VARBINARY, BLOB и TEXT) и столбца фиксированной длины. Они хранятся в разных типах страниц.

Столбцы переменной длины являются исключением из этого правила. Столбцы, такие как BLOB и VARCHAR, которые слишком длинны для размещения на странице B-дерева, хранятся на отдельно выделенных дисковых страницах, называемых страницами переполнения. Такие столбцы мы называем внестраничными. Значения этих столбцов хранятся в односвязных списках переполненных страниц, и каждый такой столбец имеет свой собственный список одной или нескольких страниц переполнения. В некоторых случаях все или префикс значения длинного столбца хранятся в B-дереве, чтобы избежать потери хранилища и устранения необходимости читать отдельную страницу.

и когда целью установки ROW_FORMAT является

Когда таблица создается с помощью ROW_FORMAT = DYNAMIC или ROW_FORMAT = COMPRESSED, InnoDB может хранить длинные значения столбцов переменной длины (для типов VARCHAR, VARBINARY и BLOB и TEXT) полностью вне страницы, а кластерная индексная запись, содержащая только 20-байтовый указатель на страницу переполнения.

Хотите узнать больше о ДИНАМИЧЕСКИЕ И СЖАТЫЕ ФОРМЫ Row

Ответ 11

Если это происходит в SELECT со многими столбцами, причиной может быть то, что mysql создает временную таблицу. Если эта таблица слишком велика для размещения в памяти, она будет использовать формат временной таблицы по умолчанию (InnoDB) для хранения на диске. В этом случае применяются ограничения размера InnoDB.

Затем у вас есть 4 варианта:

1. измените ограничение размера строки innodb, как указано в другом посте, что требует повторной инициализации сервера.
2. измените свой запрос, чтобы он включал меньше столбцов, или не создавайте временную таблицу (например, удаляя команды order by и limit).
3. изменение max_heap_table_size на большое, чтобы результат помещался в памяти и не нуждался в записи на диск.

4. измените формат временной таблицы по умолчанию на MYISAM, это то, что я сделал. Изменение в my.cnf:

   internal_tmp_disk_storage_engine=MYISAM
   

Перезапустите mysql, запрос работает.

Ответ 12

Вот простой совет для всех, кто интересуется:

После обновления с Debian 9 до Debian 10 с 10.3.17-MariaDB у меня возникли некоторые ошибки из баз данных Joomla:

[Предупреждение] InnoDB: Невозможно добавить поле field в таблицу database. table, поскольку после добавления его размер строки составляет 8742, что превышает максимально допустимый размер (8126) для записи на листе индекса.

На всякий случай я поставил innodb_default_row_format = DYNAMIC в /etc/mysql/mariadb.conf.d/50-server.cnf (это все равно было по умолчанию)

Затем я использовал phpmyadmin для запуска "Оптимизировать таблицу" для всех таблиц в базе данных Joomla. Я думаю, что восстановление таблицы, сделанное phpmyadmin в процессе, помогло. Если у вас установлен phpmyadmin, достаточно сделать всего несколько кликов.

Ответ 13

Я использую MySQL 5.6 на AWS RDS. Я обновил следующее в группе параметров.

innodb_file_per_table=1
innodb_file_format = Barracuda

Мне пришлось перезагрузить экземпляр БД, чтобы изменения группы параметров вступили в силу.

Кроме того, ROW_FORMAT = COMPRESSED не поддерживается. Я использовал DYNAMIC, как показано ниже, и он работал нормально.

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