Problems with MYSQL 5.7 prepared queries

[ga301]

Hi,

I try to use zeos (current stable version 7.2.4) in Delphi 10.2.3 with MySQL 5.7 community and the C Connector libmysql.dll version 6.01.11.
I have been working on my project for several weeks now and it (worked more or less), until I found that the prepare was only emulated.

The problem that prompted me to look closer at the inner workings of TZQuery had to do with a field default
ts_create TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3).

When using the emulated prepared query to insert a new row into the table, the processor inserted 'CURRENT_TIMESTAMP(3)' into the INSERT SQL, even though I had set the field to NULL.
I found the TZQuery.Option doPreferPrepared and that was when nothing worked anymore.

First, I found that in TZMySQLBaseDriver.GetBindOffsets, the current version of the libmysql.dll is not supported. The code maps 60000..60099, so 60111 is not handled. In a previous posting hier in the forum, I saw, that people are actually using 6.1.11 or 6.1.6. How can this be?
Are they relying on the emulated parameter binding instead of the native one?

So I checked the definition of MYSQL_BIND in the 6.1.11 source of the connector and saw no difference. I therefore increased the version range from 60099 to 60111.

But now I run into more serious problems with Access violations. It seems that buffers are not allocated correctly or too small. The problems occur immediately when try to insert a new row into a table. I use 16byte blob fields (for GUIDs). As far as I could see, just binding such a GUID value as parameter in a select * from xxxx where id=? causes problems.

What am I doing wrong?
I really hope somebody here can help. Either by fixing the generation of the plain SQL or, better, by making native binding work.

Thanks and best regards,
Gunnar

[marsupilami]

Hello Gunnar,

Egonhugeist told me that prepared statements in MySQL have some serious limitations, which is why we don't use them by default. So I suggest, you use the emulated prepared statement.

Regarding the handling of null values: There might be an issue that the statement has no chance to detect wether a field was set to null intentionally or if it was just not edited and needs replacement with the default value.

Best regards,

Jan

[marsupilami]

Hello Gunnar,

I had a phone call with Egonhugeist. For Zeos 7.2 you should not use the prepared statement. He will try to fix the problems in Zeos 7.3, so it will become available there. Please try the following:

• use the emulated prepared statement again
• In TZQuery -> Options -> doCalcDefaults

Best regards,

Jan

[ga301]

Jan,

thank you very much for your answer. I already switched back to the emulated prepared statements, but will yet have to try the doCalcDefaults option.
I would like to take advantage of MySQLs automatic update of timestamps (using both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP).
In TZMySQLDatabaseMetadata.UncachedGetColumns() I found that a default CURRENT_TIMESTAMP is already exempted from being converted to a string value. The problem is, that I would like to have higher precision on the timestamp fields, so this logic would have to recognize also the CURRENT_TIMESTAMP(n) syntax.
I only want the modification time of a record to be changed, only if anything in the record (other than the timestamp fields) actually changes. Therefore I cannot include these timestamp columns in an UPDATE and I have now switched my code to using TZUpdateSQL components instead of the automatic INSERT and UPDATE code generated by TZQuery.
Which brings me to the next problem:
In order to determine the columns in the table which I need to excempt from an UPDATE or INSERT and to generate the required SQL dynamically, I would like to use the TZSQLMetadata component. When I try to do that, it works only once for every table, on the reuse of the TZSQLMetadata component for the same table, the result set will be empty (recordcount = 0, EOF, BOF = true). I tried disabling the caching of metadata information and it worked then, but it makes the application too slow. I think that the actual rows in the Metadata result set are freed for some reason and am trying to debug that.

I first tried with a single TZSQLMetadata component on a datamodule and then also with multiple TZSQLMetadata components created temporarily. I had the same problem with both. Here is an excerpt from my code. The idea is to prepare TZQuery (TDBQuery is declared type TDBQuery = class(TZQuery) with an FUpdateSQL member variable) objects for every select statement required by the application. The select statements are specified in a constant array of records (maybe they will later be read from a configuration file). All of those query components are stored in a Hashtable. If a select needs to have corresponding UPDATE and INSERT statements, I specify the names of the affected tables which allows me to construct TZUpdateSQL components for those queries. Other queries are also used readonly and will not create TZUpdateSQL.

Code: Select all

procedure TTPDataModule.DataModuleCreate(Sender: TObject);
var
  i, j : integer;
  query : TDBQuery;

  function GetModifySQL(tablenames : array of string; keyfields : array of string) : string;
  var
    name : string;
    fielddef : TFieldDef;
    fc : integer;
    sql : string;
    i : integer;

    dbquery : TDBQuery;
    tablename : string;
    fieldnames : TStringList;
    dbmetadata : TZSQLMetadata;
  begin
    sql := '';
    for tablename in tablenames do begin
      dbmetadata := TZSQLMetadata.Create(nil);
      dbmetadata.Connection := DBCOnnection;
      DBMetadata.MetadataType := mdColumns;
      DBMetadata.TableName := tablename;
      sql := sql +  'UPDATE '+tablename+' SET ';
      DBMetadata.Active := true;
      DBMetadata.first;
      fieldnames := TStringList.Create;
      while not DBMetadata.Eof do begin
        fieldnames.Add(DBMetadata.FieldByName('COLUMN_NAME').AsString);
        DBMetadata.Next;
      end;
      DBMetadata.Active := false;
      fc := 0;
      for name in fieldnames do begin
        if Name.startsWith('ts_') then continue;
          // exclude timestamp fields from update
        if fc > 0 then begin
          sql := sql + ', ';
        end;
        sql := sql + name +'='+':'+name;
        Inc(fc);
      end;
      for i := low(keyfields) to high(keyfields) do begin
        if (i=low(keyfields)) then begin
          sql := sql + ' WHERE ';
        end else begin
          sql := sql + ' AND ';
        end;
        sql := sql + '('+keyfields[i]+'='+':'+keyfields[i]+')';
      end;
      sql := sql + ';'#$0D#$0A;
      fieldnames.Free;
      dbmetadata.free;
    end;
    result := sql;
  end;


  function GetInsertSQL(tablenames : array of string) : string;
  var
    name : string;
    fielddef : TFieldDef;
    fc : integer;
    sql : string;
    i : integer;

    dbquery : TDBQuery;
    tablename : string;
    fieldnames : TStringList;
    dbmetadata : TZSQLMetadata;
  begin
    sql := '';
    for tablename in tablenames do begin
      dbmetadata := TZSQLMetadata.Create(nil);
      dbmetadata.Connection := DBConnection;
      DBMetadata.MetadataType := mdColumns;
      DBMetadata.TableName := tablename;
      DBMetadata.Open;
      fieldnames := TStringList.Create;
      while not DBMetadata.Eof do begin
        fieldnames.Add(DBMetadata.FieldByName('COLUMN_NAME').AsString);
        DBMetadata.Next;
      end;
      DBMetadata.Close;
      sql := sql +  'INSERT INTO '+tablename+' (';
      fc := 0;
      for name in fieldnames do begin
        if Name.startsWith('ts_') then continue;
          // exclude timestamp fields from insert
        if fc > 0 then begin
          sql := sql + ', ';
        end;
        sql := sql + name;
        Inc(fc);
      end;
      sql := sql + ') VALUES(';
      fc := 0;
      for name in fieldnames do begin
        if Name.startsWith('ts_') then continue;
          // exclude timestamp fields from insert
        if fc > 0 then begin
          sql := sql + ', ';
        end;
        sql := sql + ':'+name;
        Inc(fc);
      end;
      sql := sql + ');'#$0D#$0A;
      fieldnames.free;
      dbmetadata.free;
    end;
    result := sql;
  end;

  function GetDeleteSQL(tablenames : array of string; keyfields : array of string) : string;
  var
    sql : string;
    tablename : string;
    i : integer;
  begin
    sql := '';
    for tablename in tablenames do begin
      sql := sql + 'DELETE FROM '+tablename;
      for i := low(keyfields) to high(keyfields) do begin
        if (i=low(keyfields)) then begin
          sql := sql + ' WHERE ';
        end else begin
          sql := sql + ' AND ';
        end;
        sql := sql + '('+keyfields[i]+'='+':'+keyfields[i]+')';
      end;
    end;
    result := sql;
  end;


begin
  DBConnection.Connected := true;
  FTableQuerys := THashTable<string,TDBQuery>.Create;
  FSelectStrings := THashTable<string,TQuerySQL>.Create;
  with FSelectStrings do begin
    for i := low(table_selects) to high(table_selects) do begin
      FSelectStrings.put(table_selects[i].FQueryID, table_selects[i], TQuerySQL.Empty);
      query := TDBQuery.Create(self);
      query.Connection := DBConnection;
      query.RequestLive := true;
      query.SQL.Text := table_selects[i].FSQLText; // the SELECT statement for this query (usually requests all columns and uses names params in the WHERE part)
      if Length(table_selects[i].FTableNames) > 0 then begin // The list of tables which need to be updated
        query.FUpdateSQL := TZUpdateSQL.Create(self);
        with query.FUpdateSQL do begin
          DeleteSQL.Text := GetDeleteSQL(table_selects[i].FTableNames, table_selects[i].FParamNames);
          ModifySQL.Text := GetModifySQL(table_selects[i].FTableNames, table_selects[i].FParamNames);
          InsertSQL.Text := GetInsertSQL(table_selects[i].FTableNames);
        end;
        query.UpdateObject := query.FUpdateSQL; 
      end;
      for j := 0 to query.Params.Count-1 do begin // I use mostly GUIDs for the keys
        query.Params.Items[j].DataType := ftGuid;
        query.Params.Items[j].Size := 16;
      end;
      for j := 0 to length(table_selects[i].FParamTypes)-1 do begin 
        if j < query.Params.count then begin
          if query.Params.Items[j].DataType <> ftGUID then begin // handle the exceptions
            query.Params.Items[j].DataType := table_selects[i].FParamTypes[j];
          end;
        end;
      end;
      query.Prepare;
      FTableQuerys.put(table_selects[i].FQueryID, query, nil);
    end;
  end;

What happens is, that for every first usage of a tablename, GetModifySQL will give the correct result while GetInsertSQL will find no column names and generate an "empty" INSERT statement. On further execution for the same tablename, GetModifySQL will also contain an "empty" UPDATE statement. While debugging the rest of my application, it sometimes seemed, that SELECt result sets were suddenly reduced to empty and also a Refresh on the TZQuery did not change that. These effects could still be due to errors in my usage of the components, but maybe they are related to the same problem the TZSQLMetadata resultsets have?
I do not understand, what I am doing wrong here. I do not like the idea of hard-coding the database model into the application by using explicit TZQuery or TZUpdateSQL components.
Can it be related to some changes in the Delphi 10.2 implementation of TDataset? Is MySQL 5.7 not supported?

Do you have any ideas?

Thanks and best regards,
Gunnar

[marsupilami]

Hello Gunnar,

MySQL 5.7 should be supported. I see several issues here:

• trying to use doCalcDefaults in the hopes that it solves your problem
• an error when it comes to caching metadata - at least with MySQL
• a feature request to extend the recognition of current_timestamp into current_timestamp(x)

Which option did you use to disable the caching of metadata?

Could you please try to see what happens if you disable the option doCalcDefaults? I hope this helps your issue and I can get around debugging the MySQL driver

I suggest to create a bug report on the metadata caching issue in the Sourceforge Bug Tracker. I cannot do that myself because I currently don't have a working installation of MySQL. Please try to create a sample project that shows the bug. This helps us tremendously.

The same goes for the feature request. Here no sample project is needed.

Best regards,

Jan

[ga301]

Hello Jan,
thank you very much for your response and your suggestions. I will try those.
To answer your question: I deactivated the caching the hard way, i.e. by commenting out the line which inserts the resultset into the hashmap. I just wanted to see if it might be related to the cache...

I have just created a very simple test project and will attach it to the bug I am going to report sometime today (probably).
The behaviour is very interesting:
I have a form with a DBGrid, TDatasource, TZSQLMetadata, TZConnection. The connection is established in FormCreate.
I have an input field where I can type the name of a table in the database, and a button, with the following code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZSQLMetadata1.Active := false;
  ZSQLMetadata1.tablename := LabeledEdit1.Text;
  ZSQLMetadata1.Active := true;
end;

The grid is empty to start with (the TZSQLMetadata is not active yet).
I type in the name of a table and press the button. The grid will be filled.
I press the button again: The grid still will be filled
On the third(!!!) press of the button, the grid will revert to 0 rows.
This can be reproduced for any tablename. After a tablename has been used in that way the grid will always be empty.

It does also not depend on the protocol I use. I see the same problem with the ODBC connector to MySQL via ADO.
Still have to try a different database (e.g. SQLite, or MSSQL), though.

I will also try to track down the error myself and will post my findings.

Best regards,
Gunnar

[marsupilami]

Hello Gunnar,

it seems that Egonhugeist has fixed the metadata bug already in the SVN. So you don't need to create a bug report. Thank you for your efforts
Best regards,

Jan

[ga301]

Hi Jan,
I got the current version from SVN and tried my test project.
I do not think that the metadata bug has been fixed. Actually, the problem has gotten worse, because now, only the first open on the TZSQLMetadata for any one table will return results. If I close it and open it again, the grid will only contain one (empty) row.
I really do not understand this. Maybe it is connected to something Embarcadero did in the newer Delphis?

Repeating the same select in a query seems to work, though.

I will prepare the source code of the test project and upload it.

Best regards,
Gunnar

[ga301]

Hi,

I am attaching a quick&dirty test project which demonstrates the problem I have with TZSQLMetadata.

The form consists of an input field, a memo (for result output) and an edit field for input of a table name.
A single TZSQLMetadata object and TZConnection object is used. You will have to adjust the database name in the connection component as well as the protocol. I see the problem both with mysql-5 and Ado/ODBC, but have not yet tried with other databases or protocols.

When you press the button, a list of the columns in the entered table should be output. This works only once for every table name. On the next attempts the result will be empty. Please refer to the example screenshot.

I am still trying to find and, if possible, fix the error.

Best regards
Gunnar

[EgonHugeist]

Hi Gunnar,
didn't i resolve this already?