MySQL Bugs: #46677: From Oracle to MySQL => DATETIME columns have a to

Bug #46677	From Oracle to MySQL => DATETIME columns have a to_date DEFAULT
Submitted:	12 Aug 2009 13:54	Modified:	13 Aug 2009 9:20
Reporter:	Cedric Tabin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:		OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	1.1.17

Description:
When importing an Oracle database to MySQL database, the SQL script generated for DATETIME columns looks like that :

CREATE TABLE `mydb`.`mytable` (
  `mypk` VARCHAR(255) BINARY NOT NULL,
  `mydate` DATETIME NULL DEFAULT to_date('01-01-70 00:00:00', 'dd-MM-yy hh24:mi:ss'),
  PRIMARY KEY (`mypk`)
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

The to_date function doesn't exist into MySQL => There is the same function named str_to_date, but doesn't seem to work as a DEFAULT value. In fact there should be a NULL default value.

How to repeat:
Simply follow the steps of the Migration Toolkit and to see the SQL, simply generate the script files when asked.

Suggested fix:
Just remove the DEFAULT to_date(...) since the str_to_date(...) is supported.

Many thanks for writing a bug report.

Unfortunately, this is not a bug.

Migrations from one RDBMS to another always are tricky. There a tons of tool on the market for migrations. It doesn't matter from which RDBMS to which other RDBMS you want to migrate and which special tool you are using.

Tools are just software and don't use artificial intelligence.

In 99 percent of all migrations the tools just solve parts of migration for you the rest you need to do you need to do manually.

You want to migrate from Oracle to MySQL and run into the problem of:

CRAETE TABLE ...(col DATATYPE DEFAULT function(), ...)

col DATATYPE DEFAULT function()

Here you have to think about common and differences between Oracle and MySQL:

Common:
Both are providing functions and "function" means same in Oracle then in MySQL.
Both have some pre-coded functions like sin(), pi(), ....
Both provide that users are able to code and use own functions.
Both provide DEFAULT

Differences:
Oracle has pre-coded to_date() ... MySQL str_to_date() that is what you already figured out
It is possible in Oracle to use functions as default value ... that is not possible in MySQL

What should the migration tool do now?

Oracle will give out:
DEFAULT f()

First of all the tool is not able to guess if f() is a pre-coded function or a function coded from the user.

During migration of the table it just don't know if there will be a self-made function later or if it was before. I mean here something like CREATE FUNCTION f().

Also if MySQL has by accident a function f() it could be that the behaviour of the function f() in MySQL is different to the function f() in Oracle. Maybe you have overloaded f() in Oracle.

Doesn't matter what the tool would do here ... you always will have users complaining. So the best is to let it as it is and let the user manually change into whatever is the best for him.

Nobody forbids you to create a function to_date() in MySQL. Also you could look if you will be able to overtake this function from Oracle (but I doubt that this is possible).

The second problem you run into is that Oracle is using to_date() as default value.

MySQL don't support function based default values.

To what should the tool change the default value? NULL? '1970-01-01', current_date ....

The tool just is not able to guess here what the user wants. You want NULL here but others users want current_date and according to the Oracle implementation it looks like that it would make most sense to use '1970-01-01', ....

So the best way here is, to let the user manually do the default value change.

In your case maybe default NULL and an additionally before insert trigger would make sense.

But that is up to you. The tool is not able to guess here.