Как решить SQLSTATE [42000]: синтаксическая ошибка или нарушение прав доступа: 1118 Слишком большой размер строки (> 8126) в MySQL 5.7

Если вы работаете с MySQL 5.7 и обнаружили исключение при хранении большого количества полей в текстовом формате:

SQLSTATE [42000]: синтаксическая ошибка или нарушение прав доступа: 1118 Слишком большой размер строки (> 8126). Может помочь изменение некоторых столбцов на TEXT или BLOB или использование ROW_FORMAT = DYNAMIC или ROW_FORMAT = COMPRESSED. В текущем формате строки префикс BLOB размером 768 байт хранится в строке.

И вы уверены, что для формата строки таблицы установлено значение Динамический, возможно, вы столкнулись с проблемой размера файла журнала innodb и строгого режима. В этой статье мы покажем вам, как предотвратить появление этого исключения в MySQL 5.7.

1. Найдите файл my.cnf

В качестве первого шага вам нужно будет найти файл конфигурации MySQL. Нет внутренней команды MySQL для отслеживания местоположения этого файла, поэтому файл может находиться в 5 (или более) местах, и все они будут действительными, потому что они загружают каскад:

• /etc/my.cnf
• /etc/mysql/my.cnf
• $MYSQL_HOME/my.cnf
• [datadir]/my.cnf
• ~/.my.cnf

Это расположение по умолчанию, на которое смотрит MySQL, однако, если вы все еще не можете найти правильный файл, вы можете запустить следующую команду на своем терминале:

find / -name my.cnf

Как только вы найдете файл, откройте его с помощью редактора CLI, такого как nano, и выполните следующий шаг.

2. Увеличьте значение innodb_log_file_size

В нашем случае файл находится по адресу /etc/mysql/my.cnfчтобы мы могли редактировать файл с помощью nano, используя файл с помощью следующей команды:

nano /etc/mysql/my.cnf

Вам нужно будет отключить строгий режим MySQL и увеличить размер файла журнала innodb. когда innodb_strict_mode включен, InnoDB возвращает ошибки, а не предупреждения для определенных условий. Как и во многих системах управления базами данных, MySQL использует журналы для обеспечения устойчивости данных (при использовании механизма хранения InnoDB по умолчанию). Это гарантирует, что при совершении транзакции данные не будут потеряны в случае сбоя или потери питания. Механизм хранения InnoDB MySQL использует пространство журнала Redo фиксированного размера (круглое). Размер контролируется innodb_log_file_size, Если вы увеличите значение этого свойства, вы избавитесь от этого исключения при хранении нескольких столбцов текста в MySQL 5.7.

Тема о том, какой размер подходит для файла журнала innodb, в этой статье не рассматривается. мы рекомендуем вам прочитать эту статью который содержит подробное объяснение и факты о том, как выбрать это значение. Поскольку мы просто делимся с вами решением этой проблемы, мы будем использовать значение 512Mпоэтому параметры, которые нужно добавить в блок mysqld my.cfn файл будет:

# Important: inside the mysqld block
[mysqld]
# Add new log file size
innodb_log_file_size=512M
# Disable strict mode
innodb_strict_mode=0

Пример того, как должен выглядеть файл:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
# Add new log file size
innodb_log_file_size=512M
# Disable strict mode
innodb_strict_mode=0

Хотя мы не рассмотрели подробное объяснение проблемы, вызванной ограничением размера строки, которое устраняется динамическим форматом строки, вы можете захотеть узнать о эта проблема посещение этой статьи. После сохранения изменений в файле перезапустите mysql с помощью cli в зависимости от вашей ОС и процесса установки, например:

# Ubuntu
sudo service mysql restart
# CentOS
/etc/init.d/mysqld start