Stuck by the error ‘MySQL error 1118’?

Generally, this error occurs due to the status of innodb_strict_mode set in the database configuration file or due to the value of innodb_log_file_size.

At Bobcares, we often receive requests to resolve MySQL errors as a part of our Server Management Services.

Today, let’s see how our Support Engineers troubleshoot this error and fix it.

What causes MySQL error 1118 to occur?

Before getting into the fix, let’s discuss what causes this error to occur. Here, are the different reasons for this error to occur in the database server.

1. Status of innodb_strict_mode

InnoDB raises an error in certain cases only, rather than pop-up a warning message and execute the specified command.

The status set for InnoDB strict mode affects the handling of syntax errors on the CREATE TABLE, ALTER TABLE and CREATE INDEX commands.

If the strict mode is not enabled then InnoDB will ignore a few syntax errors and will create the table or index by providing only a warning message log.

2. Value of innodb_log_file_size

The log file size must be set as big as possible. However, not bigger than the necessary value. A bigger log file size is better for performance.

So, the innodb_log_file_size setting should be 10 times larger than the largest BLOB data size found in the rows.

In case, if the value of innodb_log_file_size is smaller then it will trigger errors.

How we fix MySQL error 1118?

Having a decade of experience in managing servers, our Dedicated Engineers are familiar with these MySQL errors. Now, let’s discuss how our Support Engineers fix this error.

Recently, one of our customers ran into a problem using a MySQL database. He received the below error message while inserting values into the database tables.

ERROR 1118 (42000) at line 7019: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.mysql database_xx_yy < database_xx_yy_dump.sql && mysql database_xx_yy < database_xx_yy_dump.sql2019-11-07T15:19:38.742462Z 9 [ERROR] [MY-011825] [InnoDB] Cannot add field LINE_STATUS_CODE2 in table database_xx_yy.xxomds_so_line_if because after adding it, the row size is 8166 which is greater than maximum allowed size (8126) for a record on index leaf page.

On investigating, we found that the Row size was too large for the database backup “database_xx_yy.sql”. Here, the innodb_strict_mode was causing the error. So, we disabled that parameter through the MySQL command prompt.

Initially, we checked for the innodb_strict_mode value using the command:

show variables like '%strict%';

Then, we ran the below command to disable InnoDB strict mode.

SET GLOBAL innodb_strict_mode = OFF;

After that, we rechecked it and here is the output.

Also, we have set innodb_strict_mode to 0 in /etc/my.cnf.d/mysql-server.cnf so that it won’t reset after any reboot. We did it by following the below steps:

We navigated to the path /etc/my.cnf.d

cd /etc/my.cnf.d

Then, we edited the file mysql-server.cnf

vi mysql-server.cnf

Here, we set innodb_strict_mode to 0

innodb_strict_mode = 0

Lastly, we saved and closed the file.

Finally, this fixed the error.

[Need any assistance with MySQL errors? – We’ll help you]

Conclusion

In short, the MySQL error 1118 occurs due to the status of innodb_strict_mode set in the configuration file or due to the value of innodb_log_file_size. Today, we saw how our Support Engineers fix this error.