Issue with cloudstack-setup-databases

[kiranchavala]

problem

Getting an exception when I run the command

cloudstack-setup-databases cloud:password@localhost -s -u -v

versions

ACS 4.21, ubuntu 22.04

The steps to reproduce the bug

1. Install mysql server

apt-get install mysql-server

2. Set a root password

mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
mysql>FLUSH PRIVILEGES;

3. Configure InnoDB settings in mysql server’s /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]

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'

4. systemctl restart mysql

5. Log in with root password and execute the following commands

mysql -u root -p

-- Create the cloud and cloud_usage databases
CREATE DATABASE `cloud`;
CREATE DATABASE `cloud_usage`;

-- Create the cloud user
CREATE USER cloud@`localhost` identified by 'password';
CREATE USER cloud@`%` identified by 'password';

-- Grant all privileges to the cloud user on the databases
GRANT ALL ON cloud.* to cloud@`localhost`;
GRANT ALL ON cloud.* to cloud@`%`;

GRANT ALL ON cloud_usage.* to cloud@`localhost`;
GRANT ALL ON cloud_usage.* to cloud@`%`;

-- Grant process list privilege for all other databases
GRANT process ON *.* TO cloud@`localhost`;
GRANT process ON *.* TO cloud@`%`;

6. Check the settings

mysql> SHOW GRANTS FOR 'cloud'@'localhost';
+----------------------------------------------------------------+
| Grants for cloud@localhost                                     |
+----------------------------------------------------------------+
| GRANT PROCESS ON *.* TO `cloud`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `cloud`.* TO `cloud`@`localhost`       |
| GRANT ALL PRIVILEGES ON `cloud_usage`.* TO `cloud`@`localhost` |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'cloud'@'%';
+--------------------------------------------------------+
| Grants for cloud@%                                     |
+--------------------------------------------------------+
| GRANT PROCESS ON *.* TO `cloud`@`%`                    |
| GRANT ALL PRIVILEGES ON `cloud`.* TO `cloud`@`%`       |
| GRANT ALL PRIVILEGES ON `cloud_usage`.* TO `cloud`@`%` |
+--------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT user, host FROM mysql.user WHERE user='cloud';
+-------+-----------+
| user  | host      |
+-------+-----------+
| cloud | %         |
| cloud | localhost |
+-------+-----------+
2 rows in set (0.00 sec)

7. Execute the command

cloudstack-setup-databases cloud:password@localhost -s -u -v

  -s, --schema-only     Creates the db schema without having to pass root
                        credentials - Please note: The databases (cloud,
                        cloud_usage) and user (cloud) has to be configured
                        manually prior to running this script when using this
                        flag.
  --force-recreate      Force recreation of the existing DB schemas. This
                        option is disabled by default.Please note: The
                        databases (cloud, cloud_usage) and its tables data
                        will be lost and recreated.
  -u, --skip-users-auto-creation
                        Indicates whether to skip the auto-creation of users
                        in the database. Use this flag when your database
                        users are already configured and you only want to
                        populate the db.properties file.
-v, --debug           If enabled, print the commands it will run as they run

root@test-cs-installation:~# cloudstack-setup-databases cloud:password@localhost -s -u -v
Mysql user name:cloud                                                           [ OK ]
Mysql user password:******                                                      [ OK ]
Mysql server ip:localhost                                                       [ OK ]
Mysql server port:3306                                                          [ OK ]
Checking Cloud database files ...                                               [ OK ]
Checking local machine hostname ...                                             [ OK ]
Checking SELinux setup ...                                                      [ OK ]
Detected local IP address as 127.0.1.1, will use as cluster management server node IP[ OK ]
Preparing /etc/cloudstack/management/db.properties                              [ OK ]


We apologize for below error:
***************************************************************
Aborting script as the databases (cloud, cloud_usage) already exist.
Please use the --force-recreate parameter if you want to recreate the schemas.
***************************************************************
Please run:

    cloudstack-setup-databases -h

for full help

A workaround is to use the parameter --force-recreate command

root@test-cs-installation:~# cloudstack-setup-databases cloud:password@localhost -s -u -v --force-recreate
Mysql user name:cloud                                                           [ OK ]
Mysql user password:******                                                      [ OK ]
Mysql server ip:localhost                                                       [ OK ]
Mysql server port:3306                                                          [ OK ]
Checking Cloud database files ...                                               [ OK ]
Checking local machine hostname ...                                             [ OK ]
Checking SELinux setup ...                                                      [ OK ]
Detected local IP address as 127.0.1.1, will use as cluster management server node IP[ OK ]
Preparing /etc/cloudstack/management/db.properties                              [ OK ]
Applying /usr/share/cloudstack-management/setup/create-schema.sql               [ OK ]
Applying /usr/share/cloudstack-management/setup/create-schema-premium.sql       [ OK ]
Applying /usr/share/cloudstack-management/setup/server-setup.sql                [ OK ]
Applying /usr/share/cloudstack-management/setup/templates.sql                   [ OK ]
Processing encryption ...                                                       [ OK ]
Finalizing setup ...                                                            [ OK ]

CloudStack has successfully initialized database, you can check your database configuration in /etc/cloudstack/management/db.properties

What to do about it?

The command should without --force-recreate option

cloudstack-setup-databases cloud:password@localhost -s -u -v

[DaanHoogland]

@kiranchavala , have you validated this is not present in prior releases?

[kiranchavala]

@DaanHoogland

The options -u and --force-recreate were introduced only in 4.21

Since 4.21, the databases (cloud, cloud_usage) are only created if they do not exist. This behavior prevents accidental recreation of existing databases. The databases recreation can still be invoked by passing the –force-recreate flag.

https://docs.cloudstack.apache.org/en/4.21.0.0/installguide/management-server/index.html#install-the-database-on-the-management-server-node

The issue is not present with 4.20

root@cs-intall420:/home/kiran# cloudstack-setup-databases --help
Usage: cloudstack-setup-databases user:[password]@mysqlhost:[port] [--deploy-as=rootuser:[rootpassword]] [--auto=/path/to/server-setup.xml] [-e ENCRYPTIONTYPE] [-m MGMTSECRETKEY] [-k DBSECRETKEY] [-g ENCRYPTORVERSION] [--debug]

    This command sets up the CloudStack Management Server and CloudStack Usage Server database configuration (connection credentials and host information) based on the first argument.

    If the --deploy-as option is present, this command will also connect to the database using the administrative credentials specified as the value for the --deploy-as argument, construct the database environment needed to run the CloudStack Management Server, and alter the password specified for the user in the first argument.  In this case, the user name specified in --deploy-as= cannot be the same as the user name specified for the connection credentials that the CloudStack Management Server will be set up with.

    If a server-setup.xml cloud setup information file is specified with the --auto option, this command will also construct a customized database environment according to the cloud setup information in the file.

    The port and the password are optional and can be left out..  If host is omitted altogether, it will default to localhost.

    The encryptor version is optional. The options are V1 and V2. If it is not set, the default encryptor will be used.

    Examples:

    cloudstack-setup-databases cloud:secret
        sets user cloud and password 'secret' up in
        /etc/cloudstack/management/db.properties, using localhost as the
        database server

    cloudstack-setup-databases sheng:rules@192.168.1.1
        sets these credentials up in /etc/cloudstack/management/db.properties

    cloudstack-setup-databases alex:founder@1.2.3.4 --deploy-as=root:nonsense
        sets alex up as the MySQL user, then connects as the root user
        with password 'nonsense', and recreates the databases, creating
        the user alex with password 'founder' as necessary

    cloudstack-setup-databases alex:founder@1.2.3.4 --deploy-as=root:nonsense -e file -m password -k dbpassword -g V2
        In addition actions performing in above example, using 'password' as management server encryption key
        and 'dbpassword' as database encryption key, saving management server encryption key to a file as the
        encryption type specified by -e is file.
        The credentials in /etc/cloudstack/management/db.properties are encrypted by encryptor V2 (AeadBase64Encryptor).
        The db.cloud.encryptor.version is also set to V2. Sensitive values in cloudstack databases will be
        encrypted by the encryptor V2 using the database encryption key.

    cloudstack-setup-databases alena:tests@5.6.7.8 --deploy-as=root:nonsense --auto=/root/server-setup.xml
        sets alena up as the MySQL user, then connects as the root user
        with password 'nonsense' to server 5.6.7.8, then recreates the
        databases and sets up the alena user, then performs an automated
        database setup using the information in server-setup.xml


Options:
  -h, --help            show this help message and exit
  -v, --debug           If enabled, print the commands it will run as they run
  -d ROOTCREDS, --deploy-as=ROOTCREDS
                        Colon-separated user name and password of a MySQL user
                        with administrative privileges
  -s, --schema-only     Creates the db schema without having to pass root
                        credentials - Please note: The databases (cloud,
                        cloud_usage) and user (cloud) has to be configured
                        manually prior to running this script when using this
                        flag.
  -a SERVERSETUP, --auto=SERVERSETUP
                        Path to an XML file describing an automated unattended
                        cloud setup
  -e ENCRYPTIONTYPE, --encrypt-type=ENCRYPTIONTYPE
                        Encryption method used for db password encryption.
                        Valid values are file, web and env. Default is file.
  -m MGMTSECRETKEY, --managementserver-secretkey=MGMTSECRETKEY
                        Secret key used to encrypt confidential parameters in
                        db.properties. A string, default is password
  -k DBSECRETKEY, --database-secretkey=DBSECRETKEY
                        Secret key used to encrypt sensitive database values.
                        A string, default is password
  -i MSHOSTIP, --mshost=MSHOSTIP
                        Cluster management server host IP. A string, by
                        default it will try to detect a local IP
  -r REGIONID, --regionid=REGIONID
                        Region Id for the management server cluster
  -c DBCONFPATH, --db-conf-path=DBCONFPATH
                        The path to find db.properties which hold db
                        properties
  -f DBFILESPATH, --db-files-path=DBFILESPATH
                        The path to find sql files to create initial
                        database(s)
  -j ENCRYPTIONJARPATH, --encryption-jar-path=ENCRYPTIONJARPATH
                        The cloudstack jar to be used to encrypt the values in
                        db.properties
  -n ENCRYPTIONKEYFILE, --encryption-key-file=ENCRYPTIONKEYFILE
                        The name of the file in which encryption key to be
                        generated
  -g ENCRYPTORVERSION, --encryptor-version=ENCRYPTORVERSION
                        The encryptor version to be used to encrypt the values
                        in db.properties
  -b MYSQLBINPATH, --mysql-bin-path=MYSQLBINPATH
                        The mysql installed bin path

root@cs-intall420:/home/kiran# cloudstack-setup-databases cloud:password@localhost -s  -v
Mysql user name:cloud                                                           [ OK ]
Mysql user password:******                                                      [ OK ]
Mysql server ip:localhost                                                       [ OK ]
Mysql server port:3306                                                          [ OK ]
Checking Cloud database files ...                                               [ OK ]
Checking local machine hostname ...                                             [ OK ]
Checking SELinux setup ...                                                      [ OK ]
Detected local IP address as 127.0.1.1, will use as cluster management server node IP[ OK ]
Preparing /etc/cloudstack/management/db.properties                              [ OK ]
Applying /usr/share/cloudstack-management/setup/create-schema.sql               [ OK ]
Applying /usr/share/cloudstack-management/setup/create-schema-premium.sql       [ OK ]
Applying /usr/share/cloudstack-management/setup/server-setup.sql                [ OK ]
Applying /usr/share/cloudstack-management/setup/templates.sql                   [ OK ]
Processing encryption ...                                                       [ OK ]
Finalizing setup ...                                                            [ OK ]

CloudStack has successfully initialized database, you can check your database configuration in /etc/cloudstack/management/db.properties

[sureshanaparti]

@kiranchavala --force-recreate option is required in the cmd as databases cloud & cloud_usage exists.