Ttdfjt

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 VendorModuleNameSetupPatchData;


use MagentoFrameworkSetupPatchDataPatchInterface;
use MagentoFrameworkSetupModuleDataSetupInterface;
use MagentoFrameworkSetupPatchPatchRevertableInterface;

use MagentoQuoteSetupQuoteSetupFactory;
use MagentoSalesSetupSalesSetupFactory;

use PsrLogLoggerInterface;

/**
 */
class AddressSuburbPatch implements DataPatchInterface, PatchRevertableInterface

 /**
 * Attribute Code of the Custom Attribute
 */
 const CUSTOM_ATTRIBUTE_CODE = 'my_column';

 /**
 * @var MagentoFrameworkSetupModuleDataSetupInterface
 */
 private $moduleDataSetup;

 /**
 * @var MagentoQuoteSetupQuoteSetupFactory
 */
 private $quoteSetupFactory;


 /**
 * @var MagentoSalesSetupSalesSetupFactory
 */
 private $salesSetupFactory;

 /**
 * @var PsrLogLoggerInterface
 */
 private $logger;

 /**
 * @param MagentoFrameworkSetupModuleDataSetupInterface $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 [];
 

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.