cakephp: QueryBuilder - lastInsertId() returns "0"

This is a (multiple allowed):

• bug

• enhancement

• feature-discussion (RFC)

• CakePHP Version: 3.4.7

• Platform and Target: MariaDB (5.5.5-10.1.21), PHP Version 7.1.1, Apache, Windows 7

What you did

Trying to insert a new record and get the lastInsertId (AUTO_INCREMENT).

What happened

The result of lastInsertId() is always “0”.

Example code:

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR (255),
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8 COLLATE = utf8_unicode_ci ;

// composer require cakephp/database
require_once __DIR__ . '/vendor/autoload.php';

// Database settings
$config = array(
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => '3306',
    'username' => 'root',
    'password' => '',
    'database' => 'test',
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
);

$driver = new \Cake\Database\Driver\Mysql($config);
$db = new \Cake\Database\Connection(['driver' => $driver]);

$row = [
    'username' => 'max'
];

$result = $db->insert('users', $row);

$newId = $result->lastInsertId();
echo $newId;  // value is "0"

If I try the same with a native PDO query it works, but not with the CakePHP QueryBuilder.

$pdo = $db->getDriver()->connection();
$stmt = $pdo->query("SELECT LAST_INSERT_ID()");
$lastId = $stmt->fetchColumn(); // This works. Returns 1 or more.

What you expected to happen

Returns the ID of the last inserted row or sequence value.