MySQL upgrade

Explicit JDBC driver declaration

While upgrading, on some environments the following may be required to be added in CloudStack’s db.properties file:

# Add these to your db.properties file

db.cloud.driver=jdbc:mysql

db.usage.driver=jdbc:mysql

MySQL support updated to 8.4

As of Apache CloudStack 4.20.3, support for MySQL 8.4 has been added.

Existing deployments upgraded to version 4.20.3 can still continue using MySQL 8.0 without any changes.

If you are running MySQL 8.0 and would like to upgrade to MySQL 8.4, you may follow the standard MySQL upgrade process to migrate safely to version 8.4, and then update the authentication method for the root and CloudStack (cloud) users with caching_sha2_password plugin using the below steps as the mysql_native_password plugin is deprecated as of MySQL 8.0.34, and disabled by default in MySQL 8.4. For more details, refer to MySQL documentation here: https://dev.mysql.com/doc/refman/8.4/en/caching-sha2-pluggable-authentication.html

Stop MySQL server if already running
```
sudo systemctl stop mysqld
```

Start MySQL server in safe mode without auth

sudo mysqld --skip-grant-tables --skip-networking &

Login to MySQL without password
```
mysql -u root
```

Reset passwords for root and CloudStack (cloud) users.

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'ROOT_PASSWORD';
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'ROOT_PASSWORD';
ALTER USER 'cloud'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'CLOUD_PASSWORD';
ALTER USER 'cloud'@'%' IDENTIFIED WITH caching_sha2_password BY 'CLOUD_PASSWORD';
FLUSH PRIVILEGES;

Note: Please ensure that the password used for the cloud database user matches the value configured in /etc/cloudstack/management/db.properties. If the password in db.properties is encrypted, you can retrieve it using the below command.

java -classpath /usr/share/cloudstack-common/lib/cloudstack-utils.jar \
com.cloud.utils.crypt.EncryptionCLI -d \
-i "$(grep -oP 'db.cloud.password=ENC\(\K[^\)]+(?=\))' /etc/cloudstack/management/db.properties)" \
-p "$(cat /etc/cloudstack/management/key)"

Remove deprecated authentication plugin ‘mysql_native_password’ from the MySQL configuration. Either comment or remove the below line from /etc/my.cnf
```
default_authentication_plugin=mysql_native_password
```
Restart MySQL server
```
killall mysqld
systemctl start mysqld
```

MySQL 8.0+ sql mode change

MySQL mode (sql_mode) has changed in CloudStack db.properties to “STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION”.

This gets automatically applies to the MySQL session used by CloudStack management server.

If the admin uses MySQL directly and wants to query tables it is advised to change the sql_mode in the corresponding session or globally.

Eg. mysql> set global sql_mode=”STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

“> ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION”;

Query OK, 0 rows affected (0.00 sec)

mysql> set sql_mode=”STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,: “> ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION”;

Query OK, 0 rows affected (0.00 sec)

MySQL upgrade problems

With certain MySQL versions (see below), issues have been seen with “cloud.nics” table’s column type (which was not updated properly during CloudStack upgrades, due to MySQL limitations), which eventually may lead to exception seen in the management server logs, causing Users to not be able to start any VM.

The following SQL statement needs to be manually executed in order to fix such issue:

ALTER TABLE nics MODIFY COLUMN update_time timestamp DEFAULT CURRENT_TIMESTAMP;

The issue is known to affect the following MySQL server versions:

5.7.34 or later

8+