Merge lp:~patrick-crews/drizzle/drizzle_dump_restore_test into lp:~drizzle-trunk/drizzle/development

[Patrick Crews]

I ported a simple test of dump/restore that I had added to MySQL some time ago. It is drizzledump_restore

This test could use some expanded scenarios, but it does verify that we can dump and restore a table.

Altered test-run.pl so that we can call the drizzle client from within tests ($DRIZZLE), which was needed for the restore portion of the test.

Included a partial port of MySQL Bug#51057 - Weak code in diff_tables.inc can lead to 100% CPU consumption. If one of the tables to be compared did not exist, the test would timeout and waste time and resources.

Ran into some issues with diff_tables and ORDINAL_POSITION, but I suspect this was not needed and the new test is the only one that actually uses this include file.

[Monty Taylor (mordred)]

Looks great!

Two things...

First, it looks like you forgot to add the new test files...

Second, just go ahead and delete the code you commented out in diff_tables. If we need it, we can also pull it from a previous version.

review: Needs Fixing

[Patrick Crews (patrick-crews)]

Sorry about the omission of the new files. My bzr skills are have gathered some dust. Things are fixed and polished - removed the unneeded code from diff_tables.inc and added the new files

[Stewart Smith (stewart)]

On Thu, 20 May 2010 03:40:54 -0000, Patrick Crews <email address hidden> wrote:
> Sorry about the omission of the new files. My bzr skills are have
> gathered some dust. Things are fixed and polished - removed the
> unneeded code from diff_tables.inc and added the new files

'bzr status' can tell you what's around. useful to run before
committing, it'll tell you unknown files (taht you've probably forgotten
to add)

--
Stewart Smith

[lp:~patrick-crews/drizzle/drizzle_dump_restore_test]

1548. By Patrick Crews on 2010-05-25

Fixed .result issue where mysqldump was incorrectly replaced with drizzledump in MySQL bug names

=== modified file 'tests/include/diff_tables.inc'
--- tests/include/diff_tables.inc	2008-06-25 05:29:13 +0000
+++ tests/include/diff_tables.inc	2010-05-20 03:32:27 +0000
@@ -65,7 +65,7 @@
 while ($_diff_i) {
 
   # Parse out any leading "master:" or "slave:" from the table
-  # specification and connect the appropriate server.
+  # specification and connect to the appropriate server.
   let $_diff_conn_master=`SELECT SUBSTR('$_diff_table', 1, 7) = 'master:'`;
   if ($_diff_conn_master) {
     let $_diff_table=`SELECT SUBSTR('$_diff_table', 8)`;
@@ -78,32 +78,30 @@
   }
 
   # Sanity-check the input.
-  let $_diff_error= `SELECT '$_diff_table' NOT LIKE '_%._%'`;
-  if ($_diff_error) {
-    --echo !!!ERROR IN TEST: \$diff_table_$_diff_i='$_diff_table' is not in the form database.table
-    exit;
-  }
-
-  # We need the output files to be sorted (so that diff_files does not
-  # think the files are different just because they are differently
-  # ordered).  To this end, we first generate a query that sorts the
-  # table by all columns.  Since ORDER BY accept column indices, we
-  # just generate a comma-separated list of all numbers from 1 to the
-  # number of columns in the table.
-  let $_diff_column_index=`SELECT MAX(ordinal_position)
-                           FROM information_schema.columns
-                           WHERE CONCAT(table_schema, '.', table_name) =
-                                 '$_diff_table'`;
-  let $_diff_column_list=$_diff_column_index;
-  dec $_diff_column_index;
-  while ($_diff_column_index) {
-    let $_diff_column_list=$_diff_column_index, $_diff_column_list;
-    dec $_diff_column_index;
-  }
+  if (`SELECT '$_diff_table' NOT LIKE '_%._%'`) {
+    --echo !!!ERROR IN TEST: \$diff_table_$_diff_i='$_diff_table'
+    --echo                   is not in the form   database.table
+    --die
+  }
+
+  # Check if the table exists
+  if (`SELECT COUNT(*) = 0 FROM data_dictionary.tables
+       WHERE CONCAT(table_schema, '.', table_name) = '$_diff_table'`) {
+    --echo !!!ERROR IN TEST: The table '$_diff_table' does not exist
+    --die
+  }
+
+
+  #let $_diff_column_list=$_diff_column_index;
+  #dec $_diff_column_index;
+  #while ($_diff_column_index) {
+  #  let $_diff_column_list=$_diff_column_index, $_diff_column_list;
+  #  dec $_diff_column_index;
+  #}
 
   # Now that we have the comma-separated list of columns, we can write
   # the table to a file.
-  eval SELECT * FROM $_diff_table ORDER BY $_diff_column_list
+  eval SELECT * FROM $_diff_table ORDER BY 1 
               INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/diff_table_$_diff_i';
 
   # Do the same for $diff_table_1.

=== added file 'tests/include/drizzledump.inc'
--- tests/include/drizzledump.inc	1970-01-01 00:00:00 +0000
+++ tests/include/drizzledump.inc	2010-05-20 03:32:27 +0000
@@ -0,0 +1,45 @@
+################################################################################
+# drizzledump.inc
+#
+# SUMMARY: include file to facilitate testing the quality of drizzledump output
+#
+# INPUTS:  Two variables:
+#          $table_name - the name of the table that was dumped
+#          $drizzledumpfile - the name of the file that captured drizzledump output
+#
+# OUTPUTS: minor echo data:
+#          We 'echo' some stage information to the .result file:
+#          'altering original table', 'restoring from dumpfile', 'comparing'
+#
+# OTHER FILES:  We use include/diff_tables.inc to compare the original, renamed
+#               table with the 'restored' one. 
+#
+# DESCRIPTION: This file works by being fed the name of the original table
+#              and a drizzledump output file.  The original table is then renamed
+#              to <table_name>_orig, the drizzledump file is used to recreate the
+#              table, then diff_tables.inc is called to compare them.
+#
+# LIMITATIONS:  Does *NOT* work with xml output!
+#
+################################################################################
+
+--echo # Begin testing drizzledump output + restore
+--echo # Create 'original table name - <table>_orig
+# NOTE: We use SET then let as query_get_value has issues with the extra commas
+#       used in the CONCAT statement.
+eval SET @orig_table_name = CONCAT('$table_name', '_orig');
+let $orig_table_name = query_get_value(SELECT @orig_table_name,@orig_table_name,1);
+--echo # Rename original table
+eval ALTER TABLE $table_name RENAME to $orig_table_name;
+--echo # Recreate table from drizzledump output
+--exec $DRIZZLE test < $drizzledumpfile
+--echo # Compare original and recreated tables
+--echo # Recreated table: $table_name
+--echo # Original table: $orig_table_name
+let $diff_table_1 = $table_name;
+let $diff_table_2 = $orig_table_name;
+--source include/diff_tables.inc
+--echo # Cleanup
+--remove_file $drizzledumpfile
+eval DROP TABLE $table_name, $orig_table_name;
+

=== added file 'tests/r/drizzledump_restore.result'
--- tests/r/drizzledump_restore.result	1970-01-01 00:00:00 +0000
+++ tests/r/drizzledump_restore.result	2010-05-20 03:32:27 +0000
@@ -0,0 +1,83 @@
+# Pre-test cleanup
+DROP TABLE IF EXISTS t1;
+# Begin tests
+#
+# Bug#2005 Long decimal comparison bug.
+#
+CREATE TABLE t1 (a DECIMAL(64, 20));
+INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"),
+("0987654321098765432109876543210987654321");
+# Begin testing drizzledump output + restore
+# Create 'original table name - <table>_orig
+SET @orig_table_name = CONCAT('test.t1', '_orig');
+# Rename original table
+ALTER TABLE test.t1 RENAME to test.t1_orig;
+# Recreate table from drizzledump output
+# Compare original and recreated tables
+# Recreated table: test.t1
+# Original table: test.t1_orig
+Comparing tables test.t1 and test.t1_orig
+# Cleanup
+DROP TABLE test.t1, test.t1_orig;
+#
+# Bug#3361 drizzledump quotes DECIMAL values inconsistently
+#
+CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT);
+INSERT INTO t1 VALUES (1.2345, 2.3456);
+INSERT INTO t1 VALUES ('1.2345', 2.3456);
+INSERT INTO t1 VALUES ("1.2345", 2.3456);
+INSERT INTO t1 VALUES (1.2345, 2.3456);
+INSERT INTO t1 VALUES ('1.2345', 2.3456);
+INSERT INTO t1 VALUES ("1.2345", 2.3456);
+# Begin testing drizzledump output + restore
+# Create 'original table name - <table>_orig
+SET @orig_table_name = CONCAT('test.t1', '_orig');
+# Rename original table
+ALTER TABLE test.t1 RENAME to test.t1_orig;
+# Recreate table from drizzledump output
+# Compare original and recreated tables
+# Recreated table: test.t1
+# Original table: test.t1_orig
+Comparing tables test.t1 and test.t1_orig
+# Cleanup
+DROP TABLE test.t1, test.t1_orig;
+#
+# WL#2319 Exclude Tables from dump
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (4),(5),(6);
+# Begin testing drizzledump output + restore
+# Create 'original table name - <table>_orig
+SET @orig_table_name = CONCAT('test.t2', '_orig');
+# Rename original table
+ALTER TABLE test.t2 RENAME to test.t2_orig;
+# Recreate table from drizzledump output
+# Compare original and recreated tables
+# Recreated table: test.t2
+# Original table: test.t2_orig
+Comparing tables test.t2 and test.t2_orig
+# Cleanup
+DROP TABLE test.t2, test.t2_orig;
+DROP TABLE t1;
+#
+# Bug#8830 drizzledump --skip-extended-insert causes --hex-blob to dump wrong values
+#
+CREATE TABLE t1 (`b` blob);
+INSERT INTO `t1` VALUES (0x602010000280100005E71A);
+# Begin testing drizzledump output + restore
+# Create 'original table name - <table>_orig
+SET @orig_table_name = CONCAT('test.t1', '_orig');
+# Rename original table
+ALTER TABLE test.t1 RENAME to test.t1_orig;
+# Recreate table from drizzledump output
+# Compare original and recreated tables
+# Recreated table: test.t1
+# Original table: test.t1_orig
+Comparing tables test.t1 and test.t1_orig
+# Cleanup
+DROP TABLE test.t1, test.t1_orig;
+# End tests
+# Cleanup
+# remove drizzledumpfile

=== added file 'tests/t/drizzledump_restore.test'
--- tests/t/drizzledump_restore.test	1970-01-01 00:00:00 +0000
+++ tests/t/drizzledump_restore.test	2010-05-20 03:32:27 +0000
@@ -0,0 +1,91 @@
+###############################################################################
+# drizzledump_restore.test
+#
+# Purpose:  Tests if drizzledump output can be used to successfully restore
+#           tables and data.  
+#           We CREATE a table, drizzledump it to a file, ALTER the original
+#           table's name, recreate the table from the drizzledump file, then
+#           utilize include/diff_tables to compare the original and recreated
+#           tables.
+#
+#           We use several examples from drizzledump.test here and include
+#           the relevant bug numbers and headers from that test.
+#
+# NOTE:     This test is not currently complete and offers only basic
+#           cases of drizzledump output being restored. 
+#           Also, does NOT work with -X (xml) output!
+#           
+# Author:   pcrews
+# Created:  2009-05-21
+# Last Change:
+# Change date:
+###############################################################################
+
+#--source include/have_log_bin.inc
+
+
+# Define drizzledumpfile here.  It is used to capture drizzledump output
+# in order to test the output's ability to restore an exact copy of the table
+let $drizzledumpfile = $MYSQLTEST_VARDIR/tmp/drizzledumpfile.sql;
+
+--echo # Pre-test cleanup
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--echo # Begin tests
+--echo #
+--echo # Bug#2005 Long decimal comparison bug.
+--echo #
+CREATE TABLE t1 (a DECIMAL(64, 20));
+INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"),
+("0987654321098765432109876543210987654321");
+--exec $DRIZZLE_DUMP --compact test t1 > $drizzledumpfile
+let $table_name = test.t1;
+--source include/drizzledump.inc
+
+--echo #
+--echo # Bug#3361 mysqldump quotes DECIMAL values inconsistently
+--echo #
+CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT);
+# Check at first how mysql work with quoted decimal
+INSERT INTO t1 VALUES (1.2345, 2.3456);
+INSERT INTO t1 VALUES ('1.2345', 2.3456);
+INSERT INTO t1 VALUES ("1.2345", 2.3456);
+INSERT INTO t1 VALUES (1.2345, 2.3456);
+INSERT INTO t1 VALUES ('1.2345', 2.3456);
+INSERT INTO t1 VALUES ("1.2345", 2.3456);
+
+# check how drizzledump make quoting
+--exec $DRIZZLE_DUMP --compact test t1 > $drizzledumpfile
+let $table_name = test.t1;
+--source include/drizzledump.inc
+
+
+--echo #
+--echo # WL#2319 Exclude Tables from dump
+--echo #
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (4),(5),(6);
+--exec $DRIZZLE_DUMP --skip-comments --ignore-table=test.t1 test > $drizzledumpfile
+let $table_name = test.t2;
+--source include/drizzledump.inc
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#8830 mysqldump --skip-extended-insert causes --hex-blob to dump wrong values
+--echo #
+CREATE TABLE t1 (`b` blob);
+INSERT INTO `t1` VALUES (0x602010000280100005E71A);
+--exec $DRIZZLE_DUMP --skip-extended-insert --hex-blob test --skip-comments t1 > $drizzledumpfile
+let $table_name = test.t1;
+--source include/drizzledump.inc
+
+--echo # End tests
+
+--echo # Cleanup
+--echo # remove drizzledumpfile
+--error 0,1
+--remove_file $drizzledumpfile

=== modified file 'tests/test-run.pl'
--- tests/test-run.pl	2010-04-20 07:21:29 +0000
+++ tests/test-run.pl	2010-05-20 03:32:27 +0000
@@ -1322,6 +1322,13 @@
       "--port=$mysqld->{'port'} ";
 }
 
+sub generate_cmdline_drizzle ($) {
+  my($mysqld) = @_;
+  return
+    mtr_native_path($exe_drizzle) .
+    " -uroot --port=$mysqld->{'port'} ";
+}
+
 
 ##############################################################################
 #
@@ -1492,6 +1499,12 @@
   $ENV{'DRIZZLE_DUMP_SECONDARY'}= $cmdline_mysqldump_secondary;
 
   # ----------------------------------------------------
+  # Setup env so we can execute drizzle client
+  # ----------------------------------------------------
+  my $cmdline_drizzle = generate_cmdline_drizzle($master->[0]);
+  $ENV{'DRIZZLE'}= $cmdline_drizzle;
+
+  # ----------------------------------------------------
   # Setup env so childs can execute mysqlslap
   # ----------------------------------------------------
   if ( $exe_drizzleslap )