QSqlQuery execution resets transaction for no reason

[Seb Tur]

I have a method that checks

//transaction checker
bool common::is_in_transaction()
{
    int in_transaction = 0;
    QSqlQuery query;

    query.prepare("SELECT @@in_transaction");
    query.exec();

    if (query.next())
    {
        in_transaction = query.value(0).toInt();
        qDebug() << "query in transaction: " << query.value(0).toInt();
    }

    return(in_transaction);
}


//sql runner
bool common::sql_exec(QSqlQuery *query, QString query_description, bool force_debug, bool ok_to_commit)
{
    qDebug()<<"before sql exec in trans:"<<is_in_transaction();

    bool res=true;
    if (!db->isOpen())
    {
        qDebug()<<"Connection error @"<<query_description;
        return(false);
    }

    if (ok_to_commit)
        res = query->exec();
    qDebug()<<"after sql exec in trans:"<<is_in_transaction();
}

//later in my maincode I call

QSqlQuery query;
query.prepare(query_string);
common->sql_exec(&query, "description",true, true);

For some reason I cannot figure out after query->exec() the in_transaction gets reset to 0

query in transaction: 1
before sql exec in trans: true
query in transaction: 0
after sql exec in trans: false

I never do any commits or rollbacks in my query_string, it is just a table update statement.

[Seb Tur]

@Seb-Tur

I have solved my own problem
the fact is that the error happened when query_string contained TRUNCATE table statement

and TRUNCATE calls commit automatically which I did not know.

https://stackoverflow.com/questions/5972364/mysql-truncate-table-within-transaction

[JonB]

@Seb-Tur
This is your whole code? The very first time you call sql_exec() is returns before sql exec in trans: true, but you have never done anything to the database?
[Written before your latest post.]

[Seb Tur]

@JonB

I wanted to be clever and replace TRUNCATE with DELETE FROM table + ALTER TABLE auto_increment =1;

but alter table has the same effect -it smuggles a COMMIT under the table...
So I guess clearing the indexes is a maintenance task that needs to be taken care outside of transaction

[JonB]

@Seb-Tur
TRUNCATE commits any existing transaction. DELETE FROM TABLE does not. I would expect ALTER TABLE auto_increment =1 to do same if it is a statement that alters a table's auto-increment column in your SQL. Any DDL-change-type statement is likely to do so.