MySQL Bugs: #12397: wrong values shown in column CREATE_OPTIONS of INFORMATION

Bug #12397	wrong values shown in column CREATE_OPTIONS of INFORMATION_SCHEMA.TABLES
Submitted:	5 Aug 2005 12:31	Modified:	24 Aug 2005 17:49
Reporter:	Carsten Segieth	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.10a	OS:	Windows (Windows XP Home (german) SP 2)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
In the column CREATE_OPTIONS of table INFORMATION_SCHEMA.TABLES wrong values "max_rows=<nnn>" are shown for the tables of INFORMATION_SCHEMA itself. These values cannot be found with "SHOW CREATE TABLE information_schema.tables". Also these values are not fixed but change when the DB is used a while.

Comparing the content of CREATE_OPTIONS for a table of INFORMATION_SCHEMA with the corresponding "SHOW CREATE TABLE" shows that there is no 'table_option' "max_rows=<nnn>":

mysql> SELECT * FROM information_schema.tables WHERE table_name = "TABLES"\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: information_schema
     TABLE_NAME: TABLES
  ...snip...
 CREATE_OPTIONS: max_rows=1439
  TABLE_COMMENT:

mysql> show create table information_schema.tables;
+--------+----------------------------------------
| Table  | Create Table
+--------+----------------------------------------
| TABLES | CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) default NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL default '',
  `TABLE_NAME` varchar(64) NOT NULL default '',
...snip...
  `CREATE_OPTIONS` varchar(255) default NULL,
  `TABLE_COMMENT` varchar(80) NOT NULL default ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+--------+----------------------------------------

Testing with 'normal' tables shows the expected results (see sample below).

How to repeat:
SELECT * FROM information_schema.tables WHERE table_name = "TABLES"\G
CREATE TABLE test.pino3 ( c TEXT ) TYPE=INNODB, MAX_ROWS=4711;
SELECT table_schema, table_name, CREATE_OPTIONS FROM information_schema.tables;
SHOW CREATE TABLE information_schema.tables;
SHOW CREATE TABLE test.pino3;

Suggested fix:
remove 'max_rows=<nnn> from of INFORMATION_SCHEMA.TABLES for tables of the INFORMATION_SCHEMA.

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/28032

Fixed in 5.0.12.

Documented in 5.0.12 changelog:

<listitem><para>The <literal>CREATE_OPTIONS</literal> column of <literal>INFORMATION_SCHEMA.TABLES</literal> showed incorrect options for tables in <literal>INFORMATION_SCHEMA</literal>. (Bug #12397)</para></listitem>