[Bug] An unexpected error occurred after a database command was cancelled. (AutoIncrement on non-PK fields)

[fvet]

Please include the following with each issue:

1. Describe the bug When publishing a new version of an app in VS Code, an unclear error is shown, making it hard to find the code causing the problem

The request for path /v2.0/SandboxUS/dev/apps?tenant=xxx&SchemaUpdateMode=forcesync&DependencyPublishingOption=strict failed with code UnprocessableEntity. Reason: An unexpected error occurred after a database command was cancelled.

After lots of trial and error, seems like a copy-paste error, where a new non-PK field got introduced with the AutoIncrement property enabled.

2. To Reproduce Steps to reproduce the behavior:

• Create an AL project with following table

table 71176626 "My Table 71176626"
{
    Caption = 'My Table 50110';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; Value1; integer)
        {
            Caption = 'integer';
            DataClassification = ToBeClassified;
            AutoIncrement = true;
        }
        field(2; Value2; integer)
        {
            Caption = 'Value2';
            DataClassification = ToBeClassified;
        }
    }
    keys
    {
        key(PK; Value1)
        {
            Clustered = true;
        }
    }
}

• Publish the app.
• Set the AutoIncrement property on field 2

table 71176626 "My Table 71176626"
{
    Caption = 'My Table 50110';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; Value1; integer)
        {
            Caption = 'integer';
            DataClassification = ToBeClassified;
            AutoIncrement = true;
        }
        field(2; Value2; integer)
        {
            Caption = 'Value2';
            DataClassification = ToBeClassified;
            AutoIncrement = true;
        }
    }
    keys
    {
        key(PK; Value1)
        {
            Clustered = true;
        }
    }
}

• Publish the app
• An error is shown

The request for path /v2.0/SandboxUS/dev/apps?tenant=xxx&SchemaUpdateMode=forcesync&DependencyPublishingOption=strict failed with code UnprocessableEntity. Reason: An unexpected error occurred after a database command was cancelled.

Note: Because the developers need to copy and paste the code snippet, including a code snippet as a media file (i.e. .gif) is not sufficient.

3. Expected behavior The AL compiler should at least warn on setting AutoIncrement on non-PK fields, either the error message while publishing should provide more context on the failing object / code to support better trouble shooting.

4. Actual behavior

An unclear error is thrown, giving the developer no clue on what object is causing the error.

5. Versions:

• AL Language: v9.0.615906
• Business Central: 20.1

[dzzzb]

Good find!

The problem is likely not the presence of AutoIncrement in a non-PK field, but that fact that there can be only one. That is documented, so the compiler should enforce it properly, via Problems, rather than mysteriously failing during build.

A table can only contain one auto-increment field.

[rsgilbert]

For me this error happened when I changed the name of a field in a table. When I restored the old field name, everything was okay again

[fvet]

FYI, we recently encountered the same when we renumbered a field in a table (PTE app).

[RonaldVeenman]

Removing a field which is also a key gives the same issue. Workaround is to remove the key first, and the field afterwards. Versie: NL Business Central 22.0 (platform 22.0.54512.0 + toepassing 14.0.35916.0)

[rsgilbert]

On another note, can you guys try out this command line tool I made for renaming AL files. Its called alrenamer

You need to have nodejs installed and then followed below steps from your project (any folder with .al files in it).

1. Install alrenamer: npm install --global @passioncloud/alrenamer

2. Enable running scripts: set-executionpolicy -scope currentuser remotesigned

3. Rename the AL files alrenamer

I will really appreciate the support, thanks!

[NAVFreak]

I never found out why I got this. Couldn't find anything of above. The last working app was ~30 days ago. If I imported the last one from the pipeline (~20 days ago) and it didn't work. So I had to check out the commits and build the app manually for each day up to where it failed. But it never failed. Somehow it worked on the way. Took me the entire afternoon to fix 😒