db: add support for MySQL 8 by yadvr · Pull Request #4051 · apache/cloudstack · GitHub

Splits commands to create user and grant access on database, the old
statement is no longer supported by MySQL 8.x
NO_AUTO_CREATE_USER is no longer supported by MySQL 8.x so remove
that from db.properties conn parameters

For mysql-server 8.x setup the following changes were added/tested to
make it work with CloudStack in /etc/mysql/mysql.conf.d/mysqld.cnf and
then restart the mysql-server process:

server_id = 1
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION"
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
max_connections=1000
log-bin=mysql-bin
binlog-format = 'ROW'

default-authentication-plugin=mysql_native_password

Notice the last line above, this is to reset the old password-based
authentication used by MySQL 5.x.

Developers can set empty password as follows:

> sudo mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY '';

mysql8-deploydb

Tested on Ubuntu 20.04 for a local Primate setup.

- Splits commands to create user and grant access on database, the old statement is no longer supported by MySQL 8.x - `NO_AUTO_CREATE_USER` is no longer supported by MySQL 8.x so remove that from db.properties conn parameters For mysql-server 8.x setup the following changes were added/tested to make it work with CloudStack in /etc/mysql/mysql.conf.d/mysqld.cnf and then restart the mysql-server process: server_id = 1 sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION" innodb_rollback_on_timeout=1 innodb_lock_wait_timeout=600 max_connections=1000 log-bin=mysql-bin binlog-format = 'ROW' default-authentication-plugin=mysql_native_password Notice the last line above, this is to reset the old password based authentication used by MySQL 5.x. Developers can set empty password as follows: > sudo mysql -u root ALTER USER 'root'@'localhost' IDENTIFIED BY ''; Signed-off-by: Rohit Yadav <rohit.yadav@shapeblue.com>

Smoke tests or some test cases that should be validated:

MySQL 5.7 (or MariaDB 10.x compatible with 5.7) + CloudStack against this PR
MySQL 8.x + CloudStack against this PR with (a) single mgmt/usage server and (b) multiple mgmt servers and usage servers
Travis/developers to test mvn deploydb on their MySQL 5.7 env
Travis/developers to test mvn deploydb on their MySQL 8.x env (Rohit validated on Ubuntu 20.04 against mvn)

Other Notes:

Installation docs may need to be updated.
Optional: Upgrade from MySQL 5.7 to 8.x; against CloudStack

Signed-off-by: Rohit Yadav <rohit.yadav@shapeblue.com>

@blueorangutan package

@rhtyd a Jenkins job has been kicked to build packages. I'll keep you posted as I make progress.

Packaging result: ✔centos7 ✔debian. JID-1204

@blueorangutan test

@rhtyd a Trillian-Jenkins test job (centos7 mgmt + kvm-centos7) has been kicked to run smoke tests

two NO_ENGINE_SUBSTITUTION

Good catch I'll fix that

Signed-off-by: Rohit Yadav <rohit.yadav@shapeblue.com>

@blueorangutan test

@rhtyd a Trillian-Jenkins test job (centos7 mgmt + kvm-centos7) has been kicked to run smoke tests

Trillian test result (tid-1495)
Environment: kvm-centos7 (x2), Advanced Networking with Mgmt server 7
Total time taken: 34545 seconds
Marvin logs: https://github.com/blueorangutan/acs-prs/releases/download/trillian/pr4051-t1495-kvm-centos7.zip
Smoke tests completed. 83 look OK, 0 have error(s)
Only failed tests results shown below:

Test	Result	Time (s)	Test File

Smoke tests or some test cases that should be validated:

* [ ]  MySQL 5.7 + CloudStack against this PR

* [ ]  MySQL 8.x + CloudStack against this PR with (a) single mgmt/usage server and (b) multiple mgmt servers and usage servers

You are trying to keep this compatible with mysql 5 and 8, @rhtyd ? Does it not make more sense to actively seek support mariadb ?
look good otherwise.

@DaanHoogland yes we should support both versions until 5.x reaches EOL.
MariaDB is largely compatible with MySQL 5.x at least per their docs.
We need to check if trillian default test on CentOS7 uses mariadb.
From an applications point of view, the mysql-java library and wire protocol stays the same as well as support for (ansi) SQL.

cc @shwstppr @davidjumani - while deploying database, and a zone against mysql 8.0 (on Ubuntu 20.04 on raspberrypi4) I found an issue with the nics table where update_time was being set as null which it didn't like (the default column was not null), I could alter/modify the default param; but this needs checking against mysql8/CentOS8.

Closing in favour of b895cf9

yadvr added component:database type:new-feature labels Apr 29, 2020

yadvr added this to the 4.15.0.0 milestone Apr 29, 2020

fix sql_mode in default db.properties

94e7cc7

Signed-off-by: Rohit Yadav <rohit.yadav@shapeblue.com>

apache deleted a comment from blueorangutan Apr 29, 2020

weizhouapache reviewed Apr 30, 2020

View reviewed changes

yadvr marked this pull request as draft April 30, 2020 09:58

fix dupe

b72ec35

Signed-off-by: Rohit Yadav <rohit.yadav@shapeblue.com>

yadvr requested review from DaanHoogland, PaulAngus, andrijapanicsb and borisstoyanov April 30, 2020 10:05

yadvr marked this pull request as ready for review May 2, 2020 21:53

yadvr requested a review from weizhouapache May 2, 2020 21:53

weizhouapache mentioned this pull request May 12, 2020

[4.15] Support of next versions #4081

Closed

yadvr closed this May 27, 2020

               db.cloud.poolPreparedStatements=false
-              db.cloud.url.params=prepStmtCacheSize=517&cachePrepStmts=true&prepStmtCacheSqlLimit=4096&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
+              db.cloud.url.params=prepStmtCacheSize=517&cachePrepStmts=true&prepStmtCacheSqlLimit=4096&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION'

db: add support for MySQL 8 by yadvr · Pull Request #4051 · apache/cloudstack · GitHub

Repository navigation

db: add support for MySQL 8 - #4051

db: add support for MySQL 8#4051yadvr wants to merge 3 commits intoapache:masterapache/cloudstack:masterfrom shapeblue:mysql8-deploydbshapeblue/cloudstack:mysql8-deploydbCopy head branch name to clipboard

Conversation

yadvr commented Apr 29, 2020

Uh oh!

yadvr commented Apr 29, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

yadvr commented Apr 29, 2020

Uh oh!

blueorangutan commented Apr 29, 2020

Uh oh!

blueorangutan commented Apr 29, 2020

Uh oh!

yadvr commented Apr 29, 2020

Uh oh!

blueorangutan commented Apr 29, 2020

Uh oh!

weizhouapache Apr 30, 2020

Choose a reason for hiding this comment

Uh oh!

yadvr Apr 30, 2020

Choose a reason for hiding this comment

Uh oh!

yadvr commented May 1, 2020

Uh oh!

blueorangutan commented May 1, 2020

Uh oh!

blueorangutan commented May 2, 2020

Uh oh!

DaanHoogland commented May 2, 2020

Uh oh!

yadvr commented May 2, 2020

Uh oh!

yadvr commented May 9, 2020

Uh oh!

yadvr commented May 27, 2020

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

db: add support for MySQL 8#4051
yadvr wants to merge 3 commits into
apache:masterfrom
shapeblue:mysql8-deploydb

yadvr commented Apr 29, 2020 •
edited
Loading