Bug #96231 Failed to add Auto Increment in PK column during migration from MS SQL
Submitted: 17 Jul 2019 11:16 Modified: 18 Jul 2019 8:41
Reporter: Juro Team Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S3 (Non-critical)
Version:8.0.14 / 8.0.15 / 8.0.16 OS:Windows
Assigned to: CPU Architecture:Any
Tags: auto increment, migration, ms sql

[17 Jul 2019 11:16] Juro Team
Description:
There is a bug during migration from MS SQL to MySQL.

There is no Auto Increment in PK column in target table. 

I guess it happens during preparation of CREATE statements (examples below). 

Checked with 
  Workbench for Windows 10:
   versions with BUG: 8.0.14 / 8.0.15 / 8.0.16
   versions works FINE: 8.0.12 / 8.0.13
 
  source server:
    MS SQL 2017 on Ubuntu Linux
    MS SQL 2014 on Windows 

  and target server: 
    MySQL 8.0.16 on Ubuntu Linux
    MySQL 8.0.16 on Windows 

I paste below information only for one table (for example), but it happens in every table in multiple databases.

Sample CREATE statement from Workbench 8.0.16:

CREATE TABLE IF NOT EXISTS `ekostrateg_dev_terminarz`.`ENTRY` (
  `ID` BIGINT NOT NULL,          ####### AUTO_INCREMENT missing
  `CREATED` DATETIME(6) NULL,
  `UPDATED` DATETIME(6) NULL,
  `ALERT_DATE` DATETIME(6) NULL,
  `CATEGORY` VARCHAR(255) NULL,
  `COMPONENT` VARCHAR(255) NULL,
  `CONTENT` LONGTEXT NULL,
  `DATE` DATETIME(6) NULL,
  `ENDS` DATETIME(6) NULL,
  `NOTIFICATION_SENT` TINYINT(1) NOT NULL,
  `REGION` VARCHAR(255) NULL,
  `SHOW_ENDS` TINYINT(1) NULL,
  `TOPIC` VARCHAR(500) NULL,
  PRIMARY KEY (`ID`))
  
  
Sample CREATE statement from Workbench 8.0.12:
 
CREATE TABLE IF NOT EXISTS `ekostrateg_prod_terminarz`.`ENTRY` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT,     ####### AUTO_INCREMENT added
  `CREATED` DATETIME(6) NULL,
  `UPDATED` DATETIME(6) NULL,
  `ALERT_DATE` DATETIME(6) NULL,
  `CATEGORY` VARCHAR(255) NULL,
  `COMPONENT` VARCHAR(255) NULL,
  `CONTENT` LONGTEXT NULL,
  `DATE` DATETIME(6) NULL,
  `ENDS` DATETIME(6) NULL,
  `NOTIFICATION_SENT` TINYINT(1) NOT NULL,
  `REGION` VARCHAR(255) NULL,
  `SHOW_ENDS` TINYINT(1) NULL,
  `TOPIC` VARCHAR(500) NULL,
  PRIMARY KEY (`ID`))
  

  
Log info for that table from Workbench 8.0.16:

  - ENTRY
    warning  Autoincrement unset for column ID: Autoincrement for non primary key columns is not allowed in MySQLnote  Collation Polish_CI_AS migrated to utf8_general_ci
  - CATEGORY
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - COMPONENT
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - CONTENT
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - NOTIFICATION_SENT
    note  Source column type BIT was migrated to TINYINT(1)
  - REGION
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - SHOW_ENDS
    note  Source column type BIT was migrated to TINYINT(1)
  - TOPIC
    note  Collation Polish_CI_AS migrated to utf8_general_ci
	
    4.5. Table ekostrateg_dev_terminarz.ENTRY (ENTRY)

    Columns:
      - ID BIGINT    
      - CREATED DATETIME(6)    
      - UPDATED DATETIME(6)    
      - ALERT_DATE DATETIME(6)    
      - CATEGORY VARCHAR(255)    
      - COMPONENT VARCHAR(255)    
      - CONTENT LONGTEXT    
      - DATE DATETIME(6)    
      - ENDS DATETIME(6)    
      - NOTIFICATION_SENT TINYINT(1)    
      - REGION VARCHAR(255)    
      - SHOW_ENDS TINYINT(1)    
      - TOPIC VARCHAR(500)    

	Foreign Keys:

	Indices:
	  - PK__ENTRY__3214EC27488D42ED (ID)

Log info for that table from Workbench 8.0.12:

  - ENTRY
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - CATEGORY
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - COMPONENT
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - CONTENT
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - NOTIFICATION_SENT
    note  Source column type BIT was migrated to TINYINT(1)
  - REGION
    note  Collation Polish_CI_AS migrated to utf8_general_ci
  - SHOW_ENDS
    note  Source column type BIT was migrated to TINYINT(1)
  - TOPIC
    note  Collation Polish_CI_AS migrated to utf8_general_ci
	
	4.5. Table ekostrateg_prod_terminarz.ENTRY (ENTRY)

	Columns:
	  - ID BIGINT    
	  - CREATED DATETIME(6)    
	  - UPDATED DATETIME(6)    
	  - ALERT_DATE DATETIME(6)    
	  - CATEGORY VARCHAR(255)    
	  - COMPONENT VARCHAR(255)    
	  - CONTENT LONGTEXT    
	  - DATE DATETIME(6)    
	  - ENDS DATETIME(6)    
	  - NOTIFICATION_SENT TINYINT(1)    
	  - REGION VARCHAR(255)    
	  - SHOW_ENDS TINYINT(1)    
	  - TOPIC VARCHAR(500)    

	Foreign Keys:

	Indices:
	  - PRIMARY (ID)

How to repeat:
Use Migration wizard from MS SQL to MySQL
[17 Jul 2019 13:56] MySQL Verification Team
Thank you for the bug report. Please provide the create table statement for MSSQL server. Thanks.
[17 Jul 2019 23:18] Juro Team
This is the statement you asked for:

/****** Object:  Table [dbo].[ENTRY]    Script Date: 2019-07-18 01:09:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ENTRY](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[CREATED] [datetime2](7) NULL,
	[UPDATED] [datetime2](7) NULL,
	[ALERT_DATE] [datetime2](7) NULL,
	[CATEGORY] [varchar](255) NULL,
	[COMPONENT] [varchar](255) NULL,
	[CONTENT] [varchar](max) NULL,
	[DATE] [datetime2](7) NULL,
	[ENDS] [datetime2](7) NULL,
	[NOTIFICATION_SENT] [bit] NOT NULL,
	[REGION] [varchar](255) NULL,
	[SHOW_ENDS] [bit] NULL,
	[TOPIC] [varchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
[18 Jul 2019 8:41] MySQL Verification Team
Thank you for the feedback.
[5 Dec 2019 12:07] w lg
This bug still exist in 8.0.18, could someone to fix this bug? that will be great. Thanks.
[20 May 2020 15:11] Dennis Racine
This bug still exists in 8.0.20, I'm running 8.0.13 to get around the issue, which worked fine on MySQL 5.7 but I'm upgrading to mySQL 8 and now I have another bug that was fixed after 8.0.13, so I really need this fixed! Crazy to think this bug has existed for the last 7 versions? what the heck! Please fix this ASAP.
[2 Dec 2020 17:17] Nick White
Please fix this!

This bug is also still present in 8.0.22. Just migrated hundreds of tables from MSSQL to MySQL 5.7.32 and wound up with a big mess to fix. None of the AUTO_INCREMENT columns were set after a "successful" migration. Disappointed to find out this is a 17-month-old bug.
[8 Mar 2022 16:25] Augusto Junior
I have the same issue migrating 96 tables from MSSQL to MySQL.
I'll need to alter all the tables, and I have more huge databases to migrate...
[14 Jul 2023 16:19] Jon Hill
And 8.0.33...
[2 May 10:15] Simon Hazzard
I've just had the same experience migrating from MySQL to MySQL.
Working fix by:
  removing all foreign keys in related tables
  modifying the auto-increment field in the affected table (ALTER TABLE <tablenamne> MODIFY id INTEGER NOT NULL AUTO_INCREMENT;)
  re-creating all foreign keys removed. (I used the same key names etc.)
If related tables have no data yet there's no need to remove the foreign key first.
For all affected tables.