MySQL InnoDB Transactions
lazarus / 16-Databases

----------------------------------------------------------------------
NelsonN · 2011-04-09 02:15

I seem to have bumped into a corner with trying to understand MySQL's
InnoDB tables and SQLdb.

I seem to be able to work with commit and rollback transactions with
SQLite and Firebird 2.5 with the following:

Quote    DataModule1.SQLTransaction1.StartTransaction;

    DataModule1.SQLTransaction1.Rollback;

    DataModule1.SQLTransaction1.Commit;

    DataModule1.SQLTransaction1.EndTransaction;
But not with MySQL's InnoDB tables. Only with:

Quote    DataModule1.SQLQuery1.SQL.Text := 'Start Transaction';
    DataModule1.SQLQuery1.ExecSQL;

    DataModule1.SQLQuery1.SQL.Text := 'RollBack';
    DataModule1.SQLQuery1.ExecSQL;

    DataModule1.SQLQuery1.SQL.Text := 'Commit';
    DataModule1.SQLQuery1.ExecSQL;
Also, I have set init_connect='SET autocommit=0' in my.ini. So
autocommits are not active by default.

Can anyone with experienced with SQLdb tell me if I might be
overlooking something or if it is not possible to use SQLdb with
MySQL's InnoDB tables?

----------------------------------------------------------------------
marcov · 2011-04-10 10:31

Quote from: NelsonN on April 09, 2011, 04:15:57 amI seem to have
bumped into a corner with trying to understand MySQL's InnoDB tables
and SQLdb.

I seem to be able to work with commit and rollback transactions with
SQLite and Firebird 2.5 with the following:

Quote    DataModule1.SQLTransaction1.StartTransaction;

    DataModule1.SQLTransaction1.Rollback;

    DataModule1.SQLTransaction1.Commit;

    DataModule1.SQLTransaction1.EndTransaction;
But not with MySQL's InnoDB tables. Only with:

Quote    DataModule1.SQLQuery1.SQL.Text := 'Start Transaction';
    DataModule1.SQLQuery1.ExecSQL;

    DataModule1.SQLQuery1.SQL.Text := 'RollBack';
    DataModule1.SQLQuery1.ExecSQL;

    DataModule1.SQLQuery1.SQL.Text := 'Commit';
    DataModule1.SQLQuery1.ExecSQL;
Also, I have set init_connect='SET autocommit=0' in my.ini. So
autocommits are not active by default.

Can anyone with experienced with SQLdb tell me if I might be
overlooking something or if it is not possible to use SQLdb with
MySQL's InnoDB tables?


Seems to be correct, the overrides are empty. This could be because
older versions of mysql didn't support transactions.

Please file a bug so it is not forgotten.

----------------------------------------------------------------------
NelsonN · 2011-04-10 11:11

I filed the report and gave the MySQL version I am using: MySQL Server
version: 5.1.41.

Thanks.