I want to achieve following functionality:

Whenever user tries to insert new data, i want to remove all old data from my custom table and insert new data.

For this, i want to use truncateTable() method. i am using code from below link:

Truncate table using resource model in Magento 2

But i am getting below error when i run code:

Something went wrong while saving this configuration: User Error: DDL statements are not allowed in transaction

Is there anyway we can truncate table using code?

Note: It works with delete but reason to use truncate is to reset my custom table's auto-increment key.

  • Hello Khushbu. you can try with database level programming. Jun 21 '19 at 10:02
  • You can create a database trigger for insert event for a particular table and try to delete the data and fill with a new value. There is also before and after the event too Jun 21 '19 at 10:03
  • Add me in skype if you want to explore it further skype: samumaretiya Jun 21 '19 at 10:04
  • ok i got your point. I will try this
    – Khushbu
    Jun 21 '19 at 10:12
  • 1
    I post the answer, magento provide the way to remove all the data from table using collection. you can use that one. Jun 21 '19 at 11:10

To remove old data from database, you can use magento code as well.

use Vendor\Extension\Model\ResourceModel\CollectionFactory;

class Yourclass
    protected $_collectionFactory;

    public function __construct(.....
    CollectionFactory $collectionFactory
        $this->_collectionFactory = $collectionFactory;

    public function execute()
       // To remove all old data.

       $mycollection = $this->_collectionFactory->create()
       foreach ($mycollection as $myitem) {

        // Now you can do code here to add new data

  • I used delete code only and it's working right now. I will post that here too. but i was looking to use truncate because deleting won't reset my auto increment key. I hope you understand
    – Khushbu
    Jun 21 '19 at 11:29
  • Truncate means you need to remove all the data from table. And above code do the same things. Jun 21 '19 at 11:49
  • No truncate is different in many ways from delete. you can read this: c-sharpcorner.com/UploadFile/65fc13/…
    – Khushbu
    Jun 21 '19 at 11:54

Right now i ended up with delete instead of truncate. Actually this is not the best solution because i want to reset my auto increment key in addition to deleting all data from my table. Still someone can use this code so i am sharing here:

Solution with delete instead of truncate is as below:

class custom
    protected $connection;
    protected $dbresource;

    public function __construct(\Magento\Framework\App\ResourceConnection $dbresource)
        $this->connection = $dbresource->getConnection();
        $this->dbresource = $dbresource; 

    public function mycustomfunction()
        $table = $this->dbresource->getTableName("my_custom_table");
                ['1 = 1']

I am still looking for a way to use truncate!!

  • Do you find any way to truncate table ?
    – Kozame
    Nov 29 '19 at 16:25
  • @Kozame No.. I used above solution only.
    – Khushbu
    Dec 18 '19 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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