2 Replies Latest reply on Jan 28, 2009 4:51 AM by brettcave

    mysql datasource with read-only and write nodes

    brettcave
    brettcave Jan 23, 2009 5:07 AM

      Hi,

      I am almost sure i came across some documentation a while ago on how to configure a mysql datasource to specify readonly and write nodes. I found this thread in the forums:
      http://www.jboss.com/index.html?module=bb&op=viewtopic&t=52346
      but no real resolution on it.

      Is this possible? e.g
      1 datasource, with 2 mysql servers defined, and 1 configured as readonly server, while all writes are directed to the other.

      also, with regards to the post listed above, what is CMP referring to (excuse my ignorance)

      • 1306 Views
      • Tags:
        • 1. Re: mysql datasource with read-only and write nodes
          peterj
          peterj Jan 23, 2009 3:19 PM (in response to brettcave)

          This would require that you be able to set two connection URLs for a single datasource, and mark one to be used for reads and the other for updates. Such a thing is not possible at this time. The only option is to define two different data sources, configuring one to open the database in read-only mode if the database supports such a thing, and have your code select the data source it wants based on its needs.

            • Actions
          • 2. Re: mysql datasource with read-only and write nodes
            brettcave
            brettcave Jan 28, 2009 4:51 AM (in response to brettcave)

            Ok, thanks

            The datasources are used for persistence, so the workaround that I did was similar to the post i mentioned.

            1 - mysql: grant select only on RO nodes, and insert / update / etc on RW nodes
            2 - create 2 datasources, to ro and rw nodes
            3 - create 2 persistence units, each one using a different datasource
            4 - set persistencecontext / unit in the bean. implement all database writes in a different bean, using the rw persistence unit.


              • Actions