MySQL Bugs: #30736: Row Size Too Large Error Creating a Table and Inserting Data

Bug #30736	Row Size Too Large Error Creating a Table and Inserting Data
Submitted:	30 Aug 2007 21:29	Modified:	9 Nov 2007 0:01
Reporter:	Charles Something	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0bk, 5.1bk	OS:	Linux (Debian Sid, but also Fedora Core 5, and Windows 2003)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	1118, CREATE TABLE, error, row size too large, SELECT

Description:
On a particular CREATE TABLE ... SELECT query, this error is thrown:
Error Code : 1118
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

But the row size is not large.  It is, in fact, an INT(10), a FLOAT, and two DECIMAL(10, 2)s.

A test case is provided in the "How to Repeat" section.  The test case has been tried on at least a 5.0.22, 5.0.24, and 5.1.16 server.  The samer error code was returned in each case.  A few variations of the query have been tried.  The original query had specified the IGNORE keyword before the SELECT and had parentheses around the two select statements.  This query complies with the syntax for CREATE TABLE ... SELECT as specified in documentation on the website.  It has been tried on 5 different MySQL implementations: some on Windows 2003, some on Debian Linux, and some on Fedora Core.  At least 3 different versions of MySQL have been tried: 5.0.22, 5.0.24, and 5.1.16.  The error is identical on all.

How to repeat:
TEST EQUIPMENT:
   This has been tried on several machines.  Here is relevant information from one of them:
'protocol_version','10'
'version','5.0.24-community-nt-log'
'version_comment','MySQL Community Edition (GPL)'
'version_compile_machine','ia32'
'version_compile_os','Win32'
'storage_engine','MyISAM'

TEST CASE:
/* Replaces invmaster table for portability in testing */
CREATE TABLE `a` (`stocknum` INT(10), PRIMARY KEY(`stocknum`));
/* Replaces lineitem table for portability in testing */
CREATE TABLE `b` (`invoicenum` INT(10), `itemnum` INT(10), `price` DECIMAL(10, 2), `quantity` FLOAT, `stocknum` INT(10), PRIMARY KEY(`invoicenum`, `itemnum`));
/* Replaces invoice table for portability in testing */
CREATE TABLE `c` (`invoicenum` INT(10), `document` VARCHAR(20), PRIMARY KEY(`invoicenum`));

/* The query that doesn't work on MySQL 5.0.24 */
CREATE TEMPORARY TABLE IF NOT EXISTS `f` 
(`stocknum` INT(10), `totalsold` DECIMAL(10, 2), `quantity` FLOAT, `cost` DECIMAL(10, 2), 
KEY(`stocknum`)) 
SELECT `a`.`stocknum` AS `stocknum`, SUM(`b`.`price` * `b`.`quantity`) 
AS `totalsold`, SUM(`b`.`quantity`) AS `quantity`, 0.00 AS `cost` 
FROM `a`
JOIN `b` USING (`stocknum`) 
LEFT JOIN `c` USING (`invoicenum`) 
WHERE `c`.`document` <> 'VOID' 
GROUP BY `a`.`stocknum`;

Suggested fix:
I don't know if the problem is in the parsing engine or elsewhere.  But there are at least two workarounds:

Workaround 1:
   Create the table with one CREATE query and insert the data with a second query using INSERT ... SELECT.

Workaround 2:
   Create the table and do the insert with a single CREATE TABLE ... SELECT query, but exclude the table definition clause.  If necessary, alter the columns and add indicies in subsequent queries.

on

with linux debug builds the code above crashes 
5.0bk/5.1bk with the following backtrace:

#0  0xffffe410 in __kernel_vsyscall ()
#1  0x4003b8b8 in pthread_kill () from /lib/tls/libpthread.so.0
#2  0x0840b322 in write_core (sig=6) at stacktrace.c:240
#3  0x0827ad05 in handle_segfault (sig=6) at mysqld.cc:2278
#4  <signal handler called>
#5  0xffffe410 in __kernel_vsyscall ()
#6  0x400f3541 in raise () from /lib/tls/libc.so.6
#7  0x400f4dbb in abort () from /lib/tls/libc.so.6
#8  0x400ec925 in __assert_fail () from /lib/tls/libc.so.6
#9  0x086ba257 in decimal_bin_size (precision=10, scale=31) at decimal.c:1462
#10 0x081dd48a in my_decimal_get_binary_size (precision=10, scale=31) at my_decimal.h:196
#11 0x08259fd9 in Create_field::create_length_to_internal_length (this=0x9081b30)
    at field.cc:9166
#12 0x083ad738 in mysql_prepare_create_table (thd=0x9069810, create_info=0x42720670, 
    alter_info=0x42720b20, tmp_table=false, db_options=0x4271f694, file=0x9089ba0, 
    key_info_buffer=0x4271f68c, key_count=0x4271f690, select_field_count=4)
    at sql_table.cc:2383
#13 0x083afde3 in mysql_create_table_no_lock (thd=0x9069810, db=0x907a2e0 "test", 
    table_name=0x907a0e8 "f", create_info=0x42720670, alter_info=0x42720b20, 
    internal_tmp_table=false, select_field_count=4) at sql_table.cc:3310
#14 0x083194de in create_table_from_items (thd=0x9069810, create_info=0x42720670, 
    create_table=0x907a110, alter_info=0x42720b20, items=0x906a968, lock=0x9069868, 
    hooks=0x427200ac) at sql_insert.cc:3345
#15 0x08319a56 in select_create::prepare (this=0x907ff80, values=@0x906a968, u=0x906a66c)
    at sql_insert.cc:3489
#16 0x0830686c in JOIN::prepare (this=0x9080008, rref_pointer_array=0x906a9d8, 
    tables_init=0x907ae88, wild_num=0, conds_init=0x907bab0, og_num=1, order_init=0x0, 
    group_init=0x907bcd0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x906a8d4, 
    unit_arg=0x906a66c) at sql_select.cc:609
#17 0x0830e71e in mysql_select (thd=0x9069810, rref_pointer_array=0x906a9d8, 
    tables=0x907ae88, wild_num=0, fields=@0x906a968, conds=0x907bab0, og_num=1, order=0x0, 
    group=0x907bcd0, having=0x0, proc_param=0x0, select_options=2416200192, 
    result=0x907ff80, unit=0x906a66c, select_lex=0x906a8d4) at sql_select.cc:2253
#18 0x0830eaf7 in handle_select (thd=0x9069810, lex=0x906a610, result=0x907ff80, 
    setup_tables_done_option=0) at sql_select.cc:262
#19 0x0828ff19 in mysql_execute_command (thd=0x9069810) at sql_parse.cc:2291
#20 0x08297b3d in mysql_parse (thd=0x9069810, 
    inBuf=0x9079d50 "CREATE TEMPORARY TABLE IF NOT EXISTS `f` \n(`stocknum` INT(10), `totalsold` DECIMAL(10, 2), `quantity` FLOAT, `cost` DECIMAL(10, 2),\nKEY(`stocknum`)) \nSELECT `a`.`stocknum` AS `stocknum`, SUM(`b`.`pric"..., length=418, found_semicolon=0x42721310)
#21 0x08298661 in dispatch_command (command=COM_QUERY, thd=0x9069810, 
    packet=0x9071cf1 "CREATE TEMPORARY TABLE IF NOT EXISTS `f` \n(`stocknum` INT(10), `totalsold` DECIMAL(10, 2), `quantity` FLOAT, `cost` DECIMAL(10, 2),\nKEY(`stocknum`)) \nSELECT `a`.`stocknum` AS `stocknum`, SUM(`b`.`pric"..., packet_length=419) at sql_parse.cc:958
#22 0x082997ef in do_command (thd=0x9069810) at sql_parse.cc:717
#23 0x0828766f in handle_one_connection (arg=0x9069810) at sql_connect.cc:1094
#24 0x40038297 in start_thread () from /lib/tls/libpthread.so.0
#25 0x4018a37e in clone () from /lib/tls/libc.so.6
#26 0x42721bb0 in ?? ()

Assertion failure, crash -> p1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36144

ChangeSet@1.2543, 2007-10-23 18:03:51+04:00, anozdrin@station. +3 -0
  Patch for BUG#30736: Row Size Too Large Error Creating a Table and
  Inserting Data.
  
  The problem was that under some circumstances Field class was not
  properly initialized before calling create_length_to_internal_length()
  function, which led to assert failure.
  
  The fix is to do the proper initialization.
  
  The user-visible problem was that under some circumstances
  CREATE TABLE ... SELECT statement crashed the server or led
  to wrong error message (wrong results).

Pushed into 5.0-runtime.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.52

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

Under some circumstances, CREATE TABLE ... SELECT could crash thes
erver or incorrectly report that the table row size was too large.