view release on metacpan or  search on metacpan

lib/DBIx/Compare/ContentChecksum/mysql.pm  view on Meta::CPAN

1;

__END__


=head1 NAME

DBIx::Compare::ContentChecksum::mysql - Extension to L<DBIx::Compare|DBIx::Compare>, enables more detailed comparison of MySQL databases.

=head1 SYNOPSIS

	use DBIx::Compare::ContentChecksum::mysql;

	my $oDB_Comparison = compare_mysql_checksum->new($dbh1,$dbh2);
	$oDB_Comparison->group_concat_max_len(10000000);
	$oDB_Comparison->compare;

=head1 DESCRIPTION

DBIx::Compare::ContentChecksum::mysql takes two MySQL database handles and performs a low level comparison of their table content. It was developed to compare databases before and after table optimisation, but would be useful in any scenario where yo...

DBIx::Compare::ContentChecksum::mysql utilises the MySQL functions 'GROUP_CONCAT' and 'MD5' to generate MD5 checksums from each field of a table, ordered by their primary keys, both ascending and descending. Then it simply compares the checksums retu...

MySQL has a built in variable called C<group_concat_max_len>. This limits the length of values returned by the C<group_concat> function, truncating longer values. Helpfully, MySQL will issue a warning to let you know that the returned value has been ...

=head2 Caveats

Its worth noting that while this process can conclusively prove that two databases are different, it can only indicate that its likely two databases are identical. My understanding of the way MD5 works is that identical checksums will provide a very ...

Regardless there could always be occassions where the table contents are indeed different, but where the same checksum is produced. For instance, if a field in table 1 has values(1,21), whereas table 2 has values(12,1), then the GROUP_CONCAT function...

One issue I discovered during testing of this module is that in some cases, identical data in two tables of different Engine types (MyISAM and InnoDB) will return different MD5 checksums. I've no idea how that happens, but hopefully someone will tell...

=head1 METHODS

=over

=item B<compare>

Performs the comparison. Calls the methods compare_table_lists, compare_table_fields, compare_row_counts and compare_fields_checksum in order, each method comparing tables and fields that have passed the preceeding test. Returns true if no difference...

=item B<compare_fields_checksum>

Comparison of the MD5 checksum of each concatenated field. Can pass a table name, or will compare all tables. Returns true if no differences are found, otherwise returns undef. An array ref of fields that return different checksums can be recovered w...

=item B<field_checksum($table,$field)>

Returns the combined MD5 checksum for the given table/field. Actually this is two joined checksums - one of the group_concat sorted by ascending primary key, and the other sorted by descending key (or by descending each key in the case of a compound ...

=item B<group_concat_max_len()>

Set the MySQL variable C<group_concat_max_len>. This defaults to 1024, but you might need a larger value depending on your data. 

=back

=head1 DISCLAIMER

I have no idea how big a concatenated value would have to be to cause havoc with your system..... I took care to gradually test the limits of my own system before I risked crashing everything spectacularly, and would suggest you do the same. I accept...

Having said that, a dual-Quad-core-Xeon MySQL server with 4Gb of RAM and a C<group_concat_max_len> value of 1,000,000,000 was happy (albeit a bit slow) concatenating 100,000,000 rows of a varchar(20) with an average length of 7.5. This is only a guid...

=head1 SEE ALSO

L<DBIx::Compare|DBIx::Compare>

=head1 AUTHOR

Christopher Jones, Gynaecological Cancer Research Laboratories, UCL EGA Institute for Women's Health, University College London.

c.jones@ucl.ac.uk

=head1 COPYRIGHT AND LICENSE

Copyright 2008 by Christopher Jones, University College London

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself. 

=cut