Description:
When migrating from Microsoft SQL Server to MySql tables with Auto Increment primary key columns do not have the AI flag set in the destination schema.
Microsoft SQL Server 2019 (RTM-GDR) (KB5068405) - 15.0.2155.2 (X64) Oct 7 2025 21:11:52 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )
MySql 8.4.7-cll-lve
How to repeat:
Connect to local MS SQL instance using ODBC data source.
Connect to remote MySql instance using Standard TCPIP.
Schema is retrieved successfully from source DB.
Select database to migrate with "Only one schema" option selected.
Proceed through the remaining migration steps with default options selected.
Migration completes with zero errors reported.
It instructs me to check the logs, but there is nothing to note about AI or identity columns, or indeed much else other than a list of apparently "successfully" migrated tables and row counts.
Unfortunately for tables that have auto incrementing primary key (identity) columns in MS SQL Server, do not have the AI flag set in the automatically created destination schema. It is necessary to manually script adding AI flag back in.
Suggested fix:
Ensure AI flag is set in destination schema for tables that have the is_identity field set in MS SQL.
As all my identity columns in MS SQL are of type int, I wrote the following simple MS SQL script to generate the required MySql schema changes...
SELECT 'ALTER TABLE '+t.name+' MODIFY COLUMN '+c.name+' INT auto_increment;'
FROM sys.columns c
INNER LOOP JOIN sys.tables t on t.object_id = c.object_id
WHERE is_identity = 1
© 2026, Oracle Corporation and/or its affiliates