Friday, February 24, 2017

SQL Relay 1.0.1 Release Announcement

Version 1.0.1 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.

This release features lots of small improvements, mainly for issues discovered during attempts to make it compliant with Redhat's packaging guidelines, and for a set of issues reported by users.

Thread Fix

This is fairly important.

There was a long-standing bug in the sqlr-listener process that caused multiple threads to access the same variable, without coordination. The symptom was that the sqlr-listener process might crash, randomly, every few days. The variable was a buffer used to build log/notification messages, so the error could only occur if logging or notifications were configured, but it could happen if there were logger or notifications tags in the config file, even if the modules themselves were disabled. The workaround was to comment out the entire logger and notification tag groups, or to configure the sqlr-listener to be multi-process instead of multi-thread (using sessionhandler="process").

None of that is necessary now though. The bug is fixed. Local variables are used to build the messages in the parts of the sqlr-listener that can be run by multiple threads.

sqlrsh Fixes

You can now hit ctrl-D to abort sqlrsh without generating a segfault. Also, errors are now printed to stderr and if an error occurs (in non-interactive mode) then sqlrsh exits with status 1 instead of 0.


1.0.0 featured an ODBC fix which itself created a new bug. Between the two, the first line of the result set could be duplicated or skipped. Both bugs should be fixed now.

PDO on 32-bit Systems

The PHP PDO driver compiles correctly for PHP 7 on 32-bit systems now.

Ruby Updates

Ruby semi-recently consolidated Fixnum and Bignum classes into the Integer class. The Ruby API has been updated to support this change.

Router Sequence Fix

There was a bug that made it necessary to start all of the instances that a router instance would route to prior to starting the router itself. Eg. if you had 2 instances: routermaster and routerslave, and a third instance: router, then routermaster and routerslave had to be started prior to router. This wouldn't always happen if the instances were configured to start at boot though. Now, they can be started in any order.

Normalize Translation

Fixed a bug in the "normalize" query translation that caused it to remove spaces before unary ! and - when they followed a keyword. For example, it will now return "select !a,!b from mytable" instead of "select!a,!b from mytable".

Upates to the Drop-in Replacement Libraries

As recommended by rpmlint, the .so links aren't installed for these libraries any more. Now, the major version must be included in the LD_PRELOAD command. Eg: LD_PRELOAD=/usr/local/firstworks/lib/

The Drop-in Replacement Library for MySQL now pays attention to the SQLR_MYSQL_MAP_DATE_TO_DATETIME environment variable. If it's set to "yes" then DATE types are mapped to DATETIME. This is useful if you're mapping a MySQL app to an Oracle database, have NLS_DATE_FORMAT set to include the time, and your app truncates the time if the type of the column is DATE instead of DATETIME.

configure Script Options

The --disable-wall and --disable-werror options have been added to the configure script to work around quirks on systems who's header files generate warnings or particular errors.

The --with-abs-max-connections option has been added to set the absolute maximum number of connections that can be configured at compile time. It defaults to 4096, up from 512 in previous releases.

The --with-fortify option has been added to enable -O -D_FORTIFY_SOURCE=2 -fstack-protector-strong compiler options.

The --enable-oracle-at-runtime (and similar options for sap, db2, and informix) have been added to enable runtime loading of the database libraries rather than linking them in at compile time.

Error Messages

Various error messages have been improved to suggest solutions.

Erlang Reference

An API reference exists for the Erlang API now.

Cache Manager

The mysterious sqlr-cachemanager process isn't enabled by default any more. This process manages the client-side result set cache, only needs to be run on SQL Relay clients, and only if an app caches result sets. As such, it is installed when building from source, but isn't enabled by default. Also, in the RPM distribution it has been split out into its own RPM. The docs have also been updated to explain when to run the process.

"run", "log", and "cache" directories and file names

This is more of an internal change, but...

In the past there were various directories under .../var/sqlrelay/tmp that contained files used at runtime by the various instances of SQL Relay. These have been consolidated into .../var/run/sqlrelay. The "log" directory has been changed from .../var/sqlrelay/log to .../var/log/sqlrelay. The "cache" directory has been changed from .../var/sqlrelay/cache to .../var/cache/sqlrelay. These changes were motivated by Redhat packaging guidelines, but I think they have roots in the Linux Standard Base.

Sockets created by the sqlr-listener process to communicate with the sqlr-connection processes now have .sock extensions, and 700 permissions rather than erroneous 711 permissions). Files used by ftok() to get IPC ids now have .ipc extensions. "Up-down" files used to indicate whether the database is up now have .up extensions. The sockseq file is no longer used. Instead, the file names of sockets used to resume suspended sessions are based on the process id of the sqlr-connection process.

Full Changelog follows:

  • updated sqlr-bench to create "db"-bench.png
  • added edoc-generated API docs for the Erlang API
  • updated some module-load error verbage
  • added initial support for loading Oracle, SAP/Sybase, DB2, and Informix libraries on-demand at runtime
  • overhauled spec file
  • fixed PDO driver for PHP 7 on 32-bit platforms
  • added permissions to all open() with O_CREAT calls
  • added --with-fortify configure option
  • fixed a segfault on ctrl-D in sqlrsh fixed uncoordinated access to shared debugstr among listener threads by using local instances instead
  • added ping-loop to router connection module's logIn() method to manage cases where routed-to instances are started after the router
  • moved .../var/sqlrelay/tmp/*/* to .../var/run/sqlrelay
  • added .sock extension to sockets, .ipc extension to ipc key files, .pid extension to pid files, and .up extension to files which indicate that the db is up
  • changed mode of listener-connection sockets to 700 from 711
  • moved .../var/sqlrelay/log to .../var/log/sqlrelay moved .../var/sqlrelay/cache to .../var/cache/sqlrelay
  • libmysql* and are no longer installed
  • replaced sockseq-generated unix socket with pid-based unix socket
  • updated ruby api to support unification of Fixnum and Bignum classes into Integer class
  • added --with-abs-max-connections configure script parameter
  • increased default absolute max connections to 4096 (from 512)
  • updated "can't open ... file" errors to mention user/group
  • added SQLR_MYSQL_MAP_DATE_TO_DATETIME option to mysql drop-in replacement library
  • fixed normalize translation not to remove spaces before unary - and !
  • updated sqlrsh to set error code correctly and write errors to stderr
  • sqlr-cachemanager is no longer enabled at boot by default
  • fixed another subtle bug in the ODBC driver that caused the first row to be duplicated if columns were not bound
  • added --disable-wall and --disable-werror configure options