Skip to content

Order of Fields while Calculating MD5 #26

@djshen-ponddy

Description

@djshen-ponddy

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugfixedFixed but not yet released

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions