Open winkyao opened 6 years ago
I'm working on this issue, hope that it can be fixed soon @winkyao @zz-jason , thanks
@bb7133 Thanks for your contribution!
Here is a minimal test case:
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 AS SELECT * FROM t1;
Confirming it is still pending:
mysql> CREATE TABLE t2 AS SELECT * FROM t1;
ERROR 1105 (HY000): 'CREATE TABLE ... SELECT' is not implemented yet
mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-183-g57db6cec7
Git Commit Hash: 57db6cec7ffad78e74b6ac6c67a2bfe9a6718d17
Git Branch: master
UTC Build Time: 2020-04-04 08:06:18
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)
Hi,
I got the same error working with Flyway and Java
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 63955 Server version: 5.7.25-TiDB-v4.0.0-rc.2 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT tidb_version()\G 1. row tidb_version(): Release Version: v4.0.0-rc.2 Edition: Community Git Commit Hash: dfbb1ff8b0a8d55893bcea652748c40b9a9b91ad Git Branch: heads/refs/tags/v4.0.0-rc.2 UTC Build Time: 2020-05-15 11:54:25 GoVersion: go1.13 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false 1 row in set (0.00 sec)
This is the issue:
[log4j] 2020-08-16 23:42:57 [main] WARN org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor - DB: 'CREATE TABLE ... SELECT' is not implemented yet (SQL State: HY000 - Error Code: 1105),
[log4j] 2020-08-16 23:42:57 [main] WARN org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: ,
Migration failed,
-----------------,
SQL State : HY000,
Error Code : 1105,
Message : 'CREATE TABLE ... SELECT' is not implemented yet,
Location : (),
Line : 1,
Statement : CREATE TABLE default
.otp_schema_history
(,
installed_rank
INT NOT NULL,,
version
VARCHAR(50),,
description
VARCHAR(200) NOT NULL,,
type
VARCHAR(20) NOT NULL,,
script
VARCHAR(1000) NOT NULL,,
checksum
INT,,
installed_by
VARCHAR(100) NOT NULL,,
installed_on
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,,
execution_time
INT NOT NULL,,
success
BOOL NOT NULL,,
CONSTRAINT otp_schema_history_pk
PRIMARY KEY (installed_rank
),
) ENGINE=InnoDB AS SELECT 1 as "installed_rank", '1' as "version", '<< Flyway Baseline >>' as "description", 'BASELINE' as "type", '<< Flyway Baseline >>' as "script", NULL as "checksum", 'root' as "installed_by", CURRENT_TIMESTAMP as "installed_on", 0 as "execution_time", TRUE as "success",
,
,
[log4j] 2020-08-16 23:42:57 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...,
[log4j] 2020-08-16 23:42:57 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.,
Isn't this the same as #3839 ?
why not support?
why not support?
There are always ways to make a workaround just like 'CREATE TABLE LIKE ..." + "INSERT INTO ... SELECT", so the priority of it is low.
Please note that when using MySQL there is a big restriction on using this: In MySQL 5.7 and MySQL 8.0.20 and before this isn't allowed when GTID is used.
Source: https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-restrictions.html
While I don't see any real problem for implementing this in TiDB I would still recommend people to avoid this. I think it is often better to not combine DDL and DML in a single statement. Also it is often better to more explicitly define a table structure as it may be difficult and/or impossible for the database to use the right data types. For example CREATE TABLE t1 SELECT 1
: Would this be a tinyint (bool?), int or bigint? unsigned or signed? a column comment? what should the default be? are nulls allowed?
MySQL:
TiDB:
What version of TiDB are you using (
tidb-server -V
)?ref https://dev.mysql.com/doc/refman/5.7/en/create-table-select.html
this case is from mysql-test/alias.test