0

This question is again important to me. Does anyone have a solution?

$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'root', '********');

$conn->exec('CREATE TABLE testIncrement ' .
            '(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50))');
$sth = $conn->prepare('INSERT INTO testIncrement (name) VALUES (:name);');
$sth->execute([':name' => 'foo']);
var_dump($conn->lastInsertId());

Output is: string(1) "lastInsertId". But when i lock table then lastInsertId is always 0. So this code always returns 0:

$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'root', 'paragraf');

$conn->exec('CREATE TABLE testIncrement ' .
            '(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50))');
$sth = $conn->prepare('LOCK TABLE testIncrement WRITE; INSERT INTO testIncrement (name) VALUES (:name); UNLOCK TABLES;');
$sth->execute([':name' => 'foo']);
var_dump($conn->lastInsertId());

Conclusion: is it possible and how to get lastInsertId when table is locked? Or am I wrong somewhere?

@Ernestas I tried your suggestion and here are result :(

Code:

$sthLastId = $conn->prepare('SELECT LAST_INSERT_ID();');
$sthLastId->execute();
print_r($sthLastId->fetchAll());

//Output when there is no lock: **string(2) "40" Array ( [0] => Array ( [LAST_INSERT_ID()] => 40 [0] => 40 ) )**
//And output when lock is use: **string(1) "0" Array ( )**

MySQL version: 5.6.26

Nikola F
  • 21
  • 2
  • I *think* your statement never executes. You issued multiple queries in a single `prepare`. Check the result of your `$sth->execute();`, if it's false - it indicates failure. Also, are you sure you need to control locking and unlocking manually? – Mjh Sep 21 '15 at 08:55
  • Its executes. I checked :( Yes, i'm sure, because there is more complex query then this, this is just an example. – Nikola F Sep 21 '15 at 09:01
  • To show how awful the PDO error handling is with multiple statements see [questions/32690361 - interesting error handling (pastebin)](http://pastebin.com/fcBQAtxA). It reports as always succeeding. It shows what is actually sent to the `mysql server` and the PDO execute result. – Ryan Vincent Sep 21 '15 at 10:26

1 Answers1

0

Answer

Everything seems fine. Try adding SELECT LAST_INSERT_ID() after unlock. I don't know why PDO does not work for you.

MySQL version: 5.5.44

Look to different answer: MySQL and PDO: Could PDO::lastInsertId theoretically fail?

Community
  • 1
  • 1
Ernestas Stankevičius
  • 2,420
  • 2
  • 24
  • 30
  • Do you get any error? Exception? have you tried directly in sql server? Or only in PHP? – Ernestas Stankevičius Sep 21 '15 at 09:47
  • No, there is no error or exception. I tried in sql server and SELECT_LAST_INSERT_ID works perfectly. The issue is in PDO apparently. I'm google it now but no luck. Are you tried to reproduce the problem exactly as i do, maybe my mysql server has issue or php? – Nikola F Sep 21 '15 at 10:19