Bug #13269 Error converting a MS Access Yes/No field to unsigned TINYINT(1)
Submitted: 16 Sep 2005 15:36 Modified: 12 Jun 2006 20:45
Reporter: Dom de Wild Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S3 (Non-critical)
Version:1.0.21 rc OS:Microsoft Windows (Windows 2000)
Assigned to: Michael G. Zinner CPU Architecture:Any

[16 Sep 2005 15:36] Dom de Wild
Description:
The toolkit converted MS Access Yes/No fields (boolean) to unsigned TINYINT(1). Unfortunately, Visual Basic for Applications (VBA) converts a TRUE value (Yes or whatsoever) to a negative 1 (-1), which in return leads to an error when storing a TRUE value into the migrated MySQL TINYINT field. Once converted to signed TINYINT(1) everything runs fine.

How to repeat:
Migrate a MS Access database containing tables with boolean (Yes/No) fields an see what happens when storing TRUE (Yes) values from within a VBA application.

Suggested fix:
Convert MS Access YES/NO (boolean) fields to signed TINYINT.
[16 Sep 2005 15:58] MySQL Verification Team
mysql> show create table tbtest\G
*************************** 1. row ***************************
       Table: tbtest
Create Table: CREATE TABLE `tbtest` (
  `col1` smallint(5) default NULL,
  `col2` tinyint(1) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
[18 Oct 2005 19:32] 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
[16 Dec 2005 9:22] Dom de Wild
Unfortunately I have to reopen the bug. 

The fields are now (1.0.21 rc, previous was 1.0.18 rc) mapped to signed TINYINT(1), which allows to store True/False values generated by MS Access (via standard forms with checkboxes or VBA), but the bulk data transfer of the MySQL Migration Toolkit converts existing TRUE-Values (which are represented by a negative 1 (-1)) to positive values (unsigned 1 (+1)). It doesn't cause any problems with existing MS Access forms or applications since these seem to be able to accept a positive 1 as TRUE (i.e. any non-zero value seems to be interpreted as TRUE), but it makes a little mess when storing new TRUE values from within forms or VBA, since these then will be stored as a negative 1.
[22 Dec 2005 20:45] MySQL Verification Team
I can confirm your last comment about the bulk insert with YES/NO
Access's fields type, however I am not quite sure if can be considered
a bug instead of VB's limitation since Access's form handles values 1
correctly.
[17 Jan 2006 4:13] Monica
When i exported a table from Access to MySQL using ODBC Databases() it worked fine and converted yes/no fields to bit(1) in MySQL version 5. Which the manual says is similar to TINYINT(1). But in that datatype there is no such unsigned flag to be set. Anyway that works fine with VBScript.
[19 Jan 2006 22:37] Scott Harmon
Here's a patch:

--- MigrationGeneric.java       2006-01-19 16:32:59.000000000 -0600
+++ MigrationGeneric.java-new   2006-01-19 16:33:11.000000000 -0600
@@ -1781,7 +1781,7 @@
                                                boolean value = rset.getBoolean(i + 1);

                                                if (value)
-                                                       insert.append("1");
+                                                       insert.append("-1");
                                                else
                                                        insert.append("0");
                                        } else {
[12 Jun 2006 20:45] 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
[5 Dec 2006 1:42] Pemt Oper
The new version of Migration toolkit ist converting to TIny unsigned and the true/flase (bollena field) are converted to -1 and 1
[22 May 2007 22:28] Robert Dutcheshen
MySQL Migration Toolkit created values -1 and 0 in the MySQL table. I believe it should map the field to a tinytext(1) unsigned instead of signed.
[6 Dec 2007 18:00] Matthew Williams
This problem is more widespread than just converting MS Access. I ran into this problem converting a signed TinyInt(1) from MySQL to MySQL. I used the migration toolkit 1.1.12 after I ran the following commands:
CREATE TABLE `negativetest` (
  `idnegativetest` int(10) unsigned NOT NULL auto_increment,
  `mynumber` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`idnegativetest`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO negativetest (mynumber) VALUES (1), (0), (-1);
When I run the migration here are my create and insert scripts created:
-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

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

DROP TABLE IF EXISTS `test`.`negativetest`;
CREATE TABLE `test`.`negativetest` (
  `idnegativetest` INT(10) unsigned NOT NULL AUTO_INCREMENT,
  `mynumber` TINYINT(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idnegativetest`)
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF
-- ----------------------------------------------------------------------
-- SQL data bulk transfer script generated by the MySQL Migration Toolkit
-- ----------------------------------------------------------------------

-- Disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

INSERT INTO `test`.`negativetest`(`idnegativetest`, `mynumber`)
VALUES (1, 1),
  (2, 0),
  (3, 1);

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

-- End of script

As you can see the negative has turned positive.