MySQL Bugs: #21101: Wrong error on exceeding max row size for InnoDB table

Bug #21101	Wrong error on exceeding max row size for InnoDB table
Submitted:	17 Jul 2006 21:23	Modified:	23 Jul 2007 22:00
Reporter:	Hakan Kuecuekyilmaz
Status:	Closed
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.0.24, 5.1.11	OS:	Any
Assigned to:	Sunny Bains	Target Version:

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.

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.

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.

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

Elliot, this is storage-layer specific, please reassign.

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

Sunny has a patch for this now.

Queued to 5.1-maint team tree(s)

Pushed into 5.1.21-beta

Noted in 5.0.21 changelog.

InnoDB displayed an incorrect error message when a CREATE TABLEstatement exceeded the
InnoDB maximum allowable row size.