Trouble Connecting Jira Core 8.1 to MySQL 5.7.26 with JDBC driver 5.1.47

[Paul Alain]

Hello,

Spent two days trying to move Jira 8.1 server install to MySQL 5.7.26 but keep getting 'This MySQL instance is not properly configured...' error message.

Followed all the threads I could find online about configuring SSL but the error message persists. Now I want to connect without SSL which I tried several times initially.

When I use the config.sh tool I get the following message:

Wed May 15 15:24:14 BST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Connection successful!

The error message after restarting the server and going through the setup wizard:

This MySQL instance is not properly configured. Please follow the documentation for MySQL 5.7+ setup.

Have been following this document to get the server connected to MySQL 5.7.26 (amongst others describing how to use SSL but not interested in that right now as both DB and server reside in close proximity)

https://confluence.atlassian.com/adminjiraserver/connecting-jira-applications-to-mysql-5-7-966063305.html

The document above asks us to use config tool to configure the dbconf.xml file then restart the server but when I do that I get sent back to the configuration wizard, basically going around in a loop! When I reach the wizard the My Own Database (recommended for production environments) option is chosen but keep getting the same error message (must have tried more that 30 times)

Here is the JDBC connection string as amended in dbconf.xml

<url>jdbc:mysql://address=(protocol=tcp)(host=mysql-server)(port=3306)/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB&useSSL=false&verifyServerCertificate=false</url>

Have inserted the following in mysql.conf.d/mysqld.cnf

# Jira
default-storage-engine=INNODB
character_set_server=utf8mb4
innodb_default_row_format=DYNAMIC
innodb_large_prefix=ON
innodb_file_format=Barracuda
innodb_log_file_size=2G

After Trying SSL connection and getting the exact same error I'm no longer pursuing the SSL route for the reasons mentioned above and the sake of creating a simpler environment.

We're using mysql-connector-java-5.1.47

PS: Apologies for the double post, first one was posted in wrong category

[Paul Alain]

Have spent the entire morning following this integration document (linked to in OP) on a brand new fresh install of both Jira 8.1 and MySQL 5.7.26.

Still getting the exact same problem. Anyone from Jira support cares to chime in?

Here's dbconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<jira-database-config>  <name>defaultDS</name>  <delegator-name>default</delegator-name>  <database-type>mysql57</database-type>  <schema-name>PUBLIC</schema-name>  <jdbc-datasource>    <url>jdbc:mysql://address=(protocol=tcp)(host=localhost)(port=3306)/jiradb?useUnicode=true&characterEncoding=utf8mb4&sessionVariables=default_storage_engine=InnoDB</url>    <driver-class>com.mysql.jdbc.Driver</driver-class>    <username>jirauser</username>    <password>f1b0i2o2</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>    <min-evictable-idle-time-millis>4000</min-evictable-idle-time-millis>    <time-between-eviction-runs-millis>5000</time-between-eviction-runs-millis>    <pool-max-idle>20</pool-max-idle>    <pool-remove-abandoned>true</pool-remove-abandoned>    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>    <pool-test-on-borrow>false</pool-test-on-borrow>    <pool-test-while-idle>true</pool-test-while-idle>    <validation-query-timeout>3</validation-query-timeout>  </jdbc-datasource></jira-database-config>

And here's /etc/mysql/mysql.conf.d/mysqld.cnf

## The MySQL database server configuration file.## You can copy this to one of:# - "/etc/mysql/my.cnf" to set global options,# - "~/.my.cnf" to set user-specific options.# # One can use all long options that the program supports.# Run program with --help to get a list of available options and with# --print-defaults to see which it would actually understand and use.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients# It has been reported that passwords should be enclosed with ticks/quotes# escpecially if they contain "#" chars...# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
# Here is entries for some specific programs# The following values assume you have at least 32M ram
[mysqld_safe]socket = /var/run/mysqld/mysqld.socknice = 0
[mysqld]## * Basic Settings#user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplc-messages-dir = /usr/share/mysqlskip-external-locking## Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.#########################################################################bind-address = 127.0.0.1########################################################################## * Fine Tuning#key_buffer_size = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size       = 8# This replaces the startup script and checks MyISAM tables if needed# the first time they are touchedmyisam-recover-options  = BACKUP#max_connections        = 100#table_cache            = 64#thread_concurrency     = 10## * Query Cache Configuration#query_cache_limit = 1Mquery_cache_size        = 16M## * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtime!#general_log_file        = /var/log/mysql/mysql.log#general_log             = 1## Error log - should be very few entries.#log_error = /var/log/mysql/error.log## Here you can see queries with especially long duration#log_slow_queries = /var/log/mysql/mysql-slow.log#long_query_time = 2#log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about#       other settings you may need to change.#server-id = 1#log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size   = 100M#binlog_do_db = include_database_name#binlog_ignore_db = include_database_name## * InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!## * Security Features## Read the manual, too, if you want chroot!# chroot = /var/lib/mysql/## For generating SSL certificates I recommend the OpenSSL GUI "tinyca".## ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem# ssl-key=/etc/mysql/server-key.pem
########################################################################default-storage-engine=INNODBcharacter_set_server=utf8mb4innodb_default_row_format=DYNAMICinnodb_large_prefix=ONinnodb_file_format=Barracudainnodb_log_file_size=2G########################################################################

The install is carried out on Ubuntu 16.04 headless server.

[Dave Theodore [Coyote Creek Consulting]]

The config file should be /etc/mysql/my.cnf according to the comments. Is this file symlinked there? You should check the start script to make sure you are modifying the file that MySQL is using.

[Paul Alain]

/etc/msql/my.cnf includes two configuration files as follows

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

/etc/mysql/mysql.conf.d/mysqld.cnf is the files with [mysqld] tag and that's were I add the following entries as described in the migration document.

default-storage-engine=INNODB
character_set_server=utf8mb4
innodb_default_row_format=DYNAMIC
innodb_large_prefix=ON
innodb_file_format=Barracuda
innodb_log_file_size=2G 

[Paul Alain]

@Dave Theodore [Coyote Creek Consulting] Where can I see a detailed view of what config files is MySQL loading on startup? I looked at /var/log/mysql/error.log but there isn't much info in there.

[Dave Theodore [Coyote Creek Consulting]]

It would be in the start script.  You can see what switches are being called, and one of them should be the config file location.  Seems like you are looking at a valid config, though.

I think you are going to have to read over the docs on encryption on MySQL 5.7. It seems that the Ubuntu config is forcing encryption and you will need to adjust the configuration to allow for plain text connections. 

[Paul Alain]

Looking at the config files it seems that all SSL configurations are commented out:

# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Also I have other app servers connected the this MySQL instance and they are not complaining. 

[muhammadlilg]

Did you ever resolve this issue @Paul Alain  ?

I'm getting a similar issue. Any update on how/if you fixed this issue would be a great help.

Thanks

[muhammadlilg]

I managed to fixed this by adding the documentation changes to the following file:

/etc/mysql/mysql.conf.d/mysqld.cnf

-- These changes were added after : 

[mysqld]

<-- here -->

Then stop and start mysql. 

[Dave Theodore [Coyote Creek Consulting]]

I'd recommend following the Atlassian doc on connecting to MySQL 5.7 exactly. It goes over what my.cnf settings are required and how to properly create the database instance.

It sounds like your MySQL instance is configured with SSL stuff in the my.cnf. Check the MySQL docs for the directives. If you don't use SSL on this database, I would suggest commenting out the lines that enable SSL.