Button Try autofix

[Q&A] Using the Platform
1

For executing an installation after updating and modifying the composition, the following message appears on the installer screen:

The query contains a syntax error
Error details:
Incorrect syntax near the keyword ‘All’.
‘their’ is not a recognized SET option.
The variable name ‘@Rows’ has already been declared. Variable names must be unique within a query batch or stored procedure.
The variable name ‘@Rows2’ has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near the keyword ‘All’.
‘their’ is not a recognized SET option.

Now, three buttons appears:

  1. The Try autofix button appears
  2. Changed update scripts
  3. Abort installation

I am curious, what does the ‘Try Autofix’ button do, and in what situations should I use it?
In what situation should you use the ‘Changed Update Script’ button?

2
  1. Try autofix will process the updatescript and try to make adjustments based on tmpfld/tmptab additions. (it can add and remove this addition as needed). Otherwise it will give a bit more information about the script where it might not be working.
    In this case I don’t expect it to be able to fix this script, but maybe able to give more specified feedback.
  2. Changed update scripts means “reload from subversion”. It will unset your svn number and run the checkout again. This is used when you would have made manual changes to the script outside of deployment.
3

Hi Stephan, you found a very specific case, which is interesting nevertheless.

Your update-script contained Data Conversion to batch-wise convert data following a component change.

The data conversion was setting a value for a new Cached Function. In this case, it was expectedtransactionline.payment_method.

This is a Cached Function that non-normalized data for performance reasons. The Payment Method is already set at the Expected Transaction; this cached function stores the value at each line.

In small database tables, the Cached Fields Checker fixes these functions automatically after installations.

But when you perform an update on a large (production) application, the Cached Fields fixer is not fase enough for very high volumes. In these cases, it makes sense to pre-fill the Cached fields in the update-script.

Because some customers have millions of records here, a simple UPDATE statement would also be too long. Therefore, a specific custom piece of script was created to process it in batched of 10000 rows.

To keep track of this, variables @Rows where declared in the update-script, to support batch-wise processing.

However, in your large update, you happened to have to tables with such a procedure, so the name @Rows was double.

The solution was to rename the @Rows and @BatchSize SQL variables to something really unique (here: @Rows_PI12345 with the PI-number).

Pro tip / best practice: When writing data conversion in these cases, make sure the helper variables are unique.

1 Like