MySQL Bugs: #33925: Create .frm file with size greater than 64KB

Bug #33925	Create .frm file with size greater than 64KB
Submitted:	18 Jan 2008 16:28	Modified:	20 Aug 2009 18:31
Reporter:	Tim McCutcheon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.1-BK, 5.0, 5.1, azalea	OS:	Linux
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The documentation states that the .frm file cannot be larger than 64KB (See below). However, a table can be created where the actual size of the .frm file is over 80KB while the result of the formula for max columns is below 64KB.

"Each table has an .frm file that contains the table definition. The .frm file size limit is fixed at 64KB."

How to repeat:
See the attached CREATE TABLE statement.

The resulting files:

[root@lxxx xxxx]# ll DA*
-rw-rw----    1 mysql    mysql       84081 Jan 18 07:51 DATEST.frm
-rw-rw----    1 mysql    mysql           0 Jan 18 07:51 DATEST.MYD
-rw-rw----    1 mysql    mysql        9216 Jan 18 07:51 DATEST.MYI

Suggested fix:
Fix the max columns formula or correct the documentation to state that .frm file may be greater than 64KB

CREAT TABLE statement

Attachment: GetColumns.txt (text/plain), 58.76 KiB.

Different things happen in 5.0 vs 5.1.

In 5.1, the example CREATE TABLE statement works, with no warnings or errors.

In 5.0, the example statement, but generates an error:

Query OK, 0 rows affected, 1 warning (4.01 sec)

mysql> show warnings;
+-------+------+------------------+
| Level | Code | Message          |
+-------+------+------------------+
| Error | 1117 | Too many columns |
+-------+------+------------------+

Yet, despite the error, the table gets created, with all of its columns!
This seems like a bug to me; if the server says there are too many columns,
it should refuse to create the table, shouldn't it?

Are the .frm files larger than 64KB? The documentation indicates that this is an absolute limit.

On 5.0.54 I've got:

    ->  XXXXXXXXXXXXXXXXXXXXXXXXXXX1247 TEXT NOT NULL,
    ->  XXXXXXXXXXXXXXXXXXXXXXXXXXX1248 TEXT NOT NULL,
    ->  XXXXXXXXXXXXXXXXXXXXXXXXXXX1249 TEXT NOT NULL,
    ->  XXXXXXXXXXXXXXXXXXXXXXXXXXXLAST TEXT NOT NULL)
    -> ;
ERROR 1005 (HY000): Can't create table '.\test\datest.frm' (errno: 139)
mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>perror 139
MySQL error code 139: Too big row

Same on 5.1.22.

So, please, try to repeat with a newer version, 4.1.23, and inform about the results.

Valeriy:

Why does the documentation state that the size limit for .frm files is 64KB when I have a table in production (about 5 queries per second) with a .frm file larger than 64KB?

TABLE CREATE with fewer columns

Attachment: GetColumnsSmall.txt (text/plain), 49.21 KiB.

I have uploaded a CREATE statement that creates a table with fewer columns (1050) but still should get the .frm file over 64KB. The first CREATE statement was designed to generate the largest possible .frm file.

The new file creates a .frm of 68K on my system.

-rw-rw----    1 mysql    mysql         68K Jan 19 10:43 /var/lib/mysql/ftwill/DATEST.frm
-rw-rw----    1 mysql    mysql           0 Jan 19 10:43 /var/lib/mysql/ftwill/DATEST.MYD
-rw-rw----    1 mysql    mysql        8.0K Jan 19 10:43 /var/lib/mysql/ftwill/DATEST.MYI

The formula for calculating the size of the .frm file does not appear to be accurate. (http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html)

In particular, the total length of all column names, including one byte per name as a separator is added to the calculation as n_length. However, according the "Internals File Formats" page, the .frm file stores column names twice:

http://forge.mysql.com/wiki/MySQL_Internals_File_Formats

As a result, the formula for calculating the size of the frm file will under count the effect of column names by a factor of two. The Internals File Formats page states that "there is a transition underway from an old ('binary) format to a new ('text based') .frm format."

Could this transition to a new format be the reason why the .frm files can be larger than 64KB?

Version 4.1.23 was not available for download.

Version 4.1.22-standard was used instead.

run GetColumns.txt:
# ll /var/lib/mysql/ftwill/DA*
-rw-rw----    1 mysql    mysql       84081 Jan 23 02:58 /var/lib/mysql/ftwill/DATEST.frm
-rw-rw----    1 mysql    mysql           0 Jan 23 02:58 /var/lib/mysql/ftwill/DATEST.MYD
-rw-rw----    1 mysql    mysql        9216 Jan 23 02:58 /var/lib/mysql/ftwill/DATEST.MYI

run GetColumnsSmall.txt
# ll /var/lib/mysql/ftwill/DA*
-rw-rw----    1 mysql    mysql       69705 Jan 23 02:58 /var/lib/mysql/ftwill/DATEST.frm
-rw-rw----    1 mysql    mysql           0 Jan 23 02:58 /var/lib/mysql/ftwill/DATEST.MYD
-rw-rw----    1 mysql    mysql        8192 Jan 23 02:58 /var/lib/mysql/ftwill/DATEST.MYI

Thank you for the report.

Verified as described:

$mysql41 -e "create database test"

$mysql41 test <bug33925.sql 

$ls -lah ~/build/mysql-4.1/data/test
total 96K
drwx------  2 ssmirnova ssmirnova 4.0K Jan 26 13:19 .
drwx------  7 ssmirnova ssmirnova 4.0K Jan 26 13:19 ..
-rw-rw----  1 ssmirnova ssmirnova  69K Jan 26 13:19 DATEST.frm
-rw-rw----  1 ssmirnova ssmirnova    0 Jan 26 13:19 DATEST.MYD
-rw-rw----  1 ssmirnova ssmirnova 8.0K Jan 26 13:19 DATEST.MYI
-rw-rw----  1 ssmirnova ssmirnova   61 Jan 26 13:19 db.opt

Problem also exists in version 5.0.22-log.

Bug #40323 was marked as duplicate of this one.

Why is the bug not being fixed?

Bug #46636 was marked as duplicate of this one.

I think it's just an outdated documentation. The formula, as specified, is correct, in the sense, that it's exactly what is written in the source code.
But it doesn't take into account all of the frm fields, so the frm size may be larger than 64K.

"transition to the new frm format" has nothing to do with it, MySQL still uses the old binary frm format, there's no transition in progress.

There is no factor of two either, when frm gets big enough - this DATEST.frm for example - it only stores column fields once.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Removed statement that the *file* size limit is 64KB. Revised wording:

Each table has an .frm file that contains the table definition. The
server uses the following expression to check some of the table
information stored in the file against an upper limit of 64KB:

if (info_length+(ulong) create_fields.elements*FCOMP+288+
    n_length+int_length+com_length > 65535L || int_count > 255)

The portion of the information stored in the .frm file that is
checked against the expression cannot grow beyond the 64KB limit, so
if the table definition reaches this size, no more columns can be
added.