MySQL Bugs: #60698: Row size too large when we try to update the table

Bug #60698	Row size too large when we try to update the table
Submitted:	30 Mar 2011 9:52	Modified:	7 Nov 2015 7:43
Reporter:	Cyril SCETBON	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.9	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any
Tags:	DML, innodb, row size too large

Description:
I get an error when I try to update a field from one of my table :

mysql> UPDATE table1 SET field1='AU2002236653A' WHERE field1='AU2002236653';
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs

However, If I create another table from this one I can update the field !!

mysql> create table t1 like table1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 select * from table1;
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> UPDATE t1 SET ApplicationRef='AU2002236653A' WHERE ApplicationRef='AU2002236653';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

How to repeat:
I don't know how to repeat it as if I copy table I do not get the error anymore

Please, send the output of SHOW TABLE STATUS for the problematic table and for the copy that allows UPDATE to work.

mysql> show table status like 'table1'\G
*************************** 1. row ***************************
           Name: table1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 13
 Avg_row_length: 1260
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-03-28 09:01:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 13
 Avg_row_length: 1260
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-03-30 11:01:32
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Thank you for the feedback.

Do you use --innodb-file-per-table option? What CHECK TABLE problem_table returns?

yes I use innodb-file-per-table
I did the check table and it says ok.
However, now the update statement works although I didn't do anything on the table and the status command still returns the same output

Thank you for the feedback.

When issue happens next time please send us your *ibd, *frm files for problem table and configuration file.

did you find anything ?

Thank you for the feedback.

I am sorry for the delay: I was on leave.

What I got with table provided and version 5.5.10:

slave1 [localhost] {msandbox} (test) > show table status like 'table1'\G
*************************** 1. row ***************************
           Name: table1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 13
 Avg_row_length: 1260
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-05-05 13:34:07
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select count(*) from table1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > UPDATE table1 SET ApplicationRef='AU2002236653A' WHERE ApplicationRef='AU2002236653';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

slave1 [localhost] {msandbox} (test) > select count(*) from table1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > create table test1 like table1;
Query OK, 0 rows affected (0.01 sec)

slave1 [localhost] {msandbox} (test) > insert into test1 select * from table1;
Query OK, 13 rows affected (0.01 sec)
Records: 13  Duplicates: 0  Warnings: 0

slave1 [localhost] {msandbox} (test) > select count(*) from test1;
+----------+
| count(*) |
+----------+
|       13 |
+----------+
1 row in set (0.00 sec)

So is interesting why count(*) differs from SHOW TABLE STATUS output and from real number of rows. Also is interesting how such a table was created.

CHECK TABLE works for me though:

slave1 [localhost] {msandbox} (test) > check table table1;
+----------------------+-------+----------+-------------------------------------------------------------------------------------------+
| Table                | Op    | Msg_type | Msg_text                                                                                  |
+----------------------+-------+----------+-------------------------------------------------------------------------------------------+
| test.table1 | check | Warning  | InnoDB: The B-tree of index 'PRIMARY' is corrupted.                                       |
| test.table1 | check | Warning  | InnoDB: Index 'eedRecalculationIndex' contains 1 entries, should be 18446744073709551615. |
| test.table1 | check | error    | Corrupt                                                                                   |
+----------------------+-------+----------+-------------------------------------------------------------------------------------------+
3 rows in set (0.05 sec)

slave1 [localhost] {msandbox} (test) > repair table LgstApplication;
+----------------------+--------+----------+---------------------------------------------------------+
| Table                | Op     | Msg_type | Msg_text                                                |
+----------------------+--------+----------+---------------------------------------------------------+
| test.table1 | repair | note     | The storage engine for the table doesn't support repair |
+----------------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > alter table table1 engine=innodb;
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

slave1 [localhost] {msandbox} (test) > select count(*) from table1;
+----------+
| count(*) |
+----------+
|       13 |
+----------+
1 row in set (0.00 sec)

Cyril,

please update to latest version 5.5.11 and try in your environment if you get same results as me and CHECK TABLE started work too. Please inform us about results.

For now, I can't cause we don't work on it and don't have the issue. However as you have the files can't you try with 5.5.9 ? Something weird is that we don't know why the table becomes corrupted :( (no crash, nothing in the logs). I'll let you know if we meet it again. Do you have an explanation that could explain this issue ?

Thank you for the feedback.

5.5.9 works same as 5.5.10 for me. Which exact MySQL package do you use? (file name you downloaded)

it's compiled from the 5.5.9 sources :(

Note that 18446744073709551615 is -1 when interpreted as a signed 64-bit integer. This could be coming from the constant ULINT_UNDEFINED or ULINT64_UNDEFINED.

Is there a test I can do to check it ? using a simple sql query maybe ?

I finally managed to load the table. Did you look at the error log? It should contain something like this:

InnoDB: Summed data size 782, returned by func 795
InnoDB: Apparent corruption in space 1145 page 3 index PRIMARY
110509 12:18:54  InnoDB: Page dump in ascii and hex (16384 bytes):
[...]
InnoDB: End of page dump
110509 12:18:54  InnoDB: Page checksum 3466041520, prior-to-4.0.14-form checksum 515413889
InnoDB: stored checksum 3466041520, prior-to-4.0.14-form stored checksum 515413889
InnoDB: Page lsn 10 490896126, low 4 bytes of lsn at page end 490896126
InnoDB: Page number (if stored to page already) 3,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 1145
InnoDB: Page may be an index page where index id is 1943
InnoDB: Error in page 3 of index "PRIMARY" of table "test"."LgstApplication"

On a 32-bit system, the message returned to the client is a bit different, featuring a numeric constant 1<<32-1 instead of 1<<64-1:

| test.LgstApplication | check | Warning  | InnoDB: Index 'eedRecalculationIndex' contains 0 entries, should be 4294967295. |

I think that we should treat the ULINT_UNDEFINED specially in the error message.

But, the root cause is the corrupted table. How was the file created? If it was not copied while the MySQL server was running, then this could be a serious bug in MySQL. Otherwise, it could be a bug in the backup/restore solution.

Note: InnoDB stores the data in the clustered index, ordered by the PRIMARY KEY. When you copy the table, you would scan only the clustered index, skipping any corruption in the secondary indexes.

I would like to know what caused the corruption (or the mismatch between the secondary index and the clustered index) in the first place.

The data has just been inserted using INSERT INTO statements
however I copied the file when the server was running (but without transactions started) to send it to you (after having met the error).
Concerning the error log, I 've checked it and did not see any error concerning my table

If you copied the files from a running server, there is a slight chance that the server was just performing purge or insert buffer merge on the table when you copied the file. It is also possible that some pages were "dirty" in the buffer pool, that is, they contained changes that were not yet (or were just being) written to the file when you copied it. Copying InnoDB *.ibd files is completely safe only if the server was shut down with innodb_fast_shutdown=0.

Can you send the error log?

To fix this bug, we'd need a complete set of SQL statements for creating and populating the table. It should not be too hard, given that the .ibd file is so small.

I understand for the possibility that threads are writing in the ibd file. However as you have the same error, it seams that we're working on the same set of data.

Sorry but I don't have the statements to reproduce the error :(

Cyril, are there any triggers on this table?

none

Cyril, to save me some trouble, can you please attach a SQL dump from the *.ibd file so that we can try to run experiments ourselves? I had to jump through some hoops to load the file, and I already deleted the datadir in order to work on something else.

Marko, I'm really sorry but I don't have it anymore. I've sent you the file as soon as I've been asked to, and the developper has erased it and does not work currently on it. He may in 1/2 week(s) begin to work again on it and meet the error, but that's not actually the case.

I replayed the file again:

seq -f 'create table t(a%g int primary key)engine=innodb;drop table t;' 1 1144|
mysql -uroot test

Then I replayed the CREATE TABLE statement, shut down the server, replaced the .ibd and .frm file, and restarted. Got a debug assertion failure. Disabled UT_DBG_PANIC so that I could do SHOW CREATE TABLE.

Then, I started from the scratch, now using the SHOW CREATE TABLE output from the .frm file. Still, the index->id would be different and an assertion would fail.

First, the table looked like empty, which is because the DB_TRX_ID was too high for this new server. So, I used SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and repeated it.

I will attach the SQL for creating and populating the table, in a private attachment. The bug is not reproducible by that. We really need all SQL statements for that. If you have some script or templates for generating the SQL, that would be useful.

I cannot repeat the CHECK TABLE failure. I bumped trx_sys->max_trx_id to a higher value than contained in the .ibd file, so that SELECT would return all 13 rows. (Without this, CHECK TABLE would see an empty table.)

I'll wait one or 2 weeks to see if the developper meet this error again and send you the SQL he uses. However, I know that he doesn't know how to reproduce the error, so I'll send you SQL statements that do not always (I hope more than 1% of the time) generate the error.

Cyril,

thanks in advance. Your compiling options can be useful too.

here are the compiling options : 

cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql5.5.9  
-DWITH_ARCHIVE_STORAGE_ENGINE=1 
-DWITH_FEDERATED_STORAGE_ENGINE=1

we'll soon use the mysql compiled binary as some options were disabled cause of missing development header files

Hello,

we are using mysql 5.1.55 and experience a similar issue.

The table is in ROW_FORMAT=dynamic and has maybe a max row size of 2000 but we still get errors of type:

[ERROR] Slave SQL: Error 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs' on query. Default database: 'zankyou'. Query: 'UPDATE `events` SET `code` = 'thereisnoother', `code_language` = 'es', `id_zone_event` = NULL, `temp` = '0', `main_service` = 'standard' WHERE (code = 'tmpwusm7g1zedgbiwqlfgokt')', Error_code: 1118

CHECK TABLE events reports OK.

This only happens sporadicly and is not reproduceable.

Here http://forums.innodb.com/read.php?3,850,857#msg-857 I read something about the error messaging being "wrong" that it could be an index problem instead of a row size issue?

Help would be appreciated.

Thanks,
Arno

Ahhh ... the error is resolved by doing a:

ALTER TABLE events modify "whatevercolumn" ....;

OR

ALTER TABLE events Engine=InnoDB;

The later one once in a while crashes the server instance with an error like this:

InnoDB: Assertion failure in thread 140651524400896 in file row/row0upd.c line 988
InnoDB: Failing assertion: uf->orig_len > BTR_EXTERN_FIELD_REF_SIZE
....
Thread pointer: 0x7fec002b6a90
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7febfc261e88 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8fba29]
/usr/sbin/mysqld(handle_segfault+0x40b) [0x5e8ddb]
/lib/libpthread.so.0(+0xef60) [0x7feda8467f60]
/lib/libc.so.6(gsignal+0x35) [0x7feda6d03165]
/lib/libc.so.6(abort+0x180) [0x7feda6d05f70]
/usr/sbin/mysqld() [0x801cee]
/usr/sbin/mysqld() [0x801f15]
/usr/sbin/mysqld() [0x83b721]
/usr/sbin/mysqld() [0x80032a]
/usr/sbin/mysqld() [0x803812]
/usr/sbin/mysqld() [0x80404e]
/usr/sbin/mysqld() [0x7edfb6]
/usr/sbin/mysqld() [0x7e57e3]
/usr/sbin/mysqld() [0x7e654f]
/usr/sbin/mysqld() [0x8021f5]
/usr/sbin/mysqld() [0x803b88]
/usr/sbin/mysqld() [0x80404e]
/usr/sbin/mysqld() [0x7eea71]
/usr/sbin/mysqld() [0x7988fc]
/usr/sbin/mysqld(handler::ha_update_row(unsigned char const*, unsigned char*)+0x7f) [0x6e2c2f]
/usr/sbin/mysqld(mysql_update(THD*, TABLE_LIST*, List<Item>&, List<Item>&, Item*, unsigned int, st_order*, unsigned long long, enum_duplicates, bool)+0xe26) [0x67f006]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0xa5f) [0x5fbd0f]
/usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x54f) [0x600cdf]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x485) [0x601185]
/usr/sbin/mysqld(do_command(THD*)+0x12a) [0x60243a]
/usr/sbin/mysqld(handle_one_connection+0x34e) [0x5f44ae]
/lib/libpthread.so.0(+0x68ba) [0x7feda845f8ba]
/lib/libc.so.6(clone+0x6d) [0x7feda6da002d]

I just got Arno's above assertion on 5.6.3 dynamic table.

InnoDB: Assertion failure in thread 1259559232 in file row0upd.c line 1021
InnoDB: Failing assertion: uf->orig_len > BTR_EXTERN_FIELD_REF_SIZE

Crashing statement was:
update ignore `blobtest_6` set `data2`='2805' where `id`<=62024 limit 8

However it wasn't repeatable.  I did get a corefile though.

gdb infos from corefile.

Attachment: bug60698_arno_infos.txt (text/plain), 51.61 KiB.

I just had this happen tonight on mysql-advanced-5.5.15 on CentOS 6.2.  The really odd thing is that I have a master and two slaves...and this only happened on one of the slaves. An OPTIMIZE TABLE fixed the problem.  I took an SQL dump and a have the query that broke replication, but I didn't think to save the idb file.  Bag bug reporting on my part.

The question is, though, is this bug ever resolved anywhere?  This is an extremely low-traffic server on a tiny table..doing nothing special as far as I know. The two slaves are VMWare clones of each other, so there are no configuration differences, and they're dedicated to this database.

Thoughts from anybody?

Thanks!

Wes

as far as I know all blob related corruptions/crashes after fixed in current version (5.6.27+)  There were many.   Some examples (internal bug numbers)

Bug 13721257 - RACE CONDITION IN UPDATES OR INSERTS OF WIDE RECORDS
Bug 12704861 - CORRUPTION AFTER A CRASH DURING BLOB UPDATE
Bug 16971045 - ASSERTION FAILURES ON ROLLBACK OF AN INSERT AFTER A FAILED BLOB WRITE