Incompatible variables of password validation in mysql.global_variables after upgrading to v6.5

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. deploy tidb cluster with v6.1
2. upgrade the cluster from v6.1 to v6.5
3. query related variables of password validation from mysql.global_variables

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

The same variables with a new v6.5 cluster

mysql> select * from mysql.global_variables where variable_name like 'validate%';
+--------------------------------------+----------------+
| VARIABLE_NAME                        | VARIABLE_VALUE |
+--------------------------------------+----------------+
| validate_password.check_user_name    | ON             |
| validate_password.special_char_count | 1              |
| validate_password.length             | 8              |
| validate_password.number_count       | 1              |
| validate_password.mixed_case_count   | 1              |
| validate_password.policy             | MEDIUM         |
| validate_password.enable             | OFF            |
| validate_password.dictionary         |                |
+--------------------------------------+----------------+
8 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql>  select * from mysql.global_variables where variable_name like 'validate%';
+--------------------------------------+----------------+
| VARIABLE_NAME                        | VARIABLE_VALUE |
+--------------------------------------+----------------+
| validate_password_check_user_name    | OFF            |
| validate_password_dictionary_file    |                |
| validate_password_special_char_count | 1              |
| validate_password_number_count       | 1              |
| validate_password_length             | 8              |
+--------------------------------------+----------------+
5 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

v6.5.0

[pcqz]

/report customer

[YangKeao]

It's introduced in #38953. I think it's an expected change to improve the compatibility with MySQL (though it breaks the compatibility between multiple TiDB versions).

Maybe @CbcWestwolf can provide more background information?

[CbcWestwolf]

The reason is that #38953 forgot to modify mysql.global_variables during upgrading like

tidb/pkg/session/bootstrap.go

Line 2758 in 1dc045f

mustExecute(s, "INSERT HIGH_PRIORITY IGNORE INTO %n.%n VALUES (%?, %?);",

A workround is to manually execute INSERT HIGH_PRIORITY IGNORE INTO mysql.global_variables VALUES (...) and then trigger reloading global system variables by set global xxx = yyy.

I would fix this later.