$mysqli->commit() returns false , but it is working!

[invader7]

Hello , this is my first post in this forum , i would like to see the following code and tell me your opinion , not about the security issues not about how good or bad it is. Its just a simple demonstration in order to show you the problem.

<?php

include('config/functions.php');

$mysqli = connectionToDb();

$mysqli->autocommit(FALSE);

$stmt = $mysqli->prepare("INSERT INTO users (first_name,last_name) VALUES ('stelios','stelios2')");
$stmt->execute();
$stmt->close();

$stmt = $mysqli->prepare("INSERT INTO users (first_name,last_name) VALUES ('stelios3','stelios4')");
$stmt->execute();
$stmt->close();

var_dump($mysqli->commit());

$mysqli->close();

This is the code and the var_dump($mysqli->commit()); line prints false ! BUT , the inserts are in the database. If i comment out the commit line , then nothing is in the database. 

 

Can you advise ? Thanks !

[Barand]

I was unable to recreate that on my own test table.

 

You do seem to be missing the point of prepared queries though. You keep the values separate from the query, prepare the statement once then execute with different your values, several times as required

$mysqli->autocommit(FALSE);

$sql = "INSERT INTO users (first_name,last_name) VALUES (?,?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('ss', $fn, $ln);

$fn = 'stelios';
$ln = 'stelios2';
$stmt->execute();
 
$fn = 'stelios3';
$ln = 'stelios4';
$stmt->execute();
 
var_dump($mysqli->commit());
 
$mysqli->close();

[invader7]

Thanks for answering , you are correct this example does not going with the point of prepared statements. It was just an example, i wanted to stay at the commit part.

 

Let's say that the code is like this , with two individual queries

include('config/functions.php');
$mysqli = connectionToDb();
$mysqli->autocommit(FALSE);
 
$stmt = $mysqli->prepare("INSERT INTO users (first_name,last_name) VALUES (?,?)");
$stmt->bind_param('ss', $fn, $ln);
$fn = 'stelios';
$ln = 'stelios2';

$stmt->execute();
$stmt->close();
 
$stmt = $mysqli->prepare("INSERT INTO customers (first_name,last_name) VALUES (?,?)");
$stmt->bind_param('ss', $fn, $ln);
$fn = 'stelios';
$ln = 'stelios2';

$stmt->execute();
$stmt->close();
var_dump($mysqli->commit());
$mysqli->close();

My point with this code is to write to two different tables and to be sure that all the queries executed successfully.

 

I want the second query to be executed ONLY if the first query succeeded and if the second query fails then the first query must rollback.

 

I have some mechanisms (not shown here) in order to make sure that the first query is succeeded. Then after a successful second query i'm calling commit and everything works but the return value is FALSE.

 

If i comment out commit in this code, then nothing is written in the tables.

 

so this code returns TRUE to you ?

Edited November 18, 2014 by invader7

[jazzman1]

 

I was unable to recreate that on my own test table.

Hm...it's weird...works fine for me. The script returns bool(true).

 

//EDIT::

 

 

If i comment out commit in this code, then nothing is written in the tables.

 

Like this?

<?php

$mysqli = new mysqli("::1", "lxc", "password", "test");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

//$mysqli->autocommit(FALSE);
 
$stmt = $mysqli->prepare("INSERT INTO test.users (first_name,last_name) VALUES ('stelios','stelios2')");
$stmt->execute();
$stmt->close();
 
$stmt = $mysqli->prepare("INSERT INTO test.users (first_name,last_name) VALUES ('stelios3','stelios4')");
$stmt->execute();
$stmt->close();
 
//var_dump($mysqli->commit());
 
$mysqli->close();

Edited November 18, 2014 by jazzman1

[invader7]

Thanks for answering.

 

No , with 

$mysqli->autocommit(FALSE);
//var_dump($mysqli->commit());

[jazzman1]

works for me. what db engine are you using?

[invader7]

MySQL , yes i'm pulling out my hairs it's very strange !! i'm going to look over my code again !

[mikosiko]

Jazz man is asking if you are using Myisam or Innodb or other storage engine for your table... The behavior that are you having is indicating that you are using most likely Myisam which is non-transactional, therefore autocommit, commit nor rollback have any effect

Edited November 18, 2014 by mikosiko

[invader7]

Jazz man is asking if you are using Myisam or Innodb or other storage engine for your table... 

 

oo sorry , i have innoDB , :/

[jazzman1]

In this example, the auto-commit mode is disabled, so no SQL statements are committed until you call the method commit explicitly. To be more precise, according the docs  the default is for a SQL statement to be committed when it is completed, not when it is executed. That's a normal behaviour for the InnoDB engine.

Edited November 19, 2014 by jazzman1

[invader7]

In this example, the auto-commit mode is disabled, so no SQL statements are committed until you call the method commit explicitly. To be more precise, according the docs  the default is for a SQL statement to be committed when it is completed, not when it is executed. That's a normal behaviour for the InnoDB engine.

 

I know that it's working as it's supposed to , this is what i said from the beginning , but my concern is why it prints false since the changes committed.

 

The code is working ! The problem is the false print.

[mikosiko]

tested your code myself ... unable to reproduce what you are telling us... as a result of var_dump($mysqli->commit()) I'm getting the expected "bool(true)" ...

 

If you include this lines before your var_dump() what do you get?  (expected result is 0)

    if ($result = $mysqli->query("SELECT @@autocommit")) {
        $row = $result->fetch_row();
        printf("BEFORE THE COMMIT Autocommit is %s<br />", $row[0]);
        $result->free();
    }

    var_dump($mysqli->commit());

if you get the expected result (meaning that before the commit the autocommit is indeed FALSE) then what left is that maybe you didn't provide us the same code that you are testing and that is making the difference in our testings, or that you found a weird bug that no one else here is able to replicate with the posted code