----- Original Message -----
From: "Thomas Andersson"
Sent: Saturday, August 23, 2003 8:15 AM
Subject: Upgrading 3.23->4.0 under WinXP ?
> Hi!
>
> I'm pretty green at this and I'm a bit clueless here, After
reinstalling my
> system I upgraded from 3.23 to 4.0 of MySQL, then I moved my old dbs
in
> there and most seems to work OK. Now PHPMyAdmin tells me my userbase
is out
> of date and that I need to run mysql_fix_privilege_tables...
>
> HOW do I run this on my WinXP system? What else do I need to do?
>
> Is it DOs, should it run from within SQl?
mysql_fix_privilege_tables is a shell script for *nix. You can open it
in a text editor and see the ALTER and UPDATE queries that it would run.
I will post below the ones that I think are needed to upgrade the system
tables from 3.23 -> 4.0. BTW, if you haven't modified the default
permissions or grants on anything, you can just drop the "mysql"
database (delete it from the data directory after stopping MySQL), and
then just reinstall MySQL 4.0 and it will put the correct default tables
there. Again, don't do this if you've changed the default MySQL
permissions (or you don't want to recreate them :-)).
Anyway, here's the queries from mysql_fix_privilege_tables that I think
you need to run on the mysql database (as the root user; and you ignore
any duplicate column errors, etc.):
USE mysql;
alter table user change password password char(16) NOT NULL;
alter table user add File_priv enum('N','Y') NOT NULL;
alter table user add Grant_priv enum('N','Y') NOT NULL,add
References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT
NULL,add Alter_priv enum('N','Y') NOT NULL;
alter table host add Grant_priv enum('N','Y') NOT NULL,add
References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT
NULL,add Alter_priv enum('N','Y') NOT NULL;
alter table db add Grant_priv enum('N','Y') NOT NULL,add References_priv
enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add
Alter_priv enum('N','Y') NOT NULL;
UPDATE user SET
Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,
Alter_priv=Create_priv;
UPDATE db SET
References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri
v;
UPDATE host SET
References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri
v;
ALTER TABLE user
ADD ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL,
ADD ssl_cipher BLOB NOT NULL,
ADD x509_issuer BLOB NOT NULL,
ADD x509_subject BLOB NOT NULL;
alter table user
add Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER alter_priv,
add Super_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_db_priv,
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Super_priv,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Create_tmp_table_priv,
add Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Lock_tables_priv,
add Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Execute_priv,
add Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Repl_slave_priv;
update user set show_db_priv= select_priv, super_priv=process_priv,
execute_priv=process_priv, create_tmp_table_priv='Y',
Lock_tables_priv='Y', Repl_slave_priv=file_priv,
Repl_client_priv=file_priv where user<>"";
alter table user
add max_questions int(11) NOT NULL AFTER x509_subject,
add max_updates int(11) unsigned NOT NULL AFTER max_questions,
add max_connections int(11) unsigned NOT NULL AFTER max_updates;
alter table db
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;
alter table host
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;
FLUSH PRIVILEGES;
> (Everything seems to work except my guestbooks after the reinstall).
Not sure about that not working. :-/
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
