Sunday, December 25, 2016

Rudiments 1.0.2 Release Announcement

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


There was actually an unannounced 1.0.1 as well. 1.0.1 mainly contained fixes for OpenSSL 1.1.x. 1.0.2 just adds another SSL tweak that's required by Fedora for packaging. No pressing need to upgrade unless you're having trouble building on a platform that uses OpenSSL 1.1.x.

Saturday, December 24, 2016

Updating Your App to Support OpenSSL 1.1.0

Introduction

OpenSSL 1.1.0 introduced several API-breaking changes, and with distributions starting to use it, I found myself scrambling to get my software working. I found various docs stating that 1.1.0 changed things, but nothing that clearly stated: "if you have this now, then change it to this..." So, that's what I'll do here, and hopefully it will help you if you find your app in the same situation.

Deprecated "method" Functions

Lots of "method" functions like TLSv1_server_method() and SSLv23_client_method() and TLSv1_2_method() are deprecated in OpenSSL 1.1.0, and if you compile your software with -Werror, then that presents a problem.

All you can call now is one of:

  • TLS_method()
  • TLS_client_method()
  • TLS_server_method()

If your code allowed the user to select a SSL/TLS mode then it can't do it by calling a mode-specific function any more. It'll have to select the mode some other way that I'm not aware of. The new methods negotiate the most modern mode supported by the client and server. It's not immediately clear how to override that, or if you can. If someone knows a way, please leave a comment below.

It's easy enough to replace function calls, but if you want to support older versions, then you have to add tests for the old and new functions to your configure script and add all kinds of #ifdefs, and so forth. Actually, since various methods like these have been added over the years, you may already have such tests and #ifdefs (I did) and it won't be so bad.

Deprecated ASN1_STRING_data
unsigned char *ASN1_STRING_data(...);

has been replaced with

const unsigned char *ASN1_STRING_get0_data(...);

This is a straightforward replacement, but note the const'ness of the return value of the new function. If you're using C++ then you may have to change the type of the variable it's being assigned to (or un-const it).

Again, this is a brand new function, so, configure tests and #ifdefs.

Opaque EVP_PKEY

The EVP_PKEY struct (public key struct) is opaque now. The header files declare that an EVP_PKEY struct exists, but the definition is hidden up in the library. The error you get (with g++ at least) is fairly obtuse though, complaining about a forward declaration and an invalid use of an incomplete type. The error just means that the struct is opaque.

Fortunately there are a bunch of functions that return the various components of the struct.

I had to replace code to get the key type like:


EVP_PKEY *pubkey = X509_get_pubkey(c);
...
int t = pubkey->type;

with code like:


EVP_PKEY *pubkey = X509_get_pubkey(c);
...
int t = EVP_PKEY_base_id(pubkey);

Note that there IS an EVP_PKEY_type() function, but it does NOT return the "type" member of the struct. Ha! Not super intuitive, but the change is still straightforward.

Similarly, I had to replace code to get the key itself like:


EVP_PKEY *pubkey = X509_get_pubkey(c);
...
const unsigned char *key = pubkey->pkey.ptr;

with code like:


EVP_PKEY *pubkey = X509_get_pubkey(c);
...
const unsigned char *key = EVP_PKEY_get0(pubkey);

I'm not sure when those functions were introduced, but they appear to be available in OpenSSL 1.0.x but not in OpenSSL 0.9.x, so again, if you want to support really old versions, then you'll have to add configure tests and #ifdefs.

Opaque X509

The X509 struct is also opaque now. Like the EVP_PKEY struct, you'll have to use functions to access its members.

The one case I needed was slightly trickier though.

In the past, I was getting the signature algorithm name like:


X509 *c = SSL_get_peer_certificate(ssl);
...
char sigalg[256];
OBJ_obj2txt(pvt->_sigalg,256,c->sig_alg->algorithm,0);

But I had to change that code to:


X509 *c = SSL_get_peer_certificate(ssl);
...
char sigalg[256];
OBJ_obj2txt(pvt->_sigalg,256,OBJ_nid2obj(X509_get_signature_nid(c)),0);

Which is a little contrived. In the old code, c->sig_alg->algorithm is an "obj", and I could pass it directly into OBJ_obj2txt. In the new code, I have to get the "nid", convert it to an object, and pass that in. Ideally I'd just get the object to begin with, but there doesn't appear to be an obvious way to do that.

As a side note, the function name X509_get_signature_nid() was a little confusing at first, because it appears to return the "nid" of the signature _algorithm_ as opposed to the signature itself. But I guess the signature itself wouldn't have a "nid", so it makes sense, but it wasn't immediately intuitive to me.

Again, X509_get_signature_nid() was introduced sometime in the 1.0.x series, so you'll have to add a test for it and #ifdefs if you want to support older versions.

Conclusion

The opacity of the structs ought to help OpenSSL be more maintainable in the future. They ought to be able to change the code at will now, without breaking the API or ABI, and that's always good. I did the same kind of thing with my Rudiments library way back, and recently to SQL Relay for that exact reason. Hopefully it will work out well for both of us.

I'm not sure about the TLS_*_method() thing though. It seems like there really ought to be a way to choose a specific SSL/TLS mode. Perhaps there is though, and I just didn't see it. Somebody let me know if they figure that one out.

Wednesday, December 21, 2016

SQL Relay 1.0.0 Benchmarks

Introduction

There are a lot of reasons to use SQL Relay, but one of the most popular is just to improve throughput, usually measured in queries-per-second.

Logging in to most databases takes a noticable amount of time...

So much time, in fact, that transient apps (such as web-based applications) which start up, connect, query, disconnect, and shut down to deliver each bit of content, often take longer to log in than to run the queries. As a result, overall throughput (queries-per-second) is generally low unless many queries are run per connection.

SQL Relay maintains a pool of already-logged-in database connections, and logging in to a connection pool is substantially faster than logging into the database directly.

As a result, overall throughput (queries-per-second) should be maximized, and shouldn't depend nearly as much on the number of queries that are run per connection.

In theory.

Anecdotally this is the case and informal testing has always confirmed it. But, I wanted hard data and pretty graphs to prove it. Also, it had been a long time since I'd done any formal performance testing. For all I knew, I'd done something recently to make everything run horribly.


sqlr-bench

Of course there are popular tools for benchmarking apps and databases. JBench, for example. But they all use intermediate layers - JDBC, ODBC, web pages... I wanted raw comparisons: the database's native API vs. SQL Relay's native API. I also wanted it to be easy to run from the command-line, and automatable.

Months ago, I'd begun writing a program called "sqlr-bench" which aimed to run a battery of tests against the database directly, run the same tests through SQL Relay, collect stats, and produce a graph. It had to support every database that SQL Relay supported. It needed a dozen or more tunable paramters... It was a fairly ambitious project. I started and stopped work on it several times but I eventually got it working well enough.

It creates a table, populates it, and runs a series of selects. First it runs 1 select per connection, then 2 per connection, then 3, etc. It times all of this and calculates queries-per-second as a function of queries-per-connection. The whole process is done first against SQL Relay, and then directly against the database. The data is then correlated and gnuplot is used to produce a graph.

Various parameters are configurable, but the defaults are:

  • rows: 256
  • colums: 16
  • column size: 32
  • rows to fetch at once: 10
  • samples: 10
  • queries: 30

Initial Results

I ran the tests against each database, and against instances of SQL Relay running on the local machine (over a unix socket).

Theoretically I figured I should see...

  • ...against the database directly: The number of queries-per-second should increase steadily as the number of queries-per-connection increases. It should start off to be pretty bad, then get better and better, and eventually level off so that running more queries-per-connection doesn't make any more difference.
  • ...against SQL Relay: The number of queries-per-second should be independent of the number of queries-per-connection, and should be roughly the same as the maximum number of queries-per-second that can be run against the database directly.

Performance against the database directly was exactly as I expected, and it generally seemed to level off at about 20 queries-per-connection.

SQL Relay actually does have a small ramp-up period (2 or 3 queries per-connection is slower than 4 or more), which I didn't expect, but the number of queries-per-second were otherwise uncorrelated with the number of queries-per-connection, which I did.

Overall though, SQL Relay did not immediately perform as well as I expected. In fact, for most databases, there was a "break-even" between 5 and 10 queries per-connection.

While apps that use SQL Relay would still likely perform better than if run directly against the database (how many web pages run more than 5 queries that return 256 rows each), I really expected it to perform better.


Tweaks

Thus began days of profiling and analyzation. It appeared that, indeed, over the years new features had steadily introduced inefficiencies.

In most cases, things just needed to be reused instead of reallocated. Everything from database cursors to byte arrays. I replaced a few regular expressions with simple string compares, and made a few case-insensitive string compares case-sensitive. There were also some alignment and padding issues that prevented optimized strcpy/memcpy from being used. My socket buffer sizes were sub-optimal...

Lots of little changes.


Results

In the end, I was pleased with the results. Most of them, anyway.

Informix:

Oh yeah, that looks great.

MySQL:

Still looking good.

Oracle:

Not bad, but why the falloff?

DB2:

It's good where it counts, but why does DB2 eventually outperform SQL Relay?

SAP/Sybase:

Again, it's good where it counts, but throughput is generally low. And again, why the falloff?

PostgreSQL:

My goodness, PostgreSQL is fast!

I checked the code over and over to make sure my test program was legitimately running queries and not just erroring out or something. It was. Why is it so much faster than everything else? Strace showed that the socket is in non-blocking mode and that they do a poll() then read(), basically blocking manually rather than doing a blocking read. My preliminary tests with a similar strategy didn't show much improvement with that over a blocking read though. Non-blocking poll()/read() is a good way to multiplex reads through a single thread, but if you're only reading from one socket at a time, I wouldn't expect it to help at all.

Very strange.

SQLite:

Well, I guess I shouldn't really be disappointed that SQL Relay is slower than SQLite :)

FreeTDS:

82 queries-per-second??? Does SQL Server limit throughput?


What now?

The falloffs are strange. It's odd that it's so inconsistent. There doesn't appear to be a memory leak. Heap fragmentation?

Performance against DB2 and SAP is odd. It shouldn't break even so quickly.

Performance against PostgreSQL is the most strange. Whatever they're doing, SQL Relay needs to be doing it too.

And what's the deal with MS SQL Server?

These things deserve investigation.

I ran tests with SQL Relay on a server between the client and database too. The results were similar, but there was more of a ramp-up. I should publish those results too.


Test Environment

To perform these tests, I configured 2 Fedora Linux VMware VM's. One for the databases and one to run SQL Relay. Both VM's had 1 CPU. The database VM had 2Gb of RAM. The SQL Relay VM had 512MB.

The DB VM ran modern versions of all databases, in their default configurations, without any performance tweaks.

I built Rudiments and SQL Relay without debug and with -O3 optimizations like: CPPFLAGS="-O3" ./configure

The SQL Relay configurations were all pretty standard too. For example, the Oracle configuration was something like:

 <instance id="oracletest" port="9000" socket="/tmp/test.socket" dbase="oracle">
  <users>
   <user user="test" password="test"/>
  </users>
  <connections>
   <connection string="user=...;password=...;oracle_sid=..."/>
  </connections>
 </instance>

For the PostgreSQL tests I disabled typemangling.

The sqlr-bench program can be found in the test/bench directory of the SQL Relay distribution. It must be run from that directory. I ran it with default parameters. There's a benchall.sh script there too, which starts and stops the appropriate SQL Relay instances and runs sqlr-bench. I actually used that script rather than running the tests individually.

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

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.



Kerberos

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.

TLS

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

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.

ODBC

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.

logrotate

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.

listenertimeout

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.

Bugs

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.

Versioning

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

Tuesday, March 8, 2016

SQL Server on Linux!

It would seem that Microsoft is going to release a version of SQL Server for Linux sometime this year. You can sign up for updates and previews too.

Awesome?

Saturday, January 9, 2016

Building Ruby From Source With Microsoft Visual Studio

Introduction

Ruby packages are available for Windows, and they can be used to develop Ruby applications, all day long. However, they lack the necessary files to develop Ruby modules in C/C++ and unlike PHP, where you can fairly easily shoehorn the headers into an already-installed package, it's not so easy with Ruby. In fact, to get a hold of the components to shoehorn in, you have to do an entire build from source, so you might as well just do that to begin with.

Fortunately, it's not too difficult.

Prerequisites

For this effort you will need:

  • Microsoft Visual Studio
  • The Ruby source code.
  • A few unixish commands - gunzip and tar. These can be provided by a gnuwin32 installation, a cygwin installation.
  • A text editor. Notepad or vi will do.

The Process

I last did this with Ruby 2.2.0, so I'll use it in this example.

The Ruby source code comes as a tar.gz file. There aren't any .zip files available, or if there are, I'm not sure where to find them. So you'll have to use some unixish commands to extract it.

Move the ruby sources to somewhere convenient and use the unix tools to extract it:


gunzip ruby-2.2.0.tar.gz
tar xf ruby-2.2.0

This should create a directory named ruby-2.2.0

Open the appropriate Visual Studio command prompt. If you plan on building a 32-bit version of Ruby, then open the x86 command prompt. If you plan on building a 64-bit version of Ruby, then open the x64 command prompt.

Navigate into the ruby-2.2.0 directory that was created by the extraction process above.

To get everything to compile and install correctly, I had to edit the file tool\rbinstall.rb and change line 714 to read:


rescue LoadError

This may or may not be necessary with a newer version of ruby, or the line to edit might be different.

Now, configure the build.

For a 64-bit build, run:


win32\configure.bat --prefix=C:\Ruby --target=x64-mswin64

For a 32-bit build, run:


win32\configure.bat --prefix=C:\Ruby --without-ext=fiddle

(The --without-ext=fiddle command just disables the "fiddle" extension, which I couldn't get to build on 32-bit Windows, for some reason. Your mileage may vary.)

To actually build Ruby, run:


nmake

(This will run for a while.)

When it's done, install Ruby by running:


nmake install

Everything should get installed under C:\Ruby

To make the ruby programs generally accessible, add C:\Ruby\bin to the system PATH as follows:

  • Open the Control Panel
  • Search for Environment
  • Click the link for "Edit the system environment variables"
  • Click "Environment Variables"
  • In the System Variables pane, scroll down and click on Path
  • Click Edit...
  • Append: ;C:\Ruby\bin to the Variable Value.
  • Click OK
  • Click OK
  • Click OK

And that's it. Ruby is now built, installed and accessible.

Hopefully this helps someone out.

Good luck developing your Ruby modules on Windows.

Generating a Missing Import File From a DLL

Introduction

Occasionally I run into software packages for Windows that are missing some of the developer bits. One bit that occasionally gets left out is the import library. The headers are there, the .dll is there, but the .lib file is missing.

It's not impossible to generate a .lib from a .dll though.

Here's how.

Prerequisites

You'll need a few things to do this:

  • Microsoft Visual Studio
  • A few unixish commands - echo, cat, and cut. These can be provided by a gnuwin32 installation, a cygwin installation, or just a second linux or unix system.
  • A text editor. Notepad or vi will do.

The Process

I ran into this issue with Active Perl 5.20, so I'll use it in this example. Adjust accordingly.

First, dump the list of exports from the dll into an exports.in file. Open a Visual Studio command prompt and run some commands similar to the following:


cd C:\Perl64\bin
dumpbin /exports perl520.dll > exports

(adjusting for the path and file name of your .dll)

Now, edit the exports file.

Trim off everything prior to the first function definition. In the perl dll, that was a line like:

          1    0 000295C0 ASCII_TO_NEED

Trim off everything after the last function definition. In the perl dll, that was a line like:

       1249  4E0 001271B0 win32_write

Now, use the unixish commands to create a .def file.

(If you're doing this on a second machine, then you'll need to copy the exports file over to it and then, afterwards, copy the .def file back.)


echo EXPORTS > perl520.def
cat exports | cut -c27- >> perl520.def

Now, use Visual Studio's lib command to build the .lib file.


lib /def:perl520.def /OUT:perl520.lib /MACHINE:X64

Note: In this example, we had a 64-bit DLL. To build from a 32-bit DLL, use the /MACHINE:X86 flag instead.

Currently the .lib file is sitting in the bin directory, but .lib files generally need to go in a lib directory or some other place. So, move it now.


move perl520.lib ..\lib\CORE

You can now clean up if you like.


del exports
del perl520.def

And that's it. You can now link against the .lib file and your app will load the .dll at runtime.

Hopefully these instructions help if you ever find yourself in a similar situation.

Shoehorning Development Headers Into PHP for Windows

Introduction

In years past, I've had trouble building PHP modules on Windows because, though PHP packages are available for Windows, they lack the necessary header files to build PHP modules. One solution is to build and install PHP from source, but that takes forever, uses a ton of space, and has a long list of prerequisites. It turns out that it's actually faster and easier to shoehorn in a set of development headers into the already-installed PHP package.

Here's how.

Prerequisites

You will need the following items:

  • A Linux or Unix machine, with a working compiler toolchain. (Actually, a cygwin installation with a working compiler toolchain might suffice but I've never tried it.)
  • The PHP package for Windows.
  • The source code for the same version of PHP as the package.
  • The same version of Microsoft Visual Studio that was used to build the PHP package. The version of VS is discernible in the package filename. For example, php-7.0.2-Win32-VC14-x64.zip was built with Visual Studio 14 (aka Visual Studio 2015).
  • Several bits of software from the gnuwin32 project:

On the Windows machine, install the PHP package into C:\PHP and install Visual Studio.

Install the gnuwin32 software:

  • Create C:\gnuwin32
  • Extract each zip file by right-clicking on it and selecting Extract All.
  • Move the contents of the folders created during the extraction into C:\gnuwin32
  • Add C:\gnuwin32\bin to the PATH environment variable.
    • Open the Control Panel
    • Search for Environment
    • Click the link for "Edit the system environment variables"
    • Click "Environment Variables"
    • In the System Variables pane, scroll down and click on Path
    • Click Edit...
    • Append: ;C:\gnuwin\bin to the Variable Value.
    • Click OK
    • Click OK
    • Click OK

If you can run bison or flex from the command prompt, then it worked.

Building the Headers

Copy the source code to the Linux/Unix machine and extract it. Then build and install the headers somewhere convenient, like your home directory. In this example, we'll use /home/dmuse/php though it could be anywhere.


tar xf php-5.6.17.tar.bz2
cd php-5.6.17
./configure --prefix=/home/dmuse/php
make
make install-headers

Now, copy the entire /home/dmuse/php/include directory to the Windows machine. If the linux/unix machine is visible as a Windows share then you can just drag the directory across the network. Otherwise you can zip or tar it up, contrive to copy it over using scp, ftp, http, or something else, and then extract it by right-clicking on it and selecting Extract All.

Move the includes folder to C:\PHP\dev such that there is now a C:\PHP\dev\include folder. Verify that C:\PHP\dev\include\php exists and that C:\PHP\dev\include\include didn't get created by accident, as sometimes the top-level folder gets duplicated during extraction.

The PHP package now contains most of the headers necessary to develop PHP modules, but a few windows-specific files need to be generated and installed.

Extract the PHP source somewhere convenient on the Windows machine.

Open a Visual Studio command prompt. Make sure to open a prompt for the same architecture as the PHP binary package. For example, if the package is an x86 package, then make sure to open the x86 prompt. If the package is an x64 package, then make sure to open the x64 prompt.

Change directories to the folder that was created when you extracted the PHP source and run the following commands.


buildconf.bat
cscript /nologo configure.js

Then, copy the Windows-specific headers that were built by those commands into the PHP tree:


copy main\config.w32.h C:\PHP\dev\include\php\main
copy TSRM\tsrm_config.w32.h C:\PHP\dev\include\php\TSRM
copy Zend\zend_config.w32.h C:\PHP\dev\include\php\Zend

And that's it. You have now shoehorned development headers into the PHP package for Windows. Good luck building your PHP module!