Best Practises for CDD using MySQL 8

Document ID : KB000100270
Last Modified Date : 19/06/2018
Show Technical Document Details
Introduction:
During the certification process for CDD with MySQL8 the following best practises and findings were collated which we want to share with the wider CDD user community.
Environment:
CA Continuous Delivery Director 6.7
MySQL Server 8
Instructions:
1. MySQL 8 requires the latest MySQL connector to be used (MySQL 8.0.11 J connector)
If, for some reason, you tried to install CDD with MySQL 8 while using MySQL Connector 5.1.21, the following error will appear:
                Failed to connect to database. 'jdbc:mysql://127.0.0.1:3306/' for user 'root'. Unable to load authentication plugin 'caching_sha2_password
 
If you try to install CDD with MySQL 8 using MySQL Connector 5.1.46, the installation will fail (UI) and in the logs, the last line would be
                Migrating schema `cdd` to version 1.20160207175436


2. MySQL is not backward compatible regarding supported timezones
Previously supported timezones might not be supported by the new MySQL 8.0.11 connector

For example:     use of the EDT is no longer supported
When running the installer, the following error will appear
Failed to connect to database. 'jdbc:mysql://127.0.0.1:3306/' for user 'root'. The server time zone value 'EDT' is unrecognized or represents more than one time zones"

To fix this issue choose 1 of the following 3 options:


     a) [PREFERRED]      update the OS timezone

            Example
            run:                           timedatectl list-timezones | grep Asia/Jerusalem
            set tz:                       timedatectl set-timezone Asia/Jerusalem
            restart database (DB) for changes to take effect: service mysqld restart
            check DB value:       SELECT @@system_time_zone;

      b) Update the MysQL database to use specific TZ 
             On the DB, run:                      SET GLOBAL time_zone = 'Asia/Jerusalem';
             Restart database for changes to take effect
             Check database value

      c) Update settings.properties file and force database URL to use specific TZ  
     
cdd.database.url=jdbc\:mysql\://${database.host}\:${database.port}/${database.name}?useUnicode\=true&characterEncoding\=utf-8&createDatabaseIfNotExist\=${database.create}

 
 
 Note: the CDD 6.7 documentation with reference to MySQL 8 and required MySQL Connector and flags have been updated.