New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

QueryBuilder - lastInsertId() returns "0" #10836

Closed
odan opened this Issue Jun 28, 2017 · 18 comments

Comments

Projects
None yet
8 participants
@odan
Contributor

odan commented Jun 28, 2017

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.

@VarCI-bot VarCI-bot added the Defect label Jun 28, 2017

@dereuromark dereuromark added this to the 3.4.10 milestone Jun 28, 2017

@lorenzo

This comment has been minimized.

Show comment
Hide comment
@lorenzo

lorenzo Jun 28, 2017

Member

@odan Try with the query builder instead of working directly with the connection instead while this is being investigated.

Member

lorenzo commented Jun 28, 2017

@odan Try with the query builder instead of working directly with the connection instead while this is being investigated.

@lorenzo

This comment has been minimized.

Show comment
Hide comment
@lorenzo
Member

lorenzo commented Jun 28, 2017

@odan Here you have an example of using the query builder for inserting https://github.com/cakephp/cakephp/blob/master/tests/TestCase/Database/QueryTest.php#L4125-L4136

@markstory markstory added the database label Jun 28, 2017

@markstory

This comment has been minimized.

Show comment
Hide comment
@markstory

markstory Jun 28, 2017

Member

What does $pdo->lastInsertId('users') return in your PDO example. That is what CakePHP is doing under the hood.

Member

markstory commented Jun 28, 2017

What does $pdo->lastInsertId('users') return in your PDO example. That is what CakePHP is doing under the hood.

@odan

This comment has been minimized.

Show comment
Hide comment
@odan

odan Jun 28, 2017

Contributor

Hi @lorenzo and @markstory

I've tried it. The new row is inserted but the lastInsertId is still "0".

$query = new \Cake\Database\Query($db);
$insert = $query
	->insert(['username'])
	->into('users')
	->values(['username' => 'max'])
	->execute();

$id = $insert->lastInsertId('users', 'id'); // "0"
$id2 = $insert->lastInsertId('users'); // "0"
$id3 = $insert->lastInsertId(); // "0"
$affected = $insert->rowCount(); // 1
Contributor

odan commented Jun 28, 2017

Hi @lorenzo and @markstory

I've tried it. The new row is inserted but the lastInsertId is still "0".

$query = new \Cake\Database\Query($db);
$insert = $query
	->insert(['username'])
	->into('users')
	->values(['username' => 'max'])
	->execute();

$id = $insert->lastInsertId('users', 'id'); // "0"
$id2 = $insert->lastInsertId('users'); // "0"
$id3 = $insert->lastInsertId(); // "0"
$affected = $insert->rowCount(); // 1
@lorenzo

This comment has been minimized.

Show comment
Hide comment
@lorenzo

lorenzo Jun 28, 2017

Member

@odan ok, that looks something like you don't have auto increment keys in your table?

I'm saying that because the test case for the query builder + lastInsertId() is passing... as the rest of the tests in the ORM.

I've seen this behaviour before and turned out to be that I missed the auto increment setting

Member

lorenzo commented Jun 28, 2017

@odan ok, that looks something like you don't have auto increment keys in your table?

I'm saying that because the test case for the query builder + lastInsertId() is passing... as the rest of the tests in the ORM.

I've seen this behaviour before and turned out to be that I missed the auto increment setting

@odan

This comment has been minimized.

Show comment
Hide comment
@odan

odan Jun 28, 2017

Contributor

@lorenzo Yes there is an AUTO_INCREMENT setting for the primary key (id).

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,

Screenshot:

image

It works if I add new row manually in MySQL-Workbench and SQLyog.
Also this code returns the new ID. But this is not what I'm looking for.

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

And this native PDO insert works too:

$host = '127.0.0.1';
$dbname = 'test';
$username = 'root';
$password = '';
$charset = 'utf8';
$collate = 'utf8_unicode_ci';

$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=$charset", $username, $password,
	array(
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
		PDO::ATTR_PERSISTENT => false,
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
		PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES $charset COLLATE $collate"
	)
);

$row = [
    'username' => 'bob',
];
$sql = "INSERT INTO users SET username=:username";
$status = $pdo->prepare($sql)->execute($row);

if ($status) { // true
    $lastId = $pdo->lastInsertId();
    echo $lastId;
}

The result:

image

Contributor

odan commented Jun 28, 2017

@lorenzo Yes there is an AUTO_INCREMENT setting for the primary key (id).

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,

Screenshot:

image

It works if I add new row manually in MySQL-Workbench and SQLyog.
Also this code returns the new ID. But this is not what I'm looking for.

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

And this native PDO insert works too:

$host = '127.0.0.1';
$dbname = 'test';
$username = 'root';
$password = '';
$charset = 'utf8';
$collate = 'utf8_unicode_ci';

$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=$charset", $username, $password,
	array(
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
		PDO::ATTR_PERSISTENT => false,
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
		PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES $charset COLLATE $collate"
	)
);

$row = [
    'username' => 'bob',
];
$sql = "INSERT INTO users SET username=:username";
$status = $pdo->prepare($sql)->execute($row);

if ($status) { // true
    $lastId = $pdo->lastInsertId();
    echo $lastId;
}

The result:

image

@saeideng

This comment has been minimized.

Show comment
Hide comment
@saeideng

saeideng Jun 28, 2017

Member

and your db after your query by cake query?

Member

saeideng commented Jun 28, 2017

and your db after your query by cake query?

@lorenzo

This comment has been minimized.

Show comment
Hide comment
@lorenzo

lorenzo Jun 28, 2017

Member

@odan there is something odd. I think you may be using different databases by mistake or something similar. I copy pasted your code, created a new database and ran the script multiple times:

screen shot 2017-06-28 at 20 03 02

Can you maybe create a sample repo with the SQL we could use locally for testing?

Member

lorenzo commented Jun 28, 2017

@odan there is something odd. I think you may be using different databases by mistake or something similar. I copy pasted your code, created a new database and ran the script multiple times:

screen shot 2017-06-28 at 20 03 02

Can you maybe create a sample repo with the SQL we could use locally for testing?

@odan

This comment has been minimized.

Show comment
Hide comment
@odan

odan Jun 28, 2017

Contributor

Ok this is crazy.... I've testet the same code on my home computer and here it works.

image

But then I enabled a breakpoint and started XDebug and the result is 0 again.

image

image

If I remove the debugger breakpoint in PHPStorm (XDebug is still running) everything works!
How is that possible?

Contributor

odan commented Jun 28, 2017

Ok this is crazy.... I've testet the same code on my home computer and here it works.

image

But then I enabled a breakpoint and started XDebug and the result is 0 again.

image

image

If I remove the debugger breakpoint in PHPStorm (XDebug is still running) everything works!
How is that possible?

@lorenzo

This comment has been minimized.

Show comment
Hide comment
@lorenzo

lorenzo Jun 28, 2017

Member

Ok, that is indeed crazy. First time I see this.

When using xdebug, does it at least outputs the right value?

Member

lorenzo commented Jun 28, 2017

Ok, that is indeed crazy. First time I see this.

When using xdebug, does it at least outputs the right value?

@odan

This comment has been minimized.

Show comment
Hide comment
@odan

odan Jun 28, 2017

Contributor

@lorenzo

I try to summarize it:

XDebug is enabled:

image

With this breakpoint it doesn't work:
image

image

Without this breakpoint it works:

image

image

The same behavior on two different computers.

When using xdebug, does it at least outputs the right value?

Yes, but only if I remove all breakpoints.

Contributor

odan commented Jun 28, 2017

@lorenzo

I try to summarize it:

XDebug is enabled:

image

With this breakpoint it doesn't work:
image

image

Without this breakpoint it works:

image

image

The same behavior on two different computers.

When using xdebug, does it at least outputs the right value?

Yes, but only if I remove all breakpoints.

@lorenzo

This comment has been minimized.

Show comment
Hide comment
@lorenzo

lorenzo Jun 28, 2017

Member

@odan sounds like you will have to open a bug in the xdebug repo? Try first to use the latest php + debug available to discard that they didn't fix that issue already.

I'm closing given that the feature works correctly and that we are not in control of what debug does to the code. We'll be happy to reopen this if new information is added with code we can change to fix or make this less of an issue

Member

lorenzo commented Jun 28, 2017

@odan sounds like you will have to open a bug in the xdebug repo? Try first to use the latest php + debug available to discard that they didn't fix that issue already.

I'm closing given that the feature works correctly and that we are not in control of what debug does to the code. We'll be happy to reopen this if new information is added with code we can change to fix or make this less of an issue

@raul338

This comment has been minimized.

Show comment
Hide comment
@raul338

raul338 Jun 28, 2017

Contributor

lastInsertId only works if the last query was an insert, if you query a select or another query lastInserId returns 0
I guess in your debugging some variable inspector would execute another query (like a count?) set lastinsertid to 0

Contributor

raul338 commented Jun 28, 2017

lastInsertId only works if the last query was an insert, if you query a select or another query lastInserId returns 0
I guess in your debugging some variable inspector would execute another query (like a count?) set lastinsertid to 0

@odan

This comment has been minimized.

Show comment
Hide comment
@odan

odan Jun 28, 2017

Contributor

@lorenzo OK thank you :-)

Contributor

odan commented Jun 28, 2017

@lorenzo OK thank you :-)

@gmponos

This comment has been minimized.

Show comment
Hide comment
@gmponos

gmponos Jun 29, 2017

Contributor

lastInsertId only works if the last query was an insert, if you query a select or another query lastInserId returns 0
I guess in your debugging some variable inspector would execute another query (like a count?) set lastinsertid to 0

I have stopped using lastInsertId for the same reason. When ever I want to get back the lastInsertedId I always query the database to find the record. It's too unstable getting the id from lastInsertId and you might easily end up with race conditions.

Contributor

gmponos commented Jun 29, 2017

lastInsertId only works if the last query was an insert, if you query a select or another query lastInserId returns 0
I guess in your debugging some variable inspector would execute another query (like a count?) set lastinsertid to 0

I have stopped using lastInsertId for the same reason. When ever I want to get back the lastInsertedId I always query the database to find the record. It's too unstable getting the id from lastInsertId and you might easily end up with race conditions.

@odan

This comment has been minimized.

Show comment
Hide comment
@odan

odan Jun 29, 2017

Contributor

Here is an update:

There is an issue with the magic method: __debugInfo() in Driver.php

https://github.com/cakephp/cakephp/blob/master/src/Database/Driver.php#L386

Xdebug uses PHP's var_dump() function for displaying variables.
https://xdebug.org/docs/display

Changing the xdebug.overload_var_dump value in php.ini has no positive effect.
https://derickrethans.nl/xdebug-2.3-overload-vardump.html

Workaround / Fix: If you return just an empty array it works like expected.

image

This fix also works:
image

The isConnected method starts a database query (SELECT 1) to check if the connection is available.

image

$connected = $this->_connection->query('SELECT 1');

After every SELECT the lastInsertId() is 0. That's the reason.

@lorenzo For me this is no more an Xdebug issue. What do you think?

Contributor

odan commented Jun 29, 2017

Here is an update:

There is an issue with the magic method: __debugInfo() in Driver.php

https://github.com/cakephp/cakephp/blob/master/src/Database/Driver.php#L386

Xdebug uses PHP's var_dump() function for displaying variables.
https://xdebug.org/docs/display

Changing the xdebug.overload_var_dump value in php.ini has no positive effect.
https://derickrethans.nl/xdebug-2.3-overload-vardump.html

Workaround / Fix: If you return just an empty array it works like expected.

image

This fix also works:
image

The isConnected method starts a database query (SELECT 1) to check if the connection is available.

image

$connected = $this->_connection->query('SELECT 1');

After every SELECT the lastInsertId() is 0. That's the reason.

@lorenzo For me this is no more an Xdebug issue. What do you think?

@markstory

This comment has been minimized.

Show comment
Hide comment
@markstory

markstory Jun 29, 2017

Member

Connection != null is probably good enough for debug data. I wouldn't change isConnected() though.

Member

markstory commented Jun 29, 2017

Connection != null is probably good enough for debug data. I wouldn't change isConnected() though.

@odan

This comment has been minimized.

Show comment
Hide comment
@odan

odan Jun 29, 2017

Contributor

I added a PR. Thank you @markstory

Contributor

odan commented Jun 29, 2017

I added a PR. Thank you @markstory

lorenzo added a commit that referenced this issue Jun 29, 2017

Merge pull request #10839 from odan/patch-1
Fixed Xdebug issue in combination with isConnected() #10836
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment