Ibatis and MySQL replication

[Max-2]

We might need to add some database servers (master+N*slave) in a
couple of weeks. We are using Ibatis (2.3.4.726)  as our DB layer
right now. The MySQL java connector class has a ReplicationDriver [1]
already in place. All you have to tell the connection [2] provided by
this driver is to set the attribute "readonly" prior to the
transaction and it will direct the transaction to the master or slave
DB instances.
Unfortunately, Ibatis seems to not set this attribute and is therefore
not working with ReplicationDriver out-of-the-box.
We thought we would ask here before possibly wasting hours hacking
Ibatis low-level with interceptors...

Does anyone have an idea where we need to tell Ibatis to modify the
connection object?

Other solutions are welcome too, of course.

Thanks,
  Max

[1] http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html
[2] http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Connection.html

[François Schiettecatte]

I can pass options to the mysql java connector via the URL, like this:


private static final String JDBC_MYSQL_URL_QUERY_OPTIONS = "characterEncoding=UTF-8&useUnicode=true&elideSetAutoCommits=true&cacheServerConfiguration=true";

final Properties properties = new Properties();
final String url = "jdbc:mysql://" + hostName + "/" + databaseName + "?" + MyBatis.JDBC_MYSQL_URL_QUERY_OPTIONS;
properties.setProperty("url", url);
properties.setProperty("username", userName);
properties.setProperty("password", password);

// Create the sql session factory
final SqlSessionFactory sqlSessionFactory = MyBatis.sqlSessionFactoryBuilder.build(reader, properties);


Is this what you are looking for?

François

On Sep 7, 2010, at 11:16 AM, Max wrote:

> We might need to add some database servers (master+N*slave) in a
> couple of weeks. We are using Ibatis (2.3.4.726)  as our DB layer
> right now. The MySQL java connector class has a ReplicationDriver [1]
> already in place. All you have to tell the connection [2] provided by
> this driver is to set the attribute "readonly" prior to the
> transaction and it will direct the transaction to the master or slave
> DB instances.
> Unfortunately, Ibatis seems to not set this attribute and is therefore
> not working with ReplicationDriver out-of-the-box.
> We thought we would ask here before possibly wasting hours hacking
> Ibatis low-level with interceptors...
>
> Does anyone have an idea where we need to tell Ibatis to modify the
> connection object?
>
> Other solutions are welcome too, of course.
>
> Thanks,
>  Max
>
> [1] http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html
> [2] http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Connection.html

[Max-2]

On Sep 7, 5:28 pm, François Schiettecatte <[hidden email]>
wrote:
> [...]
>
> Is this what you are looking for?

No. What we need is set in runtime. From [1]:

<code>
Connection conn = driver.connect("jdbc:mysql:replication://
master,slave1,slave2,slave3/test", props);

conn.setReadOnly(false);
conn.setAutoCommit(false);
conn.createStatement().executeUpdate("UPDATE some_table ....");
conn.commit();

conn.setReadOnly(true);
ResultSet rs =  conn.createStatement().executeQuery("SELECT a,b FROM
alt_table");
</code>

As you see, you can set the connection readonly=false when you want to
modify the DB. Set it (back) to readonly=true when you are only
reading from the DB.

We need this done automatically within Ibatis at runtime.

-- Max

[1] http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html

[Rick.Wellman]

The following suggestion has many "cons" but if you're in a pinch you might consider somehow separating your read-only sqlmap from your 'update' sqlmap (each of which would maintain a separate connection pool configured accordingly).  [I know that is really low tech but if you're looking to convert legacy code, maybe it is an acceptable first step?]

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Max
Sent: Tuesday, September 07, 2010 10:46 AM
To: mybatis-user
Subject: Re: Ibatis and MySQL replication

On Sep 7, 5:28 pm, François Schiettecatte <[hidden email]>
wrote:
> [...]
>
> Is this what you are looking for?

No. What we need is set in runtime. From [1]:

<code>
Connection conn = driver.connect("jdbc:mysql:replication://
master,slave1,slave2,slave3/test", props);

conn.setReadOnly(false);
conn.setAutoCommit(false);
conn.createStatement().executeUpdate("UPDATE some_table ....");
conn.commit();

conn.setReadOnly(true);
ResultSet rs =  conn.createStatement().executeQuery("SELECT a,b FROM
alt_table");
</code>

As you see, you can set the connection readonly=false when you want to
modify the DB. Set it (back) to readonly=true when you are only
reading from the DB.

We need this done automatically within Ibatis at runtime.

-- Max

[1] http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-replication-connection.html

[Max-2]

On Sep 7, 5:50 pm, Rick.Wellman <[hidden email]> wrote:
> The following suggestion has many "cons" but if you're in a pinch you might consider
> somehow separating your read-only sqlmap from your 'update' sqlmap (each of which
> would maintain a separate connection pool configured accordingly).  [I know that is really
> low tech but if you're looking to convert legacy code, maybe it is an acceptable first step?]

Thanks for your suggestion, but this would be our method of choice
only if nothing else works.

[Roman Li]

Try this:

SqlSession session = sqlFac.openSession(autoCommit);
session.getConnection().setReadOnly(true);

在 2010年9月8日星期三UTC+8上午12时30分55秒，Max写道：

On Sep 7, 5:50 pm, Rick.Wellman <[hidden email]> wrote:
> The following suggestion has many "cons" but if you're in a pinch you might consider
> somehow separating your read-only sqlmap from your 'update' sqlmap (each of which
> would maintain a separate connection pool configured accordingly).  [I know that is really
> low tech but if you're looking to convert legacy code, maybe it is an acceptable first step?]

Thanks for your suggestion, but this would be our method of choice
only if nothing else works.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ted Shaw]

If you are using ibatis with spring, you can extend DelegatingDataSource and overwrite the getConnection() to call setReadonly

Refer to http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/datasource/DelegatingDataSource.html

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.