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:Microsoft Windows (Windows XP Home (german) SP 2)
Assigned to: Jim Winstead CPU Architecture:Any

[5 Aug 2005 12:31] Carsten Segieth
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.
[8 Aug 2005 23:28] Jim Winstead
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
[20 Aug 2005 1:58] Jim Winstead
Fixed in 5.0.12.
[24 Aug 2005 17:49] Mike Hillyer
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>