[SOLVED] GCP BigQuery external query to MySQL instance 8.4

GCP BigQuery external query to MySQL instance 8.4

I am getting the following error when trying to run an external query in BigQuery to a GCP MySQL instance:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

This is my query:

SELECT * FROM EXTERNAL_QUERY("myproject.us-east4.my-externalConnection", "SELECT * FROM INFORMATION_SCHEMA.TABLES;");

I have other external queries working on MySQL 8.0. I'm unable to figure out how to bypass this issue on my MySQL 8.4 instance.

Solution

MySQL 8.4 uses caching_sha2_password as its default authentication plugin, it is not supported by BigQuery’s external connection, try to connect using the old way mysql_native_password try this:

ALTER USER 'your_user'@'%'
 IDENTIFIED WITH mysql_native_password
 BY 'your_password';
FLUSH PRIVILEGES;

Or if you wanted to use the latest MySQL you can refer to this guide using the caching_sha2_password

Take note that in MySQL 9 mysql_native_password is no longer available