4

Magento 2.3.1

After making the data patch @ following location

vendor/ModuleName/Setup/Patch/Data/AddMyColumnPatch.php

code given below for AddMyColumnPatch.php. When I run bin/magento setup:upgrade to get this patch installed I get following error at cli.

DDL statements are not allowed in transactions

I have used following file as reference to add column to my table using data patch.

vendor/magento/module-quote/Setup/Patch/Data/InstallEntityTypes.php Follow lines from 47 to 65

My AddMyColumnPatch.php code is:

<?php
namespace Vendor\ModuleName\Setup\Patch\Data;


use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\PatchRevertableInterface;

use Magento\Quote\Setup\QuoteSetupFactory;
use Magento\Sales\Setup\SalesSetupFactory;

use Psr\Log\LoggerInterface;

/**
 */
class AddressSuburbPatch implements DataPatchInterface, PatchRevertableInterface
{
    /**
     * Attribute Code of the Custom Attribute
     */
    const CUSTOM_ATTRIBUTE_CODE = 'my_column';

    /**
     * @var \Magento\Framework\Setup\ModuleDataSetupInterface
     */
    private $moduleDataSetup;

    /**
     * @var \Magento\Quote\Setup\QuoteSetupFactory
     */
    private $quoteSetupFactory;


    /**
     * @var Magento\Sales\Setup\SalesSetupFactory
     */
    private $salesSetupFactory;

    /**
     * @var \Psr\Log\LoggerInterface
     */
    private $logger;

    /**
     * @param \Magento\Framework\Setup\ModuleDataSetupInterface $moduleDataSetup
     */
    public function __construct(
        ModuleDataSetupInterface $moduleDataSetup,
        QuoteSetupFactory $quoteSetupFactory,
        SalesSetupFactory $salesSetupFactory,    
        LoggerInterface $logger
    )
    {

        $this->moduleDataSetup = $moduleDataSetup;          
        $this->quoteSetupFactory = $quoteSetupFactory;
        $this->salesSetupFactory = $salesSetupFactory;            
        $this->logger = $logger;
    }

    /**
     * {@inheritdoc}
     */
    public function apply()
    {
        $this->moduleDataSetup->getConnection()->startSetup();


        $this->logger->debug('DDL Statements error');

        $quoteSetup = $this->quoteSetupFactory->create(['setup' => $this->moduleDataSetup]);

        $quoteSetup->addAttribute('quote_address', self::CUSTOM_ATTRIBUTE_CODE, ['type' => 'text']);

        $salesSetup = $this->salesSetupFactory->create(['setup' => $this->moduleDataSetup]);
        $salesSetup->addAttribute('order_address', self::CUSTOM_ATTRIBUTE_CODE, ['type' => 'text']);

        $this->logger->debug('Script working');

        $this->moduleDataSetup->getConnection()->endSetup();

    }

    /**
     * {@inheritdoc}
     */
    public static function getDependencies()
    {

        return [

        ];
    }

    public function revert()
    {
        $this->moduleDataSetup->getConnection()->startSetup();

        $this->moduleDataSetup->getConnection()->endSetup();
    }

    /**
     * {@inheritdoc}
     */
    public function getAliases()
    {

        return [];
    }


}

2 Answers 2

7

After going through Declarative Schema docs again and referencing core magento code for Quote Module and Paypal Module I have figured that if you want to add a field into the existing table in Magento 2.3 you should use Configure declarative schema for that. Read more -

https://devdocs.magento.com/guides/v2.3/extension-dev-guide/declarative-schema/db-schema.html

So create a db_schema.xml file under Vendor/ModuleName/etc

<?xml version="1.0"?>

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="quote_address" resource="checkout" comment="Sales Flat Quote Address">
        <column xsi:type="varchar" name="suburb" nullable="true" length="255" comment="Suburb for Quote Address"/>

    </table>
    <table name="sales_order_address" resource="sales" comment="Sales Flat Order Address">
        <column xsi:type="varchar" name="mycolumn" nullable="true" length="255" comment="mycolumn for Sales Order Address"/>
    </table>
</schema>

Then generate whitelist for your db_schema as follows

bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_ModuleName

Run again and your column will be added to quote_address and order_sales_address tables.

bin/magento setup:upgrade

However, further investigation revealed that there is no need of making data patch for adding columns in flat tables quote_address and sales_order_address. Only declaring columns in db_schema.xml will do the job.

2

It should also be noted that you can't make schema updates because you're implementing DataPatchInterface. Check out patch files which implement SchemaPatchInterface. Its a simple change to your existing code and then it should work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.