MySQL Bugs: #27643: query failed : 1114 (The table '' is full)

Bug #27643	query failed : 1114 (The table '' is full)
Submitted:	4 Apr 2007 12:13	Modified:	19 Jun 2007 0:44
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0BK	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any
Tags:	bfsm_2007_04_19, bfsm_2007_05_31

Description:
When having a table with some UTF8 columns, with an index on them, some queries return an error, 1114 (The table '' is full) when doing a group by and sort.

I could only get it to happen on relatively large tables so far.

This seems like an incorrect error message since it doesn't say what table is full.  Also, tmpdir has lots of free space, that's not a problem.

How to repeat:
Compile and run the attached testcase.
It should fail around here:

running initializations..
about to spawn 1 threads

completed spawning new database worker threads
testcase is now running, so watch for error output
query failed : 1114 (The table '' is full)
failure on iteration 136
query failed : 1114 (The table '' is full)
failure on iteration 137
query failed : 1114 (The table '' is full)

testcase. see top of file for host, user, password, gcc compile example.

Attachment: bug27643.c (text/plain), 7.60 KiB.

sbester@www:~> gcc bug27643.c -g -o bug27643 -L/home/sbester/server/5.0/mysql-5.0.36-linux-i686/lib -I/home/sbester/server/5.0/mysql-5.0.36-linux-i686/include   -lmysqlclient_r -lz -lpthread
sbester@www:~> ./bug27643 
running initializations..
about to spawn 1 threads

completed spawning new database worker threads
testcase is now running, so watch for error output
query failed : 1114 (The table '' is full)
failure on iteration 136
query failed : 1114 (The table '' is full)
failure on iteration 137
query failed : 1114 (The table '' is full)
failure on iteration 138

Better testcase:

set session tmp_table_size=1024;
set session max_heap_table_size=16384;
drop table if exists test_table;
CREATE TABLE test_table_is_full(
test_col1 varchar(32) character set utf8 collate utf8_bin NOT NULL,
test_col2 varchar(32) character set utf8 collate utf8_bin NOT NULL )
ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test_table_is_full values
('AAAAAAAAAA','AAAAAAAAAA'),
('AAAAAAAAAB ','AAAAAAAAAB '),
('AAAAAAAAAB','AAAAAAAAAB'),
('AAAAAAAAAC','AAAAAAAAAC'),
('AAAAAAAAAD','AAAAAAAAAD'),
('AAAAAAAAAE','AAAAAAAAAE'),
('AAAAAAAAAF','AAAAAAAAAF'),
('AAAAAAAAAG','AAAAAAAAAG'),
('AAAAAAAAAH','AAAAAAAAAH'),
('AAAAAAAAAI','AAAAAAAAAI'),
('AAAAAAAAAJ','AAAAAAAAAJ'),
('AAAAAAAAAK','AAAAAAAAAK');

SELECT MAX( test_col1 ) FROM test_table_is_full GROUP BY test_col1,test_col2;

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/26709

ChangeSet@1.2478, 2007-05-15 18:45:35+04:00, kaa@polly.local +4 -0
  Fix for bug #27643 "query failed : 1114 (The table '' is full)
  
  Problem:
  
  HASH indexes on VARCHAR columns with the utf8_bin collation did not remove trailing spaces from strings before comparisons. This could result in duplicate records being successfully inserted into a MEMORY table with unique key constraints.
  
  As a direct consequence of the above, internal MEMORY tables used for GROUP BY calculation in testcases for bug #27643 contained duplicate rows which resulted in duplicate key errors when converting those temporary tables to MyISAM. Additionally, that error was incorrectly converted to the 'table is full' error.
  
  Solution:
  
  - remove trailing spaces from strings before calculating hashes.
  - return a proper error from create_myisam_from_heap() when conversion fails.

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/26903

ChangeSet@1.2478, 2007-05-17 15:33:45+04:00, kaa@polly.local +5 -0
  Fix for bug #27643 "query failed : 1114 (The table '' is full)
  
  Problem:
  
  HASH indexes on VARCHAR columns with binary collations did not remove trailing spaces from strings before comparisons. This could result in duplicate records being successfully inserted into a MEMORY table with unique key constraints.
  
  As a direct consequence of the above, internal MEMORY tables used for GROUP BY calculation in testcases for bug #27643 contained duplicate rows which resulted in duplicate key errors when converting those temporary tables to MyISAM. Additionally, that error was incorrectly converted to the 'table is full' error.
  
  Solution:
  
  - remove trailing spaces from strings before calculating hashes.
  - return a proper error from create_myisam_from_heap() when conversion fails.

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/27696

ChangeSet@1.2478, 2007-05-30 19:27:00+04:00, kaa@polly.local +6 -0
  Fix for bug #27643 "query failed : 1114 (The table '' is full)
  
  Problem:
  
  HASH indexes on VARCHAR columns with binary collations did not ignore trailing spaces from strings before comparisons. This could result in duplicate records being successfully inserted into a MEMORY table with unique key constraints.
  
  As a direct consequence of the above, internal MEMORY tables used for GROUP BY calculation in testcases for bug #27643 contained duplicate rows which resulted in duplicate key errors when converting those temporary tables to MyISAM. Additionally, that error was incorrectly converted to the 'table is full' error.
  
  Solution:
  
  - ignore trailing spaces in VARCHAR fields with binary collations when calculating hashes.
  - return a proper error from create_myisam_from_heap() when conversion fails.

The patch http://lists.mysql.com/commits/27696 looks good.
There's only one thing:

please move ucs2 tests into ctype_ucs2.test,
to avoid test failures when ucs2 is not compiled.

Ok to push after this change.

Thanks!

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/27793

ChangeSet@1.2478, 2007-05-31 14:54:44+04:00, kaa@polly.local +8 -0
  Fix for bug #27643 "query failed : 1114 (The table '' is full)
  
  Problem:
  
  HASH indexes on VARCHAR columns with binary collations did not ignore trailing spaces from strings before comparisons. This could result in duplicate records being successfully inserted into a MEMORY table with unique key constraints.
  
  As a direct consequence of the above, internal MEMORY tables used for GROUP BY calculation in testcases for bug #27643 contained duplicate rows which resulted in duplicate key errors when converting those temporary tables to MyISAM. Additionally, that error was incorrectly converted to the 'table is full' error.
  
  Solution:
  
  - ignore trailing spaces in VARCHAR fields with binary collations when calculating hashes.
  - return a proper error from create_myisam_from_heap() when conversion fails.

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/27802

ChangeSet@1.2487, 2007-05-31 15:54:09+04:00, kaa@polly.local +2 -0
  Corrected the error codes for 5.1. This is to fix PB failures introduced by the patch for bug #27643.

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/27803

ChangeSet@1.2487, 2007-05-31 16:04:58+04:00, kaa@polly.local +4 -0
  Corrected the error codes and messages for 5.1. This is to fix PB failures introduced by the patch for bug #27643.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.