DBDiff says falsely tables are identical

[Pachat]

Depending on the presence of a third field DBDiff detects or not the difference in the second field.

Step to reproduce
1°) Create the a11_elements and a12_elements tables in two databases
2°) In database2, change a value
3°) Run DBDiff

1°)

CREATE TABLE a11_elements (
id int(6) NOT NULL,
show_in_list_summary int(1) DEFAULT NULL,
filter_exact_match int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO a11_elements (id, show_in_list_summary, filter_exact_match) VALUES(646, 1, NULL);
ALTER TABLE a11_elements ADD PRIMARY KEY (id);

CREATE TABLE a12_elements (
id int(6) NOT NULL,
show_in_list_summary int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO a12_elements (id, show_in_list_summary) VALUES (646, 1);
ALTER TABLE a12_elements ADD PRIMARY KEY (id);

2°)

database2.a11_elements.show_in_list_summary=0
database2.a12_elements.show_in_list_summary=0

3°)

php dbdiff --server1=root:root@localhost:3306 --type=data --include=all server1.database1.a11_elements:server1.database2.a11_elements --output=dbdiff_database1to2.sql

Results :

Now calculating data diff for table a11_elements
Identical resources
Completed

php dbdiff --server1=root:root@localhost:3306 --type=data --include=all server1.database1.a12_elements:server1.database2.a12_elements --output=dbdiff_database1to2.sql

Results :

Now calculating data diff for table a12_elements
Now generating UP migration
Now generating DOWN migration
Writing migration file to dbdiff_database1to2.sql
Completed

---------- UP ----------

UPDATE a12_elements SET show_in_list_summary = '0' WHERE id = '646';

---------- DOWN ----------

UPDATE a12_elements SET show_in_list_summary = '1' WHERE id = '646';

Conclusion: With the same context for the first two fields (646 on both databases and respectively 1 / 0 on each database), only when deleting the third field DBDiff detects the difference in the second field.

[djshen-ponddy]

What if filter_exact_match is not NULL ?

[Pachat]

When filter_exact_match is not NULL, the result is correct.

Step to reproduce

Mydatabase1
CREATE TABLE a13_elements (
id int(6) NOT NULL,
show_in_list_summary int(1) DEFAULT NULL,
filter_exact_match int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO a13_elements (id, show_in_list_summary, filter_exact_match) VALUES(646, 1, 0);
ALTER TABLE a13_elements ADD PRIMARY KEY (id);

Mydatabase2
CREATE TABLE a13_elements (
id int(6) NOT NULL,
show_in_list_summary int(1) DEFAULT NULL,
filter_exact_match int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO a13_elements (id, show_in_list_summary, filter_exact_match) VALUES(646, 0, 0);
ALTER TABLE a13_elements ADD PRIMARY KEY (id);

php dbdiff --server1=root:root@localhost:3306 --type=data --include=all server1.mydatabase1.a13_elements:server1.mydatabase2.a13_elements --output=dbdiff_13.sql

Now calculating data diff for table a13_elements
Now generating UP migration
Now generating DOWN migration
Writing migration file to dbdiff_13.sql
Completed

So, the result that is still incorrect is for a11_elements (see first post) when
filter_exact_match is NULL
(which may happen)

Does this gives a direction for the correction of the bug?

[djshen-ponddy]

I think this bug is the same as #9 .

In getChangeDiff` of src/DB/Data/LocalTableData.php, I replace NULL value by ''.

        $wrapCast = function($arr, $p) {
            return array_map(function($el) use ($p) {
                return "CAST(IFNULL(`{$p}`.`{$el}`, '') AS CHAR CHARACTER SET utf8)";
            }, $arr);
        };

[Pachat]

@djshen-ponddy
That's an interesting proposal. However, it makes NULL and '' equivalent. Which might not be the case.
A NULL value by default, may become '' when the user decides to leave it blank.
NULL shows that the user did not update the value, '' shows that the user updated to blank.

We may have to use explicitly IS NULL functions to compare?

[djshen-ponddy]

So we have to replace NULL value and use ISNULL as a flag for each field, and then concatenate all fields and flags and calculate md5 hash?

[Pachat]

It looks like the culprit here is MD5 or any other PHP hash function. For example:

$str1 = '';
$str2 = NULL;
echo '
md5='.(md5($str1)===md5($str2)); // answers 1 (for TRUE)
echo '
strcmp='.strcmp($str1, $str2); // answers 0 (for TRUE, as 0 is strcmp's answer when TRUE)

I think that, indeed, we cannot avoid the use of ISNULL to get the difference

[jasdeepkhalsa]

Oh dear! We do need to use hashes in some way to make it easier to compare the data...

If hashes are the problem I wonder how GIT manages to do it! (Maybe we'll have to ask them for their secret)

[djshen-ponddy]

SELECT CONCAT("a", NULL) is NULL and SELECT MD5(NULL) is NULL too.
I think we can still use hashes to compare the data, but we need to handle NULL value to avoid the problem above.

MySQL CONCAT and MD5

[rekitae]

I had the same problem.
There is also a way to solve CONCAT_WS and COALESCE functions.

How COALESCE

$ WrapCast = function ($ arr, $ p) {
             return array_map (function ($ el) use ($ p) {
                 return "COALESCE (CAST (` {$ p} `.` {$ el} `AS CHAR CHARACTER SET utf8),'')";
             }, $ Arr);
         };

How CONCAT_WS

MD5(concat_ws('', $columnsA)) AS hash1,
MD5(concat_ws('', $columnsB)) AS hash2

[djshen-ponddy]

I found another problem about CONCAT.
The two records, (1, 'abc', 'de') and (1, 'abcde', ''), are both concatenated into '1abcde', so their MD5 hashes are identical.

[rekitae]

concat_ws seems to be a good alternative.
The example below

select concat_ws('|', 1, 'abc', 'de'), concat_ws('|', 1, 'abcde');
+--------------------------------+----------------------------+
| concat_ws('|', 1, 'abc', 'de') | concat_ws('|', 1, 'abcde') |
+--------------------------------+----------------------------+
| 1|abc|de                       | 1|abcde                    |
+--------------------------------+----------------------------+

[djshen-ponddy]

I think we need a special separator (magic number) to reduce the probability of occurrence of this problem.

select concat_ws('|', 1, 'abc', 'de|'), concat_ws('|', 1, 'abc|de', '');
+---------------------------------+---------------------------------+
| concat_ws('|', 1, 'abc', 'de|') | concat_ws('|', 1, 'abc|de', '') |
+---------------------------------+---------------------------------+
| 1|abc|de|                       | 1|abc|de|                       |
+---------------------------------+---------------------------------+

[isgroup-srl]

@djshen-ponddy you can add data (a space) to the string before calling md5() to avoid the issue md5() returning null if the data is null. This way you will not have any collision (eg: "NULL" and " " will not have the same MD5 as the string will become " " and " "). With concat_ws() you cannot reliably add a separator that is not in the data itself, btw using a random token of enough entropy will make collisions unlikely.