| 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: | |
| Category: | MySQL Migration Toolkit | Severity: | S3 (Non-critical) |
| Version: | 1.0.21 rc | OS: | Windows (Windows 2000) |
| Assigned to: | Michael G. Zinner | CPU Architecture: | Any |
[16 Sep 2005 15:36]
Dom de Wild
[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.
