Row size too large (> 8126) Hibernate

Question

I have a problem - I get an "Row size too large (> 8126)." error when trying to create my database table with Hibernate (I'm using the hibernate.hbm2ddl.auto property on "create" to do that).

The issue is that I have a few columns that are supposed to be TEXT, not VARCHAR. Apparently, the rows on these columns are too large and I get the above error.

The way I have Hibernate configured is using mapping files.

Here's how one of the mapped properties looks like:

<property name="transmission_current_range" type="java.lang.String" length="65535">
        <column name="TRANSMISSION_CURRENT_RANGE"/>
</property>

Notice I set the length to 65535 in the hope that that will solve my problem, but it doesn't.

How do you tell Hibernate that you want this column to be a TEXT, not a VARCHAR? I've seen examples using adnotations, but I want to do the same using my mapping file.

Also, what's the corresponding java type for TEXT? I searched for this but I found only java.lang.String, which is the same data type used for both VARCHAR and TEXT, so it doesn't help.

EDIT: Apparently, this issue is not because I need to change the data into "TEXT" instead of "String, but because I have so many columns that each line (row) exceeds the limit of 8126.

I get this error, now:

"Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs"

EDIT2: Apparently, the reason for this error is the fact that I have so many columns that the line length becomes way too big. So it's not a matter of having TEXT instead of VARCHAR per se, but instead, I simply have too much "data" in terms of column length.

Changing the innodb_strict_mode = 0 in the mysql cfg file solved the problem, but when I let Hibernate create the database again, automatically, the columns created were BLOB, not their original data types (some of them still maintained to be their original data type).

This just as a heads up for anybody choosing my solution.

score 0 · Answer 1 · answered Mar 15 '17 at 14:05

0

Use text instead of java.lang.String:

<property name="transmission_current_range" 
    type="text" length="65535">
        <column name="TRANSMISSION_CURRENT_RANGE"/>
</property>

answered Mar 15 '17 at 14:05

nobeh

9,784
10
49
66

I changed all the previous "java.lang.String" types to exactly what you have above, and I still get the error. Now the columns generated are of type "longtext", but I get the same error. – Robert Ruxandrescu Mar 15 '17 at 14:12
1

OK, this is good. This now means that you DB engine needs more configuration; for example in case of MySQL: http://stackoverflow.com/q/15585602/248082 – nobeh Mar 15 '17 at 14:14
I made my modifications according to the link you provided, however now there's another problem: since I'm using Hibernate to automatically create the table, it destroys the one I'm creating and altering like per that link, and creates a new one from scratch. Now, I want Hibernate to do that, but I just want to update my columns, not destroy the entire table and then re-create it, since what it's creating doesn't have the `ALTER TABLE nombre_tabla ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;` code that takes care of my problem. – Robert Ruxandrescu Mar 15 '17 at 14:42
I set Hibernate to update my table instead of destroying and re-creating it, and now it generates ~200 columns or so, but I hit one of them and I get this error: "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs" I've set the column to be "text", but it still doesn't work. – Robert Ruxandrescu Mar 15 '17 at 15:02

score 0 · Answer 2 · answered Mar 15 '17 at 14:06

0

Hibernate documentation mentions sql-type of a <column> element. You can try with:

<column name="TRANSMISSION_CURRENT_RANGE" sql-type="text"/>

answered Mar 15 '17 at 14:06

veljkost

1,748
21
25

Tried your advice as well, I get the same error. This doesn't make any sense. Also, Hibernate's suggestion is so vague: "Row size too large (> 8126). Changing some columns to TEXT or BLOB may help." It doesn't even say which columns don't work. – Robert Ruxandrescu Mar 15 '17 at 14:14

Robert Ruxandrescu · Accepted Answer · 2017-03-16T15:54:38.787

0

OK, I've managed to solve my problem. The solution was this line, inserted in the "/etc/mysql/my.cnf" file, in the [mysqld] category:

innodb_strict_mode = 0

That's it. Now I can insert all my columns and I don't get the error anymore.

edited Mar 16 '17 at 15:54

answered Mar 16 '17 at 12:06

Robert Ruxandrescu

627
2
10
22

Row size too large (> 8126) Hibernate

3 Answers3