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

[4 Apr 2007 12:13] Shane Bester
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)
[4 Apr 2007 12:22] Shane Bester
testcase. see top of file for host, user, password, gcc compile example.

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

[4 Apr 2007 12:24] Shane Bester
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
[11 May 2007 9:47] Shane Bester
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;
[15 May 2007 14:45] Bugs System
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.
[17 May 2007 11:34] Bugs System
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.
[30 May 2007 15:27] Bugs System
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.
[31 May 2007 10:39] Alexander Barkov
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!
[31 May 2007 10:55] Bugs System
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.
[31 May 2007 11:54] Bugs System
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.
[31 May 2007 12:05] Bugs System
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.
[6 Jun 2007 16:54] Bugs System
Pushed into 5.1.20-beta
[6 Jun 2007 16:58] Bugs System
Pushed into 5.0.44
[19 Jun 2007 0:44] Paul Dubois
Noted in 5.0.44, 5.1.20 changelogs.