Monday, November 14, 2016

Announcing: SQL Relay Enterprise: MySQL Front-End Modules

SQL Relay Enterprise Modules provide advanced features not available in the standard SQL Relay distribution. MySQL Front-End Modules are now available.

MySQL Front-End Modules allow MySQL applications to use SQL Relay without modification and without a drop-in replacement library. Additional SQL Relay Enterprise Modules are coming soon.

MySQL Front-End Modules

Whether written using the native MySQL API, or a connector of some sort, MySQL apps communicate with the database using the MySQL client-server protocol.

Whether written using the native SQL Relay API, or a connector of some sort, SQL Relay apps generally communicate with SQL Relay using the SQL Relay client-server protocol.

However, the MySQL Front-End Modules enable SQL Relay to speak the MySQL client-server protocol. This allows MySQL apps to communicate directly with SQL Relay, rather than to a MySQL database, without modification, and without using a drop-in replacement library.

In this configuration, SQL Relay becomes a transparent proxy. MySQL apps aimed at SQL Relay still think that they're talking to a MySQL database, but in fact, are talking to SQL Relay.

Once the app is talking to SQL Relay, most of SQL Relay's features become available to the app, including Connection Pooling, Throttling, High Availability Features, Query Routing, Query Filtering, and Connection Schedules.

Since SQL Relay supports a variety of database backends, the app can also be redirected to any of these databases, instead of the MySQL database it was originally written to use.

Some queries may have to be modified to use the syntax of the new database and some code may need to be changed, but a full rewrite of the app should not be necessary.


Currently, the MySQL Front-End Modules are available for RPM-based Linux and must be used with the SQL Relay Binary Distribution For Linux. Support for non-RPM-based Linux and Windows will be available soon.

The MySQL Front-End Modules (and eventually, other SQL Relay Enterprise Modules) may be downloaded for free, but must be licensed commercially. 30-day trial licenses are also available.

Of course, the standard SQL Relay distribution, which the SQL Relay Enterprise Modules compliment, is still free to download and to use, as always.

See the following links for more information.

Installing the SQL Relay Enterprise Modules
Licensing the MySQL Front-End Modules
Configuring the MySQL Front-End Modules

SQL Relay 0.67.0 Release Announcement

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

This release mainly fixes a bunch of obscure bugs in the MySQL drop-in replacement library. A 1-byte blob-related memory leak has been fixed. An obscure bug in the firebird code has been fixed. /etc/alternatives/java_sdk is now the preferred Java SDK. The server module API has been refactored a bit too.

Full ChangeLog follows...

  • general documenation clean-up
  • added missing all: target to man/Makefile
  • mysql_info in drop-in library for mysql returns NULL instead of "" now
  • mysql_errno in the drop-in library now returns the error number from the DB, instead of CR_UNKNOWN_ERROR, if no error map is provided, and the backend database is known to be mysql
  • mysql_refresh in the drop-in library works with options other than REFRESH_GRANT now
  • mysql_shutdown/kill in the drop-in library return 1 on failure now rather than 2000
  • date/time and timestamp types are recognized as binary types now
  • year/timestamp types are recognized as unsigned types now
  • TINY/MEDIUM/LONG_TEXT types are recognized as blobs now
  • mapping date/time to MYSQL_TYPE_DATE/TIME, rather than MYSQL_TYPE_DATETIME is the default in the drop-in library now and SQLR_MAP_DATETIME_TO_DATE has been removed
  • mysql drop-in library sets column flags correctly now
  • mysql drop-in library runs appropriate kill/shutdown queries now
  • mysql connection uses non-stmt api for "show *" queries now
  • mysql drop-in library runs "show processlist" for mysql_list_processes
  • mysql drop-in library supports date/time/datetime input binds now instead of just treating them all as datetimes
  • connection modules differentiate between date/time/datetime binds now instead of just treating them all as datetimes
  • added result set row translation module framework
  • updated firebird connection module to the fieldstruct.textbuffer instead of fieldbuffer for non-text to text translations
  • added support for negative times in mysql connection module and drop-in library
  • added translation-failure event
  • general refactoring of server module API
  • consolidated sqlr-scaler code
  • fixed a 1-byte empty-blob memory leak in the client
  • /etc/alternatives/java_sdk is preferred, if found, now

Rudiments 0.57.0 Release Announcement

Version 0.57.0 of Rudiments, the C++ class library for developing systems and applications, is now available.

Not much new in this release, actually. Mainly fixes for sparc platforms and little-endian systems.

Full ChangeLog follows...

  • fixed subtle bug in codegree::compareValue
  • replaced several type-punned pointer dereferences with bytestring::copy() to avoid bus errors on sparc and other platforms that require assignments from aligned locations
  • added filesystem::hostToLittlEndian/littleEndianToHost
  • added missing __LITTLE_ENDIAN definitions to filedescriptor.cpp
  • added minimal glibc-specific process::backtrace() method

Tuesday, September 20, 2016

SQL Relay 0.66.0 Release Announcement

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

This release introduces notifications, connection schedules, query router modules, support for PHP 7, a significant fix for the ODBC driver on 32-bit Windows platforms, and support for Systemd.


It is now possible to configure an instance of SQL Relay to send notifications when events occur.

For example, a system administrator can be notified if SQL Relay logs out of the database, as this could indicate a network problem or a problem with the database or database server. If SQL Relay is configured to route to queries in a master/slave replication, then a system administrator and DBA can be notified if an integrity violation occurs. If SQL Relay is configured to filter out certain queries, then a security administrator can be notified if a filter violation occurs.

Notifications are implemented as a modular framework, and a basic "events" module is shipped with the standard SQL Relay distribution. More complex modules could also be written.

See Notifications for more information.

Connection Schedules

It is now possible to define connection schedules to allow certain users to access the database during certain times, and other users to access the database during other times. "Times" can be times of day, days of the week, days of the month, months, or years.

For example, Bob and Sally may be allowed to access the database only during the day shift, and Joe and Mary may be allowed to access the database only during the night shift. Contractor Bill may only be allowed to access the database for the next 3 weeks while he works on some project.

Connection schedules are also implemented as a modular framework, and a basic "cron_userlist" modules is shipped with the standard SQL Relay distribution. More complex modules could also be written.

See Connection Schedules for more information.

Query Router Modules

SQL Relay has long supported query routing - where queries can be routed to one database or another based on the contents of the query. For example, DML/DDL can be sent to a master database and selects can be distributed over a pool of slaves. Or, all queries that reference one set of tables can be sent to one database, and the rest can be sent to another.

In this release, the a router module framework has been implemented, and the standard regular-expression matching functionality has been reimplemented as a module.

Also, in this release, modules for routing queries by user and client-ip address have been added.

See Query Routing for more information.


PHP 7 is gaining traction and becoming more and more widely used. SQL Relay's native API for PHP and PHP PDO driver both support PHP 7 now. The configuration is the same as always. Applications shouldn't notice any differences. The updated drivers should just work.

ODBC Fixes

For several months now, bug reports have been coming in regarding ODBC on 32-bit Windows platforms, or on 64-bit Windows platforms, when using the 32-bit ODBC driver. The principal complaint is that the SQL Relay ODBC driver didn't work properly with Excel.

I'm happy to report that the problem has been fixed.

SQL Relay as an ODBC data source for Excel should now work as expected in all environments.

The bug required modifications to the ODBC driver, and (remarkably) to the SQL Relay server as well. So, if you want to resolve this issue, be sure to update both.


SQL Relay now supports systemd.

If systemd is detected during a build, then the classic /etc/init.d scripts aren't installed any more.


Various obscure bugs have been fixed, a few new features have been implemented, and a few patches from the community have been applied. Thanks community!

Full ChangeLog follows...

  • fixed mysql set isolation level query
  • updated mysql connection to run commit/rollback at the end of every session even if only selects were run, to make sure metadata locks are released
  • renamed sqlrclientexitstatus_t to clientsessionexitstatus_t
  • updated all db connection modules to reinit the column count during prepare rather than execute
  • fixed bug where server-side errors set by conn/cursor->setError() could inadverntantly be overriden during the next call to conn/cursor->errorMessage()
  • added several more queries to mysql stmt-api blacklist
  • configure option --disable-odbc-driver actually works now
  • filters can specify an error string and error number now
  • added support for systemd
  • fixed a memory leak by applying and updating a patch from Kasyanov Dmitry to deallocate the error message in C++ API destructor
  • legacy_stdio_definitions.lib is now included when building ODBC with VS2015
  • added support for PHP7
  • added -wait option to sqlr-start for non-Windows
  • disabled -disable-new-window option to sqlr-start for non-Windows
  • non-ascii character encodings work with the C# API now
  • rudiments prompt class is used instead of using readline directly now
  • renamed "logger-events" to just "events"
  • added notification framework and "events" module
  • added connection schedule framework and "cron" module
  • applied sec/usec patch from samchuck at neowiz to custom_nw logger to fix subtle time calculation error
  • applied a patch from samchuck at neowiz to the sqlrclient protocol module to deallocate storage for bind variables after logging the query to resolve a reference-after-free bug
  • added router module framework with "regex", "userlist" and "clientiplist" modules
  • when router detects a possible integrity violation now, it raises an event which can be logged or trigger a notification and disables the instance
  • router calls begin, commit, and rollback on all connections now, even if they're executed as queries rather than using the api methods
  • removed tls_userlist/krb_userlist auth modules and updated the userlist auth module to support tls and kerberos auth
  • converted most of the doc source to wikitext
  • fixed ODBC driver bug that could corrupt memory and trick the driver manager into thinking that some basic functions are unavailable
  • ODBC SQLTables works more correctly now
  • fixed an ODBC bug that caused the first row to be fetched twice when using SQLBindCol

Rudiments 0.56.0 Release Announcement

Version 0.56.0 of Rudiments, the C++ class library for developing systems and applications, is now available.

This release is mainly a clean-up release. Lots of obscure bugs have been and inconsistencies have been fixed. The only new feature is an class for generating SHA1 hashes.

Full ChangeLog Follows...

  • userentry/groupentry::initialize(NULL) don't load root any more
  • + is properly http-escaped now
  • directory::getChildByIndex() works correctly on windows now
  • dynamiclib::getError() returns NULL on all platforms when there is no error now (used to return an "ok" string on Windows)
  • userentry/groupentry::getSidString() returns NULL on non-windows platforms when uninitialized or initialized to an invalid group, used to return NULL on Windows and -1 on unix-like platforms
  • fixed configure bug causing getspnam methods not to be detected
  • datetime::addDays works correctly now
  • datetime::getTimeZoneOffset() works correctly on Windows now
  • added sha1 class
  • printBits() correctly takes a const unsigned char * argument now
  • applied Kasyanov Dmitry's patch to fix a subtle bug in TLS/SSL method initialization
  • added destructor to stdiofiledescriptor that prevents file descriptors 0, 1 and 2 from being closed when the instance is destroyed
  • added config_vs2015.h for VS2015
  • updated url.cpp to compile with VS2015
  • sys::getMaxLineLength defaults to SSIZE_MAX now
  • groupentry::getMembers() returns NULL instead of an array with a single NULL member if the group has no members on all systems now
  • directory::fpathConf works correctly now
  • renamed directory::canAccessLongFileNames() to directory::canExceedMaxFileNameLength()
  • disabled calls to deprecated readdir_r
  • moved file::createPipe() to filedescriptor class
  • added file::createTemporaryFile() with permissions argument
  • added process::waitForChildToExit()
  • added a missing clear-the-output-buffer in the compiler class

Wednesday, March 30, 2016

SQL Relay 0.65.0 Release Announcement

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

This release primarily introduces Kerberos and TLS authentication and encryption, support for Python 3, and support for Node.js 4.X and higher, but there are a lot of smaller updates as well.


SQL Relay now supports Kerberos encryption and authentication.

When Kerberos encryption and authentication is used:

  • All communications between the SQL Relay client and SQL Relay server are encrypted.
  • A user who has authenticated against a Kerberos KDC or Active Directory Domain Controller can access SQL Relay without having to provide additional credentials.

See the SQL Relay Configuration Guide for detailed information.


SQL Relay now supports TLS/SSL encryption and authentication.

When TLS/SSL encryption and authentication is used:

  • All communications between the SQL Relay client and SQL Relay server are encrypted.
  • SQL Relay clients and servers may optionally validate each other's certificates and identities.

See the SQL Relay Configuration Guide for detailed information.

Python 3

It's been a long time coming but Python 3 is really starting to take off. In response, SQL Relay's configure script can detect a Python 3 installation and the Python API now formally supports Python 3.

Currently, the configure script looks for Python 2 and Python 3 installations but defaults to Python 2 if it finds one. The Python 2 search can be disabled with the --disable-python flag though, and the Python 3 search can be disabled with the --disable-python3 flag.

In a future release, Python 3 will be the default. So, look out for that.


Node.js 4.x and higher are now correctly detected and supported.

Separate "database" and "proxied" Authentication Options

The authtier="database" configuration option has been split into two options: authtier="database" and authtier="proxied". When authtier="database" is used, the user is authenticated against the database (rather than against a list of users in the sqlrelay.conf file) by way of a re-login, except (in previous releases) when using an Oracle database. Previous releases required configuration of a proxy user when using an Oracle database and there was no way to get the standard re-login behavior. There is now. authtier="database" works the same with all databases now: it re-logs in unless the requested user is the one that the database connection is currently logged in as. To get the proxy-user behaviour, authtier="proxied" must now be used.


Various ODBC-related bugs have been fixed. If you had a problem connecting to SQL Relay from an app that uses ODBC, try this release.


A subtle bug that caused incompatibilities with the popular logrotate utility has been fixed. Logrotate can now be used to rotate any of the logs produced by SQL Relay.


A long standing problem that occurred when using a listenertimeout with a threaded listener (the default) has been fixed.

PHP/Apache Weirdness

There has been a problem for a while now where, after a graceful restart, Apache workers would get stuck in the Closing Connection state after running a PHP script that used SQL Relay. This is now fixed.


Lots of even more obscure and subtle bugs have been fixed too. Full ChangeLog follows...

  • removed erroneous SQLR_SQLExecute export in ODBC driver
  • fixed call type of internal ODBC driver functions and made them static
  • fixed --disable-API flags in configure.vbs
  • fixed bad user/password copy logic in ODBC SQLConnect function
  • added missing default implementation of sqlrservercursor::getColumnTypeName()
  • added parser debug option
  • normalize translation can convert foreign decimals now
  • updated missing-tnsnames.ora warning
  • updated sqlr-start and sqlr-scaler to run programs using fully qualified path names
  • fixed query timer in sqlrsh
  • add timer="query/command" parameter to slowqueries logger
  • added odbcversion parameter to ODBC connection
  • updated erlang api to use the C++ compiler to link (for netbsd 7.0)
  • added major-version check to node.js api for node 4.x+
  • ODBC driver's SQLGetData can deal with NULL targetvalue now
  • fixed normalize || bug
  • fixed normalize bug that removed spaces around * for "all columns" improperly
  • removed freetds warning, modern sybase has different library file names
  • updates to support python3
  • added several --disable options to the configure script for unix/linux
  • removed sqlr-fields/sqlr-query and updated sqlrsh to provide the same functionality
  • removed antiquated sqlrelay.dtd
  • moved default example directory to share/sqlrelay/examples
  • sqlrelay.conf example is installed under example directory now
  • Python DB -> Python DB-API in the docs
  • moved private members and support classes into C++ API's .cpp files
  • removed -revision during link and adopted Semantic Versioning 2.0
  • added missing get*List methods to Java API
  • removed errant information_schema views from getTableList with mysql
  • refactored listenertimeout to use timed semaphore ops and only fall back to an alarm if the system doesn't support timed sem ops and the instance isn't configured to use threads
  • fixed inode-check in logger modules - works correctly with logrotate
  • change "an error occurred" to "server closed connection" to reduce false-positives from log analyzers - "real" errors will still be reported elsewhere
  • refactored the config file parser
  • added support for kerberos authentication and encryption
  • added krb_userlist auth module
  • renamed "authentication" to "auth" (for authentication/authorization, also matches module name prefix) in config file and docs
  • removed an errant flushWriteBuffer after client auth
  • moved debugprint stuff into rudiments
  • added support for tls authentication and encryption
  • added tls_userlist auth module
  • fixed long-standing copyrefs bug
  • when calling after-triggers, "success" flag is set correctly now
  • fixed bug where after-triggers could mask failed queries
  • added support for "before and after" triggers
  • moved call to set stdinput to use blocking mode from rudiments::stdiofiledescriptor to sqlrsh, as doing it in every case caused problems with apps like Apache, which need stdin/out/err to be in non-blocking mode sometimes (fixes bug where PHP's that used the SQL Relay API would cause apache workers to hang closing connections after a graceful restart)
  • made auth_database module always re-login
  • added auth_proxied module to support Oracle's (and potentially other databases') n-tier/proxied authentication
  • fixed a long-standing bug where passwords weren't checked when switching between oracle n-tier authenticated users

Rudiments 0.55.0 Release Announcement

It's official. Version 0.55.0 of Rudiments, the C++ class library for developing systems and applications, is now available.

What's new in this release? Read on...

Security Framework

The most significant new feature is a security framework. A securitycontext may now be attached to a filedescriptor to enable encrypted reads and writes. The securitycontext class is just an interface though, and there are currently gsscontext and tlscontext implementations of it.

The gsscontext class and associated classes implement a wrapper on top of the GSSAPI on Linux/Unix and SSPI on Windows. This primarily provides Kerberos encryption and authentication, though on Windows it could be used for NTLM as well.

The tlscontext class and associated classes implement a wrapper on top of OpenSSL on Linux/Unix and, again, SSPI on Windows. This provides Transport Layer Security, or it's predecessor Secure Socket Layer, on both platforms.

See the Rudiments class reference for detailed information about all gss and tls classes. Comprehensive code examples are provided in the test directory of the source distribution as well: gssserver.cpp, gssclient.cpp, tlsserver.cpp, and tlsclient.cpp

Thread Class Improvements

The thread class has been improved a bit.

If constrained system resources cause run() to fail, it automatically retries now, and this behavior can be adjusted with the retryFailedRun()/dontRetryFailedRun() methods.

A thread can now be created in a detached state with the runDetached() method, rather than having to be detached later, which can cause a race condition.

The cancel() method has been removed now too, as it was the primary culprit in creating the aforementioned race condition, and can't be implemented on Windows at all.

Thread methods are more robust as well. They all verify that the thread is actually valid before attempting to operate on it. Not doing this caused crashes and odd behavior on some platforms.


The keen eye might observe the uncharacteristic .0 trailing the Rudiments version number. As of this version, Rudiments has adopted Semantic Versioning for the project and Libtool Versioning for library files on Linux/Unix. Hopefully, between the two, version-compatibility should be more intuitive.

General Bug Fixes

There were a slew of bug fixes as well, though most were very obscure and difficult to encounter.

The most significant had to do with handling standard input. There was a call that placed it in blocking mode by default which wreaked odd havoc on Apache after a graceful restart when running PHP/SQLRelay apps.

Full ChangeLog follows...

  • file::open sets errno=ENOENT if the file or path wasn't found on Windows now
  • fixed a bug with filedescriptor::printf when printing to non-buffered files on Windows and Solaris 11
  • fixed a bug that caused a crash in codetree if codeposition is NULL added missing dll export clause to codetreegrammar class
  • added xmldomevents class
  • added namespace discrimination in codetree::write
  • fixed a codetree end-of-string-during-concatenation bug
  • fixed a codetree tree reset bug
  • fixed an xmlsax bug that caused text nodes with empty values to be inserted between tag nodes when there is no text between tags
  • added escapechar option to filedescriptor::read-until-terminator methods
  • added a generic securitycontext class and integrated it with filedescriptor
  • implemented GSS/SSPI and TLS/SSL children of securitycontext
  • removed old SSL integration with filedescriptor
  • updated inet/unixsocketclient to close() as the first step of connect()
  • updated charstring::to(Unsigned)Integer-with-endptr methods to have const char **endptr arguments
  • updated configure script to find erlang on openbsd 5.8
  • buffers allocated by vasprintf are free()'ed now rather than deleted
  • fixed a commandline bug that could cause it to return the next parameter for getValue() of a parameter with no value
  • fixed connect()/useBlockingMode error masking bug
  • removed -revision during link and adopted Semantic Versioning 2.0
  • added pthread_create/EAGAIN retry
  • added methods to configure and examine process::fork and thread::run retry behavior
  • thread methods test for invalid thread before operating on it now
  • charstring::split doesn't crash if list or listlength are null now
  • added debugprint header
  • removed problematic thread::cancel() method
  • added thread::runDetached() methods
  • removed useBlockingMode() call in stdio constructor as is caused problems with apps like Apache that actually need stdin/out/err to be in non-blocking mode sometimes