How to upgrade MySQL from 4.0.x/4.1.x to 5.0.x?

I've upgraded MySQL on my NetWare 6.5 + SP5 server from the 4.0.26a 
version that comes with NW65SP5 to 5.0.21, the latest version for 
NetWare available from mysql.com.

I've also upgraded Apache to 2.0.59 (from 2.0.54) and phpMyAdmin to 
2.8.2 (from 2.6.4-pl3).  The latter now uses the MySQLi PHP extension as 
supplied with PHP 5.0.5 that comes with NW65SP5.  I've also upgraded the 
MySQL PHP extension and Perl libraries to work with MySQL 5.0.x.

I now notice that when I check Privileges within phpMyAdmin it reports

--begin--
  Warning: Your privilege table structure seems to be older than this 
MySQL version!
Please run the script mysql_fix_privilege_tables that should be included 
in your MySQL server distribution to solve this problem!
---end---

So I've run the above Perl script many times but no matter what I do I 
still get this error.

My data directory has been kept through the different version upgrades 
(I stopped briefly at MySQL 4.0.19 but moved on when having problems 
with the PHP extension).

Moving this data directory to one side and running 'mysql_install_db' to 
create a new database directory which phpMyAdmin does not then complain 
about - correctly listing the privileges.

I've noticed that there are 7 additional tables listed within the mysql 
database from this freshly created database directory: proc, procs_priv, 
time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, 
and time_zone_transition_type.

I've also noticed that whilst my old mysql database has all tables using 
latin1_swedish_ci collation, the newly created one uses a mixture of 
utf8_bin and utf8_general_ci.

Rolling MySQL back to 4.1.19 with the "faulty" database directory 
doesn't result in the error message from phpMyAdmin so it looks like 
it's something to do with going from 4.1.x to 5.0.x.

The MySQL 5.0 manual (section 5.6.2) talks about using 'mysqlcheck 
--check-upgrade --all-databases --auto-repair' followed by 
'mysql_fix_privilege_tables' but this doesn't make any difference.  Some 
of the user comments say running the Perl script twice fixes it (for 
other platforms) that's certainly not working for NetWare.

It seems MySQL 5.0.23 should bring 'mysql_upgrade' to the NetWare 
platform (to be used instead of the above two commands).

Has anyone experienced this error and then subsequently fixed it?

I see there is a blank placeholder document for a HowTo on this very 
subject over on Guenter Knauf's forums.

Many thanks,

Hi,

Simon Flood <S.M.Flood@ucs.cam.ac.uk> wrote in
news:kR2Ag.2883$P35.1181@prv-
forum2.provo.novell.com:

> I see there is a blank placeholder document for a HowTo on this very 
> subject over on Guenter Knauf's forums.
yep, I talked to Rodney who wanted to fill this part in....
but he had probably no time yet....

On 02/08/2006 15:42, Simon Flood wrote:

..snip..

> I now notice that when I check Privileges within phpMyAdmin it reports
> 
> --begin--
>  Warning: Your privilege table structure seems to be older than this 
> MySQL version!
> Please run the script mysql_fix_privilege_tables that should be included 
> in your MySQL server distribution to solve this problem!
> ---end---

..snip..

> Has anyone experienced this error and then subsequently fixed it?

I think that I've now fixed the above by following the Upgrade MySQL 
Systems Tables (MySQL doc, section 5.6.1) procedure for _Windows_.

I downloaded the Windows (32-bit) distribution of MySQL 5.0.22 and used 
the included 'mysql_fix_privilege_tables.sql' script.

phpMyAdmin complained about an error and stopped when I tried importing 
the script via that so I used the MySQL on the server itself.

* Extract 'mysql-x.x.xx-win32/scripts/mysql_fix_privilege_tables.sql' 
from the downloaded 'mysql-noinstall-x.x.xx-win32.zip' to a directory 
_on the server_ (ie. SYS:/)
* At the server prompt (:) enter 'mysql -u root -p'
* Enter root's password when prompted
* At the mysql prompt (mysql>) enter 'use mysql;'
* At the mysql prompt enter 'SOURCE 
<path_to_file>/mysql_fix_privilege_tables.sql' (ie. 'SOURCE 
sys:/mysql_fix_privilege_tables.sql')

After doing the above phpMyAdmin is certainly not complaining when I 
check privileges (with no database selected) when it did before.

I've also noticed that the 'mysql' database now has 17 tables (as 
opposed to 10 previously) and that, with the exception of those tables 
starting 'help_', the collations have changed from 'latin1_swedish_ci' 
to either 'utf8_bin' or 'utf8_general_ci'.

In typing up the above I've just noticed a 
'mysql_prepare_privilege_tables_for_5.sql' script within the Windows 
distribution - should I be running this too/instead?  Haven't found a 
reference to this script in the MySQL 5.0 docs.  Looking at script 
itself it has a comment "merging 'host' table and 'db'".  A Google for

this script suggests that you need to run this script (after 
'mysql_fix_privilege_tables.sql') otherwise you get errors about "user 
XXXX can't logon to localhost" (the hit refers to MySQL on Linux).

HTH

Hi Simon,

Simon Flood <S.M.Flood@ucs.cam.ac.uk> wrote in
news:BJkAg.3720$P35.2631@prv-
forum2.provo.novell.com:

> I think that I've now fixed the above by following the Upgrade MySQL 
> Systems Tables (MySQL doc, section 5.6.1) procedure for _Windows_.
> 
> I downloaded the Windows (32-bit) distribution of MySQL 5.0.22 and used 
> the included 'mysql_fix_privilege_tables.sql' script.
> 
> phpMyAdmin complained about an error and stopped when I tried importing 
> the script via that so I used the MySQL on the server itself.
cool! I've just looked at the scripts, and I think the only reason why 
phpMyAdmin refuses them are those stupid comments; 
I believe if you replace all '--' with '#' it should work:
http://www.gknw.net/development/mysql/scripts/

greetz, Guenter.