Remove deprecated PASSWORD() function

[dveeden]

Description

          > This function is deprecated in MySQL 5.7 and is removed in MySQL 8.0.

Should we rename it to password or just remove it?

I found it included by #2680, @XuHuaiyu PTAL

Originally posted by @Defined2014 in #58312 (comment)

The PASSWORD() function was used in MySQL 5.7 and before to create passwords for mysql_native_password and sha256_password. And at some point before MySQL 5.7 it also supported pre-4.1 authentication.

Reasons for removal:

• MySQL 8.0 removes it
• The PASSWORD() function doesn't work for caching_sha2_password
• Using the PASSWORD() function to directly update the mysql.user would circumvent password rules.
• Using the PASSWORD() function to create a hash which then gets used in ... IDENTIFIED AS ... might also circumvent password rules.

This seem to have been deprecated since TiDB version v2.0.0-rc.3

Related

• https://dev.mysql.com/worklog/task/?id=10774
• parser: Fix undocumented password_func function | tidb-test=pr/2458 #58312
• https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password
• https://docs.pingcap.com/tidb/stable/security-compatibility-with-mysql#authentication-plugin-status
• expression: Add deprecation warning for builtin function PASSWORD #6000
• select password("xxx") should return a warning #5946
• Remove the PASSWORD() function docs#19731

[CbcWestwolf]

@ljun0712 PTAL

[ljun0712]

LGTM. Please modify the product documentation simultaneously.

[dveeden]

To show that this has been deprecated for a long time, this is TiDB v3.0.0, which is the oldest version that I can easily run via tiup playground.

mysql-5.7.25-TiDB-v3.0.0> SELECT PASSWORD("you_will_never_guess");
+-------------------------------------------+
| PASSWORD("you_will_never_guess")          |
+-------------------------------------------+
| *A84BC1C74C11FE9E22EFC9DAFDF14C975011E489 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql-5.7.25-TiDB-v3.0.0> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql-5.7.25-TiDB-v3.0.0> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v3.0.0
Git Commit Hash: 60965b006877ca7234adaced7890d7b029ed1306
Git Branch: HEAD
UTC Build Time: 2019-06-28 12:14:07
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.01 sec)