fully support mysql 8.0 (caching_sha2_password authentication)

[thesuperzapper]

Currently, ml-metadata can't be used with most MySQL 8.0 databases out of the box, this is because in MySQL 8.0, the default-authentication-plugin was changed from mysql_native_password to caching_sha2_password for significant security reasons.

There have been a number of issues related to this over the past few years:

• MySQL client does not support caching_sha2_password #128
• mysql setup for ml-metadata #149

We even have an error message that suggests people use the less secure mysql_native_password:

ml-metadata/ml_metadata/metadata_store/mysql_metadata_source.cc

Lines 173 to 181 in 2d7a260

	LOG(ERROR)
	<< "MySQL database was not initialized. Please ensure your "
	"MySQL server is running. Also, this error might be caused by "
	"starting from MySQL 8.0, mysql_native_password used by MLMD is not "
	"supported as a default for authentication plugin. Please follow "
	"<https://dev.mysql.com/blog-archive/"
	"upgrading-to-mysql-8-0-default-authentication-plugin-"
	"considerations/>"
	"to fix this issue.";

---

As the adoption of MySQL 8.0 increases, and we hit the EOL of MySQL 5.7 in October 2023, this issue is going to affect more and more people.

I strongly suggest we plan to upgrade our MySQL client to one which properly supports MySQL 8.0.

[thesuperzapper]

@ryanpeters-google @XinranTang I would greatly appreciate your feedback on this, or if you could tag whoever is the current maintainer of the project.

[XinranTang]

Hi Mathew, thanks for raising this issue. We will take MySQL 8.0 migration into consideration.
The timeline estimate is unclear until we determine its priority internally. I will be the POC of this project. Please feel free to provide more contexts if you would like to :)

[nithin8702]

We have upgraded mysql db from 5 to 8 and now its causing issue related to mlmd authentication plugin. i think kfp uses mysql_native_password type whereas mlmd requires caching_sha2_password. could anyone please how to overcome this? Is it possible to change mlmd to kfp like mysql_native_password ?

#178
kubeflow/pipelines#9549

@chensun @thesuperzapper