Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL : Access denied for user ... using password : YES

Tags:

java

mysql

Having recently reformatted my hard-drive, I cannot get my local Java/Tomcat/MySQL stack working.

This is a clean MySQL install, running on Mac OSX 10.7.3 (Lion).

The error I'm getting is when my tomcat instance starts up and tries to connect is:

SEVERE: Servlet /concorde-web threw load() exception
java.sql.SQLException: Access denied for user 'concorde'@'localhost' (using password: YES)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

Steps taken:

  • I've set concorde@localhost's password as described here and here
  • I can connect from the console using the same credentials. All of the following work:

    mysql -u concorde -h localhost -p
    mysql -u concorde -p
    mysql -u concorde -h localhost -D concorde -p
    // the app is trying to connect as user concorde to db concorde
    
  • I've ensured that the concorde user has rights - not just from localhost, but anywhere:

    GRANT ALL ON concorde.* TO 'concorde'@'%';
    FLUSH PRIVELEDGES;
    

What am I missing?

Beyond that, what steps can I take to work out what's going on here? Are there any logs I can check?

EDIT

As requested, here's the code I'm using.

However, this code worked fine before reformatting my hard-drive, so I'm doubtful that the problem lies within it.

The app is running in a spring container, and it's during Spring's startup that things are faling over.

Here are the relevant bean declarations:

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="persistenceUnitName" value="spring-jpa" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="showSql" value="true" />
            <property name="generateDdl" value="false" />
            <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect" />
        </bean>
    </property>
</bean>


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url"
        value="jdbc:mysql://${database.host}:${database.port}/${database.name}" />
    <property name="username" value="${database.username}" />
    <property name="password" value="${database.password}" />
    <property name="initialSize" value="5" />
    <property name="maxActive" value="50" />
</bean>

And, here are the defined properties:

hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
database.name=concorde
database.username=concorde
database.password=password  
database.host=localhost
database.port=3306

Additionally here is the (abridged) output of a query on the permissions:

mysql> select * from information_schema.user_privileges;

| 'concorde'@'localhost'                 | def           | USAGE                   | NO           |
| ''@'localhost'                         | def           | USAGE                   | NO           |
| ''@'Marty-Pitts-MacBook-Pro.local'     | def           | USAGE                   | NO           |
| 'concorde'@'%'                         | def           | USAGE                   | NO           |
+----------------------------------------+---------------+-------------------------+--------------+

Note - this was run by logging on with root.

I suspect there's a clue in the NO shown there for my user, but I'm not sure how to interpret that data.

mysql> select * from information_schema.SCHEMA_PRIVILEGES;
+------------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE                | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+------------------------+---------------+--------------+-------------------------+--------------+
| 'concorde'@'localhost' | def           | concorde     | SELECT                  | YES          |
| 'concorde'@'localhost' | def           | concorde     | INSERT                  | YES          |
| 'concorde'@'localhost' | def           | concorde     | UPDATE                  | YES          |
| 'concorde'@'localhost' | def           | concorde     | DELETE                  | YES          |
| 'concorde'@'localhost' | def           | concorde     | CREATE                  | YES          |
| 'concorde'@'localhost' | def           | concorde     | DROP                    | YES          |
| 'concorde'@'localhost' | def           | concorde     | REFERENCES              | YES          |
| 'concorde'@'localhost' | def           | concorde     | INDEX                   | YES          |
| 'concorde'@'localhost' | def           | concorde     | ALTER                   | YES          |
| 'concorde'@'localhost' | def           | concorde     | CREATE TEMPORARY TABLES | YES          |
| 'concorde'@'localhost' | def           | concorde     | LOCK TABLES             | YES          |
| 'concorde'@'localhost' | def           | concorde     | EXECUTE                 | YES          |
| 'concorde'@'localhost' | def           | concorde     | CREATE VIEW             | YES          |
| 'concorde'@'localhost' | def           | concorde     | SHOW VIEW               | YES          |
| 'concorde'@'localhost' | def           | concorde     | CREATE ROUTINE          | YES          |
| 'concorde'@'localhost' | def           | concorde     | ALTER ROUTINE           | YES          |
| 'concorde'@'localhost' | def           | concorde     | EVENT                   | YES          |
| 'concorde'@'localhost' | def           | concorde     | TRIGGER                 | YES          |
| 'concorde'@'%'         | def           | concorde     | SELECT                  | NO           |
| 'concorde'@'%'         | def           | concorde     | INSERT                  | NO           |
| 'concorde'@'%'         | def           | concorde     | UPDATE                  | NO           |
| 'concorde'@'%'         | def           | concorde     | DELETE                  | NO           |
| 'concorde'@'%'         | def           | concorde     | CREATE                  | NO           |
| 'concorde'@'%'         | def           | concorde     | DROP                    | NO           |
| 'concorde'@'%'         | def           | concorde     | REFERENCES              | NO           |
| 'concorde'@'%'         | def           | concorde     | INDEX                   | NO           |
| 'concorde'@'%'         | def           | concorde     | ALTER                   | NO           |
| 'concorde'@'%'         | def           | concorde     | CREATE TEMPORARY TABLES | NO           |
| 'concorde'@'%'         | def           | concorde     | LOCK TABLES             | NO           |
| 'concorde'@'%'         | def           | concorde     | EXECUTE                 | NO           |
| 'concorde'@'%'         | def           | concorde     | CREATE VIEW             | NO           |
| 'concorde'@'%'         | def           | concorde     | SHOW VIEW               | NO           |
| 'concorde'@'%'         | def           | concorde     | CREATE ROUTINE          | NO           |
| 'concorde'@'%'         | def           | concorde     | ALTER ROUTINE           | NO           |
| 'concorde'@'%'         | def           | concorde     | EVENT                   | NO           |
| 'concorde'@'%'         | def           | concorde     | TRIGGER                 | NO           |
like image 293
Marty Pitt Avatar asked Feb 16 '12 21:02

Marty Pitt


1 Answers

Since it appears that you can log in with the expected credentials from the command line, I'd be suspicious of whether your properties are being properly substituted into your 'dataSource' bean.

Try temporarily hard-coding all of the parameters for the dataSource bean. If that works, then your properties aren't being set.

like image 119
GreyBeardedGeek Avatar answered Nov 03 '22 00:11

GreyBeardedGeek