Upgrading MovableType Berkeley to MySQL

Tom Keating : VoIP & Gadgets Blog
Tom Keating
CTO
| VoIP & Gadgets blog - Latest news in VoIP & gadgets, wireless, mobile phones, reviews, & opinions

Upgrading MovableType Berkeley to MySQL

Upgrading to MovableType running MySQL instead of the Berkeley database was certainly a challenge, but it was worth the effort (I include the MT upgrade to MySQL steps below). I've been running the default Berkeley database for TMC's blogs, including my own. I knew that the Berkeley database was not very robust in terms of database recovery/disaster, rebuilding, or fixing corrupt table entries - at least not unless you compile the Berkeley tools written in C to run on Windows 2003 where TMC's blogs reside. There are several advantages to MySQL over Berkeley, including better user community support, better tools, better backup & disaster recovery options, ability to query or modify the blog tables directly using SQL statements, and better performance.

I had several problems upgrading to MySQL and thought I would share the solution so others don't go through the nightmares that I did.

Steps to Upgrade MovableType Berkeley Database to MySQL

1) Install MySQL for your operating system. In my case I downloaded the one for Windows XP/2000/2003. Try and choose the Generally Available Version and not any beta versions. The current one is: Generally Available (GA) 4.1.11. Also, for ease of installation, choose the Installer package.

2) MySQL will run through a wizard to "optimize" your MySQL installation. You are presented with 3 choices for MySQL Server Instance Configuration:

MySQL Server Instance Configuration

MySQL Server Instance Configuration

I chose MySQL Server which uses a "medium" amount of memory. Feel free to choose any of the other options. Next, I chose MySQL Multifunctional Database as seen in the screenshot.
MySQL Multifunctional Database

MySQL Multifunctional Database


Next, the number of connections for our blogs' database. I selected manual and 50 connections..
MySQL Manual Number of Connections

MySQL Manual Number of Connections


Choose which character set to store the database in. UTF-8 is the best for "international" support, however, since my old data was stored in Latin1/ISO-8859-1 I decided to stay with this character set.
MySQL Manual Number of Connections

MySQL Character Set


3) Install DBI and MySQL DBI (dbdmysql). Here are instructions if using ActiveState Perl's PPM. You can use CPAN or other installers which will be similar to these instructions.
- In Windows 2000/2003/XP go to CMD line (cmd.exe)
- ppm>install DBD-MySQL
- ppm>install DBI

If PPM cannot find either of these PPD files on ActivePerl's PPD repository to install either of these two packages, then add this very new popular repository which has lots of PPMs:

ppm> rep add uwinnipeg http://theoryx5.uwinnipeg.ca/cgi-bin/ppmserver?urn:/PPMServer58
(above is all on 1 line)
Note that this is for ActiveState Perl 5.8. You may need to change the parameter slightly if using an earlier/later version of ActiveState Perl.

If you don't want to add theoryx5.unwinnipeg.ca to your list of repositories, then simply do this instead:
- ppm>install http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd
- ppm>install http://theoryx5.uwinnipeg.ca/ppms/DBI.ppd

Alternatively, this guy has list of PPMs and says he will compile one for you if you can’t find it:
http://crazyinsomniac.perlmonk.org/perl/ppm/5.8/

4) Create your blog database - from a command line, run the following
- mysql -u [username] -h [hostname] -p[password]

Note that there's no space between the -p and the password like the other switches. If you don't specify hostname then localhost is assumed. Also, don't type the brackets. Here's an example:
mysql -u tom -h localhost -pwheresthebeef

- From MySQL command line, type:
create database [database name];
I suggest using 'MT_blogs' or just 'blogs' for the database name, but it doesn't really matter (no single quotes by the way).

- To make sure we have our new database, type this:
show databases;

You should see your database somewhere in the list. Now for security purposes we should create a separate user to access the database from MovableType rather than using the "root" admin account. Type this:

grant all on [database name].* to [new username] identified by '[new password]';

Example:
grant all on blogs.* to jsmith identified by 'kingoftheworld';

- Let's double-check the user was added.
Type this:
quit;

This will kick you out of the MySQL command line.

Now try to log back in as the new user you created. Type:

mysql -u [username you created] -p[password you created]

Type:
Show databases;

Make sure your blog database is listed. Now we are ready to rock and roll with setting up MovableType to use this new database!

5) Edit your mt.cfg file. (back it up first) (This step from MovableType website)
Open your mt.cfg file in a text editor, and add these lines to it:

ObjectDriver driver_name
Database database_name
DBUser database_user


where *driver_name* is one of the following: *DBI::mysql*,
*DBI::postgres*, or *DBI::sqlite*; *database_name* is the name of
the SQL database where you wish to store your data; and
*database_user* is the username used to connect to that database
(note that if you are using SQLite, neither a username nor a
password is required).

If your SQL database server is on a different host, you can use
*DBHost* to specify the hostname:

DBHost database_host

Note: if you are using either PostgreSQL or MySQL, the database that
you specify in *database_name* must already exist--Movable Type
cannot create it automatically! (We did this already, so we're good to go!)

- Set your password.
For security purposes, your SQL database password should not simply
be placed in mt.cfg. Instead, you must set it in the file called
mt-db-pass.cgi.

Open mt-db-pass.cgi in a text editor. On this line:
database_password

Replace *database_password* with the password used to connect to
your SQL database. Note: DELETE the words database_password from this file - ONLY put your password here and make sure no leading or trailing characters.

6) Run the mt-db2sql.cgi script from your browser
Open your web browser and point it at the URL for mt-db2sql.cgi on your site. For example, if your site is http://www.your-site.com/, and you uploaded the Movable Type files into the /mt directory, you'd type http://www.your-site.com/mt/mt-db2sql.cgi.

mt-db2sql.cgi is a Perl script that will create the necessary schema in your SQL database, then populate the database with the data from your Berkeley DB files.

6a) I encountered the following error at this step which you may as well. If not, skip to Step 7)

"Connection error: Client does not support authentication protocol requested by server; consider upgrading MySQL client"

After some Googling, I figure out the problem is that MovableType doesn't support the new MySQL password hashing scheme. So you have to change it to the old password hashing algorithm. To do that, do the following:

- Run MYSQL with the MySQL blog account you setup, i.e.
mysql.exe –u blogaccount -pmypassword

Next, run this:
mysql>SET PASSWORD FOR 'blogaccount'@'localhost' = OLD_PASSWORD('mypassword');

Helpful links that discuss this issue if you are so inclined:
http://dev.mysql.com/doc/mysql/en/old-client.html

http://dev.mysql.com/doc/mysql/en/password-hashing.html

7) IIS CGI Timeout Error

- If you don't run IIS or are not getting this error, skip to step 8.

In IIS 5 and IIS 6.0, the default CGI Timeout period is 300 seconds (5 minutes), so the mt-db2sql.cgi kept stopping in the middle. Surprisingly, IIS did not return the usual CGI timeout error message, i.e. "CGI Timeout: The specified CGI application exceeded the allowed time for processing. The server has deleted the process." Finding the CGI Timeout setting on IIS 6.0 was like finding a needle in a haystack. I did some Google searches and there were thousands of links discussing the solution for IIS 5.0, but none of the links were helpful for IIS 6.0. Changing the CGI Timeout setting in IIS 5.0 is easy. In IIS 5.0 you simply do the following steps:
In the Start Menu, go the Settings menu, and choose Control Panel.
- Open the Administrative Tools control panel.
- Open the Internet Services Manager item.
- Right-click on the computer icon in the left panel and choose Properties from the menu that appears.
- Click "Edit..." next to "WWW Services".
- Click the "Home Directory" tab.
- Click the "Profile..." button.
- Click the "Process Options" tab.

Enter a large value in the CGI script timeout field, perhaps 999999.
Unfortunately, in Microsoft's great wisdom they decided to REMOVE this from the IIS Manager tool in IIS 6.0. My suspicion is that they want you to start using .NET and move away from CGI applications. I even saw a link on Microsoft's site basically saying that CGI is bad, .NET is good - you'll get better performance with .NET, .NET is da bomb, etc.. Wish I could find that link. Anyway. I spent countless hours searching on Google to resolve the IIS 6.0 CGI timeout issue. I saw several people having the same issue with no one having the answer. The answer to the CGI timeout was so surprisingly simple (once I found it) that I was very surprised none of the Google links mentioned this solution.

The solution to the IIS 6.0 CGI Timeout issue (default 300s) is to download the IIS 6.0 Resource Kit and then use the included IIS Metabase Editor (screenshot below) to change the default CGI timeout setting.I changed it from 300s to 3600s (1 hour).
By the way, I nearly needed that 1 full hour too, as the mt-db2sql.cgi script took about 50 minutes to run. Why oh why Microsoft removed this from the main IIS Manager and force you to download the Resource Kit I will never know. The CGI Timeout issue is probably THE MOST COMMON error on IIS when running Perl or PHP. Of course Microsoft isn't exactly a fan of their due to their competing .NET platform, so perhaps that explains why.
IIS 6.0 CGI Timeout Problem Fixed

IIS 6.0 CGI Timeout Problem Fixed


So after that big headache fixing the CGI timeout issue, I then had to drop the MySQL blog database, recreate it, and then re-run the mt-db2sql.cgi script again. (You can't just re-run the mt-db2sql.cgi script if the database was partially created - it gives you an error message about a table already existing)

8) Check for success
Once mt-db2sql.cgi has finished executing, look at the bottom of the page in your browser to check whether the conversion was successful.

If it was, this message will be displayed:
"Done copying data from Berkeley DB to SQL database! All went well."

If you received this message, you're done. When you log in to Movable Type and use the system, you will now be using the SQL version of the system. I commented out the Berkeley database in mt.cfg just to be sure, i.e:
# DataSource C:\blogdatabase\

If an error occurred, the message, "An error occurred while loading data:" will be displayed, along with the actual error message. If an error occurred, and you want to go back to the Berkeley database, simply edit your mt.cfg file and remove the following line:
ObjectDriver driver_name
where, again, *driver_name* is the name of the driver you set.

You will be back to using the Berkeley DB version of MovableType. Post the error message that you received on the Support Forums. If you think you know what you did wrong, simply repeat the steps above and don't remove the line mentioned above.

Hopefully, this helped you in your migration from Berkeley database to MySQL. If so, drop me a line. My next project is upgrading from MovableType 2.661 to MovableType 3.1 or possibly migrating to WordPress. We shall see.

Reference links:
Installing Movable Type using Windows 2000, IIS 5, and MySQL:
www.ocomik.net/movable%20type%20installation%20notes.pdf
Good install guide for WinXP, PHP, MySQL
http://www.fortysomething.ca/mt/etc/archives/004617.php
Migrating your blog to a new server
www.elise.com/moving_mt_blogs_to_new_server_or_web_host
Lots of good MT installation guide links
http://ko.offroadpakistan.com/computing/2003_07/movable_type.html
The best resource for installing MovableType on the Windows platform
wangjianshuo.com/movabletype_successfully_installed_on_windows_xp



Related Articles to 'Upgrading MovableType Berkeley to MySQL'
ckeditor-movabletype.jpg
movable-type-facebook-connect-login.png
movable-type-logo.jpg
post-office-20-plugin-settings.jpg

Featured Events