Bugzilla – Bug 188134
strange MySQL problem after upgrade
Last modified: 2007-01-08 08:37:45 UTC
After updating SUSE 10.0 to 10.1, I have a strange problem with MySQL: An existing database with a varbinary field returns the value filled up with lots of \0. When I "copy" the table using CREATE TABLE test LIKE fonts; the bug is copied too. When I create the table from the sql-dump, the problem doesn't appear. The problem is also gone after using an ALTER TABLE statement. How to reproduce: * create the table "fonts" using SUSE Linux 10.0 (database: fontlinge) CREATE TABLE `fonts` ( `font_id` int(4) NOT NULL auto_increment, `font_path` varbinary(255) NOT NULL, `font_name` varchar(255) NOT NULL default '', `font_datatype` varchar(4) NOT NULL default '', `font_filetype` varchar(8) NOT NULL default '', `font_kategorie` int(2) default NULL, `font_fullinfo` text, `font_complete` int(1) default NULL, UNIQUE KEY `font_path` (`font_path`), KEY `font_id` (`font_id`), KEY `font_kategorie_name` (`font_kategorie`,`font_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13204; INSERT INTO test set font_path='/home/cb/fontlinge/Adam_s_Family.ttf'; (maybe you can skip some fields, I can't test this unfortunately because I don't have SUSE Linux 10.0 installed any longer) * upgrade to SUSE Linux 10.1 * test with the following PHP script: <?php $ident=mysql_connect( "localhost" , "user" , "secret" ) ; mysql_select_db( "fontlinge" , $ident) ; $query="SELECT * FROM fonts LIMIT 1"; $p=mysql_query($query , $ident); $row = mysql_fetch_array($p); mysql_close($ident); $path=$row['font_path']; error_log("*$path*"); error_log(mysql_escape_string("*$path*")); $path = trim($path); error_log("*$path*"); ?> * check the error_log. You will see: [Sun Jun 25 18:08:46 2006] [error] [client 127.0.0.7] */home/cb/fontlinge/_fonts_von_ratti/Adam_s_Family.ttf [Sun Jun 25 18:08:46 2006] [error] [client 127.0.0.7] */home/cb/fontlinge/_fonts_von_ratti/Adam_s_Family.ttf\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0* [Sun Jun 25 18:08:46 2006] [error] [client 127.0.0.7] */home/cb/fontlinge/_fonts_von_ratti/Adam_s_Family.ttf* Unfortunately, the \0 breaks the Fontlinge PHP scripts and possibly more applications using MySQL. PS: You can probably avoid the "install 10.0, create table, upgrade to 10.1" by copying the binary database files to the MySQL directory. I'll attach a tarball containing them.
Created attachment 91508 [details] database in binary format please extract these files to /var/lib/mysql/fontlinge/ (or whatever test database you use)
This one looks similar: http://bugs.mysql.com/bug.php?id=19371
Yes, MySQL #19371 is exactly this bug. Unfortunately the workaround mentioned there only works partly :-(
Cboltz: have you identfied the exact piece of PHP code that stops working to suggest you a workarounf while this bug get fixed ???
UPDATE fonts SET font_path=REPLACE(font_path,"\\0",''); should wok, or al least works here ;)
Cristian, thanks for your workaround in Comment #5 - works here too :-) I'll add it to my "update database" script. (On the PHP side, a rtrim() call helps, but I would have to change too many scripts, which I don't like - I added a check / warning message instead ;-)
Created attachment 94166 [details] mysql-5.0.22-varbinary-upgrade.patch Hi guys, sorry for the delay. I've a patch basically automating the workaround mentioned in the mysql bug. I'll upload patched RPMs for testing once they're built. Bad news is that this can't be bacported to mysql-5.0.18 because the mysql_upgrade command and the 'FOR UPGRADE' option first appeared in mysql-5.0.19 :-(
As the buildservice needs some more time to rebuild, I've uploaded RPMs for 10.1 here: ftp://ftp.suse.com/pub/people/mmarek/bug188134 eventually, they should appear there too: http://software.opensuse.org/download/home:/michal-m:/mysql/ Please try whether the patch works. Thanks, Michal
Sidenote: I just found a little bug in the changelog: ----------------------------------------------------------------------------- Fri Jun 30 15:17:39 CEST 2006 - mmarek@suse.cz - use --skip-networking when running running the server with ----------------------------------------------------------------------------- Notice the "running running" - it should be just "running". I'll test the fixed packages ASAP, but I need some sleep first ;-)
I just upgraded to the packages from the build service (version 5.0.22-99.1) (ftp.suse.com is missing the mysql-shared package) Results: a) The broken table (as attached to this bug) was fixed, the \0 are gone :-) b) A table that was ALTERed and has old (with \0) and new (without \0) entries still contains \0 in the old entries :-( I'm not sure how this case should be handled correctly because - it will not happen when someone upgrades to a fixed MySQL 5.x - but it happens to all users who updated to SUSE 10.1 and ALTERed their table since then (independend if it was because of this bug or because they simply needed some changes in their table structure)
(In reply to comment #9) > Sidenote: I just found a little bug in the changelog: This will stay there forever, reminding me to allways double-check changelog entries :-) (In reply to comment #10) > I just upgraded to the packages from the build service (version > 5.0.22-99.1) (ftp.suse.com is missing the mysql-shared package) No need to upgrade it for this particular test, I removed it for space reasons. > Results: > > a) The broken table (as attached to this bug) was fixed, the \0 are gone :-) Great! > b) A table that was ALTERed and has old (with \0) and new (without \0) > entries still contains \0 in the old entries :-( The altered table will have current frm version, so it won't be handled. And even for old tables, my patch can do more harm than good, because you _can_ have trailing zeros in varbinary fields. Thanks for the testing, I'll post the patch to the mysql bugtracker. Hope they come up with something better ;-)
Created attachment 94824 [details] mysql-5.0.22-varbinary-upgrade.patch - try2
I think this can be closed. I'll leave the fix as it is now and wait if/whether upstream comes up with something better.
I just tested this on 10.2 final by placing the database files from the tarball (from comment #1) in /var/lib/mysql/test/ and restarting 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 you have broken it again ;-/ (BTW: The bug is fixed upstream now - at least I hope so, I didn't test it.)
10.2 has the fix used by upstream, can you comment on the upstream bug? Thanks. I'll have a look too.
(In reply to comment #15) > 10.2 has the fix used by upstream, can you comment on the upstream bug? Done. > I'll have a look too. An interesting test would be to place the database files from the attached tarball to /var/lib/mysql/ of a MySQL that contains your patch instead of the upstream one - this way we would know if my test method is valid.
(In reply to comment #16) > An interesting test would be to place the database files from the attached > tarball to /var/lib/mysql/ of a MySQL that contains your patch instead of the > upstream one - this way we would know if my test method is valid. Running REPAIR TABLE is definitely a valid method to test the current fix. And yes, my previous path would "fix" your table when running mysql_upgrade, because it blindly deletes all trailing zeroes / spaces (which isn't correct). The problem with your table is that it was already broken by the INSERT under 5.0, so I doubt it can be somehow recovered (eg., you won't see the original data even if you switch back to 4.x). The current patch at least fixes rows that were created with 4.x and it doesn't break anything, so I'd like to set this bug back to FIXED, ok? Let's try solve the rest in the upstream bug.
I just created a database with MySQL 4.0 (sometimes it's good to have an outdated server somewhere ;-) and copied it to my openSUSE 10.2 installation. Result: everything works, no \0 in the table found (tested with entries added in both 4.0 and 5.0) - this means the upstream patch works. It also means that 4.x tables that newer saw a broken MySQL 5.0 will be updated correctly. That's enough for me to set this bug to VERIFIED FIXED. I agree that it's difficult to repair my testcase that was already "broken" by 5.0, but let's see if upstream has an idea ;-) (I don't really care because of my broken table, it's easy to regenerate it with correct data - but there might be users who have more important data in a varbinary)
Also fixed for sles10-sp1.