MySQL Bugs: #23143: mysql_upgrade basedir and File

Bug #23143	mysql_upgrade basedir and File_Priv column error message
Submitted:	10 Oct 2006 15:39	Modified:	25 Nov 2006 13:05
Reporter:	Björn Wiberg	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.26	OS:	IBM AIX 5.2, 5.3
Assigned to:		CPU Architecture:	Any

Description:
On upgrade from 5.0.24a to 5.0.26, when running mysql_upgrade, it first complains about basedir (shouldn't it look in /usr/local/mysql by default?), then, efter fixing that by adding --basedir to the command line, it complains that the File_priv column is already there.

Did the script complete with any changes possibly needed? Or did it abort after line 22 (the File_priv complaint)?

None of this happened during previous 5.0.2x --> 5.0.2x "upgrades".
(One is supposed to run mysql_upgrade even on minor upgrades, right?)

Best regards,
Björn

How to repeat:
./mysql_upgrade --force --verbose --password=SECRET

/../
webdevsurveydb.phpsv_surveys                       OK
webdevsurveydb.phpsv_users                         OK
Could not find file mysql_fix_privilege_tables.sql
Please use --basedir to specify the directory where MySQL is installed

 ./mysql_upgrade --force --verbose --basedir=/usr/local/mysql --password=SECRET

/../
wlandatadb.exmpl_tbl                               OK
ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
root@aramus:/mysql/usr/local/mysql/bin#

Suggested fix:
Possible basedir looking in /usr/local/mysql on AIX?

Investigation if the File_priv error message is harmless, and whether the rest of the mysql_fix_privilege_tables.sql contents actually get executed.

Same happen for an upgrade from 4.1 (windows)
And also on the original data/mysql tables from the 5.0.26 distribution.
> mysql_upgrade.exe --basedir=e:\mysql-5.0 --user=root -p --force -v
   [... other tables OK ...]
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'

Same thing happens upgrading from 5.0.21 to 5.0.26 on RH 7.3 linux-i686.  Is it harmless?

-Jonathan

I just ran into the same problem when upgrading a 4.1.21 install to 5.0.26

This is a serious problem since mysql_upgrade is supposed to replace mysql_fix_privilege_tables.

I tried commenting line 22 but then it starts whining about line 28. The error is critical since the program exits on an error instead of just continuing like mysql_fix_privilege_tables does. I noticed this because the mysql.proc and mysql.procs_priv etc. tables weren't created.

The only way i could update my privilege tables was by using mysql_fix_privilege_tables...

I can not repeat the behaviour described with latest 5.0.32-BK and 5.1.14-BK on Linux.

Same problem upgrading from 4.1.22 to 5.0.27 on FreeBSD.

I had to append "--basedir /usr/local" to "mysql_upgrade" in order to get the thing started. Then I also get:

ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'

Trying these forums and Google to find out more...

I ran into this same problem and found a workaround
that hints at the cause of the initial problem.

I believe the true cause of the problem is that
mysql_upgrade is not bothering to pass the --force flag
to mysql when it runs mysql_fix_privilege_tables.sql

The reason I think this is the problem is that 
mysql_fix_privilege_tables.sql stops executing after
encountering the first error (which happens when
upgrading from mysql 4.1 to 5.0 at line 22 since
the column File_priv already exists in the user table
under mysql 4.1)

I commented out line #22 while debugging the problem
and lo and behold, it next stop executing at line
28 (the next time it tries to add a column that already
exists under 4.1).

It should not stop if the --force option were passed
to mysql.  In fact, since the shell script
mysql_fix_privilege_table DOES pass the --force flag
along it works.

Thus, the workaround is to run the following two commands
instead of mysql_upgrade:

mysqlcheck --check-upgrade --all-databases --auto-repair -p
mysql_fix_privilege_table $mysql_root_password

continuing previous comment:
Environment:
    upgrading from mysql 4.1.11 to mysql 5.0.27
    on Fedora Core 6 final (Zod) i386.