Magento 1.7 Custom Module Setup createEntityTables() Method Fails

When creating a new Magento module you might have a need for creating a new entity type. In Alan Storm’s famed Advanced Magento ORM article, there is a brief tutorial on creating a new entity type with the method createEntityTables(). Note that this method may have different problems depending on your version — separate problems occur for Magento 1.6. The fix listed here is for Magento 1.7.

So let’s troubleshoot, shall we?

Test Case

In our module we have a setup file that is very basic:

/app/code/local/CompanyName/{ModuleName}/sql/{modulename}_setup/mysql4-install-1.0.0.php

1
2
3
4
5
6

/* @var $installer Mage_Eav_Model_Entity_Setup */
$installer = $this->startSetup();
 
$installer->createEntityTables(
    $this->getTable('module_entity')
);
/* @var $installer Mage_Eav_Model_Entity_Setup */
$installer = $this->startSetup();

$installer->createEntityTables(
    $this->getTable('module_entity')
);

The above results in:

1

Mage_Eav_Exception: Can't create table: module_entity
Mage_Eav_Exception: Can't create table: module_entity

The Solution

First some debugging. In the createEntityTables() method, you’ll see the following near the end:

1
2
3
4
5
6
7
8
9
10
11

$connection->beginTransaction();
try { 
    foreach ($tables as $tableName => $table) {
        $connection->createTable($table);
    }
    $connection->commit();
} catch (Exception $e) {
   Zend_Debug::dump($e->getMessage());
   $connection->rollBack();
   throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Can't create table: %s', $tableName));
}
$connection->beginTransaction();
try { 
    foreach ($tables as $tableName => $table) {
        $connection->createTable($table);
    }
    $connection->commit();
} catch (Exception $e) {
   Zend_Debug::dump($e->getMessage());
   $connection->rollBack();
   throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Can't create table: %s', $tableName));
}

Line 8 was added because the default exception is useless. Here we see the underlying problem:

1

User Error: DDL statements are not allowed in transactions
User Error: DDL statements are not allowed in transactions

So now we have more digging. If you follow $connection->commit() you’ll notice the following method is called:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

/**
 * Check transaction level in case of DDL query
 *
 * @param string|Zend_Db_Select $sql
 * @throws Zend_Db_Adapter_Exception
 */
protected function _checkDdlTransaction($sql)
{
    if (is_string($sql) && $this->getTransactionLevel() > 0) {
        $startSql = strtolower(substr(ltrim($sql), 0, 3));
        if (in_array($startSql, $this->_ddlRoutines)) {
            trigger_error(Varien_Db_Adapter_Interface::ERROR_DDL_MESSAGE, E_USER_ERROR);
        }
    }
}
/**
 * Check transaction level in case of DDL query
 *
 * @param string|Zend_Db_Select $sql
 * @throws Zend_Db_Adapter_Exception
 */
protected function _checkDdlTransaction($sql)
{
    if (is_string($sql) && $this->getTransactionLevel() > 0) {
        $startSql = strtolower(substr(ltrim($sql), 0, 3));
        if (in_array($startSql, $this->_ddlRoutines)) {
            trigger_error(Varien_Db_Adapter_Interface::ERROR_DDL_MESSAGE, E_USER_ERROR);
        }
    }
}

$this->_ddlRoutines is an array of three-letter strings of common DDL commands (CREATE, DROP, etc).

Putting it all together, the problem is that MySQL is not able to roll back DDL transactions. The easy fix would be to comment out the trigger_error method, since this would allow all other transactions to be rolled back in case of a problem. This is an issue of course since it’s a core file. Funny enough, in this bug report Magento Team recommends this as a solution.

For a method that is only used by module developers, this doesn’t look like sound advice. (If you take a peek in core files, this method isn’t used) Instead you have two options. The first is to do what the core does: create abstracted SQL (a major pain!) –

1
2
3
4
5
6
7
8
9
10
11
12

// Example of MySQL API
/**
 * Create table array('catalog/product', 'decimal')
 */
$table = $installer->getConnection()
    ->newTable($installer->getTable(array('catalog/product', 'decimal')))
    ->addColumn('value_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Value ID')
    ->addColumn(...
// Example of MySQL API
/**
 * Create table array('catalog/product', 'decimal')
 */
$table = $installer->getConnection()
    ->newTable($installer->getTable(array('catalog/product', 'decimal')))
    ->addColumn('value_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Value ID')
    ->addColumn(...

If you are partial to saving time in the long run, just extend the method instead. You’ll create a new helper file below, and you’ll be copying the entire createEntityTables() method into your helper file. (For brevity, only a portion of the code in the method is shown — you’ll need to comment out two lines)

app/code/local/{CompanyName}/{ModuleName}/Setup/Helper.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

<?php
 
class CompanyName_ModuleName_Setup_Helper extends Mage_Eav_Model_Entity_Setup
{
    public function createEntityTables($baseTableName, array $options = array())
    {
        ...
 
        /**
         * Remove transaction code due to issues with errors.
         */
        //$connection->beginTransaction();
        try { 
            foreach ($tables as $tableName => $table) {
                $connection->createTable($table);
            }
            $connection->commit();
       } catch (Exception $e) {
           //$connection->rollBack();
           throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Can't create table: %s', $tableName));
       }
    }
}
<?php

class CompanyName_ModuleName_Setup_Helper extends Mage_Eav_Model_Entity_Setup
{
    public function createEntityTables($baseTableName, array $options = array())
    {
        ...

        /**
         * Remove transaction code due to issues with errors.
         */
        //$connection->beginTransaction();
        try { 
            foreach ($tables as $tableName => $table) {
                $connection->createTable($table);
            }
            $connection->commit();
       } catch (Exception $e) {
           //$connection->rollBack();
           throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Can't create table: %s', $tableName));
       }
    }
}

Note that you might not be able to rely on createEntityTables() long. In the same bug report listed above, Mage Team goes on to say this could be deprecated in the future since the core doesn’t make use of the method. Which begs the question… why doesn’t it?

Discussion
deric says:

If you follow along with the Alan Storm article, you will have created app/cpde/local/Company/Module/Model/Resource/Setup.php which should have a class similar to: Company_Module_Model_Resource_Setup extends Mage_Eav_Model_Entity_Setup

You can just copy the function createEntityTables to this class, and avoid having to use helpers or core rewrites.

Wakamin says:

Hi, I followed both Alan Storm and your tutorial.

After I ran this app/code/local/Alanstormdotcom/Complexworld/sql/complexworld_setup/mysql4-install-0.1.0.php

whit the following code

startSetup();
$installer->createEntityTables(
$this->getTable(‘complexworld/eavblogpost’)
);

My table prefix come twice in the table created, my table prefix is demo_

demo_demo_eavblog_posts
demo_demo_eavblog_posts_datetime
demo_demo_eavblog_posts_decimal
demo_demo_eavblog_posts_int
demo_demo_eavblog_posts_text
demo_demo_eavblog_posts_varchar

And this error appear on the browser
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘mytablename.demo_eavblog_posts’ doesn’t exist

And no additional row in the eav_attribute_set table.

Any idea? BTW many many thanks for this article.

Wakamin says:

Sorry, the code in this file app/code/local/Alanstormdotcom/Complexworld/sql/complexworld_setup/mysql4-install-0.1.0.php

is

$installer = $this->startSetup();
$installer->createEntityTables(
$this->getTable(‘complexworld/eavblogpost’)
);

Arnafee says:

Thank you for this advice!!! I was also following the Alan Storm tutorial and ran into this problem. This answer saved my sunday evening!

Ricky says:

Great !!! Thanks a lot. I’d been looking for the solution to this problem for 2 days.

Mark says:

Hi!

Not sure if this is related, but under CE 1.9.0.1, when importing products (dataflow profile), I am getting the same error:

User Error: DDL statements are not allowed in transactions

Actually, several lines in system.log.

I can’t see any problems though, products are correctly imported in the system.

Is it save to comment out the trigger_error in _checkDdlTransaction method?

Thanks for the article!

Mark

Stas says:

Prefix bug (demo_demo_eavblog_posts) resolved here http://stackoverflow.com/questions/16995383/magento-1-6-2-got-double-prefix-while-calling-createentitytables-during-custom-e

Leave a Reply

About The Author

Zachary Schuessler is a full-stack consultant based in Phoenix, Arizona. ☀️