mORMot Open Source

leus

Member

Registered: 2012-09-05

Posts: 79

TSQLRestStorageExternal.Create: external table creation failed

I'm getting the following error during CreateMissingTables() on a MySQL database hosted on Amazon. User has full permissions on this schema.

Error SQLITE_ERROR (1) [Step] using 3.34.0 - TSQLRestStorageExternal.Create: external table creation TStorageXX failed: GetFields() returned nil - SQL="StorageXX"

The first exception occurs in mORMotDB.pas, line 780:

SQL := fProperties.SQLCreate(fTableName,CreateColumns,false);
    if SQL<>'' then
      if ExecuteDirect(pointer(SQL),[],[],false)<>nil then begin
        GetFields;
        if fFieldsExternal=nil then
          raise EORMException.CreateUTF8('%.Create: external table creation % failed:'+
            ' GetFields() returned nil - SQL="%"',[self,StoredClass,fTableName,SQL]);
        TableCreated := true;
      end;

If I break during the exception and check the database, the table creation was succesful.

If I just ignore it and run the program a second time, I get a different error:

Error SQLITE_ERROR (1) [Step] using 3.34.0 - TSQLRestStorageExternal.Create: TStorageXX: unable to create external missing field StorageBoleta.Sucursal - SQL="ALTER TABLE StorageXX ADD Sucursal mediumtext character set UTF8"

This code runs locally without issues, but throws these errors when trying to connect to Amazon.

This is the Amazon information (captured with HeidiSQL):

Host: XXXXX.sa-east-1.rds.amazonaws.com
Network type: MySQL (TCP/IP)
Connected: Yes
Real Hostname: xxxxx
Server OS: Linux
Server version: 5.6.44-log - Source distribution
Connection port: 3306
Compressed protocol: No
Unicode enabled: Yes
SSL enabled: No
max_allowed_packet: 4,0 MiB
Client version (C:\Program Files\HeidiSQL\libmariadb.dll): 10.4.12
Uptime: 235 days, 09:38:38.0
Threads: 8
Questions: 17.212.537
Slow queries: 289
Opens: 253.268
Flush tables: 1
Open tables: 1.977
Queries per second avg: 0,846

Last edited by leus (2021-01-06 14:43:25)

leus

Member

Registered: 2012-09-05

Posts: 79

Re: TSQLRestStorageExternal.Create: external table creation failed

Did some extra debugging, and the issue seems to be table case sensitivity (it appears to be something related to Zeos). There is a variable lower_case_table_names but I don't know yet where it is set.

Edit: I think mormot tries to get the columns in SynDBZeos.pas, around line 720:

procedure TSQLDBZEOSConnectionProperties.GetFields(
  const aTableName: RawUTF8; out Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    n, i: integer;
    Schema, TableName: RawUTF8;
    sSchema, sTableName: string;
    F: TSQLDBColumnDefine;
    FA: TDynArray;
begin
  if GetDatabaseMetadata(meta) then begin
    SQLSplitTableName(aTableName, Schema,TableName);
    sSchema := UTF8ToString(Schema);
    { mormot does not create the Tables casesensitive but gives mixed cased strings as tablename
      so we normalize the identifiers to database defaults : }
    sTableName := meta.GetIdentifierConvertor.ExtractQuote(UTF8ToString(TableName));
    sTableName := meta.AddEscapeCharToWildcards(sTableName); //do not use "like" search patterns ['_','%'] so they need to be escaped
    res := meta.GetColumns('',sSchema,sTableName,'');

The line with sTableName := meta.GetIdentifierConvertor.ExtractQuote(UTF8ToString(TableName)) converts the table name to lowercase. This causes meta.GetColumns() to fail.

Last edited by leus (2021-01-06 15:38:07)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: TSQLRestStorageExternal.Create: external table creation failed

Sounds more like a problem on Zeos side, since it involves a Zeos method.
I don't know the internals involved with these table names casing.

Could you ask this on Zeos support forum, please?

leus

Member

Registered: 2012-09-05

Posts: 79

Re: TSQLRestStorageExternal.Create: external table creation failed

Sounds more like a problem on Zeos side, since it involves a Zeos method.
I don't know the internals involved with these table names casing.

Could you ask this on Zeos support forum, please?

Yes, I already filed a bug report, but I also think there are some weird stuff happening there on the mORMot side. I had to move to another stuff for a while but I'll revisit this topic later.