MySQL Bugs: #9092: after running mysql_fix_privilege_tables server still uses old password format

Bug #9092	after running mysql_fix_privilege_tables server still uses old password format
Submitted:	10 Mar 2005 13:00	Modified:	26 Jul 2005 4:40
Reporter:	Toon Wijnands	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	4-1-10-standard-log	OS:	Linux (RedHat 9)
Assigned to:		CPU Architecture:	Any

Description:
After upgrading from version 4.0.16 everything seems to work normal. However in the error log the server reports the following warning:

050310 13:28:22 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
/usr/sbin/mysqld: ready for connections.

I've tried running mysql_fix_privilege_tables; the output from this tool appears to be normal (some warnings on duplicates and so on). After a restart of the server, the same message appears in the error log again about disabling new password usage.

running 
mysqladmin -uroot -p variables
shows old_passwords = ON

output from mysql_fix_privilege_tables (more duplicate warnings than after the first run of course):
This scripts updates the mysql.user, mysql.db, mysql.host and the
mysql.func tables to MySQL 3.22.14 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION or want to use the more secure passwords in 3.23

If you get 'Access denied' errors, you should run this script again
and give the MySQL root user password as an argument!
Converting all privilege tables to MyISAM format

If your tables are already up to date or partially up to date you will
get some warnings about 'Duplicated column name'. You can safely ignore these!
ERROR 1060 (42S21) at line 2: Duplicate column name 'File_priv'

Creating Grant Alter and Index privileges if they don't exists
You can ignore any Duplicate column errors
ERROR 1060 (42S21) at line 1: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 2: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 3: Duplicate column name 'Grant_priv'

Setting default privileges for the new grant, index and alter privileges

Adding columns needed by GRANT .. REQUIRE (openssl)
You can ignore any Duplicate column errors
ERROR 1060 (42S21) at line 1: Duplicate column name 'ssl_type'

Creating the new table and column privilege tables
Changing name of columns_priv.Type -> columns_priv.Column_priv
You can ignore any Unknown column errors from this
ERROR 1054 (42S22) at line 1: Unknown column 'Type' in 'columns_priv'

Fixing the func table
You can ignore any Duplicate column errors
ERROR 1060 (42S21) at line 1: Duplicate column name 'type'

Adding new fields used by MySQL 4.0.2 to the privilege tables
You can ignore any Duplicate column errors
ERROR 1060 (42S21) at line 1: Duplicate column name 'Show_db_priv'

Updating new privileges in MySQL 4.0.2 from old ones

ERROR 1060 (42S21) at line 1: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 1: Duplicate column name 'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 4: Duplicate column name 'Create_tmp_table_priv'

How to repeat:
run
mysql_fix_privilege_tables   [root_password]

restart server

check error log

I've added a tar-file of the mysql database.

The same thing happens with the binary Solaris package,
mysql-max-4.1.10a-sun-solaris2.9-sparc, on Solaris9, upgrading
from mysql-max-4.0.1-alpha-sun-solaris2.7-sparc. The only
difference is that I don't see any warnings when
mysql_fix_privilege_tables is run (no matter how many times it
it run).

I was able to successfully run the mysql_fix_privilege_tables script over the database uploaded, ignoring any duplicate errors/warnings, and bring the server up again with no warnings about using the old password format. Also, the server variable "old_passwords" reports "OFF".

Please ensure you are following the upgrading instructions in the manual correctly.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

"ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'" and script dies after that (or completes and does not tell me that it was completed)

Trying to upgrade from 4.1 to 5.0.27 (CentOs using Red Hat Enterprise Linux 4 RPM (AMD64 / Intel EM64T) downloads) 

Hitting the problem... while using "mysql_upgrade"

tried many options ("mysql_upgrade -f -v -uroot -p --basedir=/usr")
and still nothing. I can see it showing the list of tables with " - OK" and then at the end just displays the error message and exits... I am simply not aware if the script completed successfully or not (this could be the major issue because it gives the impression that it failed). 

When I ran 

#mysqlcheck --check-upgrade --all-databases --auto-repair -uroot -p"

I got no errors, and after following:
#mysql_fix_privilege_tables --password=SECRET

also no errors

would help if someone could clarify how come last 2 commands did not produce any errors and first one errored out...isn't mysql_upgrade supposed to simply run last 2 commands (mysqlcheck  and mysql_fix_privilege_tables)?

I was performing the upgrade in the following way:
1) On old server (mysql 4.1)
#service mysql stop

2) Copied binary files to large tar.gz

3) Extracted tar.gz on a new server (not in the running folder of mysql 5)

4) Stopping MYSQL.... On a New server I ran
#service mysql stop

5) Moving 5.0 original data to a different location
#mv /var/lib/mysql /var/lib/mysql_original

6) Moving backed up data to target folder (mysql data folder)
#mv my_backed_up_data /var/lib/mysql

7) making sure permissions are ok
#chown -R mysql.mysql /var/lib/mysql

8) start new server with old data
#service mysql start

9) attempt to run upgrade script
#mysql_upgrade

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

The upgrade is from 4.1 to 5.0 (4.5 Gb database)... I think originally migrated from 4.0 to 4.1 like 2 years ago.