Does the Magento Database Upgrade happen in a “transaction”?

Question

We have this issue atm:

A client gets his shop upgraded from CE 1.4 to CE 1.8. The file-upgrade went well, and the database upgrade went also well on our development machine.

When we try to upgrade the client's live-db on his live-machine (connect the 1.8-Magento to the database and open it in the browser), the process seems to run for a while and ends in a 500 error.

PHP error log is empty; as it's a shared host, we can not change the apache or mysql settings; the hoster, though "specialized im magento hosting", is unwilling to change the settings and tells me that I could finish the database-upgrade by repeatedly refreshing the browser-window when the 500 error occurs, because magento will then be upgraded in small steps. This might go on for hours.

My question now is:
- Is this true? I thought the sql-statements for database-upgrades would be wrapped in a transaction, so they could be rolled back if anything goes wrong.
- Could the answer provide a hint where I could look in the code to find the answer for this question?

Thanks for your time!

Answer 1

Is this true? I thought the sql-statements for database-upgrades would be wrapped in a transaction, so they could be rolled back if anything goes wrong.

Your engineering instincts are sound, but what happens in the real world of business startup programming is more complicated/ugly.

Magento's setup resources system does not wrap individual scripts in a transaction. There's lots of reasons for this, but I've always assumed the primary one is Magento started life tied explicitly to MySQL, and many/most data definition statements (ALTER TABLE, etc) in MySQL cause an implicit commit.

While you'll find individual setup resource sometimes use transactions.

#File: app/code/core/Mage/Sales/sql/sales_setup/mysql4-upgrade-1.3.99-1.4.0.0.php
$installer->getConnection()->beginTransaction();
$installer->run("
        UPDATE {$installer->getTable('sales_flat_order')} AS o, {$installer->getTable('sales_order_entity_varchar')} AS od
    //...    

the system itself just runs the scripts and hopes for the best.

If you're interested in the code that runs these resources, the best place to start is probably here

#File: app/code/core/Mage/Core/Model/Resource/Setup.php
protected function _modifyResourceDb($actionType, $fromVersion, $toVersion)
{
    //...
}

The _modifyResourceDb method is the one that includes the actual setup resource scripts

#File: app/code/core/Mage/Core/Model/Resource/Setup.php
case 'php':
    $conn   = $this->getConnection();
    $result = include $fileName;
    break;
case 'sql':
    $sql = file_get_contents($fileName);
    if (!empty($sql)) {

        $result = $this->run($sql);
    } else {
        $result = true;
    }
    break;

A very hacky solution to your problem would be a temporary core-hack/code-pool-override that explicitly exited after 5-10 includes, and re-run that. This would reduce the chance of a setup resource script bailing half way through.

A better solution, and one of my personal "maybe one day" projects would be a custom script that used Magento core methods to examine the updates that need to be applied, list them, and let users run through them one by one.