Qt transactions rollback fails on MySQL

Question

I am trying to use transactions within a Qt connection but I am unable to understand how they work properly. My database is a MySQL instance and tables use the InnoDB engine so transactions are supported.

I wrote a little test where within a transaction I delete a record on a table and eventually create a table that already exists. When the create script fails I try to handle it by rolling back.

What I'd expect is that, rolling back, the record is not deleted. However, what I got is that as soon the rollback function is invoked the record is deleted.

#include <QApplication>
#include <QtSql>
#include <QtDebug>

int main( int argc, char **argv )
{
  QApplication app( argc, argv );

  QSqlDatabase db = QSqlDatabase::addDatabase( "QMYSQL" );

  db.setHostName( QString("XXXXX")) ;
  db.setDatabaseName( "db_test" );
  db.setUserName( "X" );
  db.setPassword( "X" );
  QSqlDatabase::database().transaction();
  QSqlQuery q;
  if( !db.open() )
  {
    qDebug() << db.lastError();
    qFatal( "Failed to connect." );
  }
  qDebug( "Connected!" );

  q.prepare("DELETE FROM vendita WHERE matricola = :m  and idOrdine = 530 and idStab = 1");
  q.bindValue(":m","0032110275928");

   if( !q.exec() ){
       qDebug("error");
       return 0;
   }

   //this fails, the table already exists
  q.prepare( "CREATE TABLE test (id INTEGER UNIQUE PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR(30))" );
  if( !q.exec() )
  {
      qDebug() << q.lastError();
      bool res =  QSqlDatabase::database().rollback();
      qDebug() << res;
      return 0;
  }
  else
  {
      qDebug() << "Table created!";
      QSqlDatabase::database().commit();
  }

  db.close();
  return 0;
}

Answer 1

but what I got is that, as soon the rollback function is invoked, the record is deleted.

CREATE TABLE statement execution causes implicit commit for your first query (the one that you used to delete record). Hence, your record is deleted whether CREATE TABLE failed or not.

From https://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html,

The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement.

Also, note that you can't roll back CREATE TABLE anyways since it's one of the Data definition language (DDL) statements that define or modify database objects