[GH-ISSUE #26] Order of Fields while Calculating MD5 #15

New issue

Open

opened 2026-02-25 21:33:02 +03:00 by kerem · 1 comment

kerem commented

2026-02-25 21:33:02 +03:00

Owner

Originally created by @djshen-ponddy on GitHub (Aug 29, 2016).
Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/26

In DBManager.php, DBDiff uses SHOW COLUMNS FROM $table to get columns.
Considering the following databases,

DB1:

CREATE TABLE IF NOT EXISTS `aa` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `pass` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `aa` (`id`, `name`, `pass`) VALUES (1, 'aa', 'zz');

DB2:

CREATE TABLE IF NOT EXISTS `aa` (
  `id` int(11) NOT NULL,
  `pass` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `aa` (`id`, `name`, `pass`) VALUES (1, 'aa', 'zz');

The columns of DB1 is (id, name, pass), and the columns of DB2 is (id, pass, name).
Therefore the MD5 values are MD5('1aazz') for DB1 and MD5('1zzaa').

After calculating diff data column by column, DBDiff will find that the two records are equal.
But DBDiff will still generate SQL

SQL_UP = u"""
UPDATE `aa` SET  WHERE `id` = '1';
"""
SQL_DOWN = u"""
UPDATE `aa` SET  WHERE `id` = '1';
"""

which is not a valid syntax.

I think you can sort the columns before concatenating the columns.
In getChangeDiff in LocalTableData.php, add

        asort($columns1);
        asort($columns2);
        $columnsA   = implode(',', $wrapCast($columns1, 'a'));
        $columnsB   = implode(',', $wrapCast($columns2, 'b'));

Update:
And when we insert data, order of fields should explicitly specified.

Originally created by @djshen-ponddy on GitHub (Aug 29, 2016). Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/26 In DBManager.php, DBDiff uses `SHOW COLUMNS FROM $table` to get columns. Considering the following databases, DB1: ``` sql CREATE TABLE IF NOT EXISTS `aa` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `pass` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `aa` (`id`, `name`, `pass`) VALUES (1, 'aa', 'zz'); ``` DB2: ``` sql CREATE TABLE IF NOT EXISTS `aa` ( `id` int(11) NOT NULL, `pass` varchar(255) DEFAULT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `aa` (`id`, `name`, `pass`) VALUES (1, 'aa', 'zz'); ``` The columns of DB1 is (`id`, `name`, `pass`), and the columns of DB2 is (`id`, `pass`, `name`). Therefore the MD5 values are MD5('1aazz') for DB1 and MD5('1zzaa'). After calculating diff data column by column, DBDiff will find that the two records are equal. But DBDiff will still generate SQL ``` SQL_UP = u""" UPDATE `aa` SET WHERE `id` = '1'; """ SQL_DOWN = u""" UPDATE `aa` SET WHERE `id` = '1'; """ ``` which is not a valid syntax. I think you can sort the columns before concatenating the columns. In getChangeDiff in LocalTableData.php, add ``` php asort($columns1); asort($columns2); $columnsA = implode(',', $wrapCast($columns1, 'a')); $columnsB = implode(',', $wrapCast($columns2, 'b')); ``` Update: And when we insert data, order of fields should explicitly specified.

kerem commented

2026-02-25 21:33:02 +03:00

Author

Owner

@kylefix commented on GitHub (Mar 7, 2018):

Any update on this, I have the same issue? @djshen-ponddy

@kylefix commented on GitHub (Mar 7, 2018): Any update on this, I have the same issue? @djshen-ponddy