Let us assume you have the module created and it is called GreatModule. Its previous version was 0.1.1 and you are on version 1.4.3 and want to make alterations to a table or a set of tables in the MySQL database during the upgrade process. As part of the original installation of GreatModule, you would have a MySQL setup file called mysql4-install-0.1.1.php in the GreatModule/sql/greatmodule_setup folder. To create the update to the MySQL database follow the steps outlined below.
Step 1: Update the version number inside the config.xml
Open GreatModule/etc/config.xml Find <version>0.1.1</version> and change it to <version>1.4.3</version> Resave the config.xml
Step 2: Make your new MySQL upgrade file
In GreatModule/sql/greatmodule_setup create a file called mysql4-upgrade-0.1.1-1.4.3.php There really are just four elements separated by hyphens, and through this filename it tells Magento to run the upgrade.
Step 3: Write your MySQL Statement
Inside the file mysql4-upgrade-0.1.1-1.4.3.php write whatever MySQL statements you need to fulfill the requirements of your upgrade.
Step 4: Refresh your cache
And you are done. Your GreatModule now has the ability to upgrade tables inside your database. One aspect which is important to note is the upgrade files will cascade and run one after another. So if later you are on version 1.5.2 and need another MySQL upgrade, you will have three files inside the GreatModule/sql/greatmodule_setup folder as follows: mysql4-install-0.1.1.php mysql4-upgrade-0.1.1-1.4.3.php mysql4-upgrade-1.4.3-1.5.2.php
This really is a simple process. Generally, if you are having problems getting the upgrade to work it could be one of these things:
1 ) You updated the GreatModule/etc/config.xml and refreshed your Magento cache prior to adding the MySQL mysql4-upgrade-0.1.1-1.4.3.php
You can manually check your database to see the installed module version. Using PhpMyAdmin or similar software, open your Magento store database and locate the core_resource table. Inside the core_resource table locate greatmodule_setup and double check the version installed.
2 ) You have a typo in your mysql4-upgrade file. Double check that the version numbers are exactly as they appeared in the previous version and the new version you are upgrading to.
3 ) You forgot to upgrade the version inside GreatModule/etc/config.xml
