How to solve SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126) in MySQL 5.7

If you are working with MySQL 5.7 and you find an exception when storing a lot of fields with text format:

And you are sure that the row format of the table is set to Dynamic, you are probably facing an issue with the innodb log file size and the strict mode. In this article, we'll show you how to prevent this exception from appearing in MySQL 5.7.

1. Find my.cnf file

As first step, you will need to search for the configuration file of MySQL. There is no internal MySQL command to trace the location of this file, so the file might be in 5 (or more) locations, and they would all be valid because they load cascading:

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

Those are the default locations MySQL looks at, however if you still don't find the correct file, you may run the following command on your terminal:

find / -name my.cnf

Once you find the file, open it with a CLI editor like nano and follow the next step.

2. Increase innodb_log_file_size value

In our case, the file is located at /etc/mysql/my.cnf, so we could edit the file with nano using the file with the following command:

nano /etc/mysql/my.cnf

You will need to disable the strict mode of MySQL and increase the size of innodb log file. When innodb_strict_mode is enabled, InnoDB returns errors rather than warnings for certain conditions. Like many database management systems, MySQL uses logs to achieve data durability (when using the default InnoDB storage engine). This ensures that when a transaction is committed, data is not lost in the event of crash or power loss. MySQL’s InnoDB storage engine uses a fixed size (circular) Redo log space. The size is controlled by innodb_log_file_size. If you increase the value of this property, you will get rid off this exception when storing multiple columns of text in MySQL 5.7.

The theme about which size is right for the innodb log file won't be covered in this article, instead we recommend you to read this article that contains a detailed explanation and facts about how to choose this value. As we are just sharing with you the solution to this problem, we'll use the value of 512M, so the parameters to add to the mysqld block of the my.cfn file will be:

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

An example of how the file should look like:

#
# 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

Although we didn't cover a detailed explanation of the problem caused by the row size limitation that is removed by the dynamic row format, you may want to inform yourself about this problem visiting this article. After saving changes in the file , restart mysql with the cli depending of your os and installation process e.g:

# Ubuntu
sudo service mysql restart

# CentOS
/etc/init.d/mysqld start

Happy coding !