A Quick Look at Magento’s Database Table Resource Cache

Published by John on July 25, 2016 Under Magento

Recently while working on a custom Magento module for a client, I was doing it the Magento way and creating a Mysql DB Resource for a custom table I had created, so that I could take advantage of the collection, set/get functions, and other neat things you can easily do with Magento’s class abstractions.

Of course I was being a bit bad too and since I was still working on getting the table structure correct, I manually edited the table a few times. This resulted in the common issue where your DB Model class is created and working mostly correct, however when you use one of the set functions for a column, it doesn’t work.

So for example, $my_model->setId(5) might work, but $my_model->setNewColumn(‘test’) does not. The first time you run into this, this can be a little confusing to debug, as usually some of the set functions will work, but others do not.

The issue is that Magento creates a cache file for tables when you create a DB Resource for it and if you are bad and modify the MYSQL table directly without doing an upgrade/install script, the cache file does not get updated.

The common advice is to just Nuke the entire /var/cache folder, as even when you are runing Magento with all caching disabled, it will still cache database tables, along with some other data. Of course, you can also just use the Flush Cache Storage button to clear it out.

While I’ve run into this issue before when working doing some custom module development, I’ve never taken the time to actually look and see how Magento caches DB tables. So, this time I decided to take a quick peek at how it handles the file cache.

Serialized For Faster Access

It turns out Magento creates at least two files with serialized data describing the table. One holds some metadata about the table and the other includes the columns and their descriptions. This helps reduce extra db calls when working with tables/collections, as file access is almost always faster than DB access. So, the first time the model is accessed after clearing the file cache, a new file will be created to speed up/make easier future use of the model.

As an example, if you had a MYSQL table called my_new_table, you would end up with a file similar to the following: /var/cache/mage–6/mage—403_DB_PDO_MYSQL_DDL_my_new_table_1

This contains a serialized array, which when unserialized contains info about the table, such as the columns, types, sizes, etc.

This is an example of the array structure:

Array
(
    [id] => Array
        (
            [SCHEMA_NAME] => 
            [TABLE_NAME] => my_new_table
            [COLUMN_NAME] => id
            [COLUMN_POSITION] => 1
            [DATA_TYPE] => bigint
            [DEFAULT] => 
            [NULLABLE] => 
            [LENGTH] => 
            [SCALE] => 
            [PRECISION] => 
            [UNSIGNED] => 
            [PRIMARY] => 1
            [PRIMARY_POSITION] => 1
            [IDENTITY] => 1
        )

    [user_id] => Array
        (
            [SCHEMA_NAME] => 
            [TABLE_NAME] => my_new_table
            [COLUMN_NAME] => user_id
            [COLUMN_POSITION] => 2
            [DATA_TYPE] => bigint
            [DEFAULT] => 
            [NULLABLE] => 
            [LENGTH] => 
            [SCALE] => 
            [PRECISION] => 
            [UNSIGNED] => 
            [PRIMARY] => 
            [PRIMARY_POSITION] => 
            [IDENTITY] => 
        )

    [name] => Array
        (
            [SCHEMA_NAME] => 
            [TABLE_NAME] => my_new_table
            [COLUMN_NAME] => name
            [COLUMN_POSITION] => 3
            [DATA_TYPE] => varchar
            [DEFAULT] => 
            [NULLABLE] => 1
            [LENGTH] => 512
            [SCALE] => 
            [PRECISION] => 
            [UNSIGNED] => 
            [PRIMARY] => 
            [PRIMARY_POSITION] => 
            [IDENTITY] => 
        )

    [manufacturer] => Array
        (
            [SCHEMA_NAME] => 
            [TABLE_NAME] => my_new_table
            [COLUMN_NAME] => manufacturer
            [COLUMN_POSITION] => 4
            [DATA_TYPE] => varchar
            [DEFAULT] => 
            [NULLABLE] => 1
            [LENGTH] => 512
            [SCALE] => 
            [PRECISION] => 
            [UNSIGNED] => 
            [PRIMARY] => 
            [PRIMARY_POSITION] => 
            [IDENTITY] => 
        )

)

As you can see, it is a fairly straightforward array with some common fields and the name of the column as the array key. The metadata file is also an array, with a created time, expire time, and hash.

No Comments |