Support SHOW CREATE USER as in MySQL 5.7

[morgo]

Feature Request

Is your feature request related to a problem? Please describe:

MySQL 5.7 supports the syntax SHOW CREATE USER. It is useful for logical dumpers to be able to recreate a user with various flags (since restoring to the tables should be discouraged, and the format can change.)

Describe the feature you'd like:

From MySQL 5.7.19:

mysql [localhost] {msandbox} ((none)) > SHOW CREATE USER 'msandbox'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for msandbox@localhost                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'msandbox'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

As a secondary issue, TiDB cannot currently import this user:

mysql> CREATE USER 'msandbox'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
ERROR 1105 (HY000): line 1 column 129 near " NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK" (total length 173)

Describe alternatives you've considered:

An alternative would be to create an information_schema view with the CREATE USER of all users.. but for compatibility reasons as described here makes the most sense.

Teachability, Documentation, Adoption, Migration Strategy:

This will mainly be used by logical dumpers. Thus, this FR relates to https://github.com/pingcap/tidb/issues/7714

[morgo]

Parser support was added in https://github.com/pingcap/tidb/pull/8970

@lnhote @tiancaiamao Do you have current plans to implement in TiDB, or would you like me to take a look at it?

[tiancaiamao]

I have no plan for it yet, go ahead if you'd like to @morgo

[lnhote]

I can try it @morgo.

[shenli]

@lnhote Cool! Thanks!

[morgo]

Excellent, thanks!

[eurekaka]

Is this failure on CI related to this issue?

----------------------------------------------------------------------
FAIL: show_test.go:205: testSuite2.TestShow3

show_test.go:209:
    tk.MustQuery("show create user 'test_show_create_user'@'%'").
        Check(testkit.Rows(`CREATE USER 'test_show_create_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK`))
/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/util/testkit/testkit.go:212:
    tk.c.Assert(errors.ErrorStack(err), check.Equals, "", comment)
... obtained string = "" +
...     "[executor:1396]Operation SHOW CREATE USER failed for 'test_show_create_user'@'%'\n" +
...     "github.com/pingcap/errors.AddStack\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/pkg/mod/github.com/pingcap/errors@v0.11.0/errors.go:174\n" +
...     "github.com/pingcap/parser/terror.(*Error).GenWithStackByArgs\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20190228070002-74e8cffabf28/terror/terror.go:233\n" +
...     "github.com/pingcap/tidb/executor.(*ShowExec).fetchShowCreateUser\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/executor/show.go:911\n" +
...     "github.com/pingcap/tidb/executor.(*ShowExec).fetchAll\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/executor/show.go:117\n" +
...     "github.com/pingcap/tidb/executor.(*ShowExec).Next\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/executor/show.go:80\n" +
...     "github.com/pingcap/tidb/executor.(*recordSet).Next\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/executor/adapter.go:104\n" +
...     "github.com/pingcap/tidb/session.GetRows4Test\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/session/tidb.go:264\n" +
...     "github.com/pingcap/tidb/util/testkit.(*TestKit).ResultSetToResult\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/util/testkit/testkit.go:211\n" +
...     "github.com/pingcap/tidb/util/testkit.(*TestKit).MustQuery\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/util/testkit/testkit.go:185\n" +
...     "github.com/pingcap/tidb/executor_test.(*testSuite2).TestShow3\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/src/github.com/pingcap/tidb/executor/show_test.go:209\n" +
...     "runtime.call32\n" +
...     "\t/usr/local/go/src/runtime/asm_amd64.s:522\n" +
...     "reflect.Value.call\n" +
...     "\t/usr/local/go/src/reflect/value.go:447\n" +
...     "reflect.Value.Call\n" +
...     "\t/usr/local/go/src/reflect/value.go:308\n" +
...     "github.com/pingcap/check.(*suiteRunner).forkTest.func1\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/pkg/mod/github.com/pingcap/check@v0.0.0-20190102082844-67f458068fc8/check.go:836\n" +
...     "github.com/pingcap/check.(*suiteRunner).forkCall.func1\n" +
...     "\t/home/jenkins/workspace/tidb_ghpr_unit_test/go/pkg/mod/github.com/pingcap/check@v0.0.0-20190102082844-67f458068fc8/check.go:730\n" +
...     "runtime.goexit\n" +
...     "\t/usr/local/go/src/runtime/asm_amd64.s:1333"
... expected string = ""
... sql:show create user 'test_show_create_user'@'%', args:[]

It is discovered in idc-jenkins-ci-tidb/unit-test with -race enabled to run go test.

[morgo]

@eurekaka looks like it, see also https://github.com/pingcap/tidb/issues/9462