Spring boot CLIENT_PLUGIN_AUTH is required

Question

I have my app working fine on local, but when I tried to connect to remote server I get this error: CLIENT_PLUGIN_AUTH is required.

server.port=8081
spring.jpa.hibernate.ddl-auto=none
#local
#spring.datasource.url=jdbc:mysql://localhost:3306/dbtest
#spring.datasource.username=user
#spring.datasource.password=password
#remote
spring.datasource.url=jdbc:mysql://userssh:passwordssh@xxx.xxx.xxx.xxx:3306/dbtest
spring.datasource.username=userRemote
spring.datasource.password=passRemote

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Answer 1

I got it.

Step 1. Create an user on remote mysql server and grant all privileges.

Step 2. Change datasource url

spring.datasource.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/dbtest

Step 3. Change pom.xml mysql

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.6</version>
</dependency>

Check that version is changed to <version>5.1.6</version> from <scope>runtime</scope>

Answer 2

you need to find a compatible version for your MySQL installed version

Default one is

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

Change it to some version

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>

Now remove the default dependency installed. "package explorer"> your project > "Maven dependencies">type MySql it will come, now delete it(mysql-connector-java-x.y.z.jar)

Try to give a version nearer to your MySql version & check maven repository and find a nearer version having more usage

For me, MySql version is 5.0.27 which you can see from "MySQL command-line client" and the version I tried is 5.1.46

1 more thing is to add to the application.proerties

spring.datasource.driver-class-name= com.mysql.jdbc.Driver

But, if you are taking MySql dependency version nearer to 8 it is

spring.datasource.driver-class-name= com.mysql.cj.jdbc.Driver

which is not required as it takes internally

Now do "clean install" your app, right-click "run as">mvn clean & "run as">mvn install, then build your application using right-click "maven">update project(Alt+F5)

Now to confirm version change, check maven dependency, mysql-connector-java-5.1.46.jar (whichever is your mentioned dependency)

And, if still not working make sure you have checked with some applications having localhost server. Then check /etc/drivers/hosts file & uncomment this line

#    127.0.0.1       localhost

Answer 3

Check that you do have a version issue.

The Spring Boot project creator will download the latest MySQL connector (currently Version 8) so if you are running an older version of MySQL then the CLIENT_PLUGIN_AUTH error probably results from this.

You can convince maven to use an older version of the connector (as in Satish's reply) or add the connector outside of Spring Boot:

Remove the mysql-connector-java dependency from your pom.xml

Download the mysql-connector-java for your MySQL – usually the one from your distro.

With: Project -> Properties -> Java Build Path

select the Libraries tab.

And add External JAR:

    /usr/share/java/mysql-connector-java-5.1.17.jar

or something similar.

Answer 4

Check if the following are configured correctly. This solved the problem for me. If you are new to Java environment then you should definitely look for these.

1) Check if the MySQL-connector-java driver version is suitable for the PySpark version that you are using. If not try changing it.

2) Make sure only one mysql-connector-java-.jar driver file is present in the folder that you are looking into. Remove the other versions from this location. There is a possibility that the other version is taken by PySpark

4) Verify if your environment variable SPARK_HOME is set correctly. To check this, copy the location that was given in the environment variable window and paste it in the file explorer (Windows). This should go to the correct location.

3) Confirm if your environment is using the mysql-connector-java-.jar driver file that you want it to use. One way to test this is to remove the file from this location and see if the error changes. If the same error (an error that was before deleting) occurs, then your environment is not using the driver file that you want it to use. Find the location of the file that your PySpark is using. Try changing this path (either by changing the environment variable or manually give the path in the code) or use this location and paste all the required driver files in that location

These were the checks I did and one of the above solutions should solve the issue provided there is nothing wrong with your code.

Answer 5

I've had similar problem just now. My local code was working fine, while my remote code wasn't working.

In local machine I've used

• jar: mysql-connector-java-8.0.17.jar
• driver: com.mysql.cj.jdbc.Driver

When I've changed jar and drived in remote server it started to work just fine. I've used:

• jar: mysql-connector-java-3.0.17-ga-bin.jar
• driver: com.mysql.jdbc.Driver

After that I didn't have any issues :)

Answer 6

This could be due to MySql database service itself. To resolve, just Restart Your MySQL Database

How to restart the MySQL service
On Windows: https://www.mysqltutorial.org/mysql-adminsitration/restart-mysql
On Linux:    https://linuxhint.com/restart-mysql-in-ubuntu/

Answer 7

This is just because of higher version, use version 5.x.x instead of version 8.

it will execute properly.