MySQL Bugs: #86232: Cannot properly migrate columns with DEFAULT CURRENT TIME

Bug #86232	Cannot properly migrate columns with DEFAULT CURRENT TIME
Submitted:	9 May 2017 2:26	Modified:	25 Sep 2017 7:19
Reporter:	Erica Kane	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S2 (Serious)
Version:	6.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	sqlanywhere

Description:
It is common for SQL Anywhere database to have columns defined as TIMESTAMP DEFAULT CURRENT TIMESTAMP. I am attempting to migrate into MySQL 5.7.18, which should have the equivalent functionality with DATETIME DEFAULT CURRENT_TIMESTAMP.

Unfortunately, the migration wizard does not realize this and it simply removes the default value for all of the relevant colunns. Therefore it is necessary to hand-edit them. This is extremely painful and error-prone.

How to repeat:
Create a SQL Anywhere database table with a column defined as TIMESTAMP DEFAULT CURRENT TIMESTAMP. Use the Migration Wizard to make a schema for MySQL 5.7. You will see migration warnings and a notice that the default has been removed.

Suggested fix:
For a target MySQL database of a version that supports DATETIME DEFAULT CURRENT_TIMESTAMP columns (which I believe would be 5.6 or higher) allow the wizard to use that option as needed.

Hello Erica Kane,

Thank you for the bug report.
Discussed internally with WB developer and confirmed that "CURRENT TIMESTAMP" is handled during migration and this seems to be something else causing the problem, could you please provide full(unaltered) WB log to investigate further on this issue? Also, provide exact table definition to reproduce the issue at our end.

Thanks,
Umesh

Workbech log of migration

Attachment: wb.log (application/octet-stream, text), 14.38 KiB.

Script generated to perform migration

Attachment: migration_script.sql (application/octet-stream, text), 1.71 KiB.

Thank you, I have attached the requested log file along with the generated migration script.

I defined the table in SQL Anywhere 16 as

CREATE TABLE DBA.MYSQL_TEST (
NAME VARCHAR(20)    NOT NULL,
ENROLL_DATE     TIMESTAMP DEFAULT CURRENT TIMESTAMP );

and then

INSERT INTO MYSQL_TEST (NAME)
VALUES ('Test Person');

I then attempted to migrate only that table, and got the warning that "Default current timestamp is not supported. Removed!" As you can see in the attached migration script that was generated, that default was indeed removed.

Hello Erica,

Thank you for the bug report.
Verified this behavior on SQL Anywhere 17 and MySQL Workbench in 6.3.9 version.

Thanks,
Chiranjeevi.

Screenshot

Attachment: Bug_86232.JPG (image/jpeg, text), 209.28 KiB.