Bug #21101 Wrong error on exceeding max row size for InnoDB table
Submitted: 17 Jul 2006 19:23 Modified: 19 Jun 2010 18:03
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.24, 5.1.11 OS:Any
Assigned to: Sunny Bains CPU Architecture:Any

[17 Jul 2006 19:23] Hakan Küçükyılmaz
Description:
Create table inside a stored procedure does fail with InnoDB but not with MyISAM.

How to repeat:
Run following test case:

SET storage_engine = InnoDB;
--echo *** Bug #158 ***
--disable_warnings
DROP TABLE IF EXISTS tj9;
DROP PROCEDURE IF EXISTS pj9;
--enable_warnings

delimiter //;

create procedure pj9 ()
begin 
  declare v int default 1; 
  drop table if exists tj9; 
  create table tj9 (s0 int); 
  while v < 500 do 
    select 'alter',v; 
    set @v = concat('alter table tj9 add column s',v,' char(64) default ''Hello World'''); 
    prepare stmt1 from @v; 
    execute stmt1; 
    set v = v + 1; 
  end while; 
  set v = 1; 
  while v < 500 do 
    select 'insert',v; 
    insert into tj9 (s1) values (v); 
    set v = v + 1; 
  end while; 
  set v = 1; 
  while v < 500 do 
    select 'update',v; 
    set @v = concat('update tj9 set s',v,'=concat(s2,s1)'); 
    prepare stmt1 from @v; 
    execute stmt1; 
    set v = v + 1; 
  end while; 
end//

call pj9()//

# Final cleanup
DROP TABLE tj9//
DROP PROCEDURE tj9//

delimiter ;//

If I set
SET storage_engine = MyISAM;
it works.
[18 Jul 2006 8:30] Sveta Smirnova
Verified on last 5.0 and 5.1 BK sources.

Additional info:

table inside SP creates successfully, but when runs "ALTER" cicle, I get error: "ERROR 1005 (HY000): Can't create table 'test.#sql-24d8_2' (errno: 139)":

...

+-------+------+
| alter | v    |
+-------+------+
| alter |  124 |
+-------+------+
1 row in set (14.29 sec)

+-------+------+
| alter | v    |
+-------+------+
| alter |  125 |
+-------+------+
1 row in set (14.40 sec)

ERROR 1005 (HY000): Can't create table 'test.#sql-24d8_2' (errno: 139)

If I set ENGINE to MyISAM, SP finishes fine.
[1 Sep 2006 10:16] Tomash Brechko
According to the manual, for InnoDB tables 'the maximum row length is about 8000 bytes', whereas for MyISAM it's 64K.  In the example, 4 + 64*124 = 7940, so on iteration number 125 you hit the maximum.  The workaround would be to use CHAR columns with smaller width, or use VARCHAR columns instead (in this case, you'd probably have to increase innodb_log_file_size).

So the bug is that error message is not clear/misleading.
[1 Sep 2006 10:55] Tomash Brechko
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
        c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
        c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
        c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
        c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
        c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
        c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
        c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
        c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
) ENGINE = InnoDB;

ERROR 1005 (HY000) at line 3: Can't create table './test/t1.frm' (errno: 139)

But should be:

ERROR 1118 (42000) at line 3: Row size too large. The maximum row size for the used table type, not counting BLOBs, is <about 8000>. You have to change some columns to TEXT or BLOBs
[1 Sep 2006 11:03] Konstantin Osipov
Elliot, this is storage-layer specific, please reassign.
[1 Sep 2006 11:10] Heikki Tuuri
Hi!

139 is HA_ERR_TOO_LONG_ROW (spelling?). The MySQL interpreter should convert it to a human-readable error code when it passes the error to the user. Can InnoDB somehow do that using some new error function?

Regards,

Heikki
[16 Apr 2007 11:51] Heikki Tuuri
Sunny has a patch for this now.
[12 Jul 2007 17:59] Timothy Smith
Queued to 5.1-maint team tree(s)
[19 Jul 2007 15:48] Bugs System
Pushed into 5.1.21-beta
[23 Jul 2007 20:00] Paul DuBois
Noted in 5.0.21 changelog.

InnoDB displayed an incorrect error message when a CREATE TABLEstatement exceeded the InnoDB maximum allowable row size.
[5 May 2010 15:02] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:17] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:47] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:44] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:47] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:48] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:25] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:12] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)