Bug #9713 Wrong Datatype Conversion MS ACCESS BYTE

• Submitted: 7 Apr 2005 11:16
• Modified: 17 May 2005 8:25
• Reporter: Thomas Oftring
• Status: Closed
• Category: MySQL Migration Toolkit
• Severity: S3 (Non-critical)
• OS: Windows (Windows XP)
• Assigned to: Michael G. Zinner
• CPU Architecture: Any

[7 Apr 2005 11:16] Thomas Oftring

Description:
By migrating an MS Access 2000 database to mysql, a create statement for the database is created, that doesn`t work.

How to repeat:
Create an MSAccess 2000 Database with a table "test" that uses a column with the column name "id" for the column data type choose "number". For "number" select the datatype "BYTE".

The Migration Toolkit now generates the following create script for MySQL:

-- ----------------------------------------------------------------------
-- SQL create script
-- ----------------------------------------------------------------------

DROP DATABASE IF EXISTS `db1`;

CREATE DATABASE `db1`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;

CREATE TABLE `db1`.`test` (
  `ID1` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `id` (nil) NULL,
  PRIMARY KEY (`ID1`),
  INDEX `id` (`id`)
)
ENGINE = INNODB;

That doesn't work because `id` (nil) NULL is created and (nil) is no datatype of MySQL.

Suggested fix:
The problem is that the "BYTE" datatype of a number column is an equivalent to the MySQL "tinyint" datatype, the right statement should be:

-- ----------------------------------------------------------------------
-- SQL create script
-- ----------------------------------------------------------------------

DROP DATABASE IF EXISTS `db1`;

CREATE DATABASE `db1`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;

CREATE TABLE `db1`.`test` (
  `ID1` INTEGER(10) NOT NULL AUTO_INCREMENT,
  `id` TINYINT NULL,
  PRIMARY KEY (`ID1`),
  INDEX `id` (`id`)
)
ENGINE = INNODB;

[7 Apr 2005 11:18] Thomas Oftring

ACCESS Database

Attachment: db1.mdb (application/msaccess, text), 116.00 KiB.

[17 May 2005 8:25] 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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html