CODEWITHSUNDEEP
javamysqljdbcspring-bootmysql-connector
Sorin Penteleiciuc
Sorin Penteleiciuc

Reputation: 865

MySQL JDBC URL Failover with 2 different database names

I would like to have a jdbc url with 2 different schema/database names in application.yml in a SpringBoot application.

I tried these out by following Reference Link but unfortunately I was not able to get this working.

jdbc:mysql://address=(type=master)(protocol=tcp)(host=IP1)(port=3306)(user=root)(password=root)/dbname1?failOverReadOnly=false,address=(type=master)(protocol=tcp)(host=IP2)(port=3306)(user=test)(password=test)/dbname2?failOverReadOnly=false

jdbc:mysql://address=(type=master)(protocol=tcp)(host=IP1)(port=3306)(user=root)(password=root)(dbname=dbname1)?failOverReadOnly=false,address=(type=master)(protocol=tcp)(host=IP2)(port=3306)(user=test)(password=test)(dbname=dbname2)?failOverReadOnly=false

The error I am receiving is at starting the application because the MySQL connector is not able to parse the url connections string.

2017-02-21 11:37:40.724] log4j - 3060 ERROR [main] --- o.a.t.j.p.ConnectionPool: Unable to create initial connections of pool.
java.sql.SQLException: The connection property 'failOverReadOnly' only accepts values of the form: 'true', 'false', 'yes' or 'no'. The value 'false,address=(type=master)(protocol=tcp)(host=localhost)(port=3306)(user=test)(password=test)/dbname2?failOverReadOnly=false' is not in this set.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)

Upvotes: 3

Views: 4706

Answers (2)

Surabhi Pandey
Surabhi Pandey

Reputation: 31

Its an old thread, and I don't know if you have got the solution worked out for you.

But I have been in the similar situation and JDBC URL below worked like a charm for me

"jdbc:mysql://IP1:port1,IP2:port2/CommonDbName?failOverReadOnly=false"

In this situation at any point of time your application will be connected to only one db, which is on IP1, and once this db is down, it will switch to second DB i.e. IP2, and since you have marked failOverReadOnly to false, second DB will be active in both read/write mode.

Reference -

8.1 Configuring Server Failover

Upvotes: 3

Sabir Khan
Sabir Khan

Reputation: 10142

I guess, error message is pretty straight forward- The connection property 'failOverReadOnly' only accepts values of the form: 'true', 'false', 'yes' or 'no'. The value 'false,address=(type=master)(protocol=tcp)(host=localhost)(port=3306)(user=test)(password=test)/dbname2?failOverReadOnly=false' is not in this set.

Your value of property - failOverReadOnly is taken as - false,address=(type=master)(protocol=tcp)(host=localhost)(port=3306)(user=test)(password=test)/dbname2?failOverReadOnly=false instead of simply false.

Format - 

jdbc:mysql://address=(key1=value)[(key2=value)]...[,address=(key3=value)[(key4=value)]...]...[/[database]]»
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

is specifying to list all address first then all properties in the end while you wrote properties just after one address.

All in all, your URLs are not in format mentioned in documentation.

Hope it helps !!

Upvotes: 1

Related Questions