How to Perform a Direct Upgrade from Drupal 4.6.x to Drupal 6.x

How to Perform a Direct Upgrade from Drupal 4.6.x to Drupal 6.x
This method will show you how to perform a direct website upgrade from Drupal 4.6.x to 6.x without having to perform the typical 4.6.x to 4.7.x to 5.x to 6.x version by version upgrade.

Why Use an Alternative Upgrade Method?
I did not want to perform the typical 4.6.x to 4.7.x to 5.x to 6.x version upgrade because of the following reasons:

• The possibility of loosing and corrupting data.
• The final upgraded database could end up containing residual and obsolete data from unused tables and fields from the version to version upgrade.
• The main reason for I chose this alternative method was that most of the content in my website had internal reference links to other pages in the site, and therefore I wanted to ensure that the upgrade would retain all the page, forum, node, and comment reference link numbers. Plus, I had about 800 users who have posted forum topics within the site and wanted to make sure that the user / posting references were retained properly.

Direct 4.6.x to 6.x Upgrade Procedure and Utility
The following Microsoft Excel utility can be use to create properly formatted CSV import files for importing tables into your 6.x database via phpMyAdmin. You can import the CSV files using phpMyAdmin's default CSV import option. The utility contains the following Drupal 6.x database table templates:

users_roles, profile_fields, profile_values, users, term_node, forum, node, and comments

Download: http://xlecom.com/downloads/Direct_4_6_x_to_6_x_Updrade_Utility.zip

Direct 4.6.x to 6.x Upgrade Procedure
The following outlines the procedures I used to perform a successful direct 4.6.x to 6.x version upgrade. The upgraded website is http://xlecom.com.

I installed a new Drupal 6.9 installation into a subdirectory and setup the site with the basic format and theme settings. I did not add any content or blocks.

I then used phpMyAdmin for the database editing environment to export the 4.6.5 tables and re-import the modified tables back into the 6.9 database.

It was not easy. I hade to first export the key data Drupal 4.6.5 database tables individually, then alter them in Microsoft Excel to match the database table field structure of Drupal 6.x so that I could import them into the Drupal 6.9 database.

The following are the database tables that needed to be imported into Drupal 6.9 and the order they were imported.

#  Database Table              Excel Modifications                     Imported into 6.9 Database   
-- -----------------------     ---------------------------             --------------------------------------
00 users_roles *               00_ users_roles 6.x.xls                 00_ users_roles 6.x.csv
01 profile_fields              01_ profile_fields 6.x.xls              01_ profile_fields 6.x.csv
02 profiles_values             02_ profile_values 6.x.xls              02_ profile_values 6.x.csv
03 users                       03 users 6.x.xls                        03 users 6.x.csv
04 term_data                   - No Conversion needed -                04_term_data.csv 6.x.zip
05 term_hierarchy              - No Conversion needed -                05_term_hierarchy 6.x.csv.zip
06 term_node                   06_term_node 6.x.xls                    06_term_node 6.x.csv
07 forum                       07_forum 6.x.xls                        07_forum 6.x.csv
08 node_access                 - No Conversion needed -                08_node_access 6.x.csv.zip
09 node_comment_statistics     - No Conversion needed -                09_node_comment_statistics 6.x.csv.zip
10 node_counter                - No Conversion needed -                10_node_counter 6.x.csv.zip
11 node                        11_node 6.x.xls                         11_node 6.x.csv
12 node_revisions  **                                                  12_node_revisions.csv.zip
13 comments **                 13 comments 6.x.xls                     13 comments 6.x.csv

* The users_roles table in Drupal 6.9 did not have any defined roles in this table (an empty table), so I skipped it.

** Both the node_revisions and comments tables needed extra work to make them compatible with the Drupal 6.x format.

I had to first import the node and comments tables from Drupal 4.6.5 directly into Drupal 6.9 as temporary node1 and comments1 tables and then modify the table structures in Drupal 6.9 to match the node_revisions and comments table format. I then exported the node1 and comments1 table data as CSV files and re-imported the data back into the database into the node_revisions and comments tables. Lastly, I deleted (dropped) the temporary node1 and comments1 tables.

Explanation for New Users
You should have database access through your website's hosting control panel, not via your Drupal site itself. This database access tool is called phpMyAdmin.

Assuming you have both the old Drupal 4.6.x site's database file and the new Drupal 6.x database file on the same web server, when you activate phpMyAdmin, you should be able to open both of the Drupal database files and see the tables inside the database. You may want to open two instances of phpMyAdmin so that you can open both databases at the same times so you can visually see the differences in the tables.

The database is made up of a series of tables, where each table is set up as rows and columns, just like a spreadsheet.

If you look at the both the database tables from your old site and new site, you will see that some of the new drupal database tables have new added fields columns and some of the field names have changes.

If you follow the database table import / export instructions mentioned above,

"first export the key data Drupal 4.6.5 database tables individually, then alter them in Microsoft Excel to match the database table field structure of Drupal 6.x so that I could import them into the Drupal 6.9 database"

then your old 4.6.x site will be directly upgraded to Drupal 6.x.

If you export the key 4.6.x tables mentioned above using the format and use the supplied spreadsheet utility I made, then you can easily convert the old 4.6.x tables the new 6.x table format. The spreadsheet utility will allow you to create a CSV table file that you can import into the Drupal 6.0 database.

Just go to each of the database tables, one by one, and perform the conversion. The import CSV files should not contain the field names. You may also want to empty the Drupal 6.x tables just before you import the new table data so that the data you import is not added to any current data in the tables.

Note: when you perform the modification on the forums and term_node tables, make sure the values in the vid and nid columns are identical, meaning that they match (vid 1 = nid 1).

After each table export / import operation, backup the Drupal 6.x database in case you happen to do something wrong.

Use this Excel utility to Convert the Database table data to CSV format for Importing into a MySQL Database table via phpMyAdmin.

Important Warning
One important message for others who may attempt to do the same type of upgrade.

MySQL 4.x and PHP 4 only scripts will render your new site completely useless if running MySQL 5.x and PHP 5.

(1) Do not transfer any custom PHP block scripts into your new site without first testing them out in a test node page using PHP 5. The node Preview feature will help you debug your scripts before saving them to the database.

(2) If you had any custom PHP 4 only scripts within your old site, via the nodes (pages, forums, comments, etc...), and transferred them to the new site, then your site will get fatal errors during cron runs. This was the hardest thing for me to determine and debug to what was causing these fatal errors.

Optional MySQL Database SQL Character Substitutions
After I upgraded I noticed that some of the characters in the node and comments within the site were translated to some other format. I identified these characters and performed an SQL replacement to restore the characters back to normal. The replacements were performed on the database via SQL within phpMyAdmin.

Below are the SQL commands for the character replacements:

SQL Replace Statement
=====================
update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

SQL Replacement Code to run within phpMyAdmin
=============================================

” = " (double quotes)
---------------------
update node_revisions set body = replace(body, '”', '"');
update node_revisions set teaser = replace(teaser, '”', '"');
update node_revisions set title = replace(title, '”', '"');
update comments set subject = replace(subject, '”', '"');
update comments set comment = replace(comment, '”', '"');

’ = ' (single quote)
----------------------
update node_revisions set body = replace(body, '’', ''');
update node_revisions set teaser = replace(teaser, '’', ''');
update node_revisions set title = replace(title, '’', ''');
update comments set subject = replace(subject, '’', ''');
update comments set comment = replace(comment, '’', ''');

” = " (double quotes)
-----------------------
update node_revisions set body = replace(body, '”', '"');
update node_revisions set teaser = replace(teaser, '”', '"');
update node_revisions set title = replace(title, '”', '"');
update comments set subject = replace(title, '”', '"');
update comments set comment = replace(comment, '”', '"');

“ = " (double quotes)
-----------------------
update node_revisions set body = replace(body, '“', '"');
update node_revisions set teaser = replace(teaser, '“', '"');
update node_revisions set title = replace(title, '“', '"');
update comments set subject = replace(subject, '“', '"');
update comments set comment = replace(comment, '“', '"');

• = • (bullet)
----------------
update node_revisions set body = replace(body, '•', '•');
update node_revisions set teaser = replace(teaser, '•', '•');
update node_revisions set title = replace(title, '•', '•');
update comments set subject = replace(subject, '•', '•');
update comments set comment = replace(comment, '•', '•');

â„¢ = ™ (trademark)
-------------------
update node_revisions set body = replace(body, 'â„¢', '™');
update node_revisions set teaser = replace(teaser, 'â„¢', '™');
update node_revisions set title = replace(title, 'â„¢', '™');
update comments set subject = replace(subject, 'â„¢', '™');
update comments set comment = replace(comment, 'â„¢', '™');

£ = £ (UK currency symbol)
---------------------------
update node_revisions set body = replace(body, '£', '£');
update node_revisions set teaser = replace(teaser, '£', '£');
update node_revisions set title = replace(title, '£', '£');
update comments set subject = replace(subject, '£', '£');
update comments set comment = replace(comment, '£', '£');

– = - (dash)
--------------
update node_revisions set body = replace(body, '–', '-');
update node_revisions set teaser = replace(teaser, '–', '-');
update node_revisions set title = replace(title, '–', '-');
update comments set subject = replace(subject, '–', '-');
update comments set comment = replace(comment, '–', '-');

Sam Raheb