Re: failed: ERROR OCIEnvNlsCreate. Check (everything)

Please keep dbi-users on Cc. The NLS_LANG issue posted by Sven Miller 
looks relevant.

On 08.04.2010 22:30, Perl Diety wrote:
>> To give you some more detail about what works and what does- we've run > Perl CGI on this server for years. We access a MYSQL database every > day, and all of that works 100% fine. We access library functions, > system libs, etc again with no issues. 
So, DBI is ok, and most of Apache is also ok.
> The lone problem is ORACLE 10 access. So I think this is a good > indicator that this is specifically related to Oracle ENV vars, or > something to do with the DBD or DBI.
Can't be DBI, or else MySQL access would also fail.

Most likely are DBD::Oracle (especially the compiled part), Oracle 
client libraries, environment variables used by Oracle, and filesystem 
permission issues.
>> Our system admn did update the DBI and DBD to the latest version
Shouldn't hurt -- but HOW did he update? RPM install or compile on that 
machine?

RPMs are precompiled, and thus need a very specific version of the 
Oracle client libraries -- those they were compiled against.

A compile on the machine should link against the Oracle client libraries 
that are installed and you want to use, so there should be no problem 
unless the machine is really f....ed up, full of different Oracle client 
versions, Perl versions, and DBI versions. Sure, you can install 
multiple versions of Oracle, Perl and DBI on the same machine, but in 
that case, you must know very exactly what you are doing, and it is very 
likely that you end with a broken installation.

> and rebuilt the server.
Which server? Oracle? In that case, you need to re-compile DBD::Oracle 
after updating Oracle.

>> Also, the script accesses Oracle fine from a command line. Which sort > of suggests that the DBD is OK?
Right. If the simple-and-stupid script from my last mail works on the 
command line, it must be an issue with filesystem permissions or 
environment variables.
>> As far as your step-by-step, I did that, and when I add in the CONNECT > is when this error is thrown and it dies. I tried to get more info > like fatalsToBrowser, inspecting $! and ORa Error codes etc.
Comment out all code added by the connect step and the following steps, 
and compare the values of $<, $>, $(, $), and %ENV for CGI mode and 
command line mode. With Data::Dumper, the Useqq and Sortkeys options are 
really useful.

$< and $> are user IDs, both values should be equal for a single run, 
but they should differ between CGI and command line mode. Typically, 
they are less than 100 (often 80) for CGI mode, and at least 1000 (100 
on old systems) for command line mode.

$( and $) are space-separated lists of group IDs, the first value is the 
primary group ID, the following values are additional group IDs. Again, 
both values should be equal for a single run, and they are very likely 
to differ between CGI and command line mode. Especially the first number 
should be different.

Look up the IDs in the output of getent group (or the file /etc/group), 
and find out which groups are not available in CGI mode. Compare with 
the owning group(s) of the Oracle client library files and the Oracle 
configuration files, especially those files that aren't world readable 
(i.e. ls -l output showing -rwxr-x--- or -rw-r----- instead of 
-rwxr-xr-x or -rw-r--r--). If the non-world-readable files are owned by 
a group available in command line mode, but not in CGI mode, you have a 
permission problem. Either put the user running Apache into the relevant 
group(s), or change the filesystem permissions (preferably though the 
Oracle provided unharden script).

Compare the entire %ENV between CGI and command line mode. All variables 
available in command line mode, but not set or set to a different value 
in CGI mode, are suspect. Some obvious ones aren't relevant, like BASH 
and BASH_*, HIST*, PS1 to PS4, PWD, OLDPWD, SHELL, DISPLAY. 
<http://www.orafaq.com/faq/managing_unix_environment_variables> lists 
some variables that may be relevant. Try adding PassEnv or SetEnv for 
those variables to the Apache configuration. As a quick hack, you could 
instead set the environment variables in a BEGIN block *BEFORE* loading 
the Oracle client libraries, i.e.:

#!/usr/bin/perl -Tw

use strict;
BEGIN {
    # Hack to test Oracle setup
    # Must execute BEFORE Oracle client libraries are loaded, i.e. 
before "use DBI", before any implicit or explicit "use DBD::Oracle".
    $ENV{'ORACLE_HOME'}='/some/where';
    $ENV{'NLS_LANG'}='AL32UTF8';
}
use DBI;
# ...
my $dbh=DBI->connect('dbi:Oracle:....


(I really don't know if that trick still works, but with Oracle 8 and 9, 
it worked fine, and I don't see why it shouldn't work with 10 or 11.)

Alexander

>>> Im off to the doctor have a great evening A!>>> MP>>>> On Thu, Apr 8, 2010 at 3:06 PM, Alexander Foken <alex...@foken.de > <mailto:alex...@foken.de>> wrote:>>     On 08.04.2010 19:09, Perl Diety wrote:>>         I wrote the script to dump %ENV (in a MUCH more Perlish way>         than the apache version!) but yes, you're correct; I filtered>         out a lot of unrelated stuff to make the list more friendly.>>     ok>>>         I don't think a linux apache server cares about *PATH* does>         it? Not sure.>>     Apache itself shouldn't care, but the programs invoked by Apache>     do. If $ENV{PATH} is not set, programs either fall back to a (more>     or less) sane default, like /bin:/usr/bin, or simply assume it to>     be empty. In the first case, only external programs in /bin and>     /usr/bin are found, unless the absolute path is passed to the exec>     library functions (execvp, execlp, ...). In the latter case, no>     external program is found unless its absolute path is passed to>     the exec functions. That can make a huge difference for many>     programs that don't contain hardcoded paths.>>>         The message seemed to imply PATH was only used for WINDOWS>         servers?>>     On Unix, $ENV{PATH} is only used to find executables. On Windows,>     it is also used to find DLLs required by executables. So yes, some>     tricks with $ENV{PATH} are only required on Windows, to make sure>     all DLLs are found. On Unix, you would use $ENV{LD_LIBRARY_PATH}>     or something like that, or entries in /etc/ld.so.conf.>>>>         I'm *pretty sure* our ENV VARS for the LIBs and server>         correctly- because before we set them we were getting a>         DIFFERENT error (cant load Oracle.so). Now we're past that>         error onto the next one. So I think the ORACLE_HOME and LD>         paths are OK now.>>     Hmmm, try beginning with a simple and stupid script, then start to>     extend it.>>     I would start with something like>>     #!/usr/bin/perl -w>     use strict;>     print "Content-Type: text/plain\r\n\r\nHello World\n";>>     Then I would add "use CGI::Carp qw(fatalsToBrowser);", and invoke>     the script again to see if it has survived the script.>>     Next, modify print to dump the entire %ENV after the "Hello World">     line, as readable and as precise as possible. Data::Dumper could>     help. Try again.>>     Next, add "use DBI;" and try again.>>     Then, add "my>     $dbh=DBI->connect("dbi:Oracle:whereever","scott","tiger",{>     RaiseError => 1 });" before "print", and try again.>>     Looking at your last postings, this step should fail. However, the>     next step would be to add a simple select-and-fetch, like this:>>     my $sth=$dbh->prepare("select 42 as result from dual");>     $sth->execute();>     my $data=$sth->fetchrow_hashref();>     $sth->finish();>     print Dumper($data);>>     If you get to this point, your problem should be gone. ;-)>>>>         Its a preplexing problem with LOTS and LOTS of difffernt>         proposed fixes in the forums. I suspect that's because there>         are so many possible problems.>>>     Yes. I think Oracle has grown too much.>>>         As a programmer, I'm not dazzled by the error message. I would>         never present my users with a message like "it might be A, or>         possibly B, or something to do with C, or possibly SOMETHING>         ELSE"..>>     Right, I also don't like message like "oops, something went>     wrong". At least, I would expect some extension like "... while>     attempting to read the configuration file". Perl makes>     do_something() or die "Couldn't ...: $!" very easy.>>>         Instead I would CHECK A, report an A error if found,. Check B,>         report a B error if found. And so on. Its unreasonable to>         expect a user to react to a message that basically says IT>         COULD BE ANYTHING!>>         In this case, some users say check the locale, language>         settings, others say check LIB PATH.>>>     DBI connect(...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME>     (Linux)>     env var or PATH (Windows) and or NLS settings, permissions, etc. at>     /cgi-bin/oratest.cgi line 32>>>     Something in your %ENV is so broken that Oracle becomes confused.>>     What happens when you try to run the CGI script (preferably the>     one I just "invented")  from your user account, simply by invoking>     it from a shell prompt? (When the CGI module is used, some>     additional tricks are needed, like passing Query parameters as>     command line parameters or $ENV{QUERY_STRING}.)>>     When it runs without trouble, compare %ENV of the command line and>     %ENV from the script running as CGI, and watch for differences.>     Also look at the user and group IDs ($<, $>, $(, $) ).>>     One "new" feature of Apache is the fact that it does not pass its>     original environment to its child processes, like it used to do in>     ancient versions. The newer apache versions only create a minimal>     environment, to which PassEnv and SetEnv add new environment>     variables. So, "PassEnv ORACLE_HOME" is often needed.>>>         Others say its a DBI or DBD / Oracle version mismatch.>>     Did you compile DBI and DBD::Oracle on that machine or did you use>     some precompiled stuff (RPMs, some tar copied over from a>     different machine)?>>     If you compiled it yourself and DID NOT update Oracle or Perl>     later, everything should be fine.>>     Mismatching DBI and DBD::Oracle should cause linker errors, and>     the same is also true for a mismatch between the Oracle libraries>     and DBD::Oracle.>>         SOme say its an Oracle 10 security issue and I need to run an>         Oracle script to un-harden it?>>     From a very quick google search for OCIEnvNlsCreate, it seems that>     the Oracle client library has trouble finding its configuration>     files, like TNSNAMES.ORA, or some libraries. And that happens>     because it does not know ORACLE_HOME, or because it does not have>     sufficient privileges (file permissions -rwxr-x--- and other ugly>     stuff, this is the "hardening").>>     Alexander>>>         I've encountered very few times in my 20 years as a programmer>         where ONE error could be caused by so many possibilities.>         After like 4 man-weeks, we're still fumbling with it.>>         Thanks!>         MP>>>>>         On Wed, Apr 7, 2010 at 4:53 PM, Alexander Foken>         <alex...@foken.de <mailto:alex...@foken.de>>         <mailto:alex...@foken.de <mailto:alex...@foken.de>>> wrote:>>            On 07.04.2010 10:45, Martin Evans wrote:>>                Perl Diety wrote:>                >                    ENV VARS>>                    DOCUMENT_ROOT = /var2/www/html>                    GATEWAY_INTERFACE = CGI/1.1>                    HTTP_ACCEPT = */*>                    HTTP_ACCEPT_ENCODING = gzip, deflate>                    HTTP_ACCEPT_LANGUAGE = en-us>                    HTTP_HOST = ournode.com <http://ournode.com>>         <http://ournode.com>>>                    HTTP_UA_CPU = x86>                    HTTP_USER_AGENT = Mozilla/4.0>                    LD_LIBRARY_PATH = /export/apps/oracle/product/10201/lib>                    LD_RUN_PATH = /export/apps/oracle/product/10201/lib>                    NLS_LANG = en_GB.UTF-8>                    ORACLE_BASE = /export/apps/oracle>                    ORACLE_HOME = /export/apps/oracle/product/10201>                    ORA_NLS10 = /export/apps/oracle/product/10201/nls/data/>                    REQUEST_METHOD = GET>                    REQUEST_URI = /cgi-bin/oratest.cgi>                    SERVER_PORT = 80>                    SERVER_PROTOCOL = HTTP/1.1>                    SERVER_SIGNATURE =>>                    Apache/2.0.52 (Red Hat) Server at ournode.com>         <http://ournode.com>>                    <http://ournode.com> Port 80>>>                    SERVER_SOFTWARE = Apache/2.0.52 (Red Hat)>                    DBI connect(...) failed: ERROR OCIEnvNlsCreate. Check>                    ORACLE_HOME (Linux)>                    env var or PATH (Windows) and or NLS settings,>                    permissions, etc. at>                    /cgi-bin/oratest.cgi line 32>                      >>            This looks roughly like a CGI enviromnent, and the Oracle>            Variables seem to be set. But lots of variables seem to be>            missing, like PATH and some variabes starting with HTTP_,>         SERVER_,>            SCRIPT_, and REQUEST_. And the LD_xxx variables shouldn't>         be there.>>            If this is the *complete* environment provided to the CGI,>            something is *very* wrong with the Apache.>>            If not, post a complete dump of %ENV in CGI context, e.g.>         with the>            printenv script that came with Apache:>>            #!/usr/local/bin/perl>            ##>            ##  printenv -- demo CGI program which just prints its>         environment>            ##>>            print "Content-type: text/plain; charset=iso-8859-1\n\n";>            foreach $var (sort(keys(%ENV))) {>              $val = $ENV{$var};>              $val =~ s|\n|\\n|g;>              $val =~ s|"|\\"|g;>              print "${var}=\"${val}\"\n";>>            }>>                      >>                Did you really set AND export ORACLE_HOME or>         LD_LIBRARY_PATH>                such that>                Apache children see them? I can't remember what the>         syntax in the>                httpd.conf file is now (SetEnv perhaps) as I no longer use>                Apache but>                you used to have to explicitly tell Apache which env>         vars to>                allow.>>                Martin>                >            Two ways:>>            1. PassEnv VariableName [...] -- see>            <http://httpd.apache.org/docs/2.2/mod/mod_env.html#passenv>>            2. SetEnv VariableName Value -- see>            <http://httpd.apache.org/docs/2.2/mod/mod_env.html#setenv>>>            I prefer PassEnv over SetEnv when the variables already>         exist in>            the environment of the process invoking the Apache server. That>            way, I don't have to change the Apache configuration when an>            environment variable changes. I use SetEnv only to set>         additional>            variables that must not appear in the environment of the>         process>            invoking the Apache server.>>            See also>          >          <http://alexander-foken.de/Censored%20copy%20of%20Oracle%20Troubleshooter%20HOWTO.html>>>            Alexander>>            --     Alexander Foken>            mailto:alex...@foken.de <mailto:alex...@foken.de>>         <mailto:alex...@foken.de <mailto:alex...@foken.de>>>>             http://www.foken.de/alexander/>>>>>     -- >     Alexander Foken>     mailto:alex...@foken.de <mailto:alex...@foken.de>>      http://www.foken.de/alexander/>>


-- 
Alexander Foken
mailto:alex...@foken.de  http://www.foken.de/alexander/