Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the MySQL-Diff CPAN distribution.

Report information
The Basics
Id:
48618
Status:
open
Priority:
Low/Low
Queue:

People
Owner:
Nobody in particular
Requestors:
rota.giuseppe [...] gmail.com
Cc:
AdminCc:

BugTracker
Severity:
Important
Broken in:
0.43
Fixed in:
(no value)



Subject: mysqldiff does not recognize CONSTRAINT lines
Date: Tue, 11 Aug 2009 14:05:00 +0200
To: bug-MySQL-Diff@rt.cpan.org
From: Giuseppe Rota <rota.giuseppe@gmail.com>
Within the Table.pm code, the parsing seems not to be able to detect CONSTRAINT definition. Here's an example from mysqldump -d ... DROP TABLE IF EXISTS `vtiger_parenttabrel`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `vtiger_parenttabrel` ( `parenttabid` int(19) NOT NULL, `tabid` int(19) NOT NULL, `sequence` int(3) NOT NULL, KEY `parenttabrel_tabid_parenttabid_idx` (`tabid`,`parenttabid`), KEY `fk_2_vtiger_parenttabrel` (`parenttabid`), CONSTRAINT `fk_1_vtiger_parenttabrel` FOREIGN KEY (`tabid`) REFERENCES `vtiger_tab` (`tabid`) ON DELETE CASCADE, CONSTRAINT `fk_2_vtiger_parenttabrel` FOREIGN KEY (`parenttabid`) REFERENCES `vtiger_parenttab` (`parenttabid`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; SET character_set_client = @saved_cs_client; (as you can see there are 2 CONSTRAINT lines). I had to comment the lines 85,86 of Table.pm #croak "definition for field `$field' duplicated in table `$name'\n" # if $self->fields($field); to make it work, but it's obviously a wrong approach. I sadly do not have the perl-skills to provide for a correct solution. I just wanted to let you know in case you wanted to fix it. Cheers, Giuseppe
Attached is a patch I received from Gihan Munasinghe to support CONSTRAINT - it will need merging with the github master branch though.
Subject: foreignkey_support.patch
diff -Naur mysqldiff.old/Diff.pm mysqldiff.new/Diff.pm --- mysqldiff.old/Diff.pm 2011-08-08 17:23:56.808905965 +0100 +++ mysqldiff.new/Diff.pm 2011-08-08 20:58:09.417655990 +0100 @@ -88,7 +88,8 @@ my @changes = (diff_fields(@_), diff_indices(@_), diff_primary_key(@_), - diff_options(@_)); + diff_options(@_), + diff_constrain(@_)); if (@changes) { $changes[-1] =~ s/\n*$/\n/; } @@ -295,4 +296,45 @@ return @changes; } +sub diff_constrain { + my ($opts, $table1, $table2) = @_; + + my %opts = %$opts; + my $name = $table1->name(); + my %constrain1 = %{ $table1->constrain() }; + my %constrain2 = %{ $table2->constrain() }; + + my @changes = (); + + foreach my $const ($table1->constrain_keys) { + if($constrain2{$const}){ + if ( $constrain1{$const} ne $constrain2{$const} ) { + debug(4, " constraint `$const' modified\n"); + my $change = "ALTER TABLE $name DROP FOREIGN KEY $const;"; + $change .= "ALTER TABLE $name ADD CONSTRAINT $const $constrain2{$const};"; + $change .= " # was ($constrain1{$const})" unless $opts{'no-old-defs'}; + $change .= "\n"; + push @changes, $change; + }else{ + #do nothing + } + }else{ + debug(4, " constraint `$const' removed\n"); + my $change = "ALTER TABLE $name DROP FOREIGN KEY $const;"; + $change .= " # was ($constrain1{$const})" unless $opts{'no-old-defs'}; + $change .= "\n"; + push @changes, $change; + } + } + foreach my $const (keys %constrain2) { + if (! $constrain1{$const}) { + debug(4, " constraint `$const' added\n"); + push @changes, + "ALTER TABLE $name ADD CONSTRAINT $const $constrain2{$const};\n"; + } + } + + return @changes; +} + 1; diff -Naur mysqldiff.old/Table.pm mysqldiff.new/Table.pm --- mysqldiff.old/Table.pm 2011-08-08 17:23:56.808905965 +0100 +++ mysqldiff.new/Table.pm 2011-08-08 20:58:09.417655990 +0100 @@ -7,7 +7,7 @@ 'new --and_then_init' => 'new', 'scalar' => 'name def source primary_key options', 'array --get_set_ref' => 'lines', - 'hash' => 'fields indices unique_index fulltext', + 'hash' => 'fields indices unique_index fulltext constrain', ; use MySQL::Utils qw(debug); @@ -74,6 +74,15 @@ next; } + if (/^CONSTRAINT\s+(\S+)\s+(\S.*)\s*$/) { + my ($key, $val) = ($1, $2); + croak "CONSTRAINT `$key' duplicated in table `$name'\n" + if $self->constrain($key); + $self->constrain_push($key,$val); + debug(6, " got CONSTRAINT `$key': ($val)\n"); + next; + } + if (/^\)\s*(.*?);$/) { # end of table definition my $options = $self->options($1); debug(6, " got table options `$options'\n");
And here's another patch from Todd Lyons. Damn!
Subject: 0001-Add-foreign-key-support.patch
From e0b3d4901e644cedac7e308ab977150825051c7a Mon Sep 17 00:00:00 2001 From: Todd Lyons <tlyons@ivenue.com> Date: Thu, 9 Dec 2010 14:48:53 -0800 Subject: [PATCH] Add foreign key support. Ignores triggers, future feature. --- Diff.pm | 65 +++++++++++++++++++++++++++++++++++++++++++++++++++--------- Table.pm | 14 ++++++++++++- mysqldiff | 17 ++++++++++----- 3 files changed, 79 insertions(+), 17 deletions(-) diff --git a/Diff.pm b/Diff.pm index da31629..bcf25b1 100644 --- a/Diff.pm +++ b/Diff.pm @@ -23,17 +23,18 @@ sub diff_dbs { foreach my $table1 ($db[0]->tables()) { my $name = $table1->name(); - if ($table_re && $name !~ $table_re) { - debug(2, " table `$name' didn't match /$table_re/; ignoring\n"); + ( my $clean = $name ) =~ s/\`//g; + if ($table_re && $clean !~ $table_re) { + debug(2, " table `$clean' didn't match /$table_re/; ignoring\n"); next; } - debug(2, " looking at tables called `$name'\n"); + debug(2, " looking at tables called `$clean'\n"); if (my $table2 = $db[1]->table_by_name($name)) { - debug(4, " comparing tables called `$name'\n"); + debug(4, " comparing tables called `$clean'\n"); push @changes, diff_tables($opts, $table1, $table2); } else { - debug(3, " table `$name' dropped\n"); + debug(3, " table `$clean' dropped\n"); push @changes, "DROP TABLE $name;\n\n" unless $opts{'only-both'} || $opts{'keep-old-tables'}; } @@ -41,12 +42,14 @@ sub diff_dbs { foreach my $table2 ($db[1]->tables()) { my $name = $table2->name(); - if ($table_re && $name !~ $table_re) { - debug(2, " table `$name' matched $opts{'table-re'}; ignoring\n"); + ( my $clean = $name ) =~ s/\`//g; + if ($table_re && $clean !~ $table_re) { + debug(2, " table `$clean' didn't match $opts{'table-re'}; ignoring\n"); next; } + debug(2, " looking at tables called `$clean'\n"); if (! $db[0]->table_by_name($name)) { - debug(3, " table `$name' added\n"); + debug(3, " table `$clean' added\n"); push @changes, $table2->def() . "\n" unless $opts{'only-both'}; } @@ -78,8 +81,8 @@ sub diff_banner { ## ## Run on $now $opt_text## -## --- $summary1 -## +++ $summary2 +## --- $summary1 => this is the database to be changed +## +++ $summary2 => make database above be the same as this one EOF } @@ -88,6 +91,7 @@ sub diff_tables { my @changes = (diff_fields(@_), diff_indices(@_), diff_primary_key(@_), + diff_foreign_keys(@_), diff_options(@_)); if (@changes) { $changes[-1] =~ s/\n*$/\n/; @@ -275,6 +279,42 @@ sub index_auto_col { return "ALTER TABLE $name ADD INDEX ($field); # auto columns must always be indexed\n"; } +sub diff_foreign_keys { + my ($opts, $table1, $table2) = @_; + + my %opts = %$opts; + my $name1 = $table1->name(); + + my @changes = (); + + foreach my $fkey ( $table1->foreign_keys_keys() ) { + my $fk1 = $table1->foreign_keys($fkey); + my $fk2 = $table2->foreign_keys($fkey); + + if ($fk2) { + if (! grep { /^$fkey$/ } $table2->foreign_keys_keys() ) { + debug(4, " FOREIGN KEY `$fkey' not present\n"); + my $change = "ALTER TABLE $name1 DROP FOREIGN KEY $fkey;" + unless $opts{'no-old-defs'}; + $change .= "\n"; + push @changes, $change; + } + } + } + + foreach my $fkey ( $table2->foreign_keys_keys() ) { + my $fk1 = $table1->foreign_keys($fkey); + my $fk2 = $table2->foreign_keys($fkey); + if (! grep { /^$fkey$/ } $table1->foreign_keys_keys() ) { + debug(4, " FOREIGN KEY `$fkey' added\n"); + push @changes, + "ALTER TABLE $name1 ADD CONSTRAINT $fkey FOREIGN KEY ($fk2->[0]) REFERENCES $fk2->[1] ($fk2->[2]) $fk2->[3];\n"; + } + } + + return @changes; +} + sub diff_options { my ($opts, $table1, $table2) = @_; @@ -283,6 +323,11 @@ sub diff_options { my $options2 = $table2->options(); my $name = $table1->name(); + if ( ! $opts{'autoincrement'} ) { + $options1 =~ s/AUTO_INCREMENT=\d+ //; + $options2 =~ s/AUTO_INCREMENT=\d+ //; + } + my @changes = (); if ($options1 ne $options2) { diff --git a/Table.pm b/Table.pm index 4258120..f0468b7 100644 --- a/Table.pm +++ b/Table.pm @@ -8,6 +8,7 @@ use Class::MakeMethods::Template::Hash 'scalar' => 'name def source primary_key options', 'array --get_set_ref' => 'lines', 'hash' => 'fields indices unique_index fulltext', + 'hash_of_arrays' => 'foreign_keys', ; use MySQL::Utils qw(debug); @@ -74,6 +75,15 @@ sub parse { next; } + if (/^CONSTRAINT\s+(\S+?)\s+FOREIGN\s+KEY\s*\((\S+)\)\s+REFERENCES\s+(\S+)\s+\((\S+)\)\s*(.*)$/ ) { + my($fkey, $local_field, $other_table, $other_field, $conditions) = ($1, $2, $3, $4, $5, $6); + croak "FOREIGN KEY `$fkey' duplicated in table `$name'\n" + if ( grep { /^$fkey$/ } $self->foreign_keys_keys() ); + $self->foreign_keys_push($fkey, ($local_field, $other_table, $other_field, $conditions) ); + debug(6, " got FOREIGN KEY `$fkey': ($local_field) to $other_table($other_field)\n"); + next; + } + if (/^\)\s*(.*?);$/) { # end of table definition my $options = $self->options($1); debug(6, " got table options `$options'\n"); @@ -98,8 +108,10 @@ sub parse { @lines = grep ! m{^/\*!40000 ALTER TABLE \Q$name\E DISABLE KEYS \*/;}, @lines; + # Look for extra lines that exist after end of table definition + # was reached, but ignore it if it looks like triggers. warn "table `$name' had trailing garbage:\n", join '', @lines - if @lines; + if ( @lines && ! grep { /DROP|DELIMITER|TRIGGER/ } @lines ); } 1; diff --git a/mysqldiff b/mysqldiff index eba78c0..0ab76fc 100755 --- a/mysqldiff +++ b/mysqldiff @@ -25,6 +25,7 @@ use Carp qw(:DEFAULT cluck); use FindBin qw($RealBin $Script); use lib $RealBin; use Getopt::Long; +use IO::File; use MySQL::Diff qw(parse_arg diff_dbs); use MySQL::Utils qw(debug_level); @@ -32,11 +33,11 @@ use MySQL::Utils qw(debug_level); my %opts = (); GetOptions(\%opts, "help|?", "debug|d:i", "apply|A", "batch-apply|B", "keep-old-tables|k", "no-old-defs|n", "only-both|o", "table-re|t=s", - "host|h=s", "port|P=s", "user|u=s", "password|p:s", - "host1|h1=s", "port1|P1=s", "user1|u1=s", "password1|p1:s", - "host2|h2=s", "port2|P2=s", "user2|u2=s", "password2|p2:s", + "host|h=s", "port|P=s", "user|u=s", "password:s", + "host1|h1=s", "port1|P1=s", "user1|u1=s", "password1:s", + "host2|h2=s", "port2|P2=s", "user2|u2=s", "password2:s", "socket|s=s", "socket1|s1=s", "socket2|s2=s", - "tolerant|i" + "tolerant|i", "autoincrement!" ); if (@ARGV != 2 or $opts{help}) { @@ -57,7 +58,7 @@ for my $num (0, 1) { $db[$num] = $new_db; } -$| = 1; +STDOUT->autoflush(1); my $diffs = diff_dbs(\%opts, @db); print $diffs; apply($diffs) if $opts{apply} || $opts{'batch-apply'}; @@ -69,7 +70,9 @@ exit 0; sub usage { print STDERR @_, "\n" if @_; die <<EOF; -Usage: $Script [ options ] <database1> <database2> +Function: Make target database1 schema look like master database2 schema + +Usage: $Script [ options ] <database> <database2> Options: -?, --help show this help @@ -81,6 +84,8 @@ Options: -n, --no-old-defs suppress comments describing old definitions -t, --table-re=REGEXP restrict comparisons to tables matching REGEXP -i, --tolerant ignore DEFAULT and formatting changes + --autoincrement use AUTOINCREMENT in table options comparison + --no-autoincrement ignore AUTOINCREMENT in table options (default) -h, --host=... connect to host -P, --port=... use this port for connection -- 1.7.1
On Wed Oct 05 14:58:34 2011, ASPIERS wrote:
Show quoted text
> And here's another patch from Todd Lyons. Damn!
Todd has kindly offered to rebase his patch against the latest master branch, and is planning to do that sometime in the next few days.
It looks like a THIRD person has fixed this :( https://github.com/aspiers/mysqldiff/pull/2 Much as I appreciate the contributions, it would be better if people collaborated on a single patch via peer review. Now I have to review all three patches and decide which is best - and this will take longer ...
From: Richard Ayotte
I noticed that some of the patches have been reviewed on github but they haven't been merged. Is there a branch with these patches applied? On Tue Nov 29 12:11:32 2011, ASPIERS wrote:
Show quoted text
> It looks like a THIRD person has fixed this :( > > https://github.com/aspiers/mysqldiff/pull/2 > > Much as I appreciate the contributions, it would be better if people > collaborated on a single patch via peer review. Now I have to review all > three patches and decide which is best - and this will take longer ...
From: kes-kes@yandex.ru
some problems with constraint Use of uninitialized value $name in concatenation (.) or string at /usr/local/lib/perl5/site_perl/5.16.0/MySQL/Diff/Table.pm line 212. definition for field 'CONSTRAINT' duplicated in table '' at /usr/local/lib/perl5/site_perl/5.16.0/MySQL/Diff/Database.pm line 269.
From: kes-kes@yandex.ru
This is debug=9 output line: [PRIMARY KEY (id)] got primary key (id) line: [KEY creator_id (creator_id)] got index key 'creator_id': (creator_id) line: [KEY accounting_mails_ibfk_2 (changer_id)] got index key 'accounting_mails_ibfk_2': (changer_id) line: [CONSTRAINT accounting_mails_ibfk_1 FOREIGN KEY (creator_id) REFERENCES admins (id) ON DELETE SET NULL ON UPDATE CASCADE] got field def 'CONSTRAINT': accounting_mails_ibfk_1 FOREIGN KEY (creator_id) REFERENCES admins (id) ON DELETE SET NULL ON UPDATE CASCADE line: [CONSTRAINT accounting_mails_ibfk_2 FOREIGN KEY (changer_id) REFERENCES admins (id) ON DELETE SET NULL ON UPDATE CASCADE] Use of uninitialized value $name in concatenation (.) or string at /usr/local/lib/perl5/site_perl/5.16.0/MySQL/Diff/Table.pm line 212. definition for field 'CONSTRAINT' duplicated in table '' at /usr/local/lib/perl5/site_perl/5.16.0/MySQL/Diff/Database.pm line 269.
Hi all, Thanks a lot for the contributions so far. I wrote a blog post containing some general principles for how to submit patches which I can accept: http://blog.adamspiers.org/2012/11/10/7-principles-for-contributing- patches-to-software-projects/ If someone is able to provide patches to address this bug in a manner which adheres to those principles, I'll be incredibly grateful!


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.