Presto: Execute SQL:create table as select . Error:'Statement violates GTID consistency: CREATE TABLE ... SELECT. '

[getGitxxx]

I've upgraded the presto version to 0.325 recently.
When I use MySQL connector execute SQL likes "create table new_table as select col1 from old_table" I got Error:'Statement violates GTID consistency: CREATE TABLE ... SELECT. '
But in old version 0.192, The SQL works fine. So, any changes in the new version that the type of SQL
Is it blocked for some reason? and How to fix it in version 0.325
Thanks in advance！

[findepi]

@getGitxxx thanks for reporting this. Can you provide exact MySQL server version and any non-default configuration you're using?

[getGitxxx]

@findepi MySQL server version is 5.7.20. MySQL server is master-slave topology, and GTID mode is on, but when I use the lower presto version, it just works fine.
I have to use GTID to sync data between the master node to the slave node.
I feel a little confused about this. Any suggestion to fix it？

[kokosing]

CREATE TABLE ... SELECT.

Do you SELECT and CREATE table in MySql? Can you please share the entire error stack trace?

[getGitxxx]

@kokosing Yes, it is.
error stack log goes here:

io.prestosql.spi.PrestoException: Statement violates GTID consistency: CREATE TABLE ... SELECT.
    at io.prestosql.plugin.jdbc.BaseJdbcClient.beginInsertTable(BaseJdbcClient.java:468)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$beginInsertTable$18(StatisticsAwareJdbcClient.java:186)
    at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.beginInsertTable(StatisticsAwareJdbcClient.java:186)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.beginInsertTable(ForwardingJdbcClient.java:122)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.beginInsertTable(ForwardingJdbcClient.java:122)
    at io.prestosql.plugin.jdbc.JdbcMetadata.beginInsert(JdbcMetadata.java:249)
    at io.prestosql.metadata.MetadataManager.beginInsert(MetadataManager.java:781)
    at io.prestosql.sql.planner.optimizations.BeginTableWrite$Rewriter.createWriterTarget(BeginTableWrite.java:183)
    at io.prestosql.sql.planner.optimizations.BeginTableWrite$Rewriter.visitTableFinish(BeginTableWrite.java:142)
    at io.prestosql.sql.planner.optimizations.BeginTableWrite$Rewriter.visitTableFinish(BeginTableWrite.java:76)
    at io.prestosql.sql.planner.plan.TableFinishNode.accept(TableFinishNode.java:106)
    at io.prestosql.sql.planner.plan.SimplePlanRewriter$RewriteContext.rewrite(SimplePlanRewriter.java:84)
    at io.prestosql.sql.planner.plan.SimplePlanRewriter$RewriteContext.lambda$defaultRewrite$0(SimplePlanRewriter.java:73)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
    at io.prestosql.sql.planner.plan.SimplePlanRewriter$RewriteContext.defaultRewrite(SimplePlanRewriter.java:74)
    at io.prestosql.sql.planner.plan.SimplePlanRewriter.visitPlan(SimplePlanRewriter.java:38)
    at io.prestosql.sql.planner.plan.SimplePlanRewriter.visitPlan(SimplePlanRewriter.java:22)
    at io.prestosql.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:49)
    at io.prestosql.sql.planner.plan.OutputNode.accept(OutputNode.java:82)
    at io.prestosql.sql.planner.plan.SimplePlanRewriter.rewriteWith(SimplePlanRewriter.java:32)
    at io.prestosql.sql.planner.optimizations.BeginTableWrite.optimize(BeginTableWrite.java:73)
    at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:180)
    at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:169)
    at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:164)
    at io.prestosql.execution.SqlQueryExecution.doPlanQuery(SqlQueryExecution.java:396)
    at io.prestosql.execution.SqlQueryExecution.planQuery(SqlQueryExecution.java:384)
    at io.prestosql.execution.SqlQueryExecution.start(SqlQueryExecution.java:336)
    at io.prestosql.$gen.Presto_325____20191127_115011_2.run(Unknown Source)
    at io.prestosql.execution.SqlQueryManager.createQuery(SqlQueryManager.java:240)
    at io.prestosql.dispatcher.LocalDispatchQuery.lambda$startExecution$4(LocalDispatchQuery.java:107)
    at io.prestosql.$gen.Presto_325____20191127_115011_2.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Statement violates GTID consistency: CREATE TABLE ... SELECT.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:829)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:729)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.execute(BaseJdbcClient.java:779)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.copyTableSchema(BaseJdbcClient.java:482)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.beginInsertTable(BaseJdbcClient.java:456)
    ... 41 more

[findepi]

MySQL server is master-slave topology, and GTID mode is on,

@getGitxxx is there a way to detect GTID mode using mysql JDBC driver?

[electrum]

Do we know what changed in the SQL produced by the connector?

[getGitxxx]

MySQL server is master-slave topology, and GTID mode is on,

@getGitxxx is there a way to detect GTID mode using mysql JDBC driver?

I've no idea how to detect that.
I've tried using lower mysql-jdbc version, but still failed.
More detail about SQL like this :

insert into tab_1(col_1）
select col_1 from tab_2

create table tab_1(
id int auto increment not null,
col_1 varchar(255)
)

[ebyhr]

JFYI: I could reproduce it using this docker image mysql-master-slave-replication-sample. We need to enable master/my.cnf#L11-L12 to use gtid and expose port 3306.

mysql> create table test.foo (c1 int);

presto> insert into mysql.test.foo values (1);
Query 20191213_154953_00006_7abc8 failed: Statement violates GTID consistency: CREATE TABLE ... SELECT.

The generated query for MySQL side:

CREATE TABLE `test`.`tmp_presto_c4f60cd063c948229ca16b91981f52f5` AS SELECT `c1` FROM `test`.`foo` WHERE 0 = 1

If we can use CREATE TABLE LIKE instead of CTAS, this issue won't happen though.

MySQL driver version was 5.1.44 in 0.192
https://github.com/prestodb/presto/blob/0.192/pom.xml#L644-L648