I am trying to connect Looker to an Azure MySQL db, and a few weeks ago I followed Google’s tutorials and successfully established a connection and created a couple dashboards. This week, it seems that that connection is no longer available (despite there not having been any changes to the underlying DB or user permissions). I have tried reconfiguring the connection, and even creating a new user from scratch but I still cannot seem to connect Looker to this db anymore.
The error I get when I try to test the connection now is
• Can connect
• Test kill: Cannot cancel queries: Java::JavaSql::SQLNonTransientConnectionException: (conn=62487) Connection reset
• Test query Cancelled
• [optional] Can check database mysql_8 version (8.0.15)
• Cannot create temporary tables[temporary indexed table] Java::JavaSql::SQLSyntaxErrorException: (conn=62518) Access denied for user 'looker'@'%' to database 'looker_tmp'
• Test connect: Cannot connect: connection refused: Java::JavaSql::SQLNonTransientConnectionException: Could not connect to address=(host=selfheal.mysql.database.azure.com)(port=3306)(type=master) : Client does not support authentication protocol requested by server. plugin type was = 'mysql_native_password'
• Testing kill...
• Testing query...
• Testing database_version...
• Testing mysql_tmp_tables...
Sometimes the cannot connect is on the test kill, which step fails seems inconsistent each time I refresh the page and attempt to test the connection.
I made a new user with
CREATE USER 'looker'@'%' IDENTIFIED WITH mysql_native_password BY [my password];
GRANT ALL PRIVILEGES ON selfheal . * TO 'looker'@'%';
But I am still seeing the above errors. Also I tried to establish this connection as both MySQL 8.0.12+ and MySQL but both have this same issue.
As mentioned before, the issue has been going on for about a week, we were successfully able to connect to the database last week and when I came in on Monday to check something (having made no changes to our Looker instance or database settings) we were having this issue to connect.
The console error when I click test for testing the connection is just
“Failed to load resource: the server responded with a status of 404 (Not Found)”
Cloud you please provide some advice to solve this issue?
Thank you in advance,