If you’re having problems with SQL commands timing out in Aginity Workbench for any PostgreSQL database (Amazon Redshift, IBM Netezza, EMC Greenplum), this post will instruct you on how to fix the issue by upgrading the included Npgsql PostgreSQL driver library to version 2.0.13 beta, which fixes the issue.
If you attempt to set “Default query timeout” or “Query timeout” over 2147 seconds, Aginity Workbench doesn’t adhere to the new timeout due to a bug in the Npgsql library version 188.8.131.52 that Aginity is currently using. Aginity Workbench will still timeout after 48 minutes with the following message:
A timeout has occured. If you were establishing a connection, increase Timeout value in ConnectionString. If you were executing a command, increase the CommandTimeout value in ConnectionString or in your NpgsqlCommand object.
You can follow these instructions to fix this issue, by upgrading to the 2.0.13 beta version of Npgsql that fixes this bug.
Close all open instances of Aginity Workbench.
Download the .NET 2.0 version of the 184.108.40.206 Npgsql binaries from the official web site at this link.
Unzip the Npgsql archive and copy the Npgsql.dll file into your Aginity Workbench install directory, replacing the older file (optionally, instead of replacing the file, you can rename the old Npgsql.dll file to something like Npgsql.dll.old). For Aginity Workbench for Redshift Beta 2, the directory is at:
C:\Program Files (x86)\Aginity\Aginity Workbench for Redshift (beta)(x64)
Open the config file for Aginity Workbench in a text editor (like Notepad). For the current version of Aginity Workbench for Redshift beta 2, the file is located at:
C:\Program Files (x86)\Aginity\Aginity Workbench for Redshift (beta)(x64)\Aginity.RedshiftWorkbench.exe.config
Inside of the <configuration> tag, add the following lines:
<runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" /> <bindingRedirect oldVersion="220.127.116.11" newVersion="18.104.22.168" /> </dependentAssembly> </assemblyBinding> </runtime>
For example, your configuration file should now start like this:
<?xml version="1.0" encoding="utf-8"?> <configuration> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" /> <bindingRedirect oldVersion="22.214.171.124" newVersion="126.96.36.199" /> </dependentAssembly> </assemblyBinding> </runtime> <appSettings>
Once you save the configuration file and start Aginity Workbench, you will now be using the newer version of Npgsql that will fix the timeout bug.
Update: As of 04/06/2015, Aginity has announced version 4.4.2183.656 of Agnity Workbench with version 188.8.131.52 of the Npgsql library, which should resolve this issue.
5 Replies to “Fixing query timeout in Aginity Workbench”
Thanks for the tip, but is this still current and relevant for the latest version of Aginity Workbench? Also, there are many newer versions of the Npgsql library than the one you use – should people be using newer versions?
Yes, the 4.3.1962 GA release that Aginity just released in December of 2014 still uses the buggy Npgsql. The install directory is slightly different, but these instructions still apply. There were newer versions of Npgsql available when I posted this article, but I chose the closest version that had the fix because new versions may have different functions and function names and be incompatible. Please let me know if a newer version works for you. Also, I emailed Aginity, and they said that they’re aware of this bug and will be fixing it in their next version. I don’t know why it wasn’t fixed in the December release.
Thanks Mark for posting. I am getting this error message:
“Could not load file or assembly ‘Npgsql,Version=184.108.40.206,culture=Neutral, public kety token=5d8b90d52f46fda7’ or one of its dependencies. The located assembly’s manifest decision does not match the assemble reference.(Exception from Hresult:0x80131040)”
once when I try to connect to database. Could you please let me know what has to be done? – Thanks
Make sure that you didn’t leave the old Npgsql.dll file lying around with a .dll file extension. For example, if you named it Npgsqlold.dll, you might get that error. Other than that, make sure you followed the instructions correctly.
Hi Mark, the reason of the old Npgsql having been used was this https://github.com/npgsql/npgsql/issues/163
Now that it seems to be fixed (https://github.com/npgsql/npgsql/issues/476), we are going to include 2.2.5 into the distribution. This should fix the timeout issue. Thanks for your patience! :)