This discussion is archived
1 Reply Latest reply: Nov 20, 2012 12:44 PM by Gary Graham RSS

MySQL Migration - Default values of DATE columns

Ivan Saenz Newbie
Currently Being Moderated
Hi all,

I'm migrating a MySQL database. Most DATE or TIMESTAMP columns in MySQL have a default value of 0000-00-00 00:00:00. This value is converted to to_date('01-JAN-70 00:00:00', 'dd-MON-yy hh24:mi:ss') even if my SQL Developer interface and NLS parameters are set to SPANISH.

This produces errors when executing the generated script because JAN is not a valid month in spanish. I think migration process should generate to_date('01-01-70 00:00:00', 'dd-MM-yy hh24:mi:ss') instead.

Any way to get the script correctly generated?

Using SQL Developer version 3.2.20.09

Thanks
  • 1. Re: MySQL Migration - Default values of DATE columns
    Gary Graham Expert
    Currently Being Moderated
    Hi Ivan,

    Unfortunately it appears the value is hard-coded. If you browse your SQL Developer installation's
    .../sqldeveloper/sqldeveloper/extensions/oracle.sqldeveloper.migration.mysql.jar
    file, and look for
    /oracle/dbtools/migration/workbench/plugin/mysql/MySQLDefaultValuesMappingRules.xml
    you will see it inside there.

    So you are saying that changing the locale settings in sqldeveloper.conf to English/US is not an option for you? Something like:
    AddVMOption -Duser.language=en
    AddVMOption -Duser.country=US
    Otherwise you might try verifying that all your NLS settings are consistent, both for Migration and the Database. That is:
    Tools > Preferences > Database > NLS
    Tools > Preferences > Migration > Data Move Options (either Masks or the User-Defined Custom Preferences By Source Type)
    Tools > Preferences > Migration > Translators
    This is not my area of expertise. I think some settings depend whether your migration is on- or off-line. If nothing mentioned here works for you, just post again on this thread and perhaps someone more knowledgeable will respond.

    Regards,
    Gary
    SQL Developer Team

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points