Bug #50286 Storage Requirements for CHAR incorrect
Submitted: 12 Jan 2010 18:16 Modified: 15 Jan 2010 12:49
Reporter: Leo Brown Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.5.99 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[12 Jan 2010 18:16] Leo Brown
Description:
I've noticed that the description for the CHAR datatype storage requirements are incorrect

  "M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set"
  http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

I have tested this, and it appears the storage requirements are greater.

How to repeat:
CREATE TABLE test_5(cat char(5));
 CREATE TABLE test_6(cat char(6));
 CREATE TABLE test_7(cat char(7));
 INSERT INTO test_5 VALUES('aaaaaaaa');
 INSERT INTO test_6 VALUES('aaaaaaaa');
 INSERT INTO test_7 VALUES('aaaaaaaa');
 \! ls -l /var/db/mysql/*/test_*.MYD
 DROP TABLE test_5;
 DROP TABLE test_6;
 DROP TABLE test_7;

You'll receive:

 -rw-rw----  1 mysql  mysql  7 Jan 12 18:01 /var/db/mysql/test/test_5.MYD
 -rw-rw----  1 mysql  mysql  7 Jan 12 18:01 /var/db/mysql/test/test_6.MYD
 -rw-rw----  1 mysql  mysql  8 Jan 12 18:01 /var/db/mysql/test/test_7.MYD

So the minimum record size is (M x w) + 1.

Suggested fix:
Update the documentation to reflect this:

"(M × w) + 1 bytes, 6 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set"
[12 Jan 2010 21:00] Sveta Smirnova
Thank you for the report.

Verified as described.
[14 Jan 2010 16:01] Paul DuBois
The storage requirements for CHAR are correct as documented. What you are observing in the MYD files are differences between *row* storage requirements and *column* storage requirements. MyISAM always writes a minimum of 4 bytes/row, but row storage is described more fully here:

http://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html
[15 Jan 2010 7:38] Sveta Smirnova
I am sorry: I was wrong when set it to "Verified".

Additional byte indicates NULL-ability of the field. See http://forge.mysql.com/wiki/MySQL_Internals_MyISAM for details:

Hexadecimal Display of Table1.MYD file

F1 61 62 63 00 F5 64 20 65 00              ... .abc..d e.

Here's how to read this hexadecimal-dump display:

    * The hexadecimal numbers F1 61 62 63 00 F5 64 20 65 00 are byte values and the column on the right is an attempt to show the same bytes in ASCII.
    * The F1 byte means that there are no null fields in the first row.
    * The F5 byte means that the second column of the second row is NULL.
[15 Jan 2010 12:49] Leo Brown
Hi

Can I clarify that a CHAR(0) field effectively uses 0 bytes storage on a per-record basis then?

Thanks
Leo
[15 Jan 2010 18:44] Paul DuBois
CHAR(0) uses 0 bytes. The only storage required is for the NULL bit, if the column can be NULL.