0019137: MySQL InnoDB Transactions - Free Pascal/Lazarus Bug Tracker

View Issue Details [ Jump to Notes ]

[ Issue History ] [ Print ]

Project

Tags

No tags attached.

FPCOldBugId

Fixed in Revision

22598

Attached Files

mysqlconn_trans.diff [^] (2,842 bytes) 2011-10-06 08:55 [Show Content] [Hide Content]

--- mysqlconn.inc.ori	Thu Oct 06 07:43:54 2011
+++ mysqlconn.inc	Thu Oct 06 08:51:22 2011
@@ -309,7 +309,7 @@ begin
     // Only available for mysql 5.0.7 and later...
     // if (mysql_set_character_set(HMySQL, PChar(CharSet)) <> 0) then
     if mysql_query(FMySQL,PChar('SET CHARACTER SET ''' + EscapeString(CharSet) +''''))<>0 then
-      MySQLError(HMySQL,Format(SErrExecuting,[StrPas(mysql_error(HMySQL))]),Self);
+      MySQLError(HMySQL, SErrExecuting, Self);
     end;
 end;
 
@@ -387,7 +387,7 @@ begin
     ConnectMySQL(AMySQL,pchar(H),pchar(U),pchar(P));
     try
       if mysql_query(AMySQL,pchar(query))<>0 then
-        MySQLError(AMySQL,Format(SErrExecuting,[StrPas(mysql_error(AMySQL))]),Self);
+        MySQLError(AMySQL, SErrExecuting, Self);
     finally
       mysql_close(AMySQL);
     end;
@@ -531,7 +531,7 @@ begin
       C.FStatement := stringsreplace(C.FStatement,ParamNames,ParamValues,[rfReplaceAll]);
       end;
     if mysql_query(FMySQL,Pchar(C.FStatement))<>0 then
-      MySQLError(FMYSQL,Format(SErrExecuting,[StrPas(mysql_error(FMySQL))]),Self)
+      MySQLError(FMYSQL, SErrExecuting, Self)
     else
       begin
       C.RowsAffected := mysql_affected_rows(FMYSQL);
@@ -1041,27 +1041,41 @@ end;
 
 function TConnectionName.Commit(trans: TSQLHandle): boolean;
 begin
-  // Do nothing.
+  //mysql_commit(FMySQL);
+  Result := mysql_query(FMySQL, 'COMMIT') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 function TConnectionName.RollBack(trans: TSQLHandle): boolean;
 begin
-  // Do nothing
+  //mysql_rollback(FMySQL);
+  Result := mysql_query(FMySQL, 'ROLLBACK') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 function TConnectionName.StartdbTransaction(trans: TSQLHandle; AParams : string): boolean;
 begin
-  // Do nothing
+  // By default, MySQL runs with autocommit mode enabled
+  Result := (pos('autocommit=0', AParams) > 0) or (Params.Values['autocommit'] = '0');
+  // if Result then mysql_autocommit(FMySQL, 0) else mysql_autocommit(FMySQL, 1);
+  if Result then
+    // To disable autocommit mode for a single series of statements, use the START TRANSACTION statement
+    if mysql_query(FMySQL, 'START TRANSACTION') <> 0 then
+      MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 procedure TConnectionName.CommitRetaining(trans: TSQLHandle);
 begin
-  // Do nothing
+  if mysql_query(FMySQL, 'COMMIT AND CHAIN') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 procedure TConnectionName.RollBackRetaining(trans: TSQLHandle);
 begin
-  // Do nothing
+  if mysql_query(FMySQL, 'ROLLBACK AND CHAIN') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 function TConnectionName.GetSchemaInfoSQL(SchemaType: TSchemaType;

mysqlconn_trans2.diff [^] (2,969 bytes) 2011-10-11 16:10 [Show Content] [Hide Content]

Index: mysqlconn.inc
===================================================================
--- mysqlconn.inc	(revision 19465)
+++ mysqlconn.inc	(working copy)
@@ -309,7 +309,7 @@
     // Only available for mysql 5.0.7 and later...
     // if (mysql_set_character_set(HMySQL, PChar(CharSet)) <> 0) then
     if mysql_query(FMySQL,PChar('SET CHARACTER SET ''' + EscapeString(CharSet) +''''))<>0 then
-      MySQLError(HMySQL,Format(SErrExecuting,[StrPas(mysql_error(HMySQL))]),Self);
+      MySQLError(HMySQL, SErrExecuting, Self);
     end;
 end;
 
@@ -387,7 +387,7 @@
     ConnectMySQL(AMySQL,pchar(H),pchar(U),pchar(P));
     try
       if mysql_query(AMySQL,pchar(query))<>0 then
-        MySQLError(AMySQL,Format(SErrExecuting,[StrPas(mysql_error(AMySQL))]),Self);
+        MySQLError(AMySQL, SErrExecuting, Self);
     finally
       mysql_close(AMySQL);
     end;
@@ -531,7 +531,7 @@
       C.FStatement := stringsreplace(C.FStatement,ParamNames,ParamValues,[rfReplaceAll]);
       end;
     if mysql_query(FMySQL,Pchar(C.FStatement))<>0 then
-      MySQLError(FMYSQL,Format(SErrExecuting,[StrPas(mysql_error(FMySQL))]),Self)
+      MySQLError(FMYSQL, SErrExecuting, Self)
     else
       begin
       C.RowsAffected := mysql_affected_rows(FMYSQL);
@@ -1041,27 +1041,51 @@
 
 function TConnectionName.Commit(trans: TSQLHandle): boolean;
 begin
-  // Do nothing.
+  //mysql_commit(FMySQL);
+  Result := mysql_query(FMySQL, 'COMMIT') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 function TConnectionName.RollBack(trans: TSQLHandle): boolean;
 begin
-  // Do nothing
+  //mysql_rollback(FMySQL);
+  Result := mysql_query(FMySQL, 'ROLLBACK') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 function TConnectionName.StartdbTransaction(trans: TSQLHandle; AParams : string): boolean;
 begin
-  // Do nothing
+  Result := mysql_query(FMySQL, 'START TRANSACTION') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 procedure TConnectionName.CommitRetaining(trans: TSQLHandle);
 begin
-  // Do nothing
+  {$ifdef MYSQL50_UP}
+  if mysql_query(FMySQL, 'COMMIT AND CHAIN') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$else}
+  if mysql_query(FMySQL, 'COMMIT') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  if mysql_query(FMySQL, 'START TRANSACTION') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$endif}
 end;
 
 procedure TConnectionName.RollBackRetaining(trans: TSQLHandle);
 begin
-  // Do nothing
+  {$ifdef MYSQL50_UP}
+  if mysql_query(FMySQL, 'ROLLBACK AND CHAIN') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$else}
+  if mysql_query(FMySQL, 'ROLLBACK') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  if mysql_query(FMySQL, 'START TRANSACTION') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$endif}
 end;
 
 function TConnectionName.GetSchemaInfoSQL(SchemaType: TSchemaType;

mysqlconn_trans2a.diff [^] (1,862 bytes) 2012-10-02 14:32 [Show Content] [Hide Content]

--- mysqlconn.inc.ori	Tue Oct 02 14:08:30 2012
+++ mysqlconn.inc	Tue Oct 02 14:24:58 2012
@@ -1111,27 +1111,51 @@ end;
 
 function TConnectionName.Commit(trans: TSQLHandle): boolean;
 begin
-  // Do nothing.
+  //mysql_commit(FMySQL);
+  Result := mysql_query(FMySQL, 'COMMIT') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 function TConnectionName.RollBack(trans: TSQLHandle): boolean;
 begin
-  // Do nothing
+  //mysql_rollback(FMySQL);
+  Result := mysql_query(FMySQL, 'ROLLBACK') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 function TConnectionName.StartdbTransaction(trans: TSQLHandle; AParams : string): boolean;
 begin
-  // Do nothing
+  Result := mysql_query(FMySQL, 'START TRANSACTION') = 0;
+  if not Result then
+    MySQLError(FMySQL, SErrExecuting, Self);
 end;
 
 procedure TConnectionName.CommitRetaining(trans: TSQLHandle);
 begin
-  // Do nothing
+  {$ifdef MYSQL50_UP}
+  if mysql_query(FMySQL, 'COMMIT AND CHAIN') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$else}
+  if mysql_query(FMySQL, 'COMMIT') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  if mysql_query(FMySQL, 'START TRANSACTION') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$endif}
 end;
 
 procedure TConnectionName.RollBackRetaining(trans: TSQLHandle);
 begin
-  // Do nothing
+  {$ifdef MYSQL50_UP}
+  if mysql_query(FMySQL, 'ROLLBACK AND CHAIN') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$else}
+  if mysql_query(FMySQL, 'ROLLBACK') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  if mysql_query(FMySQL, 'START TRANSACTION') <> 0 then
+    MySQLError(FMySQL, SErrExecuting, Self);
+  {$endif}
 end;
 
 function TConnectionName.GetSchemaInfoSQL(SchemaType: TSchemaType;

Relationships

related to	0017712	feedback	Joost van der Sluis	TPQConnection connection handling - in transaction
related to	0018841	resolved	Ludo Brands	TMySQL51Connection + TSQLTransaction Operation cannot be performed on an active transaction

Notes
(0052615) LacaK2 (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) LacaK2 (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) LacaK2 (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) LacaK2 (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) LacaK2 (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) LacaK2 (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	LacaK2	File Added: mysqlconn_trans.diff
2011-10-06 09:00	LacaK2	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	LacaK2	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	LacaK2	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	LacaK2	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	LacaK2	Note Added: 0052885
2011-10-12 09:50	Ludo Brands	Note Added: 0052891
2011-10-12 12:01	LacaK2	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	LacaK2	Note Edited: 0052896
2012-10-02 14:32	LacaK2	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

freepascal bugtracker

Open source compiler for Pascal and Object Pascal