MySQL Bugs: #24746: Error migrating tables with tinyint

Bug #24746	Error migrating tables with tinyint
Submitted:	1 Dec 2006 9:12	Modified:	23 Feb 2007 17:21
Reporter:	Thomas Fante	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.1.6	OS:	Windows (Windows Server 2003 Build 3790)
Assigned to:		CPU Architecture:	Any

Description:
using the mysql migration toolkit to migrate a database in community edition 4.1.16 standard to 5.0.26-r1 using migration toolkit 1.16 for windows (the latest stable version downloaded today)

I can go through the entire migration until I get to the bulk data transfer screen. It will even creates the tables on the 5.0 database correctly.
It then runs the transfer, and reports errors. Any table where there is a tinyint(1) unsigned field in the 4.1.16 database, the entire table will not be transferred. Any table that does not contain a tinyint(1) will be transferred entirely.

an error:
"Incorrect Integer value '' for column 'columnname' at row 1"
"0 row(s) transferred"
will appear for each table that contains a tinyint(1) unsigned in it.

this seems similar to bug 14529 that was closed long ago, except that in this case the entire table will not migrate, instead of it migrating the data wrong.

How to repeat:
1)On the mysql 4.1 server create a table with a tinyint(1) column and fill with 1 or more rows of data.

2) attempt to use the toolkit to migrate the data, either by selecting all tables or even only the table with a tinyint(1) unsigned.

3) go through all migration steps to the bulk transfer. After transferring you will see the error for each table that has a tinyint(1) unsigned in it

Suggested fix:
Have the migration toolkit handle tinyint migrations more gracefully, either detecting them and suggesting a new field type that will handle them, or transferring them correctly. Since I see that the tables were created in the 5.0 database with tinyint(1) unsigned columns, just the data was not transferred, I would assume that migration of these fields as they are, is possible.

Changed version that I typed from 1.16 to 1.1.6

Thank you for the bug report. Could you please provide a dump file for
4.1.XX. I was unable to repeat with the very simple example below:

mysql> create database dbr;
Query OK, 1 row affected (0.05 sec)

mysql> use dbr;
Database changed
mysql> create table ty (id tinyint unsigned);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into ty values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.1.21-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql>
---------------------------------------------------
C:\dbs\5.0\bin>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use dbr
Database changed
mysql> show create table ty\G
*************************** 1. row ***************************
       Table: ty
Create Table: CREATE TABLE `ty` (
  `id` tinyint(3) unsigned default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from ty;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

Thanks in advance.

More in depth steps and details to re-create the problem

Attachment: more_details.txt (text/plain), 15.91 KiB.

attached is a file with the detailed table information and what happens. If you still need a dump I will get it, cant at the moment as I am remote, and the source system is being used. I believe that the information will show the problem. It only happens with tinyint(1) unsigned fileds, as you can see in the file I attached.

notice that your test case was with a tinyint(3) unsigned, not a tinyint(1) unsigned.

Thank you for the bug report. Indeed it fails with tinyint(1) i.e:

C:\dbs\4.1\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.21-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database hh;
Query OK, 1 row affected (0.09 sec)

mysql> use hh
Database changed
mysql> create table bb (id tinyint(1) unsigned);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into bb values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

2. Data Bulk Transfer
---------------------

      `hh`.`bb`
      ---------
          Incorrect integer value: '' for column 'id' at row 1
          0 row(s) transfered.

End of report.
--------------------------------------------------------------------------------

I tried to download the latest version (1.1.8) and this bug still exists. Is there any workaround for it?

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html