Need some help on Transaction (Transaction didn't rollback)

Dear all,

Sorry for my broken English.
I'm using codeigniter 4 and PHP 8.1.1
I tried below code in model and call in controller, (I turn off DBdebug)
the first query is correct and the second one I put wrong column name to test,
This is what I get in log: Unknown column 'mobile1' in 'field list'
And the first query still executed.

Code:

function testTransaction()
{
        $this->db->transStart();

        $builder = $this->db->table('sms_otp');
        $builder->insert(['mobile' => 12344, 'usage' => 1]);

        $builder = $this->db->table('sms_otp');
        $builder->insert(['mobile1' => 123555, 'usage' => 1]);

        $this->db->transComplete();
}

I'm using manual way

Code:

function testTransaction()
{
        $this->db->transBegin();

        $builder = $this->db->table('sms_otp');
        $builder->insert(['mobile' => 123444445, 'usage' => 0]);

        $builder = $this->db->table('sms_otp');
        $builder->insert(['mobile1' => 12344444, 'usage' => 0]);

        if ($this->db->transStatus() === false)
        {
            log_message('error', 'rollback ?');
            $this->db->transRollback();
        }
        else
        {
            $this->db->transCommit();
        }
}

Error message i get
ERROR - 2021-12-29 10:51:03 --> Unknown column 'mobile1' in 'field list'
ERROR - 2021-12-29 10:51:03 --> rollback ?

Please help, or any idea what I should check for.
Thanks

Hi All,


The problem Settle already
Is because of table engine problem should use InnoDB not MyISAM.


FYI You can use below code to check your database table engine type.

Code:

SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY table_name;

Thanks all.

Make sure your DBMS supports transactions.

MySQLi MyISAM supports transactions.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

MySQLi MyISAM supports transactions.

The manual says it does not support it.
https://dev.mysql.com/doc/refman/8.0/en/...ngine.html

Ya your right I read all of the php.net on MySQLi and it says that you must use the InnoDB Engine.. Sorry about that.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )