Best Practises for CDD using MySQL 8

Document ID : KB000100270
Last Modified Date : 19/06/2018
Show Technical Document Details
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.
CA Continuous Delivery Director 6.7
MySQL Server 8
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://' 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://' 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

            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 file and force database URL to use specific TZ  

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