Stuck on initial DB setup for Confluence "Access denied for user"

[joespr]

Stuck on initial DB setup for Confluence.

Using Ubuntu 11.04.

Installed Confluence without error via command line. Entered license Key Ok. Running first setup I get "Setup Database Failed".

1. Added jdbc directory to CLASSPATH

2. I am able to log in to MySQL using command line with the username/password, so I know credentials are Ok.

3. Changed user's Host from 'localhost' to 'localhost%'

4. User has the following grants:

GRANT USAGE ON *.* TO 'confluenceuser'@'localhost%' ...

"The following error(s) occurred:

• Configuring the database failed. Couldn't create the database schema.
• com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'confluenceuser'@'localhost' to database 'confluence' "

So, not sure how to proceed. Is there a log I can look at to check what is holding this up?

Thanks,

Joe

[joespr · Accepted solution]

I should have been more clear.

I tried with user's Host as 'localhost' as well as 'localhost%', same result. The latter was tried because of the info on the following page:

https://confluence.atlassian.com/display/CONFKB/Known+Issues+for+MySQL

see the section "Access Denied".

In any event, I am stuck. My boss is getting anxious, and so am I.

Is there some log I can look as to is occurring when I attempt the db setup?

[joespr]

No getting very far.

1. from /etc/profile CLASSPATH for all users is set as:

CLASSPATH=/usr/bin/java:/opt/atlassian/confluence/confluence/WEB-INF/lib/:

2. Root is not running confluence daemon. User is 'confluence2'

3. I notice at startup there is this line:

Using CLASSPATH: /opt/atlassian/confluence/bin/bootstrap.jar

should I modify /opt/atlassian/confluence/bin/setenv.sh so CLASSPATH includes /opt/atlassian/confluence/confluence/WEB-INF/lib/ ?

4. In mysql, fixed confluenceuser 's Host back to 'localhost', same result.

Here is output from /var/atlassian/application-data/confluence/logs/atlassian-confluence.log :

2013-03-19 14:39:45,151 ERROR [http-8090-3] [atlassian.config.bootstrap.Default$
assianBootstrapManager] getTestDatabaseConnection Could not successfully test y$
 database:
 -- referer: http://server:8090/setup/setupstandarddb-start.action?database=mysq
l | url: /setup/setupstandarddb.action | userName: anonymous | action: setupsta$
rddb
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for us$
'confluenceuser'@'localhost' to database 'confluence'

What to do? I am at my wits' end, more or less.

[Nic Brough -Adaptavist-]

Points 1-3 are not a problem, you don't need to change anything there. The log in point 4 is telling you that the database is not accepting whatever credentials you are providing to it.

Can you look at the full rights for confluenceuser and post them here? Simply "show grants for confluenceuser;" in the mysql database, which should give you a full list. Also, when you said you can log into the database on the command line - you do mean from the confluence server? You are running database and confluence on the same server? (Sorry, got to ask, I ran down a complete dead-end for nearly a week where someone here hadn't bothered to tell us that they weren't running the systems on the same server)

[joespr]

Thanks for jumping in to help.

mysql> show grants for confluenceuser ;
ERROR 1141 (42000): There is no such grant defined for user 'confluenceuser' on host '%'
mysql> show grants for 'confluenceuser'@'localhost' ;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for confluenceuser@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'confluenceuser'@'localhost' IDENTIFIED BY PASSWORD 'xxxxxxxxx' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(password masked for display here).

Yes, mysql db on same server as confluence install. Also we are running Jira successfully using the same mysql as well, installed on same server.

Yes, when I say I can log in to mysql via command line means: ssh to server, can log in with confluenceuser account to mysql. That was done to check that account and password are valid.

However, I am not local when using browser to connect to server at port 8090.

[Nic Brough -Adaptavist-]

Ok, that's a good start, and kind of answers the question. (It doesn't matter where the clients are - the clients talk to Confluence, Confluence talks to the database - they're a step apart from each other).

I must apologise though - I should have spotted this sooner.

"Grant usage" creates a user with a password, but with NO privileges. You'll be able to get into the database with it, but not actually do anything. I know that's counter-intuitive and it bites me almost every time I look at a mysql database.

You need to grant it more rights - try

GRANT ALL PRIVILEGES ON `confluenceuser`.* TO 'confluenceuser'@'localhost'

And then restart confluence.

[joespr]

Thanks. I found the page

https://confluence.atlassian.com/display/DOC/Database+Setup+For+MySQL

[Nic Brough -Adaptavist-]

I think point 3 might be wrong - "localhost" is shorthand for "this computer" and the "%" is a wildcard, so you should be connecting to just "localhost".