| 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 2023 16:19]
Jon Hill
And 8.0.33...
[2 May 2024 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.
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