failed to connect to mysql database

[jingru h barr]

I try to switch jira from h2 -> mysql

mysql version is 5.7.19

jira version is -> 7.11

my database setup without issue with correct grant...

CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on JIRADB.* TO jirasa@iiaabqzwebap01d.ia.doi.net IDENTIFIED BY 'passwordishere';
flush privileges;

I use the mysql-java connector mysql-connector-java-8.0.11.jar

my jira server is on iiaabqzwebap01d.ia.doi.net

I keep getting this following error to configure the db...

2018-07-25 09:32:27,644 JIRA-Bootstrap ERROR [c.a.config.bootstrap.DefaultAtlassianBootstrapManager] Could not successfully test your database:
java.sql.SQLSyntaxErrorException: Access denied for user 'jirasa'@'iiaabqzwebap01d.ia.doi.net' to database 'jiradb'

this is the show grant result from mysql

SHOW GRANTS FOR jirasa@iiaabqzwebap01d.ia.doi.net;

| GRANT USAGE ON *.* TO 'jirasa'@'iiaabqzwebap01d.ia.doi.net' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `JIRADB`.* TO 'jirasa'@'iiaabqzwebap01d.ia.doi.net' |

so...  please assist further

[Nic Brough -Adaptavist-]

You say

Access denied for user 'jirasa'@'iiaabqzwebap01d.ia.doi.net' to database 'jiradb'

That can be quite a complex problem to resolve, depending on the database in use

Rough example - I like PostGres because it mostly works.  But I utterly despise the access control (and all the other settings) in the pg_ config files, it's poorly explained, a weak model and in 99% of cases only works by luck or guesswork, leaving massive security holes.  It's even worse than Jira's default permissions which need to be taken out and shot to put them out of our misery

But, I digress.  The problem is you have a login that does not work from the Jira server to the database server.  The permission grant you give in your question looks right.  You can log in from (somewhere else?) on the command line.  But not from the Jira server.  The Jira server is recording an authentication failure.  So, you need to look at the database server to see why it is refusing access to Jira.

[jingru h barr]

I don't agree your suggestion.  The database accepts the connection from JIRA application to mySQL db via mariaDB client.  The real issue is "java.sql.SQLSyntaxErrorException"...  Do you see "SQLSyntax".....

here is the URL connection defined in dbconfig.xml....

<jdbc-datasource>
<url>jdbc:mysql://iiahermysql002d.ia.doi.net:3306/jiradb?useSSL=false</url>
<driver-class>com.mysql.cj.jdbc.Driver</driver-class>
<username>jirasa</username>
<password>itispasswordinfohere</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<validation-query>select 1</validation-query>

so....  if this definition is not right..  please kindly advise.

[Nic Brough -Adaptavist-]

It's irrelevant if you agree or not, the error message in the question is clear - Access denied for user 'jirasa'@'iiaabqzwebap01d.ia.doi.net' to database 'jiradb'

Either you're using the wrong credentials, or the user has not been given the right permission.

[jingru h barr]

How do I turn on debug on JIRA?

[Nic Brough -Adaptavist-]

You can do it from inside the application at Admin -> Profiling and logging, or you can fiddle with the log4j.properties file in the installation.

However, extra logging in  Jira isn't going to help you with this one a lot.  The error message you've got already tells you what you need to fix.

[jingru h barr]

thanks..  All I could tell you is that the username / password are correct.  I use copy / paste from dbconfig.xml to MariaDB client to connect to Mysql server.  It connects.  So, access denied is not due to invalid credentials.  You also see the permission I grant, it is straight from the documentation.  

I even create a user without password to test, it still failed.  So, that is all I could tell you.  It seems to me that your company does not offer the support I am looking for.  The analyst won't recreate the issue I described...   Giving up that mySQL configuration and rollback to H2 since it is $10.00 purchase.

[Nic Brough -Adaptavist-]

You are correct, it might not be the credentials, it might be some other access control.  Whatever it is though, it is a problem with either the credentials or the server setup.   Or, possibly, if you are using the wrong driver for Jira (if you have not installed your own, then you have a compatible one, as part of the installation)

Using H2 is the wrong thing to do.  It does not scale, and it WILL fail on you catastrophically at some point (more frequently the longer you use it)

Most people here are end-users, not Atlassian.  It's a community for the users, not support.  We try to help because we get help in return

[informatikus]

No text

[jingru h barr]

no...  I am not using MariaDB..  MariaDB is only installed on the JIRA application as mysql client to test that jirasa account to ensure I could connect from JIRA server to MySQL DB.  that is only to test connection only to show you my DB account was set without issue and connection is OK between JIRA application server and MySQL DB server.  

The following screenshot is straight out from MySQL DB server.

[root@iiahermysql002d ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.02 sec)

[jingru h barr]

Hello.....  Are you working on my issue?

Again, I am not using MariaDB..  I am using MySQL.  I need to resolve this issue soon..

[informatikus]

Hey @jingru h barr, here didn't connect to the database here, only to the host.

What happens, connecting to the database from your JIRA-server (still using the mysql / mariadb - client)?

[root@iiaabqzwebap01d ~]# mysql -u jirasa -h iiahermysql002d.ia.doi.net -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select version();
+-----------+
| version() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.00 sec)

 //edit: And please keep in mind: We're not the official atlassian support but mostly atlassian customers who just fell in love with the atlassian ecosystem and try to support each other.  We all got our daily jobs, too and don't follow any SLAs here.

In case you're in hurry and want to reach out to the atlassian support-staff - take this way: https://support.atlassian.com/contact/#/

[jingru h barr]

no problem..  Will contact support.  

to be clear, I install mySQL client (aka mariadb client) just for testing and it is very expensive one time only installation......  The error happen before I still that client and still exist after I remove that mariadb client

[jingru h barr]

Hello....

https://support.atlassian.com/contact/#/  takes me to the same route which I opened this support request.

Do you have a real support which owns ticket and rather than just open-community support??????

Jingru

[informatikus]

@jingru h barrcheck your mysql-logs.

From what i see here the issue might be related to your my.cnf configuration:

Comment out following lines.

#bind-address           = 127.0.0.1
#skip-networking

If you do not find skip-networking line, add it and comment out it.

[jingru h barr]

try...  still does not work

[informatikus]

Have you checked your MySQL logs? 

Would be helpful to see them for the timeframe you try to connect from jira 

[jingru h barr]

yes...  nothing shown....

[informatikus]

Have you tried connecting from a MySQL client on the server your jira instance is running on?

[jingru h barr]

Yes  I did...  it works

[jingru h barr]

hello...  in that jira server, I install mysql client and test connection...  it works..so..see info below.  I believe it is the code error since the error from jira is java.sql.SQLSyntaxErrorException  --  it is syntax error

[root@iiaabqzwebap01d ~]# mysql -u jirasa -h iiahermysql002d.ia.doi.net -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select version();
+-----------+
| version() |
+-----------+
| 5.7.19 |
+-----------+
1 row in set (0.00 sec)

[informatikus]

Looks like you're using mariadb (which is not supported by Atlassian).

Are you stuck to MariaDB or able to switch to e.g. PostgresDB (would be best)?