Bug #26743 Created SQL Syntax failed for "DEFAULT USER"
Submitted: 1 Mar 2007 9:02 Modified: 30 Apr 2007 9:06
Reporter: Rolf LANG Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S3 (Non-critical)
Version:1.1.11, 1.1.10, 1.1.9 OS:Windows (Win XP)
Assigned to: CPU Architecture:Any

[1 Mar 2007 9:02] Rolf LANG
Description:
generated SQL Syntax failed!
----------------------------
DROP TABLE IF EXISTS `aplkn_archv_4`.`tbs_gsft_obj_bzhng`;
CREATE TABLE `aplkn_archv_4`.`tbs_gsft_obj_bzhng` (
  `gsft_obj_bzhng_id` BIGINT(10) NOT NULL,
  `gsft_obj_bzhng_typ_id` BIGINT(10) NOT NULL,
  `gsft_obj_1_id` BIGINT(10) NOT NULL,
  `gsft_obj_2_id` BIGINT(10) NOT NULL,
  `gsft_obj_bzhng_rolle_id` BIGINT(10) NULL,
  `dt_oprtr_id` BIGINT(10) NULL,
  `gsft_obj_bzhng_ntz` VARCHAR(2000) BINARY NULL,
  `bgn_dt` DATETIME NULL,
  `bgn_dt_txt` VARCHAR(30) BINARY NULL,
  `bgn_dt_stnd` VARCHAR(10) BINARY NULL,
  `end_dt` DATETIME NULL,
  `end_dt_txt` VARCHAR(30) BINARY NULL,
  `end_dt_stnd` VARCHAR(10) BINARY NULL,
  `dt_anzg_txt` VARCHAR(300) BINARY NULL,
  `bgn_circa_ind` INT(1) NOT NULL DEFAULT 0,
  `end_circa_ind` INT(1) NOT NULL DEFAULT 0,
  `erfsg_dt` DATETIME NOT NULL,
  `erfsg_usr_cd` VARCHAR(30) BINARY NOT NULL DEFAULT USER,
  `mttn_dt` DATETIME NULL,
  `mttn_usr_cd` VARCHAR(30) BINARY NULL,
  `tst` DATETIME NOT NULL,
  PRIMARY KEY (`gsft_obj_bzhng_id`),
  UNIQUE INDEX `cuk_gob_rolle_bgn_end_dt_id` (`gsft_obj_1_id`, `gsft_obj_bzhng_typ_id`, `gsft_obj_2_id`, `gsft_obj_bzhng_rolle_id`, `bgn_dt`, `end_dt`),
  INDEX `idx_gob_typ_2_id` (`gsft_obj_2_id`, `gsft_obj_bzhng_typ_id`, `gsft_obj_bzhng_rolle_id`),
  INDEX `idx_gob_von_bis_dt` (`bgn_dt`, `end_dt`),
  INDEX `idx_gsft_obj_bzhng_dt_oprtr_id` (`dt_oprtr_id`),
  INDEX `idx_gsft_obj_bzhng_gobr_id` (`gsft_obj_bzhng_rolle_id`),
  INDEX `idx_gsft_obj_bzhng_gobt_id` (`gsft_obj_bzhng_typ_id`),
  CONSTRAINT `cfk_gob_gobt_id` FOREIGN KEY `cfk_gob_gobt_id` (`gsft_obj_bzhng_typ_id`)
    REFERENCES `aplkn_archv_4`.`tbs_gsft_obj_bzhng_typ` (`gsft_obj_bzhng_typ_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cfk_gob_gsft_obj_1_id` FOREIGN KEY `cfk_gob_gsft_obj_1_id` (`gsft_obj_1_id`)
    REFERENCES `aplkn_archv_4`.`tbs_gsft_obj` (`gsft_obj_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cfk_gob_gsft_obj_2_id` FOREIGN KEY `cfk_gob_gsft_obj_2_id` (`gsft_obj_2_id`)
    REFERENCES `aplkn_archv_4`.`tbs_gsft_obj` (`gsft_obj_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cfk_gobz_dt_oprtr_id` FOREIGN KEY `cfk_gobz_dt_oprtr_id` (`dt_oprtr_id`)
    REFERENCES `aplkn_archv_4`.`tbs_dt_oprtr` (`dt_oprtr_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `cfk_gobz_gobr_id` FOREIGN KEY `cfk_gobz_gobr_id` (`gsft_obj_bzhng_rolle_id`)
    REFERENCES `aplkn_archv_4`.`tbs_gsft_obj_bzhng_rolle` (`gsft_obj_bzhng_rolle_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = INNODB;

How to repeat:
using defaults settings only:
SRC:Oracle Thin JDBC Driver using SID
Dst:MySQL JDBC Driver 5.0

Suggested fix:

`erfsg_usr_cd` VARCHAR(30) BINARY NOT NULL DEFAULT USER, 

is 'DEFAULT USER' a reserved keyword ?
[4 Mar 2007 19:40] Valeriy Kravchuk
Please, try to repeat with a newer version, 1.1.10 (from GUI Tools 5.0-r10), and inform about the results.
[5 Mar 2007 8:35] Rolf LANG
2007-03-05
Thanks for advise to try newer version.

I could reproduce the bug using 1.1.10.
The generated SQL statement still has an illegal syntax

CREATE TABLE 
...
...`field-name` varchar(30) BINARY NOT NULL DEFAULT USER ....

I think Oracles SQL supports "DEFAULT USER", where MySQL doesn't.
[24 Apr 2007 10:20] Valeriy Kravchuk
This is a bug, as default value is just copied from Oracle. Verified by code review of 1.1.11.
[30 Apr 2007 9:06] Michael G. Zinner
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html