can not prepare DDL statements

[ian-p-cooke]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

use diesel_cli to setup a new database diesel setup.

2. What did you expect to see?

a new database in TiDB

3. What did you see instead?

Can not prepare DDL statements

I can work around this by patching diesel_cli to not use a prepared statement in this case but it shows an area of incompatibility with MySQL 8.0. MySQL permits 'CREATE TABLE' in prepared statements: https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html

Can you permit the same DDL statements as MySQL?

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

| Release Version: v3.0.0-beta-27-g6398788
Git Commit Hash: 6398788
Git Branch: master
UTC Build Time: 2019-01-31 08:42:28
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |

[zimulala]

@ian-p-cooke
Thanks for your report!
This feature is also supported on Mysql 5.7(https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html). We will not support this feature in the short term. And we will update this docs：https://github.com/pingcap/docs/blob/master/sql/prepare.md

[ian-p-cooke]

hi @zimulala , could you elaborate on why you do not currently support preparing DDL statements? I looked at the code and while I'm not well-versed in Go, I did find where you check for that but I couldn't determine what the consequences of using a DDL StmtNode in the ast.Prepared struct would be.

The reason I went back to look at this is I wondered if you could support DDL statements that have no parameters without much work. For example, if you didn't check for DDL and received literally CREATE DATABASE db0; would anything break if you permitted that statement past your check?

Why use a prepared statement if there are no parameters? Well, some code I'm working with prepares all the SQL it executes. I have a workaround for that client but I thought that 'DDL statements with no parameters' might be a small step forward for this feature and is something I could do a pull request for.

[ian-p-cooke]

I did some testing with a modified tidb-server and some prepared DDL statements appear to work ok. What I'm suggesting is something like: ian-p-cooke@faa575f

What I did after modifying prepared was:

PREPARE create_db FROM 'create database blog';
EXECUTE create_db;
DEALLOCATE PREPARE create_db;
use blog;
PREPARE create_table FROM 'create table posts ( id BIGINT )';
EXECUTE create_table;
DEALLOCATE PREPARE create_table;

but if you could explain why DDL statements were excluded before I can try and address that.

[ian-p-cooke]

I'd like to get back to this over the weekend. Can you explain why you exclude DDL statements from being prepared? It appears to work without any modifications.

[winkyao]

@ian-p-cooke That's ok, the reason why we don't support it because of executing DDL in the prepared statements is just not too common in our user scenarios.

[ian-p-cooke]

#10144 closes this issue