When and why we need to use start/end setup methods?

The startSetup() and endSetup() methods are commonly used for schema and data setup scripts by many developers. But do we actually need to call these methods for every install or upgrade script implementation? The correct answer may be quite unexpected. :) No, we don’t need to use these methods by default in our setup scripts in most cases. Let’s find out why.

We can jump through the code and check the implementation of the mentioned methods.

For Magento 1 the source code of \Varien_Db_Adapter_Pdo_Mysql::startSetup method is the following:

/**
 * Run additional environment before setup
 *
 * @return Varien_Db_Adapter_Pdo_Mysql
 */
public function startSetup()
{
    $this->raw_query("SET SQL_MODE=''");
    $this->raw_query("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0");
    $this->raw_query("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");

    return $this;
}

For Magento 2 the source code of \Magento\Framework\DB\Adapter\Pdo\Mysql::startSetup method is the following:

/**
 * Run additional environment before setup
 *
 * @return $this
 */
public function startSetup()
{
    $this->rawQuery("SET SQL_MODE=''");
    $this->rawQuery("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0");
    $this->rawQuery("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");

    return $this;
}

As we see, the code, in general, is the same for both Magento versions, except for the names of the classes.
So what does it do? Basically, it changes the SQL mode and foreign key check mode.

For those who doubt what is going on, here is a short aside. SQL modes are a configuration of the behavior of the MySQL server, consisting of modes, each one controls an aspect of query processing. Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. The control of the current SQL mode is being performed by the system variable sql_mode. To set the value, SET command is being used.

As for the startSetup() method, there are three actions are being performed/queries are being run:

  1. First of all it resets all the modes set by default: SET sql_mode = '';. Which corresponds the default value for the newly installed database (no special modes are set).

  2. Then it disables foreign keys checks. Do you also have a bad feeling about that? :)
    The previous value of FOREIGN_KEY_CHECKS options is also being saved into variable, as you might have already guessed, for the further restoring by endSetup() method.

    The FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables. Temporarily disabling referential constraints is useful when you need to re-create the tables and load data in any parent-child order.
    Without this option, it may require a lot of effort to define the correct parent-child order especially if you have a lot of tables, and a table can be a parent for some tables and a child for others.
    But as a result, you can insert data that violates foreign key constraints, and when the Magento enables the referential constraints (set FOREIGN_KEY_CHECKS option to 1) by endSetup() method, MySQL does not re-validate the inserted rows. In such way, you can break the integrity of the database, and the real issue in setup script will be hidden, but it can show up in the future.

  3. It enables the NO_AUTO_VALUE_ON_ZERO SQL mode, which affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either “NULL” or “0” into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for “0” so that only NULL generates the next sequence number.

    This mode can be useful if “0” value needs to be stored in a table’s AUTO_INCREMENT column to be treated as zero’s item in list (storing “0” is not a best practice, by the way.). E.g. customer group “NOT LOGGED IN” with ID 0 in customer_group database table.

Note, all these changes are session based (each application can set its session SQL mode to its own requirements).

Let’s check the implementation of endSetup() method. For Magento 1 the source code of \Varien_Db_Adapter_Pdo_Mysql::endSetup
 method is the following:

/**
 * Run additional environment after setup
 *
 * @return Varien_Db_Adapter_Pdo_Mysql
 */
public function endSetup()
{
    $this->raw_query("SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')");
    $this->raw_query("SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1)");

    return $this;
}

For Magento 2 the source code of \Magento\Framework\DB\Adapter\Pdo\Mysql::endSetup method is the following:

/**
 * Run additional environment after setup
 *
 * @return $this
 */
public function endSetup()
{
    $this->rawQuery("SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')");
    $this->rawQuery("SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1)");

    return $this;
}

In general this method restores SQL modes and the value of FOREIGN_KEY_CHECKS option.

Considering the mentioned above, the startSetup() and endSetup() methods should be used only when you need to:

  1. Store zero value (“0”) in a table’s AUTO_INCREMENT field.

  2. Disable referential constraints, in case you need to re-create the tables and load data in any parent-child order etc.

I also think, that startSetup() and endSetup() methods may be used only for schema setup scripts (when you create or alter database schema etc). But these methods should be never used for data setup scripts.

Don’t hesitate to share your ideas in the comments. Thanks for reading!