MySQL Bugs: #38909: CREATE_OPTIONS in information

Bug #38909	CREATE_OPTIONS in information_schema produces wrong results
Submitted:	20 Aug 2008 10:51	Modified:	9 Dec 2008 0:15
Reporter:	nadav wexler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Linux (fedora core 6)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
when using a select on information_schema.create_options when there are partitioned tables, the column fails to show this fact.
a select * from the same table does attribute partition to the tables.
also seen on 5.1.24-rc.

How to repeat:
mysql> use test
Database changed
mysql> create table q (s1 int key) partition by key(s1) partitions 2;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table q;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| q     | CREATE TABLE `q` (
  `s1` int(11) NOT NULL,
  PRIMARY KEY (`s1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (s1) PARTITIONS 2  */ | 
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.tables where table_schema="test" and table_name="q" and CREATE_OPTIONS="partitioned";
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION   | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------------+
| NULL          | test         | q          | BASE TABLE | MyISAM |      10 | Fixed      |          0 |              1 |           0 |               0 |         2048 |         0 |           NULL | 2008-08-20 13:24:11 | 2008-08-20 13:24:11 | NULL       | latin1_swedish_ci |     NULL | partitioned    |               | 
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.tables where table_schema="test" and table_name="q" and CREATE_OPTIONS="partitioned";
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from (select * from information_schema.tables where table_schema="test" and table_name="q" and CREATE_OPTIONS="partitioned") as d;
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> select CREATE_OPTIONS from information_schema.tables where table_schema="test";
+----------------+
| CREATE_OPTIONS |
+----------------+
|                | 
+----------------+
1 row in set (0.00 sec)

mysql> select p.CREATE_OPTIONS from information_schema.tables as p where table_schema="test";
+----------------+
| CREATE_OPTIONS |
+----------------+
|                | 
+----------------+
1 row in set (0.00 sec)

mysql> select p.CREATE_OPTIONS from information_schema.tables as p where table_schema="test" and table_name='q';
+----------------+
| CREATE_OPTIONS |
+----------------+
|                | 
+----------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.26-rc | 
+-----------+
1 row in set (0.00 sec)

Verified as described above with 5.1 bzr tree.

This is a really mad behaviour.

Same mad behaviour in 6.0 bzr tree.

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/commits/58562

2706 Sergey Glukhov	2008-11-12
      Bug#38909 CREATE_OPTIONS in information_schema produces wrong results
      table->partition_info is unitialized when we open frm file only.
      The fix is check share->partition_info_len which is set on
      frm opening stage.

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/commits/58568

2706 Sergey Glukhov	2008-11-12
      Bug#38909 CREATE_OPTIONS in information_schema produces wrong results
      table->partition_info is unitialized when we open frm file only.
      The fix is check share->partition_info_len which is set on
      frm opening stage.

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/commits/58626

2709 Sergey Glukhov	2008-11-13
      Bug#38909 CREATE_OPTIONS in information_schema produces wrong results
      table->partition_info is unitialized when we open frm file only.
      The fix is check share->partition_info_len which is set on
      frm opening stage.

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/commits/58628

2925 Sergey Glukhov	2008-11-13 [merge]
      5.1-bugteam->6.0-bugteam merge(bug#39955, bug#38909)

Pushed into 5.1.31  (revid:sergey.glukhov@sun.com-20081113121151-jdb9q32qjtpn7byk) (version source revid:azundris@mysql.com-20081114081134-pswy3m5go7r64m1p) (pib:5)

Pushed into 6.0.9-alpha  (revid:sergey.glukhov@sun.com-20081113125316-lt0z01zqh50w8gva) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)

Noted in 5.1.31, 6.0.9 changelogs.

The value of the CREATE_COLUMNS column in INFORMATION_SCHEMA.TABLES
was not "partitioned" for partitioned tables.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)