1

I am migrating between servers and with the same code running the same query against one of my databases I get different results if the result data contains a unicode character.

I'm moving from:

  • Red Hat 4.4.7-23
  • PHP 5.6.32
  • Apache/2.2.15
  • OCI8 Version => 2.0.8
  • /opt/oracle/product/112020_cl_64 (client)

My new environment has:

  • Red Hat 4.8.5-39.0.1 (OL7)
  • PHP 5.6.36 (built manually)
  • Apache/2.4.6 (built manually)
  • OCI8 Version => 2.0.12
  • /usr/lib/oracle/12.2/client64 (client)

The database is:

  • read only
  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  • With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
  • NLS_CHARACTERSET = WE8ISO8859P15

The query returns a column containing a \00BF character (i.e. asciistr(column) contains \00BF).

select description raw, asciistr(description) tidied from aradmin.CHG_INFRASTRUCTURE_CHANGE where asciistr(description) != description and rownum < 2;

Raw: 08:00 ▒ 17:00
Tidied: 08:00 \00BF 17:00

Using sqlplus I get the same result on both servers.

Using php/oci my old server automagically replaces the '▒' with a '?' which a subsequent json_encode handles without issues.

My new server is currently returning the raw value, which is breaking the subsequent json_encode.

How do I get the new server to do automatic replacement too?

I'd like to avoid changing individual queries but they all use a common DatabaseConnection class which I can change, if required.

New Apache build:

sudo ./configure --enable-so --prefix=/usr/local/apache2.4 --with-apr-util=/usr/local/apr/bin/apu-1-config

New php build:

sudo ./configure --prefix=/usr/local/php5.6 --enable-bcmath --with-bz2=/usr/bin/bzip2 --enable-calendar --with-curl --enable-dba=shared --with-cdb --enable-exif --with-gd --with-gettext --with-gmp --with-ldap --with-mhash --with-openssl --with-readline --with-snmp --with-xmlrpc --with-xsl --with-zlib --enable-sysvsem --enable-sysvshm --with-libdir=lib64 --enable-dtrace --with-config-file-scan-dir=/usr/local/php5.6/php.d --with-config-file-path=/usr/local/php5.6 --with-unixODBC=/usr --enable-soap --with-jpeg-dir=/usr/lib --with-xpm-dir=/usr/lib64 --with-apxs2=/usr/local/apache2.4/bin/apxs --with-pdo-mysql

ChrisG
  • 63
  • 8
  • Did you set NLS_LANG env variable? – Sayan Malakshinov Jul 07 '20 at 17:27
  • Or did you specify "character_set" in oci_connect? – Sayan Malakshinov Jul 07 '20 at 17:28
  • [This got closed as a dup of a MySQL question, which isn't helpful since the Oracle DB configuration is not the same.] As noted, in other comments, set the character_set when connecting. Alternatively set NLS_LANG in the appropriate way for your webserver. On OL7 this is likely to be in `/etc/sysconfig/httpd`. Add a line like `NLS_LANG=.WE8ISO8859P15` (with leading full stop) or probably better is to use `NLS_LANG=.AL32UTF8`. – Christopher Jones Jul 07 '20 at 22:46
  • If you're reading this please refer also to this one: https://stackoverflow.com/questions/62794026/apache-unicode-handling - at the time of writing both are unanswered from my point of view but the other one hasn't been closed as a duplicate – ChrisG Aug 05 '20 at 10:11
  • Answer: The difference was that php was complied without mbstring on the new server, when I built php 7.4 with --enable-mbstring I got the desired behavior – ChrisG Aug 12 '20 at 07:55

0 Answers0