MySQL Bugs: #230: UPDATE using 2 tables / ERROR 1114: The table is full

Bug #230	UPDATE using 2 tables / ERROR 1114: The table is full
Submitted:	4 Apr 2003 8:51	Modified:	4 Apr 2003 9:03
Reporter:	Gerrit Hannaert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
The problem occurs when updating two tables (new in MySQL4), and results in a "table full" warning if the number of rows exeeds a certain number (it didn't happen with a small number of rows). There should be no error since this is an update and seemingly no limits (disk, memory) seem to have been hit.

Also : none of the suggestions in the online documentation around "table is full" helped, because this was not possible in MySQL3. I also tried filling around with a number of 'max_' options in my.cnf, they don't help.

This happens with both MyISAM and InnoDB table formats, I've appended another user's query + SHOW TABLE STATUS from the mailing list below (note the similarities).

UPDATE a, b
SET a.date_out='2003-04-03 15:48:06',
a.is_dirty_date_out='Y'
WHERE a.file_id=b.file_id
AND date_out='2099-12-31'
AND file_mdate >= '2002-04-03 15:48:06'
AND a.storage_id='6';

ERROR 1114: The table 'a' is full

- a currently contains 1,419,724 rows
- b currently contains 825,770 rows
- the disk is in no way full, and both tables apart take up at most a 
few 100 MB. The machine has 1GB of RAM.

- the SHOW TABLE STATUS FROM db LIKE 'a' output:
| Name             | Type   | Row_format | Rows    | Avg_row_length
| Data_length | Max_data_length | Index_length | Data_free | Auto_increment
| Create_time | Update_time | Check_time | Create_options     | 
Comment                |

| a | InnoDB | Fixed      | 1411497 |             97
|   137003008 |            NULL |    153534464 |         0 |        1419728
| NULL        | NULL        | NULL       | max_rows=100000000 | InnoDB 
free: 294912 kB |

LIKE 'b' output:

| b | MyISAM | Dynamic    | 843500 |            158 
|   133901532 |      4294967295 |    218467328 |         0 |         843547 
| 2003-02-28 18:42:09 | 2003-04-04 04:26:48 | 2003-04-03 13:33:48 | checksum=1     |  |

------- OTHER USER ----

List:     mysql
Subject:  have a problem
From:     PandaCoop-Krasimir_Slaveykov  <krasi () hq ! panda ! bg>
Date:     2003-04-02 10:10:52

update table1 as a, table2 as b set a.field1=b.field1
where a.field2=b.field2 and a.field3=b.field3 and a.field4=b.field4

| linii | MyISAM |  Dynamic    | 1194745 |            130
|   155429332 |      4294967295 |      42998784 |         0 | 3472328119327703312
| 2003-04-02 10:48:27 | 2003-04-02  12:17:14 | 2003-04-02 10:50:26 |                |         |

How to repeat:
Presumably create two tables with similar Avg_row_length and number of rows as above, and try a similar query.

Suggested fix:
At least provide a more clear error message indicating exactly what is full, or why MySQL thinks it is full...

Yes, this is a known problem.
It is already fixed in 4.0.13, which will come out this month.

This is a patch:

===== sql/sql_update.cc 1.79 vs 1.80 =====
*** /tmp/sql_update.cc-1.79-11704       Wed Mar 19 00:45:43 2003
--- 1.80/sql/sql_update.cc      Wed Apr  2 17:05:30 2003
***************
*** 741,747 ****
          (error != HA_ERR_FOUND_DUPP_KEY &&
           error != HA_ERR_FOUND_DUPP_UNIQUE))
        {
!       if (create_myisam_from_heap(table, tmp_table_param + offset, error, 1))
        {
          do_update=0;
          DBUG_RETURN(1);                       // Not a table_is_full error
--- 741,747 ----
          (error != HA_ERR_FOUND_DUPP_KEY &&
           error != HA_ERR_FOUND_DUPP_UNIQUE))
        {
!       if (create_myisam_from_heap(tmp_table, tmp_table_param + offset, error, 1))
        {
          do_update=0;
          DBUG_RETURN(1);                       // Not a table_is_full error

- SET BIG_TABLES=1 does not help
- Using two InnoDB tables or two MyISAM tables does not help

- The query takes almost 15 seconds before failing. During this time the 'mysqladmin processlist -p' status shows the state "Sending data" (so it's not copying to a tmp table?)

Oops, didn't see your comment. Thanks a lot, very helpful!