MySQL syntax error when upgrading 4.1 -> 4.2, occuring in CheckoutBundle

[DRoppelt]

Hi guys,

I hope this is okay to submit a bug directly? I have not found a similar open/resolved bug yet.

I am trying to upgrade 4.1 to 4.2, using a mysql database (community edition)

Summary
Upgrading 4.1 -> 4.2 with mysql

Steps to reproduce
Have an existing 4.1 installation Upgrade it to 4.2

Actual Result

+ sudo --preserve-env=LD_PRELOAD -u www-data php bin/console oro:platform:update --env=prod --force --symlink --timeout=7200 --skip-assets

Check system requirements

+---------+--------------------------------------------------------------------------------------------------------+

| Check | Optional recommendations |

+---------+--------------------------------------------------------------------------------------------------------+

| WARNING | To get the latest internationalization data upgrade the ICU system package and the intl PHP extension. |

| WARNING | Disable Phar extension to reduce the risk of PHP unserialization vulnerability. |

| WARNING | Library `jpegoptim` should be installed |

| WARNING | Library `pngquant` should be installed |

+---------+--------------------------------------------------------------------------------------------------------+

The application meets all mandatory requirements

Process migrations...

> Oro\Bundle\EntityExtendBundle\Migration\LoadEntityConfigStateMigration

> Oro\Bundle\CheckoutBundle\Migrations\Schema\v1_12\RemoveAlternativeCheckoutWorkflow

ERROR: An exception occurred while executing 'SELECT e.id FROM oro_checkout e INNER JOIN oro_workflow_item wi ON (CAST(wi.entity_id as integer) = e.id) AND (wi.entity_class = ?) WHERE wi.workflow_name = ?' with params ["Oro\\Bundle\\CheckoutBundle\\Entity\\Checkout", "b2b_flow_alternative_checkout"]:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'integer) = e.id) AND (wi.entity_class = 'Oro\\Bundle\\CheckoutBundle\\Entity\\Ch' at line 1

> Oro\Bridge\ContactUs\Migrations\Schema\v1_1\OroContactUsBridgeBundle - skipped

> Oro\Bundle\ShoppingListBundle\Migrations\Schema\v1_9\UpdateCustomerVisitorLineItemsOwner - skipped

> Oro\Bundle\CaseBundle\Migrations\Schema\v1_11\UpdateObjectClassFieldLength - skipped

> Oro\Bundle\PromotionBundle\Migrations\Schema\v1_6\AddCouponCodeUppercaseField - skipped

> OroCRM\Bundle\ZendeskBundle\Migrations\Schema\v1_4\UpdateObjectClassFieldLength - skipped

> Oro\Bundle\FedexShippingBundle\Migrations\Schema\v1_1\AddIgnoreDimensionsField - skipped

> Oro\Bundle\CookieConsentBundle\Migrations\Schema\OroCookieConsentBundleInstaller - skipped

> Oro\Bundle\MigrationBundle\Migration\UpdateBundleVersionMigration

> Oro\Bundle\CheckoutBundle\Migrations\Schema\RemoveWorkflowFieldsMigration - skipped

> Oro\Bundle\EntityExtendBundle\Migration\RefreshExtendCacheMigration - skipped

> Oro\Bundle\EntityConfigBundle\Migration\UpdateEntityConfigMigration - skipped

> Oro\Bundle\EntitySerializedFieldsBundle\Migration\SerializedDataMigration - skipped

> Oro\Bundle\EntityExtendBundle\Migration\UpdateExtendConfigMigration - skipped

> Oro\Bundle\ActivityContactBundle\Migration\ActivityContactMigration - skipped

> Oro\Bundle\ActivityListBundle\Migration\ActivityListMigration - skipped

> Oro\Bundle\EntityExtendBundle\Migration\UpdateExtendIndicesMigration - skipped

> Oro\Bundle\EntityConfigBundle\Migration\WarmUpEntityConfigCacheMigration - skipped

> Oro\Bundle\ScopeBundle\Migration\Schema\UpdateScopeRowHashColumn - skipped

In MigrationExecutor.php line 116:

Failed migrations: Oro\Bundle\CheckoutBundle\Migrations\Schema\v1_12\Remove

AlternativeCheckoutWorkflow.

oro:migration:load [--force] [--dry-run] [--show-queries] [--bundles [BUNDLES]] [--exclude [EXCLUDE]] [--timeout [TIMEOUT]] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--disabled-listeners DISABLED-LISTENERS] [--current-user CURRENT-USER] [--current-organization CURRENT-ORGANIZATION] [--] <command>

Expected Result
php bin/console oro:platform:update goes through with an uprade

Details about your environment

• OroCommerce version: 4.2.1 (community edition)
• PHP version: 7.4.15
• Database MySQL, version 8.0.23-1debian10

Additional information
I believe that this is the causing code: https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/WorkflowBundle/Migration/RemoveWorkflowAwareEntitiesQuery.php#L60 (pinned to commit: https://github.com/oroinc/platform/blob/1ef3de1172608b0d5689cc7f5e6c16e0e661901f/src/Oro/Bundle/WorkflowBundle/Migration/RemoveWorkflowAwareEntitiesQuery.php#L60)

When I use the failing query (and substitute ? with random values) the query fails within HeidiSql as well:

SELECT e.id FROM oro_checkout e INNER JOIN oro_workflow_item wi ON (CAST(wi.entity_id as integer) = e.id) AND (wi.entity_class = 2) WHERE wi.workflow_name = 2

But works as following: SELECT e.id FROM oro_checkout e INNER JOIN oro_workflow_item wi ON (CAST(wi.entity_id as SIGNED) = e.id) AND (wi.entity_class = 2) WHERE wi.workflow_name = 2

[anyt]

to unblock the migration you can change it to 'CAST(wi.entity_id as unsigned integer)' here https://github.com/oroinc/platform/blob/4.2/src/Oro/Bundle/WorkflowBundle/Migration/RemoveWorkflowAwareEntitiesQuery.php#L60

[DRoppelt]

Thank you, I did not expect an answer on the weekend!

That, similar to changing to SIGNED, solved the issue and let me proceed with migration.

Aside from that blocker, migration went through smoothly from 4.1 to 4.2.

I assume that piece of code is postgres specific?

[anyt]

Not sure is it a direct Postgres query or the team expected doctrine to transform the query automatically. Anyway, it's a bug that we didn't discover because, on CI, we test an application upgrade with installed https://github.com/oroinc/commerce-demo-checkouts extension. As a result, the migration never runs. The team will check how to fix it on Monday.

[anyt]

Internal ticket id #BB-20466

[yaroslavbr]

I faced with exactly same error while updating to 4.2.5. My luck that I am not the first who had such problem. @anyt workaround helped

[XaBe20]

Hi, same issue on our project with Postgres. (4.2.10 EE) Do you have a solution of this issue ? please

Oro\Bundle\CheckoutBundle\Migrations\Schema\v1_12\RemoveAlternativeCheckoutWorkflow ERROR: An exception occurred while executing 'SELECT e.id FROM oro_checkout e INNER JOIN oro_workflow_item wi ON (CAST(wi.entity_id as unsigned integer) = e.id) AND (wi.entity_class = ?) WHERE wi.workflow_name = ?' with params ["Oro\Bundle\CheckoutBundle\Entity\Checkout", "b2b_flow_alternative_checkout"]: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "integer" LINE 1: ...orkflow_item wi ON (CAST(wi.entity_id as unsigned integer) =...

[anyt]

If you are using EE version, please report the issue to the customer support portal. As I see the issue should be already fixed in 4.2.10.