Fix SynDBOracle for work under linux

IL_S · 2018-01-07 14:13:47

Hello!

I'm trying to use SynDBOracle in my project for a Linux server. But this unit is designed only for windows.

I've modified it and now it works fine for me.

Diff:

--- /usr/src/vcl/mormot.1.18/SynDBOracle.pas	2017-10-05 02:49:08.000000000 +0700
+++ /usr/src/vcl/wbLib/SynDBOracle.pas	2017-12-24 23:35:34.000000000 +0700
@@ -150,6 +150,8 @@
 uses
   {$ifdef MSWINDOWS}
   Windows,
+  {$else}
+  dynlibs,
   {$endif}
   SysUtils,
   {$ifndef DELPHI5OROLDER}
@@ -1802,36 +1804,52 @@
 end;
 
 constructor TSQLDBOracleLib.Create;
+const
+  {$ifdef MSWINDOWS}
+  libname = 'oci.dll';
+  {$else}
+  libname = 'libclntsh.so';
+  {$endif}
 var P: PPointer;
     i: integer;
     orhome: string;
+
+  function SafeLoadLibrary(const FileName: TFileName): HMODULE;
+  begin
+    {$ifdef MSWINDOWS}
+    Result:=SysUtils.SafeLoadLibrary(FileName);
+    {$else}
+    Result:=LoadLibrary(PAnsiChar(AnsiString(fLibraryPath)));
+    {$endif}
+  end;
+
 begin
-  fLibraryPath := 'oci.dll';
+  fLibraryPath := libname;
   if (SynDBOracleOCIpath<>'') and DirectoryExists(SynDBOracleOCIpath) then
-    fLibraryPath := ExtractFilePath(ExpandFileName(SynDBOracleOCIpath+'\'))+fLibraryPath;
+    fLibraryPath := ExtractFilePath(ExpandFileName(SynDBOracleOCIpath{+PathDelim}))+fLibraryPath;
   fHandle := SafeLoadLibrary(fLibraryPath);
   if fHandle=0 then begin
     if fHandle=0 then begin
       orhome := GetEnvironmentVariable('ORACLE_HOME');
       if orhome<>'' then begin
-        fLibraryPath := IncludeTrailingPathDelimiter(orhome)+'bin\oci.dll';
+        fLibraryPath := IncludeTrailingPathDelimiter(orhome)+'bin'+PathDelim+libname;
         fHandle := SafeLoadLibrary(fLibraryPath);
       end;
     end;
   end;
   if fHandle=0 then begin
-    fLibraryPath := ExeVersion.ProgramFilePath+'OracleInstantClient\oci.dll';
+    fLibraryPath := ExeVersion.ProgramFilePath+'OracleInstantClient'+PathDelim+libname;
     fHandle := SafeLoadLibrary(fLibraryPath);
   end;
   if fHandle=0 then
-    raise ESQLDBOracle.Create('Unable to find Oracle Client Interface (oci.dll)');
+    raise ESQLDBOracle.CreateFmt('Unable to find Oracle Client Interface (%s)',[libname]);
   P := @@ClientVersion;
   for i := 0 to High(OCI_ENTRIES) do begin
     P^ := GetProcAddress(fHandle,OCI_ENTRIES[i]);
     if P^=nil then begin
       FreeLibrary(fHandle);
       fHandle := 0;
-      raise ESQLDBOracle.CreateFmt('Invalid oci.dll: missing %s',[OCI_ENTRIES[i]]);
+      raise ESQLDBOracle.CreateFmt('Invalid %s: missing %s',[libname,OCI_ENTRIES[i]]);
     end;
     inc(P);
   end;

I would be happy if my changes were added to the source repository.

ab · 2018-01-07 18:59:49

Please check https://synopse.info/fossil/info/6ca1d6330d

IL_S · 2018-01-08 11:28:25

ab wrote:

Please check https://synopse.info/fossil/info/6ca1d6330d

It works for me.
thx!

A few words about possible problems:
1. Use only oci_11.2.0.x or later!
2. Use SynDBOracleOCIpath to specify the path where the oracle client is located
3. Required files for linux oracle client:

libclntsh.so  libclntsh.so.11.1  libnnz11.so  libocci.so  libocci.so.11.1  libociei.so

libclntsh.so and libocci.so is symlinks

Last edited by IL_S (2018-01-08 11:31:01)

mpv · 2018-03-23 09:59:45

Installing Oracle instant client on Ubuntu:
1) Oracle does not provide a deb package
2) Package converted form rpm to deb using alien not setup itself properly.

The simplest way to setup instant client on Ubuntu is to download a zip version instantclient-basiclite-linux.x64-12.2.0.1.0.zip and setup it manually. Light version is enough for mORMot even if you need international support, since SynDBOracle work perfect with databases in UTF8 collation.

Setup:
Download instant client and execute under sudo

unzip instantclient-basiclite-linux.x64-12.2.0.1.0.zip
mv instantclient_12_2 /usr/lib
cd /usr/lib/instantclient_12_2
ln -s libclntsh.so.12.1 libclntsh.so
pwd > /etc/ld.so.conf.d/oracle.conf
ldconfig

Check all required libs is exists and visible by linker:

ldd libclntsh.so

In my case libaio not installed, so I install it manually

sudo apt install libaio

You do not need to set SynDBOracleOCIpath to any folder. Just keep it empty.

This is only working way I found during lo-o-ong research

Last edited by mpv (2018-03-23 10:12:42)

mpv · 2019-01-04 19:46:42

I need a help with SynDBOracle (FPC x64 Windows and Linux).

6 month ago we found a AV during Oracle connection creation (usually on call to OCIEnvNlsCreate) from inside a thread. For a long time it's appears only when I compile my program with -O2 optimization level, so I think this is a compiler bug. But after some modification in my program (not related to Oracle at all) i started getting AV even with -O1.

I tried everything I know to understand the reason. Comparison of headers, code analysis, including dynamic analyzers and even dances with a tambourine - all without success.

Over the past weekend I managed to create a minimal program to reproduce the error - here is a gist.
100% AV inside OCIEnvNlsCreate if compiled with -O1 and executed from inside Lazarus IDE on Linux (this minimal program works on windows, and on Linux outside IDE. But my real app got AV on Windows and on Linux)

Seams SynDBOracle write outside a memory we allocate for it (may be some C structures is incorrect in SynDBOracle). In gist above in case I change stack allocation a little (by comment out a local variable on line 35) AV is disappear.

I would be very grateful for any ideas on how to fix it!!

mpv · 2019-01-07 15:31:58

New idea: most likely AV appears because OCI intercept a OS level signals.
And this cause conflict with FPC signals interceptors.
In app above AV occurs in case app is executed from inside Lazarus IDE because Lazarus implicitly intercept signals (at last set SIGPIPE to be ignored)
In case of real apps some signals are implicitly intercepted by FPC (SIGILL,SIGBUS,SIGFPE,SIGSEGV), some signals are intercepted by explicit call to fpSigaction (as in SynDaemonIntercept) for Linux or SetConsoleCtrlHandler for Windows.
Still do not understand how to fix....

IL_S · 2019-03-12 08:55:06

mpv
you fix problem?

useful thing: change the version of oci to 11.2.x

Last edited by IL_S (2019-03-12 09:00:39)

mpv · 2019-03-12 21:42:44

No, still not fixed. I try with oci 18 - the same problem

cybexr · 2019-03-16 09:02:19

Hi mpv,

Thank you for your help with the linux problems I've posted. And I've met some thread sync problems with SynDBOracle@Linux too, maybe some transaction-deadlock, still trying to find out the root cause.

and Did you tried with the synDBODBC , will this one be more stable?

mpv · 2019-03-16 14:23:29

@IL_S - oracle client 11.2 work under linux. But under windows - AV
@cyberx - we use SynDBOracle for many years without problems (except of AV with -O2 optimization under FPC). synDBODBC we use only to access SQL Server from Linux, don't tried with Oracle.

mpv · 2019-03-16 14:26:33

I wrote a super small example to reproduce AV

program SynDBOarAV2;
{$I Synopse.inc}
uses
  {.$I SynDprUses.inc} // with SynDprUses AV exists
  // without cthreads or with cthreads but in case all writeln is commented it's work
  cthreads,
  SynDBOracle;
var
  props: TSQLDBOracleConnectionProperties;
begin
  props := TSQLDBOracleConnectionProperties.Create('dont used', '', '', '');
  writeln('before InitEnv');
  // for oracle client 11.2 no AV
  // for 12 and UP - AV in case compiled with -O2 optimization
  props.InitEnv();
  writeln('after InitEnv');
end.

InitEvs should be added to TSQLDBOracleConnectionProperties:

procedure TSQLDBOracleConnectionProperties.InitEnv();
var fEnv: pointer;
begin
  OCI.EnvNlsCreate(fEnv,EnvironmentInitializationMode,
    nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);
end;

mpv · 2020-04-01 13:32:27

Unfortunately I got AV on OCIEnvNlsCreate in my app again after migration to latest mORMot + huge refactoring of my code. But now I can't solve it by changing optimization level or adding a fake variables

The sample above is still actual. After 2 days fight I found:
1) AV does not depends on FPC compiler version (tried both 3.2 & 3.3.1), mORMot version, instantclinet version (tried with 12.2.0.1 & 19.6.0.0)
2) AV does not depends on signal interceptors added by OCI (I disable OCI signals interception signals by adding

DIAG_ADR_ENABLED=OFF
DIAG_SIGHANDLER_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE

to sqlnet.ora
3) AV present on any FPC optimization level and depends on what program did before
4) If I adds a fake stack variable AV MAY disappear (can be added either in TSQLDBOracleConnection.Connect or in test function as below):

procedure TSQLDBOracleConnectionProperties.InitEnv();
var fEnv: pointer;
     fake_stack_variable: array[0..988] of byte;
begin
  OCI.EnvNlsCreate(fEnv,EnvironmentInitializationMode,
    nil,nil,nil,nil,0,nil,OCI_UTF8,OCI_UTF8);
end;

but in real app it's depends of what program did before

5) Loading OCI library before any call, calling OCInitialize etc. etc. does not helps
6) in case of AV strace always show the same problem - OCI tries to allocate a huge amount of memory - 140045998678016 (or 140290811813888 or another huge number)

.... OCI do some staff 
access("/usr/lib/instantclient_19_6/network/admin/tnsnav.ora", F_OK) 
... here is signal initialization if not turned off
mmap(NULL, 140045998678016, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = -1 ENOMEM (Cannot allocate memory)

In case of success call (without AV because, for example line writeln('before InitEnv'); in sample above is commented or fake stack var is adedd) the strace flow is

.... OCI do some staff 
access("/usr/lib/instantclient_19_6/network/admin/tnsnav.ora", F_OK) 
... here is signal initialization if not turned off
openat(AT_FDCWD, "/usr/lib64/libnuma.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
openat(AT_FDCWD, "/usr/lib64/libnuma.so.1", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
prlimit64(0, RLIMIT_STACK, NULL, {rlim_cur=8192*1024, rlim_max=RLIM64_INFINITY}) = 0

OCI do not ask for memory (because it already allocated by fpc?)

I even add a /usr/lib64/libnuma (on Ubuntu oracle seek for it in wrong path) by create a link

sudo ln -s /usr/lib/x86_64-linux-gnu/libnuma.so.1 /usr/lib64

But in case of AV program terminates before attempt to load it

The only good news is what Oracle now allow to download instantclinet without registration

@ab - is it possible what something in SynCommons broke a process memory (may be write some bytes ahead of memory available to fpc etc.) ?

ab · 2020-04-01 15:37:16

Did you try to run OCI.EnvNlsCreate() from a single thread?
Or with EnvironmentInitializationMode=OCI_THREADED only?
On another computer?
With another library - e.g. oracleconnection.pp from fcl-db?

I just saw that oracleconnection.pp use the following:

  fUserMem := nil;
  if OCIEnvNlsCreate(FOciEnvironment,OCI_DEFAULT,nil,nil,nil,nil,0,FUserMem,CharSetId,CharSetId) <> OCI_SUCCESS then
      DatabaseError(SErrEnvCreateFailed,self);

So perhaps the usrmempp parameter should be not nil.
But FireDAC and Zeos let usrmempp=nil...

Please try https://synopse.info/fossil/info/b5b2ed228f

mpv · 2020-04-01 18:01:00

Yes, I
- tried with single thread (simplest single thread program is it this post: https://synopse.info/forum/viewtopic.ph … 28#p29228)
- tried to change EnvironmentInitializationMode in different ways
- tried on another computer (and even on Windows)

As you advice I tried with pointers ( only dummyusermem and abcd vars)

function TSQLDBOracleConnectionProperties.InitEnv(): integer;
var
  fEnv: pointer;
  dummyusermem, a,b,c,d: pointer;
begin
  fEnv := nil;
  dummyusermem := nil; a := nil; b := nil; c := nil; d := nil;
  Result := OCI.EnvNlsCreate(fEnv,EnvironmentInitializationMode,
    a,b,c,d,0,@dummyusermem,OCI_UTF8,OCI_UTF8);
end;

Still AV

Will try with oracleconnection.pp

Last edited by mpv (2020-04-01 18:01:43)

mpv · 2020-04-01 18:07:46

Small step forward - after using @dummyusermem problem was reproduced under valgrind (before this under valgrind all works)!

P.S.P.S.
To reproduce a problem Oracle is not required - only instantclinet and this 20 lines program https://synopse.info/forum/viewtopic.ph … 228#p29228

Last edited by mpv (2020-04-01 18:28:06)

mpv · 2020-04-01 19:34:08

I cried. I have been looking for this error for so long. I'm going to drink #299

ab · 2020-04-01 19:43:49

santé!
:beer:

mpv · 2020-04-02 10:21:07

Another small fix (with dummyusermem I got AV) #300

- dummyusermem is removed
- added EnvNlsCreate result check

This edition pass my regression tests for x64 Oracle client v11.2 v12.2 and v19.6 on Windows and Linux platforms

ab · 2020-04-02 12:12:11

Great!

This is just awesome what you did with all this testing and debugging!

EgonHugeist · 2020-04-04 17:18:34

  Result := OCI.EnvNlsCreate(fEnv,EnvironmentInitializationMode,
    a,b,c,d,0,@dummyusermem,OCI_UTF8,OCI_UTF8);

Just a hint from me: characterset "utf-8" of Oracle and MySQL are just CESU-8 -> 3-Byte encoding. The name is wrong and did expire 2003. Zeos silently hooks it to AL32UTF8 which is real UTF8. You shold not use this charset, Arnaud.
See: https://community.oracle.com/thread/351 … 0&tstart=0

Last edited by EgonHugeist (2020-04-04 17:19:34)

ab · 2020-04-04 23:11:05

@Michael
You are right!
Please check https://synopse.info/fossil/info/5e7dc66fa1

mORMot Open Source

#1 2018-01-07 14:13:47

Fix SynDBOracle for work under linux

#2 2018-01-07 18:59:49

Re: Fix SynDBOracle for work under linux

#3 2018-01-08 11:28:25

Re: Fix SynDBOracle for work under linux

#4 2018-03-23 09:59:45

Re: Fix SynDBOracle for work under linux

#5 2019-01-04 19:46:42

Re: Fix SynDBOracle for work under linux

#6 2019-01-07 15:31:58

Re: Fix SynDBOracle for work under linux

#7 2019-03-12 08:55:06

Re: Fix SynDBOracle for work under linux

#8 2019-03-12 21:42:44

Re: Fix SynDBOracle for work under linux

#9 2019-03-16 09:02:19

Re: Fix SynDBOracle for work under linux

#10 2019-03-16 14:23:29

Re: Fix SynDBOracle for work under linux

#11 2019-03-16 14:26:33

Re: Fix SynDBOracle for work under linux

#12 2020-04-01 13:32:27

Re: Fix SynDBOracle for work under linux

#13 2020-04-01 15:37:16

Re: Fix SynDBOracle for work under linux

#14 2020-04-01 18:01:00

Re: Fix SynDBOracle for work under linux

#15 2020-04-01 18:07:46

Re: Fix SynDBOracle for work under linux

#16 2020-04-01 19:34:08

Re: Fix SynDBOracle for work under linux

#17 2020-04-01 19:43:49

Re: Fix SynDBOracle for work under linux

#18 2020-04-02 10:21:07

Re: Fix SynDBOracle for work under linux

#19 2020-04-02 12:12:11

Re: Fix SynDBOracle for work under linux

#20 2020-04-04 17:18:34

Re: Fix SynDBOracle for work under linux

#21 2020-04-04 23:11:05

Re: Fix SynDBOracle for work under linux

Board footer