Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support SHOW CREATE USER as in MySQL 5.7 #7733

Closed
morgo opened this Issue Sep 17, 2018 · 7 comments

Comments

Projects
None yet
7 participants
@morgo
Copy link
Member

morgo commented Sep 17, 2018

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 #7714

@morgo

This comment has been minimized.

Copy link
Member Author

morgo commented Jan 28, 2019

Parser support was added in #8970

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

@morgo morgo assigned morgo and winkyao and unassigned morgo Jan 28, 2019

@tiancaiamao

This comment has been minimized.

Copy link
Contributor

tiancaiamao commented Jan 28, 2019

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

@shenli shenli added the help wanted label Jan 28, 2019

@lnhote

This comment has been minimized.

Copy link
Contributor

lnhote commented Jan 28, 2019

I can try it @morgo.

@shenli

This comment has been minimized.

Copy link
Member

shenli commented Jan 28, 2019

@lnhote Cool! Thanks!

@jackysp jackysp assigned jackysp and unassigned winkyao and jackysp Jan 28, 2019

@morgo

This comment has been minimized.

Copy link
Member Author

morgo commented Jan 28, 2019

Excellent, thanks!

@eurekaka

This comment has been minimized.

Copy link
Contributor

eurekaka commented Mar 5, 2019

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

This comment has been minimized.

Copy link
Member Author

morgo commented Mar 5, 2019

@eurekaka looks like it, see also #9462

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.