1

I was debating whether to post this question here or on Stack Overflow but through my own examination, I believe this to be a server-side issue over Java code being wrong. My goal for this project is to be able to make write requests and verify that both servers are, in fact, replicating properly and do all sorts of other fun tests like see if the slave server can pick up where the master left off after it went down. I am new to fairly new to MariaDB, Galera, and CentOS while only a bit familiar with MySQL but I've been using Java for a while now. I slept on trying to fix this and hoped maybe sleep would allow me to think of an answer the next day (6/7) but I'm at a loss.

Just a little information on what I'm using before I get too far into explaining my issue:

  • Eclipse version: Neon.3, 4.6.3
  • MariaDB versions: 10.1-24
  • Virtual Servers' OSs: CentOS 7
  • Galera cluster size: 2*
  • MariaDB Connector/J version: 2.0.1
  • PuTTY 32-bit
  • Server IPs: 10.32.18.90 and 10.32.18.91

*I know the recommended number is three but two is all I need for now just for testing purposes. Many more will be added on during production of applications, etc.

The issue is that the Java application cannot connect to the servers no matter what form of the JDBC URL I use. The difference in usage, however, does impact how much time passes before I receive an error. Using just the jdbc:mariadb://... URL gets me an error pretty quickly (about 1 minute before the connection is closed) while using jdbc:mariadb:replication://... nets me an upwards of twenty minutes down time between tests. I can connect to the servers using PuTTY and access the MySQL consoles. However, for some reason I get a java.sql.SQLNonTransientConnectionException error in Eclipse when I try to run the application. For the record, my URL looks like this:

"jdbc:mysql:replication://10.32.18.90,10.32.18.91/replicationTest"

The user name and password are handled in separate parameters using the Java DriverManager.getConnection(String URL, String user, String password) function. It doesn't connect to either server, I may add, if I use one or the other as the Master or even individually.

The "application" is very simple: load the MariaDB driver (print whether it failed or not), open up a connection (printing when it's opened or failed), and then close the connection (whether it's succeed in failing gracefully or if a crash). No testing is being run on the server as of now.

I went and I re-checked the connection between the databases at that point to find that replication was still working. I tested to make sure replication and the servers were still on by creating a new database with a new table in both servers and then dropped the database in one to make sure the change carried over.

So, I decided to check systemctl status mysql to see what the issue was and had this returned to me (truncated to save space and because there are no error messages):

mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since Tue 2017-06-06 16:11:46 EDT; 1h 0min ago
  Process: 3044 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 2905 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 2903 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 3011 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─3011 /usr/sbin/mysqld --wsrep_start_position=08da8ce4-4a14-11e7-a87c-bff9794df6aa:7

So systemctl reports the MySQL is on and running fine. Alright, I said to myself, then why aren't you connecting? So then I decided to check service mysql status to see if that was running as well. I received this when I passed this input into the console:

ERROR! MySQL is not running

Now, being the new guy to MariaDB I'm assuming the error comes from this issue. I am running as the root user so that shouldn't be an issue. I've done the same two commands on the other server and got the same issue. I've checked my /etc/my.cnf.d/server.cnf file and it looked in order from what I stitched together looking at other people's progress.

Here is the server.cnf file with relevant sections in the event that this could be an issue:

[mysqld]
bind-address=0.0.0.0

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
default_storage_engine=InnoDB
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_cluster_address="gcomm://10.32.18.90,10.32.18.91"
#wsrep_cluster_address="gcomm://"
wsrep_cluster_name='galera'
wsrep_node_address='10.32.18.90'
wsrep_node_name='node1'
wsrep_sst_method=rsync
wsrep_sst_auth=test:PASS

*Note: The other is the same look with node name being node2 and the node address being 10.32.18.91.

Again, I think this is an issue with MySQL and the way I set it up but I'm open to being wrong. I hope I'm wrong, at least. I also think it could be the version of my Connector/J being too the incorrect version needed. Of the two, I hope it's the latter because it'd be an easy fix. If I have missed any information you might need, please do let me know and I'll append it onto the question as quickly as I can.

TL;DR:

Goal: Connect a Java application being written in Eclipse to two MariaDB servers in a Galera cluster to test replication, failover, and capacity

Issue: I have two MariaDB servers in a Galera cluster. The cluster works and I can see them replicating properly in the MySQL console. However, when I try to connect to it via application in Java using the Connector/J, I am unable to hook into the server as the error in Java says that the IPs are not reachable. The servers are virtual and are using IPs that I can ping to from my Windows console.

Is this an issue with the version of my Connector/J to MariaDB or is it something deeper than that?

Resources I have Consulted*

  • "Similar Questions" to the right of this box I wrote in
  • MairaDB Cookbook by Daniel Bartholomew
  • Stack Overflow for issues on it being an implementation issue in Java
  • The MariaDB website
  • The Galera website
  • Database Administrators to see if someone else has had at least a similar issue

*Could I have been searching using the wrong phrases?

EDIT

I should mention that the errors are occurring because the attempt to connect times out before the connection can even be made as stated by this error:

Caused by: java.net.ConnectException: Connection timed out: connect
    at java.net.DualStackPlainSocketImpl.connect0(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connect(AbstractConnectProtocol.java:392)
    at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1013)
0

Can you try connecting to a single machine on your cluster? Your client will only need to connect to a single node on the cluster, and Galera will handle all the replication between the cluster nodes.

So your URL might look something like: "jdbc:mysql://10.32.18.90/replicationTest"

  • Surprisingly that did not work. I used the URL you provided and then changed "mysql" to "mariadb" to see if something would happen but that did not work either. – Mikey G Jun 7 '17 at 16:30
  • Hi @Mikey G, I don't think the issue is with the url in that case. I just connected to my production cluster (Also running mariadb 10.1 with galera replication) using this type of url: – Mark S Jun 7 '17 at 18:08
  • Hi @Mikey G, Sorry last comment got cut off. I don't think the issue is with the url in that case. I just connected to my production cluster (Also running mariadb 10.1 / galera replication) using this url: jdbc:mysql://hostname.mydomain.local Have you been able to connect any client to your mariadb instance? mysql workbench, or the mysql command line client? – Mark S Jun 7 '17 at 18:25
  • I am able to get into the MySQL command line. I'm using PuTTY and CentOS 7 as I described in the question so Workbench is a no-go as I don't have an interface to play with. – Mikey G Jun 7 '17 at 19:57
  • Is it true that you have only connected a client running on the server machine? I.e. you've connected to 10.32.18.90, from mysql shell also on 10.32.18.90. If so, can you please try the following to confirm mysql is listening as expected? From your terminal on 10.32.18.90: mysql -u root -p --port 3306 -h 10.32.18.90. Reason - when you launch mysql shell, without specifying a host, by default it will connect using a socket File. Your Java app connecting over the network. If that is successful, try connecting accross machines as well: mysql -u root -p --port 3306 -h 10.32.18.91 – Mark S Jun 7 '17 at 20:36
0

Solved my own issue after working with someone else. The basic, easy solution was:

  • In PuTTY, before entering the terminal for either server, go to Connections > SSH > Tunnels.
  • For the source port on the first server, use 3306 and then in the field right below it (Destination), input "localhost:3306" and start the server
  • For the source port on the second server, use 3307 and then in Destination put localhost:3306 and start the server
  • The Java connector should now look like this: jdbc:[mariadb|mysql]://localhost:3306, localhost:3307/DB

It's a hack and a rudimentary way around a big problem but for those just testing and having a similar issue, do this. I would not recommend this method for production.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.