External Zeos MySQL tables with underscore ( _ ) cannot be registered.

turkerali · 2021-04-04 13:12:15

Hi,

Any "VirtualTableExternalRegister" call to an external MySQL table with an underscore ( _ ) sign produces an exception as follows:

CODE:

VirtualTableExternalRegister(aetDBSchema, TSQLDriveRecord, fConnection, 'test_table');

RESULT:

ESQLite3Exception: Error SQLITE_ERROR (1) [Step] using 3.35.3 - SQL Error: Table 'testdb.test\_table' doesn't exist
Code: 1146 SQL: SHOW INDEX FROM testdb.`test\_table`, extended_errcode=1
  $0061C92F
  $0061C3B8
  $0061B760
  $0061B7CB
  $0061945B
  $004CC04E
  $004027AD

Any ideas on how to workaround this problem? Thanks in advance.

turkerali · 2021-04-10 17:36:22

Dear forum members and ab,

As I mentioned above, Mormot fails to map an external MySQL table with an underscore character such as "user_defs".
I am stuck with this bug, and I couldn't find any workaround so far. I can not rename the table because this table is already in use.

I feel like this bug is more related to sqlite engine than Mormot, but I am not sure.
Here is the code, execution path, and the exception generated after calling "CreateMissingTables":

Code:

aetDBSchema := TSQLModel.Create([TSQLUserDefs], 'root');
VirtualTableExternalMap(aetDBSchema,TSQLUserDefs,fConnection,'mysqltest.user_defs');
aetRestServerDB := TSQLRestServerDB.Create(aetDBSchema, ':memory:', false);
aetRestServerDB.CreateMissingTables();

Execution Path

1 - TSQLRestServerDB.CreateMissingTables(user_version: cardinal=0; Options: TSQLInitializeTableOptions=[]);
2 - TSQLDataBase.Execute(const aSQL: RawUTF8);
3 - TSQLRequest.Execute(aDB: TSQLite3DB; const aSQL: RawUTF8);
4 - TSQLRequest.Prepare(DB: TSQLite3DB; const SQL: RawUTF8; NoExcept: boolean): integer;
5 - TSQLRequest.Execute;
6 - TSQLRequest.Step: integer;
7 - sqlite3_check(DB: TSQLite3DB; aResult: integer; const SQL: RawUTF8): integer; ----> Creates Exception

Exception and debug log:

SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
Step
BEGIN TRANSACTION;
Step
CREATE VIRTUAL TABLE UserDefs USING External(USERNAME TEXT COLLATE SYSTEMNOCASE, USERPASS TEXT COLLATE SYSTEMNOCASE, REAL_NAME TEXT COLLATE SYSTEMNOCASE, REAL_SURNAME TEXT COLLATE SYSTEMNOCASE, REAL_EMAIL TEXT COLLATE SYSTEMNOCASE, USER_COMPANY TEXT COLLATE SYSTEMNOCASE, USER_DISCIPLINE TEXT COLLATE SYSTEMNOCASE, EMAIL_RECIPIENT INTEGER);
Step
Error SQLITE_ERROR (1) [Step] using 3.35.4 - SQL Error: Table 'mysqltest.user\_defs' doesn't exist
Code: 1146 SQL: SHOW INDEX FROM mysqltest.`user\_defs`, extended_errcode=1
ROLLBACK TRANSACTION;
Step
An unhandled exception occurred at $0061B879:
ESQLite3Exception:
  $0061B879
  $0061B2F8
  $0061A6A0
  $0061A70B
  $0061839B
  $004CB33E
  $0040208E

I am out of ideas. Hope someone can find a solution soon.

Last edited by turkerali (2021-04-10 17:44:38)

mpv · 2021-04-10 20:43:32

Error is because virtual table name is UserDefs

CREATE VIRTUAL TABLE UserDefs

But index is retrieved for table user_defs

SHOW INDEX FROM mysqltest.`user\_defs`

Either first or second is wrong.

Last edited by mpv (2021-04-10 20:44:36)

turkerali · 2021-04-10 21:30:11

Hi mpv,

If you check this link, you will notice that VirtualTableExternalRegister function has an "aExternalTableName" argument which allows you to map the table name
different than the class name for the record. VirtualTableExternalMap function works similarly.

My ORM class is "TSQLUserDefs", which maps to an external MySQL table named "user_defs". You can see a similar usage in SynSelfTests.pas file here.
The same code works for Firebird without any problems.

So the problem should be elsewhere.

Regards.

flydev · 2021-04-12 11:15:50

turkerali wrote:

So the problem should be elsewhere.

Look like in Zeoslib, see comment on line 4640 of the file "ZDbcMetadata.pas" (zeoslib 7.2.10):

{**
  Set the Wildcards character for WildcardsArray variable.
  Overrride this method if the wildcards character is different in other database
}
procedure TZAbstractDatabaseMetadata.FillWildcards;
begin
  SetLength(WildcardsArray,2);
  WildcardsArray[0]:='_';  //<---- seems to be a trublemaker, no idea how to test it with our tests. See http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=13184
  WildcardsArray[1]:='%';
end;

If you read the post following the link, you will find a workarround and a discussion there: https://zeoslib.sourceforge.io/viewtopi … 2&p=160450

Last edited by flydev (2021-04-12 11:17:01)

EgonHugeist · 2021-04-12 14:20:10

@AB

change SynDBZeos.pas method TSQLDBZEOSConnectionProperties.GetFields
lines

    
    sTableName := meta.AddEscapeCharToWildcards(sTableName); //do not use "like" search patterns ['_','%'] so they need to be escaped
    res := meta.GetColumns('',sSchema,sTableName,'');

To

  res := meta.GetColumns('',sSchema, meta.AddEscapeCharToWildcards(sTableName),'');

IZMetadata.GetIndexInfo() simply does not support a pattern. That should help flydev.
Regards, Michael

turkerali · 2021-04-12 15:39:56

Hi flydev, EgonHugeist

I can not thank you enough for finding the source of this bug.

As flydev explained, the bug is due to this code in ZDbcMetadata.pas in Zeoslib:

{**
  Set the Wildcards character for WildcardsArray variable.
  Overrride this method if the wildcards character is different in other database
}
procedure TZAbstractDatabaseMetadata.FillWildcards;
begin
  SetLength(WildcardsArray,2);
  WildcardsArray[0]:='_';  //<---- seems to be a trublemaker, no idea how to test it with our tests. See http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=13184
  WildcardsArray[1]:='%';
end;

And I confirm that the change EgonHugeist suggested solves the issue.

BTW, I wasn't expecting to find a solution to my problem in the foreseeable future, therefore I found a shortcut to bypass half of the Mormot framework
No sqlite3 layer, no TSQLModel, no TSQLRestServerDB instance, no VirtualTableExternalMap, no CreateMissingTables. Sounds to good to be true, doesn't it?
I will share the code here if someone wants to use:

  procedure TMyRestServer.aet_insert(Ctxt: TSQLRestServerURIContext);
  var
    tmp_query: ISQLDBStatement;
    tmp_record: TSQLUserDefs;
    tmp_fields_JSON: RAWUTF8;
    tmp_fields_SQL: RawUTF8;
    tmp_SQL: RawUTF8;
  begin
    tmp_record := TSQLUserDefs.Create;
    tmp_record.USERNAME := 'test_user';
    tmp_record.USERPASS := 'test_pass';
    tmp_record.REAL_NAME := 'test_real_name';
    tmp_record.REAL_SURNAME := 'test_surname';
    tmp_record.REAL_EMAIL := 'test_email';
    tmp_record.USER_COMPANY := 'test_company';
    tmp_record.USER_DISCIPLINE := 'test_discipline';
    tmp_record.EMAIL_RECIPIENT := 1;

    tmp_fields_JSON := tmp_record.GetJSONValues(true,false,soInsert); //{"USERNAME":"test_user","USERPASS":"test_pass",..}
    tmp_fields_SQL := GetJSONObjectAsSQL(tmp_fields_JSON,False,False); //"(USERNAME,USERPASS,..) VALUES ('test_user','test_pass',..)"
    FormatUTF8('INSERT INTO %%;',['user_defs', tmp_fields_SQL], tmp_SQL);
    //Ctxt.Returns('["' + tmp_SQL + '"]'); //
    try
       tmp_query := fConnection.NewThreadSafeStatementPrepared(tmp_SQL, {ExpectResults=}false, {RaiseExceptionOnError=}true);
       tmp_query.ExecutePrepared;
       tmp_query.ReleaseRows;
       Ctxt.Returns('["DONE"]');
    except
       tmp_query := nil;
    end;
  end;

Last edited by turkerali (2021-04-12 18:07:47)

ab · 2021-04-12 19:22:17

Please check https://synopse.info/fossil/info/da27f68352

mORMot Open Source

#1 2021-04-04 13:12:15

External Zeos MySQL tables with underscore ( _ ) cannot be registered.

#2 2021-04-10 17:36:22

Re: External Zeos MySQL tables with underscore ( _ ) cannot be registered.

#3 2021-04-10 20:43:32

Re: External Zeos MySQL tables with underscore ( _ ) cannot be registered.

#4 2021-04-10 21:30:11

Re: External Zeos MySQL tables with underscore ( _ ) cannot be registered.

#5 2021-04-12 11:15:50

Re: External Zeos MySQL tables with underscore ( _ ) cannot be registered.

#6 2021-04-12 14:20:10

Re: External Zeos MySQL tables with underscore ( _ ) cannot be registered.

#7 2021-04-12 15:39:56

Re: External Zeos MySQL tables with underscore ( _ ) cannot be registered.

#8 2021-04-12 19:22:17

Re: External Zeos MySQL tables with underscore ( _ ) cannot be registered.

Board footer