OCIError: ORA-04088: error during execution of trigger 'SYS.USER_LOGIN_MONITORING'

[aman29april]

I am running a cron job using k8, which connects with the oracle database. When I am manually running the jobs, it works fine. But if its runs automatically through cron, It's giving below error

Warning: NLS_LANG is not set. fallback to US7ASCII. rails aborted! OCIError: ORA-04088: error during execution of trigger 'SYS.USER_LOGIN_MONITORING' ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 13 ISToci8.c:603:in oci8lib_250.so

This was working fine for me and recently started happening.

[kubo]

Could you check the environment variables in cron and console? I guess that some environment variables are missing in cron.

In crontab:

minute hour * * *  env | sort > /tmp/env-on-cron.log

(change above minute and hour to the time a few minutes later than now.)

After /tmp/env-on-cron.log is created,

$ env | sort > env-in-console.log
$ diff -u env-in-console.log /tmp/env-on-cron.log 

[aman29april]

@kubo I am using Kubernetes to run jobs. I am not sure if I can see ENV variables by the method provided by you. I am printed ENV from rails code using ENV.to_h.to_yaml See if this helps

RBENV_VERSION: 2.5.1 NOKOGIRI_USE_SYSTEM_LIBRARIES: '1' DEFAULT_RUBY_VERSION: 2.3.7 COOKIE_REQUIRE_HTTPS: 'true' RBENV_ROOT: "/rbenv" PATH: "/rbenv/versions/2.5.1/lib/ruby/gems/2.5.0/bin:/rbenv/versions/2.5.1/bin:/rbenv/libexec:/rbenv/plugins/ruby-build/bin:/rbenv/shims:/rbenv/bin:/nodejs/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/oracle/12.2/client64/bin" SHLVL: '0' RAILS_ENV: production BUNDLER_VERSION: 1.17.3 BUNDLER_ORIG_BUNDLE_BIN_PATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_BUNDLE_GEMFILE: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_BUNDLER_ORIG_MANPATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_BUNDLER_VERSION: 1.16.1 BUNDLER_ORIG_GEM_HOME: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_GEM_PATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_MANPATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_PATH: "/rbenv/versions/2.5.1/bin:/rbenv/libexec:/rbenv/plugins/ruby-build/bin:/rbenv/shims:/rbenv/bin:/nodejs/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/oracle/12.2/client64/bin" BUNDLER_ORIG_RB_USER_INSTALL: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_RUBYLIB: "/rbenv/rbenv.d/exec/gem-rehash:" BUNDLER_ORIG_RUBYOPT: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLE_BIN_PATH: "/rbenv/versions/2.5.1/lib/ruby/gems/2.5.0/gems/bundler-1.17.3/exe/bundle" BUNDLE_GEMFILE: "/bbb/app/Gemfile" RUBYOPT: "-rbundler/setup" GEM_PATH: "/rbenv/versions/2.5.1/lib/ruby/gems/2.5.0:/root/.gem/ruby/2.5.0" GEM_HOME: "/rbenv/versions/2.5.1/lib/ruby/gems/2.5.0" CIRCLE_COMPARE_URL: http://...

[kubo]

@aman29april Could you post the environment variables on a console where your job works fine? I guess that some environment variables are missing in the Kubernetes environment but exist in the console.

Could you ensure that the Oracle client libraries in Kubernetes in the console are same by OCI8.oracle_client_version?

[kubo]

In general, this isn't a ruby-oci8 issue. The error was raised in the trigger SYS.USER_LOGIN_MONITORING. As far as I googled it, I cannot find such trigger. I guess that the trigger is created by you or a developer of your organization.

In general, it is almost impossible to resolve the issue without detailed information. For example, source code of the trigger, ruby code, data which kick the trigger, and so on. However you wrote that you could run the job manually and it failed in Kubernetes. So I thought that if the environment in Kubernetes was same with that where you ran it manually, it would works fine.

[aman29april]

@kubo

Here are ENV when the job runs fine. Also if there is such a trigger, the code works fine with a normal job. Also, I am using the same image for both kinds of jobs. In the job XML also, things are the same. Also, this was working fine for me earlier.

RBENV_VERSION: 2.5.1 NOKOGIRI_USE_SYSTEM_LIBRARIES: '1' DEFAULT_RUBY_VERSION: 2.3.7 COOKIE_REQUIRE_HTTPS: 'true' RBENV_HOOK_PATH: "/rbenv/rbenv.d:/usr/local/etc/rbenv.d:/etc/rbenv.d:/usr/lib/rbenv/hooks" SHLVL: '0' RAILS_ENV: production DEBIAN_FRONTEND: noninteractive RAILS_LOG_TO_STDOUT: 'true' BUNDLER_VERSION: 1.17.3 BUNDLER_ORIG_BUNDLE_BIN_PATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_BUNDLE_GEMFILE: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_BUNDLER_ORIG_MANPATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_BUNDLER_VERSION: 1.16.1 BUNDLER_ORIG_GEM_HOME: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_GEM_PATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_MANPATH: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_PATH: "/rbenv/versions/2.5.1/bin:/rbenv/libexec:/rbenv/plugins/ruby-build/bin:/rbenv/shims:/rbenv/bin:/nodejs/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/oracle/12.2/client64/bin" BUNDLER_ORIG_RB_USER_INSTALL: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLER_ORIG_RUBYLIB: "/rbenv/rbenv.d/exec/gem-rehash:" BUNDLER_ORIG_RUBYOPT: BUNDLER_ENVIRONMENT_PRESERVER_INTENTIONALLY_NIL BUNDLE_BIN_PATH: "/rbenv/versions/2.5.1/lib/ruby/gems/2.5.0/gems/bundler-1.17.3/exe/bundle" BUNDLE_GEMFILE: "/bbb/app/Gemfile" RUBYOPT: "-rbundler/setup" GEM_PATH: "/rbenv/versions/2.5.1/lib/ruby/gems/2.5.0:/root/.gem/ruby/2.5.0" GEM_HOME: "/rbenv/versions/2.5.1/lib/ruby/gems/2.5.0"

[kubo]

@aman29april Did you remove some environment variables? If so, you should mention it. The output you posted is too brief. Some removed environment variables may be important. Especially they are referenced by Oracle client libraries.

[aman29april]

@kubo I am not removing any ENV. Both jobs use the same ENV variables. I am accessing a shared file from both jobs to export ENV variables. So in both cases, the same ENV variables are created.

While posting ENV in the comments above, I removed a few ENV's as they had some tokens and other sensitive information.

Can you tell any specific ENV variable which may cause this issue, maybe I can set that ENV and test.

[kubo]

@aman29april Sorry, I never thought the environment variable PATH wasn't set. Without it, no processes start unless executable files are specified by full path names.

Can you tell any specific ENV variable which may cause this issue, maybe I can set that ENV and test.

I thought it might be related to NLS_LANG. When NLS_LANG isn't set, NLS character set is US7ASCII, whose maximum size of one character is one byte. When NLS character set is AL32UTF8, ruby-oci8 allocates four times longer string buffer because AL32UTF8's maximum size of one character is four bytes. At first I thought it may be related to the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small. However the error location is in the trigger. I don't think NLS_LANG affects the size of string buffer in triggers. So I cannot think of specific situations.

If NLS_LANG doesn't fix the error (I guess it doesn't), you need to debug the trigger.

[aman29april]

@kubo When I set NLS_LANG as AL32UTF8, I am getting the following error:

OCIError: ORA-12705: Cannot access NLS data files or invalid environment specified oci8.c:603:in oci8lib_250.so

To use AL32UTF8, do we need to do anything extra?

[kubo]

When I set NLS_LANG as AL32UTF8, I am getting the following error:

OCIError: ORA-12705: Cannot access NLS data files or invalid environment specified

You specified invalid environment.

In https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-86A29834-AE29-4BA5-8A78-E19C168B690A:

The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:

NLS_LANG = language_territory.charset

For example AMERICAN_AMERICA.AL32UTF8

[aman29april]

@kubo Thanks for the assistance. I checked with the DBA and they recently created a trigger that was causing the problem.