MySQL 4.1

• Downgrading MySQL from 4.1.x to 4.0.x
• Optimize MySQL Performance
• Verify Database Version
• Create the Database

NEWS & TIPS

• Site Access Keys
• Top: Alt+t
  Previous: Alt+,
  Next: Alt+.
  Up: Alt+;
  (Note: use Ctrl on the Mac)

Downgrading MySQL from 4.1.x to 4.0.x

Author: Sean Sawyer

Why Downgrade?

On at least one occasion, clients running MySQL 4.1.15 and 4.1.16 on Windows 2003 Server have experienced repeated database crashes that resulted in significant CMS downtime. There were no apparent issues with the database itself, as it never failed to dump or restore from a dump successfully. This is known to have occurred on a Windows 2003 Server machine running MySQL versions 4.1.15 and 4.1.16, and it was corrected by performing a clean install of 4.0.26 and restoring the databases. We have not experienced this on other platforms like Linux, Solaris, or OS X.

How Do I Know It Will Help?

It's a good bet that this will solve your problem if, contemporaneously with database crashes, you have repeatedly received an error message with the following (or a similar) message:

Error checking if entity exists: org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (java.sql.SQLException: Unable to connect to any hosts due to exception: java.net.ConnectException: Connection refused: connect); - nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Unable to connect to any hosts due to exception: java.net.ConnectException: Connection refused: connect))

In the event that this does not solve your particular problem we will describe how to backup your databases for both 4.0.x and 4.1.x, should you decide later to revert to a 4.1.x installation.

Step 1 - Dump Databases

Open a command prompt and dump each database you want to preserve using mysqldump (with 4.0 compatibility), as well as for 4.1 (in case you decide to revert to a 4.1 installation in the event that the downgrade does not solve your problem) as follows:

mysqldump -u root -p --create-options --compatible=mysql40 db_name > dump_file mysqldump -u root -p db_name > dump_file

Be sure to get **EVERY** database you want to preserve, because you WILL NOT get a second chance to dump them!

Step 2 - Uninstall MySQL 4.1.x

First, stop the Windows service (if MySQL is installed as a service), and then remove the service:

NET STOP MySQL mysqld --remove

Now, using the 'Add or Remove Programs' interface on the control panel, remove MySQL from the server.

Check to see whether the MySQL installation directory still exists. If it does, delete it (along with all of its contents, of course). Also, search for any files named 'my.ini' and be sure to either delete them or rename them to something like 'my.ini.41'.

Reboot your server (just to be sure!).

Step 3 - Install MySQL 4.0.x

Download the most recent 4.0 release as a no-install archive (that is, no Windows installer) from the MySQL 4.0 download page , and unzip its contents to C:\mysql. If the archive adds another folder inside of C:\mysql, just move the contents up one directory and delete the extra folder.

Step 4 - Configure MySQL 4.0.x and Windows

Create my.ini in C:\WINDOWS (on XP) or C:\WINNT (on 2k/2k3) by copying and renaming the configuration file of your choice (my-medium, my-innodb-heavy, etc. - see the MySQL manual for more info on this). Change/add the following lines in the [mysqld] section:

max_allowed_packet=16M key_buffer=16M # set basedir to your installation path basedir = C:/mysql # set datadir to the location of your data directory datadir = C:/mysql/data

Add the following to the end of your Path system variable:

;C:\mysql\bin

Close and reopen the command prompt (to refresh system variables) and start MySQL for the first time with:

mysqld --console

Test the installation like this:

mysqlshow mysqlshow -u root mysql mysqladmin version status proc mysql test

Shutdown MySQL:

mysqladmin -u root shutdown

Install and start the Windows service:

mysqld --install NET START MySQL

Create and restore each database from the dump files by doing the following for each:

mysqladmin -u root drop db_name mysqladmin -u root create db_name mysql -u root db_name < dump_file

Secure the initial MySQL user accounts:

mysql -u root mysql&gt; SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); mysql&gt; SET PASSWORD FOR ''@'%' = PASSWORD('newpwd');

Step 5 - Almost There!

Now run ServerManager.exe and reconfigure Cascade as needed to connect to the new installation, restart Cascade and you're ready to rock!

---

Topic Feedback Form

Content Rating:	1 - Best 2 3 - Average 4 5 - Poor
Email:
Feedback:

---

Wiki | Edit