Skip to content

Database encryption engine migration script fails #9979

Closed
#10033
Closed
Database encryption engine migration script fails#9979
#10033
Assignees
weizhouapache
Labels
affects:4.18affects:4.19
Milestone
4.19.2

Description

@detaras
detaras
opened on Nov 26, 2024
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

Activity

  1. boring-cyborg commented on Nov 26, 2024

    @boring-cyborg
    boring-cyborgbot
    on Nov 26, 2024 – with boring-cyborg

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

  2. weizhouapache
    self-assigned this
    on Nov 26, 2024

  3. weizhouapache commented on Nov 26, 2024

    @weizhouapache
    weizhouapache
    on Nov 26, 2024
    Member

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

  4. detaras commented on Nov 26, 2024

    @detaras
    detaras
    on Nov 26, 2024
    Author

    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";

  5. weizhouapache commented on Nov 26, 2024

    @weizhouapache
    weizhouapache
    on Nov 26, 2024
    Member

    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 ?

  6. weizhouapache
    added this to the 4.19.2 milestone on Nov 26, 2024
  7. DaanHoogland
    added
    affects:4.18
    affects:4.19
    on Nov 27, 2024
  8. DaanHoogland
    linked a pull request that will close this issuecloudstack-migrate-databases: sql AND added #10033on Dec 4, 2024

  9. weizhouapache commented on Dec 4, 2024

    @weizhouapache
    weizhouapache
    on Dec 4, 2024
    Member

    fixed by #10033

  10. weizhouapache
    closed this as completedon Dec 4, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

affects:4.18affects:4.19

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions