MySQL Bugs: #78084: mysqldbbcopy copy blob data when some field's value is null

Bug #78084	mysqldbbcopy copy blob data when some field's value is null
Submitted:	15 Aug 2015 17:25	Modified:	4 Aug 2016 20:00
Reporter:	zhan peng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	Release-1.5.4, 1.5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldbcopy

Description:
mysqldbbcopy copy blob data when some field's value is null

mysqldbbcopy copy some table have text columns, when make_bulk_insert ,it will  _build_update_blob the update string .   
But  another's field value is null, it't will generate the update string like below:

 UPDATE `bank_order_db_00`.`t_business_relation_2` SET `Frequest` = 'uin=65070043&'  WHERE `Fbank_type` = '1032' AND `Fbank_list` = '300911085000200' AND `Fspid` = '1000000000' AND AND `Fstandby1` = NULL  AND `Fstandby2` = NULL  ;

the where can't match nothing because of the mysql use "IS" not "=" when equal "NULL"

How to repeat:
# Table, Create Table
t_business_relation_2, CREATE TABLE `t_business_relation_2` (
  `Fbank_type` smallint(6) NOT NULL DEFAULT '0',
  `Fbank_list` varchar(32) NOT NULL DEFAULT '',
  `Fspid` varchar(16) NOT NULL DEFAULT '',
  `Frequest` text NOT NULL,
  `Fstandby1` int(11) DEFAULT NULL,
  `Fstandby2` int(11) DEFAULT NULL,
  PRIMARY KEY (`Fbank_type`,`Fbank_list`),
  KEY `XPK1t_order_relation_2` (`Fcreate_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Insert row Data:

'1032', '300911085000200', '1000000000',  'uin=65070043&', NULL, NULL, 

Suggested fix:
modify the     function
def _build_update_blob(self, row, new_db, name): 580 line 
                    where_values.append("{0} = {1}".format(col_name, value))
to 

                if(value == 'NULL'):
                    where_values.append("{0} IS {1}".format(col_name, value))
                else:
                    where_values.append("{0} = {1}".format(col_name, value))

Hi Zhan,

Thank you for the report.
Could you please provide exact mysqldbcopy command used, and repeatable schema(provided table is broken with missing column etc) to reproduce this issue at our end?

Thanks,
Umesh

mysql> show create table bank_order_db_00.t_business_relation_2\G
*************************** 1. row ***************************
       Table: t_business_relation_2
Create Table: CREATE TABLE `t_business_relation_2` (
  `Fbank_type` smallint(6) NOT NULL DEFAULT '0',
  `Fbank_list` varchar(32) NOT NULL DEFAULT '',
  `Fspid` varchar(16) NOT NULL DEFAULT '',
  `Fbusiness_id` varchar(32) NOT NULL DEFAULT '',
  `Fbusiness_type` smallint(6) NOT NULL DEFAULT '1',
  `Flstate` smallint(6) NOT NULL DEFAULT '2',
  `Frequest_packet` text NOT NULL,
  `Fcreate_time` datetime DEFAULT '1970-01-01 00:00:00',
  `Fstandby1` int(11) DEFAULT NULL,
  `Fstandby2` int(11) DEFAULT NULL,
  `Fstandby3` datetime DEFAULT '1970-01-01 00:00:00',
  `Fstandby4` varchar(64) DEFAULT NULL,
  `Fstandby5` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`Fbank_type`,`Fbank_list`,`Fbusiness_type`,`Fbusiness_id`),
  KEY `XPK1t_order_relation_2` (`Fcreate_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

data:
mysql> select * from bank_order_db_00.t_business_relation_2 limit 1\G
*************************** 1. row ***************************
     Fbank_type: 103
     Fbank_list: 30091108500020
          Fspid: 10000000
   Fbusiness_id: 10020091108000000725
 Fbusiness_type: 3
        Flstate: 2
Frequest_packet: uin='"6507043&uname=???&creditcard_id=***********
   Fcreate_time: 2009-11-08 10:36:14
      Fstandby1: NULL
      Fstandby2: NULL
      Fstandby3: 1970-01-01 00:00:00
      Fstandby4: NULL
      Fstandby5: NULL

the 'Frequest_packet'  field can't copy correct

copy  commands is 
-source=root:pwd@192.168.1.103:3306 --destination=root:pwd @192.168.1.104:3306      bank_order_db_00   --drop-first --multiprocess=1 --skip=grants,triggers,procedures,functions,views,events

Thank you for the feedback.
Observed issues with Frequest_packet column.

Posted by developer:
 
Fixed as of the upcoming MySQL Utilities 1.6.4 release, and here's the changelog entry:

Corrects a problem found when copying tables that contained NOT NULL blob
fields. When blob fields are marked NOT NULL, the copy process would fail
with an SQL error when inserting data into the target table.

Copying NOT NULL blob fields is not supported. The utility
now checks all tables in the list of databases for NOT NULL blob fields.
If any are found, the database and table names are printed along with an
error message. 

A workaround is to drop the NOT NULL
definition on blob fields before the copy and add it after the copy
completes.

Thank you for the bug report.

I now cannot copy database with blog columns set to non-null. I've tried excluding these tables using --exclude=, but this is ignored by this check. Given that I cannot change the source database, is there any other work-around?