From 37d22846c1ebdebab98580ac693deb72ab68c28a Mon Sep 17 00:00:00 2001 From: Venkata Sidagam Date: Tue, 14 Aug 2012 15:13:30 +0530 Subject: Bug #12992993 MYSQLHOTCOPY FAILS IF VIEW EXISTS Problem description: mysqlhotcopy fails if a view presents in the database. Analysis: Before 5.5 'FLUSH TABLES ... WITH READ LOCK' will able to get lock for all tables (i.e. base tables and view tables). In 5.5 onwards 'FLUSH TABLES ... WITH READ LOCK' for 'view tables' will not work, because taking flush locks on view tables is not valid. Fix: Take flush lock for 'base tables' and read lock for 'view table' separately. Note: most of the patch has been backported from bug#13006947's patch --- scripts/mysqlhotcopy.sh | 88 ++++++++++++++++++++++++++++++++++++++----------- 1 file changed, 69 insertions(+), 19 deletions(-) diff --git a/scripts/mysqlhotcopy.sh b/scripts/mysqlhotcopy.sh index aaa77ec181c..a7f465dc891 100644 --- a/scripts/mysqlhotcopy.sh +++ b/scripts/mysqlhotcopy.sh @@ -272,23 +272,28 @@ if ( defined $opt{regexp} ) { } } -# --- get list of tables to hotcopy --- +# --- get list of tables and views to hotcopy --- my $hc_locks = ""; my $hc_tables = ""; +my $hc_base_tables = ""; +my $hc_views = ""; +my $num_base_tables = 0; +my $num_views = 0; my $num_tables = 0; my $num_files = 0; foreach my $rdb ( @db_desc ) { my $db = $rdb->{src}; - my @dbh_tables = get_list_of_tables( $db ); + my @dbh_base_tables = get_list_of_tables( $db ); + my @dbh_views = get_list_of_views( $db ); ## filter out certain system non-lockable tables. ## keep in sync with mysqldump. if ($db =~ m/^mysql$/i) { - @dbh_tables = grep - { !/^(apply_status|schema|general_log|slow_log)$/ } @dbh_tables + @dbh_base_tables = grep + { !/^(apply_status|schema|general_log|slow_log)$/ } @dbh_base_tables } ## generate regex for tables/files @@ -303,11 +308,20 @@ foreach my $rdb ( @db_desc ) { ## filter (out) tables specified in t_regex print "Filtering tables with '$t_regex'\n" if $opt{debug}; - @dbh_tables = ( $negated - ? grep { $_ !~ $t_regex } @dbh_tables - : grep { $_ =~ $t_regex } @dbh_tables ); + @dbh_base_tables = ( $negated + ? grep { $_ !~ $t_regex } @dbh_base_tables + : grep { $_ =~ $t_regex } @dbh_base_tables ); + + ## filter (out) views specified in t_regex + print "Filtering tables with '$t_regex'\n" if $opt{debug}; + @dbh_views = ( $negated + ? grep { $_ !~ $t_regex } @dbh_views + : grep { $_ =~ $t_regex } @dbh_views ); } + ## Now concatenate the base table and view arrays. + my @dbh_tables = (@dbh_base_tables, @dbh_views); + ## get list of files to copy my $db_dir = "$datadir/$db"; opendir(DBDIR, $db_dir ) @@ -347,15 +361,25 @@ foreach my $rdb ( @db_desc ) { $rdb->{files} = [ @db_files ]; $rdb->{index} = [ @index_files ]; - my @hc_tables = map { quote_names("$db.$_") } @dbh_tables; + my @hc_base_tables = map { quote_names("$db.$_") } @dbh_base_tables; + my @hc_views = map { quote_names("$db.$_") } @dbh_views; + + my @hc_tables = (@hc_base_tables, @hc_views); $rdb->{tables} = [ @hc_tables ]; $hc_locks .= ", " if ( length $hc_locks && @hc_tables ); $hc_locks .= join ", ", map { "$_ READ" } @hc_tables; - $hc_tables .= ", " if ( length $hc_tables && @hc_tables ); - $hc_tables .= join ", ", @hc_tables; - $num_tables += scalar @hc_tables; + $hc_base_tables .= ", " if ( length $hc_base_tables && @hc_base_tables ); + $hc_base_tables .= join ", ", @hc_base_tables; + $hc_views .= ", " if ( length $hc_views && @hc_views ); + $hc_views .= join " READ, ", @hc_views; + + @hc_tables = (@hc_base_tables, @hc_views); + + $num_base_tables += scalar @hc_base_tables; + $num_views += scalar @hc_views; + $num_tables += $num_base_tables + $num_views; $num_files += scalar @{$rdb->{files}}; } @@ -467,7 +491,10 @@ if ( $opt{dryrun} ) { print "FLUSH TABLES /*!32323 $hc_tables */\n"; } else { - print "FLUSH TABLES $hc_tables WITH READ LOCK\n"; + # Lock base tables and views separately. + print "FLUSH TABLES $hc_base_tables WITH READ LOCK\n" + if ( $hc_base_tables ); + print "LOCK TABLES $hc_views READ\n" if ( $hc_views ); } print "FLUSH LOGS\n" if ( $opt{flushlog} ); @@ -484,16 +511,24 @@ else { # flush tables to make on-disk copy up to date $start = time; $dbh->do("FLUSH TABLES /*!32323 $hc_tables */"); + printf "Flushed tables ($hc_tables) in %d seconds.\n", time-$start unless $opt{quiet}; } else { - $dbh->do("FLUSH TABLES $hc_tables WITH READ LOCK"); - printf "Locked $num_tables tables in %d seconds.\n", time-$start unless $opt{quiet}; - $hc_started = time; # count from time lock is granted + # Lock base tables and views separately, as 'FLUSH TABLES + # ... WITH READ LOCK' (introduced in 5.5) would fail for views. + # Also, flush tables to make on-disk copy up to date + $dbh->do("FLUSH TABLES $hc_base_tables WITH READ LOCK") + if ( $hc_base_tables ); + printf "Flushed $num_base_tables tables with read lock ($hc_base_tables) in %d seconds.\n", + time-$start unless $opt{quiet}; - # flush tables to make on-disk copy up to date $start = time; + $dbh->do("LOCK TABLES $hc_views READ") if ( $hc_views ); + printf "Locked $num_views views ($hc_views) in %d seconds.\n", + time-$start unless $opt{quiet}; + + $hc_started = time; # count from time lock is granted } - printf "Flushed tables ($hc_tables) in %d seconds.\n", time-$start unless $opt{quiet}; $dbh->do( "FLUSH LOGS" ) if ( $opt{flushlog} ); $dbh->do( "RESET MASTER" ) if ( $opt{resetmaster} ); $dbh->do( "RESET SLAVE" ) if ( $opt{resetslave} ); @@ -802,14 +837,29 @@ sub get_list_of_tables { my $tables = eval { - $dbh->selectall_arrayref('SHOW TABLES FROM ' . - $dbh->quote_identifier($db)) + $dbh->selectall_arrayref('SHOW FULL TABLES FROM ' . + $dbh->quote_identifier($db) . + ' WHERE Table_type = \'BASE TABLE\'') } || []; warn "Unable to retrieve list of tables in $db: $@" if $@; return (map { $_->[0] } @$tables); } +sub get_list_of_views { + my ( $db ) = @_; + + my $views = + eval { + $dbh->selectall_arrayref('SHOW FULL TABLES FROM ' . + $dbh->quote_identifier($db) . + ' WHERE Table_type = \'VIEW\'') + } || []; + warn "Unable to retrieve list of views in $db: $@" if $@; + + return (map { $_->[0] } @$views); +} + sub quote_names { my ( $name ) = @_; # given a db.table name, add quotes -- cgit v1.2.1