MySQL 4.0 to 4.1 upgrade

Submitted by chris on December 8, 2006 - 01:03. MySQL Install

Upgrade MySQL 4.0.x to 4.1.x

In my case I will be upgrading from 4.0.27. As a general rule, MySQL recommends that when upgrading from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 rather than to 4.1 or 5.0.

Before upgrading you might want to test (for a few days?), see: MySQL 4.0 to 4.1 Upgrade Testing Mode
Also read: Notable Change in MySQL 4.1

1) Prepare a new my.ini for your new 4.1 Install.
Test your new my.ini on a test PC running of MySQL 4.1. Now or later see if you can take advantage of new settings introduced with MySQL 4.1.
Centralize all your files into a folder like: "ForMySQL41upgrade" (my.ini, 3 batch files for backup/restore/fix_priv, install files, time zone table).
If you pick the default location you can already update your "basedir" like: basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
If applicable be sure to adjust "datadir" and "tmpdir":
datadir = C:\mysql\41\data
tmpdir = C:/mysql/41/tmp

2) Backup your data see: mysqldump - MySQL Database Backup Program
Test importing your data into a test MySQL 4.1 server running with your planned my.ini.
If you use accents, make sure they are converting well. If not you might want to try importing your data with the options: --default-character-set=utf8
Like:
"C:\Program Files\MySQL\MySQL Server 4.1\bin\mysql" -uroot --password=YourPassword -f --default-character-set=utf8 < "E:\_UpgradeMySQL\BACKUP_MYSQL_4.0.SQL"

3) You have to decide which install method you will be using:
- No-Installer Zip file (basically copy the file to a new location or over the existing one + Install MySQL Service)
- Installation Wizard (Remember to remove the old Mysql service if you will be using the wizard and will be replacing your current install)

Download MySQL 4.1

Here are detailed step to do this upgrade (I've actually ran this method on a production server):

a.1) Have all your strategy and downloads ready (New 4.1 version with install options in mind, New my.ini file, Post-Install steps, New Time_Zone tables).

a.2) Backup all tables with mysqldump or using your custom batch file. Before you might want to and bind to local address only or disable network "skip-networking" or better only enable writes from super-user only with read_only in my.ini and Restart MySQL 4.0)

a.3) Stop MySQL 4.0.x
You can also install 4.1 using a different port (ie like 3007 and install with a different service name ) so you can keep MySQL 4.0 running in read-only mode.
If you do this you will have to adjust your "restore batch file" to account for MySQL 4.1 being on a non-standard port (3007?).

a.4) Do a file system copy of MySQL + Data + my.ini (unnecessary step but just a precaution)
a.5) Install MySQL 4.1 using the wizard (to a new location), if applicable make sure the folders defined in your my.ini exists or adjust these values to your new 4.1 install:
basedir = FullPathToYour41Install
datadir = E:\mysql\41\data
tmpdir = E:/TmpScript/mysql41
If "datadir " is different then the default 4.1, move/copy the content of your new "x:\FullPathToYour41Install\data" to your "datadir" location.

a.5) Backup current MySQL 4.1 my.ini file (default location: C:\Program Files\MySQL\MySQL Server 4.1\my.ini) and overwrite with the one you prepared previously.
a.6) Temporarily tweak my.ini to bind only to local address only (bind-address=localhost) to avoid clients accessing it from the network or better only enable writes from super-user with read_only in my.ini.
a.7) Start/Restart MySQL 4.1
a.8) Restore all tables using your prepared batch file (if you loose your accents see note at: mysqldump - MySQL Database Backup Program)
a.9) Perform post install tasks: execute mysql_fix_privilege_tables.sql (prepare this in a batch file with full paths will also save you time) + copy time zones tables
Your Done!

With this method if anything goes wrong simply stop MySQL 4.1 modify the MySQL 4.0 my.ini to to a non-default port and start.
If for some reason certain databases to not perform well until something is modified, you can temporarily run 4.1 and 4.0 concurrently

Note: To disable network access to the server at to my.ini
bind-address=localhost

Post Upgrade

After upgrading, update the grant tables to obtain the new longer Password column that is needed for more secure handling of passwords. If you do not do this, MySQL does not use the new more secure protocol to authenticate. Here is the steps for Windows Users:
On Windows systems, there isn't an easy way to update the system tables until MySQL 4.0.15. From version 4.0.15 on, MySQL distributions include a mysql_fix_privilege_tables.sql SQL script that you can run using the mysql client. For example, if your MySQL installation is located at C:\Program Files\MySQL\MySQL Server 4.1, the commands look like this:

C:\> cd "C:\Program Files\MySQL\MySQL Server 4.1"
C:\> bin\mysql -u root -p yourpassword
mysql> SOURCE scripts/mysql_fix_privilege_tables.sql

alternatively create a batch file like fix41.bat with:
mysql -uroot -pYourPawword -f mysql < "C:\Program Files\MySQL\MySQL Server 4.1\scripts\mysql_fix_privilege_tables.sql"

The mysql command will prompt you for theroot password; enter it when prompted.

If your installation is located in some other directory, adjust the pathnames appropriately.

As with the Unix procedure, you might see some Duplicate column name warnings as mysql processes the statements in the mysql_fix_privilege_tables.sql script; you can ignore them. Note that excuting that script also should update your "mysql" system database with the changes for 4.1 ( create help tables, time_zone tables etc...).

After running the script, stop the server and restart it.
Reference: Section 5.5.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.

You can verify that the script worked but inspecting the mysql->user table. The password field should now be VARCHAR(41) versus VARCHAR(16)

---

Note: If you have people connecting with old 4.0 clients, they might have problems connecting to the new improved longer passwords. In this case you can set the option --old-passwords (to still accept the old authentication passwords, until all your clients are upgraded to 4.1).
Symptoms of this error: Client does not support authentication protocol

MySQL 4.1.3 introduces support for per-connection time zones. The previous step should have created the time zone tables in the mysql database. Now you simply need to fill in these tables data. ForWindows users:

Download the raw tables: http://dev.mysql.com/downloads/timezones.html
Un-pack
Stop MySQL Server
Copy the un-packed time_zone tables to existing empty ones in the "mysql" system tables folder. The folder called "mysql" in your data folder.
Start MySQL Server

Reference: Section 5.10.8, “MySQL Server Time Zone Support”

To use new functionality within PHP will need to use the new MySQL DLL. MySQL Functions

The main issue which upgrade to 4.1 is the character set. This can be easily resolve when importing your data to 4.1 (see mysqldump - MySQL Database Backup Program). However the default collation utf8_unicode_ci assume "e" and "é" are the same (that collation like most are accent insensitive). For example, this is the default behavior of Google. If you search for "Hélène" results with "Helene" will also match.

This can be a problem if you have a field used as a "unique index" with "Hélène" and "Helene". Before 4.1 no issue, after the4.1 upgrade problem these 2 values are considered the same (and you might have a problem importing your data).

I personally like this new behavior since they should be considered the same value. The second user "Hélène" already had her userID reserved by "Helene" and should have been banned from sign-up with a duplicate userid warning. It is the same name. To import you data can keep the "accent insensitive" collation you will have to change those instances. I change "Hélène" to "Hélène2" and told her about it.

I think you can get around this issue by switching the collation on your table to "utf8_bin". The only problem is that this collation is also case sensitive...ouch!

References

Upgrading from MySQL 4.0 to 4.1

Upgrading MySQL

http://dev.mysql.com/doc/refman/4.1/en/windows-upgrading.html

Other links (just in case I have further issues)

Post-Installation Setup and Testing

Drupal notes:

Database only upgrade from MySQL 4.0.x to 4.1.x

Going from MySQL 4.1 back down to 4.0

http://drupal.org/node/85171

http://drupal.org/node/84214

http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

» add new comment | printer friendly version |

delicious | digg

digg |

reddit |

google |

technorati

Topics

MySQL Featured

About chris

MySQL 4.0 to 4.1 upgrade

Topics Windows 2003 Apache MySQL PHP IIS Drupal Web Promotion Technical Support SiteBuddy Downloads Forums MySQL Featured How MySQL uses memory used thread_cache_size thread_concurrency MySQL 4.0 to 4.1 Upgrade Testing Mode Install MySQL 4.1.x Notable Changes in MySQL 4.1 MySQL character sets and collation explained mysqldump - MySQL Database Backup Program MySQL 4.0 to 4.1 upgrade About chris Full name Christophe D View full user profile	Home MySQL 4.0 to 4.1 upgrade Submitted by chris on December 8, 2006 - 01:03. MySQL Install Upgrade MySQL 4.0.x to 4.1.x In my case I will be upgrading from 4.0.27. As a general rule, MySQL recommends that when upgrading from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 rather than to 4.1 or 5.0. Before upgrading you might want to test (for a few days?), see: MySQL 4.0 to 4.1 Upgrade Testing Mode Also read: Notable Change in MySQL 4.1 1) Prepare a new my.ini for your new 4.1 Install. Test your new my.ini on a test PC running of MySQL 4.1. Now or later see if you can take advantage of new settings introduced with MySQL 4.1. Centralize all your files into a folder like: "ForMySQL41upgrade" (my.ini, 3 batch files for backup/restore/fix_priv, install files, time zone table). If you pick the default location you can already update your "basedir" like: basedir="C:/Program Files/MySQL/MySQL Server 4.1/" If applicable be sure to adjust "datadir" and "tmpdir": datadir = C:\mysql\41\data tmpdir = C:/mysql/41/tmp 2) Backup your data see: mysqldump - MySQL Database Backup Program Test importing your data into a test MySQL 4.1 server running with your planned my.ini. If you use accents, make sure they are converting well. If not you might want to try importing your data with the options: --default-character-set=utf8 Like: "C:\Program Files\MySQL\MySQL Server 4.1\bin\mysql" -uroot --password=YourPassword -f --default-character-set=utf8 < "E:\_UpgradeMySQL\BACKUP_MYSQL_4.0.SQL" 3) You have to decide which install method you will be using: - No-Installer Zip file (basically copy the file to a new location or over the existing one + Install MySQL Service) - Installation Wizard (Remember to remove the old Mysql service if you will be using the wizard and will be replacing your current install) Download MySQL 4.1 Here are detailed step to do this upgrade (I've actually ran this method on a production server): a.1) Have all your strategy and downloads ready (New 4.1 version with install options in mind, New my.ini file, Post-Install steps, New Time_Zone tables). a.2) Backup all tables with mysqldump or using your custom batch file. Before you might want to and bind to local address only or disable network "skip-networking" or better only enable writes from super-user only with read_only in my.ini and Restart MySQL 4.0) a.3) Stop MySQL 4.0.x You can also install 4.1 using a different port (ie like 3007 and install with a different service name ) so you can keep MySQL 4.0 running in read-only mode. If you do this you will have to adjust your "restore batch file" to account for MySQL 4.1 being on a non-standard port (3007?). a.4) Do a file system copy of MySQL + Data + my.ini (unnecessary step but just a precaution) a.5) Install MySQL 4.1 using the wizard (to a new location), if applicable make sure the folders defined in your my.ini exists or adjust these values to your new 4.1 install: basedir = FullPathToYour41Install datadir = E:\mysql\41\data tmpdir = E:/TmpScript/mysql41 If "datadir " is different then the default 4.1, move/copy the content of your new "x:\FullPathToYour41Install\data" to your "datadir" location. a.5) Backup current MySQL 4.1 my.ini file (default location: C:\Program Files\MySQL\MySQL Server 4.1\my.ini) and overwrite with the one you prepared previously. a.6) Temporarily tweak my.ini to bind only to local address only (bind-address=localhost) to avoid clients accessing it from the network or better only enable writes from super-user with read_only in my.ini. a.7) Start/Restart MySQL 4.1 a.8) Restore all tables using your prepared batch file (if you loose your accents see note at: mysqldump - MySQL Database Backup Program) a.9) Perform post install tasks: execute mysql_fix_privilege_tables.sql (prepare this in a batch file with full paths will also save you time) + copy time zones tables Your Done! With this method if anything goes wrong simply stop MySQL 4.1 modify the MySQL 4.0 my.ini to to a non-default port and start. If for some reason certain databases to not perform well until something is modified, you can temporarily run 4.1 and 4.0 concurrently Note: To disable network access to the server at to my.ini bind-address=localhost Post Upgrade After upgrading, update the grant tables to obtain the new longer Password column that is needed for more secure handling of passwords. If you do not do this, MySQL does not use the new more secure protocol to authenticate. Here is the steps for Windows Users: On Windows systems, there isn't an easy way to update the system tables until MySQL 4.0.15. From version 4.0.15 on, MySQL distributions include a mysql_fix_privilege_tables.sql SQL script that you can run using the mysql client. For example, if your MySQL installation is located at C:\Program Files\MySQL\MySQL Server 4.1, the commands look like this: C:\> cd "C:\Program Files\MySQL\MySQL Server 4.1" C:\> bin\mysql -u root -p yourpassword mysql> SOURCE scripts/mysql_fix_privilege_tables.sql alternatively create a batch file like fix41.bat with: mysql -uroot -pYourPawword -f mysql < "C:\Program Files\MySQL\MySQL Server 4.1\scripts\mysql_fix_privilege_tables.sql" The mysql command will prompt you for theroot password; enter it when prompted. If your installation is located in some other directory, adjust the pathnames appropriately. As with the Unix procedure, you might see some Duplicate column name warnings as mysql processes the statements in the mysql_fix_privilege_tables.sql script; you can ignore them. Note that excuting that script also should update your "mysql" system database with the changes for 4.1 ( create help tables, time_zone tables etc...). After running the script, stop the server and restart it. Reference: Section 5.5.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. You can verify that the script worked but inspecting the mysql->user table. The password field should now be VARCHAR(41) versus VARCHAR(16) --- Note: If you have people connecting with old 4.0 clients, they might have problems connecting to the new improved longer passwords. In this case you can set the option --old-passwords (to still accept the old authentication passwords, until all your clients are upgraded to 4.1). Symptoms of this error: Client does not support authentication protocol MySQL 4.1.3 introduces support for per-connection time zones. The previous step should have created the time zone tables in the mysql database. Now you simply need to fill in these tables data. ForWindows users: Download the raw tables: http://dev.mysql.com/downloads/timezones.html Un-pack Stop MySQL Server Copy the un-packed time_zone tables to existing empty ones in the "mysql" system tables folder. The folder called "mysql" in your data folder. Start MySQL Server Reference: Section 5.10.8, “MySQL Server Time Zone Support” To use new functionality within PHP will need to use the new MySQL DLL. MySQL Functions The main issue which upgrade to 4.1 is the character set. This can be easily resolve when importing your data to 4.1 (see mysqldump - MySQL Database Backup Program). However the default collation utf8_unicode_ci assume "e" and "é" are the same (that collation like most are accent insensitive). For example, this is the default behavior of Google. If you search for "Hélène" results with "Helene" will also match. This can be a problem if you have a field used as a "unique index" with "Hélène" and "Helene". Before 4.1 no issue, after the4.1 upgrade problem these 2 values are considered the same (and you might have a problem importing your data). I personally like this new behavior since they should be considered the same value. The second user "Hélène" already had her userID reserved by "Helene" and should have been banned from sign-up with a duplicate userid warning. It is the same name. To import you data can keep the "accent insensitive" collation you will have to change those instances. I change "Hélène" to "Hélène2" and told her about it. I think you can get around this issue by switching the collation on your table to "utf8_bin". The only problem is that this collation is also case sensitive...ouch! References Upgrading from MySQL 4.0 to 4.1 Upgrading MySQL http://dev.mysql.com/doc/refman/4.1/en/windows-upgrading.html Other links (just in case I have further issues) Post-Installation Setup and Testing Drupal notes: Database only upgrade from MySQL 4.0.x to 4.1.x Going from MySQL 4.1 back down to 4.0 http://drupal.org/node/85171 http://drupal.org/node/84214 http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html » add new comment \| printer friendly version \| delicious \| digg \| reddit \| google \| technorati
	©2006 www.SiteBuddy.com. All rights reserved.