CROWD Setup Wizard error connecting to MYSQL database

[pottind]

I installed atlassian-crowd-2.12.0 on Redhat Linux 7.3. I followed the steps in Atlassian Documentation for Installing Crowd. Completed Step 3 Connecting Crowd to a Database, MYSQL. 

Downloaded JDBC driver for MYSQL file: mysql-connector-java-5.1.42-bin.jar  and copied to the folder:  /opt/atlassian/crowd/atlassian-crowd-2.12.0/apache-tomcat/lib

In Set up Wizard, Database configuration I have these values:

Driver Class name: com.mysql.jdbc.Driver

JDBC URL:  jdbc:mysql://IP-Address/crowd?autoReconnect=true&characterEncoding=utf8&useUnicode=true

User Name: root

When I click Continue it gives error:
Could not connect to the database: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

Looking at catalina.out it is trying to connect to database with user root@hostname-of-Crowd-web-server.

Caused by: java.sql.SQLException: Access denied for user 'root'@'hostname-of-Crowd-web-server' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2194)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2045)
... 128 more
2017-06-15 07:43:32,840 http-bio-8095-exec-7 ERROR [console.action.setup.Database] Unable to connect to database: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

Please help resolve ! 

Thank You

NP

[Alex You]

I tried simliar issue on 4.1.2

mysql-connector-java-8.0.21.jar  to the following folder solve the problem /opt/atlassian/jcrd/crowd-webapp/WEB-INF/lib

The manual is not right

2. Copy the MySQL Driver to your Application Server

1. Download the MySQL Connector/J JDBC driver driver.
2. Add the MySQL JDBC driver jar (mysql-connector-java-5.x.x-bin.jar) to the following directory:
   
   • Crowd 2.0.2 or later: {CROWD_INSTALL}/apache-tomcat/lib/.
   • Crowd 2.0.1 or earlier: {CROWD_INSTALL}/apache-tomcat/common/lib/.

[Alex You]

Also

Should use utf8mb4 instead of utf8

[Sergey]

Not Work in Crowd 3.0.1!
I found correct solution!!!!
The symbol _ (underscore) cannot be used in the name of a database!!!

[Sergey]

del

[Sergey]

del

[Sergey]

del

[pottind]

Error when connecting to MYSQL database during CROWD Setup was resolved as follows:

CROWD SetupWizard adds @crowd-server-hostname at the end of database user name, example: crowduser@<crowd-server-hostname>

In MYSQL server I created a new user called crowd-user@crowd-server-hostname and granted privilege to the user:

mysql> CREATE USER 'crowd-user'@'<crowd-server-hostname.com>' IDENTIFIED BY 'crowd-user' ;
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT ALL PRIVILEGES ON crowd.* TO 'crowd-user'@'<crowd-server-hostname.com>'  IDENTIFIED BY '<passwd>';
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql>

In Crowd Setup Database connection was successful. I completed the remaining stepd in Setup. When I query crowd database it shows tables:

mysql> SHOW TABLES;
+----------------------------+
| Tables_in_crowd |
+----------------------------+
| cwd_app_dir_group_mapping |
| cwd_app_dir_mapping |
| cwd_app_dir_operation |
| cwd_application |
| cwd_application_address |
| cwd_application_alias |
| cwd_application_attribute |
| cwd_audit_log_changeset |
| cwd_audit_log_entry |
| cwd_cluster_heartbeat |
| cwd_cluster_job |
| cwd_cluster_lock |
| cwd_cluster_message |
| cwd_cluster_message_id |
| cwd_directory |
| cwd_directory_attribute |
| cwd_directory_operation |
| cwd_expirable_user_token |
| cwd_granted_perm |
| cwd_group |
| cwd_group_attribute |
| cwd_membership |
| cwd_property |
| cwd_synchronisation_status |
| cwd_token |
| cwd_tombstone |
| cwd_user |
| cwd_user_attribute |
| cwd_user_credential_record |
| cwd_webhook |
| hibernate_unique_key |
+----------------------------+
31 rows in set (0.01 sec)

mysql>

[AnnWorley]

Everything sounds good as far as the driver being in place. Without the driver, there is an error before the authentication attempt.

Since your database URL is using the IP of the MySQL server I am taking that as an indication that MySQL is on a seperate host. My theory is that the "root" account that has access to the database is the one on that host. Please try root@<mysqlserver_hostname> on the database setup page, replacing <mysqlserver_hostname> with the name of your MySQL server.

I look forward to hearing the result.

Thanks,

Ann

[pottind]

Database server is separate from Crowd server. I had tried database set up with User name: root@MySQL-Server-hostname and got the same error:

2017-06-15 09:23:58,176 http-bio-8095-exec-9 ERROR [console.action.setup.Database] Unable to connect to database: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
com.atlassian.config.bootstrap.BootstrapException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

I looked in ctalina.out file located at: /opt/atlassian/crowd/atlassian-crowd-2.12.0/apache-tomcat/logs  contained these:

Caused by: java.sql.SQLException: Access denied for user 'root'@'Crowd-server-host (using password: NO)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)

I tried with User name: root@MYSQL-server-hostname and catalina.out contained error: 

Caused by: java.sql.SQLException: Access denied for user 'root'@'MYSQL-server-host'@'Crowd-server-host' (using password: NO)

It leads me to believe User name always get added with @Crowd-server-host.

I found jdbc.properties file at:

/opt/atlassian/crowd/atlassian-crowd-2.12.0/crowd-openidserver-webapp/WEB-INF/classes/j

Edited the last line:

hibernate.transaction.factory_class=org.hibernate.transaction.JDBCTransactionFactory

added this at end of line: [root classes]

Saved jdbc.properties file, stopped and started Crowd.  Then when I access crowd setup it went past Database configuration page. 

I able to complete the remaining steps in Crowd Setup. In MYSQL new database I created crowd is empty, no tables. Does it mean it skipped DB configuration ? Do I need to manually create tables or DB objects ? Please help !

[AnnWorley]

I am sorry to report that if there are no tables in the database, the setup failed.

I tested with a MySQL database server on another host and am getting the same behavior:

Setup wizard appending local hostname to MySQL database user

Please stand by while we try to figure out a workaround.

[AnnWorley]

One of my colleagues pointed this out, from the MySQL user setup section of the doc, Database Setup For MySQL:

" If Confluence is not running on the same server as your MySQL database server, replace 'localhost' with the hostname or IP address of the Confluence server:

GRANT ALL PRIVILEGES ON confluence.* TO 'confluenceuser'@'localhost' IDENTIFIED BY 'confluencepass';

I tested this and it did work, please try it and let us know how it goes. Just enter the username without the host in the setup form.

[AnnWorley]

I just noticed the Crowd setup for MySQL doesn't mention this crucial detail - I will follow up on the documentation.

[pottind]

Hi Ann

In MYSQL database server I executed commands below to drop crowduser, created it again and granted privileges:

mysql> DROP USER 'crowduser'@'localhost';
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE USER 'crowduser'@'MYSQL-database-server-hostname' IDENTIFIED BY 'crowd-user' ;
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT ALL PRIVILEGES ON crowd.* TO 'crowduser'@'MYSQL-database-server-hostname';
Query OK, 0 rows affected (0.05 sec)

mysql>

I tried to run Crowd Setup Wizard by accessing URL:

http://Crowd-server-hostname:8095/crowd

I get login prompt for Crowd Console. Please let me know how to run Crowd database set up again.  Do I need to delete the existing Crowd installation, then unzip and untar Crowd then run Setup again ?  Please advice. 

Thank You

NP