Oracle & UTF16 - Fetched column value was truncated (solved...?)

[aehimself]

I didn't want to necro this thread so I decided to post a new one instead - plus the issue has nothing to do with 7.2 anyway.

If I change the ClientCodePage to UTF16 on an Oracle connection I consistently get ORA-01406: fetched column value was truncated (#1406).

What I know so far:
- Oracle client library and server version does not matter as the test host was upgraded to 19.0 and the issue still persists
- Listing the columns explicitly instead of SELECT * does not solve the issue, it's not the metadata collection
- Removing random (varchar) columns from the SELECT one-by-one will lead to a sweet-spot, causing the query to execute

So I went back to my dev machine to start debugging once again, and behold - the same query executed without issues on the same database! See if you can spot the difference in the logs:

Code: Select all

[2022.02.04 10:40:20] MYDB: Oracle 19.0.0, client version: 19.8.0, database access component version: 8.0.0-a846f83a8
[2022.02.04 10:40:20] MYDB: Connected to database ***, schema *** at host *** as user ***
[2022.02.04 10:40:20] MYDB: Loaded library: C:\DRVOracle\oci.dll
[2022.02.04 10:40:20] MYDB: Transaction isolation level: Read committed
[2022.02.04 10:40:20] MYDB: RawStringEncoding: DB_CP
[2022.02.04 10:40:20] MYDB: codepage: UTF16
[2022.02.04 10:40:20] MYDB: OCIMultiThreaded: True
[...]
[2022.02.04 10:40:20] MYDB: Opening dataset in background thread #11400 started.
[2022.02.04 10:40:20] MYDB: Prepare Statement 71 : SELECT * FROM MYTABLE
[2022.02.04 10:40:20] MYDB: Prepare Statement 72 : SELECT ALL_TAB_COLUMNS.OWNER, ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.NULLABLE, ALL_TAB_COLUMNS.DATA_DEFAULT, ALL_TAB_COLUMNS.COLUMN_ID, ALL_COL_COMMENTS.COMMENTS, ALL_TAB_COLUMNS.CHAR_LENGTH FROM ALL_TAB_COLUMNS JOIN ALL_COL_COMMENTS ON ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER WHERE ALL_TAB_COLUMNS.TABLE_NAME like 'MYTABLE' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2022.02.04 10:40:21] MYDB: Execute prepared Statement 72 in 516 ms
[2022.02.04 10:40:21] MYDB: Fetch Statement 72, affected 145 row(s) in 765 ms
[2022.02.04 10:40:21] MYDB: Fetch FETCH ROW ORA-01406: fetched column value was truncated  (#1406)
[2022.02.04 10:40:21] EZSQLException was raised while Opening dataset with the message SQL Error: ORA-01406: fetched column value was truncated Code: 1406 Message: FETCH ROW

Code: Select all

[2022.02.04 10:40:38] MYDB: Oracle 19.0.0, client version: 19.8.0, database access component version: 8.0.0-a846f83a8
[2022.02.04 10:40:38] MYDB: Connected to database ***, schema *** at *** as user ***
[2022.02.04 10:40:38] MYDB: Loaded library: C:\DRVOracle\oci.dll
[2022.02.04 10:40:38] MYDB: Transaction isolation level: Read committed
[2022.02.04 10:40:38] MYDB: RawStringEncoding: DB_CP
[2022.02.04 10:40:38] MYDB: codepage: UTF16
[2022.02.04 10:40:38] MYDB: internal_buffer_size: 300000
[2022.02.04 10:40:38] MYDB: OCIMultiThreaded: True
[...]
[2022.02.04 10:41:01] MYDB: Opening dataset in background thread #6312 started.
[2022.02.04 10:41:01] MYDB: Prepare Statement 77 : SELECT * FROM MYTABLE
[2022.02.04 10:41:01] MYDB: Prepare Statement 78 : SELECT ALL_TAB_COLUMNS.OWNER, ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.NULLABLE, ALL_TAB_COLUMNS.DATA_DEFAULT, ALL_TAB_COLUMNS.COLUMN_ID, ALL_COL_COMMENTS.COMMENTS, ALL_TAB_COLUMNS.CHAR_LENGTH FROM ALL_TAB_COLUMNS JOIN ALL_COL_COMMENTS ON ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER WHERE ALL_TAB_COLUMNS.TABLE_NAME like 'MYTABLE' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2022.02.04 10:41:02] MYDB: Execute prepared Statement 78 in 525 ms
[2022.02.04 10:41:02] MYDB: Fetch Statement 78, affected 145 row(s) in 649 ms
[2022.02.04 10:41:02] MYDB: Execute prepared Statement 77 in 669 ms
[2022.02.04 10:41:52] MYDB: Fetch Statement 77, affected 206733 row(s) in 50717 ms
[2022.02.04 10:41:53] MYDB: Query returned 206 733 record(s) in 50 seconds
[2022.02.04 10:41:53] MYDB: Opening dataset in background thread #6312 ended.

Hint: the difference is that on my dev machine I set internal_buffer_size to a higher value for playing around. The above logs were collected from the prod machine, just changing this setting.
So it seems yes, we are not providing a big enough buffer for the data to be filled in - fortunately this can be controlled with a property on the connection.

This leaves us (me?) with one question only. Is this behavior expected, or Zeos should be able to determine the necessary buffer size based on the maximum amount of data expected?

[aehimself]

Turns out I was too quick in declaring success.

UTF16 on Oracle databases is still broken, showing some scrambled up characters in string fields.
Setting internal_buffer_size only gets rid of the exception when downloading data, it still seems to be corrupted.

Notice the difference between AL32UTF8 and UTF16 codepage, and also how the displayed text changes if you select the field.

[aehimself]

One more interesting thing. UTF16 works perfectly if I start the application from the work PC (same client .dll, connecting to the same DB with the exact same parameters), not my local laptop.

So it seems data gets corrupted when it goes through VPN...?!

Edit: it's not the VPN as a colleague of mine confirmed that he experiences no corruption whether running locally or remotely. I also confirmed that NLS_LANGUAGE has no effect... however at work it was HUNGARIAN for me and at home it's AMERICAN, at my colleague it's the exact opposite and it works at both places. Also we are using the same client library (19.8.0) at all places. Loading the dataset in the VCL thread makes no difference (other than freezing the application for a while :)), corruption still appears.

[marsupilami]

This leaves us (me?) with one question only. Is this behavior expected, or Zeos should be able to determine the necessary buffer size based on the maximum amount of data expected?

Hmm - usually I would expect Zeos to be able to calculate bffer size correctly - if at all possible. Otherwise I would expect to have some kind of parameter. I seem to remember that Egonhugeist and you already had a discussion about the buffer size?

otice the difference between AL32UTF8 and UTF16 codepage, and also how the displayed text changes if you select the field.

This part is what really gets me thinking. The field contents seems to be displayed correctly when the field is not selected but gets corrupted when you select the field. Is that correct? Maybe Zeos is doing something wrong with the field buffers?

[aehimself]

Hmm - usually I would expect Zeos to be able to calculate bffer size correctly - if at all possible. Otherwise I would expect to have some kind of parameter. I seem to remember that Egonhugeist and you already had a discussion about the buffer size?

Yes, about the exact same issue. I already tried to debug this by manually resizing all buffers to 10-100 times, without any effective result.

This part is what really gets me thinking. The field contents seems to be displayed correctly when the field is not selected but gets corrupted when you select the field. Is that correct? Maybe Zeos is doing something wrong with the field buffers?

No, the field is already corrupted when unselected. When you select it, the content changes to some other garbage.

And here comes the funny part, because this makes no sense whatsoever....
In my bench application I could get rid of the issue if "LobCacheMode" is unset. If "LobCacheMode" is OnLoad, data is corrupted.
BUT THE TABLE HAS NO LOB FIELDS...?! And why only one PC is affected...?!

I'll try to put this change in my real application, will get back with some results...

[aehimself]

Yep, works in my main application too.

It turns out that my issue is actually two issues, which is caused by the same thing, LobCacheMode.
- Issue #1: SELECT * FROM MyTable immediately throws the error in the subject. A valid workaround is increasing the buffer size in which case query executes and data is downloaded. This table has no CLOB or BLOB field.
- Issue #2: data corruption in DBGrid, SELECT * FROM MyOtherTable. This issue is NOT solved by increasing the buffer size and this table neither has CLOB or BLOB fields.

Now, in the very moment when I set LobCacheMode from OnLoad to nothing, both issues disappear. Also, LobCacheMode can stay on OnLoad if the ClientCodePage is anything but UTF16.

Then again, only one PC is producing this symptom that I know of.

What on Earth is going on here...?!

[aehimself]

Seems they have nothing at common after all.

With the following code

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ZConnection,
  ZDataSet;

Procedure SetupConn(Const inConnection: TZConnection; Const inUseLobCache, inOverrideBuffer: Boolean);
Var
 s: String;
Begin
 If inConnection.Connected Then inConnection.Disconnect;

 WriteLn;
 s := 'UseLobCache = ';
 If inUseLobCache Then Begin
                       s := s + 'OnLoad';
                       inConnection.Properties.Values['LobCacheMode'] := 'OnLoad';
                       End
   Else Begin
        s := s + 'N/A';
        inConnection.Properties.Values['LobCacheMode'] := '';
        End;
 s := s + ', internal_buffer_size = ';

 If inOverrideBuffer Then Begin
                          s := s + '300000';
                          inConnection.Properties.Values['internal_buffer_size'] := '300000';
                          End
   Else Begin
        s := s + 'N/A';
        inConnection.Properties.Values['internal_buffer_size'] := '';
        End;

 WriteLn(s);
 inConnection.Connect;
End;

Procedure TestMyTable(Const inConnection: TZConnection);
Var
 q: TZQuery;
Begin
 Write('Testing MyTable...');
 q := TZQuery.Create(nil);
 Try
  q.Connection := inConnection;
  q.SQL.Text := 'SELECT * FROM MyTable';
  Try
   q.Open;
   WriteLn(' OK');
  Except
   On E:Exception Do WriteLn(' ' + E.Message.Replace(#10, '').Replace(#13, ' '));
  End;
 Finally
  FreeAndNil(q);
 End;
End;

Procedure TestMyTable2(Const inConnection: TZConnection);
Var
 q: TZQuery;
 tb: TBytes;
 s: String;
Begin
 Write('Testing MyTable2...');
 q := TZQuery.Create(nil);
 Try
  q.Connection := inConnection;
  q.SQL.Text := 'SELECT * FROM MyTable2';
  Try
   q.Open;
   While Not q.Eof Do
    Begin
     tb := Tencoding.Unicode.GetBytes(q.FieldByName('C_NAME').AsString);
     s := TEncoding.Unicode.GetString(q.FieldByName('C_NAME').AsBytes, 0, Length(q.FieldByName('C_NAME').AsBytes) - 1);

     If TEncoding.Unicode.GetString(tb) <> s Then Raise Exception.Create('Corrupted row #' + q.RecNo.ToString + ': "' + q.FieldByName('C_NAME').AsString + '"');
     q.Next;
    End;
   WriteLn(' OK');
  Except
   On E:Exception Do WriteLn(' ' + E.Message.Replace(#10, '').Replace(#13, ' '));
  End;
 Finally
  FreeAndNil(q);
 End;
End;

Var
 conn: TZConnection;
 a, b: Boolean;
begin
 conn := TZConnection.Create(nil);
 Try
  conn.Protocol := 'oracle';
  conn.Catalog := '***';
  conn.ClientCodepage := 'UTF16';
  conn.Database := '***';
  conn.LibraryLocation := 'C:\LocalStorage\OCI_19_8\x86\oci.dll';
  conn.Password := '***';
  conn.User := '***';

  For a := False To True Do
   For b := False To True Do
    Begin
     SetupConn(conn, a, b);
     TestMyTable(conn);
     TestMyTable2(conn);
    End;
 Finally
  FreeAndNil(conn);
  WriteLn;
  Write('Press Enter to exit...');
  ReadLn;
 End;
end.

i have the output:

UseLobCache = N/A, internal_buffer_size = N/A
Testing MyTable... SQL Error: ORA-01406: fetched column value was truncated Code: 1406 Message: FETCH ROW
Testing MyTable2... OK

UseLobCache = N/A, internal_buffer_size = 300000
Testing MyTable... OK
Testing MyTable2... OK

UseLobCache = OnLoad, internal_buffer_size = N/A
Testing MyTable... SQL Error: ORA-01406: fetched column value was truncated Code: 1406 Message: FETCH ROW
Testing MyTable2... Corrupted row #2: "InaktE_2"

UseLobCache = OnLoad, internal_buffer_size = 300000
Testing MyTable... OK
Testing MyTable2... Corrupted row #2: "InaktE_2"

Press Enter to exit...

Which makes me to believe LobCacheMode causes the corruption in MyTable2, while unset internal_buffersize causes the truncated value in MyTable.

Edit: I also realized what is causing the different behavior on one PC. There's a setting which is forcing LobCacheMode to OnLoad, and it is by default - off. This is why 3 PCs don't corrupt - there the setting wasn't changed, therefore LobCacheMode is off.

[marsupilami]

Which makes me to believe LobCacheMode causes the corruption in MyTable2, while unset internal_buffersize causes the truncated value in MyTable.

Ok - so maybe we should disable lob cache mode code for now?

And about the buffer size: Do we do some wrong calculations there?

[aehimself]

Ok - so maybe we should disable lob cache mode code for now?

I don't think so. I can skip using it in my application and once Mark can take a look and possibly fixes it I'll start using it again :)
LobCacheMode affects more drivers than Oracle, and only UTF16 seems to be negatively affected. Sounds like a really localized issue to me.

And about the buffer size: Do we do some wrong calculations there?

I don't know. The thing is - at least now I know what affects the behavior, finally I can search for something in where to look.

Edit: The buffer size is used to calculate iterations (I guess how many cycles we have to run to download the full resultset?) and I found a really interesting thing. ZDbcOracleResultSet.pas : 2543 (TZOracleResultSet.Open)

Code: Select all

  end else if (RowSize > FZBufferSize) { now let's calc the iters we can use }
    then FIteration := 1
    else FIteration := FZBufferSize div RowSize;

So, of the row size is larger than our buffer size, we run 1 iteration, otherwise we run multiple...?
The strange thing is, if I change the relation (if RowSize is smaller than our buffer then 1 iteration) my program simply eats up all the available memory and then crashes with a system OOM exception.

Edit-edit: Wrong! Iteration is misleading, it counts HOW MANY ROWS acually fit in the buffer! However, the allocation should be according to the real row size, see line 2551:

Code: Select all

  SetLength(FRowsBuffer, RowSize * FIteration); //Alloc mem we need for multiple rows

So it seems if the resultset is overflowing the buffer we have, we are simply using a large enough buffer for 1 row. Based on this I'd say yes, RowSize is incorrect. I'll do some checks later on.

[aehimself]

So this is strange. No matter what codepage is set (ANSI, UTF8 or UTF16), a 6-length varchar column ALWAYS gets the exact same information:

Precision: 6
csid: 873
ScaleOrCharSetForm: 1
CharOctedLength: 26

Because of this, buffer size is the same.

I also realized that the magic number of FIteration must be 8 in my case for the query not to throw the truncation error. As this is the (default) buffer size divided by the row size, the only way I can make that happen is if I increase the buffer size manually.

I started to think that this is intentional. If you get overflow, increase your buffer size. Also, this query is huge - 110+ columns, with 200k+ rows so I will accept it as normal behavior.

[aehimself]

As for the data corruption, it only happens if LobCacheMode is OnLoad, not if None or OnAccess. The main difference is at ZDbcOracleStatement.pas : 441 (TZAbstractOracleStatement.CreateResultSet)

Code: Select all

      if (LobCacheMode = lcmOnLoad)
      then CachedResultSet := TZCachedResultSet.Create(NativeResultSet, SQL, nil, ConSettings)
      else CachedResultSet := TZOracleCachedResultSet.Create(NativeResultSet, SQL, nil, ConSettings);

Basically, TZOracleCachedResultSet works fine, TZCachedResultSet doesn't.

I went into what is happening and I found the place where the wrong transformation happens: ZDbcOracleResultSet.pas : 903 (TZOracleAbstractResultSet.GetPAnsiChar)

Code: Select all

jmpTestN:         if ColumnCodePage = zCP_UTF16 then begin
                    Len := Len shr 1;
jmpW2A:             fRawTemp := PUnicodeToRaw(PWideChar(Result), Len, GetW2A2WConversionCodePage(ConSettings));
                    Len := Length(fRawTemp);
                    if Len = 0
                    then Result := pEmptyAnsiString
                    else Result := Pointer(fRawTemp);
                  end;

Here, fRawTemp is a RawByteString and contains the text correctly. In the moment when we cast that to a PAnsiChar (Result := Pointer(fRawTemp)) data gets corrupted and is stored this way. Therefore, the dataset and all components display the corrupt data.

It is NOT a memory corruption, it's data corruption due to wrongful transformation.

[aehimself]

Sooooo...

The thing is, fRawTemp is UTF8 and therefore Result (as a PAnsiChar) will be treated as such. This is strange to me, as we are explicitly asking for UTF16...? Maybe this is just how RDBMS systems work, they don't support Unicode...? This could be confirmed by ZDbcUtils.pas : 2193 (GetW2A2WConversionCodePage)

Code: Select all

    then if ConSettings.ClientCodePage.Encoding = ceUTF16
      then Result := zCP_UTF8

One more thing which popped up in my mind, we are on the DBC layer at the moment. Is it possible that I'm poking the bear at it's wrong end as everything is UTF8 here and it gets converted on the component layer...?

@Michael, @Jan ?

[marsupilami]

@LobCacheMode: I didn't mean to suggest to disable LobCacheMode all together. I just meant to disable LobCacheMode for Oracle. Each driver has to decide on its own what to do about LobCacheMode. The dblib driver doesn't care about LobCacheMode at all because it always has to cache LOBs. THat is just the way the TDS protocol works...

One more thing which popped up in my mind, we are on the DBC layer at the moment. Is it possible that I'm poking the bear at it's wrong end as everything is UTF8 here and it gets converted on the component layer...?

I am not sure which character set to expect with .GetAnsiString and its friends. Usually I would assume for GetAnsiString to return the data in the connection character set encoding. Then the Component Layer would be responsible for conversion. Taking a look at TZPostgreSQLResultSet.GetPAnsiChar seems to support that idea: There is no character set conversion done there. Something similar happens for the SQLITE driver (The only driver to also support UTF16, I think): TZSQLiteResultSet.GetPAnsiChar calls sqlite3_column_text, which per the sqlite documentation always returns UTF8 strings.

Soo - for a database driver that uses UTF16 as its connection character set, I would assume that GetPAnsiChar returns UTF8 data because everything else wouldn't make much sense?

Does that help?

[aehimself]

I just meant to disable LobCacheMode for Oracle

Oracle does support it, if you set UTF8 it works like a charm. Instead of disabling it I guess we should try to make it to work on UTF16 too, somehow, instead.

I am not sure which character set to expect with .GetAnsiString and its friends. Usually I would assume for GetAnsiString to return the data in the connection character set encoding. Then the Component Layer would be responsible for conversion.

This is the issue, as CachedOracleResultSet seems to return the correct encoding, while TZCacedResultSet doesn't. It would feel bad to put a check in the component that if the connection's codepage is UTF16 AND LobCacheMore is OnLoad, then do an UTF8-Unicode conversion on all text fields. As at the moment this is the only buggy combination, fixing it on the component layer would require exactly this.

Soo - for a database driver that uses UTF16 as its connection character set, I would assume that GetPAnsiChar returns UTF8 data because everything else wouldn't make much sense?

I'm sorry, I totally lost you on this one. I expect that if a connection is set to use UTF16, then they should return UTF16. So the RDBMS to return UTF8 when it is explicitly told to use UTF16 doesn't make sense to me on any level.

Then again, this only happens in TZCachedResultSet. I'll try to do a comparison between the implementation of the Oracle version and this one, maybe I can catch a meaningful difference.
Or maybe, TZCachedResultset is not fully Oracle compatible and a new has to be derived from it. Idk.

[aehimself]

TZCachedOracleResultSet is derived from TZCachedResultSet, so... :)

The only impactful difference is in the method FillColumnsInfo: Orcale version sets the ColumnType to stUnicodeString, the regular to stString... and if I change the type from the debugger to stUnicodeString, no more corruption happens...!

The only thing to find out, why MetaData returns the wrong type in TZCachedResultSet...