The mysql_native_password plugin is deprecated in MySQL 8.4 LTS and dropped in MySQL 9

[bradfordbulger-nasa]

Working with drush 12.5 setting up test copies of Drupal 10, I found that when I created a new database with drush sql:create, when I tried to access it I was getting errors about a missing plugin for mysql_native_password. Looking into it, I saw that I had upgraded mysql on my host machine (a Mac, mysql from homebrew) without thinking much about it, and it was the MySQL 9 client that was failing.

In Drush\Sql\SqlMysql::createdbSql() it is explicitly using the mysql_native_password plugin in a create user command:
$sql[] = sprintf("CREATE USER %s IDENTIFIED WITH mysql_native_password;", $user);

It looks like this was introduced for #4259 although I am not clear on why.

MySQL deprecated the mysql_native_password plugin in 8.4 LTS, and dropped it in 9. I believe that MariaDB still supports this plugin, though they do not recommend it. For what that's worth.

I'd think that there needs to be some path available to users that does not use an obsolete plugin. But it was added in for a reason, and I don't know how that looks now.

[weitzman]

Some discussion at https://drupal.slack.com/archives/C62H9CWQM/p1726163171134629. Help wanted.