AWS DMS creates tables with no auto_increment

If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like default values are moved over either.

You can either migrate all your changes after moving the data, or create the schema first on your destination and tell DMS not to create tables.

Just keep in mind that copying data with indexes at the same time is much slower, so if you have a lot of data you can create them afterwards or simply just wait longer for the sync.

I faced the same problem and since I have too many tables to go one by one, I created a SQL stored procedure to do the work: for all the tables, get the primary keys, and if the primary key is a single column and the type %int%, add the autoincrement extra.

You can find the code here

I have around 550 tables, and the process was quite fast. If you have much more maybe you will need to improve this setup that uses multiple subqueries, and is not minding perfomance.

DROP TABLE if exists temp;

CREATE TABLE temp (
  `table` varchar(250) DEFAULT NULL,
  `colum` varchar(250) DEFAULT NULL,
  `type` varchar(250) DEFAULT NULL,
  `number_of_PK` tinyint(2) DEFAULT NULL,
  `alter` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP PROCEDURE IF EXISTS foo;

DELIMITER //

CREATE PROCEDURE foo()

BEGIN

    declare tableName varchar(250);
    declare done int default 0;
    declare totalKeys int default 0;
    declare columnName varchar(250);
    declare columnType varchar(250);

    #select all the #tables but exclude the ones generated by AED DMS
    declare cur1 cursor for SELECT Distinct(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME not LIKE "awsdms_%";
    declare continue handler for not found set done=1;

    open cur1;

    igmLoop: loop

        fetch cur1 into tableName;

        set @cnt = 0;
        #SElECT all the tables-colum-datatype
        SELECT `total`,`column_name_`, `data_type_` into totalKeys, columnName, columnType FROM (
            SELECT MAX(rowNumber) as total,  `column_type_`, `data_type_` , `column_name_` FROM (
                SELECT (@cnt := @cnt + 1) AS rowNumber, `column_name_`, `column_type_`, `data_type_` FROM (
                    SELECT 
                        c.COLUMN_NAME as `column_name_`, cls.COLUMN_TYPE as `column_type_`, DATA_TYPE as `data_type_`
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS p 
                        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
                            ON  c.TABLE_NAME = p.TABLE_NAME
                            AND c.CONSTRAINT_NAME = p.CONSTRAINT_NAME
                        JOIN INFORMATION_SCHEMA.COLUMNS cls
                            ON  c.TABLE_NAME = cls.TABLE_NAME
                            AND c.COLUMN_NAME = cls.COLUMN_NAME
                        WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
                        AND c.TABLE_NAME = tableName
                        group by c.COLUMN_NAME
                    ) a                 
                ) b
            ) c;

        SET @t1 = CONCAT("ALTER table ", tableName," modify ", columnName," ", columnType," NOT NULL AUTO_INCREMENT");
        #log it
        insert into temp values (tableName, columnName, columnType, totalKeys, @t1 );
        if totalKeys = 1 and columnType like "%int%" then
                #update the row
            PREPARE stmt3 FROM @t1;
            EXECUTE stmt3;
        end if; 

    if done = 1 then leave igmLoop; end if; 

    end loop igmLoop;

    close cur1;


END //

DELIMITER ; 

call foo();