Upgrading from 4.0 to 4.1 on Windows

[siftach]

I have been using MySQL 4.0.13 for quite some time now, it seems to be working fine except for two problems:

1. It loses connection when I use Access as a client (Could be a firewall problem).
2. Tables get corrupted.

While checking out MySQL's site, I found out that 4.1 is in no longer in alpha or beta, so I want to know if I should move to 4.1, and if this move will force me to change my client (I use MyODBC 3.51), or should I just move to a newer release of 4.0?

 

[TonyGroves]

I doubt if any of those problems are due to the MySQL version you're using. However, MySQL 4.1 does have several major new features compared to 4.0, so it could well be worth your while changing over. Your existing client programs should work exactly the same with the new server.

 

[dalchri]

Be aware that MySQL 4.1 uses a longer encryption for the user passwords. If you are using MyODBC, you will need to store your passwords like this:

UPDATE mysql.user
SET Password = OLD_PASSWORD('MyPassword')
WHERE User = 'MyUser'

Or I believe that there is a startup option that will force 4.1 to used the older password encryption.

I have noticed that in using MySQL from Access that sometimes my queries are only partly populated but this is usually remedied by running the query once or twice (this is not in production).

 

[siftach]

You noticed this query problem in access when using MySQL 4.1? In 4.0 I never saw such a problem.

 

[siftach]

A fes more question:

1. Do I need to perform any upgrade script for the tables?

2. Why doesn't the installer let me choose my directory? 4.0 is installed in C:\mysql and I want 4.1 to be there as well, but the installer will not let me do it.

3. Assuming I have no choice but to install in a different directory than c:\mysql, if I change the data directory, will the existing users be kept when I set the correct data directory?

 

[TonyGroves]

You don't need to change your data tables at all. As regards the system tables, you can simply copy the old tables across after installation, but as dalchri pointed out, you might have to update the password fields.

The installer should allow you to use a different directory, but in any case, you could instead use the no-installer version, which you can just copy into whatever directory you want, edit my.cnf accordingly, and install the service manually.

 

[lgarner]

4.1 will use the short passwords if it is started with --old-passwords or if old-passwords is in my.cnf. It always understands short passwords, but old clients that change their passwords could lock themselves out.

http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html

That's the only compatibility issue that I know of.