Troubleshooting Java Connection to MySQL About a week ago David - - PDF document

Troubleshooting Java Connection to MySQL

About a week ago posted some Java code in the #experts channel. David Busby This technote will explain the steps needed to make it work and how it has been fully tested against MySQL installed in the previous blog from November 2018. First of all the code:

// Sample from #experts import java.sql.*; class MysqlCon { public static void main(String args[]) { try { Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://192.168.33.10:3307/sonno","r

• ot","Passw0rd!");

//here sonno is the database name, root is the username and password Statement stmt=con.createStatement(); ResultSet rs=stmt.executeQuery("select * from emp"); while(rs.next()) System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)); con.close(); } catch(Exception e) { System.out.println(e); } } }

The Java compiler is not installed by default on the CentOS VM, so one will need to run something like this as : root

yum install java-1.8.0-openjdk-devel.x86_64

After Java compiler is installed it is time to attempt to compile the code above. javac There should be a .java file with the same name of the class: . MysqlCon.java Note Java is case sensitive so make sure the file and class name have an exact match.

javac MysqlCon.java

The prompt should return silently and the compiler should have by then created a file with the same name. .class

$ ls -la total 556 drwxrwxr-x. 2 percona percona 4096 Jan 25 14:36 . drwx------. 17 percona percona 4096 Jan 25 14:36 ..

• rw-rw-r--. 1 percona percona 1532 Jan 31 16:01 MysqlCon.class
• rw-rw-r--. 1 percona percona 652 Jan 25 14:36 MysqlCon.java
• rw-r--r--. 1 percona percona 540852 Jan 25 14:16 mysql-connector.jar

Note that has already been downloaded and placed in the same directory as the Java code. mysql-connector.jar So what happens if we attempt to run this now:

$ java -cp . MysqlCon java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

The JVM can not find the class even setting the classpath (

• ption) to look at the current directory.

com.mysql.jdbc.Driver

• cp

In order to solve this problem you need to place the connector jar file under the JDK /jre/lib/ext.

$ pwd /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.191.b12-1.el7_6.x86_64/jre/lib/ext $ ls -la mysql-connector.jar

• rw-r--r--. 1 root root 540852 Jan 25 14:16 mysql-connector.jar

Now to another attempt to run:

$ java MysqlCon com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.ConnectException MESSAGE: Connection refused (Connection refused) STACKTRACE: java.net.ConnectException: Connection refused (Connection refused) at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:35 0) at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl. java:206) at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) at java.net.Socket.connect(Socket.java:589) at java.net.Socket.connect(Socket.java:538) at java.net.Socket.<init>(Socket.java:434) at java.net.Socket.<init>(Socket.java:244) at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:25 6) at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:271) at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771) at com.mysql.jdbc.Connection.<init>(Connection.java:1555) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at MysqlCon.main(MysqlCon.java:12) ** END NESTED EXCEPTION ** Last packet sent to the server was 1 ms ago.

It turns out that the connection was being attempted at port 3307 but by default MySQL was listening to port 3306. Changed the JDBC connection string to reflect that:

Connection con=DriverManager.getConnection("jdbc:mysql://192.168.33.10:3306/sonno","r

• ot","Passw0rd!");

Compile the code and attempt to run it once again:

$ java MysqlCon java.sql.SQLException: null, message from server: "Host '192.168.33.11' is not allowed to connect to this MySQL server"

What now? The host is reachable at the correct port but still no luck. The solution is to grant the required privilege at MySQL level so that the connection can be established:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'PASSWORD'

Now the code runs successfully:

[percona@pmm javacode]$ java MysqlCon [percona@pmm javacode]$

It does not return anything though... Well there was no data in the table to be retrieved! emp So we added some data to the table:

mysql> show tables; +-----------------+ | Tables_in_sonno | +-----------------+ | emp | +-----------------+ 1 row in set (0.00 sec) mysql> insert into emp(id,name,age) values (1,'Percona Live',10); Query OK, 1 row affected (0.01 sec) mysql> select * from emp; +------+--------------+------+ | id | name | age | +------+--------------+------+ | 1 | Percona Live | 10 | +------+--------------+------+ 1 row in set (0.00 sec) mysql>

And now our code is ready to be up and running:

[percona@pmm javacode]$ java MysqlCon 1 Percona Live 10 [percona@pmm javacode]$

Hope you enjoy this post. It was fun !