Transaction with FireDAC and MySQL

bludata · December 5, 2017, 4:38pm

Hi,

with the latest version of Delphi and DA+FireDAC+MySQL I have some iusses when adding or updating one record of a table at a time.

If I add record A, in table T I haven’t any new records.
If I add record B, in table T I find the record A.
If I add record C, in table T I find the record A,B but not C.

I think there is an iusse on the latest Firedac.

To fix it temporarily I made this changes:

unit uDAFireDACDriver;
....
function TDAEFireDACConnection.CreateCustomConnection: TCustomConnection;
begin
 ...
 FADConnection.TxOptions.AutoCommit:=false; // default is true
 ...
end;
...

unit DataAbstractService_Impl;
...
procedure TDataAbstractService.DoCommitTransaction(aConnection: IDAConnection);
begin
  if Assigned(aConnection) and // aConnection.InTransaction and <== Firedac bug ?!
    TriggerTransactionEvent(fOnUpdateDataCommitTransaction) then begin
    ...
  end;
end;
...

I debugged the code FireDAC-MySQL and with these changes the methods TFDPhysMySQLTransaction.InternalStartTransaction(…) and TFDPhysMySQLTransaction.InternalCommit(…) are called correctly.

(Delphi 10.2.1, DA: 9.3.105.1345 and Driver: FireDAC + MySql)

Pierantonio

EvgenyK · December 6, 2017, 8:51am

I’ve tested and seems it works as expected.
can you create a simple testcase for this issue, pls?

you can attach it here or send directly to support@

bludata · December 6, 2017, 1:03pm

Here is an example.

I used these table:

CREATE TABLE `profiliapp` (
	`Codice` INT(11) NOT NULL AUTO_INCREMENT,
	`Profilo` VARCHAR(15) NULL DEFAULT NULL,
	PRIMARY KEY (`Codice`),
	INDEX `profiloidx` (`Profilo`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

build the server and the client
run the server and the client
click on the test button
check the table with a mysql utils.

No record appears!

Pierantonio

TestFireDAC.zip (120.7 KB)

EvgenyK · December 8, 2017, 10:10am

it works as expected (Delphi 10.2.1 & DA: 9.3.105.1345):

in MySQL log I see

initial select in MySQL Workbench

171208 12:42:42	   10 Query	SELECT * FROM test.profiliapp
LIMIT 0, 1000
		   10 Prepare	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, INDEX_NAME AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
		   10 Execute	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, INDEX_NAME AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 'profiliapp' AND INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
		   10 Close stmt	
		   10 Prepare	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE,CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN   CASE    WHEN LOCATE('zerofill', COLUMN_TYPE) != 0 AND LOCATE('zerofill', DATA_TYPE) = 0 THEN CONCAT(UCASE(DATA_TYPE), ' UNSIGNED ZEROFILL')    ELSE CONCAT(UCASE(DATA_TYPE), ' UNSIGNED')  END     ELSE UCASE(DATA_TYPE)END AS TYPE_NAME,CASE WHEN LCASE(DATA_TYPE)='year' THEN SUBSTRING(COLUMN_TYPE, 6, 1) -- 'year('=5
WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, '' AS BUFFER_LENGTH,NUMERIC_SCALE AS DECIMAL_DIGITS,10 AS NUM_PREC_RADIX,CASE WHEN IS_NULLABLE='NO' THEN 0 ELSE CASE WHEN IS_NULLABLE='YES' THEN 1 ELSE 2 END END AS NULLABLE,COLUMN_COMMENT AS REMARKS,COLUMN_DEFAULT AS COLUMN_DEF,0 AS SQL_DATA_TYPE,0 AS SQL_DATETIME_SUB,CHARACTER_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE,NULL AS SCOPE_CATALOG,NULL AS SCOPE_SCHEMA,NULL AS SCOPE_TABLE,NULL AS SOURCE_DATA_TYPE,IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND COLUMN_NAME LIKE ? ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
		   10 Execute	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE,CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN   CASE    WHEN LOCATE('zerofill', COLUMN_TYPE) != 0 AND LOCATE('zerofill', DATA_TYPE) = 0 THEN CONCAT(UCASE(DATA_TYPE), ' UNSIGNED ZEROFILL')    ELSE CONCAT(UCASE(DATA_TYPE), ' UNSIGNED')  END     ELSE UCASE(DATA_TYPE)END AS TYPE_NAME,CASE WHEN LCASE(DATA_TYPE)='year' THEN SUBSTRING(COLUMN_TYPE, 6, 1) -- 'year('=5
WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, '' AS BUFFER_LENGTH,NUMERIC_SCALE AS DECIMAL_DIGITS,10 AS NUM_PREC_RADIX,CASE WHEN IS_NULLABLE='NO' THEN 0 ELSE CASE WHEN IS_NULLABLE='YES' THEN 1 ELSE 2 END END AS NULLABLE,COLUMN_COMMENT AS REMARKS,COLUMN_DEFAULT AS COLUMN_DEF,0 AS SQL_DATA_TYPE,0 AS SQL_DATETIME_SUB,CHARACTER_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE,NULL AS SCOPE_CATALOG,NULL AS SCOPE_SCHEMA,NULL AS SCOPE_TABLE,NULL AS SOURCE_DATA_TYPE,IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 'profiliapp' AND COLUMN_NAME LIKE 'Codice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
		   10 Close stmt

lines related to DA server

171208 12:42:47	   12 Connect	root@localhost on test
		   12 Query	SET SQL_AUTO_IS_NULL = 0
		   12 Query	SHOW VARIABLES LIKE 'lower_case_table_names'
		   12 Query	BEGIN
		   12 Prepare	SELECT
   `Codice`, `Profilo`
FROM
   profiliapp
		   12 Execute	SELECT
   `Codice`, `Profilo`
FROM
   profiliapp
		   12 Close stmt	
		   12 Query	COMMIT
171208 12:42:50	   12 Query	BEGIN
		   12 Prepare	INSERT INTO profiliapp (
`Profilo`)
 VALUES (?)
		   12 Prepare	SELECT 
`Codice`
 FROM profiliapp
 WHERE 
`Codice` = ?
		   12 Close stmt	
		   12 Prepare	INSERT INTO profiliapp (
`Profilo`)
 VALUES (?)
		   12 Execute	INSERT INTO profiliapp (
`Profilo`)
 VALUES ('test')
		   12 Prepare	SELECT LAST_INSERT_ID()
		   12 Execute	SELECT LAST_INSERT_ID()
		   12 Close stmt	
		   12 Execute	SELECT 
`Codice`
 FROM profiliapp
 WHERE 
`Codice` = 11
		   12 Close stmt	
		   12 Close stmt	
		   12 Query	COMMIT

select in MySQL Workbench after record was inserted by DAD server

171208 12:42:54	   10 Query	SELECT * FROM test.profiliapp
LIMIT 0, 1000
		   10 Prepare	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, INDEX_NAME AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
		   10 Execute	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, INDEX_NAME AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 'profiliapp' AND INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX
		   10 Close stmt	
		   10 Prepare	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE,CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN   CASE    WHEN LOCATE('zerofill', COLUMN_TYPE) != 0 AND LOCATE('zerofill', DATA_TYPE) = 0 THEN CONCAT(UCASE(DATA_TYPE), ' UNSIGNED ZEROFILL')    ELSE CONCAT(UCASE(DATA_TYPE), ' UNSIGNED')  END     ELSE UCASE(DATA_TYPE)END AS TYPE_NAME,CASE WHEN LCASE(DATA_TYPE)='year' THEN SUBSTRING(COLUMN_TYPE, 6, 1) -- 'year('=5
WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, '' AS BUFFER_LENGTH,NUMERIC_SCALE AS DECIMAL_DIGITS,10 AS NUM_PREC_RADIX,CASE WHEN IS_NULLABLE='NO' THEN 0 ELSE CASE WHEN IS_NULLABLE='YES' THEN 1 ELSE 2 END END AS NULLABLE,COLUMN_COMMENT AS REMARKS,COLUMN_DEFAULT AS COLUMN_DEF,0 AS SQL_DATA_TYPE,0 AS SQL_DATETIME_SUB,CHARACTER_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE,NULL AS SCOPE_CATALOG,NULL AS SCOPE_SCHEMA,NULL AS SCOPE_TABLE,NULL AS SOURCE_DATA_TYPE,IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND COLUMN_NAME LIKE ? ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
		   10 Execute	SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE,CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN   CASE    WHEN LOCATE('zerofill', COLUMN_TYPE) != 0 AND LOCATE('zerofill', DATA_TYPE) = 0 THEN CONCAT(UCASE(DATA_TYPE), ' UNSIGNED ZEROFILL')    ELSE CONCAT(UCASE(DATA_TYPE), ' UNSIGNED')  END     ELSE UCASE(DATA_TYPE)END AS TYPE_NAME,CASE WHEN LCASE(DATA_TYPE)='year' THEN SUBSTRING(COLUMN_TYPE, 6, 1) -- 'year('=5
WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, '' AS BUFFER_LENGTH,NUMERIC_SCALE AS DECIMAL_DIGITS,10 AS NUM_PREC_RADIX,CASE WHEN IS_NULLABLE='NO' THEN 0 ELSE CASE WHEN IS_NULLABLE='YES' THEN 1 ELSE 2 END END AS NULLABLE,COLUMN_COMMENT AS REMARKS,COLUMN_DEFAULT AS COLUMN_DEF,0 AS SQL_DATA_TYPE,0 AS SQL_DATETIME_SUB,CHARACTER_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE,NULL AS SCOPE_CATALOG,NULL AS SCOPE_SCHEMA,NULL AS SCOPE_TABLE,NULL AS SOURCE_DATA_TYPE,IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE 'test' AND TABLE_NAME LIKE 'profiliapp' AND COLUMN_NAME LIKE 'Codice' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
		   10 Close stmt	
171208 12:44:15	   12 Quit

Note: last line for DAserver is COMMIT

bludata · December 12, 2017, 9:21am

These is my log:

171212 10:04:38	    5 Connect	bludata_locale@192.168.5.37 as anonymous on test
		    5 Query	SET SQL_AUTO_IS_NULL = 0
		    5 Query	SHOW VARIABLES LIKE 'lower_case_table_names'
		    5 Query	BEGIN
		    5 Prepare	SELECT
   `Codice`, `Profilo`
FROM
   profiliapp
		    5 Execute	SELECT
   `Codice`, `Profilo`
FROM
   profiliapp
		    5 Close stmt	
171212 10:04:39	    5 Query	BEGIN
		    5 Prepare	INSERT INTO profiliapp (
`Profilo`)
 VALUES (?)
		    5 Prepare	SELECT 
`Codice`
 FROM profiliapp
 WHERE 
`Codice` = ?
		    5 Close stmt	
		    5 Prepare	INSERT INTO profiliapp (
`Profilo`)
 VALUES (?)
		    5 Execute	INSERT INTO profiliapp (
`Profilo`)
 VALUES ('test')
		    5 Prepare	SELECT LAST_INSERT_ID()
		    5 Execute	SELECT LAST_INSERT_ID()
		    5 Close stmt	
		    5 Execute	SELECT 
`Codice`
 FROM profiliapp
 WHERE 
`Codice` = 20
		    5 Close stmt	
		    5 Close stmt

There are no comit lines. It is not present even in the first select.
I do not understand, the version of Delphi and DA are the same.

EvgenyK · December 12, 2017, 9:25am

they have released Delphi 10.2.2.
let to install it and test things with this version.

bludata · December 13, 2017, 8:19am

I found the reason and the solution to the problem.

In Dephi Tokyo http://docwiki.embarcadero.com/RADStudio/Tokyo/en/What’s_New they changed the interface with MySql: “Improvements for a synchronization of a connection transaction state and the TFDConnection.InTransaction property. This property allows you to determine whether a connection transaction is currently in progress”.

I updated libmysql.dll renaming the latest version of libmariadb.dll. Everything works properly. If I use the latest versions of libmysql.dll, the server does not comit the record. What version of libmysql.dll are you using? Do you also use mariadb?

Thank you
Pierantonio

EvgenyK · December 13, 2017, 8:23am

my version of libmysql.dll is 5.7.19.0 and I’m using InnoDB engine