[CONJ-757] Cannot change ws_rep variables Created: 2020-01-21  Updated: 2020-03-06  Resolved: 2020-03-06

Status: Closed
Project: MariaDB Connector/J
Component/s: configuration
Affects Version/s: 2.5.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Levieux stéphane Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

We would like to implement new 10.4 feature of Streaming Replication.
It's necessary to change some variable at session scope, so we tried to change the url of the connector jdbc by appending :
&sessionVariables=wsrep_trx_fragment_unit=rows,wsrep_trx_fragment_size=10000

Then we had an error ( cf trace at the end ) .
After several tests, it seems we cannot change any wsrep variables.
I tried to change another global variable and it worked contrary to any wsrep variable .

Is it a limitation of the Connector ?
Is it the correct way to change wsrep_trx_fragment_unit and wsrep_trx_fragment_size at session scope in java ?

java.sql.SQLSyntaxErrorException: Could not connect to address=(host=10.253.253.78)(port=3306)(type=master) : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows,wsrep_trx_fragment_size=10000' at line 1
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:243) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1241) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:610) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:142) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.Driver.connect(Driver.java:86) ~[mariadb-java-client-2.5.3.jar:?]
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175) ~[c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220) ~[c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206) ~[c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203) ~[c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1176) ~[c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1163) ~[c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44) ~[c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1908) [c3p0-0.9.5.5.jar:0.9.5.5]
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696) [mchange-commons-java-0.2.19.jar:0.2.19]
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows,wsrep_trx_fragment_size=10000' at line 1
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1599) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1461) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1424) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.additionalData(AbstractConnectProtocol.java:996) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.postConnectionQueries(AbstractConnectProtocol.java:800) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:549) ~[mariadb-java-client-2.5.3.jar:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1236) ~[mariadb-java-client-2.5.3.jar:?]

 Comments   
Comment by Diego Dupin [ 2020-01-27 ]

You'll have the same issue compared to directly using command: 

set wsrep_trx_fragment_unit=rows, wsrep_trx_fragment_size=10000;

I don't have a server with replication to confirm that, but solution is probably to add quote to rows:

set wsrep_trx_fragment_unit='rows', wsrep_trx_fragment_size=10000;

meaning using connection string 

"...&sessionVariables=wsrep_trx_fragment_unit='rows',wsrep_trx_fragment_size=10000"

Comment by Levieux stéphane [ 2020-01-29 ]

Thanks for your answer .
Pehaps you' re right but we already have this kind of parameters in url without quotes and it works ( ?autoReconnect=true&jdbcCompliantTruncation=false )
) it's different for sessionVariables values ?
Since my post, i tried another method, we change parameters with queries "SET SESSION" ( jdbcprop is a custom object) and it works

stmtSet.execute("SET SESSION wsrep_trx_fragment_unit='"jdbcprop.get("wsrep_trx_fragment_unit")"'");
stmtSet.execute("SET SESSION wsrep_trx_fragment_size="+jdbcprop.get("wsrep_trx_fragment_size"));

if i have time i will try with sessionVariables and quotes.

Comment by Diego Dupin [ 2020-01-30 ]

autoReconnect=true is a java option
sessionVariables values are quite different, because will be executed on server.

Just for an example : 

set wsrep_trx_fragment_unit=rows;
 
//return SQL (1064) : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows' at line 1 
 
 
set wsrep_trx_fragment_unit='rows';
 
/return (1193) : Unknown system variable 'wsrep_trx_fragment_unit'
 
//because not using galera cluster

Using "SET SESSION" is a good solution. Using 'rows' will do exactly the same, just saving 2 queries on connection creation.

Comment by Diego Dupin [ 2020-03-06 ]

Closing as not a bug, because exception indicate the error.

Generated at Thu Feb 08 03:18:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.