MySQL Bugs: #73333: mysqldbbcopy Problem inserting data. when copy to remote server

Bug #73333	mysqldbbcopy Problem inserting data. when copy to remote server
Submitted:	20 Jul 2014 11:52	Modified:	24 May 2016 7:59
Reporter:	Gidi Kern	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.5.6	OS:	Linux (Redhat 6.4)
Assigned to:		CPU Architecture:	Any
Tags:	mysql 5.6.12 MySQL Utilities mysqldbcopy version 1.4.3

Description:
When using mysqldbcopy to remote server getting:
"Problem inserting data. Error = Query failed. 1048 (23000): Column 'label' cannot be null"
When using the same mysqldbcopy command to the local server the copy completed successfully.
Both servers are identical with the same mysql configuration.

The 'label' column is varchar(1000) and include no null values.

mysqldbcopy --source=$source_user:$source_pass@$source_server:$source_port --destination=$dest_user:$dest_pass@$dest_server:$dest_port $source_db:$dest_db_prefix$start_from_tenant;

How to repeat:
Just to mysqldbcopy to remote destination.

I tried using both:
MySQL Utilities mysqldbcopy version 1.3.6
MySQL Utilities mysqldbcopy version 1.4.3

Thank you for the report.
Please provide the complete repeatable test case ( schema, exact source and target version used) and result of below statement from source and target environments.

show global variables like 'sql_mode';

Thanks,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Here I can procedure the bug:
I have a table  `bank_order_db_00`.`t_business_relation_2`
which only have one line data:
mysql> desc bank_order_db_00.t_business_relation_2;
+-----------------+--------------+------+-----+---------------------+-------+
| Field           | Type         | Null | Key | Default             | Extra |
+-----------------+--------------+------+-----+---------------------+-------+
| Fbank_type      | smallint(6)  | NO   | PRI | 0                   |       |
| Fbank_list      | varchar(32)  | NO   | PRI |                     |       |
| Fspid           | varchar(16)  | NO   |     |                     |       |
| Fbusiness_id    | varchar(32)  | NO   | PRI |                     |       |
| Fbusiness_type  | smallint(6)  | NO   | PRI | 1                   |       |
| Flstate         | smallint(6)  | NO   |     | 2                   |       |
| Frequest_packet | text         | NO   |     | NULL                |       |
| Fcreate_time    | datetime     | YES  | MUL | 1970-01-01 00:00:00 |       |
| Fstandby1       | int(11)      | YES  |     | NULL                |       |
| Fstandby2       | int(11)      | YES  |     | NULL                |       |
| Fstandby3       | datetime     | YES  |     | 1970-01-01 00:00:00 |       |
| Fstandby4       | varchar(64)  | YES  |     | NULL                |       |
| Fstandby5       | varchar(128) | YES  |     | NULL                |       |
+-----------------+--------------+------+-----+---------------------+-------+
13 rows in set (0.00 sec)

mysql> select * from bank_order_db_00.t_business_relation_2 \G
*************************** 1. row ***************************
     Fbank_type: 1032
     Fbank_list: 300911085000200
          Fspid: 1000000000
   Fbusiness_id: 100200911080000007666
 Fbusiness_type: 3
        Flstate: 2
Frequest_packet: uin=65070043&uname=???&creditcard_id=************7562&creditcard_id_md5=157cc65dae6d560ef47d03adc9b35bd3&fee_type=1&banklist_id=1032100200911080000007666&token=80405eb76220884aebff849a4528628b&draw_id=202200911080000000350&amount=1&bank_type=1020&bank_name=???????&area=2&city=21&save_flag=0&time_stamp=1257647221&history_flag=1&client_ip=10.6.35.38
   Fcreate_time: 2009-11-08 10:36:14
      Fstandby1: NULL
      Fstandby2: NULL
      Fstandby3: 1970-01-01 00:00:00
      Fstandby4: NULL
      Fstandby5: NUL

I use pdb to debug mysql-utilities follow the function's data as following:

And when I copy the insert string to mysql console It's Error like this:

mysql> INSERT INTO `bank_order_db_00`.`t_business_relation_2` VALUES  (1032, '300911085000200', '1000000000', '100200911080000007666', 3, 2, NULL, '2009-11-08 10:36:14', NULL, NULL, '1970-01-01 00:00:00', NULL, NULL);
ERROR 1048 (23000): Column 'Frequest_packet' cannot be null

And I fix this bug like this in __init__ of file table.py
change
self._insert = "INSERT INTO %s.%s VALUES " 
 to 
self._insert = "INSERT IGNORE INTO %s.%s VALUES "  

and in make_bulk_insert of file table.py:
insert_str = "INSERT  INTO {0}.{1} ({2}) VALUES ".format(
to 
insert_str = "INSERT IGNORE INTO {0}.{1} ({2}) VALUES ".format(

pdb output

Attachment: table.py(854)_bulk_insert.txt (text/plain), 4.99 KiB.

Here I can procedure the bug:
I have a table  `bank_order_db_00`.`t_business_relation_2`
which only have one line data:
mysql> desc bank_order_db_00.t_business_relation_2;
+-----------------+--------------+------+-----+---------------------+-------+
| Field           | Type         | Null | Key | Default             | Extra |
+-----------------+--------------+------+-----+---------------------+-------+
| Fbank_type      | smallint(6)  | NO   | PRI | 0                   |       |
| Fbank_list      | varchar(32)  | NO   | PRI |                     |       |
| Fspid           | varchar(16)  | NO   |     |                     |       |
| Fbusiness_id    | varchar(32)  | NO   | PRI |                     |       |
| Fbusiness_type  | smallint(6)  | NO   | PRI | 1                   |       |
| Flstate         | smallint(6)  | NO   |     | 2                   |       |
| Frequest_packet | text         | NO   |     | NULL                |       |
| Fcreate_time    | datetime     | YES  | MUL | 1970-01-01 00:00:00 |       |
| Fstandby1       | int(11)      | YES  |     | NULL                |       |
| Fstandby2       | int(11)      | YES  |     | NULL                |       |
| Fstandby3       | datetime     | YES  |     | 1970-01-01 00:00:00 |       |
| Fstandby4       | varchar(64)  | YES  |     | NULL                |       |
| Fstandby5       | varchar(128) | YES  |     | NULL                |       |
+-----------------+--------------+------+-----+---------------------+-------+
13 rows in set (0.00 sec)

mysql> select * from bank_order_db_00.t_business_relation_2 \G
*************************** 1. row ***************************
     Fbank_type: 1032
     Fbank_list: 300911085000200
          Fspid: 1000000000
   Fbusiness_id: 100200911080000007666
 Fbusiness_type: 3
        Flstate: 2
Frequest_packet: uin=65070043&uname=???&creditcard_id=************7562&creditcard_id_md5=157cc65dae6d560ef47d03adc9b35bd3&fee_type=1&banklist_id=1032100200911080000007666&token=80405eb76220884aebff849a4528628b&draw_id=202200911080000000350&amount=1&bank_type=1020&bank_name=???????&area=2&city=21&save_flag=0&time_stamp=1257647221&history_flag=1&client_ip=10.6.35.38
   Fcreate_time: 2009-11-08 10:36:14
      Fstandby1: NULL
      Fstandby2: NULL
      Fstandby3: 1970-01-01 00:00:00
      Fstandby4: NULL
      Fstandby5: NUL

I use pdb to debug mysql-utilities follow the function's data as following:

http://bugs.mysql.com/file.php?id=23086&bug_id=73333

And when I copy the insert string to mysql console It's Error like this:

mysql> INSERT INTO `bank_order_db_00`.`t_business_relation_2` VALUES  (1032, '300911085000200', '1000000000', '100200911080000007666', 3, 2, NULL, '2009-11-08 10:36:14', NULL, NULL, '1970-01-01 00:00:00', NULL, NULL);
ERROR 1048 (23000): Column 'Frequest_packet' cannot be null

And I fix this bug like this in __init__ of file table.py
change
self._insert = "INSERT INTO %s.%s VALUES " 
 to 
self._insert = "INSERT IGNORE INTO %s.%s VALUES "  

and in make_bulk_insert of file table.py:
insert_str = "INSERT  INTO {0}.{1} ({2}) VALUES ".format(
to 
insert_str = "INSERT IGNORE INTO {0}.{1} ({2}) VALUES ".format(

In table.py function:
     def get_column_metadata(self, columns=None):
                elif 'blob' in col_type or 'text'in col_type:
                    self.blob_columns.append(col)
It show that col_type is text.At first "insert " it a null, After "update" the col.
But the text's "Null" may be not null

Now use following code can finally fix the bug:
 get_column_metadata(self, columns=None):

       if columns is not None:
            for col in range(0, stop):
                if is_quoted_with_backticks(columns[col][0]):
                    self.column_names.append(
                        remove_backtick_quoting(columns[col][0]))
                    self.q_column_names.append(columns[col][0])
                else:
                    self.column_names.append(columns[col][0])
                    self.q_column_names.append(
                        quote_with_backticks(columns[col][0]))
                col_type = columns[col][1].lower()
                is_null = columns[col][2].lower()
                if ('char' in col_type or 'enum' in col_type
                        or 'set' in col_type or 'binary' in col_type or ('text' in col_type and is_null =='no')):
                    self.text_columns.append(col)
                    col_format_values[col] = "'%s'"
                elif (is_null == 'yes') and ('blob' in col_type or 'text'in col_type):
                    self.blob_columns.append(col)
                    col_format_values[col] = "%s"
                elif "date" in col_type or "time" in col_type:
                    col_format_values[col] = "'%s'"
                else:
                    col_format_values[col] = "%s"

Thank you for the feedback.
Most likely duplicate of Bug #81553