Best practices when using Magento setup scripts

As I mentionned before, Magento setup scripts enable you to make your software setup more reliable. But working with setup require to use some best practices. If not, it could become a nightmare…

Here’s a list of points you should take care of when writing setup scripts

Do not update a setup script already under revision control

A script already under version control has perhaps already been played in some locations, and so core_resource record for this setup has already been updated

In this case, in order to play a second time this script, you must restore the initial state before it:

  • At least, downgrade the core_resource related record
  • Perhaps destroy all that has been run within the script

It’s very problematic because we lost all the automation within the setup. So you should really be careful before sharing a script to ensure that all the script’s content is well written

Be independant from database structure

This is why I wrote previously to not use the SQL files and prefer using the PHP scripts: Magento allow you to prefix your table names for security reasons. If in production you’ve prefixed your tables but not in testing, your script must work fine in each case.

So you must be able to find the table name in currnet context, and this is the role of the methode Mage_Core_Model_Setup::getTableName method

Be independant of the server type which hosts your Magento

For now, this is only in theory that Magento can be run on other database servers than MySQL. But even if it’s a dream, perhaps the module you developed will exist very longer and in 2015, when Magento will be able to use another SGBD, your setup script will also work fine

To do that, it’s really easy since the 1.6CE / 1.11EE because database connexion layer has been updated to be more abstract

Reduce SQL instructions to their minimal to have the more accurate error management

Take a look at the following SQL query we want to run:

SELECT entity_id, sku FROM catalog_product_entity;
ALTER TABLE foo ADD INDEX (entity_id);
<p>The historical way to run it in Magento could something like this:</p>
$this->run("CREATE TABLE {$this->getTableName('module/foor')} AS
SELECT entity_id, sku FROM {$this->getTableName('catalog/product')}
ALTER TABLE  {$this->getTableName('module/foor')} ENGINE=Memory;
ALTER TABLE  {$this->getTableName('module/foor')} ADD UNIQUE (entity_id)");

Fine, but each SQL instruction can have its own errors. So if we play back the script, it's possible to have some instructions run many times and could lead to an inconsistent context, like:

  • Same index defined multiple times
  • Insert run many times
  • ...

To reduce this risk, divide each SQL query to its minimal entity: if an SQL error is encountered, the following code won't be executed and so next time you'll launch the script you won't run the same SQL request. So in this example, use multple run instructions

Take care of existing

It's very important to take care of possible existing resources, and so, for the following reasons:

  • In 1.6 and previous versions, there is no semaphore in Magento setup scripts; in multiple servers, it's possible that you've started your setup while another server was aleady running it (but have not yet updated the core_resource table)
  • If the first time you've run it, there was an error, you'll need to execute it a second time. Testing already existing resources will avoid you to restore initial state before executing it a second time

It can be done in SQL instruction or within PHP code

Don't use constants in Magento setup scripts

Constants are used to declare some reference content. So it's often the case when you write some setups scripts.

But if you reuse the constant definition in multiples scripts, take care of ensuring that you test if the constant already exists: When Magento run some upgrade scripts, it will execute all the required scripts in one time by including all scripts. So it could lead to a case thasituation where the constant is defined multiple times, and so through an error.

Leave Magento use its own database connexion management

It means, do not access connexions objects in setup scripts: this is not a problem when you use only one database server, but if you use different read and write database servers, by using database connexion objects you 'll may change where will be written the data you want to setup

So leave Magento make it's own work

Using Setups to deliver application software with Magento

A long time ago,

in a (not) so far away office,

a Magento project is coming

we could hear something like this

Guys, I have an error when I display product sheet: “table does not exist”, what’s wrong?

The little guy on the openspace corner who talks like a robot answered him: It’s normal, you must execute the SQL script create_table.sql located in var/temp

This is not about the force I’ll talk about, but the power of the dark side, the lazyness: How Magento can help us to solve deployment problems? With the magento setup scripts

Introduction to Magento setup

Main interest of the Magento setup

Have you ever asked how Magento install its own database? This setup process is run against the Magento setup scripts: all database tables, initials configuration values or required records are inserted with these scripts: no need to play any SQL, all is managed with the native setup Magento API

Files involved in Magento setup

This mechanism is strongly linked with Magento modules architecture: if your module requires a dedicated table, it must be able to create the prerequisite to work properly. Another case study is when project is started: you made some modeling choices, and those ones require perhaps a specific configuration. it’ll very interesting that this configuration will be set up automaticly on each location (developpement, testing, prod, …), without any human action.

After reading this post, if you check what is executed by Magento during database setup, you’ll see that each module is responsible for its own setup:

  • Mage_Catalog create the catalog attributes
  • Mage_Checkout create the tables of agreements
  • etc

All modules files involved in setup are located in the module sql subfolder

Let’s see how it works

Main working

How does Magento find the module list that have some setups to run?

As I mentionned before, all is based upon the configuration files located in the app/etc/modules folder. When configuration is merged, Magento knows which are active modules.

From this merged configuration, Magento will find the setup list to run.

But not every module will have some setup. To know which modules have installers, it does not browse the sql folder for each module but looks for some dedicated XML nodes in configuration. These nodes depend on resources and are defined like this:

Without this node, and even if you have defined some sql scripts in your sql subfolder, nothing will happen

Now Magento can iterate through the configuration saved in cache, look for the <config><global><resources> to find the availables resources defined. if this resource has a setup subnode defined, it will be known as “with setup”

In which state are your Magento module sources? the version notion

Your module sources can be in 3 differents states with your Magento:

  • Install mode: you have just installed the module source on your Magento, but for now nothing has already been set up
  • Update mode: you have just made an update of your module sources, and the new setup has not already been played.
  • Downgrade mode: you’ve made a downgrade of your module sources. And now, the state of your database is too far from your module sources

All these states are managed with two version numbers: the current version number of your sources and the latest installed version number of your module in database

Magento current installed module version number

This number is defined in the core_resource table. This table contains for each resource the latest version number installed

Magento current module version number

This number should be defined in the config.xml file located in module etc subfolder with the following node:


Is there a specific format for version number?

No, Magento does not expect a specific format. It’s a versionning problem and often we define the version number with a format like X.Y.Z, where:

  • X: the major version number, and increases when you review the software architecture
  • Y: the minor version number, and increases when you add a new functionnality in module
  • Z: the bug fix version number, and increases when you fix a defect

What’s important is that your version number means something and changes according to your source code update

How does Magento find the list of setup to run?

The Magento resource defines where are located files and if there is some setup defined for your module

How does Magento link resource name with file path to find script list involved in setup?

The resource name is used to find where are located setup scripts:


This is in this folder that Magento will find the script used in setup

Setup state will help Magento to find possible file list to run

Then Magento will check in which state are your modules sources:

  • install: there is no db record for your %resource_name% in core_resource table
  • upgrade mode: core_resource database record is lower than the version defined in the module source
  • downgrade mode: core_resource database record is higher than the version defined in the module source

According to this state, Magento defines in which case you are:

  • install (Mage_Core_Model_Resource_Setup::TYPE_DB_INSTALL)
  • upgrade (Mage_Core_Model_Resource_Setup::TYPE_DB_UPGRADE)
  • rollback (Mage_Core_Model_Resource_Setup::TYPE_DB_ROLLBACK)

Version comparison between already installed version and source version is based upon the version_compare PHP method

Possibles file list to run

Here’s how Magento knows if there are some scripts to run. Let’s see how this file list is built

Can we use subfolders to organise our setups scripts in app/code/%Your_Module_CodePool%/%Your_Module_Namespace%/%Your_Module_Name%/sql/%resource_name%/ folder?

First, Magento iterates on these folders with dir class and so, will iterate only in this subfolder; so you cannot put your scripts in a subfolder, they won’t be played

Which extensions can be used to run setup scripts?

Magento iterates on all files and expects to find .SQL or .PHP files to run.

As a best practice, you must use PHP file to avoid using hard coded table names and field names: For example Magento allows you during install to provide a table name prefix. The easyest way to find this prefix without using PHP setup scripts.

How can Magento take care of the database server type used with your Magento? the database connexion connector type

On paper, Magento can use multiple databases server types. Default one is MySQL.

By default, Magento setups scripts can take care of the setups differences between each database servers with the connector type: Connector type is defined by the configuration node model used by the connection defined to run your module. By default, all connections of your Magento will inherit the connection default_setup, defined in the app/etc/config.xml. As you can see in the following screenshot, this node is by default defined as mysql4

When determining which scripts can be involved in the setup subfolder, Magento will look for the files matching the pattern %connector_type%-%source_state%-.+

How does Magento avoid playing two times the same setup script? the role of version

After finding all files matching the previous pattern, Magento reduces the list according to the current database state to only required versions with the version compare usage

At the end of this step, every script in the available list must be run

Running the scripts: how does Magento increase resource version number in core_resource table?

Plus the setup list to run, Magento will include one other that follows. Version number in core_resource table is not updated at the end of all the scripts, but after a script has completed normally. So if you need to restart the setup process, you’ll restart at the last error setup script

Conclusion about this Magento installer mechanism

This mechanism is really helpfull to deploy and normalize your different projects locations: by setting these scripts, you are sure that every location has the same required configuration values, concepts specified. Deploying a new location for a new developper or for the production server will be more easily because all required configuration values, tables, etc will be already set up.

One other great advantage of these scripts is to allow you to versionate only the scources code: no SQL state versionning requirement, all is managed through sources modules files

Develop and test setups scripts may have a cost to take care, but reduce risks based on misconfiguration or a forgotten SQL script

Magento modules versions

If you produce some modules for Magento connect, you have perhaps test your modules under differents releases.

In major and minor upgrades, you know that there is some upgrade in code source and so, if you want to have only one module version instead of using branchs, you must test in which case you are.

I’ve made here a summary of available module versions defined from 1.3 to current version (1.5 when I wrote this post), and for the related enterprise versions.

Magento core module versions summary

         0.0.1 0.0.1

Magento module versions usage

In previous Magento versions, there was some source code updates but no update in module version. So use module version carrefully

I'll upgrade this listing frequently

Unix permissions policies on Magento

Recently, Prestashop has been attacked by a worm: this worm uploaded on the server was able to update the content of a template; this template load many iframe which make you request some unexpected things. Many users have been infected. According to the first feedback, it seems that a good unix permissions policy on Prestashop folder could have stopped this infection.

Magento‘s hosted on unix servers seem to have the same problem: even if there is – for the moment – no problem like Prestashop’s one, most of the plateforms I saw have unix permissions policy problems, and so provide attack facilities. This is the reason why I wrote this article.

Unix permissions resume

First remember the basic of unix permissions: For files and folders, they define if:

  • You can read a file / folder
  • You can write or delete a file / write in this folder
  • You can execute this file / browse the folder

They are defined for three users groups:

  • owner of the resource
  • one group of user
  • other users which are neitherbowner, nor member of the group defined

With this combination of users groups and roles, we can set the most common access permissions on our magento. But which one is the most secured one?

Unix permissions and Magento deployments methods

Permissions set are closely linked to your deployment method: Do you:

  • Upgrade magento directly with Magento connect?
  • Use the installer setup process and deploy your data with magento’s installers?
  • Copy a database coming from a previous server and update the configuration?

In the following article I’ll take the option that you test your updates on a non production server, push your source code through FTP, svn, or other file mechanism, and push your database upgrades with magento installers. Then, I’ll explain you which updates you require to be able to use other delivery mechanism.

Unix permissions Magento requirements

Unix permissions for Web server user in Magento

The most important thing from the point of view of your server security is the permissions defined for the owner of the files: this user run a server, so accept connexions. We must control which services can be provide by this server.
In most of the case this user is the user which run the webserver.

Common rules to apply

First, we will check what is requested for the webserver user to be able to use normally Magento scripts: this user needs to be able to read all files (this is the only requirement to be able to use PHP script) and be able to browse the magento directory tree. That’s all. So your PHP scripts must not have more than 4 octal permission. For your directory tree, we will give to your account access to read and browse, so 5 octal permission.

This is the most common structure for all Magento’s files and folders. Now we will take a look at some special cases.

Folders which require to be writable

Take a look now at the var and media subfolders; webserver user will write there its own data:

  • in var subfolder, he will write: backup files, log files, perhaps cache file, session user files, index process lock files, etc.
  • in media subofolder, he will write thumbnails and media librairy

So these two subfolders require the write permission.

Executables scripts by Webserver user

The question is : does your webserver user require to be able to execute scripts?
Depending on your Magento version, only two or four files require to be executable:

  • Installer script (pear for 1.4.2 or lower,  mage for the earlier versions): all to install modules from magento connect, or to be able to update directly from magento connect your magento
  • scheduler entry point
  • pear embedded
  • pecl embedded

In our deployment model, pear is not used from back-office: so we do not set executable permission for our webserver to our webserver user: mage / pear / pecl won’t be executable by this user.
If you want to run scheduled tasks with this user, you must set up executable on the file. But you can also run scheduled tasks with another account and so remove also this permission on this file for this user.

Unix permissions for group in Magento

Which group content ?

With group user, we will define all the permissions for administratives tasks: deploy, upgrade, maintenance management, etc.
This allow us to be really independant of the webserver user permissions.
So the group choice depends on who run these tasks. Most common thing is to set up the group of the administrative user as group.

Be able to upgrade source code

To be able to upgrade source code, this group must be able to write in all magento folders, delete, read files.

Be able to manage var data

Unix permissions for all others users in Magento

If all others users are not the owner of the file nor part of the group which realize the administrative tasks, they should not be able to access Magento directory tree: so no permissions

Unix permissions for others deployment methods

Deployment method with Magento connect

No, do you really update your source code from magento connect? Hope I won’t be on your website when you’ll do an upgrade 🙂

But anyway, in this case because it’s the webserver user which run magento connect, you must:

  • Give read / write on all files and folder (packages potentially contain files which can go in every folder).
  • Give the ability to execute mage / pear / pecl

This is one of the reason I don’t recommand this method: all your source files are potentially writeable by webserver user, and so, can be editable like in Prestashop worm.

Initial setup starting with Magento install program

In this case, there’s no big difference with the way I explained before: the only difference is on the app/etc/ folder which must be writeable during installation process to allow creation and update of the local.xml file. Then, when installtion process will finish, you can remove the write access on app/etc and app/etc/local.xml file.


You can find here a summary of the policy we should apply on our Magento folder.

# edit the following variables according with your server configuration
#Check if run as root
if [ "$UID" -ne "$ROOT_UID" ] ; then
   echo "You must be root to do that!"
   exit 1
# ok, let's go for setting up permissions
find . -type f -exec chmod 0460 {} \;
find . -type d -exec chmod 0570 {} \;
find var -type f -exec chmod 0660 {} \;
find var -type d -exec chmod 0770 {} \;
find media -type f -exec chmod 0660 {} \;
find media -type d -exec chmod 0770 {} \;
# set up installer script for our admin account
if [ -f pear ]; then
    chmod 0570 pear;
   if [ -f mage ]; then
     chmod 0570 mage;
if [ -f ]; then
   chmod 0570