Bug #97591 Create table structure from temporary table missing primary key definition

• Submitted: 12 Nov 2019 2:34
• Modified: 14 Nov 2019 13:40
• Reporter: Zhiyong Dai
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 8.0.17
• OS: Windows (windows 10 home)
• CPU Architecture: x86 (x86_64)
• Tags: CREATE TABLE, DDL

[12 Nov 2019 2:34] Zhiyong Dai

Description:
Copying table structures and data from temporary tables loses the primary key definition of the temporary table

How to repeat:
# environment: mysql server 8.0.17
# database: Create database if not exists test default character set utf8mb4
#           collate utf8mb4_general_ci;
# Step 1: Drop table if exists.
DROP TABLE
IF
	EXISTS `account`;

# Step 2: Create temporary table.
CREATE TEMPORARY TABLE
IF
	NOT EXISTS `account` (
		`id` INT ( 11 ) NOT NULL AUTO_INCREMENT ,
		`account_name` VARCHAR ( 100 ) CHARACTER 
		SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL ,
		`password` VARCHAR ( 100 ) CHARACTER 
		SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT,
		`create_time` TIMESTAMP DEFAULT NULL,
		`update_time` TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
		`account_d` VARCHAR ( 500 ) CHARACTER 
		SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
		`account_level` VARCHAR ( 1 ) CHARACTER 
		SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
		PRIMARY KEY ( `id` ) USING BTREE,
		UNIQUE KEY `accountName` ( `account_name` ) USING BTREE 
	) ENGINE = MyISAM AUTO_INCREMENT = 27 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

# Step 3: Create non-temporary table from the temporary table account.
CREATE TABLE
IF
	NOT EXISTS `account` AS SELECT
	* 
FROM
	account;

# Step 4: Watch table structure.
SHOW CREATE TABLE account;

# Step 5: Executing this statement will be successful.
ALTER TABLE account 
	ADD CONSTRAINT account_id_key PRIMARY KEY USING BTREE (`id`) ;

# Step 6: It will throw a sql error.
ALTER TABLE account 
	ADD CONSTRAINT account_id_key PRIMARY KEY USING BTREE (`id`) ;

Suggested fix:
Temporary tables can be fully copied

[14 Nov 2019 13:40] MySQL Verification Team

Hello Mr. Dai,

Thank you for your feature request.

However, it can not be accepted.

First of all, filling table with SELECT from any other table does not create table with the exact structure like the original. This is fully described in our Reference Manual.

Second, if you wish to create table with the same structure, you should read that manual, which has in it a description of the command of this sort:

CREATE TABLE ........... LIKE ............;

Last, MyISAM storage engine does not support constraints.