PHP 7.4 & MySQL: caching_sha2_password Denying long (20c) passwords

Question

UPDATE:

23.02.2020: The bug has been fixed in PHP 7.4.3.

23.12.2019: I've found out which passwords are affected. See my answer below. An answer to why long passwords are rejected would still be much appreciated.

---

Disclaimer: I have tried about 2 hours just setting different passwords. I am absolutely confident that the password below causes the issue. It works with other passwords. I do know how to fix it: use a different password. I want to know why it does not work. Because such inconsistency is not acceptable.

I am able to reproduce the issue on my system.

---

Recently switched to PHP 7.4 and MySQL 8, which by default use caching_sha2_password. After validating that it is in fact supported by PHP, I couldn't get it to work with my randomly generated password, so I used PHP 7.3 with mysql_native_password again.

Now, I setup a new server and it worked, so I tried to find out where the issue lies, so I can use caching_sha2_password on my other server.

MySQL (via mysql shell using root)

ALTER USER 'test'@'localhost' IDENTIFIED WITH caching_sha2_password BY '';
FLUSH PRIVILEGES;
QUIT

PHP

const DB_HOST = '127.0.0.1';
const DB_USERNAME = 'test';
const DB_PASSWORD = '';
const DB_NAME = 'test_db';
$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);

TEST (to reproduce)

php db.php

Error

PHP Warning: mysqli::__construct(): (HY000/1045): Access denied for user 'test'@'localhost' (using password: YES) in /db.php on line 5

WORKAROUND: Funny thing is running this fixes it again for some insane reason:

mysql -u test -p
  # Enter password
  QUIT

Re-setting the password introduces the issue again.

I noticed that it failed based on the password I used. So I thought it might be unsupported characters or pasting issues.

I boiled my original password (40chars) down to this (20chars):

This password breaks it: l0QDEptp*L6tNo28ey^8

All shorter combinations work with no issue: This password works for example: l0QDEptp*L6tNo28ey^ as well as this one: 0QDEptp*L6tNo28ey^8

Important: Only test this public password in safe environments!

If you are able to reproduce the issue or have any thoughts on this (solution / possible reason), let me know.

Note: This issue actually occurs for all my randomly generated passwords, which are 40 characters long and contain special chars (e.g.: %^$!). I guess some relatively common combination triggers this issue.

Image: Demo

---

MySQL Version: mysql Ver 8.0.18-0ubuntu0.19.10.1 for Linux on aarch64 ((Ubuntu))
PHP version: PHP 7.4.1 (cli) (built: Dec 18 2019 14:44:55) ( NTS )
PHP Bug report: Here
MySQL Bug report: Here

Answer 1

Use this script to automatically set random length and content password, then reconnent to mysql server.

$mysqli = new mysqli('localhost', 'username', 'initialpassword', 'dbname');
$i=0;
while (true) {
    if($i!=0){
        $mysqli = new mysqli('localhost', 'username', $pw, 'dbname');
    }
    $pw = bin2hex(random_bytes(rand(1, 16)));
    $successd = $mysqli->query("ALTER USER 'username'@'localhost' IDENTIFIED WITH caching_sha2_password BY '{$pw}'");
    echo "times:{$i} pw:{$pw} {$successd}\n";
    $i++;
}

In my environment there's not a single access denied error occurred after iterate 1 million times.