Knowledgebase:

Database connection errors after upgrading PostgreSQL to v.10

Posted by Anna Christian on 12/11/2017 08:29 AM

Issue: after upgrading the PostgreSQL database to v.10, the Ignition Gateway log posts errors containing the org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers.

Solution: update the JDBC driver for PostgreSQL.

Background

After upgrading the PostgreSQL database to v.10 (from one of the previous versions), the Ignition Gateway log posts errors when queries creating new rows are run,  for example:

TagHistoryDatasourceSink 18Oct2017 13:24:04 Error modifying tag information in the database.
org.postgresql.util.PSQLException: Returning autogenerated keys is only supported for 8.2 and later servers

This message can be misleading as clearly PostgreSQL v. 10 is a later version than v. Postgres 8.2. The actual issue is a result of using an older JDBC driver to connect to your database. Ignition comes with JDBC drivers for major databases pre-installed, but when new database version that requires an updated driver is released by the database developer, this pre-packaged driver may end up not working for the newly released database. Since in this case the error messages are typically  a result of some functionality that the older driver does not support and is unable to correctly deal with, the error messages returned to Ignition may potentially be unhelpful or misleading. The solution to the problem in cases like that is to update the JDBC driver to the latest version provided by the database developer.

Steps

To resolve the issue follow the steps below:

  1.  Download the latest driver
    You can obtain the latest driver (at the time of this writing, v. 42.1.4) here. Download the driver archive, extract it, and locate the .jar file (for example. postgresql-42.1.4.jar).
    Note the path to the .jar file, you will need it in Step 4.
  2. Edit JDBC Driver
    Open the Gateway Web interface and navigate to the Configure / Databases / Drivers page.
    Click "Edit" next to the "PostgreSQL JDBC Driver" option to open the "Edit JDBC Driver" page.
  3. Upload the new .jar file
    Click "Choose File" button  of the "JAR File(s)" property, navigate  to the .jar file you located in Step 2, and upload the file to the Gateway. This can take a few seconds. 
  4. Save the changes to the "Edit JDBC Driver" page
  5. Reinitialize the database connection
    Navigate to the Configure / Databases / Connections, click the "Edit" button next your connection name, and save the connection (no changes are necessary).

You should now see that the new errors related to this database connections stopped appearing and that the data is now getting inserted into the database. 

(19 vote(s))
Helpful
Not helpful