I have followed the instruction to install it but when trying to run it, it says that it doesn't recognize the command:

'php-mysql-diff' is not recognized as an internal or external command

anyone experienced the same issue?

php-mysql-diff is locked at an old version of symfony/console
This lock makes it incompatible with current versions of software such as phpstan/phpstan for example.
Also, the version of phpunit is deprecated.

Hi.

I'm using PHP MySQL Diff 1.2.1 (installed from composer).
I give the command these two schemas.

from.schema

CREATE TABLE post (
post_date date NOT NULL COMMENT 'Created At'
);

to.schema

CREATE TABLE post (
post_date date NOT NULL COMMENT 'Created'
);

It returns:

ALTER TABLE post
CHANGE COLUMN post_date post_date date NOT NULL COMMENT 'Created' FIRST;

but MySQL(Server version: 5.7.17) says:

#1138 - Invalid use of NULL value

Without 'FIRST', it worked.

ALTER TABLE post
CHANGE COLUMN post_date post_date date NOT NULL COMMENT 'Created';

When a column with a foreign key gets renamed the generated migrate-script won't work.

Cannot drop index 'FK_table_1_table_2': needed in a foreign key constraint

Here are the SQL files to reproduce the issue.
Database test_1

-- Host: 127.0.0.1    Database: test_1

DROP TABLE IF EXISTS `table_1`;
CREATE TABLE `table_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foreignId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_table_1_table_2` (`foreignId`),
  CONSTRAINT `FK_table_1_table_2` FOREIGN KEY (`foreignId`) REFERENCES `table_2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='.';


DROP TABLE IF EXISTS `table_2`;
CREATE TABLE `table_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='.';

Database test_2

-- Host: 127.0.0.1    Database: test_2

DROP TABLE IF EXISTS `table_1`;
CREATE TABLE `table_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foreignId2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_table_1_table_2` (`foreignId2`),
  CONSTRAINT `FK_table_1_table_2` FOREIGN KEY (`foreignId2`) REFERENCES `table_2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='.';


DROP TABLE IF EXISTS `table_2`;
CREATE TABLE `table_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='.';

Command
php php-mysql-diff.phar migrate test_1.sql test_2.sql -o output.sql

Output

# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = '';

# Deleted Tables

# Changed Tables

-- changed table `table_1`

ALTER TABLE `table_1`
  DROP INDEX `FK_table_1_table_2`,
  DROP COLUMN `foreignId`,
  ADD COLUMN `foreignId2` int(11) DEFAULT NULL AFTER `id`,
  CHANGE COLUMN `id` `id` int(11) NOT NULL AUTO_INCREMENT AFTER `foreignId2`,
  ADD KEY `FK_table_1_table_2` (`foreignId2`);

# New Tables

# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 1;

Version

php php-mysql-diff.phar --version
PHP MySQL Diff version 1.1.8

In another table/key combination I receive the following message for the generated script

Duplicate foreign key constraint name ...

From SQL:

CREATE TABLE IF NOT EXISTS `XYZ`
(
            `irig_time` DATETIME(6) NOT NULL COMMENT 'The moment of the snapshot.', 
            `device_id` MEDIUMINT UNSIGNED NOT NULL COMMENT 'reference to the device table' REFERENCES device( device_id ), 
            `message_id` BIGINT UNSIGNED COMMENT 'The message ID', 
    primary key(`irig_time`, `device_id`)
)
COMMENT='Table comment'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

To SQL:

CREATE TABLE IF NOT EXISTS `XYZ`
(
            `irig_time` DATETIME(6) NOT NULL COMMENT 'The moment of the snapshot.', 
            `device_id` BIGINT UNSIGNED NOT NULL COMMENT 'reference to the device table' REFERENCES device( device_id ), 
            `message_id` BIGINT UNSIGNED COMMENT 'The message ID', 
    primary key(`irig_time`, `device_id`)
)
COMMENT='Table comment'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Gives the output "The databases have the same schema!"

Expected result should be that the device_id has been changed from MEDIUMINT to BIGINT.

Because you replace comments with base64. Read RFC 2045. '/' is 63 symbol in Base64.

Alternatives that could be found so fat:

• A somewhat living fork by @Benjaminhu: https://github.com/Benjaminhu/php-mysql-diff
• Dbdiff @DBDiff : https://github.com/DBDiff/DBDiff (ancient)
• Schemadiff by @phlib https://github.com/phlib/schemadiff

Any other ideas?

I found that using SHOW CREATE TABLE 'xxx' is different result.
I have mariadb on development and mysql on production. Problem is for example DEFAULT, one is DEFAULT 0 and second db is DEFAULT '0'.

As it is now, if the difference between two databases is a renamed table, this library will delete the old and recreate the new one, losing all the data.

It would be great if tables could be renamed in migration scripts.
Auto-detecting this could be difficult, especially if some columns are changed at the same time, but maybe table renames could be passed in the CLI call?

e.g.
$ php-mysql-diff --rename 'table_old:table_new,table2_old:table2_new' ...

I'm busy writing a script that automatically generates all migration scripts for a whole bunch of versions, so having something automated would be really great, instead of having to manually fix a couple of script every time.

Open to any other tips or suggestions on this, thanks!

Hi,

When parsing this table definition the list_price column is not detected correctly:

CREATE TABLE `jos_finder_links` (
  `link_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `route` varchar(255) NOT NULL,
  `title` varchar(400) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `indexdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `md5sum` varchar(32) DEFAULT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `state` int(5) DEFAULT '1',
  `access` int(5) DEFAULT '0',
  `language` varchar(8) NOT NULL,
  `publish_start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_end_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `start_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `list_price` double unsigned NOT NULL DEFAULT '0',
  `sale_price` double unsigned NOT NULL DEFAULT '0',
  `type_id` int(11) NOT NULL,
  `object` mediumblob NOT NULL,
  PRIMARY KEY (`link_id`),
  KEY `idx_type` (`type_id`),
  KEY `idx_md5` (`md5sum`),
  KEY `idx_url` (`url`(75)),
  KEY `idx_published_list` (`published`,`state`,`access`,`publish_start_date`,`publish_end_date`,`list_price`),
  KEY `idx_published_sale` (`published`,`state`,`access`,`publish_start_date`,`publish_end_date`,`sale_price`),
  KEY `idx_title` (`title`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I've looked at RegExpPattern::$columnTypeRegExps but so far cannnot figure out why this is not correctly detected.

Hi,

if there is a limit to the key length in a key or index definition, the index is not correctly detected.

I get the following result :
► table "system_log" has a different schema ▲ column "log_version" is in the TO database but not in the FROM database ► column "level" has a different definition FROMlevelenum('log','notice','warning','error','debug') NOT NULL TOlevelenum('log','notice','warning','error','debug') NOT NULL
Since "log_version" is added before "level", we get a different definition.

2 things we may expect :

• Message than order has changed
• Nothing since the insertion of "log_version" will have a add with "AFTER previous_column_of_log_version"

When I reformat my query in PHPStorm regex for tables not work. Tested at regex101.com will show me "Catastrophic backtracking has been detected and the execution of your expression has been halted.".

Look like not all cases handled.
From MySQL help:

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

zerofill, bit, real with params, numeric, binary param for text, and so on.

Version doesn't change with diff and migrate command.
I get always "PHP MySQL Diff 1.0.0"

Hi.

Changing only table comments does not work.
The alter statement is output, but there is no value.

Execution result.

ALTER TABLE target_table
;

Is not it supposed to change table comments?

Thank you.

from.sql

 CREATE TABLE `contact` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
 ) ENGINE = InnoDB;

to.sql

 CREATE TABLE IF NOT EXISTS `contact` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Command used :

php mysqldiff/php-mysql-diff migrate from.sql to.sql

Result :

PHP Warning:  Invalid argument supplied for foreach() in /var/www/fw57/mysqldiff/src/Differ.php on line 23

Warning: Invalid argument supplied for foreach() in /var/www/fw57/mysqldiff/src/Differ.php on line 23
# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = '';

# Deleted Tables

# Changed Tables

# New Tables

-- new table `contact`

CREATE TABLE IF NOT EXISTS `contact` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Disable Foreign Keys Check
SET FOREIGN_KEY_CHECKS = 1;

Expected result:

The databases have the same schema!

I guess nobody is maintaining this repository anymore but I thought I would mention this bug and how to (probably) fix it.

When I run the script I sometimes end up with a migration-script that wants to delete tables that are still in both databases (I suspect that this could also happen the other way round where the migration wants to add an already existing table but so far I haven't encountered this particular situation).

The issue seems to be the base64_encode function in combination with the RegEx used to parse the table-definitions.

base64_encode (and base64_decode) are used in Parser.php to replace comments and default-values with their base64-encoded versions. My guess is that this is done to make parsing easier. Problem is, that base64_encode can produce a string containing a slash at the end. This makes it so that the regex will no longer be able to parse the table-definition and therefore the script will act strangely.

To "fix" this issue I replaced base64_encode/base64_decode with "custom" versions. I used the base64url_encode/base64_decode functions from the comments of the base64_encode-function on php.net - they basically just replace "+/" with "-_" - and this seems to fix the issue (DISCLAIMER: I didn't have the time to test that a whole lot but the few tests I did worked flawlessly).

If the from table has a ROW_FORMAT and the to table doesn't the migration sql is:
ALTER TABLE table_name
ROW_FORMAT=;

Which is not a correct format, it should be ROW_FORMAT=DEFAULT

From what I looked the problem is in Camcima\MySqlDiff\Model\ChangedTable:383
$tableChanges[] = sprintf('ROW_FORMAT=%s', $this->toTable->getRowFormat());
And this fix it:
$toRowFormat = $this->toTable->getRowFormat(); if( empty($toRowFormat) ){ $toRowFormat = 'DEFAULT'; } $tableChanges[] = sprintf('ROW_FORMAT=%s', $toRowFormat);
If you think this is ok , I can make PR.

I have tested some formated sql scripts and after formated your regex not recognize some columns:
for example this is not recognized:
``quantity decimal(11, 2) unsigned NOT NULL DEFAULT '0.00',
because there is formated (11, 2) with space between number 2.
Your regex:
`decimal((?\d+),(?\d+))(?:\s+unsigned)?`
do nothing.
I temporary fixed by this line:
`decimal((?\d+),(?\s*\d+))(?:\s+unsigned)?`
Can you fix it in your code please to other columns type?

Hi Team,

I am using php-mysql-diff tool and this is really helpful for me and all other's.

But, I am stuck in one place that is "Is this possible to append the database name along with table name in migration script?"

For example, I am going to compare Database A and Database B.

Require output is:
ALTER TABLE A.TABLE_NAME *******
CREATE TABLE B.TABLE_NAME (*******)

Please help me with this. Thanks, in advance.

It does not seem to support views.

From table:

CREATE TABLE `poll` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `A` int(11) unsigned NOT NULL,
  `B` tinyint(1) unsigned NOT NULL,
  `C` tinyint(1) unsigned NOT NULL,
  `D` tinyint(1) unsigned NOT NULL,
  `E` tinyint(1) unsigned NOT NULL,
  `F` int(11) unsigned NOT NULL,
  `G` int(11) unsigned NOT NULL,
  `H` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `I` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `J` varchar(100) NOT NULL DEFAULT '',
  `K` varchar(200) NOT NULL DEFAULT '',
  `L` varchar(250) NOT NULL DEFAULT '',
  `M` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `N` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `O` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `P` text,
  PRIMARY KEY (`id`),
  KEY `L` (`L`),
  KEY `N` (`N`),
  KEY `O` (`O`),
  KEY `B` (`B`),
  FULLTEXT KEY `fulltext_index` (`J`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

To table:

CREATE TABLE IF NOT EXISTS `dyw_cms_ankety` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `A` int(11) unsigned NOT NULL DEFAULT 0,
    `B` tinyint(1) unsigned NOT NULL DEFAULT 0,
    `C` tinyint(1) unsigned NOT NULL DEFAULT 0,
    `D` tinyint(1) unsigned NOT NULL DEFAULT 0,
    `E` tinyint(1) unsigned NOT NULL DEFAULT 0,
    `F` int(11) unsigned NOT NULL DEFAULT 0,
    `G` int(11) unsigned NOT NULL DEFAULT 0,
    `H` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
    `I` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
    `J` varchar(100) NOT NULL DEFAULT '',
    `K` varchar(200) NOT NULL DEFAULT '',
    `L` varchar(250) NOT NULL DEFAULT '',
    `M` tinyint(1) unsigned NOT NULL DEFAULT 1,
    `N` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
    `O` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
    `P` text NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `L` (`L`),
    KEY `N` (`N`),
    KEY `O` (`O`),
    KEY `B` (`B`),
    FULLTEXT KEY `fulltext_index` (`J`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Result:

ALTER TABLE `dyw_cms_ankety`
  CHANGE COLUMN `M` `M` tinyint(1) unsigned NOT NULL DEFAULT 1 AFTER `L`,
  CHANGE COLUMN `P` `P` text DEFAULT NULL AFTER `O`;

Problem is that columns are already ordered same at "from" and "to" scripts.
2.
Not generated alter for "DEFAULT"

The command ignores the table completely if there is a backslash in the dump.

time_zone varchar(255) NOT NULL DEFAULT 'America/Los_Angeles',`

It skips the table in the parser with the character.