Big Query can't connect to Cloud SQL after upadte from MySql 5.7 to 8.0

[cputoaster]

I have the a similar problem to this here https://www.googlecloudcommunity.com/gc/Data-Analytics/Big-Query-can-t-connect-to-Cloud-SQL/m-p/5518... , but the suggestions there don't seem to work. When trying to execute a query I get this:

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]

When trying to query from BigQuery to a MySQL 8.0.37 Cloud SQL db. Tried with two different users. Works fine with those credentials from the Cloud SQL Studio. This used to work before upgrading to 8.0 from 5.7. I did change the auth type for the user to caching_sha2_password and the db is running with caching_sha2_password as default_authentication_plugin.

The Cloud SQL server log shows:

[Note] [MY-010914] [Server] Got an error reading communication packets

for each query attempt.

External (mysql commandline and Intellij datagrid) and cloud run JDBC connections work too, just not the BigQuery External access.

Any ideas?

[ronnelg]

Hi @cputoaster, 

Welcome to Google Cloud Community!

The new default authentication plugin for MySQL 8.0 is now caching_sha2_password from mysql_native_password. The issue is most likely because the caching_sha2_password is not yet supported by the BigQuery federated queries.

As a workaround, you may consider using mysql_native_password as your authentication for the meantime, while waiting for the ongoing feature request to support the BigQuery federated queries. However, I can’t give a timeline as to when this will be implemented, but you can view and comment on the request.

I hope the above information is helpful.