i have been in and out of a ton of forums looking for the answer to this question, I really hope someone can help. I am getting this error wile trying to finish chapter 15 ex. :

SQLException: java.sql.SQLException: Invalid authorization specification: Access denied for user: 'username@localhost' (Using password: YES)

if i log into mysql and use the username and password i have in the CreateTable file it works fine, but for some reason when i run the file it doesnt. Please help,

I don't know too much about MySQL, but you might be getting confused between Authentication and authorization.

Authentication is the process of validating that you are who you say you are. i.e. username & password.

Authorization checks the database access control list to make sure that you have permission to perform the data activity that you are attempting.

You might want to try using the administrator logon and password, which you can verify by using MySQL_GUI.

I appologise if you already know this.

Regards

Martyn

Whoops! Like the fool that I am I went and originally posted this reply as a new topic. Sorry guys! Anyway, I've been thing about your problem and here a few other thoings to try:

When you log into the database form the console the hostname is 127.0.0.1 and when Tomcat logs into the database it uses the host localhost. Make sure that you have both hostnames listed in the user-table (I know people have solved this solved this by setting the host-name to '%'). You could also try to use the URL jdbc:mysql://127.0.0.1/etc.

GRANT ALL ON dbname.* TO "username"@hostname IDENTIFIED BY "password"

Confirm that you have the correct password in the connection string. Also, check the spelling and the case. Use something like this:

getConnection( "jdbc:mysql://localhost/mydbname", "myusername", "mypassword" );


Good luck....

Martyn

Thanks, let me try some of your suggestions, and let you know ...

Still not working...

here is my code:

import java.sql.*;

public class CreateTable {
    public static void main(String args[]) {
    Connection con = null;
    try {

        Class.forName("org.gjt.mm.mysql.Driver").newInstan ce();
        System.out.println("JDBC driver Loaded Dude");
        con = DriverManager.getConnection(
        "jdbc:mysql://localhost/wrox?user=root&password=richard");
        System.out.println("Database connection established");

        Statement stmt = con.createStatement();
        String upd = "Create Table Author (Author_ID INTEGER NOT NULL PRIMARY KEY,Author_Name CHAR(50));";
        stmt.executeUpdate(upd);
        System.out.println("Table - Author Created");

        upd = "Create Table Category (Category_ID INTEGER NOT NULL PRIMARY KEY,Category_Description CHAR(50));";

        stmt.executeUpdate(upd);
        System.out.println("Table - Category Created");

        upd = "Create Table Contribution (Contribution_ID INTEGER NOT NULL PRIMARY KEY,Contribution_Description CHAR(50));";

        stmt.executeUpdate(upd);
        System.out.println("Table - Contribution Created");

    } catch (ClassNotFoundException cnfe) {
        System.out.println("ClassNotFoundException: Could not locate driver");

    } catch (SQLException cnfe) {
        System.out.println("SQLException: "+cnfe);

    } catch (Exception e) {
        System.out.println("An unknown error occured while connecting to the database");

    } finally {
        try {
            if ( con != null)
            con.close();
    } catch(SQLException sqle) {
        System.out.println("Unable to close database connection.");
    }
       }
      }
   }



I tried to replace localhost with 127.0.0.1 but that did not work either. this is the error that i get:

JDBC driver Loaded
SQLException: java.sql.SQLException: Invalid authorization specification: Access
 denied for user: 'username@localhost' (Using password: YES)

I am wondering, is the username@localhost really the word "username" or are they referring to root, ??

Sorry, no offence meant but I had to smile. You need to enter the user name that you have set up in your database, or you could do as you suggested and use root, but deffinately not 'username'. The book you are using ought to have made that clear. If you are not sure have a look in the mysql database for table user.

I hope this sorts you out, but if not, I'm going away on a trip for a week but I'll gladly help on my return if you haven't sorted it, but I'm pretty confident that you will.

Cheers

Martyn

well i am using root as my username, what I meant was, in the error message is says username@localhost, not root@localhost, and i dont know why. In my code example i have user=root.

Hi,

I've just load and ran your code and it worked pefectly. The only change i made was to the connection statement, changinfg the database to one that all ready exists and to my username & password combination:

con = DriverManager.getConnection(
        "jdbc:mysql://localhost/test?user=root&password=admin");


As you can see, all I've changed is the database and the password. Have a look at your set-up as that is obviously where your problem is. I'm still bettting that it is your username/password combination, but as a last minute thought, have you created the database 'wrox' and granted permission to root to access this db?

Good luck... I'm off on my holiday now. See you in a week!

Martyn

Martyn,

I have tried creating new databases, and that does not work. I have downloaded the mysql gui and thought that might help, but still nothing. I am almost at a quit point, but I need to know what it is that is wrong. Thanks for your suggestions. My real question is why does it say 'username@localhost' (Using password: YES). I dont know where it gets these words, I could change the localhost to some ip and it still says localhost. Also, can i just set the mysql to no password, to just make this thing work??

Thanks

Rick

Rick,

Sorry for the delay in replying but I've only just got back from my holiday.

As I mentioned previously, your code runs fine on my machine, which leads me to reiterate that it must be a privilage thing. When you used mysql_gui did you check to ensure that you had suffucent privileges to access the data base? Check both localhost and \% in table users and also check the database access privileges in table db, which should all be 'Y'.

Let me know what settings you have and if this doesn't work then we can go thru it step by step, starting with creating a new user, database, table, etc.

Cheers

Martyn