We're getting close to deploying one of our applications using ha-jdbc.
We had to make some modifications to the application to use ha-jdbc, but
nothing too bad.
In support of the applications, we like to use tools like SequelPro, a MySQL
tool for Mac OS X. It has native support for MySQL, but we do not have the
source for it, and, since it is written in ObjectiveC for the Mac, we couldn't
modify it to use ha-jdbc anyhow.
To allow us to continue to use tools like this, we are attempting to use
Tungsten-Connector as a proxy. It accepts native MySQL "wire protocol" from
programs like SequelPro and converts it back to JDBC calls for use with a
configured JDBC driver such as ha-jdbc.
Unfortunately, Tungsten Connector wants to generate JDBC URLs of the form
"jdbc:<driver>://<cluster>/<database>". The problem is that we could find no
way to configure Tungsten Connector to NOT include the <database> in the URL,
nor could we find anyway to configure ha-jdbc to deal with them adequately.
First of all, did we miss something? Is there a way to configure ha-jdbc to
reasonably deal with URLs that include a <database> specification? (This
probably isn't the right place for this, but is there a way to configure
Tungsten Connector to generate URLs that don't include the <database>
specification?)
To deal with this, we changed the URL_PATTERN in net.sf.hajdbc.sql.Driver from
"jdbc:hajdbc:(.+)?" to "jdbc:ha-jdbc://(+)(/.+)*", and this solves the problem
as far as Tungsten Connector and ha-jdbc are concerned.
Tools like SequelPro can now access an ha-jdbc cluster via Tungsten Connector,
but with a few tolerable restrictions.
I guess what we're asking for is a change or enhancement to ha-jdbc like we
made to make it more usable in situations where the user does not have
complete control over the URLs that are used.
Thanks in advance for your attention to this.
If anyone would like more information on configuring Tungsten Connector and
ha-jdbc to work together, let me know and I'll oblige as best I can.
Jim Renkel
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You are the 1st person of which I've heard that has tried to use HA-JDBC
w/Tugsten connector.
What would happen if you made "//cluster/database" the id of your cluster?
Does that break anything else? That may be a problem for mbean object names...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
In the little bit of playing around with "//<cluster>/<database>" style id's,
we ran into all kinds of problems: duplicate MBean object names; multiple
JGroups channels being opened (sometimes successfully, mostly not); etc.
Complicating the issue is the fact that Tungsten Connector issues JDBC connect
requests to the driver on its own, without any "prompting" from a client
connected to it. It uses a database name of "mysql" in the cases we saw (Which
sounds awfully dangerous), but then doesn't do anything with them. Maybe it's
trying to test whether the database is alive, or something, we don't know, and
haven't investigated any further.
That's why we resorted to the URL_PATTERN change in net.sf.hajdbc.sql.Driver.
We appreciate your offer to make this change a part of the code base. We
suggest when you do so that you make it a system property with a default value
of what it is now for backward compatibility. We, and others that need to do
so, could then change it as needed via the system property. (We only need this
when ha-jdbc is used with Tungsten Connector; our main applications don't
require it.)
Also, we suggest that you log, probably at the debug level, the value of the
URL being processed and the value of the cluster id that is extracted from it.
We had to do this in order to figure out the right pattern to use. :-)
Thanks in advance for your attention to this,
Jim Renkel
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am considering using Tungsten Connector to bridge a PHP application to a HA-
JDBC data source. I already have a HA-JDBC data source for my Java web
application in Tomcat, but want to add another data source to give HA DB
support to a bundled PHP application.
The marriage of Tungsten Connector to HA-JDBC is not unique. PostgreSQL wiki
mentions it in this Point Point.
What workarounds did you implement to bridge Tungsten Connector to HA-JDBC
data source?
I am stuck at the same spot as you. I run the Tungsten Connector application
in a thread of my web app (not as a Windows service via the bundled "Tanuki
Software Java Service Wrapper"). This is the code snippet if anyone is
interested:
Note: Tungsten Connector has dependencies on Tungsten Replication's tungsten-
commons.jar, and on Tungsten Enterprise's .manifest.json. Not explicitly using
other Tungsten Enterprise components if I can help it.
My web app successfully starts Tungsten Connector and listens on port 9999 for
native MySQL commands. I connect to port 9999 using MySQL command line client
with command "mysql -umyuser -pmypwd myschema -P9999 -hlocalhost".
I assume this is what you mean by the hostname problem. Tungsten Connector is
inserting 127.0.0.1 into the URL based on these settings from
connector.properties.
My question to you jrenkel: Were you successful making Tungsten Connector
proxy to your HA-JDBC data source, instead of your MySQL database.
a) Is it possible to tweak connector.properties in Tungsten Connector to
exclude the hostname?
b) Did you hack Tungsten Connector code to exclude the hostname?
c) Did you hack HA-JDBC code to ignore a hostname?
d) Any other ideas?
Your help would be much appreciated. Thanks.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
MySQL CLI native client connecting to Myosotis port 9999
mysql -umyuser -pmypasswd myschema -P9999 -hlocalhost
DEBUG - Start serving client: /127.0.0.1:63394
DEBUG - Connection string: jdbc:ha-jdbc:mycluster/myschema
DEBUG - User: myuser Password: mypasswd
NFO (AbstractDatabaseCluster.java:1033) Initializing HA-JDBC 2.0.15 from
file:/C:/tomcat/webapps/cspm/WEB-INF/classes/ha-jdbc-mycluster.xml
INFO (LocalStateManager.java:82) Using local initial cluster state .
INFO - Using local initial cluster state .
mysql> show databases like 'myschema%';
+-----------------+
| Database (myschema%) |
+-----------------+
| myschema1 |
| myschema2 |
+-----------------+
mysql> use myschema;
Database changed
Note: MySQL CLI client connects to myschema, but "show databases" says it does
not exist. The myschema is a virtual schema backed by two real schemas calls
myschema1 and myschema2.
MySQL CLI native client connecting to real port 3306
mysql -umyuser -pmypasswd -P3306 -hlocalhost
mysql> show databases like 'myschema%';
+-----------------+
| Database (myschema%) |
+-----------------+
| myschema1 |
| myschema2 |
+-----------------+
mysql> use myschema;
ERROR 1049 (42000): Unknown database 'myschema'
Note: MySQL native client connected to 3306 has no concept of virtual, only
real schemas myschema1 and myschema2. You only see the virtual "myschema" if
you connect to Myosotis port 9999 which proxies to HA-JDBC mycluster (where
myschema is truncated from url in HA-JDBC's Driver.java).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
We're getting close to deploying one of our applications using ha-jdbc.
We had to make some modifications to the application to use ha-jdbc, but
nothing too bad.
In support of the applications, we like to use tools like SequelPro, a MySQL
tool for Mac OS X. It has native support for MySQL, but we do not have the
source for it, and, since it is written in ObjectiveC for the Mac, we couldn't
modify it to use ha-jdbc anyhow.
To allow us to continue to use tools like this, we are attempting to use
Tungsten-Connector as a proxy. It accepts native MySQL "wire protocol" from
programs like SequelPro and converts it back to JDBC calls for use with a
configured JDBC driver such as ha-jdbc.
Unfortunately, Tungsten Connector wants to generate JDBC URLs of the form
"jdbc:<driver>://<cluster>/<database>". The problem is that we could find no
way to configure Tungsten Connector to NOT include the <database> in the URL,
nor could we find anyway to configure ha-jdbc to deal with them adequately.
First of all, did we miss something? Is there a way to configure ha-jdbc to
reasonably deal with URLs that include a <database> specification? (This
probably isn't the right place for this, but is there a way to configure
Tungsten Connector to generate URLs that don't include the <database>
specification?)
To deal with this, we changed the URL_PATTERN in net.sf.hajdbc.sql.Driver from
"jdbc:hajdbc:(.+)?" to "jdbc:ha-jdbc://(+)(/.+)*", and this solves the problem
as far as Tungsten Connector and ha-jdbc are concerned.
Tools like SequelPro can now access an ha-jdbc cluster via Tungsten Connector,
but with a few tolerable restrictions.
I guess what we're asking for is a change or enhancement to ha-jdbc like we
made to make it more usable in situations where the user does not have
complete control over the URLs that are used.
Thanks in advance for your attention to this.
If anyone would like more information on configuring Tungsten Connector and
ha-jdbc to work together, let me know and I'll oblige as best I can.
Jim Renkel
You are the 1st person of which I've heard that has tried to use HA-JDBC
w/Tugsten connector.
What would happen if you made "//cluster/database" the id of your cluster?
Does that break anything else? That may be a problem for mbean object names...
If the above doesn't work, I can modify the regex to accept this other format.
In the little bit of playing around with "//<cluster>/<database>" style id's,
we ran into all kinds of problems: duplicate MBean object names; multiple
JGroups channels being opened (sometimes successfully, mostly not); etc.
Complicating the issue is the fact that Tungsten Connector issues JDBC connect
requests to the driver on its own, without any "prompting" from a client
connected to it. It uses a database name of "mysql" in the cases we saw (Which
sounds awfully dangerous), but then doesn't do anything with them. Maybe it's
trying to test whether the database is alive, or something, we don't know, and
haven't investigated any further.
That's why we resorted to the URL_PATTERN change in net.sf.hajdbc.sql.Driver.
We appreciate your offer to make this change a part of the code base. We
suggest when you do so that you make it a system property with a default value
of what it is now for backward compatibility. We, and others that need to do
so, could then change it as needed via the system property. (We only need this
when ha-jdbc is used with Tungsten Connector; our main applications don't
require it.)
Also, we suggest that you log, probably at the debug level, the value of the
URL being processed and the value of the cluster id that is extracted from it.
We had to do this in order to figure out the right pattern to use. :-)
Thanks in advance for your attention to this,
Jim Renkel
I am considering using Tungsten Connector to bridge a PHP application to a HA-
JDBC data source. I already have a HA-JDBC data source for my Java web
application in Tomcat, but want to add another data source to give HA DB
support to a bundled PHP application.
The marriage of Tungsten Connector to HA-JDBC is not unique. PostgreSQL wiki
mentions it in this Point Point.
Page 4 of the PPT shows this stack:
Native Clients (PHP, Perl, C, Ruby, etc)
Tungsten Connector
JDBC Data Source (Sequoia or HA-JDBC)
Hi jrenkel,
What workarounds did you implement to bridge Tungsten Connector to HA-JDBC
data source?
I am stuck at the same spot as you. I run the Tungsten Connector application
in a thread of my web app (not as a Windows service via the bundled "Tanuki
Software Java Service Wrapper"). This is the code snippet if anyone is
interested:
Thread tungstenConnectorThread = new Thread(new Runnable() {
public void run() {
System.setProperty("cluster.home", "C:/tungsten-enterprise-1.3.4/tungsten-
connector");
System.setProperty("myosotis.logger", "C:/tungsten-enterprise-1.3.4/tungsten-
connector/conf/log4j.properties");
String args = {"C:/tungsten-enterprise-1.3.4/tungsten-
connector/conf/connector.properties"};
org.continuent.myosotis.App.main(args);
}
}, "TungstenConnector");
tungstenConnectorThread.start();
Note: Tungsten Connector has dependencies on Tungsten Replication's tungsten-
commons.jar, and on Tungsten Enterprise's .manifest.json. Not explicitly using
other Tungsten Enterprise components if I can help it.
My web app successfully starts Tungsten Connector and listens on port 9999 for
native MySQL commands. I connect to port 9999 using MySQL command line client
with command "mysql -umyuser -pmypwd myschema -P9999 -hlocalhost".
I see these log messages in my web app's console:
DEBUG - Start serving client: /127.0.0.1:55904
DEBUG - Connection string: jdbc:mysql://127.0.0.1/myschema?autoReconnect=true
I assume this is what you mean by the hostname problem. Tungsten Connector is
inserting 127.0.0.1 into the URL based on these settings from
connector.properties.
connector.properties: jdbc.driver.base.url = jdbc:mysql://
connector.properties: jdbc.driver = com.mysql.jdbc.Driver
connector.properties: jdbc.driver.options =
?autoReconnect=true&alwaysSendSetIsolation=false
I obviously want to switch to HA-JDBC, but that means getting rid of the
hostname or ignoring it:
connector.properties: jdbc.driver.base.url = jdbc:ha-jdbc:myschema_mysql
connector.properties: jdbc.driver = net.sf.hajdbc.sql.Driver
connector.properties: jdbc.driver.options =
My question to you jrenkel: Were you successful making Tungsten Connector
proxy to your HA-JDBC data source, instead of your MySQL database.
a) Is it possible to tweak connector.properties in Tungsten Connector to
exclude the hostname?
b) Did you hack Tungsten Connector code to exclude the hostname?
c) Did you hack HA-JDBC code to ignore a hostname?
d) Any other ideas?
Your help would be much appreciated. Thanks.
Never mind. Got it. I modified the URL_PATTERN in
net/sf/hajdbc/sql/Driver.java as you mentioned.
// Extract HA-JDBC cluster id "mycluster" and truncate "/mysql" from Tungsten
Connector url="jdbc:ha-jdbc:mycluster/mysql"
private static final Pattern URL_PATTERN = Pattern.compile("jdbc:ha-
jdbc:(+).*"); //$NON-NLS-1$
I got this working. Here are some details in case anyone else needs them:
Tungsten Connector's connector.properties
server.protocol = mysql
server.version = 5.5.15-myosotis-0.8
server.port = 9999
jdbc.driver = net.sf.hajdbc.sql.Driver
jdbc.driver.base.url = jdbc:ha-jdbc:
jdbc.driver.options =
user.map.filename = C:/tungsten-enterprise-1.3.4/tungsten-
connector/conf/user.map
forcedDBforUnspecConnections = none
Tungsten Connector's user.map
myuser mypasswd mycluster
@direct COMMENT OUT DIRECT CONNECTION, HA-JDBC TAKES CARE OF IT
Embedded Tungsten Connector's Myosotis in Tomcat web app
Thread tungstenConnectorThread = new Thread(new Runnable() {
public void run() {
System.setProperty("cluster.home", "C:/tungsten-enterprise-1.3.4/tungsten-
connector");
System.setProperty("myosotis.logger", "C:/tungsten-enterprise-1.3.4/tungsten-
connector/conf/log4j.properties");
String args = {"C:/tungsten-enterprise-1.3.4/tungsten-
connector/conf/connector.properties"};
org.continuent.myosotis.App.main(args);
}
}, "TungstenConnector");
tungstenConnectorThread.start();
HA-JDBC net/sf/hajdbc/sql/Driver.java customization
private static final Pattern URL_PATTERN = Pattern.compile("jdbc:ha-
jdbc:(+).*"); //$NON-NLS-1$
MySQL CLI native client connecting to Myosotis port 9999
mysql -umyuser -pmypasswd myschema -P9999 -hlocalhost
DEBUG - Start serving client: /127.0.0.1:63394
DEBUG - Connection string: jdbc:ha-jdbc:mycluster/myschema
DEBUG - User: myuser Password: mypasswd
NFO (AbstractDatabaseCluster.java:1033) Initializing HA-JDBC 2.0.15 from
file:/C:/tomcat/webapps/cspm/WEB-INF/classes/ha-jdbc-mycluster.xml
INFO (LocalStateManager.java:82) Using local initial cluster state .
INFO - Using local initial cluster state .
mysql> show databases like 'myschema%';
+-----------------+
| Database (myschema%) |
+-----------------+
| myschema1 |
| myschema2 |
+-----------------+
mysql> use myschema;
Database changed
Note: MySQL CLI client connects to myschema, but "show databases" says it does
not exist. The myschema is a virtual schema backed by two real schemas calls
myschema1 and myschema2.
MySQL CLI native client connecting to real port 3306
mysql -umyuser -pmypasswd -P3306 -hlocalhost
mysql> show databases like 'myschema%';
+-----------------+
| Database (myschema%) |
+-----------------+
| myschema1 |
| myschema2 |
+-----------------+
mysql> use myschema;
ERROR 1049 (42000): Unknown database 'myschema'
Note: MySQL native client connected to 3306 has no concept of virtual, only
real schemas myschema1 and myschema2. You only see the virtual "myschema" if
you connect to Myosotis port 9999 which proxies to HA-JDBC mycluster (where
myschema is truncated from url in HA-JDBC's Driver.java).