Bug #14673 Partitions: Wrong InnoDB default row format
Submitted: 5 Nov 2005 21:33 Modified: 14 Mar 2006 18:48
Reporter: John David Duncan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1 OS:Linux (linux)
Assigned to: Jim Winstead CPU Architecture:Any

[5 Nov 2005 21:33] John David Duncan
Description:
If you create an InnoDB table in MySQL 5.1, the default row format is COMPACT.

But if you create a partitioned InnoDB table, the old REDUNDANT row format is used by default (and SHOW TABLE STATUS expresses the row format as "fixed"). 

How to repeat:
CREATE TABLE t1 ( i int unsigned not null, j int unsigned not null, primary key (i,j) ) 
TYPE=InnoDB PARTITION BY HASH (i) ;

CREATE TABLE t2 ( i int unsigned not null, j int unsigned not null, primary key (i,j) ) 
TYPE=InnoDB; 

SHOW TABLE STATUS like 't1'; 
SHOW TABLE STATUS like 't2';

-- see the different row formats 

If you insert rows into both tables, you will see that the average row length is much larger on t1 than on t2.  "ALTER TABLE t1 ROW_FORMAT=compact" will fix things.
[8 Mar 2006 17:58] Jim Winstead
This is almost certainly just a reporting issue -- all partitioned tables are reported as using the 'Fixed' row format, because the partition handler doesn't report a row format. The same is probably true for the average row length.
[8 Mar 2006 18:59] Jim Winstead
The average row length is reported as 8192 for an empty partitioned InnoDB table because partitioning always reports of at least 2 rows in the table (for the optimizer's benefit, I believe). It should be more accurate with actual data in the table.
[8 Mar 2006 19:02] 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/3600
[13 Mar 2006 19:05] Jim Winstead
Fixed in 5.1.8.