AWS DMS Issue with boolean column

Question

I'm trying to enable replication with DMS, using as source an Aurora mySQL instance and as destination a Redshift instance. The replication fails on boolean columns. I have declared the boolean column as BIT(1) on the mySQL instance. According to the documentation boolean columns in mySQL should be defined as BIT:

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html#CHAP_Source.MySQL.DataTypes

If I remove the boolean column it works. I also tried to define the column as Boolean. That did not work either.

This is the error I'm getting:

2018-08-26T16:59:19 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 42804 NativeError: 30 Message: [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42804] ERROR: column "state" is of type boolean but expression is of type character varying, HINT: You will need to rewrite or cast the expression. [1022502] (ar_odbc_stmt.c:4428)

Answer 1

This turns out to be a bug of DMS. This occurs only during ongoing replication, and not in full load. During replication the from Aurora MySql to Redshift the boolean is cast to Varchar resulting the error above.

Answer 2

I stuck in the same problem but I migrated my base so I solved with a post-script that may be can help you. Also, you can use the DMS events to notify the SMS and then call a lambda to make that.

Using node just run the file.js init()

const AWS = require("aws-sdk");

AWS.config.update({
    region: "us-east-1"    
});

const documentClient = new AWS.DynamoDB.DocumentClient();

let invalidList = [];

const TableName = 'TableName';
const params = {
    TableName: TableName,
};

module.exports.init = function () {
    console.log("Start Conversions of Details Booleans")

    documentClient.scan(params, function(err, data) {
        if (err) {
            console.error("Unable to read item. Error JSON:", JSON.stringify(err, null, 2));
        } else {
            console.log("Scan succeeded.");

            // By default scan retrieves at max 1 mb of data
            if (typeof data.LastEvaluatedKey != "undefined") {
                console.log("Scanning for more...");
                params.ExclusiveStartKey = data.LastEvaluatedKey;
                documentClient.scan(params, onScan);
            }

            invalidList = getinvalidList(data);

            if(invalidList.length == 0) {
                console.log("All data is aready migrated");
                return;
            }

            updateList(invalidList);
        }
    });
};

function getinvalidList(list) {
    return list.Items.reduce((invalidList, item) => {
        if (item) {
            const variable = (item.variable && item.variable != undefined) ? item.variable : '0'; 

            if (isNotBoolean(variable)) {
                invalidList.push(item);
            } 
        }

    return invalidList;
    }, []);
}

function updateList(list) {
    list.forEach(item => {                
        var params = {
            TableName: TableName,         
            Key: {
                "id": item.id,
            },
            UpdateExpression: "set variable = :variable",
            ExpressionAttributeValues: {
                ":variable": newValue(item.variable),
            },
            ReturnValues: "UPDATED_NEW"
        };

        documentClient.update(params, function(err, data) {
            if (err) console.log(err);
            else console.log(data);
        },
    )
    });
}

function newValue(variable) {    
    return isNotBoolean(variable) ? !!+variable : variable
}

function isNotBoolean(variable) {
    return (typeof variable !== 'boolean')
}