Spring Boot - Unable to connect Azure MySQL database

Question

I am new in connecting azure MySQL database from Spring boot application.

Below is the snippet mentioned in application.properties

    spring.datasource.url=jdbc:mysql://XXXX.mysql.database.azure.com:3306/MyDbName
spring.datasource.username= ******
spring.datasource.password= ******

I have faced the following exception

org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta data; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Unknown system variable 'query_cache_size'

I have googled and somewhere it was mentioned that it is required to update the version of MySQLConnector to 8.0.13

Please help me find the solution.

Answer 1

Based on your exception , its not because of Azure mysql. Its because of system variable 'query_cache_size'. refer : java.sql.SQLException: Unknown system variable 'query_cache_size'

Answer 2

I too have met this bundle of joy on azure MySQL. As dvo mentions it places this variable in when it thinks it is under version 8.0.3.

From com.mysql.cj.NativeSession.java...

if (!versionMeetsMinimum(8, 0, 3)) {
    queryBuf.append(", @@query_cache_size AS query_cache_size");
    queryBuf.append(", @@query_cache_type AS query_cache_type");
}

But as my Azure MySQL version is 8.0.15 which matches my driver version then I suspect whilst the database may well be version 8 part of the stack the driver deals with is not.

Edit:

From https://learn.microsoft.com/en-us/azure/mysql/concepts-limits

Current known issues

MySQL server instance displays the wrong server version after connection is established. To get the correct server instance engine version, use the select version(); command.

So I'm guessing that this is the problem and as it stands Azure MySQL 8 will not work with the java mysql connector.

I'll probably look at building the connector myself and hard coding in the version number to 8.0.15. What joy.

Edit Edit:

Modified ServerVersion.java parseVersion(…) to return

return new ServerVersion(8, 0, 15);

Which works around the problem enough for me to carry on with my work.

It's a problem Microsoft should be fixing ASAP on their side.