Bug #47432 CREATE TABLE tbl_new SELECT * FROM tbl_exists; ignores indexes

• Submitted: 18 Sep 2009 9:45
• Modified: 18 Sep 2009 10:02
• Reporter: Harald Reindl
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[18 Sep 2009 9:45] Harald Reindl

Description:
CREATE TABLE tbl_new SELECT * FROM tbl_exists; ignores indexes

See the output of "show create table" of the original table and the copy
This affects all indexes including auto_increments
________________                                                                                                                                                             
CREATE TABLE `aume_snippets` (
  `s_id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
  `s_title` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
  `s_content` text COLLATE latin1_german1_ci NOT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=1 DELAY_KEY_WRITE=1;

CREATE TABLE `aume2009_snippets` (
  `s_id` mediumint(7) unsigned NOT NULL DEFAULT '0',
  `s_title` varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '',
  `s_content` text COLLATE latin1_german1_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

How to repeat:
Try the simple query

Suggested fix:
"CREATE TABLE tbl_new SELECT * FROM tbl_exists; ignores indexes" should create the new table with all propertys from the original including table options

[18 Sep 2009 9:52] Valeriy Kravchuk

This behavior is documented. See http://dev.mysql.com/doc/refman/5.1/en/create-table.html:

"CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;"

[18 Sep 2009 10:02] Harald Reindl

I made the workaround creating all tables before i wrote the bugreport but this is clearly a bug for me because for complex tables it is much easier to drop indexes rather to add them after copy the table

This can make really troubles if you do not see missing indexes and believe you have a 1:1 copy of the existing table 

Following example to copy the whole data of a website with new prefixes for make layout-changes, in the worst case you start working in the new page and realize much too late that new entries without page-id are created 

________________________

use aume;
drop table if exists aume2009_global_cache;
drop table if exists aume2009_haupt;
drop table if exists aume2009_locks;
drop table if exists aume2009_meta;
drop table if exists aume2009_newsletter;
drop table if exists aume2009_newsletter_archiv;
drop table if exists aume2009_produktionsmeldung;
drop table if exists aume2009_search_index;
drop table if exists aume2009_snippets;
drop table if exists aume2009_stg_sub;
drop table if exists aume2009_stg_sub2;
drop table if exists aume2009_sub;
drop table if exists aume2009_sub2;
drop table if exists aume2009_user_group_permissions;
drop table if exists aume2009_user_login;
drop table if exists aume2009_user_modules;
drop table if exists aume2009_user_online;
drop table if exists aume2009_users;
create table aume2009_global_cache select * from aume_global_cache;
create table aume2009_haupt select * from aume_haupt;
create table aume2009_locks select * from aume_locks;
create table aume2009_meta select * from aume_meta;
create table aume2009_newsletter select * from aume_newsletter;
create table aume2009_newsletter_archiv select * from aume_newsletter_archiv;
create table aume2009_produktionsmeldung select * from aume_produktionsmeldung;
create table aume2009_search_index select * from aume_search_index;
create table aume2009_snippets select * from aume_snippets;
create table aume2009_stg_sub select * from aume_stg_sub;
create table aume2009_stg_sub2 select * from aume_stg_sub2;
create table aume2009_sub select * from aume_sub;
create table aume2009_sub2 select * from aume_sub2;
create table aume2009_user_group_permissions select * from aume_user_group_permissions;
create table aume2009_user_login select * from aume_user_login;
create table aume2009_user_modules select * from aume_user_modules;
create table aume2009_user_online select * from aume_user_online;
create table aume2009_users select * from aume_users;