Bug #80616 mysqldbcompare: object of type 'NoneType' has no len()
Submitted: 4 Mar 2016 14:24 Modified: 8 Mar 2016 22:55
Reporter: Hayden Ball Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.3.6 OS:CentOS (7)
Assigned to: CPU Architecture:Any
Tags: mysqldbcompare

[4 Mar 2016 14:24] Hayden Ball
Description:
mysqldbcompare crashes with the following error when outputting the diff in SQL format.

mysqldbcompare does not crash when using the text format with the same pair of databases.

The crash seems to be triggered by a column having a value in one table, and being "NULL" in another.

What I did:

mysqldbcompare --changes-for=server2 --run-all-tests --skip-diff --server1=user:password@host1 --server2=user:password@host2 --difftype=sql DB:DB

What I expected:

A list of SQL statements to make DB on server2 the same as DB on server1.

What actually happened:

sql_transform.py:225:build_set_clauses:TypeError: object of type 'NoneType' has no len()

Traceback (most recent call last):
  File "/bin/mysqldbcompare", line 250, in <module>
    db1, db2, options)
  File "/usr/lib/python2.7/site-packages/mysql/utilities/command/dbcompare.py", line 480, in database_compare
    reporter, options)
  File "/usr/lib/python2.7/site-packages/mysql/utilities/command/dbcompare.py", line 318, in _check_data_consistency
    obj2, obj1, options)
  File "/usr/lib/python2.7/site-packages/mysql/utilities/common/dbcompare.py", line 1009, in check_consistency
    (tbl1_rows, tbl2_rows)))
  File "/usr/lib/python2.7/site-packages/mysql/utilities/common/sql_transform.py", line 278, in transform_data
    sql_str += " %s" % build_set_clauses(source, src_cols, row1, row2)
  File "/usr/lib/python2.7/site-packages/mysql/utilities/common/sql_transform.py", line 225, in build_set_clauses
    if len(src_row[col_idx]) == 0 \
TypeError: object of type 'NoneType' has no len()

Please see attached abrt report.

How to repeat:
Set up two databases with identical schemas but differing contents. At least one of the differing rows must have a NULL value.

Suggested fix:
Adjusting `sql_transform.py` as follows appears to mitigate the problem.
This may have other side-effects.

    224             # Check for NULL for non-text fields that have no value in new row
    225             if not src_row[col_idx]:
    226                set_str += "%s = %s" % (table_cols[col_idx], "NULL")
    227             elif len(src_row[col_idx]) == 0 \
    228                and not col_metadata[col_idx]['is_text']:
    229                 set_str += "%s = %s" % (table_cols[col_idx], "NULL")
    230             else:
    231                 set_str += "%s = %s" % (table_cols[col_idx],
    232                                         to_sql(src_row[col_idx]))
[4 Mar 2016 14:24] Hayden Ball
abrt report

Attachment: [abrt] full crash report.eml (application/x-extension-eml, text), 3.54 KiB.

[8 Mar 2016 7:40] Umesh Shastry
Hello Hayden,

Thank you for the report.
I'm not seeing this issue with 1.5.6 build on OL6 with dummy schema.
Could you please try with latest MySQL utilities build i.e 1.5.6 and check if this issue still exists at your end. If you are still seeing this issue then I would request you to provide exact schema/sub-set of data to reproduce this issue at our end.

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Umesh
[8 Mar 2016 7:42] Umesh Shastry
-- 1.5.6

mysql>
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed
mysql> create table t1(id int not null auto_increment primary key, txt char(10) default null);
insert into t1(txt) values(NULL),(NULL),(NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(txt) values(NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> use db2;
Database changed
mysql> create table t1(id int not null auto_increment primary key, txt char(10) default null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(txt) values('MySQL'),(''),('MySQL');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  \! mysqldbcompare --changes-for=server2 --run-all-tests --skip-diff --server1=root:'***'@localhost --server2=root:'***'@localhost --difftype=sql db1:db2
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases db1 on server1 and db2 on server2
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     t1                                      SKIP    pass    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  FAIL
#
# Transformation for --changes-for=server2:
#

# Data differences found among rows:
UPDATE `db2`.`t1` SET `txt` = NULL WHERE `id` = '2';
UPDATE `db2`.`t1` SET `txt` = NULL WHERE `id` = '1';
UPDATE `db2`.`t1` SET `txt` = NULL WHERE `id` = '3';

# Database consistency check failed.
#
# ...done

mysql> \! rpm -qa|grep mysql-utilities
mysql-utilities-extra-1.5.6-1.el6.noarch
mysql-utilities-1.5.6-1.el6.noarch
mysql>
[8 Mar 2016 8:30] Hayden Ball
Thanks for investigating.

Looking through the changes to sql_transform.py since 1.3.6 and 1.5.6, it looks like it *should* be fixed.

I'll attempt to deploy the new version later today, and check that this does indeed solve the problem.
[8 Mar 2016 22:55] Hayden Ball
I can confirm this has been fixed in 1.5.6.

Please accept my apologies for wasting your time.
[9 Mar 2016 4:14] Umesh Shastry
Thank you Hayden for confirming!