Use code MAG25 to get $25 off! Limited time offer on Cloudways Magento hosting.
AVAIL NOW

What’s new in the Magento 1.9/1.14 database

Each time a new version of Magento is released, the first question every developer has is, “What are the changes?”. In this guest post by Tim Bezhashvyly, we will take a look at what changed database-wise in Magento 1.9 CE and 1.14 EE.

Introduction

Finding out code diffs is not that easy. Even though there are plenty of Magento 1.X mirrors on GitHub, reading the diffs is quite a task.

Especially with the newest tradition of releasing one version per year (Imagine, you know) the problem arises of each and every file being included into the diff because the years in the copyright notices changed. I tried to solve this issue at my Magento mirror by separating essential modifications from the secondary stuff.

The other question is: “What has been changed in the database?”. To get an answer to that is even more complicated. This knowledge turns out to be especially critical in cases when a rollback to a previous Magento version has to be performed. Fortunately, the 1.9 CE and 1.14 EE release did not introduce lots of changes.

Structure

The first thing you can do to find out what has changed, is a brutal structure dump compare. So what will you find?

First, there are 9 new triggers for the catalog_category_product, catalog_product_website and cataloginventory_stock_item tables. The following statement was added to the INSERT, UPDATE and DELETE operation for each of these tables:

This results in that each time the category, website or stock status of a product is modified, the product will be scheduled for partial reindex for the catalog search. This is clearly a bugfix as the triggers were definitely missing. That means that when performing a rollback, there is no necessity in removing those triggers. This modification only affects EE. The remaining changes are applied to both branches of Magento.

The next thing is more of a performance improvement. So in case of a rollback, there is again no need to revert it. The following index was added to catalogsearch_query table:

The next modification touched the log_url table. The primary key url_id turned into an index. Hard to say why they did it. To perform a rollback you have to drop the new index and restore the primary one:

Those are actually all the database structure changes made.

Data

Data modifications can be traced by examining new upgrade scripts. In Magento 1.9 CE and 1.14 EE, there are two that are actually doing some important modifications.

The first one is a new Mage_Paypal script versioned as data-install-1.6.0.5.php. It adds two new order statuses: PayPal Reversed and PayPal Canceled Reversal. For a rollback it makes sense to remove them from the sales_order_status table, because the next attempt of an upgrade to 1.9 CE or 1.14 EE will end up with a broken setup and a duplicate entries error.

The second one is more critical as in the upgrade-1.6.0.0.18-1.6.0.0.19.php update script of Mage_Catalog, the backend model for the special_from_date attribute is changed from catalog/product_attribute_backend_startdate to catalog/product_attribute_backend_startdate_specialprice. In case of a rollback, this has to be reverted. Otherwise, any page which has a product with a special price will throw a fatal error due to a non existing class.

This modification was made in sake of abstracting a start date class and then extending it in a special price start date implementation. Even though it is absolutely trivial, it is a nice addition as the parent class can now be used for any new attribute which need to have a start date “nature”.

These are actually all database changes caused by upgrading to Magento 1.9 CE or 1.14 EE.

Remarks

You may ask why the “end date” backend type was not modified. The most curious of you may even find out that this type doesn’t even exist! Further inspection will reveal that special_to_date attribute has an eav/entity_attribute_backend_datetime backend type.

The answer is simple: the start date is a bit special. It actually extends eav/entity_attribute_backend_datetime which is performing one obvious validation… that the start date is less then the end date :-)

  • Bju00f6rn Kraus

    Regarding the log_url index: That was a fix submitted by us as the log cleaning routine creates a big join the causes huge temporary tables without the index. So the index heals problems with the log clean cron job.

    • tim_bezhashvyly

      Nice, thanks for clarifying it.

  • Pingback: Magento-Neuigkeiten der Wochen 27/28 2014()