create database in mySQL doesn't seem to be working correctly

Question

I want to have Java create a database and a user for me. I am using Netbeans so that I can see what databases exist. When I create the database I can see in Netbeans -> Services -> Databases that the database exists.

The program initially checks if the database already exists, so that I can run it multiple times. What surprised me was when I ran it a second time it didn't know that the database already existed so that it wanted to recreate the database. If Netbeans sees it, why doesn't my Java program see it? (I found my error in the grant statement, so I'll fix it to be correct.)

I have routines to drop databases and users. Here too if I drop a database, it disappears inside the Netbeans -> Services -> Databases.

The situation with users is working with no problems. If I drop a user and then create the database, it will recognize that the user doesn't exist, and create him. If I drop just the database, it recognizes that the user already exists.

I will include some code snippets for the drop database and user:

    String sql = "drop user '" + user + "'@'%'";
    try {
        stm1 = con1.createStatement();
        executeStatement(stm1, sql);
        stm1.close();
        con1.close();
    } catch (Exception e) { System.out.println(e.getMessage());}

    String sql = "drop database " + dbName;
    try {
        stm1 = con1.createStatement();
        executeStatement(stm1, sql);
        stm1.close();
        con1.close();
    } catch (Exception e) { System.out.println(e.getMessage());}

As can be seen there is nothing special going on here. In the create database, I thought the problem could stem from the fact that I wasn't creating any tables at this stage. (Edited to remove the adding of a table.)

OK, at this stage the database has no tables. I need to go in a second time and now I expect to be able to connect to the database I just created with the user who has privileges on the database. The error I get is

Access denied for user 'myuser'@'%' to database 'mytst1'

I just created mytst1, and I see it under Netbeans -> Services -> Databases. It currently has no tables. Again the error was in the grant statement....

Here is the routine to create the database:

void createDB() {
    Connection con1 = connect2database(false, false);
    if( con1 != null) {
        JOptionPane.showMessageDialog(this, "The database already exists.\n"
            + "Maybe you only need to create the tables?");
        return;
    }
    con1 = connect2database(false, true);
    if( con1 == null) {
        JOptionPane.showMessageDialog(this, "Can't connect to mySQL server.\n"
            + "Is the path to the database correct, including IP (with :3306)\n"
            + "The database name itself (the last part) is ignored, but the path is used.\n"
            + "Is the root password correct?");
        return;
    }
    String tmp, user = jTextUser.getText();
    String host, pwTmp;
    String userPw = jTextPW.getText();
    String sql = "create database if not exists " + dbName;
    boolean OK1, itExists;
    Statement stm1, stm2;
    ResultSet rSet;
    try {
        stm1 = con1.createStatement();
        OK1 = executeStatement(stm1, sql);
        if( !OK1) {
            JOptionPane.showMessageDialog(this, "Create database failed.");
            return;
        }

        sql = "select host, user from mysql.user where user = '" + user + "'";
        rSet = stm1.executeQuery(sql);
        itExists = false;
        while(rSet.next()) {
            itExists = true;
            host = rSet.getString(1);
            tmp = rSet.getString(2);
        }

        if(!itExists) {
            sql = "create user '" + user + "'@'%' identified by '" + userPw + "'";
            OK1 = executeStatement(stm1, sql);
            if( !OK1) {
                JOptionPane.showMessageDialog(this, "Create user failed.");
                return;
            }
        }
        sql = "grant all privileges on " + dbName + ".* to '" + user + "'@'%' identified ";
        sql += "by '" + userPw + "' with grant option";
        executeStatement(stm1, sql);
        sql = "flush privileges";
        executeStatement(stm1, sql);
        stm1.close();
        con1.close();
    } catch (Exception e) { System.out.println(e.getMessage()); }
}

What I would expect to happen is that it would exit at the top of the routine saying the database already exists. It does exit at this point if I ask to use a database which I have been using for ages to store my data. The old database obviously has lots of tables.

My error was doing a "grant all privileges on mytst1" instead of "grant all privileges on mytst1.*", i.e. all table in mytst1.

Thanks, Ilan

score 1 · Accepted Answer · 2013-11-03 09:11:39Z

1

When you create a user or schema, the user and schema is not the same but you have to add a user privileges to operate on schema, and them are lost once you drop either of them. When you create a separate statement to grant privileges make sure that the user exists. FLUSH statement reloads privs from the mysql schema to make it effective once the user is connected. If there are not records in that schema it is less effective. Granting the user privs to mysql schema is also required if you want to have access to the database metadata, especially during a development phase.

edited Nov 3, 2013 at 9:11

answered Nov 3, 2013 at 9:06

user2173738

Sign up to request clarification or add additional context in comments.

6 Comments

Ilan Tal Over a year ago

I'm not sure I understand what you are saying. My code first creates the database and after that goes to check if the user exists. If not he is created. If he already exists, he is granted privileges on the database just created. In the connect2datbase(false, false), it tries to connect to the database which either does or does not exist. If it fails connect2database(false, true) connects to the database mysql with the root password. Is this what you are referring to with the schema? Maybe I should be connecting to something else and not mysql? I'm not aware of what else I can connect to.

Ilan Tal Over a year ago

Sorry, I did something stupid. By adding the "debugging information" and creating the table, I did it while being connected to mysql, not to the database I just created. Dumb... So I go back to my original code with no tables added, but I can't connect to my newly created database with its user. I can't figure out why not.

user2173738 Over a year ago

In the connection url you could specify the default schema.

Ilan Tal Over a year ago

By specifying my connection to mysql with the root pw, I think I am effectively specifying the default schema? Note that I edited the original question not to add the table while connected to mysql. The question is: is there any reason why I can't connect to a database which currently has no tables??

user2173738 Over a year ago

I don't see your connect2database source code, think you are. If you can't connect to the database, there are several possibilities, check and test the connection with your favorite IDE before you code it to your program's datasource.

|

Collectives™ on Stack Overflow

create database in mySQL doesn't seem to be working correctly

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related