MySQL Bugs: #10317: Max_Rows option for Innodb, Myisam and Memory engines.

Bug #10317	Max_Rows option for Innodb, Myisam and Memory engines.
Submitted:	2 May 2005 16:28	Modified:	11 May 2005 18:14
Reporter:	Disha	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.4.-beta-standard	OS:	Any (Any)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Create a table say memory_t1 by defining the option MAX_ROWS equal to say 5. Insert 10 records in the table memory_t1. If we see the table status using SHOW TABLE STATUS it shows the Rows = 10. The table accepts the data more than the specified rows.

How to repeat:
delimiter //
create database test//
use test//
drop table if exists memory_t1//
create table memory_t1 (f1 int) max_rows=5 engine=memory//
insert into memory_t1 values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100)//
show table status like 'mem%'//

Actual Result: It accepts 10 records and displays status as,
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| memory_t1 | MEMORY |       9 | Fixed      |   10 |              5 |          80 |              25 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | latin1_swedish_ci |     NULL | max_rows=5     |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+

Expected Result: Table should not accept the records more than the specified  rows in MAX_ROWS option.

MAX_ROWS is not a hard limit, but more like a hint to storage engine, to let it chose the storage optimized for the dataset size.

The manual says:

`MAX_ROWS'
     The maximum number of rows you plan to store in the table.

but we can make it more clear that MAX_ROWS is not a constraint.

Hi!

InnoDB ignores MAX_ROWS.

Regards,

Heikki

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
product(s).

Additional info:

I'll clarify tht MAX_ROWS = n means the table must be
able to hold *at least* n rows.