View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0019137FPCDatabase Componentspublic2011-04-10 13:082014-03-04 17:45
ReporterNelsonN 
Assigned ToLudo Brands 
PrioritynormalSeverityminorReproducibilityalways
StatusresolvedResolutionfixed 
PlatformOSOS Version
Product VersionProduct Build 
Target Version2.6.1Fixed in Version2.6.4 
Summary0019137: MySQL InnoDB Transactions
DescriptionI seem to be able to work with commit and rollback transactions with SQLite and Firebird 2.5 with the following:

    DataModule1.SQLTransaction1.StartTransaction;

    DataModule1.SQLTransaction1.Rollback;

    DataModule1.SQLTransaction1.Commit;

    DataModule1.SQLTransaction1.EndTransaction;

But not with MySQL's InnoDB tables. Only with:

    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;

I am using MySQL Server version: 5.1.41.


According to moderator, marcov, over at the Free Pascal/Lazarus forum:

http://forum.lazarus.freepascal.org/index.php/topic,12750.msg65937.html [^]

"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."
TagsNo tags attached.
FPCOldBugId0
Fixed in Revision23906
Attached Filesdiff file icon mysqlconn_trans.diff [^] (2,842 bytes) 2011-10-06 08:55 [Show Content]
diff file icon mysqlconn_trans2.diff [^] (2,969 bytes) 2011-10-11 16:10 [Show Content]
diff file icon mysqlconn_trans2a.diff [^] (1,862 bytes) 2012-10-02 14:32 [Show Content]

- Relationships
related to 0017712resolvedMichael Van Canneyt TPQConnection connection handling - in transaction 
related to 0018841resolvedLudo Brands TMySQL51Connection + TSQLTransaction Operation cannot be performed on an active transaction 

-  Notes
(0052615)
LacaK (reporter)
2011-10-06 09:00

This patch preserver backward compatibility, so by default works in autocommit mode. To escape from autocommit mode you must set either in tsqlconnection.params or tsqltransaction.params "autocommit=0"
I hope, that as starting point it is good enough ... fcl-db test passed
(0052637)
Marco van de Voort (manager)
2011-10-06 13:14

This patch has the same problem (if it is a problem) as the related postgres mantis item.

While firebird can do multiple transactions in one connection, postgres/mysql (with these patches) can't.
(0052733)
LacaK (reporter)
2011-10-07 14:41

Yes it seems be limitation of underlaying database, if there is no support for overlaping or named transactions.
But for most cases, where is 1connection-1transaction used it will be fine.
For other complicate scenarios users must open another connection with another transaction (until there will be implemented some other solution), does not ?
(0052825)
Marco van de Voort (manager)
2011-10-10 13:21

It is internally incompatible with the firebird driver which does support that.
(0052833)
Ludo Brands (developer)
2011-10-10 16:02

A few comments on the patch:

- COMMIT/ROLLBACK AND CHAIN is not available in mysql 4 (http://dev.mysql.com/doc/refman/4.1/en/commit.html [^])

- According to the manual "To disable autocommit mode for a single series of statements, use the START TRANSACTION statement.
With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state. " So autocommit=0 is not a condition to do transactions.

- the autocommit default can be overridden server side for normal users with the init_connect= config option. So testing Params to get the autocommit value isn't completely safe neither.
(0052851)
LacaK (reporter)
2011-10-11 08:12

Ad mysql4: yes you are right, my guess, was that there is no big group of users, that use mysql4 in this days, so because there is retained backward compatibility, they will not be affected (of course they will not be able use "new transactions"). We can {$IFDEF MYSQL5_UP} if we can fix it

Ad autocommit and start transaction: yes it was my intention after ending explicit transaction to return to previos autocommit state

Ad Params: It is easy option (way) how user can say, that "I want switch off default/implicit autocommit (transaction) mode and turn on explicit transactions controled at sqlDB level".
My intention was preserve current behavior (when no transaction support is implemented) and give possibility to turn it on. "autocommit=0" is only phrase in this POV.
(0052852)
Ludo Brands (developer)
2011-10-11 08:29

I think you misunderstood how autocommit and start transaction interact on the server. When autocommit is 1, transactions work. START TRANSACTION will disable temporary the autocommit on the server. The COMMIT/ROLLBACK or other implicit commits/rollbacks will reset autocommit to the setting before the transaction. No saving/restoring is required on the client and you should completely leave out the test on autocommit.
A simple test will comfirm: set or leave autocommit=1. START TRANSACTION; INSERT xx; ROLLBACK; will leave the table as is with a innodb table. Do a INSERT xx; ROLLBACK; and the table is modified.

When you leave out the test on autocommit, then the params test comments become irrelevant. It is a general comment on the danger of "guessing" server setting by looking at the parameters passed. Most defaults can be overridden server side. The only sure way is to query server variables.
(0052854)
Ludo Brands (developer)
2011-10-11 09:16

Regarding COMMIT/ROLLBACK AND CHAIN: in mysql4 this can be easily emulated.
COMMIT AND CHAIN -> COMMIT + START TRANSACTION
ROLLBACK AND CHAIN -> ROLLBACK + START TRANSACTION

I tested this with autocommit=1 and the COMMIT/ROLLBACK AND CHAIN extends the disabling of autocommit.
(0052858)
LacaK (reporter)
2011-10-11 11:00

No, I understand how autocommit and start transaction works :-)
To not walk into circle, can you post patch, which will reflect your comments ?
(0052867)
Ludo Brands (developer)
2011-10-11 16:10

mysqlconn_trans2.diff attached
(0052885)
LacaK (reporter)
2011-10-12 07:17

ok, may be, but this solution affect backward compatibility.
ATM TMySQLConnection does not implement transaction support, so users are not required use SQLTransaction.Commit to commit their updates.
Now users must update their applications and put where needed (may be after SQLQuery.ApplyUpdates or at least at ending their applications) also SQLTransaction.Commit (it does not depend if on server side is autocommit=0 or 1)
(0052891)
Ludo Brands (developer)
2011-10-12 09:50

Backward compatibility is affected only for users using InnoDB and autocommit on and not using transactions directly (ie. calling START TRANSACTION, COMMIT,... with execqsl). If using InnoDB and autocommit off, they already had to call directly COMMIT to save their data. So we are talking here about Mysql users that changed the default table type to InnoDB because of transaction support, use a TSQLTransaction component but are not using transactions and think they can get away with it eternally :)

This being said, the choice is between:
- implementing transactions correctly at the cost of affecting backward compatibility for a small group of users and cases.
- implement a halfhearted solution where autocommit init params are (ab)used to condition the sending of transaction commands to the server. That definitely will need some very good documentation.

I'll leave the choice to the deciders.
(0052896)
LacaK (reporter)
2011-10-12 12:01
edited on: 2012-10-02 14:31

Ok, I agree with Ludo's previous comment ;-)
So patch mysqlconn_trans2.diff is fine for me.
I updated this patch (mysqlconn_trans2a.diff) to apply without conflicts to current trunk.


- Issue History
Date Modified Username Field Change
2011-04-10 13:08 NelsonN New Issue
2011-10-05 15:17 Vincent Snijders Project Lazarus => FPC
2011-10-05 15:23 Jonas Maebe FPCOldBugId => 0
2011-10-05 15:23 Jonas Maebe Category Database => Database Components
2011-10-05 15:23 Jonas Maebe Product Version 0.9.30 =>
2011-10-06 08:55 LacaK File Added: mysqlconn_trans.diff
2011-10-06 09:00 LacaK Note Added: 0052615
2011-10-06 12:46 Marco van de Voort Relationship added related to 0018841
2011-10-06 13:13 Marco van de Voort Relationship added related to 0017712
2011-10-06 13:14 Marco van de Voort Note Added: 0052637
2011-10-07 14:41 LacaK Note Added: 0052733
2011-10-10 13:21 Marco van de Voort Note Added: 0052825
2011-10-10 16:02 Ludo Brands Note Added: 0052833
2011-10-11 08:12 LacaK Note Added: 0052851
2011-10-11 08:29 Ludo Brands Note Added: 0052852
2011-10-11 09:16 Ludo Brands Note Added: 0052854
2011-10-11 11:00 LacaK Note Added: 0052858
2011-10-11 16:10 Ludo Brands Note Added: 0052867
2011-10-11 16:10 Ludo Brands File Added: mysqlconn_trans2.diff
2011-10-12 07:17 LacaK Note Added: 0052885
2011-10-12 09:50 Ludo Brands Note Added: 0052891
2011-10-12 12:01 LacaK Note Added: 0052896
2012-04-25 22:43 Marco van de Voort Status new => assigned
2012-04-25 22:43 Marco van de Voort Assigned To => Joost van der Sluis
2012-10-02 14:31 LacaK Note Edited: 0052896
2012-10-02 14:32 LacaK File Added: mysqlconn_trans2a.diff
2012-10-10 09:46 Ludo Brands Assigned To Joost van der Sluis => Ludo Brands
2012-10-10 09:47 Ludo Brands Fixed in Revision => 22598
2012-10-10 09:47 Ludo Brands Status assigned => resolved
2012-10-10 09:47 Ludo Brands Fixed in Version => 2.7.1
2012-10-10 09:47 Ludo Brands Resolution open => fixed
2012-10-10 09:47 Ludo Brands Target Version => 2.6.1
2014-03-04 17:45 Jonas Maebe Fixed in Revision 22598 => 23906
2014-03-04 17:45 Jonas Maebe Fixed in Version 2.7.1 => 2.6.4



MantisBT 1.2.12[^]
Copyright © 2000 - 2012 MantisBT Group
Powered by Mantis Bugtracker