MySQL Bugs: #21504: CREATE TABLE CHAR(x) BINARY fields not correctly stored in a table

Bug #21504	CREATE TABLE CHAR(x) BINARY fields not correctly stored in a table
Submitted:	8 Aug 2006 13:14	Modified:	4 Sep 2006 22:37
Reporter:	Marko Pecar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.24	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	BINARY, char, CREATE TABLE

Description:
In a database (with utf8 default charset) sometimes when tables are created with CHAR(x) BINARY attribute fields the row size is not correct. Also, when the table definition is edited in MySQL Query Browser, the BINARY flag is lost. 

When importing with LOAD DATA INFILE from text file, the table with CHAR fields should have approximately the same data size as the text file, but sometimes the size of the table is much larger. If viewed with binary viewer, it can be seen that the size of CHAR fields is too big. 

The problem goes away if field data types are changed from CHAR(x) to BINARY(x).

How to repeat:
1. Create table
CREATE TABLE MODEL_TYPE (
  A_MODEL_ID BINARY(36) NOT NULL,
  A_TYPE_ID BINARY(36) NOT NULL,
  MODEL_NAME VARCHAR(100) NULL,
  VIRTUAL BOOL NULL
);

2. Use LOAD DATA INFILE to fill the table from "model_type.txt"
3. Sometimes the resulting MODEL_TYPE.MYD file is much larger than "model_type.txt".

Thank you for the bug report. Could you please provide the script of the
test case including the data file. Thanks in advance.

this is a text file to be used in LOAD DATA

Attachment: wf.tbl (text/plain), 1.35 KiB.

This is a table create script

Attachment: create.sql (text/plain), 220 bytes.

A fill script

Attachment: fill.sql (text/plain), 55 bytes.

Execute CREATE.SQL, then FILL.SQL

Resulting WAYPOINT_FLIGHT.MYD is MUCH bigger than WF.TBL. 

Also, if I edit table structure in SQL Query Browser, the ID fields do not have BINARY flag set.

Regards,
Marko

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Addtional Info:
Please read the below Manual chapter:
http://dev.mysql.com/doc/refman/5.0/en/char.html