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?
LinkedIn
Twitter
