List:General Discussion« Previous MessageNext Message »
From:suomi Date:February 9 2006 1:03pm
Subject:Re: Failed to upgrade from 4.1.16 to 5.0.18
View as plain text  
Hi Dave
i tried a new proceeding, which seems to work:

1.
before upgrading i did:
mysqldump -A --all -uroot -p > mysql-backup-before-upgrade-to-5.sql

2.
i moved the present database away.

3.
i did the upgrade as described many messages before.

4.
i created a new database.

5.
root@myhost ~> mysql -u root < mysql-backup-before-upgrade-to-5.sql
root@myhost ~> mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

localhost.(none)>quit
root@myhost ~> mysql_fix_privilege_tables --user root --verbose
This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

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

You can safely ignore all 'Duplicate column' and 'Unknown column' errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!

ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type'
ERROR 1060 (42S21) at line 70: Duplicate column name 'Routine_type'
ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 100: Duplicate column name 'type'
ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 137: Duplicate column name 
'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 140: Duplicate column name 
'Create_tmp_table_priv'
ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor'
ERROR 1054 (42S22) at line 247: Unknown column 'Create_view_priv' in 
'where clause'
ERROR 1054 (42S22) at line 277: Unknown column 'Create_routine_priv' in 
'where clause'
ERROR 1054 (42S22) at line 313: Unknown column 'Create_user_priv' in 
'where clause'
done
root@myhost ~>


6.
myuser@myhost ~> mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

localhost.(none)> flush privileges;
Query OK, 0 rows affected (0.01 sec)

localhost.(none)> quit
Bye


7.
myuser@myhost ~> mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using 
password: NO)
myuser@myhost ~> mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

localhost.(none)>



8.
this seems to work now, is far from being elegant and straight-forward, 
but works.
i would like to have a more straight-forward method.


suomi



Logan, David (SST - Adelaide) wrote:

>Have you tried it without a password? eg. just --user root Are you able
>to connect with the mysql client?
>
>Sorry, I don't know a lot about rpm and how it works as I work on
>Solaris and use the .tar.gz versions. Hopefully somebody with
>familiarity with rpm can help out here? I'd be interested to know what
>is happening. I have noticed the message about setting root password,
>maybe it thinks this is a new install?
>
>Regards
>
>
>---------------------------------------------------------------
>********** _/     **********  David Logan 
>*******   _/         *******  ITO Delivery Specialist - Database
>*****    _/            *****  Hewlett-Packard Australia Ltd
>****    _/_/_/  _/_/_/  ****  E-Mail: david.logan@stripped
>****   _/  _/  _/  _/   ****  Desk:   +618 8408 4273
>****  _/  _/  _/_/_/    ****  Mobile: 0417 268 665
>*****        _/       ******    
>******      _/      ********  Postal: 148 Frome Street,
>********   _/     **********          Adelaide SA 5001
>                                      Australia 
>i    n    v    e    n    t                                   
>---------------------------------------------------------------
>
>-----Original Message-----
>From: suomi [mailto:mysql@stripped] 
>Sent: Thursday, 9 February 2006 10:16 PM
>To: Logan, David (SST - Adelaide)
>Cc: mysql@stripped
>Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18
>
>Hi again,
>
>it just does not work.
>
>follows the entire dialog:
>
>root@myhost /home/myuser/software> rpm -U --nodeps 
>MySQL-server-standard-5.0.18-0.rhel3.i386.rpm
>Giving mysqld a couple of seconds to exit nicely
>PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
>To do so, start the server, then issue the following commands:
>/usr/bin/mysqladmin -u root password 'new-password'
>/usr/bin/mysqladmin -u root -h myhost.mydom.com password 'new-password'
>See the manual for more instructions.
>
>NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run
>the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
>able to use the new GRANT command!
>
>Please report any problems with the /usr/bin/mysqlbug script!
>
>The latest information about MySQL is available on the web at
>http://www.mysql.com
>Support MySQL by buying support/licenses at https://order.mysql.com
>Starting MySQL[ OK ]
>root@myhost /home/myuser/software> netstat -ln |grep 3306
>tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
>root@myhost /home/myuser/software> rpm -U --nodeps 
>MySQL-client-standard-5.0.18-0.rhel3.i386.rpm
>root@myhost /home/myuser/software> rpm -U --nodeps 
>MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm
>/sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty,
>
>not checked.
>/sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty,
>
>not checked.
>root@myhost /home/myuser/software> rpm -U --nodeps 
>MySQL-devel-standard-5.0.18-0.rhel3.i386.rpm
>root@myhost /home/myuser/software> which mysql_fix_privilege_tables
>/usr/bin/mysql_fix_privilege_tables
>root@myhost /home/myuser/software> mysql_fix_privilege_tables --user 
>root --password=pppppp --verbose
>This script updates all the mysql privilege tables to be usable by
>MySQL 4.0 and above.
>
>This is needed if you want to use the new GRANT functions,
>CREATE AGGREGATE FUNCTION, stored procedures, or
>more secure passwords in 4.1
>
>You can safely ignore all 'Duplicate column' and 'Unknown column' errors
>because these just mean that your tables are already up to date.
>This script is safe to run even if your tables are already up to date!
>
>ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using 
>password: YES)
>Got a failure from command:
>cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql 
>--no-defaults --force --user=root --host=localhost --password=pppppp 
>--database=mysql
>Please check the above output and try again.
>
>If you get an 'Access denied' error, you should run this script again
>and
>give the MySQL root user password as an argument with the --password=
>option
>root@myhost /home/myuser/software>
>
>
>suomi
>
>
>Logan, David (SST - Adelaide) wrote:
>
>  
>
>>Hi suomi,
>>
>>    
>>
>>From my understanding (and I have just performed it myself about 2
>hours
>  
>
>>ago on one of my servers), I stopped the server, took a backup of the
>>data directories, built the new server and put it in place (same as
>>    
>>
>your
>  
>
>>rpm step), started the server, ran the fix script as
>>
>>/usr/local/mysql/bin/mysql_fix_privilege_tables
>>--basedir=/usr/local/mysql --user=root --password=xxxxxx --verbose
>>
>>It ran ok. I stopped and started the server and away it went with no
>>problems.
>>
>>Regards
>>
>>
>>
>>---------------------------------------------------------------
>>********** _/     **********  David Logan 
>>*******   _/         *******  ITO Delivery Specialist - Database
>>*****    _/            *****  Hewlett-Packard Australia Ltd
>>****    _/_/_/  _/_/_/  ****  E-Mail: david.logan@stripped
>>****   _/  _/  _/  _/   ****  Desk:   +618 8408 4273
>>****  _/  _/  _/_/_/    ****  Mobile: 0417 268 665
>>*****        _/       ******    
>>******      _/      ********  Postal: 148 Frome Street,
>>********   _/     **********          Adelaide SA 5001
>>                                     Australia 
>>i    n    v    e    n    t                                   
>>---------------------------------------------------------------
>>
>>-----Original Message-----
>>From: suomi [mailto:mysql@stripped] 
>>Sent: Thursday, 9 February 2006 6:47 PM
>>To: Logan, David (SST - Adelaide)
>>Cc: mysql@stripped
>>Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18
>>
>>Hi Dave
>>i did so, but probably in the wrong moment. i got "Access denied" when
>>    
>>
>i
>  
>
>>run the script.
>>you mean that this should have been the very first step after the
>>upgrade.
>>
>>suomi
>>
>>Logan, David (SST - Adelaide) wrote:
>>
>> 
>>
>>    
>>
>>>Hi,
>>>
>>>Did you run the bin/mysql_fix_privilege_tables script? This is
>>>documented in the upgrade procedures on the website under the
>>>installation chapter.
>>>
>>>Regards
>>>
>>>
>>>---------------------------------------------------------------
>>>********** _/     **********  David Logan 
>>>*******   _/         *******  ITO Delivery Specialist - Database
>>>*****    _/            *****  Hewlett-Packard Australia Ltd
>>>****    _/_/_/  _/_/_/  ****  E-Mail: david.logan@stripped
>>>****   _/  _/  _/  _/   ****  Desk:   +618 8408 4273
>>>****  _/  _/  _/_/_/    ****  Mobile: 0417 268 665
>>>*****        _/       ******    
>>>******      _/      ********  Postal: 148 Frome Street,
>>>********   _/     **********          Adelaide SA 5001
>>>                                    Australia 
>>>i    n    v    e    n    t                                   
>>>---------------------------------------------------------------
>>>
>>>-----Original Message-----
>>>From: suomi [mailto:mysql@stripped] 
>>>Sent: Thursday, 9 February 2006 6:04 PM
>>>To: mysql@stripped
>>>Subject: Failed to upgrade from 4.1.16 to 5.0.18
>>>
>>>Hi listers
>>>
>>>1.
>>>i did the upgrad as i usually did so far:
>>>/etc/init.d/mysql stop
>>>rpm -U MySQL-server-standard-5.0.18-0.rhel3.i386.rpm
>>>rpm -U MySQL-client-standard-5.0.18-0.rhel3.i386.rpm
>>>rpm -U MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm
>>>
>>>   
>>>
>>>      
>>>
>>>from then on: no chance to enter the database via whatever interface: 
>> 
>>
>>    
>>
>>>always Access denied for user .... on host ...
>>>
>>>2.
>>>i checked to see, whether the mysql userid had sufficient access 
>>>privileges to all database files: all access was ok.
>>>i also checked the error log myhost.mydomain.tld.err: no hint why
>>>   
>>>
>>>      
>>>
>>access
>> 
>>
>>    
>>
>>>was denied.
>>>
>>>3.
>>>i then went back to 4.1.16, entered the database (no problem) and for 
>>>the root user created the long password and intentionally did a flush 
>>>privileges.
>>>
>>>4.
>>>then i re-did the above (under 1) upgrade steps again to the same
>>>   
>>>
>>>      
>>>
>>event:
>> 
>>
>>    
>>
>>>Access denied.
>>>
>>>5.
>>>i then took the procedure described in 
>>>http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html
>>>while in the procedure, when i wanted to enter the flush privileges, 
>>>again access denied.
>>>
>>>6.
>>>i checked to see, whether there is some upgrade procedure to follow, 
>>>where privilege tables are upgraded (as from 3 to 4), and i found
>>>      
>>>
>none.
>  
>
>>>   
>>>
>>>      
>>>
>> 
>>
>>    
>>
>>>i read the README even more carefully: nothing found.
>>>
>>>7.
>>>back to 4.1.16
>>>
>>>
>>>which essential step did i omitt?
>>>
>>>
>>>suomi
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>   
>>>
>>>      
>>>
>> 
>>
>>    
>>
>
>  
>

Thread
Failed to upgrade from 4.1.16 to 5.0.18suomi9 Feb
RE: Failed to upgrade from 4.1.16 to 5.0.18SST - Adelaide)9 Feb
  • Re: Failed to upgrade from 4.1.16 to 5.0.18suomi9 Feb
RE: Failed to upgrade from 4.1.16 to 5.0.18SST - Adelaide)9 Feb
  • Re: Failed to upgrade from 4.1.16 to 5.0.18suomi9 Feb
  • Re: Failed to upgrade from 4.1.16 to 5.0.18suomi9 Feb
RE: Failed to upgrade from 4.1.16 to 5.0.18SST - Adelaide)9 Feb
  • Re: Failed to upgrade from 4.1.16 to 5.0.18suomi9 Feb