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.

Let’s delve into the code and examine the implementation of the aforementioned 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, it resets all modes to their default: SET sql_mode = '';, which corresponds to the default value for a newly installed database (no special modes are set).
2. It then disables foreign key checks. Does that give you a sense of unease?
   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 this way, you can compromise the integrity of the database; the real issue in the setup script may be hidden and could emerge later.

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 that all these changes are session-based (each application can set its session SQL mode according 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). However, these methods should never be used for data setup scripts.

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