SQL Row size too large

Alexandre

HI,

 

I got an SQL dataset that was working well for over a month and yesterday I got the error message: 

 

the database reported a syntax error: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. 

 

Looking at the raw data the biggest row contain around 6k character. I don't understant why I have the error message. I also tried the ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED but it didn't do anything.

 

Someone know how to remove the error?

 

thanks,

timehat

Column widths are going to be based on either the data type (LONG vs DATETIME vs etc.) or, for text columns, the width of the longest string found in the column across all rows. So though an individual row may be less than 8126, if you had two rows at 5000 each where 4999 was column A and 1 for column B for the first row and the inverse for the 2nd row (columnB is very wide), column A and B would both want to be 5000, but the sum of those two (10k) is greater than the 8126 limit.

amehdad

If it wouldn't be a lot of work to pull your data in between tables, you can split your dataset.

Another workaround is to change anywhere you have from varchar to text data type.

Or you can increase the value of the innodb_log_file_size (https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html#mysqld-5-6-20-innodb).

KenTravels

I am getting the same error, happen on a random day with no other input changes.

Nothing I do seems to fix this. changed all fields to Text, Still getting error.