MySQL Bugs: #17880: Access singles are converted to MySQL 7,2 double

Bug #17880	Access singles are converted to MySQL 7,2 double
Submitted:	3 Mar 2006 0:18	Modified:	30 May 2013 11:24
Reporter:	Scott Harmon	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	migrate MSAccess to MySQL

Description:
The precision on the decimal is always set to 2 decimal places no matter what it was in msaccess.  The data is then rounded on import (even in strict mode).

How to repeat:
Reverse engineer a table with singles.

Suggested fix:
Default to a greater number of decimal places (6).  I do not think access exposes the precision through the ODBC driver.

Thank you for the bug report.
I was unable to repeat the behavior reported. I created a MSAccess table with
decimal, precision 18 and scale 5. Below how was migrated:

mysql> desc table1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| col1  | decimal(18,5) | YES  |     | NULL    |       |
| col2  | varchar(50)   | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

Please choose 'Single' in the Field Size for the access database.

I created a table called 'Table1' with a field called 'col1' in MsAccess, the type of the field is 'Number' and size is 'Single', Decimal places is 'Auto'

When migrating this is what the migration tool created:

-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `db1`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `db1`.`Table1`;
CREATE TABLE `db1`.`Table1` (
  `ID` INT(10) NOT NULL AUTO_INCREMENT,
  `col1` DOUBLE(7, 2) NULL,
  PRIMARY KEY (`ID`)
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF

Even though the field in access may have more than 2 decimal places.

This is in Access2000.

I can reproduce the same problem with Access 2000 and the latest MySQL Migration Toolkit.

This seems to be a Microsoft Access error. Please try with newer version of Access.

I tried with MS Access 2003 on Windows XP SP2, all products fully patched and Sun Java 1.5 u12. Unfortunately, this did not change the results. Which version of MS Access did you use?

The problem is still the creation script:
CREATE TABLE `single3`.`foobar` (
  `ID1` INT(10) NOT NULL AUTO_INCREMENT,
  `id` INT(10) NULL,
  `foobar` DOUBLE(7, 2) NULL,
  PRIMARY KEY (`ID1`),
  INDEX `id` (`id`)
)

The insertion script works and gets the data in the necessary precision:
...
INSERT INTO `single3`.`foobar`(`ID1`, `id`, `foobar`)

VALUES (1, 42, 0.0075);

...

But MySQL will round the value to comply with the table schema.

Today I checked with Access 2007. The MySQL Migration Toolkit does not support the new Access 2007 file format so I had to save the database in an Access 2002-2003 compatible mdb format. Nevertheless, the single column is still being created as Decimal(7,2).

Further experiments showed that both the Decimal and double (MS Access) datatype are converted correctly, just the single type is not.

PS: All Microsoft products are using the German locale. Maybe this is a problem when reproducing this issue?

Just another note: When using pyodbc and reflecting the database myself, it seems that the precision is not exposed via odbc. In the column info the attribute "decimal_digits" is None (Python equivalent to Java's null) and "num_prec_radix" 2. Maybe we just need a manual default setting?

(The "great"/"big" solution would be inspecting all SINGLE columns and checking for the maximum number of decimal places and setting the value before creating the schema.)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This bug is still open. I'm able to reproduce the problem using MS Access 2000 and 2003 (and MS Access 2007 when using the mdb format).

MySQL employees: Please prevent automatic closing of this bug. The lack of progress here is only due to lacking response from the MySQL team.

New here--sorry in advance if I break any protocol by my post.

This bug just bit me while attempting a migrating from an Access 95 format database.  The fields in questions show these properties in Access:

Field Size: Single
Decimal Places: Auto

The Migration toolkit converted these fields to DOUBLE(7,2) which ended up rounding off the extra digits of various tax rate fields.

From what I can see, it seems the problem code comes from MigrationAccess.java:

187 } else if (sourceDatatypeName.equalsIgnoreCase("REAL")
188     || sourceDatatypeName.equalsIgnoreCase("DOUBLE")) {
189   targetColumn.setDatatypeName("DOUBLE");
190
191   if ((targetColumn.getScale() == 0)
192       && (migrationParams != null)
193       && (migrationParams.get("autoDecimalDigits")
194           .equalsIgnoreCase("yes"))) {
195     targetColumn.setScale(targetColumn.getPrecision() / 3);
196   }

Line 195 forces scale for these fields to be 1/3 of precision.  This does not seem like an accurate translation when Access allows for total of 7 digits and variable scale on these fields.  In my tests, it seems the better option would be to set the precision and scale to -1 as is done for FLOAT values from Oracle in MigrationOracle.java.

To implement this change would mean replacing the above line 195 with this code:

195    targetColumn.setScale( -1 ); targetColumn.setPrecision( -1 );

My tests show this results in a target field of DOUBLE (no length or scale).  The resulting field holds more digits than the original Access field and allows for variable scale.

Hi guys. Verified as described. Chris is right, hardcoded part makes bug happen so it does not matter what one defines in Access.

Partial workaround would be, as suggested, not to use Single Access type but hardcoded part of migration toolkit still requires proper fix.

Maybe we should add "Treat single as xxx decimals field" or something. Analyzing Access data is out of question as it would take too much time.

Image 1

Attachment: Bug#17880-1.JPG (image/jpeg, text), 116.83 KiB.

Image 2, finetune mappings

Attachment: Bug#17880-2.JPG (image/jpeg, text), 112.21 KiB.

Proper table created

Attachment: Creates.sql (, text), 696 bytes.

After careful review of how Access behaves and another look at Migration toolkit I must revert my ruling.

As Access does not provide detailed metadata on Single column, it is my opinion that GUI team did good job providing users with a way to correct this by hand (see attached images).

Just a note. Migration of Access data to MySQL can be achieved, for example, via ODBC connection too.

I respectfully disagree.  This could most certainly be handled better by the migration toolkit.  Even a WARNING would be better than the silent truncation of your data.  This is a VERY COMMON use of the migration toolkit (migration from msaccess to mysql), and I think this is a potentially very damaging 'feature'.

Scott, I agree with your conclusions but it's as dangerous as Access not having proper metadata... As many and viable workarounds exist I can only set this report to "Verified" if you lower severity to S4 (feature request) in terms of "Add this functionality (change behaviour, throw warning...) to Migration toolkit".

Thanks for your interest in MySQL.

"it's as dangerous as Access not having proper metadata" <-- I do not agree with this statement.  The danger for the migration is that you silently lose some of your data, the 'not having proper metadata' has never lost me data (until the migration :-D).  That is why I think at least a warning (if the metadata isn't 'proper') is warranted and I take it very seriously.  I still think it is a serious bug and should be addressed with at least feedback to the user that they may lose data and should manually change the datatype (7,2 is almost ALWAYS WRONG). Thus, I do not agree that this is a feature request, but a serious issue.  However, if that is the only way you will agree to keep the bug open and not close it (as 'not a bug', or whatnot), I will change it to 'feature request'.

Scott: 'not having proper metadata' has never lost me data (until the migration :-D) <<< exactly... Without proper metadata you are actually asking from Migration toolkit devs to perform wizardry that suits you and in this particular case. This is the reason I can not just put verified, no matter how problematic this is for you. Conversion has to relay on something, otherwise we would have like 10000000 IF's for each client that exists on the market.

Please lower the severity and I'll put report into "Verified".

After review and discussion I will mark this bug as Verified now as it truly represents a problem.

Second problem we need to address is to, at least, update documentation to reflect this truncation.

Bug #38156 is a duplicate of this bug here.