Bug #21011 mysql_upgrade fails on malformed upgrade_defaults file
Submitted: 12 Jul 2006 17:40 Modified: 19 Sep 2006 16:44
Reporter: Matthew North Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.11-beta OS:FreeBSD (FreeBSD 5.5-STABLE)
Assigned to: Bugs System CPU Architecture:Any

[12 Jul 2006 17:40] Matthew North
Description:
Upgrading MySQL 5.0.22 to 5.1.11-beta fails on execution of the mysql_upgrade program:
#mysql_upgrade --basedir=/usr/local --datadir=/web/data --user=root --password=<PASSWORD> --verbose
Running /usr/local/bin/mysqlcheck --defaults-extra-file=/web/data/upgrade_defaults --check-upgrade --all-databases --auto-repair --user=root
error: Found option without preceding group in config file: /web/data/upgrade_defaults at line: 2
Fatal error in defaults handling. Program aborted
Error executing '/usr/local/bin/mysqlcheck --defaults-extra-file=/web/data/upgrade_defaults --check-upgrade --all-databases --auto-repair --user=root'
#                                     

mysql_upgrade deletes the offending 'upgrade_defaults' file when it exits, so I modified the source to leave it and discovered that the contents of upgrade_defaults are:

# cat upgrade_defaults

password=<PASSWORD>
#

Doesn't that need to have something like '[client]' above it?  It looks like the mysql_upgrade source intends to do just that, but fails:

  buffer_end= strnmov(buffer, "\n[client]", sizeof(buffer));
  if (opt_password)
    buffer_end= strxnmov(buffer, sizeof(buffer),
                         "\npassword=", opt_password, NullS);

NOTE: Both versions of MySQL were installed via freebsd ports: databases/mysql50-(client|server) and databases/mysql51-(client|server), although that latter doesn't appear to have any patches for mysql_upgrade that would cause this problem.

How to repeat:
On our development system this is reliably reproducible under the following circumstances:

Starting with mysql 4.1.x installed with basedir=/usr/local/mysql and datadir=/web/data

- stop server and install FreeBSD port databases/mysql50-server into the normal location, basedir=/usr/local (note that since mysql4 was installed in /usr/local/mysql, this duplicate installation *should* be ok)

- start server and run mysql_upgrade with appropriate options -- this works ok.

- stop server, deinstall port databases/mysql50-server, and install databases/mysql51-server (again, this goes into the default location, basedir=/usr/local)

- start server and run mysql_upgrade as shown in the description

This is when the error occurs.  Note that the error also occurred when upgrading directly from mysql41 to mysql51, so the intermediate upgrade to mysql50 might not be necessary to reproduce.

Suggested fix:
Aside from simply fixing mysql_upgrade so that it creates upgrade_defaults properly, alter it so that if it fails it doesn't delete things that could be useful in determining the problem (although I know leaving files lying around that contain passwords in clear-text is bad).
[12 Jul 2006 18:02] Matthew North
I constructed an upgrade_defaults file by hand containing:

[client]
password=<PASSWORD>

and then altered mysql_upgrade.c to use the file already there instead of clobbering it, and mysql_upgrade then succeeded without error.

gravity$ diff -u mysql_upgrade.c ~/mysql_upgrade.c
--- mysql_upgrade.c     Fri Jun  2 11:01:24 2006
+++ /home/mnorth/mysql_upgrade.c        Wed Jul 12 11:00:29 2006
@@ -151,6 +151,12 @@
   char *buffer_end;
   int error;

+  /* MHN HACK */
+  if (1) {
+         upgrade_defaults_created= 1;
+         return 0;
+       }
+
   /* check if the defaults file is needed at all */
   if (!opt_password)
     return 0;
[14 Jul 2006 11:16] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with MySQL binaries, not with FreeBSD ports, and inform about the results. 

I was not able to repeat the behaviour described with 5.0.x upgrade to 5.1.12-BK on Linux. The following:

bin/mysql_upgrade -uroot --basedir=/home/openxs/dbs/5.1 --datadir=/home/openxs/dbs/5.0/var/ --verbose

wroked just as expected.
[21 Jul 2006 21:31] Christian Hammers
Matthew has even pointed out the buggy code (which I was about to report, too :)) and it makes it clear that the bug only occurs if there is a "password=" option in the "[client]" section of e.g. /root/.my.cnf.

The variable buffer contains the content for the to-be-created /var/lib/mysql/upgrade_defaults and strxnmov does always overwrite and never append to this variable. So when opt_password happens to be present the buffer which already contains "[client]\n" gets overwritten.

bye,

-christian-
[24 Jul 2006 16:41] Matthew North
(1) The FreeBSD port simply downloads the latest MySQL source archive and compiles from there.  It does apply some patches, but none of them effect mysql_upgrade.c, so I don't think compiling from non-port sources is going to solve the problem; (2) We are attempting to move AWAY from non-port sources TO FreeBSD ports, so we're less than eager to go the other direction; (3) We have decided to postpone upgrading for other reasons (client 3rd-party software is not yet compatible with 5.x).  In light of these issues, we can't rationalize additional man-hours pursuing this apparent bug.  I will only add that a 2nd freebsd system DID successfully upgrade from 4.1 to 5.1.11-beta via ports, without experiencing this issue.  So it does seem to have something to do with a perhaps unusual mysql configuration somewhere on the failing system (none-the-less, there is no rationale for an upgrade to result in creation of a configuration file with invalid syntax -- at the very least this situation should be caught and cause a graceful exit).

Good luck!
[8 Aug 2006 6:12] Lachlan Mulcahy
verified.
[8 Aug 2006 20:23] Richard McLane
I'm running into this same problem on RHEL4.  I installed 5.0.24 from RPMs and tried to run mysql_upgrade and get the same error.

Additionally, the default basedir variable is incorrect.  I have to use --basedir=/usr for it to even find the mysqlcheck command.
[11 Aug 2006 13:43] Pooly
Hi,

Same problem with Mandrake 9.2 and the generic RPM for 5.0.24.
#mysql_upgrade --basedir=/usr --datadir=/var/lib/mysql -u root -ppassword
error: Found option without preceding group in config file: /var/lib/mysql/upgrade_defaults at line: 2
[14 Aug 2006 8:38] Björn Wiberg
Same thing on AIX 5.3, using the MySQL 5.0.24 32-bit binaries for AIX 5.2 from the MySQL website:

bash-3.00# ./mysql_upgrade --force --verbose --password=SECRET
Running /usr/local/mysql/bin/mysqlcheck --defaults-extra-file=/usr/local/mysql/data/upgrade_defaults --check-upgrade --all-databases --auto-repair --user=root
error: Found option without preceding group in config file: /usr/local/mysql/data/upgrade_defaults at line: 2
Fatal error in defaults handling. Program aborted
Error executing '/usr/local/mysql/bin/mysqlcheck --defaults-extra-file=/usr/local/mysql/data/upgrade_defaults --check-upgrade --all-databases --auto-repair --user=root'
bash-3.00#

Never had any problems with mysql_upgrade before.

Although I suspect that the upgrade script is more or less only necessary when doing "major" upgrades, not minor ones (here from 5.0.22 to 5.0.24). Any comments on that?

Best regards,
Björn
[14 Aug 2006 8:41] Björn Wiberg
Of course, running:

./mysqlcheck --check-upgrade --all-databases --auto-repair --user=root --password=SECRET

...manually works fine (as a work-around).

Best regards,
Björn
[7 Sep 2006 21:50] David Sparks
Confirmed that this bug exists on Gentoo mysql-5.0.24-r1 (unreleased)

Successfully used this workaround:
mysqlcheck --check-upgrade --all-databases --auto-repair --user=root --password=***
[12 Sep 2006 0:59] 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/11743

ChangeSet@1.2262, 2006-09-12 04:59:13+04:00, ted@ted.mysql.internal +1 -0
  Bug#21011 The [client] group header was overwritten by "password" option in upgrade_defaults file
  which confised mysqlcheck thus used to prevent the whole thing from running correctly
  when the "--password=" option is specified on mysql_upgrade command line with no
  correspondidng "--user=" option
[12 Sep 2006 22:29] 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/11809

ChangeSet@1.2262, 2006-09-13 02:29:35+04:00, ted@ted.mysql.internal +1 -0
  Bug#21011 The [client] group header was overwritten by "password" option in upgrade_defaults file which confused mysqlcheck thus used to prevent the whole thing from running correctly when the "--password=" option is specified on mysql_upgrade command line with no corresponding "--user=" option
[15 Sep 2006 0:01] 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/11977

ChangeSet@1.2265, 2006-09-15 04:01:47+04:00, ted@ted.mysql.internal +1 -0
  Bug#21011 The [client] group header was overwritten by "password" option in upgrade_defaults file which confused mysqlcheck thus used to prevent the whole thing from running correctly when the "--password=" option is specified on mysql_upgrade command line with no corresponding "--user=" option
[15 Sep 2006 1:47] Vladimir Shebordaev
Pushed to -maint team trees (expected to be in 5.0.26 and 5.1.12)
[15 Sep 2006 22:22] Chad MILLER
Available in 5.1.12-beta.
[15 Sep 2006 23:27] Paul Dubois
Noted in 5.1.12 changelog.

mysql_upgrade produced a malformed upgrade_defaults file by overwriting the [client] group header with a password option. This
prevented mysqlcheck from running successfully when invoked by mysql_upgrade.

Resetting report to NDI pending merge into 5.0.
[18 Sep 2006 21:25] Timothy Smith
pushed to 5.0.26
[19 Sep 2006 16:44] Paul Dubois
Noted in 5.0.26 changelog.