Codeigniter 4 - MySQL Duplicate Entry exception not working

Question

I am using Codeigniter v4.4.5 and struggling to catch MySQL Duplicate entry exception. If there is any duplicate value then I want to handle in my controller so that I can send relevant message back to user. But when there is Duplicate Entry error, then Codeigniter is logging the exception but never send that exception back to Controller. I investigated further and can see that exception is first raised in CodeIgniter\Database\MySQLi\Connection.php (code below)

protected function execute(string $sql)
    {
        while ($this->connID->more_results()) {
            $this->connID->next_result();
            if ($res = $this->connID->store_result()) {
                $res->free();
            }
        }

        try {
            return $this->connID->query($this->prepQuery($sql), $this->resultMode);
        } catch (mysqli_sql_exception $e) {
            log_message('error', (string) $e);

            if ($this->DBDebug) {
                throw new DatabaseException($e->getMessage(), $e->getCode(), $e);
            }
        }

        return false;
    }

If i have DBDebug TRUE then it passes the exception to CodeIgniter\Database\BaseConnection (code below)

// Run the query for real
        try {
            $exception      = null;
            $this->resultID = $this->simpleQuery($query->getQuery());
        } catch (DatabaseException $exception) {
            $this->resultID = false;
        }

But then it never passes back to Controller. I have try/catch in controller as below, but it never comes to it, because framework code never passes exception back. Any idea guys. Below code from my controller.

try{
 //...More code above
  $modelProducts = new ProductsModel();
            $modelProducts->insert($data_product);
 //..EXCEPTION IS LOGGED HERE IN LOGS AS INSERTED VALUE IS DUPLICATED
//FURTHER CODE KEEPS getting progressed.
}
catch(DatabaseException $e)
{            
    **// THIS NEVER GET CALLED, EVEN THOUGH THERE WAS MYSQL EXCEPTION LOGGED IN LOGS**
    log_message('error',  __NAMESPACE__ . " > " . __FUNCTION__ . $e);
}
catch(Exception $e) {  
    **// THIS NEVER GET CALLED, EVEN THOUGH THERE WAS MYSQL EXCEPTION LOGGED IN LOGS**          
    log_message('error', __NAMESPACE__ . " > " . __FUNCTION__ . $e);
}

Answer 1

In case anyone else looking for answer, then you can do it 2 ways.

1. As mentioned by @b126 in comments, you can use db_error(). But make sure you check this immediately after your DB transaction. If you check after rollback then it not going to work.
2. Second way (which I used) is to $this->db->transException(true) . This way if there is DB exception then it will get caught by DatabaseException in try catch block. I am actually checking "Duplicate Error" in Database exception.

<?php

// When DBDebug in the Database Config must be true.

use CodeIgniter\Database\Exceptions\DatabaseException;

try {
    $this->db->transException(true)->transStart();
    $this->db->query('AN SQL QUERY...');
    $this->db->query('ANOTHER QUERY...');
    $this->db->query('AND YET ANOTHER QUERY...');
    $this->db->transComplete();
} catch (DatabaseException $e) {
    // Automatically rolled back already.
    // Check if the exception message or error code indicates a duplicate entry error
    if (strpos($e->getMessage(), 'Duplicate entry') !== false || $e->getCode() === 1062) {
          //return error message you want
      } else {
          //return error message you want
      }      
}

You can read more here - https://codeigniter4.github.io/CodeIgniter4/database/transactions.html#throwing-exceptions