How to solve MySQL General error: 1030 Got error 139 from storage engine

You will face this kind of error random times, where an insert statement will fail, causing the fatal error 139. It happens normally in production, when the user inserts a lot of text in some field of a table (although the field may be of type LONGTEXT). Innodb triggers this error when it is unable to store all of the variable-length columns for a given row on a single database page.

In this article, we'll show you how to bypass this limitation and explain you why this happens.

Why does this happens?

Innodb has a known limit for the maximum row size, which is slightly less than the half of a database page (the actual math is 16K - (page header + page trailer)/2. For the default page size of 16kb. this sets an ~8000 bytes limit on row size. This limit is a consequence of InnoDB storing two rows on a page. If you're using compression, however, you can store a single row on a page. If your row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns for off-page storage.

It’s worth mentioning that this limit applies to the byte-size of values, not the character-size. This means if you insert a 500 character string with all multi-byte characters into a VARCHAR(500) column, that value will also be chosen for off page storage. This means it is possible that you hit this error after converting from a native language character set to utf8, as it can increase the size dramatically. For example, in case that you have a table with at least 8 columns with text (TEXT, LONGTEXT) and each exceeds 768 bytes, then you’ll have at least 8448 bytes for local storage. This exceeds the limit and therefore you will face the exception.

In short words, a lot of data that InnoDB is unable to store with the current configuration. The best solution for this problem is changing the default format of your MySQL installation from Compact or Redundant to Dynamic or Compressed.

1. Verify if Barracuda format is available

The solution to this problem is to set the row format of the table where you have the problem to DYNAMIC, however this options is not available when the format of the table is set to Antelope. To verify if your MySQL server has the Barracuda format enabled, you can check for example with a tool like PHPMyAdmin the row format options of any table:

If you don't see the DYNAMIC or COMPRESSED option, you need to change the innodb_file_format to barracuda following the step #2, which normally is set Antelope. If you don't have a tool like PHPMyAdmin available, you can open the mysql terminal and get the information with the following command:

show variables like "%innodb_file%";

A mysql version with this problem should output something like:

As you can see, the file format is set to Antelope, which doesn't support the dynamic row format.

2. Change file format to Barracuda

The first thing that you need to do is to locate the configuration file of Mysql (my.cnf in unix or my.ini in windows) and add the new 2 options with the following values on the mysqld block:

# The MySQL server
[mysqld]

innodb_file_per_table = 1
innodb_file_format = barracuda

Restart the mysql server and repeat the first step to see if the DYNAMIC and COMPACT row formats are available or running again the following instruction in the mysql terminal:

show variables like "%innodb_file%";

Which should output now:

Using the innodb_file_format with Barracuda as value, you will be able to use the required Dynamic row format on the table where you have this storage engine problem.

3. Change row format of the table to DYNAMIC

Now that you have the barracuda file format configured, simply change the table row format either with the tool in PHPMyAdmin (go to the table, then operations and search for Table options and change the row_format option):

Or if you can't use a tool, with an instruction on the mysql command line:

ALTER TABLE `mytable` ROW_FORMAT=DYNAMIC;

After modifying the row format of the table, saving a row on the database with a lot of data shouldn't be a problem anymore and you won't see this exception neither.

Happy coding !