| 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: | |
| 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 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...
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