com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large (> 8126)

標籤：mysql row size too large (> 8126)

同樣是工作中遇到的問題，寫下來留個紀念。

一個老系統已經跑了一年多了，最近報出這個問題的，具體問題貼出來給大家看

org.springframework.jdbc.BadSqlGrammarException: 

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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.

### The error occurred while setting parameters

### SQL: update test    SET product_id=?,    sp_id=?,    key1=?,    key2=?,    key3=?,    key4=?,    key5=?,    key6=?,    key7=?,    key8=?,    key9=?,    key10=?,    key11=?,    key12=?,    key13=?,    key14=?,    key15=?,    key16=?,    key17=?,    key18=?,    key19=?,    key20=?,    key21=?,    key22=?,    key23=?,    key24=?,    key25=?,    key26=?,    key27=?,    key28=?,    key29=?,    key30=?,    gmt_modify=?,    json_data=?,    record_no=?,    hash_no=?,    gmt_create=?,    is_repack=?    where ID=?

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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.

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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.

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:95)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)

at com.sun.proxy.$Proxy18.update(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:254)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:49)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)

at com.sun.proxy.$Proxy27.update(Unknown Source)

at com.ancun.webank.biz.service.TestRowSize.testCompBqData(TestRowSize.java:109)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)

at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)

at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:232)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)

at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)

at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)

at org.junit.runners.ParentRunner.run(ParentRunner.java:309)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:175)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

由於test表中儲存了一個text欄位用於接收非結構化資料，並且預留了30個欄位用於解析這個非結構化資料。在解析這個text欄位時報上述錯誤，行大小超過限制8192。

通過SHOW VARIABLES LIKE ‘%engine%‘，確認目前資料庫用的是innodb的引擎。

通過innodb_page_size可以確認每頁大小為16k；

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M01/8E/DB/wKiom1jNGkXyMbeZAAAihW5X1_4742.png-wh_500x0-wm_3-wmp_4-s_674892384.png" title="QQ20170318192958.png" style="float:left;" alt="wKiom1jNGkXyMbeZAAAihW5X1_4742.png-wh_50" />

INNODB儲存引擎是索引組織的，即每頁中至少有兩行記錄，因此如果頁中只能存放一行記錄，INNODB會自動將行資料放到溢出頁中。當發生溢出行的時候，實際資料儲存在BLOB頁中，資料頁只儲存資料的前768位元組（老的檔案格式），新的檔案格式（Barracuda）採用完全行溢出的方式，資料頁只儲存20個位元組的指標，BLOB也儲存所有資料。

所以每個row的zise不能大於8k，即8192個位元組。

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large (> 8126)