MySQL Bugs: #94651: the parameter "MAX_ROWS" while create a table has no effect?

Bug #94651	the parameter "MAX_ROWS" while create a table has no effect?
Submitted:	14 Mar 2019 6:15	Modified:	15 Mar 2019 2:17
Reporter:	JianJun Shi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.23 & 8.0.13	OS:	Linux
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
We point out the parameter "MAX_ROWS" and set it to 2000, while create a table. Then, insert into the table with 2001 rows of data. 

It can execute successfully, without any error or warning massage.

So, the parameter "MAX_ROWS" has no effect?

How to repeat:
mysql> create database MANUAL_057_taurus;
Query OK, 1 row affected (0.05 sec)

mysql> use MANUAL_057_taurus;
Database changed

mysql> create table MANUAL_057_taurus_T (
    -> C_ID INT,
    -> C_D_ID INTEGER NOT NULL,
    -> C_W_ID BIGINT NOT NULL,
    -> C_DOUBLE DOUBLE NOT NULL,
    -> C_DECIMAL DECIMAL NOT NULL,
    -> C_FIRST VARCHAR(64) NOT NULL,
    -> C_MIDDLE CHAR(2),
    -> C_LAST VARCHAR(64) NOT NULL,
    -> C_STREET_1 VARCHAR(20) NOT NULL,
    -> C_STREET_2 VARCHAR(20),
    -> C_CITY VARCHAR(64) NOT NULL,
    -> C_STATE CHAR(2) NOT NULL,
    -> C_ZIP CHAR(9) NOT NULL,
    -> C_PHONE CHAR(16) NOT NULL,
    -> C_SINCE TIMESTAMP,
    -> C_CREDIT CHAR(2) NOT NULL,
    -> C_CREDIT_LIM NUMERIC(12,2),
    -> C_DISCOUNT NUMERIC(4,4),
    -> C_BALANCE NUMERIC(12,2),
    -> C_YTD_PAYMENT REAL NOT NULL,
    -> C_PAYMENT_CNT FLOAT NOT NULL,
    -> C_DELIVERY_CNT BOOLEAN NOT NULL,
    -> C_END DATE NOT NULL,
    -> C_VCHAR VARCHAR(1000)
    -> ) engine=MEMORY MAX_ROWS=2000;
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER $$

mysql> CREATE PROCEDURE insertvalues_MEMORY()
    -> begin
    -> declare num_temp int;
    -> set num_temp=0;
    ->
    -> while num_temp<2001 do
    -> INSERT INTO MANUAL_057_taurus_T VALUES (
    -> num_temp+1,
    -> num_temp+1,
    -> 234213542352134,
    -> 100.2,
    -> 123523.2132,
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'q2',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'dfecddfecddfecddfecd',
    -> 'dfecddfecddfecddfecd',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds',
    -> 'sw',
    -> 'ewfxejdsk',
    -> 'wedjkswikilsdcnr',
    -> '2019-03-11 17:00:01',
    -> '21',
    -> 12342124.23,
    -> 0.2,
    -> 2323423.56,
    -> 2342324.3454,
    -> 1200234.235,
    -> true,
    -> '2019-03-11',
    -> 'wevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbdswevfwbds'
    -> );
    -> set num_temp=num_temp+1;
    -> end while;
    -> end;
    -> $$

mysql> DELIMITER ;

mysql> call insertvalues_MEMORY();
Query OK, 1 row affected (0.05 sec)

mysql> select count(*) from MANUAL_057_taurus.MANUAL_057_taurus_T;
+----------+
| count(*) |
+----------+
|     2001 |
+----------+
1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

"The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows." Note this: "not a hard limit"

It is a very old thing in MySQL and probably irrelevant with recent server versions and/or InnoDB. IMO it is long overdue to deprecate and remove max_rows and min_rows settings for tables.

-- Peter
-- not a MySQL/Oracle person

Hi,

This is not a bug.

MAX_ROWS parameter does not limit number of rows in a table, it actually allows for "bigger tables" then default ones for some storage engines (e.g. search for "table full" error).

kind regards
Bogdan

Thank you for patient answer my question! :D