Решение ошибки MySQL 1118: превышен размер строки

Быстрый ответ

Для устранения ошибки MySQL 1118, связанной с превышением допустимого размера строки, выполните следующие шаги:

1. Преобразовывайте типы данных: Конвертируйте поля с большим объемом VARCHAR в типы данных TEXT или BLOB. Это позволит осуществлять их хранение вне таблицы и уменьшит размер строки.

   SQL

   Скопировать код

   ALTER TABLE ваша_таблица MODIFY ваш_столбец TEXT;

   Запустить код

2. Переключитесь на формат строки DYNAMIC: Этот формат эффективно обрабатывает длинные строки.

   SQL

   Скопировать код

   ALTER TABLE ваша_таблица ROW_FORMAT=DYNAMIC;

   Запустить код

Двигайтесь в этом направлении для оптимизации размера строк и повышения эффективности работы базы данных.

Разбираемся с ограничением по размеру строки в MySQL

Чрезмерный размер строки в MySQL, особенно для движка InnoDB, может мешать нормальной работе. Понимая корень этой проблемы, вы сможете найти правильное решение:

• Схема хранения данных InnoDB: InnoDB пакует данные в страницу определенного размера (по умолчанию — 16 KB). Совокупные данные строки не должны превышать это ограничение.
• Структура страниц: Каждая страница InnoDB с содержимым таблицы сопровождается служебной информацией, которая уменьшает пространство для самих данных.
• Поле с переменной длиной: Для столбцов с переменной длиной (VARCHAR, VARBINARY, TEXT, BLOB) InnoDB резервирует пространство для хранения информации о длине.

Альтернативные способы преобразования столбцов

Если колонки с переменными типами данных неэффективны, можно рассмотреть эти варианты:

1. Отключите innodb_strict_mode: Временное отключение этого параметра сервера может помочь создавать таблицы, уклоняясь от проверки.

   SQL

   Скопировать код

   SET GLOBAL innodb_strict_mode = OFF;

   Запустить код

2. Увеличьте размер файла журнала InnoDB: Большой параметр innodb_log_file_size в настройках my.cnf или my.ini может облегчить работу с крупными транзакциями.

3. Измените размер страницы InnoDB: Увеличьте innodb_page_size, чтобы получить больше места для данных, но будьте готовы к переустановке MySQL.

Размышления о возможных изменениях

Изменение параметров файла журнала и размера страницы InnoDB несет за собой риски:

• Размер файла журнала Innodb: Большой размер файла позволит работать с крупными транзакциями, но увеличит время восстановления после сбоев.
• Размер страницы Innodb: Увеличение размера страницы допустит хранение больших строк, но может привести к неэффективному использованию пространства при малом размере большинства строк.

Настраиваем параметры базы данных

Для предотвращения подобных проблем настройте следующие параметры базы данных:

1. Размер буфера пула Innodb: Увеличив innodb_buffer_pool_size, вы способствуете производительности при достаточном объеме памяти.

   cnf

   Скопировать код

   innodb_buffer_pool_size = 1G

   Запустить код

2. Размер буфера журнала Innodb: Настройте innodb_log_buffer_size, чтобы оптимизировать запись данных в журнал.

   cnf

   Скопировать код

   innodb_log_buffer_size = 256M

   Запустить код

Подробнее об этом расскажет наш спикер на видео

Меры без структурных изменений

Когда система отказывается вносить структурные изменения, действуйте следующим образом:

• Динамическое изменение SQL: Используйте SET SESSION для динамического внесения изменений на время сессии.
• Неструктурные изменения: Изучите настройки MySQL, которые можно изменить для повышения производительности без реорганизации структуры, например, размеры буфера пула и буфера журнала.

Визуализация

Возьмем для примера метафору: представьте предел размера строки в MySQL как:

Это как библиотечная полка 📚: Каждая книга — это колонка, занимающая свое фиксированное место на полке.

Обычные колонки 📗📙📘: Книги аккуратно помещаются на полку.
Колонки TEXT/BLOB 📕📒: Однако некоторые экземпляры слишком толстые!

Проблема:

Ширина полки 📏: Максимум вмещает 8126 единиц!
Переполненная полка 📚*📚*📚: Когда книги вываливаются с полки (❌)

Решение:

Замените толстые книги 📕📒 на пергаменты 📜, которые можно раскатать и разместить на любой полке.

Итог:

Организованная полка: [📗📙📚*📜*📜] Все книги и свитки помещаются на полку (✅)

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 8.4.7 Ограничения на количество столбцов и размер строки таблицы — официальная документация MySQL по количеству столбцов и размеру строки в таблице.
2. InnoDB create table error: "Row size too large" – Database Administrators Stack Exchange — обсуждение практических способов уменьшения размера строк в MySQL.
3. Innodb row size limitation — аналитическое обсуждение проблемы размера строки от экспертов Percona.
4. Change limit for MySQL row size too large – Stack Overflow — советы от пользователей по обходу ограничений по размеру строк в MySQL.
5. MySQL :: Форумы MySQL :: InnoDB — обсуждения на официальном форуме MySQL проблемы с ошибкой 1118 и связанными с ней ограничениями InnoDB.