Hi,
Thx for spotting this bug. A patch for this has been pushed now to the
5.1 tree and should appear in 5.1.11
Rgrds Mikael
Mikael Ronstrom wrote:
> Hi,
>
> marc steikert wrote:
> > I thought about Fixed Tables that "Each row
> is
> > stored using a fixed number of bytes" but
> ...
> > mysql> create table t (id int) engine =
> myisam
> > partition by hash(id) partitions 2;
> > Query OK, 0 rows affected (0.04 sec)
> >
> > mysql> insert into `t` values
> > (1),(2),(3),(4),(5),(6),(7),(8),(9);
> > Query OK, 9 rows affected (0.00 sec)
> > Datensätze: 9 Duplikate: 0 Warnungen: 0
> >
> > mysql> show table status like 't'\G
> > *************************** 1. row
> > ***************************
> > Name: t
> > Engine: MyISAM
> > Version: 10
> > Row_format: Fixed
> > Rows: 9
> > Avg_row_length: 7
> > Data_length: 63
> > Max_data_length: 0
> > Index_length: 2048
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2006-04-30 00:13:23
> > Update_time: 2006-04-30 00:13:23
> > Check_time: NULL
> > Collation: utf8_general_ci
> > Checksum: NULL
> > Create_options: partitioned
> > Comment:
> > 1 row in set (0.00 sec)
> >
>
> So 9 entries are allocated in file with 7 bytes
> per record
>
> > mysql> delete from t where id > 5;
> > Query OK, 4 rows affected (0.00 sec)
> >
> > mysql> show table status like 't'\G
> > *************************** 1. row
> > ***************************
> > Name: t
> > Engine: MyISAM
> > Version: 10
> > Row_format: Fixed
> > Rows: 5
> > Avg_row_length: 12
> > Data_length: 63
> > Max_data_length: 0
> > Index_length: 2048
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2006-04-30 00:13:23
> > Update_time: 2006-04-30 00:13:23
> > Check_time: NULL
> > Collation: utf8_general_ci
> > Checksum: NULL
> > Create_options: partitioned
> > Comment:
> > 1 row in set (0.00 sec)
> >
>
> So the delete removes 4 entries, these entries do
> however still remain in
> the MyISAM data file, thus the file remains 63
> byte long.
>
> > mysql> alter table t optimize partition
> p0,p1;
> > Query OK, 0 rows affected (0.01 sec)
> > Datensätze: 0 Duplikate: 0 Warnungen: 0
> >
> > mysql> show table status like 't'\G
> > *************************** 1. row
> > ***************************
> > Name: t
> > Engine: MyISAM
> > Version: 10
> > Row_format: Fixed
> > Rows: 5
> > Avg_row_length: 7
> > Data_length: 35
> > Max_data_length: 0
> > Index_length: 2048
> > Data_free: 0
> > Auto_increment: NULL
> > Create_time: 2006-04-30 00:13:23
> > Update_time: 2006-04-30 00:13:23
> > Check_time: 2006-04-30 00:13:23
> > Collation: utf8_general_ci
> > Checksum: NULL
> > Create_options: partitioned
> > Comment:
> > 1 row in set (0.00 sec)
> >
>
> So after optimize partitions there is only 5
> entries left of 7 bytes each.
>
> >
> > ... it look's like dynamic
> > ... and thus is Data_free = 0 ???
>
> After checking things I noticed that you spotted a
> bug. The variable
> delete_length which is used to calculate Data_free
> wasn't handled in the
> ::info call in the partition handler. I'll file a
> bug and fix it.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com