| Bug #21111 | Migrtion of default values (getdate()) from MSSQL to mysql | ||
|---|---|---|---|
| Submitted: | 18 Jul 2006 11:20 | Modified: | 8 Aug 2006 15:20 |
| Reporter: | Lava Kumar Inampudi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Migration Toolkit | Severity: | S3 (Non-critical) |
| Version: | 1.0.25/1.1.1 Beta | OS: | Windows (Windows 2000 sp4 professional) |
| Assigned to: | CPU Architecture: | Any | |
[18 Jul 2006 14:14]
MySQL Verification Team
Thank you for the bug report. I was able to repeat.
`pubs_dbo`.`employee`
---------------------
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate(),
PRIMARY KEY (`emp_id`),
INDEX `employee_ind` (`lname`, `fname`, `' at line 9
[8 Aug 2006 15:20]
Michael G. Zinner
This is now fixed in the source repository and will be included into the next release. DATETIME/TIMESTAMP DEFAULT getdate(); is now migrated to TIMESTAMP DEFAULT CURRENT_TIMESTAMP; Please note that other functions used in the DEFAULT clause need to be migrated automatically as MySQL does not support functions for DEFAULT values.
Description: While migrating pubs database from MSSQL 2000 to MySQL 5.1, I found following bug. Please look into the following. May be it is the problem of migrating default value (getdate()) or it may be indexes. Creating table employee ... DROP TABLE IF EXISTS `pubs_dbo`.`employee` Creating table employee ... CREATE TABLE `pubs_dbo`.`employee` ( `emp_id` CHAR(9) NOT NULL, `fname` VARCHAR(20) NOT NULL, `minit` CHAR(1) NULL, `lname` VARCHAR(30) NOT NULL, `job_id` SMALLINT(5) NOT NULL DEFAULT 1, `job_lvl` TINYINT(3) NULL DEFAULT 10, `pub_id` CHAR(4) NOT NULL DEFAULT '9952', `hire_date` DATETIME NOT NULL DEFAULT getdate(), PRIMARY KEY (`emp_id`), INDEX `employee_ind` (`lname`, `fname`, `minit`), CONSTRAINT `FK__employee__job_id__1BFD2C07` FOREIGN KEY `FK__employee__job_id__1BFD2C07` (`job_id`) REFERENCES `pubs_dbo`.`jobs` (`job_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK__employee__pub_id__1ED998B2` FOREIGN KEY `FK__employee__pub_id__1ED998B2` (`pub_id`) REFERENCES `pubs_dbo`.`publishers` (`pub_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = INNODB An error occured while executing the SQL statement. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate(), PRIMARY KEY (`emp_id`), INDEX `employee_ind` (`lname`, `fname`, `' at line 9 How to repeat: Try to migrate all tables from pubs database from mssql to mysql. Observe creation Results schema creation task. You will get error for employees and titles. Suggested fix: Whenever we come across defaultvalues in the form of functions, we need to migrate them to mysql accordingly.