MySQL Bugs: #33044: Unsigned TinyInt(1) negative numbers turn positive during migration

Bug #33044	Unsigned TinyInt(1) negative numbers turn positive during migration
Submitted:	6 Dec 2007 18:12	Modified:	21 Oct 2008 13:13
Reporter:	Matthew Williams	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.1.12	OS:	Windows
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	tinyint negative

Description:
When you migrate a database from MySQL to MySQL with a signed tinyint(1) field with negative numbers they will turn positive during the migration.

How to repeat:
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);
Run migration toolkit for above table. You will get:
-- ----------------------------------------------------------------------
-- 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.

Suggested fix:
Keep negative numbers negative.

Thank you for a bug report. Verified just as described while migrating from MySQL 5.0.x.

Mark, it may sound strange, but this is not a bug. Actually, MT tries to handle the special case TINYINT(1) in a way, which is most commonly used: in the form as boolean. Boolean is an alias for TINYINT(1) and the server converts columns with that datatype to TINYINT(1). However, after that MT cannot know what the user originally wanted so he assumes the most common case: boolean. This results in the data conversion you encountered. All you byte values have been converted to boolean.

If you want to avoid this then you have to switch off this automatic conversion. You can do this in the advanced section of the source database connection settings. Specify a full JDBC connection string (http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html) and add tinyInt1isBit=false to it. This will then make the JDBC connector not to do any conversion and your data migrates correctly.

If you need a sample connection string then just do a first connection as usual and look in the log (advanced section) where you can find the used connection string. Copy that over and add the flag and you are done.

Oh, sorry, I meant Matthew of course.