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
Triage: D4 (Minor)

[2 May 2005 16:28] Disha
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.
[2 May 2005 16:55] Sergei Golubchik
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.
[3 May 2005 6:24] Heikki Tuuri
Hi!

InnoDB ignores MAX_ROWS.

Regards,

Heikki
[11 May 2005 18:14] Paul Dubois
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.