DB Insert Timing

Ran into something strange today. Running CI4.2.11 with PHP 8.025
I have a process setup that takes some input, does an insert into the database, opens a dialog box which is a confirmation, takes that click, then 2 searches are run and then another insert is run. All works great, except according to the timestamp in MariaDB, both inserts take place at the exact same time - down to 6 decimal places! I tried inserting a "usleep(150000)" before the second insert, but it didn't make any difference (actually not sure it is even being recognized/executed).
Time of first insert: 2023-01-16 14:48:41.920000
Time of second insert: 2023-01-16 14:48:41.920000
Because of this, attempting to find the most recent record is failing (well, actually it is returning BOTH records).
Is CI holding the connection open and then MariaDB uses the time the connection was opened for the timestamp or something?
Help!

Is CI holding the connection open

Yes in a request unless you close the connection.

and then MariaDB uses the time the connection was opened for the timestamp or something?

I guess not.

Can you show minimum sample code?

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Can you show minimum sample code?

Sure. Here it is (I cut out a bunch of stuff that isn't relative - the code works, it is just the time stamp I am questioning):

PHP Code:

$data = array(
 'update_reason' => $update_reason.$statusMsg,
 .
 .
 .
 'internal_notes' => $post['internal_notes'],
);

$this->lotsVersionModel->insert($data); <<-- Row shows a time stamp of say "2023-01-16 15:08:14.814000"

return redirect() ->to("/auction/lots/delete_lot_confirm/".$newID.'/'.$post['parent_id'])
 ->with('info', 'Record updated successfully.');
 
*********
Confirmation screen opens
and User confirms which leads to:
*********

public function move_to_stockyard($lotVersionID,$masterID)
{
 //Get the current auction # so we can return to that list
 $record = $this->lotsMasterModel ->select('auction_lots_master.auction_id')
 ->where('id',$masterID)
 ->first();
 $data = array (
 'auction_id' => 1,
 );
 $this->lotsMasterModel->update($masterID,$data);

 //close db connection to avoid duplicate time stamps
 $this->lotsVersionModel->close();

 //Need to update history to reflect move to Stockyard
 //duplicate record and change "reason"
 
 $oldRow = $this->lotsVersionModel->where('id',$lotVersionID)->first();
 
 $oldRow['update_reason'] = '** Moved to Stockyard **';
 unset($oldRow->id);
 unset($oldRow->created_at);
 
 $this->lotsVersionModel->insert($oldRow); <<-- Row shows an identical time stamp of: "2023-01-16 15:08:14.814000"

} 


---

In case it helps, the field definition:

Code:

`created_at` datetime(6) NOT NULL DEFAULT current_timestamp(3),

How do you set the  created_at value?
Using Model $useTimestamps?
https://codeigniter4.github.io/CodeIgnit...timestamps

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

How do you set the  created_at value?
Using Model $useTimestamps?
https://codeigniter4.github.io/CodeIgnit...timestamps

No, I let MariaDB set it on record creation. The field is a DateTime column with a default value of "Current_TimeStamp" ( https://mariadb.com/kb/en/datetime/ ).

Check your real SQL statements with Debug Toolbar or DB Server log.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Check your real SQL statements with Debug Toolbar or DB Server log.

Code:

INSERT INTO `auction_lots_versions` (`parent_id`, `created_at`, `createdby_id`)
VALUES ('5297', '2023-01-17 09:55:09', '8')

Ok that tells me what is going on. CI is overriding and putting the 'created_at' in there, rather than allowing MariaDB to do it as I thought it was doing. I remember reading something about that in the docs. Off to find it! 

Thanks Kenjis you are always so helpful!! I very much appreciate it!

---

So looking at my Model, I see I included the "created_at" field in the allowed fields. Simply removing this solves the issue and allows MariaDB to set the timestamps.

PHP Code:

namespace App\Models;

class LotsVersionModel extends \CodeIgniter\Model
{
 protected $table = 'auction_lots_versions';

 protected $allowedFields = [
 'parent_id','created_at','createdby_id','update_reason','lot_num_temp','lot_num_final',
 'consigner_id','market_id','rep_id_1','rep_id_2','rep_id_3','location_id','location_cs_id',
 .
 .
 .
 'orig_contract_name','location_state'
 ];
} 


Looking into the docs, I get the impression that I needed to explicitly allow CI to set the created_at timestamp. Am I missing something?

$createdField
Specifies which database field to use for data record create timestamp. Leave it empty to avoid updating it (even if $useTimestamps is enabled).

Since I don't specify the field, shouldn't it NOT set the timestamp?

The $useTimestamps is false by default.
https://codeigniter4.github.io/CodeIgnit...timestamps
If it is false, CI Model does not set the timestamps.

Check your model code.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere,

the same value must be used throughout the column definition. This is permitted:

Code:

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

This is not permitted:

Code:

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

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

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

The $useTimestamps is false by default.
https://codeigniter4.github.io/CodeIgnit...timestamps
If it is false, CI Model does not set the timestamps.

Check your model code.

My model code is in the post above yours. I didn't set the timestamp one way or the other. Maybe I need to explicitly set it to false. Could it be set in a config file or something?