bk commit into 5.0 tree (msvensson:1.2314) BUG#19371

[msvensson]

Below is the list of changes that have just been committed into a local
5.0 repository of msvensson. When msvensson does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-09 12:00:27+01:00, msvensson@neptunus.(none) +7 -0
  Bug#19371 VARBINARY() have trailing zeros after upgrade from 4.1
   - Detect if a table has field of type MYSQL_TYPE_VAR_STRING while running
     "CHECK TABLE t FOR UPGRADE" and indicate it need to be fixed
     with "REPAIR TABLE t".
   - When running a "REPAIR TABLE t" or "ALTER TABLE t FORCE" on the above
     table, install a special copy function to trim off the trailing spaces
     which we safely can say that the pre 5.0 mysqld didn't put there. 

  mysql-test/r/varbinary.result@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +52 -0
    Add test to see that a table with varbinary from 4.1 can be REPAIRED 

  mysql-test/std_data/bug19371.MYD@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +2 -0
    New BitKeeper file ``mysql-test/std_data/bug19371.MYD''

  mysql-test/std_data/bug19371.MYD@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +0 -0

  mysql-test/std_data/bug19371.MYI@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +24 -0
    New BitKeeper file ``mysql-test/std_data/bug19371.MYI''

  mysql-test/std_data/bug19371.MYI@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +0 -0

  mysql-test/std_data/bug19371.frm@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +192 -0
    New BitKeeper file ``mysql-test/std_data/bug19371.frm''

  mysql-test/std_data/bug19371.frm@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +0 -0

  mysql-test/t/varbinary.test@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +40 -0
    Add test to see that a table with varbinary from 4.1 can be REPAIRED 

  sql/field_conv.cc@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +24 -0
    Add new field copy function 'do_field_varbinary_pre50' used for copying
    between MYSQL_TYPE_VAR_STRING and MYSQL_TYPE_VARCHAR. It will remove trailing
    spaces from the field as MySQL <= 4.1 never stores the trailing spaces for
    a MYSQL_TYPE_VAR_STRING.
    Install this new copy function in ALTER TABLEs list of functions to use for
    copying data during and alter if from field is a <= 4.1 varbinary and to
    field is 5.0 varbinary. 

  sql/handler.cc@stripped, 2006-11-09 12:00:25+01:00, msvensson@neptunus.(none) +4 -0
    If the table has a pre 5.0 varbinary, table not to be altered so the field
    type is upgraded to 5.0 version and trailing space can be trimmed.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	msvensson
# Host:	neptunus.(none)
# Root:	/home/msvensson/mysql/bug19731/my50-bug19731

--- 1.57/sql/field_conv.cc	2006-11-09 12:00:32 +01:00
+++ 1.58/sql/field_conv.cc	2006-11-09 12:00:32 +01:00
@@ -309,6 +309,21 @@ static void do_field_string(Copy_field *
 }
 
 
+static void do_field_varbinary_pre50(Copy_field *copy)
+{
+  char buff[MAX_FIELD_WIDTH];
+  copy->tmp.set_quick(buff,sizeof(buff),copy->tmp.charset());
+  copy->from_field->val_str(&copy->tmp);
+
+  /* Use the same function as in 4.1 to trim trailing spaces */
+  uint length= my_lengthsp_8bit(&my_charset_bin, copy->tmp.c_ptr_quick(),
+                                copy->from_field->field_length);
+
+  copy->to_field->store(copy->tmp.c_ptr_quick(), length,
+                        copy->tmp.charset());
+}
+
+
 static void do_field_int(Copy_field *copy)
 {
   longlong value= copy->from_field->val_int();
@@ -570,6 +585,15 @@ void (*Copy_field::get_copy_func(Field *
     // Check if identical fields
     if (from->result_type() == STRING_RESULT)
     {
+      /*
+        Detect copy from pre 5.0 varbinary to varbinary as of 5.0 and
+        use special copy function that removes trailing spaces and thus
+        repairs data.
+      */
+      if (from->type() == MYSQL_TYPE_VAR_STRING && !from->has_charset() &&
+          to->type() == MYSQL_TYPE_VARCHAR && !to->has_charset())
+        return do_field_varbinary_pre50;
+
       /*
         If we are copying date or datetime's we have to check the dates
         if we don't allow 'all' dates.

--- 1.222/sql/handler.cc	2006-11-09 12:00:32 +01:00
+++ 1.223/sql/handler.cc	2006-11-09 12:00:32 +01:00
@@ -1972,6 +1972,10 @@ int handler::check_old_types()
       {
         return HA_ADMIN_NEEDS_ALTER;
       }
+      if ((*field)->type() == MYSQL_TYPE_VAR_STRING)
+      {
+        return HA_ADMIN_NEEDS_ALTER;
+      }
     }
   }
   return 0;

--- 1.16/mysql-test/r/varbinary.result	2006-11-09 12:00:32 +01:00
+++ 1.17/mysql-test/r/varbinary.result	2006-11-09 12:00:32 +01:00
@@ -26,3 +26,55 @@ select x,xx from t1;
 x	xx
 1	2
 drop table t1;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varbinary(255) default NULL,
+  `b` varchar(255) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select length(a), length(b) from t1;
+length(a)	length(b)
+255	3
+255	3
+CHECK TABLE t1 FOR UPGRADE;
+Table	Op	Msg_type	Msg_text
+test.t1	check	error	Table upgrade required. Please do "REPAIR TABLE `t1`" to fix it!
+REPAIR TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varbinary(255) default NULL,
+  `b` varchar(255) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select length(a), length(b) from t1;
+length(a)	length(b)
+3	3
+3	3
+insert into t1 values("ccc", "ddd");
+select length(a), length(b) from t1;
+length(a)	length(b)
+3	3
+3	3
+3	3
+select hex(a), hex(b) from t1;
+hex(a)	hex(b)
[phone]
[phone]
[phone]
+select concat("'", a, "'"), concat("'", b, "'") from t1;
+concat("'", a, "'")	concat("'", b, "'")
+'aaa'	'ccc'
+'bbb'	'ddd'
+'ccc'	'ddd'
+drop table t1;
+create table t1(a varbinary(255));
+insert into t1 values("aaa   ");
+select length(a) from t1;
+length(a)
+6
+alter table t1 modify a varchar(255);
+select length(a) from t1;
+length(a)
+6

--- 1.9/mysql-test/t/varbinary.test	2006-11-09 12:00:32 +01:00
+++ 1.10/mysql-test/t/varbinary.test	2006-11-09 12:00:32 +01:00
@@ -37,3 +37,43 @@ select x,xx from t1;
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# Bug #19371 VARBINARY() have trailing zeros after upgrade from 4.1
+#
+
+# Test with a saved table from 4.1
+copy_file std_data/bug19371.frm $MYSQLTEST_VARDIR/master-data/test/t1.frm;
+copy_file std_data/bug19371.MYD $MYSQLTEST_VARDIR/master-data/test/t1.MYD;
+copy_file std_data/bug19371.MYI $MYSQLTEST_VARDIR/master-data/test/t1.MYI;
+
+# Everything _looks_ fine
+show create table t1;
+
+# But the length of the varbinary columns are too long
+select length(a), length(b) from t1;
+
+# Run CHECK TABLE, it should indicate table need a REPAIR TABLE
+CHECK TABLE t1 FOR UPGRADE;
+
+# Run REPAIR TABLE to alter the table and repair
+# the varbinary fields
+REPAIR TABLE t1;
+
+# Now check it's back to normal
+show create table t1;
+select length(a), length(b) from t1;
+insert into t1 values("ccc", "ddd");
+select length(a), length(b) from t1;
+select hex(a), hex(b) from t1;
+select concat("'", a, "'"), concat("'", b, "'") from t1;
+
+drop table t1;
+
+# Check that the fix does not affect table created with current version
+create table t1(a varbinary(255));
+insert into t1 values("aaa   ");
+select length(a) from t1;
+alter table t1 modify a varchar(255);
+select length(a) from t1;
+
--- New file ---
+++ mysql-test/std_data/bug19371.MYD	06/11/09 12:00:25

--- New file ---
+++ mysql-test/std_data/bug19371.MYI	06/11/09 12:00:25
 
--- New file ---
+++ mysql-test/std_data/bug19371.frm	06/11/09 12:00:25