CreateMissingTables error with Zeos / MariaDB

AOG · 2015-10-14 12:08:15

Hello,

I am encountering a strange error when the tables have to be created using Zeos and MariaDB.

It has come down into the following function (inside ZPlainMySqlDriver):

function TZMySQLBaseDriver.FetchLengths(Res: PZMySQLResult): PMySQLLengthArray;
var
  x:integer;
begin
  x := mysql_num_fields(Res);
  Result := mysql_fetch_lengths(Res);
end; 

The table definition is:

TSQLSystemUser = class(TSQLAuthUser)
protected
    fVisible:boolean;
    fActive:boolean;
    fNewPassword:RawUTF8;
published
    property Visible:boolean read fVisible write fVisible;
    property Active:boolean read fActive write fActive;
    property NewPassword: RawUTF8 read fNewPassword write fNewPassword;
end;

In my case, x returns 9, which is correct.
But the mysql_fetch_lengths(Res) returns an array of length 1
Which would be merely impossible !!

Can somebody help me out ? I am stuck now for days ...

Edit:
I am using latest FPC, latest mORMot, latest Zeos.
The table is created correct in database. And is visible with e.g. phpmysqladmin.

Last edited by AOG (2015-10-14 12:33:40)

AOG · 2015-10-14 13:33:50

Well, I get three tables (out of 16) created if I change the Zeos sources (FPC Win32 on Win8.1 64 bit):

PMySQLLengthArray = PLongWord;
//PMySQLLengthArray = ^TMySQLLengthArray;
//TMySQLLengthArray = array[0..0] of Ulong; //http://dev.mysql.com/doc/refman/4.1/en/column-count-limit.html 

and

//Len := FLengthArray^[ColumnIndex];
Len := FLengthArray[ColumnIndex];

Working better, but still no 100% success. Seems something with Zeos ...

AOG · 2015-10-14 13:38:49

Well, 100% success if I remove all unique settings from my tables !!!

AOG · 2015-10-14 15:26:37

Found the cause of all troubles !

A table like this:

TSQLClient = class(TSQLRecord)
  protected
    fName: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
end;

produces a table with a Name field defined as varchar with length 0.
This causes troubles for Zeos and also for mORMot when defining a field as unique.

Changing the above into:

TSQLClient = class(TSQLRecord)
  protected
    fName: RawUTF8;
  published
    property Name: RawUTF8 index 50 read fName write fName;
end;

produces a table with a Name field defined as varchar with length 50. This solves all !!

Last edited by AOG (2015-10-14 15:27:15)

ab · 2015-10-14 15:30:39

A field with no "index ###" attribute should create a CLOB kind of column in the table.
AFAIK a mediumtext character set UTF8 kind of column.

Sounds like if MySQL does not allow to index such column values.
See http://synopse.info/files/html/api-1.18 … DEXONBLOBS

AOG · 2015-10-14 16:49:02

After debugging, I saw that a RawUTF8 without any index-length setting is created as ftNull with a width of 0 !

function TSQLDBConnectionProperties.SQLFieldCreate(const aField: TSQLDBColumnCreate;
  var aAddPrimaryKey: RawUTF8): RawUTF8;
begin
  if (aField.DBType=ftUTF8) and (aField.Width-1<fSQLCreateFieldMax) then
    result := FormatUTF8(fSQLCreateField[ftNull],[aField.Width]) else  <----------------------- here !
    result := fSQLCreateField[aField.DBType];

And a ftNull is a varchar !

Last edited by AOG (2015-10-14 16:49:26)

ab · 2015-10-14 17:00:25

If aField.Width=0, aField.Width-1 equals integer(-1), and since it is a cardinal, it is > fSQLCreateFieldMax.
So it is not created as ftNull with a width of 0, but as ftUTF8.

At least under Delphi...

Perhaps FPC is not doing the same type conversion.
Could you try:

  if (aField.DBType=ftUTF8) and (cardinal(aField.Width-1)<fSQLCreateFieldMax) then

AOG · 2015-10-14 17:27:38

Yep ! Works !! Fields are now created as mediumtext (with no length defined).
However, Zeos still errors out on the same impossible spot as previous :
#fields = 9; #lengths=1 !!!

ab · 2015-10-14 19:24:41

It was indeed a weird FPC compilation issue... I observed similar problems about string concatenation (code pages not propagated), but I did not know it was also the case with integer types.
Committed as http://synopse.info/fossil/info/7d4255cc54

Now I do not know what happens now at ZDBC level.

AOG · 2015-10-14 20:16:17

After your patch:

Fields are created as mediumtext.
Zeos errors out on previous mentioned impossible bug.
mORMot errors out on creating indexes on mediumtext fields (if field is designed AS unique).

ab · 2015-10-14 22:22:30

Mediumtext fields cannot be indexed with MySQL.
You need to specify a width to define nvarchar columns...

AOG · 2015-10-15 05:13:04

If I read the documentation correct, indexes are allowed on text !
See : https://dev.mysql.com/doc/refman/5.6/en/blob.html
See : https://dev.mysql.com/doc/refman/5.6/en … dexes.html
However, the doc states that you need to define a prefix length for the index ?!

Last edited by AOG (2015-10-15 05:15:48)

AOG · 2015-10-15 07:05:33

The included (very ugly !!) patch does work for me, in creating text columns with a unique index !
Do not judge the code, it's just ment as a proof of concept.
Inside function TSQLDBConnectionProperties.SQLCreate(const aTableName: RawUTF8; ........

G:='';
for i := 0 to high(aFields) do begin
    F := SQLFieldCreate(aFields[i],AddPrimaryKey);
    if (Pos('mediumtext',F)>0) AND (Pos('UNIQUE',F)>0) then
    begin
      F := StringReplace(F, 'UNIQUE', '', []);
      G := G + aFields[i].Name +'(100),';
    end;
    if i<>high(aFields) then
      F := F+',';
    result := result+F;
  end;
  if Length(G)>0 then
  begin
    delete(G,length(G),1);
    G := ' unique (' + G;
    G := G + ')';
    result := result + ', ' + G;
  end;

AOG · 2015-10-15 09:28:47

Hi Ab,

I have another question, related to the setup used in this message thread.

I am performing a executelist statement that includes the ID's. But, in my case, the virtual tables don't have an ID included !
Am I doing something wrong in my setup ?

RestClient.ExecuteList([TSQLProduct],'SELECT Product.ID AS ID FROM Product')

ab · 2015-10-15 10:45:28

All SQLite3 virtual tables do have an ID/RowID field, by definition.
This primary key may be mapped with another external primary key field, but it should be an integer column also.

I do not get what you are asking...

BTW, running ExecuteList as such does not make any sense.
Use the ORM methods instead!
And with an external table, it would be slower, since your SELECT statement would force passing through the virtual table SQLite3 mechanism, which is not needed in your case.

AOG · 2015-10-15 11:17:25

To clearify some things:

This is the (mORMot-)SQL that creates the virtual table for the SystemUser in my setup:

CREATE VIRTUAL TABLE SystemUser USING External(LogonName TEXT COLLATE SYSTEMNOCASE, DisplayName TEXT COLLATE SYSTEMNOCASE, PasswordHashHexa TEXT COLLATE SYSTEMNOCASE, GroupRights INTEGER, Data BLOB, Visible INTEGER, Active INTEGER, NewPassword TEXT COLLATE SYSTEMNOCASE);

There is no ID !

This is the real SQL I use (to create a pivot table):

                SQLWhere:=FormatUTF8(' WHERE WeekNumber = ? AND Year = ? ',[],[aWeekNumber,aYear]);
                SQLWhere:=
                ' SELECT * FROM ( '+
                ' SELECT J.ID,J.Number,J.Name,'+
                '        NULL AS Monday, '+
                '        NULL AS Tuesday, '+
                '        NULL AS Wednesday, '+
                '        NULL AS Thursday, '+
                '        NULL AS Friday, '+
                '        NULL AS Saturday, '+
                '        NULL AS Sunday, '+
                '        NULL AS Total '+
                ' FROM   Project J '+
                ' WHERE '+
                ' J.Number LIKE '+QuotedStr('201%')+
                ' AND '+
                ' J.ID <> '+
                ' ( '+
                ' SELECT Project '+
                ' FROM   ProjectHours '+
                SQLWhere+
                ' ) '+
                ' UNION '+
                ' SELECT I.ID,I.Number,I.Name,'+
                '        max(case when sum(N.Monday>0) then sum(N.Monday) else NULL end) Monday, '+
                '        max(case when sum(N.Tuesday>0) then sum(N.Tuesday) else NULL end) Tuesday, '+
                '        max(case when sum(N.Wednesday>0) then sum(N.Wednesday) else NULL end) Wednesday, '+
                '        max(case when sum(N.Thursday>0) then sum(N.Thursday) else NULL end) Thursday, '+
                '        max(case when sum(N.Friday>0) then sum(N.Friday) else NULL end) Friday, '+
                '        max(case when sum(N.Saturday>0) then sum(N.Saturday) else NULL end) Saturday, '+
                '        max(case when sum(N.Sunday>0) then sum(N.Sunday) else NULL end) Sunday, '+
                '        sum(Sunday+Monday+Tuesday+Wednesday+Thursday+Friday+Saturday+Sunday) Total '+
                ' FROM   ProjectHours N '+
                '        INNER JOIN Project I ON N.Project = I.ID '+
                SQLWhere+
                ' GROUP BY I.ID ) AS Z'+
                ' GROUP BY Z.ID '+
                ' ORDER BY Z.Number '+
                ';';

But the most important thing is: why are the ID's not created in the virtual tables on my system ??

Edit:
Ab, would you advice me not to use this extensive SQL, but to use ORM and do the pivot-calculation by pascal-code ?
My reasoning was: on a slow server (in my case), this single SQL would be much faster than multiple ORM's.

Last edited by AOG (2015-10-15 11:47:39)

ab · 2015-10-15 12:09:54

Even if you do not create the ID field, there is an implicit RowID field in all virtual tables.
This ID/RowID is mandatory to access the content, for both reading and writing.
See e.g. https://www.sqlite.org/vtab.html#xrowid

If there is no primary key, mapping this ID/RowID field, in your external table, any request via the SQlite3 virtual tables won't work!

When you run the SQL over the SQLite3 main engine, it would use RowID values to access the virtual table content.
See http://synopse.info/files/html/Synopse% … IRTUAL_EMF
and http://synopse.info/files/html/Synopse% … DESIGN_EMF

AOG · 2015-10-15 12:33:26

Thanks for clearing things up !

So, if I understand correctly, the external identifier is ID, the (internal) virtual identifier is RowID (which is mapped towards the external ID) !?
The logic: ORM still uses ID, raw SQL (on virtual) must use RowID ?!

ab · 2015-10-15 13:43:07

On external table, you can map the internal ID/RowID field with whatever field name you wish.
See http://synopse.info/files/html/Synopse% … l#TITL_120

AOG · 2015-10-15 14:31:58

Alright ! Thanks Ab for all the explanations !!
Final (lazy) question ... don't burn me ... ;-)
If the identifier RowID would be used as standard ID for all ID's used, this would make my current program transparent.
I am not asking you to apply this, because it would break a lot.
But would it be doable for me, by changing some of the mORMot sources ?

Edit: I did rework the sources a little to use RowID everywhere ... and it works ... app is transparent to internal and external, even with custom SQL statements !

Last edited by AOG (2015-10-15 15:12:04)

ab · 2015-10-15 15:24:16

Warning: some database engines (e.g. Oracle) do use RowID as private internal field, which is not compatible with the SQlite3's primary key.

This is why the primary key is called ID almost everywhere in the mORMot source code.
The ORM changes 'RowID' into 'ID' almost everywhere it can...

mORMot Open Source

#1 2015-10-14 12:08:15

CreateMissingTables error with Zeos / MariaDB

#2 2015-10-14 13:33:50

Re: CreateMissingTables error with Zeos / MariaDB

#3 2015-10-14 13:38:49

Re: CreateMissingTables error with Zeos / MariaDB

#4 2015-10-14 15:26:37

Re: CreateMissingTables error with Zeos / MariaDB

#5 2015-10-14 15:30:39

Re: CreateMissingTables error with Zeos / MariaDB

#6 2015-10-14 16:49:02

Re: CreateMissingTables error with Zeos / MariaDB

#7 2015-10-14 17:00:25

Re: CreateMissingTables error with Zeos / MariaDB

#8 2015-10-14 17:27:38

Re: CreateMissingTables error with Zeos / MariaDB

#9 2015-10-14 19:24:41

Re: CreateMissingTables error with Zeos / MariaDB

#10 2015-10-14 20:16:17

Re: CreateMissingTables error with Zeos / MariaDB

#11 2015-10-14 22:22:30

Re: CreateMissingTables error with Zeos / MariaDB

#12 2015-10-15 05:13:04

Re: CreateMissingTables error with Zeos / MariaDB

#13 2015-10-15 07:05:33

Re: CreateMissingTables error with Zeos / MariaDB

#14 2015-10-15 09:28:47

Re: CreateMissingTables error with Zeos / MariaDB

#15 2015-10-15 10:45:28

Re: CreateMissingTables error with Zeos / MariaDB

#16 2015-10-15 11:17:25

Re: CreateMissingTables error with Zeos / MariaDB

#17 2015-10-15 12:09:54

Re: CreateMissingTables error with Zeos / MariaDB

#18 2015-10-15 12:33:26

Re: CreateMissingTables error with Zeos / MariaDB

#19 2015-10-15 13:43:07

Re: CreateMissingTables error with Zeos / MariaDB

#20 2015-10-15 14:31:58

Re: CreateMissingTables error with Zeos / MariaDB

#21 2015-10-15 15:24:16

Re: CreateMissingTables error with Zeos / MariaDB

Board footer