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.



Notifications

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

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.

Systemd

SQL Relay now supports systemd.

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

Miscellaneous

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