Bug #25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA

Description:
Trying to CREATE TABLE LIKE from INFORMATION_SCHEMA.TABLES does not work.

My intention was to snapshot several INFORMATION_SCHEMA tables, then reindex them to speed up some operations. That is not done easily now, because CREATE TABLE LIKE is unavailable.

How to repeat:
mysql> create database kris;
Query OK, 1 row affected (0.01 sec)

mysql> use kris
Database changed
mysql> show create table INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) default NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL default '',
  `TABLE_NAME` varchar(64) NOT NULL default '',
  `TABLE_TYPE` varchar(64) NOT NULL default '',
  `ENGINE` varchar(64) default NULL,
  `VERSION` bigint(21) default NULL,
  `ROW_FORMAT` varchar(10) default NULL,
  `TABLE_ROWS` bigint(21) default NULL,
  `AVG_ROW_LENGTH` bigint(21) default NULL,
  `DATA_LENGTH` bigint(21) default NULL,
  `MAX_DATA_LENGTH` bigint(21) default NULL,
  `INDEX_LENGTH` bigint(21) default NULL,
  `DATA_FREE` bigint(21) default NULL,
  `AUTO_INCREMENT` bigint(21) default NULL,
  `CREATE_TIME` datetime default NULL,
  `UPDATE_TIME` datetime default NULL,
  `CHECK_TIME` datetime default NULL,
  `TABLE_COLLATION` varchar(64) default NULL,
  `CHECKSUM` bigint(21) default NULL,
  `CREATE_OPTIONS` varchar(255) default NULL,
  `TABLE_COMMENT` varchar(80) NOT NULL default ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> create table t like INFORMATION_SCHEMA.TABLES;
ERROR 1051 (42S02): Unknown table 'TABLES'

Suggested fix:
Have a proper data dictionary and get rid of the darn .frm files, finally!

Thank you for a bug report. Verified just as described. It is a documentation request, for sure, and, likely a feature request for 5.1.x. I hope we will have a proper data dictionary eventually.

Same problem with latest 5.1.15-BK also.

bug#31584 was marked a duplicate of this bug

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

ChangeSet@1.2605, 2007-11-09 09:09:39+04:00, gluh@mysql.com +3 -0
  Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA
  added new func mysql_create_like_schema_frm() which creates frm file based on I_S table

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

ChangeSet@1.2656, 2007-11-28 15:37:00+04:00, gluh@mysql.com +3 -0
  Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA
  added new func mysql_create_like_schema_frm() which creates frm file based on I_S table

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.1.23, 6.0.5 changelogs.

CREATE TABLE LIKE did not work when the source table was an
INFORMATION_SCHEMA table.