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)
