--------------------------------------------------------------------- On 4.1 machine source I will inspect the machine and then create a test table that will not work: mysql> use import_data; Database changed mysql> select version(); +---------------------+ | version() | +---------------------+ | 4.1.16-standard-log | +---------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `test` ( -> `testid` int(10) unsigned NOT NULL auto_increment, -> `testtinyint` tinyint(1) unsigned default '0', -> PRIMARY KEY (`testid`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> insert into import_data.test (testtinyint) values (1), (2), (3), (4); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from import_data.test; +--------+-------------+ | testid | testtinyint | +--------+-------------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 1 | | 6 | 2 | | 7 | 3 | | 8 | 4 | +--------+-------------+ 8 rows in set (0.00 sec) ----------------------------------------------------------------------------- Heres an example of a table that will work with the import that is in this same database: notice that it has no tinyint(1) unsigned fields in it: show create table dice_copy; CREATE TABLE `dice_copy` ( `link_id` int(10) unsigned NOT NULL auto_increment, `rip_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `cURL_link` varchar(255) NOT NULL default '' COMMENT 'links are in cURL format. replace -d with "&" and append together to create url.', `job` blob, `fname` varchar(255) default NULL, `lname` varchar(255) default NULL, `company` varchar(255) default NULL, `addy_1` varchar(255) default NULL, `addy_2` varchar(255) default NULL, `city` varchar(255) default NULL, `state` varchar(50) default NULL, `zip` varchar(255) default NULL, `phone_area` varchar(255) default NULL, `phone_prefix` varchar(255) default NULL, `phone_suffix` varchar(255) default NULL, `fax_area` varchar(255) default NULL, `fax_prefix` varchar(255) default NULL, `fax_suffix` varchar(255) default NULL, `email` varchar(255) default NULL, `web` varchar(255) default NULL, `visited` varchar(255) default NULL, PRIMARY KEY (`link_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ------------------------------------------------------------------------------ heres an example of a table that will work with the import that is on the same server: notice that it has no tinyint(1) unsigned in it (it does work with a tinyint(2) unsigned: mysql> show create table calc_results; +--------------+-------------+ | Table | Create Table +--------------+-----------------+ | calc_results | CREATE TABLE `calc_results` ( `contractid` int(10) unsigned NOT NULL default '0', `cst_hr` float(8,2) unsigned default '0.00', `cadd_hr` float(8,2) unsigned default '0.00', `bill_hr` float(8,2) unsigned default '0.00', `badd_hr` float(8,2) unsigned default '0.00', `stdshifts` tinyint(2) unsigned default '0', `stdhrs` tinyint(2) unsigned default '0', `yearhours` int(6) unsigned default '0', `gmpercent` float(5,2) default '0.00', `gmdollars` float(5,2) default '0.00', UNIQUE KEY `contractid` (`contractid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------------+-----------------+ 1 row in set (0.00 sec) ------------------------------------------------------------------------------- Here is an example of another table on the same server that will not work in the import: notice that it has a tinyint(1) unsigned in it: mysql> use pulse_contacts; Database changed mysql> show create table app_recruiterget; +------------------+-------------+ | Table | Create Table | +------------------+-------------+ | app_recruiterget | CREATE TABLE `app_recruiterget` ( `indexnum` int(10) unsigned default NULL, `indexid` tinyint(1) unsigned default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------------+-------------+ 1 row in set (0.00 sec) mysql> select * from app_recruiterget; +----------+---------+ | indexnum | indexid | +----------+---------+ | 1 | 1 | +----------+---------+ 1 row in set (0.00 sec) ------------------------------------------------------------------------------- Here is an example of another table on the same server that will not work in the import: notice that it has a tinyint(1) unsigned in it: mysql> show create table audit_detail; +--------------+----------+ | Table | Create Table | +--------------+----------+ | audit_detail | CREATE TABLE `audit_detail` ( `detailid` int(10) unsigned NOT NULL auto_increment, `contid` int(10) unsigned default '0', `locationid` int(10) unsigned default '0', `contractid` int(10) unsigned default '0', `itemid` int(10) unsigned default '0', `itemtype` tinyint(1) unsigned default '0', `itemname` varchar(255) default '', `itemexp` int(10) unsigned default '0', `itemclass` varchar(20) default NULL, PRIMARY KEY (`detailid`), KEY `itemid` (`itemid`), KEY `locationid` (`locationid`), KEY `itemtype` (`itemtype`), KEY `contid` (`contid`), KEY `itemexp` (`itemexp`), KEY `contractid` (`contractid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------------+-------------+ 1 row in set (0.03 sec) Here is a chunk of data representing the data found in this table (it has over 100,000 rows) mysql> select * from audit_detail where detailid > 191209; +----------+--------+------------+------------+--------+----------+-------------------------------+------------+--------------+ | detailid | contid | locationid | contractid | itemid | itemtype | itemname | itemexp | itemclass | +----------+--------+------------+------------+--------+----------+-------------------------------+------------+--------------+ | 191210 | 75491 | 0 | 0 | 0 | 0 | Missing resume | 0 | resneeded | | 191211 | 75491 | 0 | 0 | 75491 | 3 | Checklist builder checklist | 4294967295 | skillsneeded | | 191212 | 75491 | 0 | 0 | 1682 | 6 | Online Employment application | 4294967295 | appneeded | | 191218 | 75498 | 0 | 0 | 0 | 0 | Missing reference | 0 | refneeded | | 191219 | 75498 | 0 | 0 | 0 | 0 | Missing reference | 0 | refneeded | | 191220 | 75498 | 0 | 0 | 483 | 2 | Resume builder resume | 4294967295 | resneeded | | 191221 | 75498 | 0 | 0 | 75498 | 3 | Checklist builder checklist | 4294967295 | skillsneeded | | 191222 | 75498 | 0 | 0 | 1694 | 6 | Online Employment application | 4294967295 | appneeded | +----------+--------+------------+------------+--------+----------+-------------------------------+------------+--------------+ 8 rows in set (0.00 sec) ----------------------------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ on mysql destination I will gather the machine information: mysql> select version(); +------------+ | version() | +------------+ | 5.0.26-log | +------------+ 1 row in set (0.00 sec) --------------------------------------------------------------------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ now I go to the import tool: 1) select the only option available, direct migration, and hit next 2) select database system "mysql server" then select the 4.1 database connection, logging in as root, then hit next 3) select database system "mysql server" then select the 5.0 database connection, logging in as root, then hit next 4) the "tasks to execute" screen will say "execution completed successfully" so I hit next 5) on the "source schemata" screen I select "import_data" and hit next 6) the "reverse engineering" screen reads "execution completed successfully" so I hit next 7) on the object type screen I will check "objects of type Mysql table" and then select "import_data.dice_copy" and "import_data.test" I will then hit next 8) I will leave the migration method to "default" and then hit next 9) on the tasks to execute screen, it will read "execution completed successfully" so i will hit next 10) on the next screen it will read "no mapping problems found" so I will hit next 11) on the database object creation parameters I will select "create object online" and hit next 12) on the next screen it reads that I was successful so I hit next 13) on the next screen it reads "no creation problems found" so i hit next 14) on the bulk transfer settings I select transfer data online and hit next 15) on the tasks to execute screen it reads "execution completed successfully" and the log outputs this showing that it indeed found the rows in the 4.1 database: -------------------------------------------------------------------------------------------------------------------------- Starting online data bulk transfer ... Initializing source JDBC driver ... Initializing JDBC driver ... Driver class MySQL JDBC Driver 5.0 Opening connection ... Connection jdbc:mysql://10.10.1.10:3306/?user=root&password=something&useServerPrepStmts=false&characterEncoding=UTF-8 Initializing target JDBC driver ... Initializing JDBC driver ... Driver class MySQL JDBC Driver 5.0 Opening connection ... Connection jdbc:mysql://10.10.1.15:3306/?user=root&password=something&useServerPrepStmts=false&characterEncoding=UTF-8 Processing schema import_data ... Getting the number of rows of table dice_copy SELECT count(*) AS total_num FROM `import_data`.`dice_copy` Transfering data from table dice_copy (65119/65119 rows) SELECT `link_id`, `rip_date`, `cURL_link`, `job`, `fname`, `lname`, `company`, `addy_1`, `addy_2`, `city`, `state`, `zip`, `phone_area`, `phone_prefix`, `phone_suffix`, `fax_area`, `fax_prefix`, `fax_suffix`, `email`, `web`, `visited` FROM `import_data`.`dice_copy` Transfering data from table test (8/8 rows) SELECT `testid`, `testtinyint` FROM `import_data`.`test` --------------------------------------------------------------------------------------------------------------- 16) on the last screen here is the log: notice that the small table with a tinyint(1) unsigned did not transfer, even though it was able to read 8 rows above: Data bulk transfer finished -------------------------------------------------------------------------------- -- MySQL Migration Toolkit Report -- -- -- -- Title: Summary Of The Migration Process -- -- Date: 2006-12-01 12:16 -- -------------------------------------------------------------------------------- 1. Schema Migration ------------------- Number of migrated schemata: 1 Schema Name: import_data - Tables: 2 - Views: 0 - Routines: 0 - Routine Groups: 0 - Synonyms: 0 - Structured Types: 0 - Sequences: 0 Details: - Tables `import_data`.`dice_copy` ------------------------- `import_data`.`test` -------------------- - Views - Routines - Routine Groups - Synonyms - Structured Types - Sequences 2. Data Bulk Transfer --------------------- `import_data`.`dice_copy` ------------------------- 65119 row(s) transfered. `import_data`.`test` -------------------- Incorrect integer value: '' for column 'testtinyint' at row 1 0 row(s) transfered. End of report. -------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------- 17) now I go into the 5.0 database and see what is there: mysql> use import_data Database changed mysql> select version(); +------------+ | version() | +------------+ | 5.0.26-log | +------------+ 1 row in set (0.00 sec) mysql> show create table dice_copy; +-----------+-----------------+ | Table | Create Table +-----------+-----------+ | dice_copy | CREATE TABLE `dice_copy` ( `link_id` int(10) unsigned NOT NULL auto_increment, `rip_date` timestamp NULL default CURRENT_TIMESTAMP, `cURL_link` varchar(255) NOT NULL, `job` blob, `fname` varchar(255) default NULL, `lname` varchar(255) default NULL, `company` varchar(255) default NULL, `addy_1` varchar(255) default NULL, `addy_2` varchar(255) default NULL, `city` varchar(255) default NULL, `state` varchar(50) default NULL, `zip` varchar(255) default NULL, `phone_area` varchar(255) default NULL, `phone_prefix` varchar(255) default NULL, `phone_suffix` varchar(255) default NULL, `fax_area` varchar(255) default NULL, `fax_prefix` varchar(255) default NULL, `fax_suffix` varchar(255) default NULL, `email` varchar(255) default NULL, `web` varchar(255) default NULL, `visited` varchar(255) default NULL, PRIMARY KEY (`link_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-----------+---------+ 1 row in set (0.00 sec) (i selected data from dice_copy, I wont post it here since it is a lot of long data, but It did copy correctly) now for the other table: mysql> show create table test; +-------+-------------------------------------+ | Table | Create Table | +-------+-------------------------------------+ | test | CREATE TABLE `test` ( `testid` int(10) unsigned NOT NULL auto_increment, `testtinyint` tinyint(1) unsigned default '0', PRIMARY KEY (`testid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------+ 1 row in set (0.00 sec) as you can see the table was created properly mysql> select * from test; Empty set (0.00 sec) -------------------------------------------------------------------------------------------------- If i repeat these steps with any tinyint(1) unsigned field in the data, then it will not work. If I repeat these steps with even a tinyint above 1 it works as in the example: mysql> show create table calc_results; +--------------+-------------+ | Table | Create Table +--------------+-----------------+ | calc_results | CREATE TABLE `calc_results` ( `contractid` int(10) unsigned NOT NULL default '0', `cst_hr` float(8,2) unsigned default '0.00', `cadd_hr` float(8,2) unsigned default '0.00', `bill_hr` float(8,2) unsigned default '0.00', `badd_hr` float(8,2) unsigned default '0.00', `stdshifts` tinyint(2) unsigned default '0', `stdhrs` tinyint(2) unsigned default '0', `yearhours` int(6) unsigned default '0', `gmpercent` float(5,2) default '0.00', `gmdollars` float(5,2) default '0.00', UNIQUE KEY `contractid` (`contractid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------------+-----------------+ 1 row in set (0.00 sec) this table works and it has a tinyint, but its tinyint(2)