Tuesday, December 20, 2016

SQL Relay 1.0.0 Release Announcement

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

At great length: 1.0.0!

The client APIs has been stable for a long time. The server APIs have been stable for long enough. Recent efforts during the past few releases updated the internal structure such that significant internal changes can be made without affecting the API or ABI. Yes, it's definitely time for a 1.0.0 release.

Of course, no project is ever feature-complete. There are still plenty of tasks in the backlog, but it should be possible to implement most, or all, of them without breaking the API and ABI.

New Features

The most noticeable new feature in this release is that all DB abstraction layer drivers support the same set of connect-string options, including TLS and Kerberos options, as well as columnnamecase, resultsetbuffersize, dontgetcolumninfo, nullsasnulls, and lazyconnect options. The options are named subtly differetly for each driver, following the conventions for the DB abstraction layer, but all options are present.

Changes

This release mainly features a bunch of internal changes though. Most improve performance or stability in one way or another.

The most outwardly noticeable change is that column names are case-sensitive now when getting a field by name. The docs never promised case insensitivity, and the performance improvement is notable. Also, there are options for upper and lower-casing column names, if you need them to be converted one way or the other.

Another semi-noticeable changes is the removal of calls to mysql_stmt_store_result/mysql_stmt_num_rows in the mysql database connection module. Actually the removal of them isn't the noticeable part. Rather, that since they have been removed, the row count is no longer available immediately, if a result-set-buffer-size is being used. This is consistent with almost all other databases though, and by default, no result-set-buffer-size is used.

Another, nearly unnoticeable change, is that by default now, connections start with 1 cursor, but will scale up, on-demand, to 5 cursors. Of course, maxcursors can be set higher or lower than 5, but it defaults to 5.

Most other changes were fixes for subtle bugs or tweaks to improve performance.

Full ChangeLog follows:

  • updated md5 pwdenc to use rudiments md5 class
  • solved a long-standing hang that could occur if the database password expired while sqlrelay was running, and the sqlr-scaler attempted to start new connections
  • added convertcase, convertcasedoublequotes, and removedoublequotes parameters to normalize translation module
  • added ColumnCaseName parameter to ODBC driver
  • fixed a subtle bug in the ODBC driver that caused the first row to be skipped if columns were not bound
  • added -backtrace option for debugging
  • fixed mysql connection module so it can use the statement API on Windows
  • fixed semaphore reference-before-init bug in sqlrservercontroller
  • added support for db, debug, columnnamecase, dontgetcolumninfo, nullsasnulls, and lazyconnect connect-string options to all drivers (though in some they are camel-case and others lower-case)
  • in DB-abstraction-layer drivers, the debug, dontgetcolumninfo, nullsasnulls, lazyconnect, krb, and tls connect-string options now support any yes/no equivalent such as yes, Yes, y, Y, true, True, 1, (and similar for no)
  • fixed a regression that could cause crashes in the C#/ADO driver when binding null or date values
  • added all parameters to ODBC config screen
  • removed calls to mysql_stmt_store_result/mysql_stmt_num_rows from mysql connection to improve performance. mysql connection doesn't know the total number of rows prior to full fetch now (which is consistent with most other databases).
  • updated postgresql connection to use an unnamed cursor instead of naming it and having to deallocate it over an over
  • tweaked skipWhitespaceAndComments and related methods to use charstring::isNullOrEmpty and character::isWhitespace
  • replaced regular expression comparisons in sap/freetds connections with simpler charstring comparisons
  • updated db2 connection to reuse the statement instead of recreating it with each prepare
  • made column-lookup by name case sensitive to improve performance
  • fixed nagle-disable and sizing of socket read/write buffers
  • changed default maxcursors to 5
  • fixed an uninitialized variable in the firebird connection
  • python api's are installed in dist-packages subdirectory on systems that have that instead of site-packages
  • added route-entire-session option to router modules

Rudiments 1.0.0 Release Announcement

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


Yes, that's right, 1.0.0. It's been a very long time coming, but Rudiments has finally become stable enough to call it version 1. The last few releases have been aggressively targeting this. Now it's time.

So, is Rudiments complete? Hardly. But, the core API has been stable enough, for long enough. Also, the general design has proven to make it easy enough to modify the internals quite a bit without changing the API or ABI. For the most part, anyway.

So what's new in this release? Lots of little fixes. Several features that I've been putting off adding. Several bugs that I've been putting off fixing.

Probably the most significant change is the addition of an avltree class and use of it in various places.

Other big changes include refactoring of the thread class such that the thread and process classes both follow the same spawn/detach/exit/wait paradigm, and refactoring of the filesystem class so that it and the file class both follow the same open/getCurrentProperties/close paradigm.

An md5 class was also added. It and the sha1 class are now children of a hash class.

Full ChangeLog follows:

  • images directory and contents are installed with the docs now
  • added md5 class
  • moved sendSignal/raiseSignal to process class
  • implemented process::backtrace() for windows
  • added missing charstring::getLastOfSet(OrEnd) methods
  • refactored thread class to match process class paradigm
  • added avltree class
  • updated dictionary to use an avltree
  • update xmldom to use a dictionary for its string cache
  • file::getContents(fd) gets the entire file contents now, even if the current position in the file isn't the beginning
  • fixed detection of whether timed semaphore ops are supported on platforms that just implement semtimedop() to return -1 and set ENOSYS
  • fake certs aren't generated now (in tests) on Windows, if ssl is disabled, or if openssl isn't found
  • added charstring::isYes/isNo
  • fixed high/low-bits calculation bugs in memorymap and sharedmemory for Windows
  • fixed a maxsize bug in memorymap for Windows
  • changed filesystem paradigm to match file paradigm - open/close/getCurrentProperties
  • added O_BINARY to various open()'s in file class
  • fixed threadmutex::wait() return value on Windows
  • fixed timezone offset during DST for systems with the "timezone" external variable
  • filesystem class returns NULL consistently for methods that return const char * but that aren't implemented by the OS
  • sys::getSuggested* return 1024 if they are unsupported by the OS now
  • added dlerror() calls before dlopen/dlsym/dlclose to clear stale errors
  • added fs type-name to fs type-id mapping for systems with statvfs
  • added type-name and type-id for SCO htfs
  • if unsetenv doesn't exist, then environment::remove() checks to see if the variable exists at all before setting it to an empty string now, to avoid creating it unnecessarily
  • xmldomnode writes out numeric entites like È correctly now
  • fixed groupentry::initialize(NULL) on UnixWare
  • implemented missing unixsocketserver::close()
  • fixed filesystem::open(fd) for Windows 8+
  • fixed file::changeOwner() for Windows 8+
  • initialized timeremaining in snooze::nanosnooze for systems that don't set it at all if the full sleep occurred (eg. arm64 linux)
  • renamed linkedlistutil_* to node_*
  • added commandline::getValue/found that take arg and abbr parameters
  • added supported() methods to sharedmemory/semaphoreset classes
  • renamed supportsXXX() methods to supported()
  • unix sockets are faked on syllable now
  • tweaked dynamicarray::find() to greatly reduce the number of comparisons it does and improve performance significantly
  • various performance tweaks to memorypool
  • updated memory pool to 8-byte-align allocations
  • renamed filedescriptor::get/setTcpRead/WriteBufferSize to
  • filedescriptor::get/setSocketRead/WriteBufferSize
  • fixed *entry::getAliasList() to return NULL under all circumstances if there are no aliases
  • added missing libraries to the library link command

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.

Availability

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.



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

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