JDBC Hibernate - Mysql Connection Error

Question

I have a local mysql server on my Ubuntu 11.10 desktop. The hostname = localhost; username = root; password = root; database name = CBS. I am really confused because when I access mysql using terminal, mysql administrator, and mysql query browser I use those authentication I mentioned above and everything is OK. My problem is when I configure my jdbc.properties in my Java App I'm getting this error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [28000]; error code [1045]; Access denied for user 'root '@'localhost' (using password: YES); nested exception is java.sql.SQLException: Access denied for user 'root '@'localhost' (using password: YES)

My configuration file, jdbc.properties:

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/CBS
jdbc.username=root  
jdbc.password=root

By the way, the reason I will be using local server because our main server shutdown so I have to use my local mysql to continue with my project. Please help me... Thanks in advance.

Solution 1

More than likely your framework is logging into your local database as 127.0.0.1. Which will create a login problem in MySQL if you have not defined an appropriate domain scoped credential. Try this to verify:

mysql -uroot -proot
SELECT * from mysql.user WHERE user = 'root';

If there is no 'root'@'127.0.0.1' then have found the problem and to remedy it, do one of two things:

1. Define a domain scoped credential for 'root', at '127.0.0.1'.
2. Define a wildcard domain scoped credential for 'root', so you can login to your MySQL with those credentials from multiple locations.

Here's an example of the second:

mysql -uroot -proot

CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

On a side note, I would definitely recommend using something more creative for your user id and password. Especially if you have TCP sockets enabled for your server.

Solution 2

You might have to GRANT permissions to access the database.

Look at the MySQL docs for something that looks like this:

create database pmt;
create user pmt identified by 'pmt';
grant all on pmt.* to 'pmt'@'%';

"pmt" is just an example above. I happened to make the name of the database, username, and password all the same. I don't recommend that as a best practice. It's just something I did for some local development.

I personally don't like GRANTing root access to any application. I would not use root username and password in even a toy application. It doesn't take much effort to create a new user and GRANT appropriate permissions.

Comments

[jmq]

What is your hostname when using mysql adminstrator (or the other tools)? As duffymo mentioned below you may have a grant issue with that user connecting to localhost. I didn't see any mention about the hostname in the first part of your message.

[Nir Alfasi]

do you run your "java app" using root creds ? when youy app runs - check it out using "ps -ef | grep <java app name>" and see which user is running it - in case it's not "root" you have your answer right there.

[NinjaBoy]

Do I have to restart my server after executing those commands?

[NinjaBoy]

When I ran the command SELECT * from users WHERE user = 'root'; what database will I use to see if I have a 'root'@'127.0.0.1'? Im new to this.

[Perception]

Actually, the command is select * from mysql.user where user = 'root';. I updated my answer.

[NinjaBoy]

I saw a lot of random characters like '+' and '-'. However I saw this line: | localhost | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | Y | Y. Does it mean I have a root?

[Perception]

Eh, do me a favor and execute this in mysql select user, host from user, and tell me how many rows show up with user 'root', and what the host field for the rows are.

[NinjaBoy]

I have 8 rows. I saw user = root and host = localhost.

[Perception]

Well, assuming you didn't see 'root' and host = '127.0.0.1', execute the solution I showed in my answer.

[NinjaBoy]

Im sorry but actually I saw it. username = root and host = 127.0.0.1. Is 127.0.0.1 the same with localhost? Anyway I saw both.

[NinjaBoy]

let us continue this discussion in chat