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

[4 Mar 2005 4:35] Arjen Lentz
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.
[24 Mar 2005 13:16] Sergei Glukhov
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?
[24 Mar 2005 23:28] Arjen Lentz
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).
[25 Mar 2005 8:14] Bugs System
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
[25 Mar 2005 8:53] Alexander Barkov
Ok to push
[25 Mar 2005 11:56] Sergei Glukhov
the print of 'MAX_ROWS=#' for 'SHOW CRETAE TABLE' with information schema tables is
skiped now
Fixed in 5.0.4
[11 Apr 2005 2:10] Paul Dubois
Noted in 5.0.4 changelog.