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. Commented Jun 21, 2019 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 Commented Jun 21, 2019 at 10:03
  • Add me in skype if you want to explore it further skype: samumaretiya Commented Jun 21, 2019 at 10:04
  • ok i got your point. I will try this
    – Khushbu
    Commented Jun 21, 2019 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. Commented Jun 21, 2019 at 11:10

2 Answers 2


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
    Commented Jun 21, 2019 at 11:29
  • Truncate means you need to remove all the data from table. And above code do the same things. Commented Jun 21, 2019 at 11:49
  • No truncate is different in many ways from delete. you can read this: c-sharpcorner.com/UploadFile/65fc13/…
    – Khushbu
    Commented Jun 21, 2019 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
    Commented Nov 29, 2019 at 16:25
  • @Kozame No.. I used above solution only.
    – Khushbu
    Commented Dec 18, 2019 at 11:46

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.