Pages

Tuesday, August 7, 2012

Database log throws error "Error executing code: Wrong argument types for comparison."

So we have had our database log turned on since AX 3.0, and we've upgraded to AX 4.0, then AX 2009.  An ongoing issue has been with the database log throwing "Wrong argument types for comparison", and I've finally gotten around to diagnosing it, and it seems like it could be a very common issue caused by an upgrade.

Error executing code: Wrong argument types for comparison.
(C)\Data Dictionary\Tables\SysDataBaseLog\Methods\getDataAslist - line 28
(C)\Forms\SysDatabaseLog\Methods\buildContents - line 30
(C)\Forms\SysDatabaseLog\Data Sources\DBHistory\Methods\active - line 7


The actual issue is this, when we were in AX 3.0, we made a chance to a record, which was logged to Tables\SysDatabaseLog and had the ModifiedDate stored as a type of Date.  Somewhere along the AX 3.0 to AX 4.0 to AX 2009, this data wasn't upgraded properly.

When the database log compare form tries to work with it, it's expecting to work with two utcDateTime objects for comparison, but it gets a Date and fails.

You can put this code in Tables\SysDatabaseLog\getDataAslist at around line 30 and see the issue in the debugger:

if (typeOf(conpeek(tmp, 2)) == Types::Date      &&
    dictField.baseType()    == Types::UtcDateTime)
{
    breakpoint;
}

You can write a hack with conPoke(...) and DateTimeUtil::newDateTime(...) to replace the Date with a utcDateTime, but you'll run into issues in other methods.  The best thing to do is correct the data and I wrote a job to do just that.  One specific thing to note, this is just proof of concept code...for our actual corrective actions, I wrote a class and table to do batches for various reasons since we have nearly 4 million records.  As always, run at your own risk and test.  Enjoy:




static void DatabaseLogFix(Args _args)
{
    SysDataBaseLog  log;
    container       tmp;
    container       newData;
    SysDictField   dictField;
    fieldId         fieldId;
    int             i, idx;
    boolean         updateCon;
    boolean         updateData;
    Counter         counter;
    #Properties;
    ;

    //select firstonly log where log.RecId == 5638018469;
    
    ttsbegin;

    while select forupdate log
        order by CreatedDateTime, RecId
    {
        updateData = false;
        if (typeof(conpeek(log.Data, 1)) == Types::Container)   //Version 3.0 format
        {
            newData = log.Data;

            for (i=conlen(log.Data); i; i--)
            {
                updateCon = false;
                tmp = conpeek(log.Data, i);
                fieldId = conpeek(tmp, 1);
                dictField = new SysDictField(log.Table, fieldId);
                if (dictField)
                {
                    if (dictField.name() == #PropertyModifiedTime
                    || dictField.name() == #PropertyCreatedTime)
                    {
                        continue;
                    }

                    // Type mismatch for "New" value
                    if (typeOf(conPeek(tmp, 2)) != dictField.baseType())
                    {
                        // If it's a date that should be a utcDateTime, upgrade it
                        if (typeOf(conpeek(tmp, 2)) == Types::Date      &&
                            dictField.baseType()    == Types::UtcDateTime)
                        {
                            tmp = conPoke(tmp, 2, DateTimeUtil::newDateTime(conPeek(tmp, 2), 0));
                            updateCon = true;
                        }
                        // Otherwise throw an error
                        else
                        {
                            info(strfmt("[RecId %1][TableID %5][FieldId %2][2 NEW] Type mismatch (%3/%4)", log.RecId, fieldId, typeOf(conPeek(tmp, 2)), dictField.baseType(), log.table));
                        }
                    }

                    // Type mismatch for "Old" value
                    if (typeOf(conPeek(tmp, 3)) != dictField.baseType())
                    {
                        // If it's a date that should be a utcDateTime, upgrade it
                        if (typeOf(conpeek(tmp, 3)) == Types::Date      &&
                            dictField.baseType()    == Types::UtcDateTime)
                        {
                            tmp = conPoke(tmp, 3, DateTimeUtil::newDateTime(conPeek(tmp, 3), 0));
                            updateCon = true;
                        }
                        // Otherwise throw an error
                        else
                        {
                            info(strfmt("[RecId %1][TableID %5][FieldId %2][3 OLD] Type mismatch (%3/%4)", log.RecId, fieldId, typeOf(conPeek(tmp, 3)), dictField.baseType(), log.table));
                        }
                    }

                    if (updateCon)
                    {
                        updateData = true;
                        newData = conPoke(newData, i, tmp);
                    }
                }
            }

            if (updateData)
            {
                counter++;
                log.Data = newData;
                log.update();
            }
        }
    }
    ttscommit;
    
    select count(RecId) from log;

    info(strfmt("%1 updates made against %2 records", counter, log.RecId));


}