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 16:19] Jon Hill
And 8.0.33...