MySQL connection problem - OK for root.

[petetodd]

I have a servlet that calls a session bean to connect and return a value from a MySQL database.

Works OK if I use "root" in the mysql-ds.xml file:

<local-tx-datasource>
<jndi-name>mySQLDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/test</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>

</local-tx-datasource>



If I use any other user then fails:

<local-tx-datasource>
<jndi-name>mySQLDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/test</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>petetodd</user-name>
xxxx
</local-tx-datasource>



Produces following error:
Message from Session Bean is: Could not create connection; - nested throwable: (java.sql.SQLException: Invalid authorization specification: Access denied for user: '[e-mail]' (Using password: YES)); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Invalid authorization specification: Access denied for user: '[e-mail]' (Using password: YES)))

I've tested this from mysql prompt and the password for user is correct. I've also tried setting the user password to null "" which allows me to logon to mysql from the prompt without a password but that produces following error:

Message from Session Bean is: Could not create connection; - nested throwable: (java.sql.SQLException: General error, message from server: "Access denied for user: '@localhost.localdomain' to database 'gibems'"); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: General error, message from server: "Access denied for user: '@localhost.localdomain' to database 'gibems'"))


I'm new to using MySQL with JBoss so any advice much appreciated.

-Pete

[rdb55]

MySQL (IMHO) has a wierd set of default permissions. Everything appears to work ok when you are root, however, you can not do anything as a normal user. The problem is discussed in their documentation.

My suggestion is to remove the wildcard entries from the users, db, and host tables. You will then need to insert some entries for the user name you want. I can not remember the particulars off the top of my head, but because of the way mysql resolves permissions, that default wildcard resolves first and thus fails your logon attempts.

If you have already explored this avenue, my apologies for wasting your time. Hope you find a good solution.

Ciao
RDB

[dumber168]

Hi Petetodd,

In order for your account (petetodd, xxxx) to be validated by
MySql, make sure that it has been granted access properly.

As root using mysql client, execute the following:

GRANT SELECT,INSERT,DELETE,UPDATE on test.* to petetodd@'' IDENTIFIED BY 'xxxx';

GRANT SELECT,INSERT,DELETE,UPDATE on test.* to petetodd@'%' IDENTIFIED BY 'xxxx';

GRANT SELECT,INSERT,DELETE,UPDATE on test.* to petetodd@localhost IDENTIFIED BY 'xxxx';

The '' after the ATSUM sign in the first line uses two single quotes
without space in between to signify that user "petetodd" of
unknown host may access the DB, provided that the password
is correct.

Hope this helps.