Database encryption engine migration script fails

[detaras]

ISSUE TYPE

• Bug Report

COMPONENT NAME

Database encryption engine migration script

CLOUDSTACK VERSION

4.18.2.3

CONFIGURATION

Database with legacy cryptographic cipher (MD5/DES)

OS / ENVIRONMENT

Centos 7

SUMMARY

When migrating from the old database encryption scheme (MD5/DES 56-bit) to the new one (AES-GCM 256-bit) using the supplied script (/usr/bin/cloudstack-migrate-databases) from any of the managers; the migration process starts, but suddenly, when working on on the 'user_vm_deploy_as_is_details' table, we get an error and the process fails.

STEPS TO REPRODUCE

# systemctl stop cloudstack-management cloudstack-usage
# /usr/bin/cloudstack-migrate-databases -d oldDBKey -m oldMSKey -e newDBKey -n newMSKey -v V2
...
...
Data Migration failed. Reverting db.properties

EXPECTED RESULTS

The script should end without error. The database content is migrated using the new cipher and the configuration files are updated as needed.

ACTUAL RESULTS

The database and configuration files remains untouched, the migration script fails with the following error:

Unable to update user_vm_deploy_as_is_details values due to: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vm_instance.id = 20850 AND template_deploy_as_is_details.name = 'property-timezo' at line 1
Error during data migration
com.cloud.utils.exception.CloudRuntimeException: Unable to update user_vm_deploy_as_is_details values
        at com.cloud.utils.crypt.EncryptionSecretKeyChanger.throwCloudRuntimeException(EncryptionSecretKeyChanger.java:794)
        at com.cloud.utils.crypt.EncryptionSecretKeyChanger.migrateTemplateDeployAsIsDetails(EncryptionSecretKeyChanger.java:663)
        at com.cloud.utils.crypt.EncryptionSecretKeyChanger.migrateData(EncryptionSecretKeyChanger.java:485)
        at com.cloud.utils.crypt.EncryptionSecretKeyChanger.migratePropertiesAndDatabase(EncryptionSecretKeyChanger.java:343)
        at com.cloud.utils.crypt.EncryptionSecretKeyChanger.main(EncryptionSecretKeyChanger.java:142)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vm_instance.id = 20850 AND template_deploy_as_is_details.name = 'property-timezo' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
        at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
        at com.cloud.utils.crypt.EncryptionSecretKeyChanger.migrateTemplateDeployAsIsDetails(EncryptionSecretKeyChanger.java:649)
        ... 3 more
Data Migration failed. Reverting db.properties

[boring-cyborg]

Thanks for opening your first issue here! Be sure to follow the issue template!

[weizhouapache]

@detaras
thanks for reporting the issue, I will have a look

[detaras]

I have found a workaround for this issue.
I took a look at the code of the EncryptionSecretKeyChanger class (located at framework/db/src/main/java/com/cloud/utils/crypt/EncryptionSecretKeyChanger.java) and found an error in the query created at lines 656-659:

        String sqlTemplateDeployAsIsDetails = "SELECT template_deploy_as_is_details.value " +
                "FROM template_deploy_as_is_details JOIN vm_instance " +
                "WHERE template_deploy_as_is_details.template_id = vm_instance.vm_template_id " +
                "vm_instance.id = %s AND template_deploy_as_is_details.name = '%s' LIMIT 1";

If we run the created query in MySQL CLI we get an error and that's because the last line shoud have a connector at the beginning (I think an 'AND' is needed there). After adding that connector and building ACS again, the migration script ended successfully.

The modified code I used is this:

        String sqlTemplateDeployAsIsDetails = "SELECT template_deploy_as_is_details.value " +
                "FROM template_deploy_as_is_details JOIN vm_instance " +
                "WHERE template_deploy_as_is_details.template_id = vm_instance.vm_template_id " +
                "AND vm_instance.id = %s AND template_deploy_as_is_details.name = '%s' LIMIT 1";

[weizhouapache]

I have found a workaround for this issue. I took a look at the code of the EncryptionSecretKeyChanger class (located at framework/db/src/main/java/com/cloud/utils/crypt/EncryptionSecretKeyChanger.java) and found an error in the query created at lines 656-659:

        String sqlTemplateDeployAsIsDetails = "SELECT template_deploy_as_is_details.value " +
                "FROM template_deploy_as_is_details JOIN vm_instance " +
                "WHERE template_deploy_as_is_details.template_id = vm_instance.vm_template_id " +
                "vm_instance.id = %s AND template_deploy_as_is_details.name = '%s' LIMIT 1";

If we run the created query in MySQL CLI we get an error and that's because the last line shoud have a connector at the beginning (I think an 'AND' is needed there). After adding that connector and building ACS again, the migration script ended successfully.

The modified code I used is this:

        String sqlTemplateDeployAsIsDetails = "SELECT template_deploy_as_is_details.value " +
                "FROM template_deploy_as_is_details JOIN vm_instance " +
                "WHERE template_deploy_as_is_details.template_id = vm_instance.vm_template_id " +
                "AND vm_instance.id = %s AND template_deploy_as_is_details.name = '%s' LIMIT 1";

good finding @detaras
it should be my mistake.

can you create a pull request against 4.18 or 4.19 branch ?

[weizhouapache]

fixed by #10033