MySQL 8 and insert_id()

almostcompletely

Anyone running MySQL 8.0 now having trouble with $this->db->insert_id() returning a valid ID?

I'm on PHP 7.4 and can't seem to get anything other than "0" after a $this->test_model->insert(...); Used to work fine.

admin

Double check that the column is using an auto_increment. If so, then maybe check it manually with the native functions to see if it is indeed the MySQL driver:
https://www.php.net/manual/en/mysqli.insert-id.php

almostcompletely

auto_increment is on. The table and the code has been running for a number of years.

$el = $this->xx_model->find_one('','id DESC);
$id = $el['id'] 

works.

almostcompletely

INSERT INTO `mytable` (staffid,datefrom,dateto,bhmode,daysequivelent) 
VALUES(70,"2023-01-20","2023-01-20",1,1);
SELECT LAST_INSERT_ID();

Works fine from MySQL CLI. It's not the database.

I have the same problem with another Fuel site too. v1.5.2 in both cases.

admin

I don't currently have MySQL 8 to test. However, I think this may be something with the CI DB Driver but haven't found anything on their GitHub repo about it.

almostcompletely

Looks like insert_id is a property for MySQL8, rather than a function. So
$this->db->insert_id
works

I don't have MySQL 7.x access, can anyone check if this is backwards compatible please?