tag:blogger.com,1999:blog-84923685774790228712024-02-22T10:54:52.289-08:00firstworks software and systemsThe chronicles of SQL Relay, Rudiments, and other firstworks technologies<br><br>...and general adventures in Software Development, IT, and computing.David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comBlogger125125tag:blogger.com,1999:blog-8492368577479022871.post-84563533176967935432022-08-09T08:13:00.005-07:002022-08-09T08:13:48.281-07:00SQL Relay 1.9.3 Re-Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>I just updated the release files for version 1.9.3 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>. There was a problem that caused the master branch rather than the 1.9.3 branch to get packaged as 1.9.3, which led to all kinds of build errors. That's been fixed. The files that are out there now should contain source for the actual 1.9.3 release. If you had trouble building, then that's almost certainly why. Try re-downloading the SQL Relay source. It should work this time.</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-54648369120399850852022-08-05T11:08:00.003-07:002022-08-05T11:08:40.320-07:00SQL Relay 1.9.3 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.9.3 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This release has 2 main features:</p>
<ul>
<li>Support for PHP 8.1</li>
<li>Support for Oracle 18 and 19</li>
</ul>
<p>However, it does include some bug fixes as well:</p>
<ul>
<li>It is now possible to bind NULLs when using the ODBC backend to talk to MS SQL. This, apparently, didn't work in previous versions.</li>
<li>Some SQLGetInfo()s were missing from the ODBC driver, which prevented it from working correcltly with specific apps, in conjunction with ODBC 3.8+, most notably the Oracle Heterogenous Agent. Those are now fixed.</li>
<li>I had inadvertantly enabled some experimental code in the PostgreSQL backend. That's disabled now, and works correctly.</li>
<li>The PostgreSQL front-end had bind-related trouble when used with a non-PostgreSQL backend due to various subtle bugs. Those are fixed now.</li>
<li>In certain circumstances, SQL Relay had trouble binding date values when used with MySQL. That's also fixed.</li>
</ul>
<p>If any of that sounds familiar to you, or if you need support for PHP 8.1 or Oracle 18+, give this release a try!</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-44809758971036871282021-11-03T22:02:00.002-07:002021-11-03T22:02:22.576-07:00Rudiments 1.4.2 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.4.2 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>This release fixes bugs that caused timeouts to not timeout on systems that use epoll_wait() (eg. Linux) and kqueue() (eg. FreeBSD).</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-16121270457869286652021-09-27T21:03:00.002-07:002021-09-27T21:03:24.909-07:00SQL Relay 1.9.2 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.9.1 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This release has 2 main features:</p>
<b>Improved Ruby API</b><br/>
<p>Some time ago Ruby added a Global Interpreter Lock, similar to the one that Python has had forever. To improve performance with modern Ruby, I added code to release/reacquire the lock around SQL Relay C-API calls in case they perform I/O, similar to what the Python API does.</p>
<b>Password File Support in the Userlist Auth Modules and Connect Strings</b><br/>
<p>If you don't want to store passwords directly in the sqlrelay.conf files any more, it's now possible to store them in external files (with potentially more restrictive permissions) and refer to them in the sqlrelay.conf file enclosed in square brackets.</p>
<p>The userlist, mysql_userlist, and postgresql_userlist auth modules now support this. It is also supported in the "password" parameter of the "string" attribute of the "connection" tag for all databases.</p>
<b>Bug Fixes</b><br/>
<p>The sqlrclient protocol module had some quirks related to how it handled cases where it was out of cursors, but was sent a really long query. These are resolved now. There was also a bug that could cause the result set not to be closed and the "current error" not to be cleared between reexecutions of the same query (eg. with updated bind variables) if the previous execution resulted in an error. That's also fixed. Both fairly obscure errors, but if they sound familiar then try this release.</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-83559749646296372242021-09-27T20:52:00.000-07:002021-09-27T20:52:00.542-07:00Rudiments 1.4.1 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.4.1 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>Though this release just adds the sensitivedata class required by SQL Relay 1.9.2.</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-23676865198914423732021-06-21T08:27:00.002-07:002021-06-21T08:27:57.290-07:00SQL Relay 1.9.1 second packaging update<p>Well, I can't explain how, but apparently a few files inside of the previously released sqlrelay source code got gzipped. Specifically config.guess and config.sub. This led to configure not working, and general impossibility compiling things. I just repackaged everything again, double-checked it prior to upload, and double-checked the download. All appears to be well at the moment. If you had trouble building SQL Relay from source, please re-download and try again.</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-22722681600997856352021-06-18T13:39:00.004-07:002021-06-18T13:39:51.584-07:00SQL Relay 1.9.1 packaging update<p>It looks like I made a mistake during the 1.9.1 release. The sqlrelay-1.9.1.tar.gz and sqlrelay-1.9.1.zip files containing the source for SQL Relay actually contained the source code for the trunk, rather than the 1.9.1 release. Similarly, the source code for both rudiments and sqlrelay that was included inside of the binary distributions was also the source code for the trunk, rather than the appropriate vesions. This has just been fixed. So, if you had trouble building the source, or if you managed to successfully build the source, but running "sqlrsh --version" returns "sqlrsh 2.0.0" rather than "sqlrsh 1.9.1", then please re-download/build/install the 1.9.1 release.</p>
<p>There was no issue with the binary release. The rpms, debs, and windows installers all included the correct versions. The mistake was, oddly, just in the source distribution.</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-55346505166325514972021-06-15T16:36:00.007-07:002021-06-15T16:37:16.270-07:00SQL Relay 1.9.1 Release<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.9.1 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This release fixes 2 bugs: a potential crash in the PostgreSQL front-end module, and a fix/workaround for an isuse where if a NULL was bound to the first argument of MS SQL Server's isnull() function then it wouldn't actually be interpreted as a NULL.</p>
<p>If you've run into either of these issues, then this release is for you!</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-29453014919317414222021-04-28T01:41:00.002-07:002021-04-28T01:42:10.759-07:00SQL Relay 1.9.0 Release<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.9.0 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>The main features of this release are:</p>
<p><b>Support for TLS Encryption in the MySQL and PostgreSQL Protocol Modules</b></p>
<p>SQL Relay has long supported the MySQL and PostgreSQL protocols, on the front-end, allowing SQL Relay to be dropped-in, transparently between many MySQL or PostgreSQL applications and the database. However, SQL Relay's implementation of the MySQL and PostgreSQL protocols has lacked support for TLS encryption, until now. It is now possible to do end-to-end encryption (without a tunnel) from a MySQL or PostgreSQL application, through SQL Relay, and into the database.</p>
<p><b>Support for UTF-16 Characters in SQL Server nchar/nvarchar Fields</b></p>
<p>SQL Server 2012 introduced the ability to store UTF-16 characters in nchar/nvarchar fields in addition to the standard UCS-2 that they traditionally supported. However, SQL Relay has traditionally forced a UCS-2 encoding when using nchar/nvarchar fields, preventing SQL Server users from being able to take advantage of this feature. Starting with this release, when using ODBC to connect SQL Relay to a SQL Server database, you can now specify ncharencoding=UCS-2 or ncharencoding=UTF-16 in the connect string to instruct SQL Relay how to deal with nchar/nvarchar fields.</p>
<p><b>PHP 8 Support</b></p>
<p>SQL Relay should now compile against, and work seamlessly with PHP 8.</p>
<p><b>NodeJS 14+ Support</b></p>
<p>SQL Relay should now compile against, and work seamlessly with NodeJS 14+.</p>
<p>Of course, there are many other subtle new features, improvements, and bug fixes. Full ChangeLog follows:</p>
<ul>
<li>added missing inequality operators to end-of-bind detection</li>
<li>fixed commit/begin without commitcount error in sqlrimportcsv</li>
<li>fetch errors aren't returned if sqlrclient protocol version < 2</li>
<li>query-intercept catches "begin transaction" now</li>
<li>query-intercept doesn't intercept various begin-type statements if the query is actually a block of sql containing commit or rollback</li>
<li>added ncharencoding option to odbc connection to enable UTF-16 values in SQL Server nchars/nvarchars</li>
<li>fixed a bug that caused output binds of length 8000+ to fail on SQL Server</li>
<li>begin() runs "begin transaction" instead of just "begin" on SQL Server</li>
<li>fixed a column-count reset bug in odbc, db2, informix, and postgresql that could cause begin queries to fail when fake transaction blocks are used</li>
<li>object lists have correct column names for mysql, odbc, and jdbc now</li>
<li>fixed a bug that caused rollback to be called insted of commit when endofsession="commit" and faketransactionblocks=yes are used</li>
<li>commit/rollback is now called at the end of session when faketransactoinblocks=yes is used, whether or not the server believes it's in a transaction block, to catch cases where the begin-interceptor is intentionally bypassed</li>
<li>sqlrimportcsv handles dates without centuries now</li>
<li>fixed crash in replay module when table name is quoted</li>
<li>mysql explain statements work now</li>
<li>fixed node-gyp.js detection on ubuntu 20.04</li>
<li>added configure options for PHP 8.x</li>
<li>added php/pdo module tweaks for PHP 8</li>
<li>mysql and postgresql protocol modules support TLS now</li>
<li>pushed most of tls/gss code up into sqlrprotocol parent class</li>
<li>fixed subtle issues in mysql/postgresql database modules that could sometimes cause reexecutes of the same query with the same bind variables to return no results the second time</li>
<li>the configure script specifically looks for liberl_interface.a now</li>
<li>updated nodejs macros for node 14+</li>
<li>fixed subtle bugs in Python getField, getFieldLength, and getRowLengthsDictionary functions</li>
<li>added GVL management to the Ruby API</li>
<li>applied patch from Igor to fix configure.vbs VC++ version detection for non-US versions</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-34215530633691063902021-04-28T01:28:00.001-07:002021-04-28T01:28:47.470-07:00Rudiments 1.4.0 Release<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.4.0 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>Though this release does include some new classes, additions to existing classes, this release mainly features changes to improve consistency between classes, as well as some under-the-hood improvements.</p>
<p>ChangeLog follows:</p>
<ul>
<li>added wcharacter, wcharstring, and wstringbuffer classes</li>
<li>memorymap::getLength() -> getSize()</li>
<li>added various *IgnoringCase methods to charstring/wcharstring</li>
<li>removed length()/isNullOrEmpty() with const unsigned char * arguments</li>
<li>now size="number of bytes" and length="number of characters/elements", updated comments, parameters, and some method names</li>
<li>added datetime::getCentury()</li>
<li>added datetime::getShortYear()</li>
<li>fixed jsondom::write() of non-existent string values</li>
<li>fixed datetime::parse() crash when NULL parameters are passed in</li>
<li>added commandline::toDictionary()</li>
<li>added parameterstring::getDictionary()</li>
<li>fixed potential null dereference in jsondom::write()</li>
<li>fixed potential null dereference in templateengine::replaceVariable()</li>
<li>added error reporting to templateengine class</li>
<li>fixed potentially uninitialized optimum block size in sax class</li>
<li>optimized domnode::insertNode() for empty, append, and prepend cases</li>
<li>added quoted-printable encodings to charstring</li>
<li>added lots of row/column manipulation methods to csvdom class</li>
<li>tls::setProtocolVersion() supports "v" format: SSLv3, TLSv1.3, etc.</li>
<li>applied patch from Igor to fix configure.vbs VC++ version detection for non-US versions</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-28834011981383934122020-09-15T22:24:00.004-07:002020-09-15T22:24:36.271-07:00Rudiments 1.3.1 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.3.1 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>This patch release fixes a bug in the rpm spec file and a missing library dependency in the link command for librudiments-apache.</p>
David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-84099211087644601262020-08-25T22:35:00.001-07:002020-08-25T22:35:32.875-07:00SQL Relay 1.8.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.8.0 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This release mainly lays the groundwork for some future features, including a generic import/export framework. Some notable progress was also made on the JDBC driver, though it's still not finished. The most significant (finshed) new feature is an aes128 password encryption module.</p>
<p>There are some bugfixes as well. A long-standing issue with postgresql that could cause results from multiple open cursors to get confused has been resolved. A subtle error that could cause counting of bind variables on the client-side to hang has been fixed. A long-standing, but apparently obscure, issue that could cause tables from other MySQL schemas to be included in a "show tables" command has been fixed. As well as various other issues.</p>
<p>Full ChangeLog follows:</p>
<ul>
<li>unattended tests</li>
<li>added sqlrresultsetdomnode class to c++ client API</li>
<li>fixed datedelimiters parameter</li>
<li>added support for SQLParamOptions with SQLUINTEGER arguments</li>
<li>sqlr-import detects uppercase .CSV suffix now</li>
<li>fixed a csv number-detection but in sqlr-import</li>
<li>fixed a delete[] of a const in sqlr-import</li>
<li>it's possible to specify a commitcount of 0 with sqlr-import now</li>
<li>moved sqlrimportxml/csv classes into libsqlrclient</li>
<li>moved sqlrexportxml/csv classes into libsqlrclient</li>
<li>csv import/export is consistent now</li>
<li>added some event methods to sqlrexport</li>
<li>migrated parsedatetime functions to rudiments datetime class</li>
<li>different postgresql cursors use different stmtNames now</li>
<li>sqlrimportcsv can create a primary key that's not in the CSV now</li>
<li>sqlrexportcsv quotes 12+ digit numbers now</li>
<li>fixed subtle, count-related issues when validating bind variables on the client side, that could cause a hang</li>
<li>added an aes128 pwdenc module</li>
<li>applied a patch to fix a crash in the debug logger (missing "%s")</li>
<li>added a tweak to getsitearchdir.rb to fix incorrect lib/lib64 reporting on some centos x64 systems</li>
<li>fixed mysql getColumnList to distinquish proper db/schema</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-38430273658591812842020-08-25T22:28:00.000-07:002020-08-25T22:28:00.879-07:00Rudiments 1.3.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.3.0 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>This release mainly featured integration of code from other projects. The remnants of the old firstworks project "stencil" have been merged in, as well as some MVC base classes from other projects. Some date-time parsing code from SQL Relay has been pushed down into the datetime class.</p>
<p>There are some new features as well - most notably an encryption framework and aes128 class. There are also sax/dom parsers for CSV, ini, and java-style properties files as well, similar to the xml/json sax/dom classes.</p>
<p>The logger classes have been refactored a bit, in back-incompatible ways. Thus the minor version bump.
<p>Full ChangeLog follows:</p>
<ul>
<li>unattended tests</li>
<li>added support for escaping single/double-quotes to templateengine</li>
<li>migrated in clasess from stencil project</li>
<li>added librudiments-apache</li>
<li>added \r\n support when parsing CSVs</li>
<li>integrated MVC base classes from other projects</li>
<li>added inisax/inidom and propsax/propdom classes</li>
<li>added cronschedule class</li>
<li>added logger start/end methods</li>
<li>logger string-write works like printf now</li>
<li>logger indent uses uint32_t now</li>
<li>removed some unused logger::write() methods</li>
<li>added a log level to the logger class</li>
<li>process::spawn()'s forked child exits if exec() fails now</li>
<li>charstring::replace() methods are null-safe now</li>
<li>migrated datetime::parse() and datetime::formatAs() from sqlrelay</li>
<li>csvsax accepts empty (or all whitespace) files now</li>
<li>logger::write() is null-safe now</li>
<li>base64encode is whitespace-safe now</li>
<li>added support for 5-part dates and day-month-year 4-part dates</li>
<li>added datetime::getWeekOfYear</li>
<li>added file::extension</li>
<li>added directory::createTemporaryDirectory</li>
<li>added directory::removeTree</li>
<li>added encryption interface</li>
<li>added aes128 encryption</li>
<li>added charstring::startsWith/endsWith</li>
</ul>
David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-51615070418692911222020-01-06T13:53:00.001-08:002020-01-06T13:55:03.446-08:00SQL Relay 1.7.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.7.0 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This release adds 2 significant features: support for the PostgreSQL client-server protocol, and a "replay" module to help automatically recover from deadlocks and lock-timeouts, but also has the usual assortment of minor bugfixes and internal changes.</p>
<br/><br/>
<b>PostgreSQL Client-Server Protocol</b>
<p>SQL Relay has supported the MySQL client-server protocol for several minor releases, enabling MySQL applications to take advantage of SQL Relay's features by aiming SQL Relay at the database(s) and aiming the application at SQL Relay. It has been a server-only solution requiring no modification of the client application or even any installation of software on the client system.</p>
<p>Release 1.7.0 features similar support for the PostgreSQL client-server protocol. PostgreSQL applications can now take advantage of SQL Relay's features by aiming SQL Relay at the database(s) and aiming the application at SQL Relay. It is also a server-only solution requiring no modification of the client application or even any installation of software on the client system.</p>
<p>See the <a href="http://sqlrelay.sourceforge.net/sqlrelay/admin/configguide.html#postgresqlprotocol">SQL Relay Configuration Guide</a> for configuration details.</p>
<br/>
<b>Replay Module</b>
<p>"How do I handle database deadlocks!?"</p>
<p>The answer is usually: figure out which queries are deadlocking and rewrite them to be less likely to deadlock. When that fails, the prescribed solution is to rewrite your application to detect the deadlock and either rerun the previous query (eg. with Oracle) or rerun all queries in the current transaction (eg. with MySQL).</p>
<p>While rewriting queries to be less likely to deadlock might be possible in some cases, rewriting applications to record queries and be able to replay entire transactions is unrealistic. It is especially unrealistic if you consider the trouble involved with doing an insert into a table with an auto-increment column, followed by updates that use the generated id.</p>
<p>But, while it might be unrealistic for applications, it's not terribly unrealistic for a database proxy like SQL Relay.</p>
<p>This release of SQL Relay provides a "replay" module, which can be configured to record and replay either the previous query, or previous transactions-worth of queries upon detecting a deadlock, lock timeout, or other condition. It even correctly rewrites the inserts that it records to handle auto-increment columns.</p>
<p>See the <a href="http://sqlrelay.sourceforge.net/sqlrelay/admin/configguide.html#replay">SQL Relay Configuration Guide</a> for configuration details.</p>
<br><br>
<p>Full ChangeLog follows:</p>
<ul>
<li>added postgresql protocol module</li>
<li>updated postgresql connection module to get column info pre-execute</li>
<li>fixed postgresql connection module type oid bug</li>
<li>added tag filter/moduledata</li>
<li>added moduledata(s)::closeResultSet()/endTransaction()/endSession()</li>
<li>mysql protocol returns empty lobs correctly now (not as nulls)</li>
<li>configure replaces -lfbclient with -lgds on freebsd/firebird-2.0.3</li>
<li>fixed a bug that could cause sqlr-stop to try to kill pid 0</li>
<li>fixed unixodbc detection on solaris 11.4</li>
<li>added configure test for PQdescribePrepared</li>
<li>test improvements</li>
<li>documentation improvements</li>
<li>split sqlrelay-crash directive into its own module</li>
<li>deprecated drop-in replacement libraries in favor of protocol support</li>
<li>fixed various mysql 4.x bugs</li>
<li>sqlr-status creates statistics on heap now instead of stack, to work on platforms with a small default ulimit stack</li>
<li>mysql stored procedure test is bypassed for older mysql</li>
<li>tls test is bypassed for older openssl</li>
<li>added NULL handoff socket workaround</li>
<li>improved shutdown/crash handlers for sqlr-listener/connection</li>
<li>fixed hang when more-than-one address was specified in the instance:addresses attribute</li>
<li>*_null used instead of *_unset on PHP 7.4</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-75170804637570636362020-01-06T13:27:00.001-08:002020-01-06T13:27:49.997-08:00Rudiments 1.2.2 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.2.2 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>This is a minor bug-fix release. ChangeLog follows:</p>
<ul>
<li>fixed another long-standing charstring::httpEscape bug</li>
<li>-Wno-deprecated-declarations is now included if possible</li>
<li>added configure tests for RB_HALT/AUTOBOOT (enums on solaris 11.4)</li>
<li>certificate tests use sha1 if sha256 is unavailable</li>
<li>filedescriptor::pvt->_lstnr is initialzed to NULL in clone operation now</li>
<li>listener::listen() safely handles non pollin/out events now</li>
<li>removed waitForChildren() from shutdown/crash handlers</li>
<li>sigsuspend() fails if errno!=EINTR now instead of whether it returns -1</li>
<li>filtered out -Wl,-Bsymbolic-functions -Wl,-z,relro from krb5-config</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-23608392116785368142019-08-12T11:29:00.000-07:002019-08-12T11:30:00.447-07:00SQL Relay 1.6.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.6.0 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This release mainly addresses some recently discovered regressions, but also adds some internal features that required the minor version to be bumped.</p>
<br><br>
<p>ChangeLog follows:</p>
<ul>
<li>added begin, commit, rollback events</li>
<li>fixed array_init() calls for php-7.3</li>
<li>integrated my_bool fix for mysql 8.0.1+</li>
<li>mysql sslmode=require/prefer + bad sslca/sslcapath generates warning rather than error now (like the mysql cli)</li>
<li>refactored various routines that parse bind variables out of queries</li>
<li>added bindvariabledelimiters config option to define supported bind variable delimiters</li>
<li>added fakeinputbindvariablesunicodestrings config option</li>
<li>added bind variable delimiters config methods to c++ api</li>
<li>replay trigger can now run a query (eg. "show engine innodb status") and log the reslits to a file when a replay condition occurs</li>
<li>replay trigger doesn't log/replay selects by defalit now (but this is configurable)</li>
<li>updated normalize translation to support queries containing binary data</li>
<li>fixed a backslash-escape bug in the normalize translation</li>
<li>refactored some sqlrclient api private methods</li>
<li>refactored various bind-manipliation/detection methods</li>
<li>sqlr-listener creates tmpdir now on start, if it doesn't exist (because this is often in /run, which is often a tmpfs)</li>
<li>postgresql connection modlie forces re-fetch of column data after execute now</li>
<li>everything uses charstring::isYes/isNo now, instead of direct comparisons against "yes" or "no"</li>
<li>fixed subtle sqlexecdirect bug</li>
<li>fixed subtle sqlserver max-varchar bind length bug</li>
<li>fixed various subtle sqlserver bugs where column-info isn't valid until after execute</li>
<li>odbc connection modlie sets column precision = column length if column precision = -1</li>
<li>when using odbc on front and back end, the object type works in SQLTables now</li>
<li>reslit set translations work with "show databases/tables/etc." queries with an ODBC backend now</li>
<li>increased oid buffer sizes in postgresql connection</li>
<li>fixed typemangling->tablemangling typo in postgresql connection - tablemangling sholid work without typemangling now</li>
<li>fixed a '...\\''...' parsing bug</li>
<li>non-odbc connection modlies now return odbc-compatible(ish) table lists</li>
<li>client info is no longer reset during endSession</li>
<li>fixed a bug that colid cause sqlite "show tables like '...'" to crash</li>
<li>fixed odbc unicode nlil user/password bug</li>
<li>fixed PyString_AsString for python 3.<3</li>
<li>fixed bug that caused some MSSQL lobs to sometimes be returned as nlils when using ODBC on the backend</li>
<li>fixed bug that caused some MSSQL date fields to get returned as garbage</li>
<li>fixed a few older sqlrclient compatibility bugs</li>
<li>fixed SQLFetch parameter type mismatch in ODBC api</li>
<li>removed a non-c++17-compliant "register" from custom_nw logger</li>
<li>added support for nodejs 12</li>
<li>SQLDriverConnect can take an inline DSN now</li>
<li>fixed odbc maxcolumncount=-1 crash</li>
<li>odbc, db2, and informix set bind format error now</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-89231224374769046192019-08-12T11:23:00.000-07:002019-08-12T11:23:58.022-07:00Rudiments 1.2.1 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.2.1 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>This is a minor bug-fix release. ChangeLog follows:</p>
<ul>
<li>charstring::isYes includes "on" and charstring::isNo includes "off"</li>
<li>tabs are url-encoded correctly now</li>
<li>"unsafe" characters are url-encoded now</li>
<li>httpEscape uses character::isAlphanumeric now (to improve perforamnce)</li>
<li>some file-descriptor-passing tweaks for modern FreeBSD</li>
<li>fixed some json parsing bugs</li>
<li>updated default_md=sha256 in ca.cnf to generate ca.pem in tests</li>
<li>fixed a possible double-free in listener::cleanUp</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-28738643005580811692019-04-17T22:02:00.000-07:002019-04-23T11:46:18.938-07:00SQL Relay 1.5.2 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.5.2 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This patch release features support for PHP 7.3 and MySQL 8.0 and some internal updates.</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-65203029556285946972019-03-28T19:33:00.000-07:002019-03-28T19:39:58.430-07:00SQL Relay 1.5.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.5.0 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>This release features some significant new features, and the usual number of obscure bug fixes.</p>
<br/>
<br/>
<br/>
<br/>
<h3>Notable Changes and Improvements</h3>
<br/>
<b>MySQL Front-End Module</b>
<p>The most notable feature of this release is the integration of the MySQL Front-End Module to the standard (free) SQL Relay distribution. Previously this module was only available as part of the SQL Relay Enterprise Modules, and had to be purchased and installed separately.</p>
<p>The MySQL Front-End Module allows SQL Relay to speak the MySQL client-server protocol and effectively act as a transparent proxy for MySQL applications. This enables MySQL apps to take advantage of other SQL Relay features, such as Connection Pooling, Throttling, High Availability, Query Routing, Query Translation, 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.</p>
<b>MySQL Performance</b>
<p>Several tweaks were made to the MySQL connection module as well. Most significantly, mysql_stmt_reset() is only called when needed now, significantly improving performance when api=stmt is used (the default).</p>
<b>SHA Auth Modules</b>
<p>sha1 and sha256 auth modules have been added. Basically, this means that sha1 or sha256 password hashes can be stored in the sqlrelay.conf file, rather than plaintext passwords. Previously, md5 was the only hash supported.</p>
<b>NodeJS 11 Support</b>
<p>NodeJS 11 is now supported.</p>
<b>Row Fetch Error Reporting</b>
<p>Errors that occur during a row-fetch are now reported. This resolves a long standing oversight. Basically, in the past, if a row-fetch failed, then the cursor was presumed to have fetched the last row. In practice, row-fetch errors are somewhat rare. Result set translation modules make them more likely though, so error reporting has been implemented. Of course, it should have always been there...</p>
<br/>
<h3>Less Notable, But Still Important Changes and Improvements</h3>
<br/>
<b>Various ODBC Improvements</b>
<p>ODBC uses different begin-transaction queries for different databases now.</p>
<p>Various improvements were also made to unicode-related functionality.</p>
<p>ODBC gets column info after prepare (before execute) in all cases now.</p>
<b>Various MySQL Improvements</b>
<p>Re-added a conditional mysql_stmt_reset call to automatically recover from "Commands out of sync" errors.</p>
<p>Made mapping of 0-scale decimals to longlong's in the protocol module optional, and disabled by default.</p>
<p>Fixed varchar type mapping (from char to varstring).</p>
<p>Fixed some subtle bind-realated bugs in the protocol module.</p>
<p>Database names can apparently contain characters which are otherwise invalid in MySQL identifiers. So, the connection module quotes database name when selecting the db now.</p>
<p>MySQL queries can contain raw binary data, preceeded by the __binary introducer. So, bytestring::copy() used now when copying queries between buffers, rather than charstring::copy().</p>
<b>Perl DBI Driver Fixes</b>
<p>The Perl DBI driver allows leading delimiters in bind variable names now.</p>
<p>Fixed bind_inout_param -> bind_param_inout in Perl DBI docs.</p>
<b>Various Firebird Fixes.</b>
<p>Fixed an int64 datatype decimal problem, and a blob error that could cause SQL Relay to hang.</p>
<br/>
<h3>Even Less Notable, Or Behind-The-Scenes Changes and Improvements</h3>
<p>countBindVariables correctly ignores @@'s now.</p>
<p>Added bulk load, bind variable translation, and module data frameworks. These are actually quite powerful, but not yet wrung out or documented.</p>
<p>Added error handling to translation frameworks.</p>
<p>Added per-tx/session memory pools, and migrated sqlrtranslations memory pool to per-session pool.</p>
<p>All tx-queries are intercepted now (including autocommit on/off) and "in-transacton" is now tracked. Added a controller-level endTransaction() method and endTransaction/endSession methods to all modules.</p>
<p>Added an (incomplete) replay trigger module.</p>
<p>Fixed a systemctl enable bug that affected some Ubuntu platforms.</p>
David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-67537977466671380782019-03-28T18:44:00.000-07:002019-03-28T18:44:56.030-07:00Rudiments 1.2.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.2.0 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>This release adds a few minor features, and fixes a few minor bugs...</p>
<p>The jsonsax/dom classes handle escaping correctly now.</p>
<p>The url class features a getError() method which returns more detailed error information than the error class. For example, if there's a protocol error, as opposed to an operating-system-level error, then url::getError() returns it.</p>
<p>A sha256 class has been added. The sha1, sha256, and md5 classes now prefer to use libcrypto implementations, if they are available, as they might be hardware accelerated, but fall back to internal implementations if they are not available.</p>
<p>hash::getHash() returns binary data now, for all hashes. Previously sha1/256 returned binary data and md5 returned a string.</p>
<p>charstring::hexEncode()/hexDecode(), and charstring::before()/between()/after() methods have been added to the charstring class.</p>
<p>The url class now supports setting the user agent and other headers for http urls. It also supports making http POST requests.</p>
<p>The various container classes (linkedlist, avltree, dictionary, etc.) all support remove/clearAnd(Array)Delete methods. The removeAnd(Array)Delete methods remove the node from the container and delete the value stored in the node as well. The clearAnd(Array)Delete methods operate similarly, removing all nodes.</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-49028572257177156792018-10-30T20:30:00.002-07:002018-10-30T21:04:31.717-07:00Accessing Teradata from SQL Relay<p><b>Intro</b></p>
<p>Teradata, the company, has been around, in one form or another, since 1979. Their EDW-focused database has been around since the mid-80's. That's years before "data-warehouse" was even a term. I always love when something has been around longer than the word for what the thing is.</p>
<p>In the late 90's I remember reading that Wal-Mart was data-mining more than a terabyte database of customer info. At the time, I couldn't even get my mind around how much data that was. I couldn't even imagine the storage array. Turns out, that was a Teradata database, and it had apparently been running for years (since 1992) when I finally heard about it. In the early 90's, I'm sure the term "terabyte" technically existed, but I doubt many people had actually spoken the word.</p>
<p>Pretty amazing!</p>
<p>I recently needed to get SQL Relay talking to a Teradata database. A quick search revealed an Express version and ODBC drivers for every conceivable platform, so I figured it would be straightforward. It turned out to be <i>reasonably</i> straightforward, but I did hit a few snags worth mentioning to anyone else pursuing the same goal.</p>
<p>Details follow...</p>
<br/>
<p><b>Teradata Express</b></p>
<p><a href="http://downloads.teradata.com/download/database/teradata-express-for-vmware-player">Teradata Express</a> is available as a VMware image. You have to create an account to download it, but once you do, you're presented with a 7z file that you can extract on linux as follows:</p>
<pre>
7za x TDExpress16.20.12.01_Sles11_20180620112938.7z
</pre>
<p>It expands into a directory named TDE16.20.12.01 which contains a vmx file and a couple of vmdk files. Running it is as simple as aiming VMware Player or Workstation at the vmx and clicking Start. It requires 4G of memory to run though, so make sure to shut down or suspend whatever else you might have to to free that up before you actually click Start or your system will be paging for the next 10 minutes.</p>
<p>When you start it up, VMware will ask you if you moved or copied the VM. In this case, it's safe to click either "I Moved It" or "I Copied It". "I Copied It" will just assign the NIC a new virtual MAC address.</p>
<p>The OS is apparently SuSE Enterprise 11, and VMware tools is already installed. It boots up to a super primitive-looking X login, but after you log in, the desktop appears to be Gnome 2 with a few SuSE customizations.</p>
<p>The root password is "root". Poking around, there appear to be other logins too, but I haven't tried any of them.</p>
<p>The database starts by default. If you manage to stop it, there's an icon on the desktop to restart it.</p>
<p>The database also comes configured with a user named "dbc" with password "dbc".</p>
<p>There's an icon for the Teradata Studio Express on the desktop. It's a full-featured graphical database shell, like Toad, or RazorSQL, or other similar tools.</p>
<blockquote>
<p>It appears to be based on Eclipse too, which I thought was neat. I'd long heard that Eclipse isn't just an IDE, but rather a framework for building generic IDEs, and Eclipse-proper is more like a flagship-example of the technology. I'd never actually seen (or noticed) another Eclipse-framework-based tool though, until this one.</p>
</blockquote>
<p>You can create a new database connection to the local server using:</p>
<ul>
<li>Connection Profile Type: Teradata</li>
<li>Database Server Name: localhost</li>
<li>User Name[Domain]: dbc</li>
<li>Password: dbc</li>
</ul>
<p>After connecting, you can run queries and play around, in general.</p>
<p>If you want to create another user, you can log in as dbc and run a command like:</p>
<pre>
create user testuser as password=testpassword perm=524288000 spool=524288000;
</pre>
<blockquote>
<p>The <i>perm</i> and <i>spool</i> arguments are required. The <i>perm</i> parameter specifies the maximum amount of permanent storage allocated to the user. The <i>spool</i> parameter specifies the amount of "spool space" allocated to the user. It's not immediately clear what either of these parameters mean, but they're both required. I don't remember where I even got those sizes from, probably from some example online, but I haven't run into any problems with them yet, in my Express instance.</p>
</blockquote>
<p>One quirk though... After running the command, the UI replaces the password with *'s, and then displays a red X to the right of the query. Mouse-overing the red X pops up an error. This may lead you to think that the query failed. It didn't. Rather, the error is just complaining about the *'s being invalid SQL. It kind-of makes sense, but it's confusing.</p>
<p>To drop a user, log in as dbc and run a command like:</p>
<pre>
drop user testuser;
</pre>
<p>Various Teradata command line tools are also installed, like bteq (the command line db shell), fastload, and tdload. If you want to write some programs, ODBC and JDBC drivers are also installed, and the VM comes with gcc 4.3 and Oracle Java 8. ODBC doesn't appear to be configured though.</p>
<p>I'm sure that the database has various limits imposed on it. I don't know what they are offhand, or whether I've even run up against them or not. If you're just interested in getting familiar with the technology though, it's a pretty good environment.</p>
<p>The virtual NIC is configured to grab an address from DHCP by default, but you'll probably want to give it a static IP if you plan on hitting the database from another machine. Just navigate to Computer -> Control Center -> Network Settings to access the network configuration tool. The only quirky bit is that the VM is apparently configured without a hostname, and the tool is rather adamant that you give it one.</p>
<p>In case you want to access the VM remotely, the ssh server is running, and root logins aren't disabled.</p>
<br/>
<p><b>Installing Teradata ODBC</b></p>
<p>I wanted to access the database from SQL Relay on a remote machine - another VM, running Fedora 26. The most straightforward way to do this seemed to be to install and configure the <a href="https://downloads.teradata.com/download/connectivity/odbc-driver/linux">Teradata ODBC Driver for Linux</a> and then configure SQL Relay to use that.</p>
<p>The driver seems semi-straightforward to install. BUT! It tends to inadvertently sabotage your existing ODBC infrastructure, if you have one. So, it actually requires a bit of special handling.</p>
<p>The problem is that it contains its own copies of:</p>
<ul>
<li>/lib/libodbc.so</li>
<li>/lib/libodbcinst.so</li>
<li>/lib64/libodbc.so</li>
<li>/lib64/libodbcinst.so</li>
</ul>
<p>I guess this is so that it doesn't have to depend on an existing unixODBC installation. I don't know. All I know is, that if you install the software, it will overwrite those files, and the new files tend to break previously-working ODBC configurations.</p>
<p>So, if you have an existing unixODBC installation, then to safely install the Teradata driver alongside of it, you have to move those files out of the way, do the installation, them move them back. The Teradata driver appears to work fine with the libraries provided by unixODBC, at least in Fedora 26.</p>
<p>If you have an existing unixODBC installation, here's what you have to do to install the Teradata Driver:</p>
<p>If you have 32-bit unixODBC installed:</p>
<pre>
cd /lib
sudo mv libodbc.so libodbc.so.save
sudo mv libodbcinst.so libodbcinst.so.save
</pre>
<p>If you have 64-bit unixODBC installed:</p>
<pre>
cd /lib64
sudo mv libodbc.so libodbc.so.save
sudo mv libodbcinst.so libodbcinst.so.save
</pre>
<p>If you're running some version of Linux other than Fedora, then you'll have to find and move the appropriate libraries for your platform.</p>
<p>To actually install the driver:</p>
<pre>
tar xfz tdodbc1620__linux_indep.16.20.00.36-1.tar.gz
cd tdodbc1620
sudo ./setup_wrapper.sh
</pre>
<p>Hit return to allow it to install in /opt</p>
<p>Afterwards, it will run unattended and install tdodbc1620-16.20.00.36-1.noarch.rpm. Oddly, this rpm is marked "noarch" but it actually installs binaries for both x86 and x64 platforms.</p>
<p>Post-install, you have to move the newly installed libodbc.so and libiodbc.so links out of the way, as follows:</p>
<pre>
cd /lib
sudo mv libodbc.so libodbc.so.teradata
sudo mv libodbcinst.so libodbcinst.so.teradata
cd /lib64
sudo mv libodbc.so libodbc.so.teradata
sudo mv libodbcinst.so libodbcinst.so.teradata
</pre>
<p>Then, if you have 32-bit unixODBC installed:</p>
<pre>
cd /lib
sudo mv libodbc.so.save libodbc.so
sudo mv libodbcinst.so.save libodbcinst.so
</pre>
<p>Or, if you have 64-bit unixODBC installed:</p>
<pre>
cd /lib64
sudo mv libodbc.so.save libodbc.so
sudo mv libodbcinst.so.save libodbcinst.so
</pre>
<p>And that is it. The ODBC driver for Teradata is now installed.</p>
<br/>
<p><b>Configuring Teradata ODBC</b></p>
<p>Configuring the Teradata ODBC Driver is a lot simpler than installing it.</p>
<p>You don't have to add anything to /etc/odbcinst.ini, just append a DSN like the following to /etc/odbc.ini</p>
<pre>
[teradata]
# This key is not necessary and is only to give a description of the data source.
Description=Teradata Database ODBC Driver 16.20
# Driver: The location where the ODBC driver is installed to.
Driver=/opt/teradata/client/16.20/lib64/tdataodbc_sb64.so
# Required: These values can also be specified in the connection string.
DBCName=192.168.123.101
UID=testuser
PWD=testpassword
# Optional
AccountString=
CharacterSet=ASCII
DatasourceDNSEntries=
DateTimeFormat=IAA
DefaultDatabase=
DontUseHelpDatabase=0
DontUseTitles=1
EnableExtendedStmtInfo=1
EnableReadAhead=1
IgnoreODBCSearchPattern=0
LogErrorEvents=0
LoginTimeout=20
MaxRespSize=65536
MaxSingleLOBBytes=0
MaxTotalLOBBytesPerRow=0
MechanismName=
NoScan=0
PrintOption=N
retryOnEINTR=1
ReturnGeneratedKeys=N
SessionMode=System Default
SplOption=Y
TABLEQUALIFIER=0
TCPNoDelay=1
TdmstPortNumber=1025
UPTMode=Not set
USE2XAPPCUSTOMCATALOGMODE=0
UseDataEncryption=0
UseDateDataForTimeStampParams=0
</pre>
<p>The most important parameters are:</p>
<ul>
<li>DBCName - the hostname or IP of the database (192.168.123.101 in my case, but maybe different in your environment)</li>
<li>UID - the username to log in to the database with (testuser in my case, but could also be dbc or another user)</li>
<li>PWD - the password corresponding to the UID</li>
</ul>
<p>The rest of the parameters do various things which can be researched online, but aren't critical to change for general operation.</p>
<p>Once configured, you can connect to the database using isql, provided by unixODBC.</p>
<pre>
$ isql teradata
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select 1
+-----+
| 1 |
+-----+
| 1 |
+-----+
SQLRowCount returns 1
1 rows fetched
SQL>
</pre>
<p>If that works, then the Teradata ODBC Driver has been configured successfully.</p>
<br/>
<p><b>Configuring SQL Relay</b></p>
<p>The ultimate goal here is to access Teradata through SQL Relay. After getting everything else working, that last bit is pretty simple. Assuming SQL Relay is already installed, all you have to do is update the configuration file (either sqlrelay.conf or a file in sqlrelay.conf.d) with a teradata instance, as follows:</p>
<pre>
<?xml version="1.0"?>
<instances>
<instance id="teradataexample" dbase="odbc">
<users>
<user user="exampleuser" password="examplepassword"/>
</users>
<connections>
<connection string="dsn=teradata;user=testuser;password=testpassword;autocommit=yes"/>
</connections>
</instance>
</instances>
</pre>
<p>In the connection tag, the dsn option must match the DSN defined in /etc/odbc.ini, and the user/password options must match the UID/PID definied in that DSN.</p>
<p>The user/password defined in the user tag are the user/password that you'll use to log into SQL Relay itself.</p>
<p>To start it up:</p>
<pre>
sqlr-start -id teradataexample
</pre>
<p>To access the database:</p>
<pre>
$ sqlrsh -host localhost -user exampleuser -password examplepassword
sqlrsh - Version 1.4.0
Connected to: localhost:9000 as exampleuser
type help; for help.
0> create table test (col1 int, col2 varchar(200));
Rows Returned : 0
Fields Returned : 0
Elapsed Time : 0.063005 sec
0> insert into test values (1,'hello');
Rows Returned : 0
Fields Returned : 0
Elapsed Time : 0.029303 sec
0> select * from test;
col1 col2
==========
1 hello
Rows Returned : 1
Fields Returned : 2
Elapsed Time : 0.030262 sec
0> drop table test;
Rows Returned : 0
Fields Returned : 0
Elapsed Time : 0.077361 sec
0> quit;
</pre>
<p>To shut it down:</p>
<pre>
sqlr-stop -id teradataexample
</pre>
<p>If all of that worked, then you can now access Teradata from SQL Relay.</p>
<br/>
<p><b>Quirks</b></p>
<p>SQL Relay -> ODBC -> Teradata is generally usable, but ODBC drivers are quirky, as a rule, so there are probably things that don't work correctly. I've already discovered a few esoteric ones and I'll be updating the ODBC connection module with Teradata-specific workarounds as appropriate.</p>
<p>If you run into anything weird, please report it to support@firstworks.com.</p>
<p>Thanks!</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-56021086592738263442018-10-23T13:26:00.000-07:002018-10-23T13:47:35.048-07:00Wrangling mydumper and Auto-Increment Columns<p>One of my client's apps has a large MySQL database that I regularly have to get a dump of and reload a local database from the dump for dev and testing. To expedite this process, I recently started using the excellent <a href="https://github.com/maxbube/mydumper">mydumper/myloader</a> in place of the venerable mysqldump. It does shave considerable time off of the reload process, but I got strange results the first few times I tried to use it.</p>
<p>Basically, there were a few tables that look like this in production:</p>
<pre>
col1 | col2
------+----------------
0 | some value
1 | some other value
2 | some third value
... | ...and so on...
</pre>
<p>When I'd use mysqldump to dump them, and source the dump to load my local db, it would reliably dump/load the same data, in the same order.</p>
<p>When I'd use mydumper/myloader, I'd get something like:</p>
<pre>
col1 | col2
------+----------------
1 | some other value
2 | some third value
... | ...and so on...
20 | some value
</pre>
<p>The first row (with col1 = 0) would end up at the end of the table and col1 would have the wrong value. The app really cares too, so I had to figure out what was happening, and how to fix it.</p>
<p>This took a while, but the problem ultimately came down to:</p>
<ul>
<li>col1 is an auto-increment column</li>
<li>mydumper and mysqldump both output instructions to:</li>
<ul>
<li>create the table</li>
<li>reset the next-auto-increment value to whatever it was in the database that is being dumped</li>
<li>insert rows, using the exact values from the database that is being dumped</li>
</ul>
<li>by default, if you insert a 0 into an auto-increment column, mysql substitutes the next auto-increment value for the 0</li>
<li>mysqldump outputs one big .sql file to create tables and insert rows, and it includes instructions to disable this behavior</li>
<li>mydumper outputs lots of individual files (for each table - a script to create the table, and a binary full of data to load into it) but none of them contain instructions to disable this behavior</li>
</ul>
<p>So, basically if you source a script created by mysqldump, if col1 = 0, then you get a 0 for col1, but if you use myloader to load the output of mydumper, if col1 = 0, then you get whatever the next auto-increment value is. For all other values of col1, both work as expected.</p>
<p>Working around the problem was tricky. There's no obvious way to add a SET SESSION sql_mode='...,NO_AUTO_VALUE_ON_ZERO' to the mydumper binary data file. I thought about adding it to each of the table-create scripts, but I wasn't sure if the data-load would necessarily be run in the same session as the create, so the SET SESSION might not even be in effect when the data was being loaded.</p>
<p>I could have run a SET GLOBAL sql_mode='...,NO_AUTO_VALUE_ON_ZERO' prior to the dump, but I reboot the system all the time and I'd certainly forget to re-run it the next time, and even if I remembered, I'd forget exactly what it was that I needed to run. The only sure-fire solution was to alter the sql_mode of the server itself.</p>
<p>This involved adding a line to /etc/mysql/my.cnf, in the [mysqld] section, like this:</p>
<pre>
[mysqld]
...
sql-mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
</pre>
<p>...and bouncing the server. Actually, there's probably some way to get the server to reload the options, but I could easily bounce mine, so I did.</p>
<p>The NO_AUTO_VALUE_ON_ZERO part was the operative part, but I had to add the other options because they were apparently already in effect, according to:</p>
<pre>
select @@sql_mode
</pre>
<p>The sketchy part of this solution is that I'm not 100% sure that somewhere deep in that app (or in another app that uses the same DB), there isn't some query that relies on NO_AUTO_VALUE_ON_ZERO being disabled. So, arguably, this isn't the best solution. I don't know of a better one though, so for now I'm going with this.</p>
<p>Ideally, it would be great if mydumper handled this, per-session, itself. I think I'll submit a feature request...</p>
<p>(Update: looks like there's already an open issue for this: <a href="https://github.com/maxbube/mydumper/issues/142">https://github.com/maxbube/mydumper/issues/142</a>)</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-17817706277061496012018-10-23T12:33:00.000-07:002018-10-23T12:40:45.744-07:00PPTP on CentOS 7<p>I recently needed to get a PPTP VPN connection working from a CentOS 7 machine to one of my clients. I'd gotten the same working from MINT Linux a few weeks earlier, and it was as simple as clicking the network icon on the toolbar, selecting VPN Connections -> Configure VPN, and adding a PPTP connection. I figured it would be equally simple on CentOS 7, but I was surprised to find that the list of VPN types didn't include PPTP.</p>
<p>This was confusing because NetworkManager-pptp and NetworkManager-pptp-gnome packages were both installed. Long story short... As of 10/23/18, CentOS 7 uses NetworkManager 1.10 and compatible versions of the PPTP-related RPMs aren't available in the CentoOS repos. If you want to get it working, you have to build and install new enough RPMs from source.</p>
<p>Steps to accomplish this follow...</p>
<p>Make sure NetworkManager-pptp and NetworkManager-pptp-gnome are installed, just to be sure that you have various dependencies installed as well:</p>
<pre>
sudo yum install NetworkManager-pptp NetworkManager-pptp-gnome
</pre>
<p>Set up an RPM-build environment (if you don't already have one set up). This is most easily done by installing the fedora-packager meta-package and running a handy script that it provides:</p>
<pre>
sudo yum install fedora-packager @development-tools
rpmdev-setuptree
</pre>
<p>Note that rpmdev-setuptree wasn't run using sudo. This script should be run as you. It basically creates a .rpmmacro file and rpmbuild tree under your home directory.</p>
<p>Download a new enough source RPM for NetworkManager-pptp from somewhere. A new enough version is included in Fedora Core 28, for example:</p>
<pre>
wget https://dl.fedoraproject.org/pub/fedora/linux/updates/28/Everything/SRPMS/Packages/n/NetworkManager-pptp-1.2.8-1.fc28.src.rpm
</pre>
<p>Install packages that you'll need to build the RPMs:</p>
<pre>
sudo yum install glib2-devel gtk3-devel NetworkManager-libnm-devel libnma-devel ppp-devel libtool intltool libsecret-devel NetworkManager-devel NetworkManager-glib-evel libnm-gtk-devel
</pre>
<p>Build the RPMs:</p>
<pre>
rpmbuild --rebuild NetworkManager-pptp-1.2.8-1.fc28.src.rpm
</pre>
<p>And, finally, install the RPMs that you just built:</p>
<pre>
cd ~/rpmbuild/RPMS/x86_64
sudo yum install NetworkManager-pptp-1.2.8-1.el7.x86_64.rpm NetworkManager-pptp-gnome-1.2.8-1.el7.x86_64.rpm
</pre>
<p>(Of course, this assumes that you're building on an x86_64 host. Adjust the path and file names accordingly if you are not.)</p>
<p>At this point, if you try to create a new VPN connection, you should see "Point-to-Point Tunneling Protocol (PPTP)" in the list of available VPN types.</p>
<p>You can also safely remove NetworkManager-pptp-1.2.8-1.fc28.src.rpm at this point. If you don't think you'll be building any more RPMs any time soon, then you can also remove ~/.rpmmacros and the ~/rpmbuild tree. If you find that you do need to build more RPMs, then you can just rpmdev-setuptree again to re-setup the RPM-build environment.</p>
<p>Good luck!</p>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-58066212072303396482018-09-11T21:16:00.000-07:002018-09-11T21:16:33.890-07:00SQL Relay 1.4.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://sqlrelay.sourceforge.net/sqlrelay/images/sqlrelay.png" /></a></div>
<p>Version 1.4.0 of <a href="http://sqlrelay.sourceforge.net">SQL Relay</a>, the powerful connection pool, load balancer, query router, and database proxy, is now available.</p>
<p>Like the last release, this release is mainly focused on integrating recent improvements and customizations, contributed by or developed for various clients into the official distribution.</p>
<br/>
<br/>
<br/>
<h3>Notable Changes and Improvements</h3>
<p>dbase="mariadb" is now supported. You don't have to use dbase="mysql" when running SQL Relay against mariadb any more.</p>
<p>sqlr-import can import CSV files now.</p>
<p>sqlr-export correctly escapes double-quotes now.</p>
<p>Query translations are applied to the select-database query now.</p>
<p>The MySQL/MariaDB connect string supports an api=stmt/classic option now that selects which MySQL API to use. When api=classic, the mysql_*() functions are used and binds are faked. When api=stmt, the mysql_stmt_*() functions are used and binds are supported natively. It turns out that the classic API is faster, even with modern MySQL, so if you don't specifically need the functionality of the stmt API, then api=classic might work better for you. When using the MySQL Front-End Modules, you can even point an app that uses the stmt API at SQL Relay which is configured to use api=classic, and vice-versa.</p>
<p>The ODBC connect string supports a unicode=yes/no option that makes it possible to disable unicode with drivers that don't support it, such as the Cloudera Impala ODBC driver.</p>
<hr/>
<p>There are a lot of other changes too, but everything else is subtle or behind-the-scenes.</p>
<p>Full ChangeLog follows:</p>
<ul>
<li>odbc connection module - trace=yes/no/default now instead of just yes/no</li>
<li>pushed detachbeforelogin parameter up to sqlrserverconnection class</li>
<li>updated to use rudiments container clearAndDelete...() methods</li>
<li>fixed nonsensical uint32_t majorversion>=0 comparison in freetds conn</li>
<li>fixed nonsensical uint16_t isnull==-1 comparison in odbc conn</li>
<li>fixed return NULL in method that returns false in router conn</li>
<li>added manual removal of build directory in nodejs clean target</li>
<li>updated nodejs clean target with - in front of node-gyp clean</li>
<li>fleshed out the installing-sqlrelay-from-packages doc</li>
<li>connect string "timeout" -> "connecttimeout" in docs</li>
<li>dbase="mariadb" works now</li>
<li>docs have been updated to acknowledge mariadb</li>
<li>added configure test to disable ODBC on platforms without SQLULEN</li>
<li>updated mysql_config test to add -L/usr/local/lib if it fails, to deal with libiconv, which is located there, on newer freebsd</li>
<li>updated mysql drop-in test not to test def_length if def is NULL</li>
<li>added NODEGYPPYTHON flag that can be set manually on netbsd</li>
<li>fixed gcj/gcj3/kaffe detection on older systems</li>
<li>fixed some buggy static module declarations/assignments</li>
<li>sqlr-import can import csv files now</li>
<li>sqlr-export correctly escapes double-quotes now</li>
<li>query translations apply to the select-database query now</li>
<li>added api=stmt/classic option to mysql connection module</li>
<li>added a per-cursor bindpool</li>
<li>added unicode=yes/no opiton to odbc connection module</li>
<li>fixed a bug that could cause stale column metadata to a protocol module between prepare and execute when binds are being faked</li>
<li>added stalecursors logger</li>
<li>added send()/recv() passthrough operations</li>
<li>made various bind-related buffers per-cursor</li>
<li>added pattern option to query notification event</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.comtag:blogger.com,1999:blog-8492368577479022871.post-44040414493179473082018-09-11T21:05:00.002-07:002018-09-11T21:05:47.701-07:00Rudiments 1.1.0 Release Announcement<div class="separator" style="clear: both; text-align: center;"><a href="http://rudiments.sourceforge.net/images/rudiments-icon.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://rudiments.sourceforge.net/images/rudiments-icon.png" /></a></div>
<p>Version 1.1.0 of Rudiments, the C++ class library for developing systems and applications, is now available.</p>
<p>This release features some fairly significant restructuring of the SAX and DOM related classes. In particular, xmlsax has been genericized into sax, xmldomn has been genericized ingo dom, and xmldomnode has been genericized into domnode. The xmlsax/xmldom classes have been reworked to inherit from sax/dom. The jsonsax/jsondom classes have been added to manipulate JSON objects. The csvsax/csvdom classes have been added to manipulate CSV files. And, finally, the xmldomevents class has been renamed to just domevents, as it can be used with any class that inherits from dom.</p>
<p>The bytebuffer class has also been overhauled and is now implemented similarly to std::basic_string, with similar performance characteristics.</p>
<p>The url class supports HTTP POST now.</p>
<p>Otherwise, there are a ton of internal fixes and improvements.</p>
<p>ChangeLog follows:</p>
<ul>
<li>fixed some dictionary and xmlsax memory leaks</li>
<li>fixed lib/lib64 detection in configure script</li>
<li>fixed Werror management in configure script</li>
<li>fixed race condition in threadmutext test</li>
<li>--enable-built-in-regex works on uw7 now</li>
<li>added regularexpression match-with-length methods</li>
<li>fixed file test on OSR and UnixWare</li>
<li>disabled chown in file test on Windows (not reliable over cifs)</li>
<li>fixed file descriptor passing on OSR</li>
<li>updated xmldomnode::print() to use the output interface</li>
<li>added various clearAndDelete...() methods to container classes</li>
<li>reenabled chat class by default</li>
<li>updated process::backtrace() to take an output, consolidated methods</li>
<li>added explicit virtual destructors to input/output classes</li>
<li>removed unused containerutilinlines.h header</li>
<li>improved dlopen/-ldl configure test</li>
<li>abstracted sax and dom classes</li>
<li>added csvsax and csvdom classes</li>
<li>renamed xmldomnode and xmldomevents to domnode and domevents</li>
<li>refactored write/print/xml methods of dom/domnode classes</li>
<li>refactored some classes to have trivial constructors</li>
<li>renamed regularexpression::compile() to setPattern(), added getPattern()</li>
<li>renamed memorypool::deallocate() to memorypool::clear()</li>
<li>renamed memorypool::allocateAndClear() to memorypool::allocateAndZero()</li>
<li>added clear-with-parameter-reset methods and parameter-getters to dynamicarray, bytebuffer, stringbuffer</li>
<li>added cursordomnode class</li>
<li>added jsonsax and jsondom classes</li>
<li>added some http-post options to url class</li>
<li>improved performance of charstring::integerLength(), charstring::parseInteger(), and stringbuffer::append(integer)</li>
<li>refactored bytebuffer/stringbuffer to improve performance</li>
<li>getStringLength() -> getSize() in read() with terminator</li>
<li>added safe-to-include-byteswap.h-after-netinet/in.h configure test</li>
<li>updated tls code to actually use RUDIMENTS_SSL_VOID_PTR macro</li>
<li>moved various madvise/mprotect/mlock methods to sys class</li>
<li>added tests for systems that have, but don't define tzset, ftruncate, and fsync</li>
<li>various windows spawn() fixes</li>
</ul>David Musehttp://www.blogger.com/profile/17642486619298975505noreply@blogger.com