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.
[EMAIL PROTECTED] ~> mysql -u root < mysql-backup-before-upgrade-to-5.sql
[EMAIL PROTECTED] ~> 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
[EMAIL PROTECTED] ~> 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
[EMAIL PROTECTED] ~>
6.
[EMAIL PROTECTED] ~> 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.
[EMAIL PROTECTED] ~> mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: NO)
[EMAIL PROTECTED] ~> 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: [EMAIL PROTECTED]
**** _/ _/ _/ _/ **** 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:[EMAIL PROTECTED]
Sent: Thursday, 9 February 2006 10:16 PM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18
Hi again,
it just does not work.
follows the entire dialog:
[EMAIL PROTECTED] /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 ]
[EMAIL PROTECTED] /home/myuser/software> netstat -ln |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
[EMAIL PROTECTED] /home/myuser/software> rpm -U --nodeps
MySQL-client-standard-5.0.18-0.rhel3.i386.rpm
[EMAIL PROTECTED] /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.
[EMAIL PROTECTED] /home/myuser/software> rpm -U --nodeps
MySQL-devel-standard-5.0.18-0.rhel3.i386.rpm
[EMAIL PROTECTED] /home/myuser/software> which mysql_fix_privilege_tables
/usr/bin/mysql_fix_privilege_tables
[EMAIL PROTECTED] /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
[EMAIL PROTECTED] /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: [EMAIL PROTECTED]
**** _/ _/ _/ _/ **** 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:[EMAIL PROTECTED]
Sent: Thursday, 9 February 2006 6:47 PM
To: Logan, David (SST - Adelaide)
Cc: mysql@lists.mysql.com
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: [EMAIL PROTECTED]
**** _/ _/ _/ _/ **** 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:[EMAIL PROTECTED]
Sent: Thursday, 9 February 2006 6:04 PM
To: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
