Cannot use extension auth plugin in init sql script

[yzhan1]

Bug Report

Currently, users cannot create users with extension auth plugin in the init sql script. If they do so, TiDB will complain about plugin not loaded during the execution and causing the SQL statements to fail.

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Write an auth plugin using the extension system following the example in https://github.com/pingcap/tidb/blob/master/pkg/extension/README.md
2. Create an init sql file with content:

CREATE USER myuser IDENTIFIED WITH 'my_auth_plugin' AS 'mypwd';

3. Start the server with this init SQL file

2. What did you expect to see? (Required)

Logging in as the user should succeed since the user should be created successfully.

3. What did you see instead (Required)

Logging in fails because the user is not created. The CREATE USER statement hits the following error:

[2024/07/16 21:54:04.259 -07:00] [INFO] [session.go:4008] ["CRUCIAL OPERATION"] [conn=0] [schemaVersion=51] ["secure text"="create user {myuser@% password = ***}"] [user=]
[2024/07/16 21:54:04.259 -07:00] [INFO] [tidb.go:269] ["rollbackTxn called due to ddl/autocommit failure"]
[2024/07/16 21:54:04.259 -07:00] [WARN] [session.go:2162] ["run statement failed"] [schemaVersion=51] [error="[executor:1524]Plugin 'my_auth_plugin' is not loaded"] [session="{\n  \"currDBName\": \"\",\n  \"id\": 0,\n  \"status\": 2,\n  \"strictMode\": true,\n  \"user\": null\n}"]
[2024/07/16 21:54:04.259 -07:00] [WARN] [bootstrap.go:3249] ["InitializeSQLFile error"] [error="[executor:1524]Plugin 'my_auth_plugin' is not loaded"]

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.2.0-alpha-606-gecf9031c1b-dirty
Edition: Community
Git Commit Hash: ecf9031c1b8e8243ac9dd7cc1439818d4d85e160
Git Branch: extbootstrap
UTC Build Time: 2024-07-17 04:53:08
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)