cannot achieve connectivity with MySQL db on remot

问题:

I'm developing a Java/Spring/Hibernate/CXF/MySQL SOAP webservice and corresponding web Spring MVC client on my laptop. The plan is to eventually move the two resulting war-files over to my remote server hosted at mybiz.com .

Both laptop and server have an instance of MySQL version 5.x.x Both laptop and server have a root@localhost user (duh) The server also has three other users: zzdb_admin@% zzdb_admin@localhost [e-mail] all with the same password remotepw and which have all had assorted privileges granted and flushed.

Both instances of MySQL have a database named zzdb. Both instances of MySQL have @@session.old_passwords, @@global.old_passwords and @@global.secure_auth set to 0; In all cases hashes of passwords are 41 characters wide.

While logged in to the remote server directly I can manually log in to all accounts on both machines

mysql --user=root --password=remoterootpw
mysql --user=zzdb_admin --password=remotepw
mysql --host=localhost --user=zzdb_admin --password=remotepw

On the laptop I can log in to the local mysql with

mysql --user=root --password=localrootpw
mysql --host=mybiz.com --user=zzdb_admin --password=remotepw

So all users and passwords are correct. And their hashes are all 41 characters. Important: note this last proves that connection can be made with the instance on the remote machine.

The webservice' pom has version 5.1.8 of mysql-connector-java.

Now it gets weird. With these lines in the webservice' properties file:

hibernate.connection.url=jdbc:mysql://localhost:3306/zzdb
hibernate.connection.username=root
hibernate.connection.password=localrootpw

the webapp can connect to the local db instance and all is peachy. But changing only these three lines to

hibernate.connection.url=jdbc:mysql://mybiz.com:3306/zzdb
hibernate.connection.username=zzdb_admin
hibernate.connection.password=remotepw

throws the dreaded "Access denied for user 'zzdb_admin'@'localhost' " error

This has got me pulling out what few hairs I have left. Doesn't look like I'm missing anything and everything is spelled correctly. Anybody have an idea of what's going on?

TIA,

Still-learning Steve

Addendum: trying a different, simpler approach WORKED!

Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://mybiz.com/zzdb?user=zzdb_admin&password=remoteapw";
Connection conn = DriverManager.getConnection(connectionUrl);

Now how about that? Only change is the method of connecting. Crazy

回答1:

I am not sure but mysql has a user permissions option. So your zzdb_admin user may not have a remote connection access. You can view permissions from the mysql workbench

回答2:

MySQL remote link have a bind address. You should comment it. You can refer this blog.

回答3:

Solved the problem, and oh what a subtle bugger it was!

The applicationContext.xml at the base of the web service uses an Atomikos bean for a data source

<bean id="dataSourceServerA" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
  <property name="uniqueResourceName"    value="XADBMS_A" />
  <property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
  <property name="xaProperties">  <!-- properties will be different for different data sources ie MySQL/HSDB/DB2 etc -->
    <props>
        <prop key="uri">$dbServerA{hibernate.connection.uri}</prop>
        <prop key="user">$dbServerA{hibernate.connection.username}</prop>
        <prop key="password">$dbServerA{hibernate.connection.password}</prop>
    </props>
</property>
<property name="poolSize"><value>20</value></property>
<property name="testQuery" value="SELECT 1" />

which in turn gets used in the EntityManagerFactor bean. This configuration works great as long as I'm pointing to the local instance of MySQL. Changing the configuration to point to the remote instance kept failing with references to zzdb_admin@localhost no matter what I did.

So I created a second, much simpler connectivity tester using only

Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://mybiz.com/zzdb?user=zzdb_admin&password=removepw";
theApp.conn = DriverManager.getConnection(connectionUrl);

and EUREKA! It worked to make a remote connection. So that got me thinking the problem had to lie with the Atomikos bean configuration.

Turns out there's one crucial xaProperty I wasn't setting - serverName, which when not explictly set defaults to - you guessed it - localhost. Atomikos isn't "smart" enough to infer the hostname out of the uri passed to it.

So merely switching to

<bean id="dataSourceServerA" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
  <property name="uniqueResourceName"    value="XADBMS_A" />
  <property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
  <property name="xaProperties">  <!-- properties will be different for different data sources ie MySQL/HSDB/DB2 etc -->
    <props>
        <prop key="serverName">mybiz.com</prop>
        <prop key="port">3306</prop>
        <prop key="databaseName">zzdb</prop>
        <prop key="user">$dbServerA{hibernate.connection.username}</prop>
        <prop key="password">$dbServerA{hibernate.connection.password}</prop>
    </props>
</property>
<property name="poolSize"><value>20</value></property>
<property name="testQuery" value="SELECT 1" />

did the trick. So the problem was one of configuration. Fiendishly subtle indeed!

Thanks to all who replied!

CASE CLOSED!

Still-learning Steve