MySQL Bugs: #8941: garbage MAX_ROWS=# from SHOW CREATE TABLE INFORMATION

Bug #8941	garbage MAX_ROWS=# from SHOW CREATE TABLE INFORMATION_SCHEMA.*
Submitted:	4 Mar 2005 4:35	Modified:	11 Apr 2005 2:10
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux/any)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
SHOW CREATE TABLE on any INFORMATION_SCHEMA table contains a MAX_ROWS=# where # is a garbage (random) number.

How to repeat:
mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.SCHEMATA;
===
CREATE TEMPORARY TABLE `SCHEMATA` (
  `CATALOG_NAME` varchar(4096) default NULL,
  `SCHEMA_NAME` varchar(64) NOT NULL default '',
  `DEFAULT_CHARACTER_SET_NAME` varchar(64) NOT NULL default '',
  `SQL_PATH` varchar(4096) default NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=671
===

Suggested fix:
The problem will be an unitialized variable.
This would only show up with virtual schemas as real tables will get their info from a .frm file.

In this case the max_rows value is calculated using the following method
(sql_select.cc, create_tmp_table() function):

max_rows= min(thd->variables.tmp_table_size,
                       thd->variables.max_heap_table_size) :
                       thd->variables.tmp_table_size)/ table->s->reclength);

Result depends on values of 'tmp_table_size', 'max_heap_table_size' variables and
table record length. I checked result and it is correct value, so I think it's not a bug.
Arjen, that do you think about this?

I don't think this is correct, as it's a regular temporary table, and not a memory table per-se.
So taking the example, there can be more than 671 databases in the server, right?
And I assume that INFORMATION_SCHEMA.SCHEMATA will correctly display them, too.
By that time the temporary table will have gone to disk.
MAX_ROWS in CREATE TABLE indicates a limit, and in the case of a temporary table there is no real limit (until you run out of disk space), unless it has been explicitly defined when the table was created.

Therefore, I think the max_rows variable for a table should only be calculated if the table is NOT temporary but just a MEMORY table, and otherwise it should be whatever was already defined for the table (at create time), or 0 (or whatever other value/flag indicates that it won't be printed).

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/internals/23369

Ok to push

the print of 'MAX_ROWS=#' for 'SHOW CRETAE TABLE' with information schema tables is
skiped now
Fixed in 5.0.4

Noted in 5.0.4 changelog.