How to upgrade Magento when the database structure changes between versions?

Question

All of the upgrade methods I have seen (not sure about the magento connect method) do not touch the database only the files and & directories). I am on my first Magento build but I see that they have changed the directory structure with past updates. So my question is how should I update Magento to ensure the database gets upgraded?

Answer 1

As per @Anton's answer, the database upgrades are applied by the PHP code in the Magento modules themselves.

If you look in any of the code modules (say DOCROOT\app\code\core\Mage\Catalog\ for example), you will see a folder called sql\modulename_setup which contains numerous files that perform the database upgrades relevant to their module version.

Note that the module version (specified in the etc/config.xml for that module) will not always match the overall Magento version, though for some of the major modules such as Mage_Catalog, they are fairly close.

When you upload the new code onto your webserver's filesystem, the next time that you load any Magento page, the system will check the current database version number using the core_resource table in mysql. If it is less than the version listed in that module's config.xml, then Magento will apply the files in the sql\modulename_setup folder in sequence to bring the data_version up to match the module's version.

The files under the sql folder often contain both DDL and DML statements, and may be responsible for changing table structure, keys, relationships and inserting fixture data.

So, the short answer to your question "How do you update" is to let Magento do the update for you. Trying to manually recreate all those queries would be a recipe for disaster, particularly given the interdependencies between modules.

One important note - make sure you backup your database before upgrading!!!

Answer 2

A process we've used that works:

• extract a fresh copy of the Magento version you want to upgrade to somewhere
• manually create an 'app/etc/local.xml' that points to your old database
• go to a Magento page - Magento will run the database migrations, upgrading your database
• now re-install your modules and themes

This way you keep all order/product/customer/etc data.

Of course, you'll need to ensure your modules and themes work with the new version of Magento first, and the usual caveats for 'backup first' etc etc...

Answer 3

If you upgrade magento :

1. turn off your theme and reset to default
2. turn off all custom extensions used
3. upgrade version by version (if you use 1.3.2.4 today you have to apply all versions between latest to make it work flawlessly)
4. turn on and debug your template
5. turn on extensions one by one and debug

database upgrades are handled by versions setup scripts

To illustrate this:

• you need 2 git repositories . One for magento version and one for your own site
• make a git repo of needed magento versions with this bash script http://pastie.org/1573801
• next up you have to copy your own site , unify the line endings (so it can be compared to original magento version) and make it as a git repo , it can be done manually or with rsync, I prefer rsync way
• make a empty dir and init this a s a git repo, pull your clean magento version to it like git pull ../magento magento-1.3.2.4
• copy over your live site and you can perform a git status to see what you have added, changed
• move or discard any changes that have been made to core files, move edits in default templates under your own theme
• commit your changes , add extensions and so on , ignore what's not needed in .gitignore
• turn off your template, and disable extensions (move them out from app/etc/modules/ folder)
• perform a git pull ../magento magento-nextversion as this will merge all changes and eliminate all deleted files from new magento version
• visit your site frontpage and see how it is upgraded
• repeat the git pull nextversion and home page visiting with each version you need to upgrade , commit between if you need
• start enabling your theme and extensions one by one

Now you can make a shell or python , ruby or whatever script to automate this for you. My experience says that all this (with generating magento repos, copying live database to dev, rsyncing needed files, upgrading in a loop) will take less tan 5 mins from 1.3.2.4 to 1.5.0.1 if automated