dropwizard and MySQL replication

[Francesco Pace]

I'm using RDS Amazon service with a MySQL master node and a MySQL replica (for read-only purpose).

I've in Maven:

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>5.1.29</version>

        </dependency>

and I'm using MySQL 5.6.

I've followed the mysql tutorial, and in my .yml configuration I've this properties in the database section:

database:

  driverClass: com.mysql.jdbc.ReplicationDriver

  user: myuser

  password: mypass

  url: jdbc:mysql:replication://url_master:3306,url_slave:3306/chl_db_test?autoCommit=false

  properties:

    charSet: utf8mb4

    autoCommit: false

  maxWaitForConnection: 1s

  validationQuery: "SELECT 1"

  minSize: 11

  maxSize: 32

  checkConnectionWhileIdle: false

  checkConnectionHealthWhenIdleFor: 10s

  closeConnectionIfIdleFor: 1 minute

  autoCommentsEnabled: true

For any resource, I've set the @UnitOfWork annotation with:

• @UnitOfWork(readOnly = true, cacheMode = CacheMode.GET) for read-only queries (SLAVE)
  
• @UnitOfWork for read-write queries (MASTER)
  

But if I watch my mysql nodes all the queries has sent to master node.

Where I wrong?

[Matt Brown]

I haven't used either of these, but it looks to me like com.mysql.jdbc.ReplicationDriver decides to send queries to the slaves when you set Connection.setReadOnly(true): http://dev.mysql.com/doc/connector-j/en/connector-j-master-slave-replication-connection.html

And in dropwizard's UnitOfWorkDispatcher, it calls setDefaultReadOnly on the Hibernate Session, not the JDBC connection:

    private void configureSession(Session session) {

        session.setDefaultReadOnly(unitOfWork.readOnly());

        session.setCacheMode(unitOfWork.cacheMode());

        session.setFlushMode(unitOfWork.flushMode());

    }

I believe that Session.setDefaultReadOnly only affects Hibernate behavior in treating any entities it loads as read-only and not writing any changes made to the object to the database; sounds like Hibernate doesn't make the underlying JDBC connection readonly.

So perhaps one solution to your issue is to extend UnitOfWorkDispatcher or add your own annotation/dispatcher to call setReadOnly(true) on the actual JDBC connection.

[Francesco Pace]

Hi Matt,
Thanks for your response.

I've implemented a new UnitOfWork for this purpose and works correctly.

Can I participate to project with a pull request with this new feature?

[Matt Brown]

I don't think anyone needs permission to create a pull request :) I would recommend always just giving it a shot and seeing what the committers think

--
You received this message because you are subscribed to a topic in the Google Groups "dropwizard-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/dropwizard-user/nxURxVWDtEY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to dropwizard-us...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[S Ahmed]

This is something I would be interested in also, very much appreciated.

--
You received this message because you are subscribed to the Google Groups "dropwizard-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-us...@googlegroups.com.

[Martin Konecny]

Just an FYI to whoever stumbles across this in 2019. com.mysql.jdbc.ReplicationDriver is not the way to approach this with AWS Aurora. 

You are supposed to use the MariaDB connector as documented on the AWS webpage: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Connecting.html

The MariaDB connector connects to your cluster (not to a db instance), and the cluster handles loadbalancing and connector works with the cluster to handle failover.