Bug #19371 VARBINARY() have trailing zeros after upgrade from 4.1
Submitted: 26 Apr 2006 10:24 Modified: 11 Dec 2006 8:43
Reporter: Max Kirillov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.21 OS:Linux (Linux)
Assigned to: Magnus Blåudd

[26 Apr 2006 10:24] Max Kirillov
Description:
Values SELECTed from VARBINARY fields contain trailing zero characters, even if they are inserted without them.

This happens on tabled created in mysql-4.1, after server was upgraded to 5.0.

SHOW CREATE TABLE shows completely the same output:
$mysql -uroot -B -e 'show create table db1.t2'
Table   Create Table
t2      CREATE TABLE `t2` (\n  `a` varbinary(255) default NULL\n) ENGINE=MyISAM
DEFAULT CHARSET=latin1
$mysql -uroot -B -e 'show create table db1.t1'
Table   Create Table
t1      CREATE TABLE `t1` (\n  `a` varbinary(255) default NULL\n) ENGINE=MyISAM
DEFAULT CHARSET=latin1

Please, if possible, also tall me how to fix such table in existing versions.

How to repeat:
* install mysql-4.0 (4.1.15-1 in debian)
$mysql -uroot
mysql> create database db1;
mysql> create table db1.t1(a varbinary(255));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('aaa');
Query OK, 1 row affected (0.01 sec)

* upgrade to mysq-5.0 (5.0.20-2 in debian)
$mysql -uroot -B -e 'select * from db1.t1'
a
aaa\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0......

$mysql -uroot -B -e "insert into db1.t1 values('bbb')"
$mysql -uroot -B -e 'select * from db1.t1'
a
aaa\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0......
bbb\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0......

on the contrary, if I create a new table after the upgrade, everything works fine:
$mysql -uroot -B -e 'select * from db1.t2'
a
bbb
[26 Apr 2006 11:18] Valerii Kravchuk
Thank you for a problem report. Have you run CHECK TABLE ... FOR UPGRADE on that table after upgrade? Read http://dev.mysql.com/doc/refman/5.0/en/check-table.html for the details.  If no, please do, and then check for trailing zeros again. Inform about the results.
[26 Apr 2006 11:22] Max Kirillov
mysql> check table db1.t1 for upgrade;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| db1.t1 | check | status   | OK       |
+--------+-------+----------+----------+
1 row in set (0.00 sec)

max@max$mysql -uroot -B -e 'select * from db1.t1'
a
aaa\0\0\0\0\0\0\0\0\0.....
[26 Apr 2006 11:23] Max Kirillov
btw, I saw the same bug on an InnoDB table.
[3 Jun 2006 1:04] Kolbe Kegel
This bug affects both MyISAM and InnoDB.

There are two problems: corrupted data, and strange results for new inserts.

Here is a workaround:

ALTER TABLE t1 ENGINE= /* replace with original engine */;
UPDATE t1 SET varbinary_col=RTRIM(varbinary_col);
[6 Jul 2006 22:02] Christian Boltz
My database was fixed using the ALTER TABLE statement - when I insert new rows, they don't have trailing zeros.

But I can't remove the zeros from existing rows using the recommended
    UPDATE t1 SET varbinary_col=RTRIM(varbinary_col);

Result: 0 rows affected, zeros still alive :-(

(using mysql-5.0.18, SUSE Linux 10.1)
[30 Jul 2006 11:34] Christian Boltz
See also https://bugzilla.novell.com/show_bug.cgi?id=188134 - it includes a working ;-) workaround as well as a patch.
[31 Jul 2006 10:44] Michal Marek
Here is the patch with some leaks fixed. However, it's not optimal, because
a) it addresses the problem in the mysqlcheck program, not in the server's
   REPAIR TABLE implementation (because the server internals are a bit harder
   to lear :))
b) trailing zeros present in the original (mysql 4.x) table are lost.

So I'm asking for an idea how to solve it in a cleaner way rather than
proposing this patch as a solution :)

Now, why can't I attach a file to this bug? Should I inline it base64-encoded??
Here is a link: https://bugzilla.novell.com/show_bug.cgi?id=188134#c12
[31 Jul 2006 10:52] Magnus Blåudd
Thanks for the patches!
[11 Oct 2006 14:05] Michal Marek
Is there any news on this bug? Or some comments on the above patch? I'm thinking of including the above patch in the mysql.rpm package for OpenSUSE 10.2, but I don't want to make our mysql package incompatible with the rest of the world...
[17 Oct 2006 11:42] Magnus Blåudd
In 4.1 all varbinary columns was stored as fixed length padded with zeroes. Thus the "length" of each row of a varbinary(255) was 255.

This was changed in 5.0.3 so that the "length" 
should be set to the actual number of bytes in the field. Thus the max length of each row for a varbinary(255) is 255 but each row will have it's own "length".

When reading a 4.1 format table with 5.0.3 it will interpret the varbinary columns as having length 255, thus the trailing zeroes are displayed.

Using the TRIM and RTRIM commands to remove the trailing spaces in the column is fine if the application don't like them there.

But as the patch will remove any trailing zeroes that might have been put there intentionally we will not add the patch to the MySQL source. As Mikel writes "patch can do more harm than good, because you _can_ have trailing zeros in varbinary fields".

We should write a SQL script(or small binary) that performs these operations on selected tables - as is done in the patch.  The user would then be able to run the script in order to remove the trailing spaces.
[17 Oct 2006 20:15] Christian Boltz
I don't agree with your last comment.

In 4.1, the values were returned _without_ all the trailing zeros, so I would expect that this happens in 5.0 also. Having the trailing zeros in the 5.0 caused some serious problems in PHP applications for me (even when checking the PHP error log because the log messages are cut off at \0 - but that's a different story ;-)

(I never inserted a value including a trailing zero, so I don't know what would have happened in that case.)

> In 4.1 all varbinary columns was stored as fixed length padded with zeroes. 
> Thus the "length" of each row of a varbinary(255) was 255.

I wonder how MySQL could then know the "real" length when the database was queried... - RTRIM?

> When reading a 4.1 format table with 5.0.3 it will interpret the varbinary
> columns as having length 255, thus the trailing zeroes are displayed.

That's the bug. There should be a special handling for upgrades that do not add the trailing zeros (aka "set the correct length info for each row").

Yes, adding compatibility code can be annoying. (I know because I already had to add a workaround to my application because of this bug :-/ )
However, customers complaining about accidently modified data will be more annoying for you ;-)
[18 Oct 2006 2:21] Max Kirillov
What about INSERT'ed data? Padding them is sertainly a bug.

Then, if something in the table metadata makes mysql INSERT wrong data, then it must be possible to detect that the table was created in old versions. So, it is possible to make mysql-5 behave like mysql-4 only for tables that was created in older version (did I understant it right that I cannot have a varbinary with trailing zeroes in mysql-4?).
[18 Oct 2006 13:38] Magnus Blåudd
Thanks for your comments, it is possible to distinguish a table created with mysql-4.1. The 33'd byte of the frm file is set to 5 when created by 5.0

I'll have a second look.
[19 Oct 2006 14:33] Magnus Blåudd
This looks promising, if you have inserted "aaa" in to a table with varbinary created with 4.1, it will store the length of the record as 3.
[19 Oct 2006 14:59] Magnus Blåudd
I canät see any trailing zeros anymore but the length of the varbinary is 255 and it's padded with spaces.

drop table if exists t1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varbinary(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select a, hex(a), length(a) from t1;
a	hex(a)	length(a)
aaa                                                                                                                                                                                                                                                            	616161202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020	255
bbb                                                                                                                                                                                                                                                            	626262202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020	255
[9 Nov 2006 11:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15081

ChangeSet@1.2314, 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.
[29 Nov 2006 22:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16167

ChangeSet@1.2317, 2006-11-29 23:03:45+01:00, msvensson@neptunus.(none) +1 -0
  Bug#19371 VARBINARY() have trailing zeros after upgrade from 4.1
   - chmod the saved files from 4.1 to make sure they are writable
[11 Dec 2006 8:43] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix for 5.0.32 and 5.1.15.
[28 Dec 2006 11:43] Christian Boltz
I just tested this on openSUSE 10.2 final which has, according to Michal Marek, the patch for this bug included (package version: mysql-5.0.26-12).

How I tested: I placed the database files from the tarball (saved from MySQL 4.x, available in comment #1 on https://bugzilla.novell.com/show_bug.cgi?id=141030) in /var/lib/mysql/test/ and restarted MySQL.

Unfortunately, I still got lots of \0 in the varbinary field - both on existing and new entries. Either my testing method is wrong (I used phpMyAdmin and even tried REPAIR TABLE) or your patch doesn't work as expected :-/

Please reopen this bug (I don't have permissions to do it) - or tell me if my testing method is wrong.
[29 Dec 2006 12:45] Michal Marek
I tried 5.0.34-nightly-20061220 and I can also reproduce the problem with this table: https://bugzilla.novell.com/show_bug.cgi?id=188134#c1

However, with the mysql-test/std_data/bug19371.* files or with a manually created table from 4.1.13 (SuLi 10.0 rpm if that matters), the fields are padded with spaces and a REPAIR TABLE `bug19371` fixes it (and trailing zeros from the original table are not destroyed).
[29 Dec 2006 13:06] Michal Marek
But then, with Christian's table, I even get trailing zeros with 4.1.13:

select version(); select hex(`font_path`) from `fonts`; select length(`font_path`) from `fonts`
version()
4.1.13
hex(`font_path`)
2F686F6D652F63622F666F6E746C696E67652F5F666F6E74735F766F6E5F72617474692F4164616D5F735F46616D696C792E74746600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
length(`font_path`)
255

Confused :-)

Christian, can you perhaps remember in which MySQL version was the table created? Or what was the last version when it really worked? <wild guess>Perhaps some older php-mysql cut the trailing zeros, so you didn't notice them?</wild guess>
[2 Jan 2007 9:54] Michal Marek
I see, you created the table with 4.x and then inserted the row with (unpatched) 5.0.x, rigth? This is indeed not fixed. But at least the current fix works fine for data that were inserted with 4.x.